[Home] [Help]
1: PACKAGE BODY hr_person_delete AS
2: /* $Header: peperdel.pkb 120.9.12010000.2 2009/02/23 10:15:14 ghshanka ship $ */
3: /*
4: ******************************************************************
5: * *
48: delete_a_person so that any COBRA
49: enrollment records are removed when a
50: contact is removed.
51: 80.2 17-FEB-94 PBARRY B339 Change HR_PERSDON_DELETE to
52: HR_PERSON_DELETE in locations.
53: 80.3 23-FEB-93 PBARRY B402 PAY_HOLIDAY* tables removed from R10
54: and R10G.
55: 80.4 11-APR-94 JTHURING Made changes to 1st select in
56: product_installed procedure:
71: 70.11 14-jun-95 TMathers Added moderate_predel_validation
72: moved all but one test from strong
73: into moderate, and added call to
74: moderate in strong after test.
75: 70.12 03-jan-96 AMills Changed HR_PERSON_DELETE.DELETE_A_
76: PERSON,35 (pay_assignment_link_usages_f)
77: to improve performance,
78: using index instead of F.T.Scan,
79: Bug 329490.
189: 115.33 24-Jun-2004 smparame 3732129 Modified delete_a_person procedure. Assignment ids
190: forthe person_id passed fetched into a pl/sql table
191: to improve performance.
192: 115.35 10-Aug-2004 jpthomas 3524713 Modified the procedure moderate_predel_validation in the
193: package HR_PERSON_DELETE to delete the child items from the
194: table BEN_EXT_CHG_EVT_LOG for the selected person.
195: 115.36 14-Mar-2004 njaladi 4169275 Modified procedures strong_pre_del_validation,
196: moderate_pre_del_validion and weak_pre_del_validation
197: to add parameter of date track mode.
197: to add parameter of date track mode.
198: 115.37 04-Aug-2005 pchowdav 4508139 Modified procedure strong_pre_del_validation to call
199: moderate_pre_del_validion if p_dt_delete_mode = 'ZAP'.
200: 115.38 29-Sep-2005 pchowdav 4238025 Modified the procedure
201: hr_person_delete.moderate_predel_validation
202: 115.39 04-Jan-2006 bshukla 4889068 Performance Fix of SQL ID:14960008
203: 115.40 04-Jan-2006 bshukla 4889068 Performance Fix of SQL ID:14961062,14961042
204: and 14960478
205: 115.41 19-JAN-2006 vbanner 4873360 Performance Fix of SQL ID:14959971
208: table scan on PER_PARTICIPANTS,
209: HR_QUEST_ANSWER_VALUES and PER_APPRAISALS.
210: Broke query into two peices using Conditional logic in a pl/sql block to see
211: if delete needs to be run.
212: 115.42 27-Jul-2006 pdkundu 5405424 Modified the procedure hr_person_delete.
213: moderate_predel_validation to change the
214: message number for checking secondary assignment
215: status links.
216: 115.43 16-Aug-2006 pdkundu 5464252 Modified the procedure delete_a_person
573: --
574: BEGIN
575: --
576: --
577: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',1);
578: --
579: hr_person_delete.person_existance_check(P_PERSON_ID);
580: --
581: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',2);
575: --
576: --
577: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',1);
578: --
579: hr_person_delete.person_existance_check(P_PERSON_ID);
580: --
581: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',2);
582: --
583: hr_person_delete.product_installed('PAY', v_pay_status,
577: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',1);
578: --
579: hr_person_delete.person_existance_check(P_PERSON_ID);
580: --
581: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',2);
582: --
583: hr_person_delete.product_installed('PAY', v_pay_status,
584: v_pay_installed, v_oracle_id);
585: --
579: hr_person_delete.person_existance_check(P_PERSON_ID);
580: --
581: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',2);
582: --
583: hr_person_delete.product_installed('PAY', v_pay_status,
584: v_pay_installed, v_oracle_id);
585: --
586: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',4);
587: --
582: --
583: hr_person_delete.product_installed('PAY', v_pay_status,
584: v_pay_installed, v_oracle_id);
585: --
586: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',4);
587: --
588: -- 4169275 start
589: -- During deletion of next change or future change need not require
590: -- this validation
589: -- During deletion of next change or future change need not require
590: -- this validation
591: --
592: if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
593: hr_person_delete.aol_predel_validation(P_PERSON_ID);
594: end if;
595: -- 4169275 end
596: --
597: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',5);
593: hr_person_delete.aol_predel_validation(P_PERSON_ID);
594: end if;
595: -- 4169275 end
596: --
597: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',5);
598: --
599: hr_person_delete.assignment_set_check(P_PERSON_ID);
600: --
601: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',6);
595: -- 4169275 end
596: --
597: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',5);
598: --
599: hr_person_delete.assignment_set_check(P_PERSON_ID);
600: --
601: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',6);
602: --
603: if (v_pay_installed = 'Y') then
597: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',5);
598: --
599: hr_person_delete.assignment_set_check(P_PERSON_ID);
600: --
601: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',6);
602: --
603: if (v_pay_installed = 'Y') then
604: hr_person_delete.pay_predel_validation(P_PERSON_ID);
605: end if;
600: --
601: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',6);
602: --
603: if (v_pay_installed = 'Y') then
604: hr_person_delete.pay_predel_validation(P_PERSON_ID);
605: end if;
606: --
607: -- Removed check for ben install
608: -- as OSB can now have enrollment results
607: -- Removed check for ben install
608: -- as OSB can now have enrollment results
609: -- and unrestricted Life events in progress
610: --
611: hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
612: --
613: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',8);
614: --
615: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
609: -- and unrestricted Life events in progress
610: --
611: hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
612: --
613: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',8);
614: --
615: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
616: --
617: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
611: hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
612: --
613: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',8);
614: --
615: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
616: --
617: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
618: --
619: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
613: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',8);
614: --
615: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
616: --
617: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
618: --
619: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
620: --
621: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
615: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
616: --
617: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
618: --
619: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
620: --
621: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
622: --
623: hr_person_delete.contracts_check(P_PERSON_ID);
617: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
618: --
619: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
620: --
621: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
622: --
623: hr_person_delete.contracts_check(P_PERSON_ID);
624: --
625: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',11);
619: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
620: --
621: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
622: --
623: hr_person_delete.contracts_check(P_PERSON_ID);
624: --
625: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',11);
626: --
627:
621: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
622: --
623: hr_person_delete.contracts_check(P_PERSON_ID);
624: --
625: hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',11);
626: --
627:
628: END weak_predel_validation;
629: -------------------- END: weak_predel_validation --------------------------
658: -- Bug 3524713 Ends Here
659: --
660: begin
661: --
662: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 1);
663: --
664: hr_person_delete.person_existance_check(P_PERSON_ID);
665: --
666: hr_person_delete.assignment_set_check(P_PERSON_ID);
660: begin
661: --
662: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 1);
663: --
664: hr_person_delete.person_existance_check(P_PERSON_ID);
665: --
666: hr_person_delete.assignment_set_check(P_PERSON_ID);
667: --
668: --
662: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 1);
663: --
664: hr_person_delete.person_existance_check(P_PERSON_ID);
665: --
666: hr_person_delete.assignment_set_check(P_PERSON_ID);
667: --
668: --
669: -- 4169275 start
670: -- During deletion of next change or future change need not require
670: -- During deletion of next change or future change need not require
671: -- this validation
672: --
673: if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
674: hr_person_delete.aol_predel_validation(P_PERSON_ID);
675: end if;
676: -- 4169275 end
677: --
678: --
675: end if;
676: -- 4169275 end
677: --
678: --
679: hr_person_delete.pay_predel_validation(P_PERSON_ID);
680: --
681: hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
682: --
683: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 2);
677: --
678: --
679: hr_person_delete.pay_predel_validation(P_PERSON_ID);
680: --
681: hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
682: --
683: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 2);
684: --
685: -- VT 1403481 09/19/00
679: hr_person_delete.pay_predel_validation(P_PERSON_ID);
680: --
681: hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
682: --
683: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 2);
684: --
685: -- VT 1403481 09/19/00
686: begin
687: -- bug fix 3732129.
731: hr_utility.set_message (801,'HR_6325_ALL_PER_RL_NO_DEL');
732: hr_utility.raise_error;
733: end;
734: --
735: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 3);
736: --
737: begin
738: select 'Y'
739: into v_delete_permitted
748: hr_utility.set_message (801,'HR_6326_ALL_PER_CR_NO_DEL');
749: hr_utility.raise_error;
750: end;
751: --
752: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 5);
753: --
754: begin
755: select 'Y'
756: into v_delete_permitted
764: hr_utility.set_message (801,'HR_6328_ALL_PER_EVENT_NO_DEL');
765: hr_utility.raise_error;
766: end;
767: --
768: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 6);
769: --
770: begin
771: select 'Y'
772: into v_delete_permitted
780: hr_utility.set_message (801,'HR_6329_ALL_PER_BOOK_NO_DEL');
781: hr_utility.raise_error;
782: end;
783: --
784: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 7);
785: --
786: -- VT 1403481 09/19/00
787: begin
788: select 'Y'
798: hr_utility.set_message (801,'HR_6330_ALL_PER_ASSGT_NO_DEL');
799: hr_utility.raise_error;
800: end;
801: --
802: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 8);
803: --
804: begin
805: select 'Y'
806: into v_delete_permitted
815: hr_utility.set_message (801,'HR_6331_ALL_PER_RT_SUP_NO_DEL');
816: hr_utility.raise_error;
817: end;
818: --
819: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 9);
820: --
821: begin
822: select 'Y'
823: into v_delete_permitted
831: hr_utility.set_message (801,'HR_6332_ALL_PER_TERM_NO_DEL');
832: hr_utility.raise_error;
833: end;
834: --
835: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 10);
836: --
837: begin
838: select 'Y'
839: into v_delete_permitted
847: hr_utility.set_message (801,'HR_6334_ALL_PER_ANAL_NO_DEL');
848: hr_utility.raise_error;
849: end;
850: --
851: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 11);
852: --
853: begin
854: select 'Y'
855: into v_delete_permitted
863: hr_utility.set_message (801,'HR_6335_ALL_PER_ABS_ATT_NO_DEL');
864: hr_utility.raise_error;
865: end;
866: --
867: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 12);
868: --
869: begin
870: select 'Y'
871: into v_delete_permitted
880: hr_utility.set_message (801,'HR_6336_ALL_PER_AUTH_NO_DEL');
881: hr_utility.raise_error;
882: end;
883: --
884: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 13);
885: --
886: begin
887: select 'Y'
888: into v_delete_permitted
897: hr_utility.set_message (801,'HR_6337_ALL_PER_REC_NO_DEL');
898: hr_utility.raise_error;
899: end;
900: --
901: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);
902: begin
903: select 'Y'
904: into v_delete_permitted
905: from sys.dual
913: fnd_message.set_name(801,'PER_52467_APR_PAR_REC_NO_DEL');
914: fnd_message.raise_error;
915: end;
916: --
917: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);
918: begin
919: select 'Y'
920: into v_delete_permitted
921: from sys.dual
928: fnd_message.set_name(801,'PER_52467_APR_PAR_REC_NO_DEL');
929: fnd_message.raise_error;
930: end;
931: --
932: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 14);
933: --
934: begin
935: select 'Y'
936: into v_delete_permitted
944: hr_utility.set_message (801,'HR_6338_ALL_PER_REQ_NO_DEL');
945: hr_utility.raise_error;
946: end;
947: --
948: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 15);
949: --
950: begin
951: select 'Y'
952: into v_delete_permitted
960: hr_utility.set_message (801,'HR_6339_ALL_PER_VAC_NO_DEL');
961: hr_utility.raise_error;
962: end;
963: --
964: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 16);
965: --
966: -- Any discretionary link element entries?
967: --
968: begin
983: hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
984: hr_utility.raise_error;
985: end;
986: --
987: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 17);
988: --
989: -- Any entry adjustments, overrides etc.?
990: -- (We cannot capture manual enty of standard link entries)
991: --
1005: hr_utility.set_message (801,'HR_6375_ALL_PER_ENTRY_NO_DEL');
1006: hr_utility.raise_error;
1007: end;
1008: --
1009: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 171);
1010: --
1011: -- Are the entries to be deleted in a closed period? If so cannot delete.
1012: --
1013: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
1009: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 171);
1010: --
1011: -- Are the entries to be deleted in a closed period? If so cannot delete.
1012: --
1013: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
1014: --
1015: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 18);
1016: --
1017: begin
1011: -- Are the entries to be deleted in a closed period? If so cannot delete.
1012: --
1013: hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
1014: --
1015: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 18);
1016: --
1017: begin
1018: select 'Y'
1019: into v_delete_permitted
1031: hr_utility.set_message (801,'HR_6341_ALL_PER_ASS_INFO_DEL');
1032: hr_utility.raise_error;
1033: end;
1034: --
1035: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 19);
1036: --
1037: begin
1038: select 'Y'
1039: into v_delete_permitted
1052: hr_utility.set_message (801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
1053: hr_utility.raise_error;
1054: end;
1055: --
1056: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 20);
1057: --
1058: begin
1059: select 'Y'
1060: into v_delete_permitted
1072: hr_utility.set_message (801,'HR_6344_ALL_PER_INT_NO_DEL');
1073: hr_utility.raise_error;
1074: end;
1075: --
1076: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 21);
1077: --
1078: begin
1079: select 'Y'
1080: into v_delete_permitted
1092: hr_utility.set_message (801,'HR_6374_ALL_PER_SPINE_NO_DEL');
1093: hr_utility.raise_error;
1094: end;
1095: --
1096: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 22);
1097: --
1098: begin
1099: select 'Y'
1100: into v_delete_permitted
1112: hr_utility.set_message (801,'HR_6379_ALL_PER_QP_NO_DEL');
1113: hr_utility.raise_error;
1114: end;
1115: --
1116: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 26);
1117: --
1118: begin
1119: select 'Y'
1120: into v_delete_permitted
1132: hr_utility.set_message (801,'HR_6476_ALL_PER_COB_NO_DEL');
1133: hr_utility.raise_error;
1134: end;
1135: --
1136: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 27);
1137: --
1138: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
1139: --
1140: hr_person_delete.contracts_check(P_PERSON_ID);
1134: end;
1135: --
1136: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 27);
1137: --
1138: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
1139: --
1140: hr_person_delete.contracts_check(P_PERSON_ID);
1141: --
1142: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 28);
1136: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 27);
1137: --
1138: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
1139: --
1140: hr_person_delete.contracts_check(P_PERSON_ID);
1141: --
1142: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 28);
1143: -- Validation for BEN
1144: --
1138: hr_person_delete.contact_cobra_validation(P_PERSON_ID);
1139: --
1140: hr_person_delete.contracts_check(P_PERSON_ID);
1141: --
1142: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 28);
1143: -- Validation for BEN
1144: --
1145: -- Bug 3524713 Starts Here
1146: OPEN ben_ext_chg_log (
1167:
1168: --
1169: -- validation for PA
1170: --
1171: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 29);
1172: --
1173: pa_person.pa_predel_validation(P_PERSON_ID);
1174: --
1175: -- validation for WIP
1173: pa_person.pa_predel_validation(P_PERSON_ID);
1174: --
1175: -- validation for WIP
1176: --
1177: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 30);
1178: --
1179: wip_person.wip_predel_validation(P_PERSON_ID);
1180: --
1181: -- validation for ENG
1179: wip_person.wip_predel_validation(P_PERSON_ID);
1180: --
1181: -- validation for ENG
1182: --
1183: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 31);
1184: --
1185: eng_person.eng_predel_validation(P_PERSON_ID);
1186: --
1187: -- validation for AP
1185: eng_person.eng_predel_validation(P_PERSON_ID);
1186: --
1187: -- validation for AP
1188: --
1189: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 32);
1190: --
1191: ap_person.ap_predel_validation(P_PERSON_ID);
1192: --
1193: -- validation for FA
1191: ap_person.ap_predel_validation(P_PERSON_ID);
1192: --
1193: -- validation for FA
1194: --
1195: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 33);
1196: --
1197: fa_person.fa_predel_validation(P_PERSON_ID);
1198: --
1199: -- validation for PO
1197: fa_person.fa_predel_validation(P_PERSON_ID);
1198: --
1199: -- validation for PO
1200: --
1201: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 34);
1202: --
1203: po_person.po_predel_validation(P_PERSON_ID);
1204: --
1205: -- validation for RCV
1203: po_person.po_predel_validation(P_PERSON_ID);
1204: --
1205: -- validation for RCV
1206: --
1207: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 35);
1208: --
1209: rcv_person.rcv_predel_validation(P_PERSON_ID);
1210: --
1211: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 36);
1207: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 35);
1208: --
1209: rcv_person.rcv_predel_validation(P_PERSON_ID);
1210: --
1211: hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 36);
1212: --
1213: --
1214: end moderate_predel_validation;
1215: -------------------- END: moderate_predel_validation ---------------------
1246: v_person_types number;
1247: --
1248: BEGIN
1249: --
1250: hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1251: --
1252: -- If >1 system person types then non default amendments have been made.
1253: -- If v_person_types = 0 then only 1 system person type else > 1.
1254: --
1274: hr_utility.set_message (801,'HR_6324_ALL_PER_ADD_NO_DEL');
1275: hr_utility.raise_error;
1276: end if;
1277: --
1278: hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1279: --
1280: -- fix for bug 4508139
1281: if p_dt_delete_mode = 'ZAP' then
1282: hr_person_delete.moderate_predel_validation(p_person_id => p_person_id
1278: hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1279: --
1280: -- fix for bug 4508139
1281: if p_dt_delete_mode = 'ZAP' then
1282: hr_person_delete.moderate_predel_validation(p_person_id => p_person_id
1283: ,p_session_date =>p_session_date
1284: ,p_dt_delete_mode => p_dt_delete_mode -- 4169275
1285: );
1286: end if;
1317: v_delete_contact varchar2(1);
1318: --
1319: BEGIN
1320: --
1321: hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 1);
1322: --
1323: hr_person_delete.person_existance_check(P_CONTACT_PERSON_ID);
1324: --
1325: begin
1319: BEGIN
1320: --
1321: hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 1);
1322: --
1323: hr_person_delete.person_existance_check(P_CONTACT_PERSON_ID);
1324: --
1325: begin
1326: select 'Y'
1327: into v_contact_elsewhere
1338: if SQL%ROWCOUNT > 0 then
1339: return;
1340: end if;
1341: --
1342: hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 2);
1343: --
1344: begin
1345: select 'Y'
1346: into v_other_only
1361: -- contact.
1362: --
1363: begin
1364: v_delete_contact := 'Y';
1365: hr_person_delete.strong_predel_validation(P_CONTACT_PERSON_ID,
1366: P_SESSION_DATE);
1367: exception
1368: when hr_utility.hr_error then
1369: v_delete_contact := 'N';
1369: v_delete_contact := 'N';
1370: end;
1371: --
1372: if v_delete_contact = 'Y' then
1373: hr_person_delete.people_default_deletes(P_CONTACT_PERSON_ID,
1374: TRUE);
1375: end if;
1376: --
1377: end;
1494: --
1495: --
1496: --
1497: if P_FORM_CALL = FALSE then
1498: hr_person_delete.person_existance_check(P_PERSON_ID);
1499: end if;
1500: --
1501: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 0);
1502: --
1497: if P_FORM_CALL = FALSE then
1498: hr_person_delete.person_existance_check(P_PERSON_ID);
1499: end if;
1500: --
1501: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 0);
1502: --
1503: -- Lock person rows, delete at end of procedure.
1504: --
1505: open LOCK_PERSON_ROWS;
1505: open LOCK_PERSON_ROWS;
1506: --
1507: -- Now start cascade.
1508: --
1509: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1);
1510: --
1511: -- bug fix 3732129 starts here.
1512: -- fetching the assignment ids into a pl/sql table.
1513:
1516: close c_asg;
1517:
1518: -- bug fix 3732129 ends here.
1519:
1520: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 888);
1521:
1522: begin
1523: update per_requisitions r
1524: set r.person_id = null
1524: set r.person_id = null
1525: where r.person_id = P_PERSON_ID;
1526: exception
1527: when NO_DATA_FOUND then
1528: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 101);
1529: end;
1530: --
1531: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2);
1532: --
1527: when NO_DATA_FOUND then
1528: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 101);
1529: end;
1530: --
1531: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2);
1532: --
1533: begin
1534: -- bug fix 3732129.
1535: -- Delete statement modified to improve performance.
1550: where a.person_id = P_PERSON_ID
1551: and a.assignment_id = l.assignment_id);*/
1552: exception
1553: when NO_DATA_FOUND then
1554: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 201);
1555: end;
1556: --
1557: -- Leave per_letter_requests for the moment - may not be necessary to
1558: -- delete the parent with no children which requires some work with
1557: -- Leave per_letter_requests for the moment - may not be necessary to
1558: -- delete the parent with no children which requires some work with
1559: -- cursors.
1560: --
1561: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3);
1562: --
1563: begin
1564: delete from per_absence_attendances a
1565: where a.person_id = P_PERSON_ID;
1564: delete from per_absence_attendances a
1565: where a.person_id = P_PERSON_ID;
1566: exception
1567: when NO_DATA_FOUND then
1568: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 301);
1569: end;
1570: --
1571: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4);
1572: --
1567: when NO_DATA_FOUND then
1568: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 301);
1569: end;
1570: --
1571: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4);
1572: --
1573: begin
1574: update per_absence_attendances a
1575: set a.authorising_person_id = null
1575: set a.authorising_person_id = null
1576: where a.authorising_person_id = P_PERSON_ID;
1577: exception
1578: when NO_DATA_FOUND then
1579: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 401);
1580: end;
1581: --
1582: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 5);
1583: --
1578: when NO_DATA_FOUND then
1579: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 401);
1580: end;
1581: --
1582: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 5);
1583: --
1584: begin
1585: update per_absence_attendances a
1586: set a.replacement_person_id = null
1586: set a.replacement_person_id = null
1587: where a.replacement_person_id = P_PERSON_ID;
1588: exception
1589: when NO_DATA_FOUND then
1590: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 501);
1591: end;
1592: --
1593: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 6);
1594: --
1589: when NO_DATA_FOUND then
1590: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 501);
1591: end;
1592: --
1593: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 6);
1594: --
1595: begin
1596: delete from per_person_analyses a
1597: where a.person_id = P_PERSON_ID;
1596: delete from per_person_analyses a
1597: where a.person_id = P_PERSON_ID;
1598: exception
1599: when NO_DATA_FOUND then
1600: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 601);
1601: end;
1602: --
1603: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 8);
1604: --
1599: when NO_DATA_FOUND then
1600: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 601);
1601: end;
1602: --
1603: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 8);
1604: --
1605: -- Delete of per_periods_of_service at end after delete of
1606: -- per_assignments_f.
1607: --
1610: set p.termination_accepted_person_id = null
1611: where p.termination_accepted_person_id = P_PERSON_ID;
1612: exception
1613: when NO_DATA_FOUND then
1614: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 801);
1615: end;
1616: --
1617: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 9);
1618: --
1613: when NO_DATA_FOUND then
1614: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 801);
1615: end;
1616: --
1617: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 9);
1618: --
1619: begin
1620: update per_recruitment_activities r
1621: set r.authorising_person_id = null
1621: set r.authorising_person_id = null
1622: where r.authorising_person_id = P_PERSON_ID;
1623: exception
1624: when NO_DATA_FOUND then
1625: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 901);
1626: end;
1627: --
1628: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 10);
1629: --
1624: when NO_DATA_FOUND then
1625: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 901);
1626: end;
1627: --
1628: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 10);
1629: --
1630: begin
1631: update per_recruitment_activities r
1632: set r.internal_contact_person_id = null
1632: set r.internal_contact_person_id = null
1633: where r.internal_contact_person_id = P_PERSON_ID;
1634: exception
1635: when NO_DATA_FOUND then
1636: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1001);
1637: end;
1638: --
1639: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',11);
1640: --
1635: when NO_DATA_FOUND then
1636: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1001);
1637: end;
1638: --
1639: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',11);
1640: --
1641: -- Bug 4873360 fix for performance repository sql id 14959971.
1642: -- Rewrote the delete query commented out below (and already once tuned for
1643: -- bug 3619599) to avoid a merge join cartesian and a full table scan on
1669: and qsa.type_object_id = par.participant_id
1670: and qsa.type = 'PARTICIPANT'
1671: and par.person_id = P_PERSON_ID);
1672: end if;
1673: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 215); --added for bug 5464252
1674: /*Start of bug 5464252*/
1675: exception
1676: when NO_DATA_FOUND then
1677: v_dummy := null;
1674: /*Start of bug 5464252*/
1675: exception
1676: when NO_DATA_FOUND then
1677: v_dummy := null;
1678: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 211);
1679: /*End of bug 5464252*/
1680: end; -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
1681:
1682: begin -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
1707: /* start of bug 5464252 */
1708: exception
1709: when NO_DATA_FOUND then
1710: v_dummy := null;
1711: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 220);
1712: /* end of bug 5464252 */
1713: end; -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
1714: end; -- Delete from HR_QUEST_ANSWER_VALUES
1715: -- original sql.
1732: and par.person_id = P_PERSON_ID)
1733: ); -- Fix 3619599
1734: exception
1735: when NO_DATA_FOUND then
1736: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1101);
1737: end;
1738: */
1739: -- Now delete from HR_QUEST_ANSWERS
1740: --
1777: WHERE person_id = P_PERSON_ID;
1778:
1779: EXCEPTION
1780: WHEN Others Then
1781: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1111);
1782:
1783: END;
1784: BEGIN
1785: DELETE
1801: WHERE appraisee_person_id = P_PERSON_ID);
1802:
1803: EXCEPTION
1804: WHEN Others Then
1805: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1112);
1806: END;
1807: -- fix for the bug 8265994
1808: --
1809: begin
1822: and par.person_id = P_PERSON_ID)
1823: ); -- Fix 3619599
1824: exception
1825: when NO_DATA_FOUND then
1826: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1102);
1827: end;
1828: --
1829: -- Now delete from per_participants
1830: -- SQL Fixed for Performance
1849: )
1850: );
1851: exception
1852: when NO_DATA_FOUND then
1853: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1103);
1854: end;
1855: --
1856: -- Now delete from per_appraisals
1857: --
1860: where --apr.appraiser_person_id = P_PERSON_ID or -- changed as part of bug#8265994
1861: apr.appraisee_person_id = P_PERSON_ID;
1862: exception
1863: when NO_DATA_FOUND then
1864: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1104);
1865: end;
1866:
1867: --
1868: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 12);
1864: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1104);
1865: end;
1866:
1867: --
1868: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 12);
1869: --
1870: hr_security.delete_per_from_list(P_PERSON_ID);
1871: --
1872: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 14);
1868: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 12);
1869: --
1870: hr_security.delete_per_from_list(P_PERSON_ID);
1871: --
1872: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 14);
1873: --
1874: begin
1875: update per_vacancies v
1876: set v.recruiter_id = null
1876: set v.recruiter_id = null
1877: where v.recruiter_id = P_PERSON_ID;
1878: exception
1879: when NO_DATA_FOUND then
1880: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1401);
1881: end;
1882: --
1883: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 15);
1884: --
1879: when NO_DATA_FOUND then
1880: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1401);
1881: end;
1882: --
1883: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 15);
1884: --
1885: begin
1886: update per_assignments_f ass
1887: set ass.person_referred_by_id = null
1887: set ass.person_referred_by_id = null
1888: where ass.person_referred_by_id = P_PERSON_ID;
1889: exception
1890: when NO_DATA_FOUND then
1891: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1501);
1892: end;
1893: --
1894: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 16);
1895: --
1890: when NO_DATA_FOUND then
1891: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1501);
1892: end;
1893: --
1894: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 16);
1895: --
1896: begin
1897: update per_assignments_f a
1898: set a.recruiter_id = null
1898: set a.recruiter_id = null
1899: where a.recruiter_id = P_PERSON_ID;
1900: exception
1901: when NO_DATA_FOUND then
1902: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1601);
1903: end;
1904: --
1905: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 17);
1906: --
1901: when NO_DATA_FOUND then
1902: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1601);
1903: end;
1904: --
1905: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 17);
1906: --
1907: begin
1908: update per_assignments_f a
1909: set a.supervisor_id = null
1909: set a.supervisor_id = null
1910: where a.supervisor_id = P_PERSON_ID;
1911: exception
1912: when NO_DATA_FOUND then
1913: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1701);
1914: end;
1915: --
1916: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 18);
1917: --
1912: when NO_DATA_FOUND then
1913: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1701);
1914: end;
1915: --
1916: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 18);
1917: --
1918: -- LOCK ASSIGNMENTS NOW: have to use cursor as cannot return >1 row for
1919: -- 'into' part of PL/SQL.
1920: --
1919: -- 'into' part of PL/SQL.
1920: --
1921: open LOCK_ASSIGNMENT_ROWS;
1922: --
1923: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 185);
1924: --
1925: begin
1926: --
1927: -- Bug 349818. Delete from per_pay_proposal_components before
1947: where ass.assignment_id = p.assignment_id
1948: and ass.person_id = P_PERSON_ID);
1949: exception
1950: when NO_DATA_FOUND then
1951: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1801);
1952: end;
1953: --
1954: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 19);
1955: --
1950: when NO_DATA_FOUND then
1951: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1801);
1952: end;
1953: --
1954: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 19);
1955: --
1956: begin
1957: delete from pay_personal_payment_methods_f m
1958: where m.assignment_id in (
1960: from per_assignments_f ass
1961: where ass.person_id = P_PERSON_ID);
1962: exception
1963: when NO_DATA_FOUND then
1964: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1901);
1965: end;
1966: --
1967: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 20);
1968: --
1963: when NO_DATA_FOUND then
1964: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1901);
1965: end;
1966: --
1967: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 20);
1968: --
1969: begin
1970: delete from per_assignment_budget_values_f a
1971: where a.assignment_id in (
1973: from per_assignments_f ass
1974: where ass.person_id = P_PERSON_ID);
1975: exception
1976: when NO_DATA_FOUND then
1977: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2001);
1978: end;
1979: --
1980: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 21);
1981: --
1976: when NO_DATA_FOUND then
1977: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2001);
1978: end;
1979: --
1980: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 21);
1981: --
1982: begin
1983: delete from per_assignment_extra_info a
1984: where a.assignment_id in (
1986: from per_assignments_f ass
1987: where ass.person_id = P_PERSON_ID);
1988: exception
1989: when NO_DATA_FOUND then
1990: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2101);
1991: end;
1992: --
1993: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 22);
1994: --
1989: when NO_DATA_FOUND then
1990: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2101);
1991: end;
1992: --
1993: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 22);
1994: --
1995: begin
1996: delete from per_secondary_ass_statuses a
1997: where a.assignment_id in (
1999: from per_assignments_f ass
2000: where ass.person_id = P_PERSON_ID);
2001: exception
2002: when NO_DATA_FOUND then
2003: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2201);
2004: end;
2005: --
2006: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23);
2007: --
2002: when NO_DATA_FOUND then
2003: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2201);
2004: end;
2005: --
2006: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23);
2007: --
2008: -- Delete COBRA references and then any contact relationships. COBRA
2009: -- must be deleted first as PER_COBRA_COV_ENROLLMENTS has a
2010: -- contact_relationship_id which may be constrained later.
2021: and ass.person_id = P_PERSON_ID)
2022: );
2023: exception
2024: when NO_DATA_FOUND then
2025: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2301);
2026: end;
2027: --
2028: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 230);
2029: --
2024: when NO_DATA_FOUND then
2025: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2301);
2026: end;
2027: --
2028: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 230);
2029: --
2030: begin
2031: delete from per_cobra_coverage_benefits c2
2032: where c2.cobra_coverage_enrollment_id in (
2042: and ass.person_id = r.person_id)
2043: );
2044: exception
2045: when NO_DATA_FOUND then
2046: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23001);
2047: end;
2048: --
2049: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 231);
2050: --
2045: when NO_DATA_FOUND then
2046: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23001);
2047: end;
2048: --
2049: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 231);
2050: --
2051: begin
2052: delete from per_cobra_coverage_statuses c2
2053: where c2.cobra_coverage_enrollment_id in (
2060: and ass.person_id = P_PERSON_ID)
2061: );
2062: exception
2063: when NO_DATA_FOUND then
2064: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23101);
2065: end;
2066: --
2067: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 232);
2068: --
2063: when NO_DATA_FOUND then
2064: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23101);
2065: end;
2066: --
2067: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 232);
2068: --
2069: begin
2070: delete from per_cobra_coverage_statuses c2
2071: where c2.cobra_coverage_enrollment_id in (
2081: and ass.person_id = r.person_id)
2082: );
2083: exception
2084: when NO_DATA_FOUND then
2085: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23201);
2086: end;
2087: --
2088: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 233);
2089: --
2084: when NO_DATA_FOUND then
2085: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23201);
2086: end;
2087: --
2088: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 233);
2089: --
2090: begin
2091: delete from per_sched_cobra_payments c2
2092: where c2.cobra_coverage_enrollment_id in (
2099: and ass.person_id = P_PERSON_ID)
2100: );
2101: exception
2102: when NO_DATA_FOUND then
2103: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23301);
2104: end;
2105: --
2106: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 234);
2107: --
2102: when NO_DATA_FOUND then
2103: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23301);
2104: end;
2105: --
2106: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 234);
2107: --
2108: begin
2109: delete from per_sched_cobra_payments c2
2110: where c2.cobra_coverage_enrollment_id in (
2120: and ass.person_id = r.person_id)
2121: );
2122: exception
2123: when NO_DATA_FOUND then
2124: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23401);
2125: end;
2126: --
2127: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 235);
2128: --
2123: when NO_DATA_FOUND then
2124: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23401);
2125: end;
2126: --
2127: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 235);
2128: --
2129: begin
2130: delete from per_cobra_cov_enrollments c
2131: where c.assignment_id in (
2133: from per_assignments_f ass
2134: where ass.person_id = P_PERSON_ID);
2135: exception
2136: when NO_DATA_FOUND then
2137: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23501);
2138: end;
2139: --
2140: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 236);
2141: --
2136: when NO_DATA_FOUND then
2137: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23501);
2138: end;
2139: --
2140: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 236);
2141: --
2142: begin
2143: delete from per_cobra_cov_enrollments c
2144: where exists
2153: and ass.person_id = r.person_id)
2154: );
2155: exception
2156: when NO_DATA_FOUND then
2157: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23601);
2158: end;
2159: --
2160: --Bug# 3026024 Start Here
2161: --Description : Delete the entry in the table ben_covered_dependents_f for the
2161: --Description : Delete the entry in the table ben_covered_dependents_f for the
2162: -- contact person whom is getting deleted.
2163: --
2164: --
2165: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 237);
2166: --
2167: begin
2168: delete from ben_covered_dependents_f c
2169: where c.contact_relationship_id in (
2172: where r.contact_person_id = p_person_id
2173: );
2174: exception
2175: when NO_DATA_FOUND then
2176: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);
2177: end;
2178: --
2179: --Bug# 3026024 End Here
2180: --
2188: -- NB If b is created as a contact of b then 2 contact relationships are
2189: -- are created: a,b and b,a so that they can be queried in either
2190: -- direction. Hence must delete both here.
2191: --
2192: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 237);
2193: --
2194: begin
2195: select count(*)
2196: into v_dummy
2205: and r.contact_person_id = EACH_CONTACT.CONTACT_PERSON_ID)
2206: or (r.person_id = EACH_CONTACT.CONTACT_PERSON_ID
2207: and r.contact_person_id = P_PERSON_ID);
2208: --
2209: hr_person_delete.check_contact(P_PERSON_ID,
2210: EACH_CONTACT.CONTACT_PERSON_ID,
2211: EACH_CONTACT.CONTACT_RELATIONSHIP_ID,
2212: P_SESSION_DATE);
2213: end loop;
2214: end if;
2215: --
2216: exception
2217: when NO_DATA_FOUND then
2218: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);
2219: end;
2220: --
2221: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 24);
2222: --
2217: when NO_DATA_FOUND then
2218: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);
2219: end;
2220: --
2221: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 24);
2222: --
2223: begin
2224: delete from per_contact_relationships r
2225: where r.contact_person_id = P_PERSON_ID;
2224: delete from per_contact_relationships r
2225: where r.contact_person_id = P_PERSON_ID;
2226: exception
2227: when NO_DATA_FOUND then
2228: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2401);
2229: end;
2230: --
2231: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25);
2232: --
2227: when NO_DATA_FOUND then
2228: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2401);
2229: end;
2230: --
2231: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25);
2232: --
2233: begin
2234: delete from per_addresses a
2235: where a.person_id = P_PERSON_ID;
2234: delete from per_addresses a
2235: where a.person_id = P_PERSON_ID;
2236: exception
2237: when NO_DATA_FOUND then
2238: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2501);
2239: end;
2240: --
2241: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25.5);
2242: --
2237: when NO_DATA_FOUND then
2238: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2501);
2239: end;
2240: --
2241: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25.5);
2242: --
2243: begin
2244: delete from per_phones a
2245: where a.parent_id = P_PERSON_ID
2245: where a.parent_id = P_PERSON_ID
2246: and a.parent_table = 'PER_ALL_PEOPLE_F';
2247: exception
2248: when NO_DATA_FOUND then
2249: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25501);
2250: end;
2251: --
2252: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26);
2253: --
2248: when NO_DATA_FOUND then
2249: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25501);
2250: end;
2251: --
2252: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26);
2253: --
2254: -- we must do this delete in dynamic sql because the per_performance_reviews
2255: -- table will not exist if the database has not been upgraded to new salary admin
2256: -- (introduced April 1998). The procedure would not compile if this was not dynamic.
2270: raise;
2271: end if;
2272: end;
2273: --
2274: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 261);
2275: --
2276: -- About to delete interview events for assignments. However, must
2277: -- first delete bookings (interviewers) for those events.
2278: --
2298: and ass.person_id = P_PERSON_ID)
2299: );*/
2300: exception
2301: when NO_DATA_FOUND then
2302: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26101);
2303: end;
2304: --
2305: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 262);
2306: --
2301: when NO_DATA_FOUND then
2302: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26101);
2303: end;
2304: --
2305: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 262);
2306: --
2307: begin
2308: -- bug fix 3732129.
2309: -- Delete statement modified to improve performance.
2318: from per_assignments_f ass
2319: where ass.person_id = P_PERSON_ID);*/
2320: exception
2321: when NO_DATA_FOUND then
2322: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26201);
2323: end;
2324: --
2325: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 27);
2326: --
2321: when NO_DATA_FOUND then
2322: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26201);
2323: end;
2324: --
2325: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 27);
2326: --
2327: begin
2328: update per_events e
2329: set e.internal_contact_person_id = null
2329: set e.internal_contact_person_id = null
2330: where e.internal_contact_person_id = P_PERSON_ID;
2331: exception
2332: when NO_DATA_FOUND then
2333: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2701);
2334: end;
2335: --
2336: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 28);
2337: --
2332: when NO_DATA_FOUND then
2333: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2701);
2334: end;
2335: --
2336: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 28);
2337: --
2338: begin
2339: delete from per_bookings b
2340: where b.person_id = P_PERSON_ID;
2339: delete from per_bookings b
2340: where b.person_id = P_PERSON_ID;
2341: exception
2342: when NO_DATA_FOUND then
2343: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2801);
2344: end;
2345: --
2346: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 29);
2347: --
2342: when NO_DATA_FOUND then
2343: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2801);
2344: end;
2345: --
2346: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 29);
2347: --
2348: begin
2349: delete from per_quickpaint_result_text q
2350: where q.assignment_id in (
2352: from per_assignments_f ass
2353: where ass.person_id = P_PERSON_ID);
2354: exception
2355: when NO_DATA_FOUND then
2356: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2901);
2357: end;
2358: --
2359: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 30);
2360: --
2355: when NO_DATA_FOUND then
2356: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2901);
2357: end;
2358: --
2359: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 30);
2360: --
2361: -- Validation has already been performed against
2362: -- hr_assignment_set_amendments in weak_predel_validation.
2363: --
2368: from per_assignments_f ass
2369: where ass.person_id = P_PERSON_ID);
2370: exception
2371: when NO_DATA_FOUND then
2372: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3001);
2373: end;
2374: --
2375: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 31);
2376: --
2371: when NO_DATA_FOUND then
2372: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3001);
2373: end;
2374: --
2375: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 31);
2376: --
2377: begin
2378: delete from pay_cost_allocations_f a
2379: where a.assignment_id in (
2381: from per_assignments_f ass
2382: where ass.person_id = P_PERSON_ID);
2383: exception
2384: when NO_DATA_FOUND then
2385: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3101);
2386: end;
2387: --
2388: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 32);
2389: --
2384: when NO_DATA_FOUND then
2385: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3101);
2386: end;
2387: --
2388: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 32);
2389: --
2390: begin
2391: delete from per_spinal_point_placements_f p
2392: where p.assignment_id in (
2394: from per_assignments_f ass
2395: where ass.person_id = P_PERSON_ID);
2396: exception
2397: when NO_DATA_FOUND then
2398: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3201);
2399: end;
2400: --
2401: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 33);
2402: --
2397: when NO_DATA_FOUND then
2398: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3201);
2399: end;
2400: --
2401: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 33);
2402: --
2403: -- Validation has already been performed against
2404: -- pay_assignment_actions in weak_predel_validation.
2405: --
2411: where ass.person_id = P_PERSON_ID
2412: and ass.assignment_id = a.assignment_id);
2413: exception
2414: when NO_DATA_FOUND then
2415: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3301);
2416: end;
2417: --
2418: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 34);
2419: --
2414: when NO_DATA_FOUND then
2415: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3301);
2416: end;
2417: --
2418: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 34);
2419: --
2420: begin
2421: delete from pay_assignment_latest_balances b
2422: where b.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', 3401);
2429: end;
2430: --
2431: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 35);
2432: --
2427: when NO_DATA_FOUND then
2428: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3401);
2429: end;
2430: --
2431: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 35);
2432: --
2433: begin
2434: -- bug fix 3732129
2435: -- Delete statement modified to improve performance.
2445: from per_assignments_f ass
2446: where ass.person_id = P_PERSON_ID); */
2447: exception
2448: when NO_DATA_FOUND then
2449: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3501);
2450: end;
2451: --
2452: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 36);
2453: --
2448: when NO_DATA_FOUND then
2449: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3501);
2450: end;
2451: --
2452: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 36);
2453: --
2454: begin
2455: delete from pay_element_entry_values_f v
2456: where v.element_entry_id in (
2463: and ass.person_id = P_PERSON_ID)
2464: );
2465: exception
2466: when NO_DATA_FOUND then
2467: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3601);
2468: end;
2469: --
2470: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 37);
2471: --
2466: when NO_DATA_FOUND then
2467: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3601);
2468: end;
2469: --
2470: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 37);
2471: --
2472: begin
2473: delete from pay_run_results r
2474: where r.source_type = 'E'
2482: and ass.person_id = P_PERSON_ID)
2483: );
2484: exception
2485: when NO_DATA_FOUND then
2486: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3701);
2487: end;
2488: --
2489: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 38);
2490: --
2485: when NO_DATA_FOUND then
2486: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3701);
2487: end;
2488: --
2489: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 38);
2490: --
2491: begin
2492: delete from pay_element_entries_f e
2493: where e.assignment_id in (
2495: from per_assignments_f ass
2496: where ass.person_id = P_PERSON_ID);
2497: exception
2498: when NO_DATA_FOUND then
2499: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3801);
2500: end;
2501: --
2502: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 39);
2503: --
2498: when NO_DATA_FOUND then
2499: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3801);
2500: end;
2501: --
2502: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 39);
2503: --
2504: -- Rmonge Bug 1686922 22-FEB-2002
2505: -- Tax records were not being deleted. Therefore, there were orphans rows in
2506: -- the pay_us_fed_tax_rules_f, pay_us_state_tax_rules_f,
2514: from per_assignments_f ass
2515: where ass.person_id = p_person_id );
2516: exception
2517: when no_data_found then
2518: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3802);
2519: end;
2520:
2521: begin
2522:
2519: end;
2520:
2521: begin
2522:
2523: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 40);
2524:
2525: Delete pay_us_emp_state_tax_rules_f pest
2526: Where pest.assignment_id in (
2527: select ass.assignment_id
2528: from per_assignments_f ass
2529: where ass.person_id = p_person_id );
2530: exception
2531: when no_data_found then
2532: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3803);
2533: end;
2534:
2535: begin
2536:
2533: end;
2534:
2535: begin
2536:
2537: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 41);
2538:
2539: Delete pay_us_emp_county_tax_rules_f pect
2540: Where pect.assignment_id in (
2541: select ass.assignment_id
2542: from per_assignments_f ass
2543: where ass.person_id = p_person_id );
2544: exception
2545: when no_data_found then
2546: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3804);
2547: end;
2548:
2549: begin
2550:
2547: end;
2548:
2549: begin
2550:
2551: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 42);
2552:
2553: Delete pay_us_emp_city_tax_rules_f pecit
2554: Where pecit.assignment_id in (
2555: select ass.assignment_id
2556: from per_assignments_f ass
2557: where ass.person_id = p_person_id );
2558: exception
2559: when no_data_found then
2560: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3805);
2561: end;
2562:
2563: -- Finished, now unlock assignments and delete them.
2564: --
2563: -- Finished, now unlock assignments and delete them.
2564: --
2565: close LOCK_ASSIGNMENT_ROWS;
2566: --
2567: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 43);
2568: --
2569: begin
2570: delete from per_all_assignments_f a
2571: where a.person_id = P_PERSON_ID;
2570: delete from per_all_assignments_f a
2571: where a.person_id = P_PERSON_ID;
2572: exception
2573: when NO_DATA_FOUND then
2574: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4001);
2575: end;
2576: --
2577: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 44);
2578: --
2573: when NO_DATA_FOUND then
2574: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4001);
2575: end;
2576: --
2577: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 44);
2578: --
2579: begin
2580: delete from per_periods_of_service p
2581: where p.person_id = P_PERSON_ID;
2580: delete from per_periods_of_service p
2581: where p.person_id = P_PERSON_ID;
2582: exception
2583: when NO_DATA_FOUND then
2584: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4101);
2585: end;
2586: --
2587: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 45);
2588: --
2583: when NO_DATA_FOUND then
2584: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4101);
2585: end;
2586: --
2587: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 45);
2588: --
2589: begin
2590: delete from per_applications a
2591: where a.person_id = P_PERSON_ID;
2590: delete from per_applications a
2591: where a.person_id = P_PERSON_ID;
2592: exception
2593: when NO_DATA_FOUND then
2594: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4201);
2595: end;
2596: --
2597: -- 03/18/98 Bug #642566
2598: -- delete per_people_extra_info records
2595: end;
2596: --
2597: -- 03/18/98 Bug #642566
2598: -- delete per_people_extra_info records
2599: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 46);
2600: --
2601: begin
2602: delete from per_people_extra_info e
2603: where e.person_id = P_PERSON_ID;
2602: delete from per_people_extra_info e
2603: where e.person_id = P_PERSON_ID;
2604: exception
2605: when NO_DATA_FOUND then
2606: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4301);
2607: end;
2608: -- 03/18/98 Change Ends
2609: --
2610: -- 03/18/98 Change Ends
2610: -- 03/18/98 Change Ends
2611: --
2612: -- 28/5/98
2613: -- Add delete from per_person_type_usages_f
2614: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',47);
2615:
2616: for ptu_rec in c_ptu loop
2617:
2618: select min(ptu1.effective_start_date)
2655:
2656: end loop;
2657: --
2658: -- delete per_person_dlvry_methods
2659: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 48);
2660: --
2661: begin
2662: delete from per_person_dlvry_methods
2663: where person_id = P_PERSON_ID;
2666: end;
2667: --
2668: -- Added this delete for quickhire checklists
2669: --
2670: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 49);
2671: begin
2672: delete from per_checklist_items
2673: where person_id = P_PERSON_ID;
2674: exception
2677: --
2678: -- End addition for quickhire checklists
2679: --
2680: -- delete per_qualification and per_subjects_taken records
2681: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 50);
2682: --
2683: begin
2684:
2685: --PMFLETCH Added delete from tl table
2690: where q.person_id = P_PERSON_ID
2691: and s.qualification_id = q.qualification_id
2692: );
2693:
2694: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4698);
2695:
2696:
2697: delete from per_subjects_taken s
2698: where s.qualification_id in ( select qualification_id
2698: where s.qualification_id in ( select qualification_id
2699: from per_qualifications
2700: where person_id = P_PERSON_ID );
2701:
2702: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4699);
2703:
2704: --PMFLETCH Added delete from tl table
2705: delete from per_qualifications_tl qt
2706: where qt.qualification_id in ( select q.qualification_id
2707: from per_qualifications q
2708: where q.person_id = P_PERSON_ID
2709: );
2710:
2711: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4700);
2712:
2713: delete from per_qualifications q
2714: where q.person_id = P_PERSON_ID;
2715:
2714: where q.person_id = P_PERSON_ID;
2715:
2716: exception
2717: when NO_DATA_FOUND then
2718: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4701);
2719: end;
2720: --
2721:
2722: close LOCK_PERSON_ROWS;
2720: --
2721:
2722: close LOCK_PERSON_ROWS;
2723: --
2724: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 99);
2725: --
2726: begin
2727: delete from per_all_people_f
2728: where person_id = P_PERSON_ID;
2727: delete from per_all_people_f
2728: where person_id = P_PERSON_ID;
2729: exception
2730: when NO_DATA_FOUND then
2731: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4401);
2732: end;
2733: --
2734: -- Now remove contracts
2735: --
2737: P_PERSON_ID,
2738: NULL,
2739: NULL);
2740: --
2741: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 100);
2742: --
2743: -- Now remove Medical Assessments
2744: --
2745: FOR mea_rec IN medical_assessment_records LOOP
2750: ,mea_rec.object_version_number);
2751: --
2752: END LOOP;
2753: --
2754: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 110);
2755: --
2756: --
2757: -- Now remove disabilities
2758: --
2762: EXIT when DISABILITIES%NOTFOUND;
2763: 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);
2764: END LOOP;
2765: --
2766: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 120);
2767: --
2768: --
2769: -- Now remove Work incidences
2770: --
2774: EXIT when WORK_INCIDENTS%NOTFOUND;
2775: per_work_incident_api.delete_work_incident(false,v_incident_id, v_object_version_number);
2776: END LOOP;
2777: --
2778: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 130);
2779: --
2780: --
2781: -- Now remove Supplementary Roles
2782: --
2786: EXIT when ROLES%notfound;
2787: per_supplementary_role_api.delete_supplementary_role(false, v_role_id, v_ovn_roles);
2788: END LOOP;
2789: --
2790: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 150);
2791: --
2792: --
2793: begin
2794: delete from per_periods_of_placement p
2794: delete from per_periods_of_placement p
2795: where p.person_id = P_PERSON_ID;
2796: exception
2797: when NO_DATA_FOUND then
2798: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4501);
2799: end;
2800: --
2801: --
2802: --
2833: BEGIN
2834: --
2835: --
2836: if P_FORM_CALL = FALSE then
2837: hr_person_delete.person_existance_check(P_PERSON_ID);
2838: end if;
2839: --
2840: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 1);
2841: --
2836: if P_FORM_CALL = FALSE then
2837: hr_person_delete.person_existance_check(P_PERSON_ID);
2838: end if;
2839: --
2840: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 1);
2841: --
2842: open LOCK_PERSON_ROWS;
2843: --
2844: -- Now start cascade.
2850: ben_person_delete.delete_ben_rows(p_person_id);
2851: --
2852: -- End of Fix for WWBUG 1294400
2853: --
2854: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 2);
2855: --
2856: hr_security.delete_per_from_list(P_PERSON_ID);
2857: --
2858: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 4);
2854: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 2);
2855: --
2856: hr_security.delete_per_from_list(P_PERSON_ID);
2857: --
2858: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 4);
2859: --
2860: -- Lock assignments now, delete at end.
2861: -- Can select into a variable as max one assignment should exist (as
2862: -- strong_predel_validation has already been performed).
2871: exception
2872: when NO_DATA_FOUND then null;
2873: end;
2874: --
2875: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 5);
2876: --
2877: begin
2878: delete from pay_personal_payment_methods p
2879: where p.assignment_id = V_ASSIGNMENT_ID;
2880: exception
2881: when NO_DATA_FOUND then null;
2882: end;
2883: --
2884: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 6);
2885: --
2886: begin
2887: delete from per_assignment_budget_values_f v
2888: where v.assignment_id = V_ASSIGNMENT_ID;
2889: exception
2890: when NO_DATA_FOUND then null;
2891: end;
2892: --
2893: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 7);
2894: --
2895: begin
2896: delete from per_addresses a
2897: where a.person_id = P_PERSON_ID;
2898: exception
2899: when NO_DATA_FOUND then null;
2900: end;
2901: --
2902: hr_utility.set_location('HR_PERSON_DELETE.DELETE_DEFAULT_DELETES', 7.5);
2903: --
2904: begin
2905: delete from per_phones a
2906: where a.parent_id = P_PERSON_ID
2908: exception
2909: when NO_DATA_FOUND then null;
2910: end;
2911: --
2912: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 8);
2913: --
2914: begin
2915: delete from pay_cost_allocations_f a
2916: where a.assignment_id = V_ASSIGNMENT_ID;
2917: exception
2918: when NO_DATA_FOUND then null;
2919: end;
2920: --
2921: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 9);
2922: --
2923: begin
2924: delete from pay_element_entry_values_f v
2925: where exists (
2930: exception
2931: when NO_DATA_FOUND then null;
2932: end;
2933: --
2934: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 10);
2935: --
2936: begin
2937: delete from pay_run_results r
2938: where r.source_type = 'E'
2944: exception
2945: when NO_DATA_FOUND then null;
2946: end;
2947: --
2948: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 11);
2949: --
2950: begin
2951: delete from pay_element_entries_f e
2952: where e.assignment_id = V_ASSIGNMENT_ID;
2953: exception
2954: when NO_DATA_FOUND then null;
2955: end;
2956: --
2957: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 12);
2958: --
2959: -- No exception, should succeed.
2960: --
2961: begin
2962: delete from per_assignments_f ass
2963: where ass.assignment_id = V_ASSIGNMENT_ID;
2964: end;
2965: --
2966: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 13);
2967: --
2968: begin
2969: delete from per_periods_of_service p
2970: where p.person_id = P_PERSON_ID;
2971: exception
2972: when NO_DATA_FOUND then null;
2973: end;
2974: --
2975: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 14);
2976: --
2977: begin
2978: delete from per_applications a
2979: where a.person_id = P_PERSON_ID;
2982: end;
2983: --
2984: -- Added this delete for quickhire checklists
2985: --
2986: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 17);
2987: begin
2988: delete from per_checklist_items
2989: where person_id = P_PERSON_ID;
2990: exception
2993: --
2994: -- End addition for quickhire checklists
2995: --
2996: --
2997: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 15);
2998: --
2999: close LOCK_PERSON_ROWS;
3000: --
3001: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 16);
2997: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 15);
2998: --
2999: close LOCK_PERSON_ROWS;
3000: --
3001: hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 16);
3002: --
3003: begin
3004: delete from per_people_f
3005: where person_id = P_PERSON_ID;
3047: BEGIN
3048: --
3049: --
3050: if P_FORM_CALL = FALSE then
3051: hr_person_delete.person_existance_check(P_PERSON_ID);
3052: end if;
3053: --
3054: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 1);
3055: --
3050: if P_FORM_CALL = FALSE then
3051: hr_person_delete.person_existance_check(P_PERSON_ID);
3052: end if;
3053: --
3054: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 1);
3055: --
3056: open LOCK_PERSON_ROWS;
3057: --
3058: -- Now start cascade.
3056: open LOCK_PERSON_ROWS;
3057: --
3058: -- Now start cascade.
3059: --
3060: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 3);
3061: --
3062: begin
3063: delete from per_person_list l
3064: where l.person_id = P_PERSON_ID;
3065: exception
3066: when NO_DATA_FOUND then null;
3067: end;
3068: --
3069: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 5);
3070: --
3071: -- Can select into a variable as only one assignment should exist (as
3072: -- strong_predel_validation has already been performed).
3073: --
3078: where ass.person_id = P_PERSON_ID
3079: FOR UPDATE;
3080: end;
3081: --
3082: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 6);
3083: --
3084: begin
3085: delete from per_addresses a
3086: where a.person_id = P_PERSON_ID;
3087: exception
3088: when NO_DATA_FOUND then null;
3089: end;
3090: --
3091: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 6.5);
3092: --
3093: begin
3094: delete from per_phones a
3095: where a.parent_id = P_PERSON_ID
3097: exception
3098: when NO_DATA_FOUND then null;
3099: end;
3100: --
3101: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 7);
3102: --
3103: begin
3104: delete from per_assignments_f ass
3105: where ass.assignment_id = V_ASSIGNMENT_ID;
3104: delete from per_assignments_f ass
3105: where ass.assignment_id = V_ASSIGNMENT_ID;
3106: end;
3107: --
3108: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 8);
3109: --
3110: begin
3111: delete from per_applications a
3112: where a.person_id = P_PERSON_ID;
3115: end;
3116: --
3117: -- Added this delete for quickhire checklists
3118: --
3119: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 15);
3120: begin
3121: delete from per_checklist_items
3122: where person_id = P_PERSON_ID;
3123: exception
3129: --
3130: --
3131: -- Added this delete for PTU
3132: --
3133: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 16);
3134: begin
3135: delete from per_person_type_usages_f
3136: where person_id = P_PERSON_ID;
3137: exception
3140: --
3141: -- End addition for PTU
3142: --
3143: --
3144: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3145: --
3146: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3147: --
3148: close LOCK_PERSON_ROWS;
3142: --
3143: --
3144: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3145: --
3146: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3147: --
3148: close LOCK_PERSON_ROWS;
3149: --
3150: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 10);
3146: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3147: --
3148: close LOCK_PERSON_ROWS;
3149: --
3150: hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 10);
3151: --
3152: begin
3153: delete from per_people_f
3154: where person_id = P_PERSON_ID;
3158: --
3159: END applicant_default_deletes;
3160: -------------------- END: applicant_default_deletes -----------------------
3161: --
3162: end hr_person_delete;