[Home] [Help]
281: from per_all_people_f p
282: where p.person_id = P_PERSON_ID;
283: exception
284: when NO_DATA_FOUND then
285: hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
286: hr_utility.set_message_token ('PROCEDURE',
287: 'PERSON_EXISTANCE_CHECK');
288: hr_utility.set_message_token ('STEP', '1');
289: hr_utility.raise_error;
282: where p.person_id = P_PERSON_ID;
283: exception
284: when NO_DATA_FOUND then
285: hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
286: hr_utility.set_message_token ('PROCEDURE',
287: 'PERSON_EXISTANCE_CHECK');
288: hr_utility.set_message_token ('STEP', '1');
289: hr_utility.raise_error;
290: END person_existance_check;
284: when NO_DATA_FOUND then
285: hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
286: hr_utility.set_message_token ('PROCEDURE',
287: 'PERSON_EXISTANCE_CHECK');
288: hr_utility.set_message_token ('STEP', '1');
289: hr_utility.raise_error;
290: END person_existance_check;
291: -------------------- END: person_existance_check ---------------------------
292: --
285: hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
286: hr_utility.set_message_token ('PROCEDURE',
287: 'PERSON_EXISTANCE_CHECK');
288: hr_utility.set_message_token ('STEP', '1');
289: hr_utility.raise_error;
290: END person_existance_check;
291: -------------------- END: person_existance_check ---------------------------
292: --
293: -------------------- BEGIN: pay_predel_validation --------------------------
321: and ppa.action_type <> 'Z');
322: --
323: exception
324: when NO_DATA_FOUND then
325: hr_utility.set_message (801,'HR_6237_ALL_ASS_ACTIONS_EXIST');
326: hr_utility.raise_error;
327: end;
328: --
329: END pay_predel_validation;
322: --
323: exception
324: when NO_DATA_FOUND then
325: hr_utility.set_message (801,'HR_6237_ALL_ASS_ACTIONS_EXIST');
326: hr_utility.raise_error;
327: end;
328: --
329: END pay_predel_validation;
330: -------------------- END: pay_predel_validation ---------------------------
377: );
378: --
379: exception
380: when NO_DATA_FOUND then
381: hr_utility.set_message (801,'HR_6274_ALL_AOL_PER_NO_DEL');
382: hr_utility.raise_error;
383: end;
384: --
385: END aol_predel_validation;
378: --
379: exception
380: when NO_DATA_FOUND then
381: hr_utility.set_message (801,'HR_6274_ALL_AOL_PER_NO_DEL');
382: hr_utility.raise_error;
383: end;
384: --
385: END aol_predel_validation;
386: -------------------- END: aol_predel_validation -------------------------
416: and asa2.assignment_id <> asa.assignment_id)
417: );
418: EXCEPTION
419: when NO_DATA_FOUND then
420: hr_utility.set_message (801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
421: hr_utility.raise_error;
422: --
423: END assignment_set_check;
424: -------------------- END: assignment_set_check -------------------------
417: );
418: EXCEPTION
419: when NO_DATA_FOUND then
420: hr_utility.set_message (801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
421: hr_utility.raise_error;
422: --
423: END assignment_set_check;
424: -------------------- END: assignment_set_check -------------------------
425: --
455: l.effective_start_date and l.effective_end_date;
456: --
457: BEGIN
458: --
459: hr_utility.set_location('closed_element_entry_check',1);
460: --
461: for EACH_ENTRY in THIS_PERSONS_ELEMENT_ENTRIES loop
462: hr_entry.chk_element_entry_open(EACH_ENTRY.ELEMENT_TYPE_ID,
463: EACH_ENTRY.EFFECTIVE_START_DATE,
465: EACH_ENTRY.EFFECTIVE_END_DATE,
466: EACH_ENTRY.ASSIGNMENT_ID);
467: end loop;
468: --
469: hr_utility.set_location('closed_element_entry_check',2);
470: --
471: END closed_element_entry_check;
472: -------------------- END: closed_element_entry_check -----------------------
473: --
503: and c.contact_relationship_id = e.contact_relationship_id);
504: --
505: exception
506: when NO_DATA_FOUND then
507: hr_utility.set_message (801,'HR_6981_ALL_CONT_COBRA_EXISTS');
508: hr_utility.raise_error;
509: end;
510: --
511: END contact_cobra_validation;
504: --
505: exception
506: when NO_DATA_FOUND then
507: hr_utility.set_message (801,'HR_6981_ALL_CONT_COBRA_EXISTS');
508: hr_utility.raise_error;
509: end;
510: --
511: END contact_cobra_validation;
512: -------------------- END: pay_predel_validation ---------------------------
524: v_delete_permitted varchar2(1);
525: --
526: begin
527: --
528: hr_utility.set_location('contracts_check',10);
529: --
530: -- Check that no child records exist for the
531: -- person on per_contracts_f when
532: -- the person is deleted
539: where person_id = p_person_id);
540: --
541: exception
542: when NO_DATA_FOUND then
543: hr_utility.set_message(800,'PER_52851_PER_NO_DEL_CONTRACTS');
544: hr_utility.raise_error;
545: --
546: END contracts_check;
547:
540: --
541: exception
542: when NO_DATA_FOUND then
543: hr_utility.set_message(800,'PER_52851_PER_NO_DEL_CONTRACTS');
544: hr_utility.raise_error;
545: --
546: END contracts_check;
547:
548: --
572: --
573: BEGIN
574: --
575: --
576: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',1);
577: --
578: hr_person_delete.person_existance_check(P_PERSON_ID);
579: --
580: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',2);
576: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',1);
577: --
578: hr_person_delete.person_existance_check(P_PERSON_ID);
579: --
580: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',2);
581: --
582: hr_person_delete.product_installed('PAY', v_pay_status,
583: v_pay_installed, v_oracle_id);
584: --
581: --
582: hr_person_delete.product_installed('PAY', v_pay_status,
583: v_pay_installed, v_oracle_id);
584: --
585: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',4);
586: --
587: -- 4169275 start
588: -- During deletion of next change or future change need not require
589: -- this validation
592: hr_person_delete.aol_predel_validation(P_PERSON_ID);
593: end if;
594: -- 4169275 end
595: --
596: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',5);
597: --
598: hr_person_delete.assignment_set_check(P_PERSON_ID);
599: --
600: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',6);
596: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',5);
597: --
598: hr_person_delete.assignment_set_check(P_PERSON_ID);
599: --
600: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',6);
601: --
602: if (v_pay_installed = 'Y') then
603: hr_person_delete.pay_predel_validation(P_PERSON_ID);
604: end if;
608: -- and unrestricted Life events in progress
609: --
610: hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
611: --
612: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',8);
613: --
614: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
615: --
616: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
612: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',8);
613: --
614: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
615: --
616: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
617: --
618: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
619: --
620: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
616: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
617: --
618: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
619: --
620: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
621: --
622: hr_person_delete.contracts_check(P_PERSON_ID);
623: --
624: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',11);
620: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
621: --
622: hr_person_delete.contracts_check(P_PERSON_ID);
623: --
624: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',11);
625: --
626:
627: END weak_predel_validation;
628: -------------------- END: weak_predel_validation --------------------------
657: -- Bug 3524713 Ends Here
658: --
659: begin
660: --
661: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 1);
662: --
663: hr_person_delete.person_existance_check(P_PERSON_ID);
664: --
665: hr_person_delete.assignment_set_check(P_PERSON_ID);
678: hr_person_delete.pay_predel_validation(P_PERSON_ID);
679: --
680: hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
681: --
682: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 2);
683: --
684: -- VT 1403481 09/19/00
685: begin
686: -- bug fix 3732129.
726:
727:
728: exception
729: when NO_DATA_FOUND then
730: hr_utility.set_message (801,'HR_6325_ALL_PER_RL_NO_DEL');
731: hr_utility.raise_error;
732: end;
733: --
734: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 3);
727:
728: exception
729: when NO_DATA_FOUND then
730: hr_utility.set_message (801,'HR_6325_ALL_PER_RL_NO_DEL');
731: hr_utility.raise_error;
732: end;
733: --
734: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 3);
735: --
730: hr_utility.set_message (801,'HR_6325_ALL_PER_RL_NO_DEL');
731: hr_utility.raise_error;
732: end;
733: --
734: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 3);
735: --
736: begin
737: select 'Y'
738: into v_delete_permitted
743: where r.person_id = P_PERSON_ID
744: or r.contact_person_id = P_PERSON_ID);
745: exception
746: when NO_DATA_FOUND then
747: hr_utility.set_message (801,'HR_6326_ALL_PER_CR_NO_DEL');
748: hr_utility.raise_error;
749: end;
750: --
751: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 5);
744: or r.contact_person_id = P_PERSON_ID);
745: exception
746: when NO_DATA_FOUND then
747: hr_utility.set_message (801,'HR_6326_ALL_PER_CR_NO_DEL');
748: hr_utility.raise_error;
749: end;
750: --
751: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 5);
752: --
747: hr_utility.set_message (801,'HR_6326_ALL_PER_CR_NO_DEL');
748: hr_utility.raise_error;
749: end;
750: --
751: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 5);
752: --
753: begin
754: select 'Y'
755: into v_delete_permitted
759: from per_events e
760: where e.internal_contact_person_id = P_PERSON_ID);
761: exception
762: when NO_DATA_FOUND then
763: hr_utility.set_message (801,'HR_6328_ALL_PER_EVENT_NO_DEL');
764: hr_utility.raise_error;
765: end;
766: --
767: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 6);
760: where e.internal_contact_person_id = P_PERSON_ID);
761: exception
762: when NO_DATA_FOUND then
763: hr_utility.set_message (801,'HR_6328_ALL_PER_EVENT_NO_DEL');
764: hr_utility.raise_error;
765: end;
766: --
767: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 6);
768: --
763: hr_utility.set_message (801,'HR_6328_ALL_PER_EVENT_NO_DEL');
764: hr_utility.raise_error;
765: end;
766: --
767: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 6);
768: --
769: begin
770: select 'Y'
771: into v_delete_permitted
775: from per_bookings b
776: where b.person_id = P_PERSON_ID);
777: exception
778: when NO_DATA_FOUND then
779: hr_utility.set_message (801,'HR_6329_ALL_PER_BOOK_NO_DEL');
780: hr_utility.raise_error;
781: end;
782: --
783: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 7);
776: where b.person_id = P_PERSON_ID);
777: exception
778: when NO_DATA_FOUND then
779: hr_utility.set_message (801,'HR_6329_ALL_PER_BOOK_NO_DEL');
780: hr_utility.raise_error;
781: end;
782: --
783: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 7);
784: --
779: hr_utility.set_message (801,'HR_6329_ALL_PER_BOOK_NO_DEL');
780: hr_utility.raise_error;
781: end;
782: --
783: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 7);
784: --
785: -- VT 1403481 09/19/00
786: begin
787: select 'Y'
793: where a.person_id = P_PERSON_ID
794: and a.effective_start_date > P_SESSION_DATE);-- fix for bug 4238025
795: exception
796: when NO_DATA_FOUND then
797: hr_utility.set_message (801,'HR_6330_ALL_PER_ASSGT_NO_DEL');
798: hr_utility.raise_error;
799: end;
800: --
801: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 8);
794: and a.effective_start_date > P_SESSION_DATE);-- fix for bug 4238025
795: exception
796: when NO_DATA_FOUND then
797: hr_utility.set_message (801,'HR_6330_ALL_PER_ASSGT_NO_DEL');
798: hr_utility.raise_error;
799: end;
800: --
801: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 8);
802: --
797: hr_utility.set_message (801,'HR_6330_ALL_PER_ASSGT_NO_DEL');
798: hr_utility.raise_error;
799: end;
800: --
801: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 8);
802: --
803: begin
804: select 'Y'
805: into v_delete_permitted
810: where a.recruiter_id = P_PERSON_ID
811: or a.supervisor_id = P_PERSON_ID);
812: exception
813: when NO_DATA_FOUND then
814: hr_utility.set_message (801,'HR_6331_ALL_PER_RT_SUP_NO_DEL');
815: hr_utility.raise_error;
816: end;
817: --
818: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 9);
811: or a.supervisor_id = P_PERSON_ID);
812: exception
813: when NO_DATA_FOUND then
814: hr_utility.set_message (801,'HR_6331_ALL_PER_RT_SUP_NO_DEL');
815: hr_utility.raise_error;
816: end;
817: --
818: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 9);
819: --
814: hr_utility.set_message (801,'HR_6331_ALL_PER_RT_SUP_NO_DEL');
815: hr_utility.raise_error;
816: end;
817: --
818: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 9);
819: --
820: begin
821: select 'Y'
822: into v_delete_permitted
826: from per_periods_of_service p
827: where p.termination_accepted_person_id = P_PERSON_ID);
828: exception
829: when NO_DATA_FOUND then
830: hr_utility.set_message (801,'HR_6332_ALL_PER_TERM_NO_DEL');
831: hr_utility.raise_error;
832: end;
833: --
834: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 10);
827: where p.termination_accepted_person_id = P_PERSON_ID);
828: exception
829: when NO_DATA_FOUND then
830: hr_utility.set_message (801,'HR_6332_ALL_PER_TERM_NO_DEL');
831: hr_utility.raise_error;
832: end;
833: --
834: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 10);
835: --
830: hr_utility.set_message (801,'HR_6332_ALL_PER_TERM_NO_DEL');
831: hr_utility.raise_error;
832: end;
833: --
834: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 10);
835: --
836: begin
837: select 'Y'
838: into v_delete_permitted
842: from per_person_analyses a
843: where a.person_id = P_PERSON_ID);
844: exception
845: when NO_DATA_FOUND then
846: hr_utility.set_message (801,'HR_6334_ALL_PER_ANAL_NO_DEL');
847: hr_utility.raise_error;
848: end;
849: --
850: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 11);
843: where a.person_id = P_PERSON_ID);
844: exception
845: when NO_DATA_FOUND then
846: hr_utility.set_message (801,'HR_6334_ALL_PER_ANAL_NO_DEL');
847: hr_utility.raise_error;
848: end;
849: --
850: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 11);
851: --
846: hr_utility.set_message (801,'HR_6334_ALL_PER_ANAL_NO_DEL');
847: hr_utility.raise_error;
848: end;
849: --
850: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 11);
851: --
852: begin
853: select 'Y'
854: into v_delete_permitted
858: from per_absence_attendances a
859: where a.person_id = P_PERSON_ID);
860: exception
861: when NO_DATA_FOUND then
862: hr_utility.set_message (801,'HR_6335_ALL_PER_ABS_ATT_NO_DEL');
863: hr_utility.raise_error;
864: end;
865: --
866: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 12);
859: where a.person_id = P_PERSON_ID);
860: exception
861: when NO_DATA_FOUND then
862: hr_utility.set_message (801,'HR_6335_ALL_PER_ABS_ATT_NO_DEL');
863: hr_utility.raise_error;
864: end;
865: --
866: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 12);
867: --
862: hr_utility.set_message (801,'HR_6335_ALL_PER_ABS_ATT_NO_DEL');
863: hr_utility.raise_error;
864: end;
865: --
866: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 12);
867: --
868: begin
869: select 'Y'
870: into v_delete_permitted
875: where a.authorising_person_id = P_PERSON_ID
876: or a.replacement_person_id = P_PERSON_ID);
877: exception
878: when NO_DATA_FOUND then
879: hr_utility.set_message (801,'HR_6336_ALL_PER_AUTH_NO_DEL');
880: hr_utility.raise_error;
881: end;
882: --
883: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 13);
876: or a.replacement_person_id = P_PERSON_ID);
877: exception
878: when NO_DATA_FOUND then
879: hr_utility.set_message (801,'HR_6336_ALL_PER_AUTH_NO_DEL');
880: hr_utility.raise_error;
881: end;
882: --
883: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 13);
884: --
879: hr_utility.set_message (801,'HR_6336_ALL_PER_AUTH_NO_DEL');
880: hr_utility.raise_error;
881: end;
882: --
883: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 13);
884: --
885: begin
886: select 'Y'
887: into v_delete_permitted
892: where r.authorising_person_id = P_PERSON_ID
893: or r.internal_contact_person_id = P_PERSON_ID);
894: exception
895: when NO_DATA_FOUND then
896: hr_utility.set_message (801,'HR_6337_ALL_PER_REC_NO_DEL');
897: hr_utility.raise_error;
898: end;
899: --
900: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);
893: or r.internal_contact_person_id = P_PERSON_ID);
894: exception
895: when NO_DATA_FOUND then
896: hr_utility.set_message (801,'HR_6337_ALL_PER_REC_NO_DEL');
897: hr_utility.raise_error;
898: end;
899: --
900: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);
901: begin
896: hr_utility.set_message (801,'HR_6337_ALL_PER_REC_NO_DEL');
897: hr_utility.raise_error;
898: end;
899: --
900: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);
901: begin
902: select 'Y'
903: into v_delete_permitted
904: from sys.dual
912: fnd_message.set_name(801,'PER_52467_APR_PAR_REC_NO_DEL');
913: fnd_message.raise_error;
914: end;
915: --
916: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);
917: begin
918: select 'Y'
919: into v_delete_permitted
920: from sys.dual
927: fnd_message.set_name(801,'PER_52467_APR_PAR_REC_NO_DEL');
928: fnd_message.raise_error;
929: end;
930: --
931: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 14);
932: --
933: begin
934: select 'Y'
935: into v_delete_permitted
939: from per_requisitions r
940: where r.person_id = P_PERSON_ID);
941: exception
942: when NO_DATA_FOUND then
943: hr_utility.set_message (801,'HR_6338_ALL_PER_REQ_NO_DEL');
944: hr_utility.raise_error;
945: end;
946: --
947: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 15);
940: where r.person_id = P_PERSON_ID);
941: exception
942: when NO_DATA_FOUND then
943: hr_utility.set_message (801,'HR_6338_ALL_PER_REQ_NO_DEL');
944: hr_utility.raise_error;
945: end;
946: --
947: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 15);
948: --
943: hr_utility.set_message (801,'HR_6338_ALL_PER_REQ_NO_DEL');
944: hr_utility.raise_error;
945: end;
946: --
947: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 15);
948: --
949: begin
950: select 'Y'
951: into v_delete_permitted
955: from per_vacancies v
956: where v.recruiter_id = P_PERSON_ID);
957: exception
958: when NO_DATA_FOUND then
959: hr_utility.set_message (801,'HR_6339_ALL_PER_VAC_NO_DEL');
960: hr_utility.raise_error;
961: end;
962: --
963: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 16);
956: where v.recruiter_id = P_PERSON_ID);
957: exception
958: when NO_DATA_FOUND then
959: hr_utility.set_message (801,'HR_6339_ALL_PER_VAC_NO_DEL');
960: hr_utility.raise_error;
961: end;
962: --
963: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 16);
964: --
959: hr_utility.set_message (801,'HR_6339_ALL_PER_VAC_NO_DEL');
960: hr_utility.raise_error;
961: end;
962: --
963: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 16);
964: --
965: -- Any discretionary link element entries?
966: --
967: begin
978: and e.element_link_id = l.element_link_id
979: and l.standard_link_flag = 'N');
980: exception
981: when NO_DATA_FOUND then
982: hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
983: hr_utility.raise_error;
984: end;
985: --
986: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 17);
979: and l.standard_link_flag = 'N');
980: exception
981: when NO_DATA_FOUND then
982: hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
983: hr_utility.raise_error;
984: end;
985: --
986: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 17);
987: --
982: hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
983: hr_utility.raise_error;
984: end;
985: --
986: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 17);
987: --
988: -- Any entry adjustments, overrides etc.?
989: -- (We cannot capture manual enty of standard link entries)
990: --
1000: and a.assignment_id = e.assignment_id
1001: and e.entry_type <> 'E');
1002: exception
1003: when NO_DATA_FOUND then
1004: hr_utility.set_message (801,'HR_6375_ALL_PER_ENTRY_NO_DEL');
1005: hr_utility.raise_error;
1006: end;
1007: --
1008: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 171);
1001: and e.entry_type <> 'E');
1002: exception
1003: when NO_DATA_FOUND then
1004: hr_utility.set_message (801,'HR_6375_ALL_PER_ENTRY_NO_DEL');
1005: hr_utility.raise_error;
1006: end;
1007: --
1008: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 171);
1009: --
1004: hr_utility.set_message (801,'HR_6375_ALL_PER_ENTRY_NO_DEL');
1005: hr_utility.raise_error;
1006: end;
1007: --
1008: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 171);
1009: --
1010: -- Are the entries to be deleted in a closed period? If so cannot delete.
1011: --
1012: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
1010: -- Are the entries to be deleted in a closed period? If so cannot delete.
1011: --
1012: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
1013: --
1014: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 18);
1015: --
1016: begin
1017: select 'Y'
1018: into v_delete_permitted
1026: where a.person_id = P_PERSON_ID
1027: and a.assignment_id = i.assignment_id));
1028: exception
1029: when NO_DATA_FOUND then
1030: hr_utility.set_message (801,'HR_6341_ALL_PER_ASS_INFO_DEL');
1031: hr_utility.raise_error;
1032: end;
1033: --
1034: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 19);
1027: and a.assignment_id = i.assignment_id));
1028: exception
1029: when NO_DATA_FOUND then
1030: hr_utility.set_message (801,'HR_6341_ALL_PER_ASS_INFO_DEL');
1031: hr_utility.raise_error;
1032: end;
1033: --
1034: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 19);
1035: --
1030: hr_utility.set_message (801,'HR_6341_ALL_PER_ASS_INFO_DEL');
1031: hr_utility.raise_error;
1032: end;
1033: --
1034: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 19);
1035: --
1036: begin
1037: select 'Y'
1038: into v_delete_permitted
1047: and a.assignment_id = s.assignment_id));
1048: exception
1049: when NO_DATA_FOUND then
1050: ---changed the message number from 6340 to 7407 for bug 5405424
1051: hr_utility.set_message (801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
1052: hr_utility.raise_error;
1053: end;
1054: --
1055: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 20);
1048: exception
1049: when NO_DATA_FOUND then
1050: ---changed the message number from 6340 to 7407 for bug 5405424
1051: hr_utility.set_message (801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
1052: hr_utility.raise_error;
1053: end;
1054: --
1055: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 20);
1056: --
1051: hr_utility.set_message (801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
1052: hr_utility.raise_error;
1053: end;
1054: --
1055: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 20);
1056: --
1057: begin
1058: select 'Y'
1059: into v_delete_permitted
1067: where a.person_id = P_PERSON_ID
1068: and a.assignment_id = e.assignment_id));
1069: exception
1070: when NO_DATA_FOUND then
1071: hr_utility.set_message (801,'HR_6344_ALL_PER_INT_NO_DEL');
1072: hr_utility.raise_error;
1073: end;
1074: --
1075: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 21);
1068: and a.assignment_id = e.assignment_id));
1069: exception
1070: when NO_DATA_FOUND then
1071: hr_utility.set_message (801,'HR_6344_ALL_PER_INT_NO_DEL');
1072: hr_utility.raise_error;
1073: end;
1074: --
1075: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 21);
1076: --
1071: hr_utility.set_message (801,'HR_6344_ALL_PER_INT_NO_DEL');
1072: hr_utility.raise_error;
1073: end;
1074: --
1075: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 21);
1076: --
1077: begin
1078: select 'Y'
1079: into v_delete_permitted
1087: where a.person_id = P_PERSON_ID
1088: and a.assignment_id = p.assignment_id));
1089: exception
1090: when NO_DATA_FOUND then
1091: hr_utility.set_message (801,'HR_6374_ALL_PER_SPINE_NO_DEL');
1092: hr_utility.raise_error;
1093: end;
1094: --
1095: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 22);
1088: and a.assignment_id = p.assignment_id));
1089: exception
1090: when NO_DATA_FOUND then
1091: hr_utility.set_message (801,'HR_6374_ALL_PER_SPINE_NO_DEL');
1092: hr_utility.raise_error;
1093: end;
1094: --
1095: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 22);
1096: --
1091: hr_utility.set_message (801,'HR_6374_ALL_PER_SPINE_NO_DEL');
1092: hr_utility.raise_error;
1093: end;
1094: --
1095: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 22);
1096: --
1097: begin
1098: select 'Y'
1099: into v_delete_permitted
1107: where a.person_id = P_PERSON_ID
1108: and a.assignment_id = t.assignment_id));
1109: exception
1110: when NO_DATA_FOUND then
1111: hr_utility.set_message (801,'HR_6379_ALL_PER_QP_NO_DEL');
1112: hr_utility.raise_error;
1113: end;
1114: --
1115: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 26);
1108: and a.assignment_id = t.assignment_id));
1109: exception
1110: when NO_DATA_FOUND then
1111: hr_utility.set_message (801,'HR_6379_ALL_PER_QP_NO_DEL');
1112: hr_utility.raise_error;
1113: end;
1114: --
1115: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 26);
1116: --
1111: hr_utility.set_message (801,'HR_6379_ALL_PER_QP_NO_DEL');
1112: hr_utility.raise_error;
1113: end;
1114: --
1115: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 26);
1116: --
1117: begin
1118: select 'Y'
1119: into v_delete_permitted
1127: where a.person_id = P_PERSON_ID
1128: and a.assignment_id = c.assignment_id));
1129: exception
1130: when NO_DATA_FOUND then
1131: hr_utility.set_message (801,'HR_6476_ALL_PER_COB_NO_DEL');
1132: hr_utility.raise_error;
1133: end;
1134: --
1135: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 27);
1128: and a.assignment_id = c.assignment_id));
1129: exception
1130: when NO_DATA_FOUND then
1131: hr_utility.set_message (801,'HR_6476_ALL_PER_COB_NO_DEL');
1132: hr_utility.raise_error;
1133: end;
1134: --
1135: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 27);
1136: --
1131: hr_utility.set_message (801,'HR_6476_ALL_PER_COB_NO_DEL');
1132: hr_utility.raise_error;
1133: end;
1134: --
1135: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 27);
1136: --
1137: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
1138: --
1139: hr_person_delete.contracts_check(P_PERSON_ID);
1137: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
1138: --
1139: hr_person_delete.contracts_check(P_PERSON_ID);
1140: --
1141: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 28);
1142: -- Validation for BEN
1143: --
1144: -- Bug 3524713 Starts Here
1145: OPEN ben_ext_chg_log (
1166:
1167: --
1168: -- validation for PA
1169: --
1170: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 29);
1171: --
1172: pa_person.pa_predel_validation(P_PERSON_ID);
1173: --
1174: -- validation for WIP
1172: pa_person.pa_predel_validation(P_PERSON_ID);
1173: --
1174: -- validation for WIP
1175: --
1176: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 30);
1177: --
1178: wip_person.wip_predel_validation(P_PERSON_ID);
1179: --
1180: -- validation for ENG
1178: wip_person.wip_predel_validation(P_PERSON_ID);
1179: --
1180: -- validation for ENG
1181: --
1182: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 31);
1183: --
1184: eng_person.eng_predel_validation(P_PERSON_ID);
1185: --
1186: -- validation for AP
1184: eng_person.eng_predel_validation(P_PERSON_ID);
1185: --
1186: -- validation for AP
1187: --
1188: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 32);
1189: --
1190: ap_person.ap_predel_validation(P_PERSON_ID);
1191: --
1192: -- validation for FA
1190: ap_person.ap_predel_validation(P_PERSON_ID);
1191: --
1192: -- validation for FA
1193: --
1194: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 33);
1195: --
1196: fa_person.fa_predel_validation(P_PERSON_ID);
1197: --
1198: -- validation for PO
1196: fa_person.fa_predel_validation(P_PERSON_ID);
1197: --
1198: -- validation for PO
1199: --
1200: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 34);
1201: --
1202: po_person.po_predel_validation(P_PERSON_ID);
1203: --
1204: -- validation for RCV
1202: po_person.po_predel_validation(P_PERSON_ID);
1203: --
1204: -- validation for RCV
1205: --
1206: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 35);
1207: --
1208: rcv_person.rcv_predel_validation(P_PERSON_ID);
1209: --
1210: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 36);
1206: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 35);
1207: --
1208: rcv_person.rcv_predel_validation(P_PERSON_ID);
1209: --
1210: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 36);
1211: --
1212: --
1213: end moderate_predel_validation;
1214: -------------------- END: moderate_predel_validation ---------------------
1245: v_person_types number;
1246: --
1247: BEGIN
1248: --
1249: hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1250: --
1251: -- If >1 system person types then non default amendments have been made.
1252: -- If v_person_types = 0 then only 1 system person type else > 1.
1253: --
1269: and ppt2.system_person_type <> ppt.system_person_type
1270: );
1271: --
1272: if v_person_types > 0 then
1273: hr_utility.set_message (801,'HR_6324_ALL_PER_ADD_NO_DEL');
1274: hr_utility.raise_error;
1275: end if;
1276: --
1277: hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1270: );
1271: --
1272: if v_person_types > 0 then
1273: hr_utility.set_message (801,'HR_6324_ALL_PER_ADD_NO_DEL');
1274: hr_utility.raise_error;
1275: end if;
1276: --
1277: hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1278: --
1273: hr_utility.set_message (801,'HR_6324_ALL_PER_ADD_NO_DEL');
1274: hr_utility.raise_error;
1275: end if;
1276: --
1277: hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1278: --
1279: -- fix for bug 4508139
1280: if p_dt_delete_mode = 'ZAP' then
1281: hr_person_delete.moderate_predel_validation(p_person_id => p_person_id
1316: v_delete_contact varchar2(1);
1317: --
1318: BEGIN
1319: --
1320: hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 1);
1321: --
1322: hr_person_delete.person_existance_check(P_CONTACT_PERSON_ID);
1323: --
1324: begin
1337: if SQL%ROWCOUNT > 0 then
1338: return;
1339: end if;
1340: --
1341: hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 2);
1342: --
1343: begin
1344: select 'Y'
1345: into v_other_only
1363: v_delete_contact := 'Y';
1364: hr_person_delete.strong_predel_validation(P_CONTACT_PERSON_ID,
1365: P_SESSION_DATE);
1366: exception
1367: when hr_utility.hr_error then
1368: v_delete_contact := 'N';
1369: end;
1370: --
1371: if v_delete_contact = 'Y' then
1496: if P_FORM_CALL = FALSE then
1497: hr_person_delete.person_existance_check(P_PERSON_ID);
1498: end if;
1499: --
1500: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 0);
1501: --
1502: -- Lock person rows, delete at end of procedure.
1503: --
1504: open LOCK_PERSON_ROWS;
1504: open LOCK_PERSON_ROWS;
1505: --
1506: -- Now start cascade.
1507: --
1508: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1);
1509: --
1510: -- bug fix 3732129 starts here.
1511: -- fetching the assignment ids into a pl/sql table.
1512:
1515: close c_asg;
1516:
1517: -- bug fix 3732129 ends here.
1518:
1519: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 888);
1520:
1521: begin
1522: update per_requisitions r
1523: set r.person_id = null
1523: set r.person_id = null
1524: where r.person_id = P_PERSON_ID;
1525: exception
1526: when NO_DATA_FOUND then
1527: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 101);
1528: end;
1529: --
1530: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2);
1531: --
1526: when NO_DATA_FOUND then
1527: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 101);
1528: end;
1529: --
1530: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2);
1531: --
1532: begin
1533: -- bug fix 3732129.
1534: -- Delete statement modified to improve performance.
1549: where a.person_id = P_PERSON_ID
1550: and a.assignment_id = l.assignment_id);*/
1551: exception
1552: when NO_DATA_FOUND then
1553: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 201);
1554: end;
1555: --
1556: -- Leave per_letter_requests for the moment - may not be necessary to
1557: -- delete the parent with no children which requires some work with
1556: -- Leave per_letter_requests for the moment - may not be necessary to
1557: -- delete the parent with no children which requires some work with
1558: -- cursors.
1559: --
1560: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3);
1561: --
1562: begin
1563: delete from per_absence_attendances a
1564: where a.person_id = P_PERSON_ID;
1563: delete from per_absence_attendances a
1564: where a.person_id = P_PERSON_ID;
1565: exception
1566: when NO_DATA_FOUND then
1567: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 301);
1568: end;
1569: --
1570: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4);
1571: --
1566: when NO_DATA_FOUND then
1567: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 301);
1568: end;
1569: --
1570: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4);
1571: --
1572: begin
1573: update per_absence_attendances a
1574: set a.authorising_person_id = null
1574: set a.authorising_person_id = null
1575: where a.authorising_person_id = P_PERSON_ID;
1576: exception
1577: when NO_DATA_FOUND then
1578: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 401);
1579: end;
1580: --
1581: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 5);
1582: --
1577: when NO_DATA_FOUND then
1578: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 401);
1579: end;
1580: --
1581: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 5);
1582: --
1583: begin
1584: update per_absence_attendances a
1585: set a.replacement_person_id = null
1585: set a.replacement_person_id = null
1586: where a.replacement_person_id = P_PERSON_ID;
1587: exception
1588: when NO_DATA_FOUND then
1589: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 501);
1590: end;
1591: --
1592: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 6);
1593: --
1588: when NO_DATA_FOUND then
1589: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 501);
1590: end;
1591: --
1592: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 6);
1593: --
1594: begin
1595: delete from per_person_analyses a
1596: where a.person_id = P_PERSON_ID;
1595: delete from per_person_analyses a
1596: where a.person_id = P_PERSON_ID;
1597: exception
1598: when NO_DATA_FOUND then
1599: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 601);
1600: end;
1601: --
1602: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 8);
1603: --
1598: when NO_DATA_FOUND then
1599: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 601);
1600: end;
1601: --
1602: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 8);
1603: --
1604: -- Delete of per_periods_of_service at end after delete of
1605: -- per_assignments_f.
1606: --
1609: set p.termination_accepted_person_id = null
1610: where p.termination_accepted_person_id = P_PERSON_ID;
1611: exception
1612: when NO_DATA_FOUND then
1613: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 801);
1614: end;
1615: --
1616: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 9);
1617: --
1612: when NO_DATA_FOUND then
1613: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 801);
1614: end;
1615: --
1616: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 9);
1617: --
1618: begin
1619: update per_recruitment_activities r
1620: set r.authorising_person_id = null
1620: set r.authorising_person_id = null
1621: where r.authorising_person_id = P_PERSON_ID;
1622: exception
1623: when NO_DATA_FOUND then
1624: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 901);
1625: end;
1626: --
1627: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 10);
1628: --
1623: when NO_DATA_FOUND then
1624: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 901);
1625: end;
1626: --
1627: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 10);
1628: --
1629: begin
1630: update per_recruitment_activities r
1631: set r.internal_contact_person_id = null
1631: set r.internal_contact_person_id = null
1632: where r.internal_contact_person_id = P_PERSON_ID;
1633: exception
1634: when NO_DATA_FOUND then
1635: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1001);
1636: end;
1637: --
1638: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',11);
1639: --
1634: when NO_DATA_FOUND then
1635: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1001);
1636: end;
1637: --
1638: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',11);
1639: --
1640: -- Bug 4873360 fix for performance repository sql id 14959971.
1641: -- Rewrote the delete query commented out below (and already once tuned for
1642: -- bug 3619599) to avoid a merge join cartesian and a full table scan on
1668: and qsa.type_object_id = par.participant_id
1669: and qsa.type = 'PARTICIPANT'
1670: and par.person_id = P_PERSON_ID);
1671: end if;
1672: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 215); --added for bug 5464252
1673: /*Start of bug 5464252*/
1674: exception
1675: when NO_DATA_FOUND then
1676: v_dummy := null;
1673: /*Start of bug 5464252*/
1674: exception
1675: when NO_DATA_FOUND then
1676: v_dummy := null;
1677: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 211);
1678: /*End of bug 5464252*/
1679: end; -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
1680:
1681: begin -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
1706: /* start of bug 5464252 */
1707: exception
1708: when NO_DATA_FOUND then
1709: v_dummy := null;
1710: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 220);
1711: /* end of bug 5464252 */
1712: end; -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
1713: end; -- Delete from HR_QUEST_ANSWER_VALUES
1714: -- original sql.
1731: and par.person_id = P_PERSON_ID)
1732: ); -- Fix 3619599
1733: exception
1734: when NO_DATA_FOUND then
1735: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1101);
1736: end;
1737: */
1738: -- Now delete from HR_QUEST_ANSWERS
1739: begin
1752: and par.person_id = P_PERSON_ID)
1753: ); -- Fix 3619599
1754: exception
1755: when NO_DATA_FOUND then
1756: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1102);
1757: end;
1758: --
1759: -- Now delete from per_participants
1760: -- SQL Fixed for Performance
1779: )
1780: );
1781: exception
1782: when NO_DATA_FOUND then
1783: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1103);
1784: end;
1785: --
1786: -- Now delete from per_appraisals
1787: --
1790: where apr.appraiser_person_id = P_PERSON_ID
1791: or apr.appraisee_person_id = P_PERSON_ID;
1792: exception
1793: when NO_DATA_FOUND then
1794: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1104);
1795: end;
1796: --
1797: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 12);
1798: --
1793: when NO_DATA_FOUND then
1794: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1104);
1795: end;
1796: --
1797: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 12);
1798: --
1799: hr_security.delete_per_from_list(P_PERSON_ID);
1800: --
1801: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 14);
1797: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 12);
1798: --
1799: hr_security.delete_per_from_list(P_PERSON_ID);
1800: --
1801: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 14);
1802: --
1803: begin
1804: update per_vacancies v
1805: set v.recruiter_id = null
1805: set v.recruiter_id = null
1806: where v.recruiter_id = P_PERSON_ID;
1807: exception
1808: when NO_DATA_FOUND then
1809: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1401);
1810: end;
1811: --
1812: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 15);
1813: --
1808: when NO_DATA_FOUND then
1809: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1401);
1810: end;
1811: --
1812: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 15);
1813: --
1814: begin
1815: update per_assignments_f ass
1816: set ass.person_referred_by_id = null
1816: set ass.person_referred_by_id = null
1817: where ass.person_referred_by_id = P_PERSON_ID;
1818: exception
1819: when NO_DATA_FOUND then
1820: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1501);
1821: end;
1822: --
1823: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 16);
1824: --
1819: when NO_DATA_FOUND then
1820: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1501);
1821: end;
1822: --
1823: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 16);
1824: --
1825: begin
1826: update per_assignments_f a
1827: set a.recruiter_id = null
1827: set a.recruiter_id = null
1828: where a.recruiter_id = P_PERSON_ID;
1829: exception
1830: when NO_DATA_FOUND then
1831: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1601);
1832: end;
1833: --
1834: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 17);
1835: --
1830: when NO_DATA_FOUND then
1831: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1601);
1832: end;
1833: --
1834: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 17);
1835: --
1836: begin
1837: update per_assignments_f a
1838: set a.supervisor_id = null
1838: set a.supervisor_id = null
1839: where a.supervisor_id = P_PERSON_ID;
1840: exception
1841: when NO_DATA_FOUND then
1842: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1701);
1843: end;
1844: --
1845: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 18);
1846: --
1841: when NO_DATA_FOUND then
1842: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1701);
1843: end;
1844: --
1845: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 18);
1846: --
1847: -- LOCK ASSIGNMENTS NOW: have to use cursor as cannot return >1 row for
1848: -- 'into' part of PL/SQL.
1849: --
1848: -- 'into' part of PL/SQL.
1849: --
1850: open LOCK_ASSIGNMENT_ROWS;
1851: --
1852: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 185);
1853: --
1854: begin
1855: --
1856: -- Bug 349818. Delete from per_pay_proposal_components before
1876: where ass.assignment_id = p.assignment_id
1877: and ass.person_id = P_PERSON_ID);
1878: exception
1879: when NO_DATA_FOUND then
1880: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1801);
1881: end;
1882: --
1883: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 19);
1884: --
1879: when NO_DATA_FOUND then
1880: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1801);
1881: end;
1882: --
1883: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 19);
1884: --
1885: begin
1886: delete from pay_personal_payment_methods_f m
1887: where m.assignment_id in (
1889: from per_assignments_f ass
1890: where ass.person_id = P_PERSON_ID);
1891: exception
1892: when NO_DATA_FOUND then
1893: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1901);
1894: end;
1895: --
1896: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 20);
1897: --
1892: when NO_DATA_FOUND then
1893: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1901);
1894: end;
1895: --
1896: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 20);
1897: --
1898: begin
1899: delete from per_assignment_budget_values_f a
1900: where a.assignment_id in (
1902: from per_assignments_f ass
1903: where ass.person_id = P_PERSON_ID);
1904: exception
1905: when NO_DATA_FOUND then
1906: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2001);
1907: end;
1908: --
1909: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 21);
1910: --
1905: when NO_DATA_FOUND then
1906: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2001);
1907: end;
1908: --
1909: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 21);
1910: --
1911: begin
1912: delete from per_assignment_extra_info a
1913: where a.assignment_id in (
1915: from per_assignments_f ass
1916: where ass.person_id = P_PERSON_ID);
1917: exception
1918: when NO_DATA_FOUND then
1919: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2101);
1920: end;
1921: --
1922: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 22);
1923: --
1918: when NO_DATA_FOUND then
1919: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2101);
1920: end;
1921: --
1922: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 22);
1923: --
1924: begin
1925: delete from per_secondary_ass_statuses a
1926: where a.assignment_id in (
1928: from per_assignments_f ass
1929: where ass.person_id = P_PERSON_ID);
1930: exception
1931: when NO_DATA_FOUND then
1932: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2201);
1933: end;
1934: --
1935: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23);
1936: --
1931: when NO_DATA_FOUND then
1932: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2201);
1933: end;
1934: --
1935: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23);
1936: --
1937: -- Delete COBRA references and then any contact relationships. COBRA
1938: -- must be deleted first as PER_COBRA_COV_ENROLLMENTS has a
1939: -- contact_relationship_id which may be constrained later.
1950: and ass.person_id = P_PERSON_ID)
1951: );
1952: exception
1953: when NO_DATA_FOUND then
1954: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2301);
1955: end;
1956: --
1957: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 230);
1958: --
1953: when NO_DATA_FOUND then
1954: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2301);
1955: end;
1956: --
1957: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 230);
1958: --
1959: begin
1960: delete from per_cobra_coverage_benefits c2
1961: where c2.cobra_coverage_enrollment_id in (
1971: and ass.person_id = r.person_id)
1972: );
1973: exception
1974: when NO_DATA_FOUND then
1975: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23001);
1976: end;
1977: --
1978: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 231);
1979: --
1974: when NO_DATA_FOUND then
1975: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23001);
1976: end;
1977: --
1978: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 231);
1979: --
1980: begin
1981: delete from per_cobra_coverage_statuses c2
1982: where c2.cobra_coverage_enrollment_id in (
1989: and ass.person_id = P_PERSON_ID)
1990: );
1991: exception
1992: when NO_DATA_FOUND then
1993: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23101);
1994: end;
1995: --
1996: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 232);
1997: --
1992: when NO_DATA_FOUND then
1993: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23101);
1994: end;
1995: --
1996: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 232);
1997: --
1998: begin
1999: delete from per_cobra_coverage_statuses c2
2000: where c2.cobra_coverage_enrollment_id in (
2010: and ass.person_id = r.person_id)
2011: );
2012: exception
2013: when NO_DATA_FOUND then
2014: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23201);
2015: end;
2016: --
2017: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 233);
2018: --
2013: when NO_DATA_FOUND then
2014: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23201);
2015: end;
2016: --
2017: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 233);
2018: --
2019: begin
2020: delete from per_sched_cobra_payments c2
2021: where c2.cobra_coverage_enrollment_id in (
2028: and ass.person_id = P_PERSON_ID)
2029: );
2030: exception
2031: when NO_DATA_FOUND then
2032: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23301);
2033: end;
2034: --
2035: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 234);
2036: --
2031: when NO_DATA_FOUND then
2032: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23301);
2033: end;
2034: --
2035: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 234);
2036: --
2037: begin
2038: delete from per_sched_cobra_payments c2
2039: where c2.cobra_coverage_enrollment_id in (
2049: and ass.person_id = r.person_id)
2050: );
2051: exception
2052: when NO_DATA_FOUND then
2053: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23401);
2054: end;
2055: --
2056: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 235);
2057: --
2052: when NO_DATA_FOUND then
2053: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23401);
2054: end;
2055: --
2056: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 235);
2057: --
2058: begin
2059: delete from per_cobra_cov_enrollments c
2060: where c.assignment_id in (
2062: from per_assignments_f ass
2063: where ass.person_id = P_PERSON_ID);
2064: exception
2065: when NO_DATA_FOUND then
2066: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23501);
2067: end;
2068: --
2069: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 236);
2070: --
2065: when NO_DATA_FOUND then
2066: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23501);
2067: end;
2068: --
2069: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 236);
2070: --
2071: begin
2072: delete from per_cobra_cov_enrollments c
2073: where exists
2082: and ass.person_id = r.person_id)
2083: );
2084: exception
2085: when NO_DATA_FOUND then
2086: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23601);
2087: end;
2088: --
2089: --Bug# 3026024 Start Here
2090: --Description : Delete the entry in the table ben_covered_dependents_f for the
2090: --Description : Delete the entry in the table ben_covered_dependents_f for the
2091: -- contact person whom is getting deleted.
2092: --
2093: --
2094: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 237);
2095: --
2096: begin
2097: delete from ben_covered_dependents_f c
2098: where c.contact_relationship_id in (
2101: where r.contact_person_id = p_person_id
2102: );
2103: exception
2104: when NO_DATA_FOUND then
2105: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);
2106: end;
2107: --
2108: --Bug# 3026024 End Here
2109: --
2117: -- NB If b is created as a contact of b then 2 contact relationships are
2118: -- are created: a,b and b,a so that they can be queried in either
2119: -- direction. Hence must delete both here.
2120: --
2121: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 237);
2122: --
2123: begin
2124: select count(*)
2125: into v_dummy
2143: end if;
2144: --
2145: exception
2146: when NO_DATA_FOUND then
2147: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);
2148: end;
2149: --
2150: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 24);
2151: --
2146: when NO_DATA_FOUND then
2147: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);
2148: end;
2149: --
2150: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 24);
2151: --
2152: begin
2153: delete from per_contact_relationships r
2154: where r.contact_person_id = P_PERSON_ID;
2153: delete from per_contact_relationships r
2154: where r.contact_person_id = P_PERSON_ID;
2155: exception
2156: when NO_DATA_FOUND then
2157: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2401);
2158: end;
2159: --
2160: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25);
2161: --
2156: when NO_DATA_FOUND then
2157: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2401);
2158: end;
2159: --
2160: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25);
2161: --
2162: begin
2163: delete from per_addresses a
2164: where a.person_id = P_PERSON_ID;
2163: delete from per_addresses a
2164: where a.person_id = P_PERSON_ID;
2165: exception
2166: when NO_DATA_FOUND then
2167: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2501);
2168: end;
2169: --
2170: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25.5);
2171: --
2166: when NO_DATA_FOUND then
2167: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2501);
2168: end;
2169: --
2170: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25.5);
2171: --
2172: begin
2173: delete from per_phones a
2174: where a.parent_id = P_PERSON_ID
2174: where a.parent_id = P_PERSON_ID
2175: and a.parent_table = 'PER_ALL_PEOPLE_F';
2176: exception
2177: when NO_DATA_FOUND then
2178: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25501);
2179: end;
2180: --
2181: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26);
2182: --
2177: when NO_DATA_FOUND then
2178: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25501);
2179: end;
2180: --
2181: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26);
2182: --
2183: -- we must do this delete in dynamic sql because the per_performance_reviews
2184: -- table will not exist if the database has not been upgraded to new salary admin
2185: -- (introduced April 1998). The procedure would not compile if this was not dynamic.
2199: raise;
2200: end if;
2201: end;
2202: --
2203: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 261);
2204: --
2205: -- About to delete interview events for assignments. However, must
2206: -- first delete bookings (interviewers) for those events.
2207: --
2227: and ass.person_id = P_PERSON_ID)
2228: );*/
2229: exception
2230: when NO_DATA_FOUND then
2231: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26101);
2232: end;
2233: --
2234: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 262);
2235: --
2230: when NO_DATA_FOUND then
2231: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26101);
2232: end;
2233: --
2234: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 262);
2235: --
2236: begin
2237: -- bug fix 3732129.
2238: -- Delete statement modified to improve performance.
2247: from per_assignments_f ass
2248: where ass.person_id = P_PERSON_ID);*/
2249: exception
2250: when NO_DATA_FOUND then
2251: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26201);
2252: end;
2253: --
2254: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 27);
2255: --
2250: when NO_DATA_FOUND then
2251: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26201);
2252: end;
2253: --
2254: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 27);
2255: --
2256: begin
2257: update per_events e
2258: set e.internal_contact_person_id = null
2258: set e.internal_contact_person_id = null
2259: where e.internal_contact_person_id = P_PERSON_ID;
2260: exception
2261: when NO_DATA_FOUND then
2262: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2701);
2263: end;
2264: --
2265: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 28);
2266: --
2261: when NO_DATA_FOUND then
2262: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2701);
2263: end;
2264: --
2265: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 28);
2266: --
2267: begin
2268: delete from per_bookings b
2269: where b.person_id = P_PERSON_ID;
2268: delete from per_bookings b
2269: where b.person_id = P_PERSON_ID;
2270: exception
2271: when NO_DATA_FOUND then
2272: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2801);
2273: end;
2274: --
2275: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 29);
2276: --
2271: when NO_DATA_FOUND then
2272: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2801);
2273: end;
2274: --
2275: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 29);
2276: --
2277: begin
2278: delete from per_quickpaint_result_text q
2279: where q.assignment_id in (
2281: from per_assignments_f ass
2282: where ass.person_id = P_PERSON_ID);
2283: exception
2284: when NO_DATA_FOUND then
2285: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2901);
2286: end;
2287: --
2288: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 30);
2289: --
2284: when NO_DATA_FOUND then
2285: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2901);
2286: end;
2287: --
2288: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 30);
2289: --
2290: -- Validation has already been performed against
2291: -- hr_assignment_set_amendments in weak_predel_validation.
2292: --
2297: from per_assignments_f ass
2298: where ass.person_id = P_PERSON_ID);
2299: exception
2300: when NO_DATA_FOUND then
2301: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3001);
2302: end;
2303: --
2304: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 31);
2305: --
2300: when NO_DATA_FOUND then
2301: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3001);
2302: end;
2303: --
2304: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 31);
2305: --
2306: begin
2307: delete from pay_cost_allocations_f a
2308: where a.assignment_id in (
2310: from per_assignments_f ass
2311: where ass.person_id = P_PERSON_ID);
2312: exception
2313: when NO_DATA_FOUND then
2314: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3101);
2315: end;
2316: --
2317: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 32);
2318: --
2313: when NO_DATA_FOUND then
2314: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3101);
2315: end;
2316: --
2317: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 32);
2318: --
2319: begin
2320: delete from per_spinal_point_placements_f p
2321: where p.assignment_id in (
2323: from per_assignments_f ass
2324: where ass.person_id = P_PERSON_ID);
2325: exception
2326: when NO_DATA_FOUND then
2327: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3201);
2328: end;
2329: --
2330: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 33);
2331: --
2326: when NO_DATA_FOUND then
2327: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3201);
2328: end;
2329: --
2330: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 33);
2331: --
2332: -- Validation has already been performed against
2333: -- pay_assignment_actions in weak_predel_validation.
2334: --
2340: where ass.person_id = P_PERSON_ID
2341: and ass.assignment_id = a.assignment_id);
2342: exception
2343: when NO_DATA_FOUND then
2344: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3301);
2345: end;
2346: --
2347: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 34);
2348: --
2343: when NO_DATA_FOUND then
2344: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3301);
2345: end;
2346: --
2347: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 34);
2348: --
2349: begin
2350: delete from pay_assignment_latest_balances b
2351: where b.assignment_id in (
2353: from per_assignments_f ass
2354: where ass.person_id = P_PERSON_ID);
2355: exception
2356: when NO_DATA_FOUND then
2357: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3401);
2358: end;
2359: --
2360: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 35);
2361: --
2356: when NO_DATA_FOUND then
2357: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3401);
2358: end;
2359: --
2360: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 35);
2361: --
2362: begin
2363: -- bug fix 3732129
2364: -- Delete statement modified to improve performance.
2374: from per_assignments_f ass
2375: where ass.person_id = P_PERSON_ID); */
2376: exception
2377: when NO_DATA_FOUND then
2378: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3501);
2379: end;
2380: --
2381: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 36);
2382: --
2377: when NO_DATA_FOUND then
2378: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3501);
2379: end;
2380: --
2381: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 36);
2382: --
2383: begin
2384: delete from pay_element_entry_values_f v
2385: where v.element_entry_id in (
2392: and ass.person_id = P_PERSON_ID)
2393: );
2394: exception
2395: when NO_DATA_FOUND then
2396: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3601);
2397: end;
2398: --
2399: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 37);
2400: --
2395: when NO_DATA_FOUND then
2396: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3601);
2397: end;
2398: --
2399: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 37);
2400: --
2401: begin
2402: delete from pay_run_results r
2403: where r.source_type = 'E'
2411: and ass.person_id = P_PERSON_ID)
2412: );
2413: exception
2414: when NO_DATA_FOUND then
2415: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3701);
2416: end;
2417: --
2418: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 38);
2419: --
2414: when NO_DATA_FOUND then
2415: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3701);
2416: end;
2417: --
2418: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 38);
2419: --
2420: begin
2421: delete from pay_element_entries_f e
2422: where e.assignment_id in (
2424: from per_assignments_f ass
2425: where ass.person_id = P_PERSON_ID);
2426: exception
2427: when NO_DATA_FOUND then
2428: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3801);
2429: end;
2430: --
2431: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 39);
2432: --
2427: when NO_DATA_FOUND then
2428: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3801);
2429: end;
2430: --
2431: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 39);
2432: --
2433: -- Rmonge Bug 1686922 22-FEB-2002
2434: -- Tax records were not being deleted. Therefore, there were orphans rows in
2435: -- the pay_us_fed_tax_rules_f, pay_us_state_tax_rules_f,
2443: from per_assignments_f ass
2444: where ass.person_id = p_person_id );
2445: exception
2446: when no_data_found then
2447: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3802);
2448: end;
2449:
2450: begin
2451:
2448: end;
2449:
2450: begin
2451:
2452: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 40);
2453:
2454: Delete pay_us_emp_state_tax_rules_f pest
2455: Where pest.assignment_id in (
2456: select ass.assignment_id
2457: from per_assignments_f ass
2458: where ass.person_id = p_person_id );
2459: exception
2460: when no_data_found then
2461: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3803);
2462: end;
2463:
2464: begin
2465:
2462: end;
2463:
2464: begin
2465:
2466: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 41);
2467:
2468: Delete pay_us_emp_county_tax_rules_f pect
2469: Where pect.assignment_id in (
2470: select ass.assignment_id
2471: from per_assignments_f ass
2472: where ass.person_id = p_person_id );
2473: exception
2474: when no_data_found then
2475: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3804);
2476: end;
2477:
2478: begin
2479:
2476: end;
2477:
2478: begin
2479:
2480: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 42);
2481:
2482: Delete pay_us_emp_city_tax_rules_f pecit
2483: Where pecit.assignment_id in (
2484: select ass.assignment_id
2485: from per_assignments_f ass
2486: where ass.person_id = p_person_id );
2487: exception
2488: when no_data_found then
2489: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3805);
2490: end;
2491:
2492: -- Finished, now unlock assignments and delete them.
2493: --
2492: -- Finished, now unlock assignments and delete them.
2493: --
2494: close LOCK_ASSIGNMENT_ROWS;
2495: --
2496: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 43);
2497: --
2498: begin
2499: delete from per_all_assignments_f a
2500: where a.person_id = P_PERSON_ID;
2499: delete from per_all_assignments_f a
2500: where a.person_id = P_PERSON_ID;
2501: exception
2502: when NO_DATA_FOUND then
2503: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4001);
2504: end;
2505: --
2506: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 44);
2507: --
2502: when NO_DATA_FOUND then
2503: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4001);
2504: end;
2505: --
2506: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 44);
2507: --
2508: begin
2509: delete from per_periods_of_service p
2510: where p.person_id = P_PERSON_ID;
2509: delete from per_periods_of_service p
2510: where p.person_id = P_PERSON_ID;
2511: exception
2512: when NO_DATA_FOUND then
2513: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4101);
2514: end;
2515: --
2516: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 45);
2517: --
2512: when NO_DATA_FOUND then
2513: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4101);
2514: end;
2515: --
2516: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 45);
2517: --
2518: begin
2519: delete from per_applications a
2520: where a.person_id = P_PERSON_ID;
2519: delete from per_applications a
2520: where a.person_id = P_PERSON_ID;
2521: exception
2522: when NO_DATA_FOUND then
2523: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4201);
2524: end;
2525: --
2526: -- 03/18/98 Bug #642566
2527: -- delete per_people_extra_info records
2524: end;
2525: --
2526: -- 03/18/98 Bug #642566
2527: -- delete per_people_extra_info records
2528: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 46);
2529: --
2530: begin
2531: delete from per_people_extra_info e
2532: where e.person_id = P_PERSON_ID;
2531: delete from per_people_extra_info e
2532: where e.person_id = P_PERSON_ID;
2533: exception
2534: when NO_DATA_FOUND then
2535: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4301);
2536: end;
2537: -- 03/18/98 Change Ends
2538: --
2539: -- 03/18/98 Change Ends
2539: -- 03/18/98 Change Ends
2540: --
2541: -- 28/5/98
2542: -- Add delete from per_person_type_usages_f
2543: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',47);
2544:
2545: for ptu_rec in c_ptu loop
2546:
2547: select min(ptu1.effective_start_date)
2554: from per_person_type_usages_f ptu2
2555: where ptu2.person_type_usage_id = ptu_rec.person_type_usage_id
2556: and ptu2.effective_start_date = l_effective_date;
2557:
2558: hr_utility.set_location('l_person_type_usage_id = '||to_char(ptu_rec.person_type_usage_id),44);
2559: hr_utility.set_location('l_effective_date = '||to_char(l_effective_date,'DD/MM/YYYY'),44);
2560: hr_utility.set_location('l_object_version_number = '||to_char(l_object_version_number),44);
2561: begin
2562: -- hr_per_type_usage_internal.maintain_ptu(
2555: where ptu2.person_type_usage_id = ptu_rec.person_type_usage_id
2556: and ptu2.effective_start_date = l_effective_date;
2557:
2558: hr_utility.set_location('l_person_type_usage_id = '||to_char(ptu_rec.person_type_usage_id),44);
2559: hr_utility.set_location('l_effective_date = '||to_char(l_effective_date,'DD/MM/YYYY'),44);
2560: hr_utility.set_location('l_object_version_number = '||to_char(l_object_version_number),44);
2561: begin
2562: -- hr_per_type_usage_internal.maintain_ptu(
2563: -- p_person_id => p_person_id,
2556: and ptu2.effective_start_date = l_effective_date;
2557:
2558: hr_utility.set_location('l_person_type_usage_id = '||to_char(ptu_rec.person_type_usage_id),44);
2559: hr_utility.set_location('l_effective_date = '||to_char(l_effective_date,'DD/MM/YYYY'),44);
2560: hr_utility.set_location('l_object_version_number = '||to_char(l_object_version_number),44);
2561: begin
2562: -- hr_per_type_usage_internal.maintain_ptu(
2563: -- p_person_id => p_person_id,
2564: -- p_action => 'DELETE',
2584:
2585: end loop;
2586: --
2587: -- delete per_person_dlvry_methods
2588: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 48);
2589: --
2590: begin
2591: delete from per_person_dlvry_methods
2592: where person_id = P_PERSON_ID;
2595: end;
2596: --
2597: -- Added this delete for quickhire checklists
2598: --
2599: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 49);
2600: begin
2601: delete from per_checklist_items
2602: where person_id = P_PERSON_ID;
2603: exception
2606: --
2607: -- End addition for quickhire checklists
2608: --
2609: -- delete per_qualification and per_subjects_taken records
2610: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 50);
2611: --
2612: begin
2613:
2614: --PMFLETCH Added delete from tl table
2619: where q.person_id = P_PERSON_ID
2620: and s.qualification_id = q.qualification_id
2621: );
2622:
2623: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4698);
2624:
2625:
2626: delete from per_subjects_taken s
2627: where s.qualification_id in ( select qualification_id
2627: where s.qualification_id in ( select qualification_id
2628: from per_qualifications
2629: where person_id = P_PERSON_ID );
2630:
2631: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4699);
2632:
2633: --PMFLETCH Added delete from tl table
2634: delete from per_qualifications_tl qt
2635: where qt.qualification_id in ( select q.qualification_id
2636: from per_qualifications q
2637: where q.person_id = P_PERSON_ID
2638: );
2639:
2640: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4700);
2641:
2642: delete from per_qualifications q
2643: where q.person_id = P_PERSON_ID;
2644:
2643: where q.person_id = P_PERSON_ID;
2644:
2645: exception
2646: when NO_DATA_FOUND then
2647: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4701);
2648: end;
2649: --
2650:
2651: close LOCK_PERSON_ROWS;
2649: --
2650:
2651: close LOCK_PERSON_ROWS;
2652: --
2653: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 99);
2654: --
2655: begin
2656: delete from per_all_people_f
2657: where person_id = P_PERSON_ID;
2656: delete from per_all_people_f
2657: where person_id = P_PERSON_ID;
2658: exception
2659: when NO_DATA_FOUND then
2660: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4401);
2661: end;
2662: --
2663: -- Now remove contracts
2664: --
2666: P_PERSON_ID,
2667: NULL,
2668: NULL);
2669: --
2670: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 100);
2671: --
2672: -- Now remove Medical Assessments
2673: --
2674: FOR mea_rec IN medical_assessment_records LOOP
2679: ,mea_rec.object_version_number);
2680: --
2681: END LOOP;
2682: --
2683: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 110);
2684: --
2685: --
2686: -- Now remove disabilities
2687: --
2691: EXIT when DISABILITIES%NOTFOUND;
2692: per_disability_api.delete_disability(false,p_session_date ,'ZAP',v_disability_id, v_object_version_no, v_effective_start_date, v_effective_end_date);
2693: END LOOP;
2694: --
2695: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 120);
2696: --
2697: --
2698: -- Now remove Work incidences
2699: --
2703: EXIT when WORK_INCIDENTS%NOTFOUND;
2704: per_work_incident_api.delete_work_incident(false,v_incident_id, v_object_version_number);
2705: END LOOP;
2706: --
2707: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 130);
2708: --
2709: --
2710: -- Now remove Supplementary Roles
2711: --
2715: EXIT when ROLES%notfound;
2716: per_supplementary_role_api.delete_supplementary_role(false, v_role_id, v_ovn_roles);
2717: END LOOP;
2718: --
2719: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 150);
2720: --
2721: --
2722: begin
2723: delete from per_periods_of_placement p
2723: delete from per_periods_of_placement p
2724: where p.person_id = P_PERSON_ID;
2725: exception
2726: when NO_DATA_FOUND then
2727: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4501);
2728: end;
2729: --
2730: --
2731: END delete_a_person;
2764: if P_FORM_CALL = FALSE then
2765: hr_person_delete.person_existance_check(P_PERSON_ID);
2766: end if;
2767: --
2768: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 1);
2769: --
2770: open LOCK_PERSON_ROWS;
2771: --
2772: -- Now start cascade.
2778: ben_person_delete.delete_ben_rows(p_person_id);
2779: --
2780: -- End of Fix for WWBUG 1294400
2781: --
2782: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 2);
2783: --
2784: hr_security.delete_per_from_list(P_PERSON_ID);
2785: --
2786: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 4);
2782: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 2);
2783: --
2784: hr_security.delete_per_from_list(P_PERSON_ID);
2785: --
2786: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 4);
2787: --
2788: -- Lock assignments now, delete at end.
2789: -- Can select into a variable as max one assignment should exist (as
2790: -- strong_predel_validation has already been performed).
2799: exception
2800: when NO_DATA_FOUND then null;
2801: end;
2802: --
2803: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 5);
2804: --
2805: begin
2806: delete from pay_personal_payment_methods p
2807: where p.assignment_id = V_ASSIGNMENT_ID;
2808: exception
2809: when NO_DATA_FOUND then null;
2810: end;
2811: --
2812: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 6);
2813: --
2814: begin
2815: delete from per_assignment_budget_values_f v
2816: where v.assignment_id = V_ASSIGNMENT_ID;
2817: exception
2818: when NO_DATA_FOUND then null;
2819: end;
2820: --
2821: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 7);
2822: --
2823: begin
2824: delete from per_addresses a
2825: where a.person_id = P_PERSON_ID;
2826: exception
2827: when NO_DATA_FOUND then null;
2828: end;
2829: --
2830: hr_utility.set_location('HR_PERSON_DELETE.DELETE_DEFAULT_DELETES', 7.5);
2831: --
2832: begin
2833: delete from per_phones a
2834: where a.parent_id = P_PERSON_ID
2836: exception
2837: when NO_DATA_FOUND then null;
2838: end;
2839: --
2840: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 8);
2841: --
2842: begin
2843: delete from pay_cost_allocations_f a
2844: where a.assignment_id = V_ASSIGNMENT_ID;
2845: exception
2846: when NO_DATA_FOUND then null;
2847: end;
2848: --
2849: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 9);
2850: --
2851: begin
2852: delete from pay_element_entry_values_f v
2853: where exists (
2858: exception
2859: when NO_DATA_FOUND then null;
2860: end;
2861: --
2862: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 10);
2863: --
2864: begin
2865: delete from pay_run_results r
2866: where r.source_type = 'E'
2872: exception
2873: when NO_DATA_FOUND then null;
2874: end;
2875: --
2876: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 11);
2877: --
2878: begin
2879: delete from pay_element_entries_f e
2880: where e.assignment_id = V_ASSIGNMENT_ID;
2881: exception
2882: when NO_DATA_FOUND then null;
2883: end;
2884: --
2885: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 12);
2886: --
2887: -- No exception, should succeed.
2888: --
2889: begin
2890: delete from per_assignments_f ass
2891: where ass.assignment_id = V_ASSIGNMENT_ID;
2892: end;
2893: --
2894: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 13);
2895: --
2896: begin
2897: delete from per_periods_of_service p
2898: where p.person_id = P_PERSON_ID;
2899: exception
2900: when NO_DATA_FOUND then null;
2901: end;
2902: --
2903: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 14);
2904: --
2905: begin
2906: delete from per_applications a
2907: where a.person_id = P_PERSON_ID;
2910: end;
2911: --
2912: -- Added this delete for quickhire checklists
2913: --
2914: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 17);
2915: begin
2916: delete from per_checklist_items
2917: where person_id = P_PERSON_ID;
2918: exception
2921: --
2922: -- End addition for quickhire checklists
2923: --
2924: --
2925: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 15);
2926: --
2927: close LOCK_PERSON_ROWS;
2928: --
2929: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 16);
2925: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 15);
2926: --
2927: close LOCK_PERSON_ROWS;
2928: --
2929: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 16);
2930: --
2931: begin
2932: delete from per_people_f
2933: where person_id = P_PERSON_ID;
2978: if P_FORM_CALL = FALSE then
2979: hr_person_delete.person_existance_check(P_PERSON_ID);
2980: end if;
2981: --
2982: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 1);
2983: --
2984: open LOCK_PERSON_ROWS;
2985: --
2986: -- Now start cascade.
2984: open LOCK_PERSON_ROWS;
2985: --
2986: -- Now start cascade.
2987: --
2988: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 3);
2989: --
2990: begin
2991: delete from per_person_list l
2992: where l.person_id = P_PERSON_ID;
2993: exception
2994: when NO_DATA_FOUND then null;
2995: end;
2996: --
2997: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 5);
2998: --
2999: -- Can select into a variable as only one assignment should exist (as
3000: -- strong_predel_validation has already been performed).
3001: --
3006: where ass.person_id = P_PERSON_ID
3007: FOR UPDATE;
3008: end;
3009: --
3010: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 6);
3011: --
3012: begin
3013: delete from per_addresses a
3014: where a.person_id = P_PERSON_ID;
3015: exception
3016: when NO_DATA_FOUND then null;
3017: end;
3018: --
3019: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 6.5);
3020: --
3021: begin
3022: delete from per_phones a
3023: where a.parent_id = P_PERSON_ID
3025: exception
3026: when NO_DATA_FOUND then null;
3027: end;
3028: --
3029: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 7);
3030: --
3031: begin
3032: delete from per_assignments_f ass
3033: where ass.assignment_id = V_ASSIGNMENT_ID;
3032: delete from per_assignments_f ass
3033: where ass.assignment_id = V_ASSIGNMENT_ID;
3034: end;
3035: --
3036: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 8);
3037: --
3038: begin
3039: delete from per_applications a
3040: where a.person_id = P_PERSON_ID;
3043: end;
3044: --
3045: -- Added this delete for quickhire checklists
3046: --
3047: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 15);
3048: begin
3049: delete from per_checklist_items
3050: where person_id = P_PERSON_ID;
3051: exception
3057: --
3058: --
3059: -- Added this delete for PTU
3060: --
3061: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 16);
3062: begin
3063: delete from per_person_type_usages_f
3064: where person_id = P_PERSON_ID;
3065: exception
3068: --
3069: -- End addition for PTU
3070: --
3071: --
3072: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3073: --
3074: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3075: --
3076: close LOCK_PERSON_ROWS;
3070: --
3071: --
3072: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3073: --
3074: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3075: --
3076: close LOCK_PERSON_ROWS;
3077: --
3078: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 10);
3074: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3075: --
3076: close LOCK_PERSON_ROWS;
3077: --
3078: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 10);
3079: --
3080: begin
3081: delete from per_people_f
3082: where person_id = P_PERSON_ID;