1 PACKAGE BODY hr_person_delete AS
2 /* $Header: peperdel.pkb 120.6.12000000.2 2007/04/05 08:29:10 pdkundu noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 11 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : hr_person (HEADER)
24
25 Description : This package declares procedures required to DELETE
26 people on Oracle Human Resources. Note, this
27 does not include extra validation provided by calling programs (such
28 as screen QuickPicks and HRLink validation) OR that provided by use
29 of constraints and triggers held against individual tables.
30 Change List
31 -----------
32
33 Version Date Author ER/CR No. Description of Change
34 -------+---------+----------+---------+--------------------------
35 70.0 10-AUG-93 TMATHERS Date Created
36 80.0 Removed these functions from
37 peperson, all were created
38 Pbarry.
39
40 80.1 19-Oct-93 JRhodes 1. Added extra check in weak_predel_val..
41 and strong_predel_val.. to ensure
42 that a person cannot be deleted if
43 they have a contact who has COBRA
44 enrollments related to the Persons
45 assignment
46
47 2. Added extra delete statements in
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:
57 (1) stop selecting oracle_username
58 as it's no longer needed
59 (2) add 'distinct' to select clause
60 to avoid ORA-1422 when products
61 installed multiple times
62 (3) don't return 'Y' if install status
63 is 'S'
64 - wwbug 210697
65 80.5 (70.8) 11-AUG-94 JRHODES #226211 Changed del from pay_element_entries_f
66 to improve performance
67 70.9 01-FEB-95 JRHODES Delete PER_PAY_PROPOSALS (G1773)
68 70.10 06-APR-95 JRHODES Bug 271369
69 Use effective_start_date in
70 closed_element_entry_check
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.
80 70.14 03-Jul-96 SXShah Added call to ota_predel_per_validation
81 to moderate_predel_validation procedure.
82 70.15 10-Jul-96 AMills Bug 349818.
83 Added cursor DELETE_COMPONENTS
84 to procedure delete_a_person
85 to delete per_pay_proposal_components
86 in the case of a multi-component
87 proposal, to maintain referential
88 integrity. The original delete from
89 per_pay_proposals follows as before.
90 70.16 22-Oct-96 Ty Hayden Added section to delete phones.
91 70.17 06-Nov-96 BSanders 410245 Performance modifications to delete
92 statements replacing the exists statement
93 with 'in' to prevent full table scan of
94 delete table.
95 70.18 14-Nov-96 BSanders 410245 Previous fix had to be applied to version 70.12
96 70.19 14-Nov-96 BSanders 410245 Reinstate 70.17
97 110.01 25-Aug-97 mswanson Remove and aol.person_type = 'E' from
98 aol_predel_validation.
99 110.2 13-Oct-97 rfine 563034 Changed parent table name from
100 PER_PEOPLE_F to PER_ALL_PEOPLE_F
101 110.3 06-Nov-97 mmillmor 593864 Added pa validation to moderate delete
102
103 110.4 18-Mar-98 fychu 642566 Added code to delete_a_person procedure
104 to delete per_people_extra_info records.
105 110.5 25-MAR-1998 mmillmor Added delete from per_performance_reviews for
106 (in dynamic sql because table
107 may not exist for some clients)
108 110.6 16-APR-1998 SASmith Change to table from
109 per_assignment_budget_values to
110 per_assignment_budget_values_f. Required
111 as table changed to being a datetrack
112 table.
113 115.2 12-Jun-98 M Bocutt Added call to maintain_ptu to delete
114 PTU records when person deleted.
115 115.3 13-Aug-98 I Harding Delete records from the table
116 per_person_dlvry_methods
117 115.4 07-Sep-98 smcmilla Added to delete_a_person to delete from
118 hr_quest_answer_values,
119 hr_quest_answers,
120 per_appraisals,
121 per_participants.
122 115.5 08-SEP-98 smcmilla Disallow delete if orws exist in
123 PER_APPRAISALS or PER_PARTICIPANTS.
124 Change made to moderate_predel...
125 115.6 17-MAR-1999 mmillmor 814301 Altered DELETE_A_PERSON to delete from
126 per_all_people_f and per_all_assignments_f
127 115.7 18-MAR-98 CColeman Added WIP pre-del validation.
128 115.8 13-APR-99 CCarter 800050 Added pre-del validation for the
129 following products ENG, AP, FA, PO
130 and RCV.
131 115.9 23-JUL-99 Asahay 941591 Modified v_dummy definition to number(3)
132 115.10 22-Nov-99 Rvydyana Added delete of per_checklist_items
133 115.11 10-Mar-00 I Harding Added contracts_check and called it
134 from weak_predel and moderate_predel.
135 Added callto hr_contract_api.maintain_contracts
136 in delete_a_person to remove contracts.
137 115.12 15-MAR-00 CDickinson Added a call to the Work Incident api and
138 Disability api to delete any child records
139 115.13 26-JUL-00 CCarter Added a call to the Roles api to delete
140 any child records.
141 115.16 19-SEP-2000 VTreiger Added P_SESSION_DATE to validations to fix
142 bug 1403481.
143 115.17 31-OCT-2000 GPERRY Fixed WWBUG 1294400.
144 Added in checks for benefits.
145 115.18 20-DEC-2000 MGettins Changed Delete_A_person to now also delete
146 From PER_MEDICAL_ASSESSMENTS.
147 115.19 08-Aug-2001 Tmathers Added new weak_predel checks when called
148 from delete person screen.
149 115.20 24-AUG-2001 ASahay Replaced maintain_ptu with
150 delete_person_type_usage
151 115.21 30-Aug-2001 rvydyana added PTU call for default applicant delete
152 note : no ptu delete reqd in default
153 person delete procedure as it is not used
154 115.22 06-AUG-2001 rvydyana 1844844 Added per_qualifications to the delete
155 list.
156 115.23 22-FEB-2002 rmonge 1686922 Added code to delete tax records from
157 pay_us_emp_fed_tax_rules_f
158 pay_us_emp_state_tax_rules_f
159 pay_us_emp_county_tax_rules_f
160 pay_us_emp_city_tax_rules_f
161 The deletion process was leaving
162 orphan rows.
163 115.24 02-DEC-2002 eumenyio added the nocopy compiler and also
164 the WHENEVER OSERROR EXIT FAILURE
165 ROLLBACK
166 115.25 03-DEC-2002 pmfletch MLS Added delete from per_subjects_taken_tl
167 for MLS
168 115.26 03-DEC-2002 pmfletch MLS Added delete from per_qualifications_tl
169 for MLS
170 115.27 16-JUL-2003 jpthomas 3026024 Added delete from ben_covered_dependents_f.
171 Delete the entry in the above table for the
172 contact person whom is getting deleted.
173 115.29 Sep-2003 mbocutt ex-person security enhancements.
174 Remove refs to per_person_list_changes.
175 This file is now dependent on other
176 security changes delivered in Nov 2003 FP.
177 115.30 10-OCT-2003 njaladi 3183868 Removed the per_periods_of_placement
178 validation Code in procedure
179 moderate_pre_del_validation as this check
180 is not required for CWK.
181 115.31 12-May-2004 bdivvela 3619599 Modified delete queries on tables
182 hr_quest_answer_values and hr_quest_answers
183 in delete_a_person procedure
184 115.32 07-Jun-2004 sbuche 3598568 Modified delete_a_person and people_default_deletes
185 procedures to call hr_security.delete_per_from_list
186 procedure for deleting a record in static list
187 instead of deleting it directly from unsecured
188 table per_person_list.
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.
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
206 Rewrote Delete from HR_QUEST_ANSWER_VALUES
207 to avoid a cartesian join and a full
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
217 to add exception handlers.
218 115.44 20-Nov-2006 risgupta 5464252 Modified the fix done in 115.43 in
219 procedure delete_a_person to put exception
220 at correct place.
221 115.45 05-APR-2007 pdkundu 5945972 Modified the call to Validation for OTA.
222
223 ========================================================================= */
224 --
225 -------------------- BEGIN: product_installed ------------------------------
226 /*
227 NAME
228 product_installed
229 DESCRIPTION
230 Returns 'Y' if this product is installed, 'N' if not in p_yes_no
231 and the ORACLEID of the application in p_oracle_username.
232 */
233 --
234 PROCEDURE product_installed (p_application_short_name IN varchar2,
235 p_status OUT NOCOPY varchar2,
236 p_yes_no OUT NOCOPY varchar2,
237 p_oracle_username OUT NOCOPY varchar2)
238 IS
239 --
240 BEGIN
241 --
242 p_yes_no := 'N';
243 p_oracle_username := 'DUMMY';
244 --
245 begin
246 select 'Y',
247 fpi.status
248 into p_yes_no,
249 p_status
250 from fnd_product_installations fpi
251 where fpi.status = 'I'
252 and fpi.application_id =
253 (select fa.application_id
254 from fnd_application fa
255 where fa.application_short_name = P_APPLICATION_SHORT_NAME
256 );
257 exception
258 when NO_DATA_FOUND then null;
259 end;
260 --
261 END product_installed;
262 -------------------- END: product_installed --------------------------------
263 --
264 -------------------- BEGIN: person_existance_check -------------------------
265 /*
266 NAME
267 person_existance_check
268 DESCRIPTION
269 Raises error (and hence falls right out of package) if this person does
270 not exist.
271 */
272 --
273 PROCEDURE person_existance_check (p_person_id number)
274 IS
275 --
276 v_dummy number(15);
277 --
278 BEGIN
279 select count(*)
280 into v_dummy
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;
290 END person_existance_check;
291 -------------------- END: person_existance_check ---------------------------
292 --
293 -------------------- BEGIN: pay_predel_validation --------------------------
294 /*
295 NAME
296 pay_predel_validation
297 DESCRIPTION
298 Ensures that there are no assignments actions for this person other than
299 Purge actions. If there are then raise an error and disallow delete.
300 */
301 --
302 PROCEDURE pay_predel_validation (p_person_id number)
303 IS
304 --
305 v_delete_permitted varchar2(1);
306 --
307 BEGIN
308 --
309 begin
310 select 'Y'
311 into v_delete_permitted
312 from sys.dual
313 where not exists (
314 select null
315 from pay_assignment_actions paa,
316 per_assignments_f ass,
317 pay_payroll_actions ppa
318 where paa.assignment_id = ass.assignment_id
319 and ass.person_id = P_PERSON_ID
320 and ppa.payroll_action_id = paa.payroll_action_id
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;
330 -------------------- END: pay_predel_validation ---------------------------
331 --
332 -- Start of Fix for WWBUG 1294400
333 -------------------- BEGIN: ben_predel_validation --------------------------
334 /*
335 NAME
336 ben_predel_validation
337 DESCRIPTION
338 Ensures that there are no open life events for a person.
339 */
340 --
341 PROCEDURE ben_predel_validation (p_person_id NUMBER
342 ,p_effective_date DATE)
343 IS
344 --
345 --
346 BEGIN
347 --
348 ben_person_delete.check_ben_rows_before_delete(p_person_id
349 ,p_effective_date);
350 --
351 END ben_predel_validation;
352 -------------------- END: ben_predel_validation ---------------------------
353 -- End of Fix for WWBUG 1294400
354 -------------------- BEGIN: aol_predel_validation -------------------------
355 /*
356 NAME
357 aol_predel_validation
358 DESCRIPTION
359 Foreign key reference check.
360 */
361 --
362 PROCEDURE aol_predel_validation (p_person_id number)
363 IS
364 --
365 v_delete_permitted varchar2(1);
366 --
367 BEGIN
368 --
369 begin
370 select 'Y'
371 into v_delete_permitted
372 from sys.dual
373 where not exists (
374 select null
375 from fnd_user aol
376 where aol.employee_id = P_PERSON_ID
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;
386 -------------------- END: aol_predel_validation -------------------------
387 --
388 -------------------- BEGIN: assignment_set_check ----------------------------
389 /*
390 NAME
391 assignment_set_check
392 DESCRIPTION
393 Sets error code and status if this person has any assignments which are
394 the only ones in an assignment set and where that assginment is included.
395 */
396 PROCEDURE assignment_set_check (p_person_id IN number)
397 IS
398 --
399 v_delete_permitted varchar2(1);
400 --
401 BEGIN
402 select 'Y'
403 into v_delete_permitted
404 from sys.dual
405 where not exists (
406 select null
407 from per_assignments_f ass,
408 hr_assignment_set_amendments asa
409 where asa.assignment_id = ass.assignment_id
410 and ass.person_id = P_PERSON_ID
411 and asa.include_or_exclude = 'I'
412 and not exists (
413 select null
414 from hr_assignment_set_amendments asa2
415 where asa2.assignment_set_id = asa.assignment_set_id
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 -------------------------
425 --
426 -------------------- BEGIN: closed_element_entry_check --------------------
427 /*
428 NAME
429 closed_element_entry_check
430 DESCRIPTION
431 Check that for any element entries that are about to be deleted, the
432 element type is not closed for the duration of that entry. Also check
433 that if the assignment is to a payroll, the payroll period is not closed.
434 If any of these 2 checks fail, the delete is disallowed.
435 */
436 --
437 PROCEDURE closed_element_entry_check (p_person_id IN number,
438 p_session_date IN date)
439 IS
440 --
441 cursor THIS_PERSONS_ELEMENT_ENTRIES is
442 select l.element_type_id,
443 e.effective_start_date,
444 e.effective_end_date,
445 a.assignment_id
446 from pay_element_entries_f e,
447 per_assignments_f a,
448 pay_element_links_f l
449 where a.person_id = P_PERSON_ID
450 and a.assignment_id = e.assignment_id
451 and e.effective_start_date between
452 a.effective_start_date and a.effective_end_date
453 and e.element_link_id = l.element_link_id
454 and e.effective_start_date between
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,
464 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 --
474 -------------------- BEGIN: contact_cobra_validation -----------------------
475 /*
476 NAME
477 contact_cobra_validation
478 DESCRIPTION
479 Searches for any contacts of the person being deleted who have
480 COBRA Coverage Enrollments which are as a result of the Persons
481 Assignments.
482 */
483 --
484 PROCEDURE contact_cobra_validation (p_person_id number)
485 IS
486 --
487 v_delete_permitted varchar2(1);
488 --
489 BEGIN
490 --
491 begin
492 select 'Y'
493 into v_delete_permitted
494 from sys.dual
495 where not exists (
496 select null
497 from per_assignments_f a
498 , per_contact_relationships c
499 , per_cobra_cov_enrollments e
500 where a.person_id = P_PERSON_ID
501 and a.assignment_id = e.assignment_id
502 and c.person_id = P_PERSON_ID
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;
512 -------------------- END: pay_predel_validation ---------------------------
513 -------------------- BEGIN: contracts_check -----------------------
514 /*
515 NAME
516 contracts_check
517 DESCRIPTION
518 Raise an error if related contracts exist for the given person.
519 */
520 --
521 PROCEDURE contracts_check (p_person_id number)
522 IS
523 --
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
533 --
534 select null
535 into v_delete_permitted
536 from sys.dual
537 where not exists(select null
538 from per_contracts_f
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
548 --
549 -------------------- BEGIN: weak_predel_validation -------------------------
550 /*
551 NAME
552 weak_predel_validation
553 DESCRIPTION
554 Validates whether a person can be deleted from the HR database.
555 This is the weak validation performed prior to delete using the
556 Delete Person form.
557 */
558 --
559 PROCEDURE weak_predel_validation (p_person_id IN number,
560 p_session_date IN date,
561 p_dt_delete_mode IN varchar2) -- 4169275
562 IS
563 --
564 -- DECLARE THE LOCAL VARIABLES
565 --
566 v_pay_installed varchar2(1);
567 v_pay_status varchar2(1);
568 v_ben_installed varchar2(1);
569 v_ben_status varchar2(1);
570 v_oracle_id varchar2(30);
571 v_delete_permitted varchar2(1);
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);
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
590 --
591 if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
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);
601 --
602 if (v_pay_installed = 'Y') then
603 hr_person_delete.pay_predel_validation(P_PERSON_ID);
604 end if;
605 --
606 -- Removed check for ben install
607 -- as OSB can now have enrollment results
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);
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);
625 --
626
627 END weak_predel_validation;
628 -------------------- END: weak_predel_validation --------------------------
629 --
630 --
631 -------------------- BEGIN: moderate_predel_validation ---------------------
632 /*
633 NAME
634 moderate_predel_validation
635 DESCRIPTION
636 Moderate pre-delete validation called from the Stong_predel_validation
637 procedure and HR API's.
638
639 */
640 PROCEDURE moderate_predel_validation (p_person_id IN number,
641 p_session_date IN date,
642 p_dt_delete_mode IN varchar2) -- 4169275
643
644 IS
645 v_delete_permitted varchar2(1);
646 --
647 -- Bug 3524713 Starts Here
648 CURSOR ben_ext_chg_log (
649 p_person_id NUMBER
650 ) IS
651 SELECT ext_chg_evt_log_id
652 FROM ben_ext_chg_evt_log
653 WHERE person_id = p_person_id
654 FOR UPDATE OF ext_chg_evt_log_id;
655 --
656 l_id NUMBER;
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);
666 --
667 --
668 -- 4169275 start
669 -- During deletion of next change or future change need not require
670 -- this validation
671 --
672 if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
673 hr_person_delete.aol_predel_validation(P_PERSON_ID);
674 end if;
675 -- 4169275 end
676 --
677 --
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.
687 -- Select statement modified to improve performance.
688
689 select 'Y'
690 into v_delete_permitted
691 from sys.dual
692 where not exists (
693 select null
694 from per_letter_request_lines r
695 where r.person_id = P_PERSON_ID
696 and r.date_from >= P_SESSION_DATE );
697
698 select 'Y'
699 into v_delete_permitted
700 from sys.dual
701 where not exists (
702 select null
703 from per_letter_request_lines r
704 where exists (
705 select null
706 from per_assignments_f a
707 where a.person_id = P_PERSON_ID
708 and a.effective_start_date >= P_SESSION_DATE
709 and a.assignment_id = r.assignment_id));
710
711
712 /* select 'Y'
713 into v_delete_permitted
714 from sys.dual
715 where not exists (
716 select null
717 from per_letter_request_lines r
718 where r.person_id = P_PERSON_ID
719 and r.date_from >= P_SESSION_DATE
720 or exists (
721 select null
722 from per_assignments_f a
723 where a.person_id = P_PERSON_ID
724 and a.effective_start_date >= P_SESSION_DATE
725 and a.assignment_id = r.assignment_id));*/
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);
735 --
736 begin
737 select 'Y'
738 into v_delete_permitted
739 from sys.dual
740 where not exists (
741 select null
742 from per_contact_relationships r
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);
752 --
753 begin
754 select 'Y'
755 into v_delete_permitted
756 from sys.dual
757 where not exists (
758 select null
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);
768 --
769 begin
770 select 'Y'
771 into v_delete_permitted
772 from sys.dual
773 where not exists (
774 select null
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);
784 --
785 -- VT 1403481 09/19/00
786 begin
787 select 'Y'
788 into v_delete_permitted
789 from sys.dual
790 where 1 >= (
791 select count(*)
792 from per_assignments_f a
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);
802 --
803 begin
804 select 'Y'
805 into v_delete_permitted
806 from sys.dual
807 where not exists (
808 select null
809 from per_assignments_f a
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);
819 --
820 begin
821 select 'Y'
822 into v_delete_permitted
823 from sys.dual
824 where not exists (
825 select null
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);
835 --
836 begin
837 select 'Y'
838 into v_delete_permitted
839 from sys.dual
840 where not exists (
841 select null
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);
851 --
852 begin
853 select 'Y'
854 into v_delete_permitted
855 from sys.dual
856 where not exists (
857 select null
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);
867 --
868 begin
869 select 'Y'
870 into v_delete_permitted
871 from sys.dual
872 where not exists (
873 select null
874 from per_absence_attendances a
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);
884 --
885 begin
886 select 'Y'
887 into v_delete_permitted
888 from sys.dual
889 where not exists (
890 select null
891 from per_recruitment_activities r
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);
901 begin
902 select 'Y'
903 into v_delete_permitted
904 from sys.dual
905 where not exists (
906 select null
907 from per_appraisals apr
908 where apr.appraisee_person_id = P_PERSON_ID
909 or apr.appraiser_person_id = P_PERSON_ID);
910 exception
911 when NO_DATA_FOUND then
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
921 where not exists (
922 select null
923 from per_participants par
924 where par.person_id = P_PERSON_ID);
925 exception
926 when NO_DATA_FOUND then
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
936 from sys.dual
937 where not exists (
938 select null
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);
948 --
949 begin
950 select 'Y'
951 into v_delete_permitted
952 from sys.dual
953 where not exists (
954 select null
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);
964 --
965 -- Any discretionary link element entries?
966 --
967 begin
968 select 'Y'
969 into v_delete_permitted
970 from sys.dual
971 where not exists (
972 select null
973 from pay_element_entries_f e,
974 per_assignments_f a,
975 pay_element_links_f l
976 where a.person_id = P_PERSON_ID
977 and a.assignment_id = e.assignment_id
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);
987 --
988 -- Any entry adjustments, overrides etc.?
989 -- (We cannot capture manual enty of standard link entries)
990 --
991 begin
992 select 'Y'
993 into v_delete_permitted
994 from sys.dual
995 where not exists (
996 select null
997 from pay_element_entries_f e,
998 per_assignments_f a
999 where a.person_id = P_PERSON_ID
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);
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);
1013 --
1014 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 18);
1015 --
1016 begin
1017 select 'Y'
1018 into v_delete_permitted
1019 from sys.dual
1020 where not exists (
1021 select null
1022 from per_assignment_extra_info i
1023 where exists (
1024 select null
1025 from per_assignments_f a
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);
1035 --
1036 begin
1037 select 'Y'
1038 into v_delete_permitted
1039 from sys.dual
1040 where not exists (
1041 select null
1042 from per_secondary_ass_statuses s
1043 where exists (
1044 select null
1045 from per_assignments_f a
1046 where a.person_id = P_PERSON_ID
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);
1056 --
1057 begin
1058 select 'Y'
1059 into v_delete_permitted
1060 from sys.dual
1061 where not exists (
1062 select null
1063 from per_events e
1064 where exists (
1065 select null
1066 from per_assignments_f a
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);
1076 --
1077 begin
1078 select 'Y'
1079 into v_delete_permitted
1080 from sys.dual
1081 where not exists (
1082 select null
1083 from per_spinal_point_placements_f p
1084 where exists (
1085 select null
1086 from per_assignments_f a
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);
1096 --
1097 begin
1098 select 'Y'
1099 into v_delete_permitted
1100 from sys.dual
1101 where not exists (
1102 select null
1103 from per_quickpaint_result_text t
1104 where exists (
1105 select null
1106 from per_assignments_f a
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);
1116 --
1117 begin
1118 select 'Y'
1119 into v_delete_permitted
1120 from sys.dual
1121 where not exists (
1122 select null
1123 from per_cobra_cov_enrollments c
1124 where exists (
1125 select null
1126 from per_assignments_f a
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);
1136 --
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 (
1146 p_person_id
1147 );
1148 --
1149 LOOP
1150 FETCH ben_ext_chg_log INTO l_id;
1151 EXIT WHEN ben_ext_chg_log%NOTFOUND;
1152 DELETE FROM ben_ext_chg_evt_log
1153 WHERE CURRENT OF ben_ext_chg_log;
1154 END LOOP;
1155 CLOSE ben_ext_chg_log;
1156 -- Bug 3524713 Ends Here
1157 --
1158 ben_person_delete.perform_ri_check(p_person_id);
1159 --
1160 -- Validation for OTA.
1161 --Added for bug 5945972
1162
1163 if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
1164 per_ota_predel_validation.ota_predel_per_validation(P_PERSON_ID);
1165 end if;
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
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
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
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
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
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
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);
1211 --
1212 --
1213 end moderate_predel_validation;
1214 -------------------- END: moderate_predel_validation ---------------------
1215 --
1216 --
1217 -------------------- BEGIN: strong_predel_validation ---------------------
1218 /*
1219 NAME
1220 strong_predel_validation
1221 DESCRIPTION
1222 Called from PERREAQE and PERPEEPI. It performs many checks
1223 to find if additional data has been entered for this person. It is
1224 more stringent than weak_predel_validation and ensures that this
1225 person only has the default data set up by entering a person, contact
1226 or applicant afresh onto the system.
1227 If additional data is found then the delete of this person from
1228 the calling module is invalid as it is beyond its scope. The Delete
1229 Person form should therefore be used (which only performs
1230 weak_predel_validation) if a delete really is required.
1231 p_person_mode - 'A' check for applicants
1232 'E' check for employees
1233 'O' check for other types
1234
1235 NOTE
1236 No validation is required for security (PER_PERSON_LIST* tables) as
1237 this is implicit for the person via assignment criteria. The
1238 rows in these tables can just be deleted.
1239 */
1240 PROCEDURE strong_predel_validation (p_person_id IN number,
1241 p_session_date IN date,
1242 p_dt_delete_mode IN varchar2) -- 4169275
1243 IS
1244 --
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 --
1254 -- VT 1403481 09/19/00
1255 select count(*)
1256 into v_person_types
1257 from per_people_f p,
1258 per_person_types ppt
1259 where p.person_id = P_PERSON_ID
1260 and p.effective_end_date >= P_SESSION_DATE
1261 and p.person_type_id = ppt.person_type_id
1262 and exists
1263 (select null
1264 from per_people_f p2,
1265 per_person_types ppt2
1266 where p2.person_id = p.person_id
1267 and p2.effective_end_date >= P_SESSION_DATE
1268 and p2.person_type_id = ppt2.person_type_id
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);
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
1282 ,p_session_date =>p_session_date
1283 ,p_dt_delete_mode => p_dt_delete_mode -- 4169275
1284 );
1285 end if;
1286 --
1287 END strong_predel_validation;
1288 -------------------- END: strong_predel_validation -----------------------
1289 --
1290 -------------------- BEGIN: check_contact ---------------------------------
1291 /*
1292 NAME
1293 check_contact
1294 DESCRIPTION
1295 Is this contact a contact for anybody else? If so then do nothing.
1296 If not then check if this person has ever been an employee or
1297 applicant. If they have not then check whether they have any extra
1298 info entered for them (other than default info). If they have not
1299 then delete this contact also. Otherwise do nothing.
1300 NOTES
1301 p_person_id non-contact in relationship
1302 p_contact_person_id contact in this relationship - the person
1303 who the check is performed against.
1304 p_contact_relationship_id relationship which is currently being
1305 considered for this contact.
1306 */
1307 --
1308 PROCEDURE check_contact (p_person_id IN number,
1309 p_contact_person_id IN number,
1310 p_contact_relationship_id IN number,
1311 p_session_date IN date)
1312 IS
1313 --
1314 v_contact_elsewhere varchar2(1);
1315 v_other_only varchar2(1);
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
1325 select 'Y'
1326 into v_contact_elsewhere
1327 from sys.dual
1328 where exists (
1329 select null
1330 from per_contact_relationships r
1331 where r.contact_relationship_id <> P_CONTACT_RELATIONSHIP_ID
1332 and r.contact_person_id = P_CONTACT_PERSON_ID);
1333 exception
1334 when NO_DATA_FOUND then null;
1335 end;
1336 --
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
1346 from sys.dual
1347 where not exists
1348 (select null
1349 from per_people_f p
1350 where p.person_id = P_CONTACT_PERSON_ID
1351 and p.current_emp_or_apl_flag = 'Y');
1352 exception
1353 when NO_DATA_FOUND then return;
1354 end;
1355 --
1356 begin
1357 --
1358 -- Can contact be deleted? If strong val errors then just trap
1359 -- error as we will continue as usual. If it succeeds then delete
1360 -- contact.
1361 --
1362 begin
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
1372 hr_person_delete.people_default_deletes(P_CONTACT_PERSON_ID,
1373 TRUE);
1374 end if;
1375 --
1376 end;
1377 --
1378 --
1379 END check_contact;
1380 -------------------- END: check_contact ---------------------------------
1381 --
1382 -------------------- BEGIN: delete_a_person --------------------------------
1383 /*
1384 NAME
1385 delete_a_person
1386 DESCRIPTION
1387 Validates whether a person can be deleted from the HR database.
1388 It is assumed that weak_predel_validation and the other application
1389 *_delete_person.*_predel_valdation procedures have been successfully
1390 completed first.
1391 Cascades are all performed according to the locking ladder.
1392 NOTE
1393 P_FORM_CALL is set to 'Y' if this procedure is called from a forms
1394 module. In this case, the deletes are performed post-delete and a
1395 row therefore may not exist in per_people_f (for this person_id).
1396 For this reason the existance check will be ignored.
1397 */
1398 --
1399 PROCEDURE delete_a_person (p_person_id IN number,
1400 p_form_call IN boolean,
1401 p_session_date IN date)
1402 IS
1403 --
1404 cursor THIS_PERSONS_CONTACTS is
1405 select contact_person_id,
1406 contact_relationship_id
1407 from per_contact_relationships
1408 where person_id = P_PERSON_ID;
1409 --
1410 cursor LOCK_PERSON_ROWS is
1411 select person_id
1412 from per_people_f
1413 where person_id = P_PERSON_ID
1414 FOR UPDATE;
1415 --
1416 cursor LOCK_ASSIGNMENT_ROWS is
1417 select assignment_id
1418 from per_assignments_f
1419 where person_id = P_PERSON_ID
1420 FOR UPDATE;
1421 --
1422 cursor DELETE_COMPONENTS is
1423 select pp.pay_proposal_id
1424 from per_pay_proposals pp,
1425 per_assignments_f pa
1426 where pa.person_id = P_PERSON_ID
1427 and pa.assignment_id = pp.assignment_id
1428 FOR UPDATE;
1429 --
1430 CURSOR medical_assessment_records IS
1431 SELECT medical_assessment_id,
1432 object_version_number
1433 FROM per_medical_Assessments pma
1434 WHERE pma.person_id = p_person_id;
1435 --
1436 cursor WORK_INCIDENTS is
1437 select incident_id, object_version_number
1438 from per_work_incidents
1439 where person_id = p_person_id;
1440 --
1441 cursor DISABILITIES is
1442 select disability_id, object_version_number, effective_start_date, effective_end_date
1443 from per_disabilities_f
1444 where person_id = p_person_id;
1445 --
1446 cursor ROLES is
1447 select role_id, object_version_number
1448 from per_roles
1449 where person_id= p_person_id;
1450
1451 cursor c_ptu is
1452 select distinct person_type_usage_id
1453 -- ,ptu.effective_start_date
1454 -- ,ptu.object_version_number
1455 from per_person_type_usages_f ptu
1456 where ptu.person_id = p_person_id
1457 order by person_type_usage_id;
1458
1459 --
1460 -- v_dummy varchar2(1);
1461 v_dummy number(3); /* Bug 941 591 and 4873360*/
1462 v_proposal_id number;
1463 v_review_cursor number;
1464 v_rows_processed number;
1465 v_incident_id per_work_incidents.person_id%TYPE;
1466 v_object_version_number per_work_incidents.object_version_number%TYPE;
1467 v_disability_id per_disabilities_f.disability_id%TYPE;
1468 v_object_version_no per_disabilities_f.object_version_number%TYPE;
1469 v_effective_start_date per_disabilities_f.effective_start_date%TYPE;
1470 v_effective_end_date per_disabilities_f.effective_end_date%TYPE;
1471 v_ovn_roles per_roles.object_version_number%TYPE;
1472 v_role_id per_roles.role_id%TYPE;
1473 --
1474 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
1475 l_effective_date per_person_type_usages_f.effective_start_date%TYPE;
1476 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
1477 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
1478 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
1479
1480 -- bug fix 3732129 starts here.
1481 -- to improve performance assignment id fetched into a pl/sql table.
1482
1483 type assignmentid is table of per_all_assignments_f.assignment_id%type index by binary_integer;
1484 l_assignment_id assignmentid;
1485
1486 Cursor c_asg is
1487 select distinct assignment_id
1488 from per_assignments_f
1489 where person_id = p_person_id;
1490 -- bug fix 3732129 ends here.
1491
1492 BEGIN
1493 --
1494 --
1495 --
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;
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
1513 open c_asg;
1514 fetch c_asg bulk collect into l_assignment_id;
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
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 --
1532 begin
1533 -- bug fix 3732129.
1534 -- Delete statement modified to improve performance.
1535
1536 delete from per_letter_request_lines l
1537 where l.person_id = P_PERSON_ID;
1538
1539 forall i in 1..l_assignment_id.count
1540 delete from per_letter_request_lines l
1541 where l.assignment_id = l_assignment_id(i);
1542
1543 /*
1544 delete from per_letter_request_lines l
1545 where l.person_id = P_PERSON_ID
1546 or exists (
1547 select null
1548 from per_assignments_f a
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
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;
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 --
1572 begin
1573 update per_absence_attendances a
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 --
1583 begin
1584 update per_absence_attendances a
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 --
1594 begin
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 --
1604 -- Delete of per_periods_of_service at end after delete of
1605 -- per_assignments_f.
1606 --
1607 begin
1608 update per_periods_of_service p
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 --
1618 begin
1619 update per_recruitment_activities r
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 --
1629 begin
1630 update per_recruitment_activities r
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 --
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
1643 -- PER_PARTICIPANTS, HR_QUEST_ANSWER_VALUES and PER_APPRAISALS
1644 --
1645 -- Broke query into two peices using conditional logic in a pl/sql block to
1646 -- see if delete needs to be run.
1647 --
1648 begin -- Delete from HR_QUEST_ANSWER_VALUES
1649 begin -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
1650 select 1
1651 into v_dummy
1652 from sys.dual
1653 where exists (
1654 select null
1655 from per_participants par
1656 where par.person_id = P_PERSON_ID);
1657
1658 if v_dummy = 1
1659 then
1660 v_dummy := null;
1661 delete from hr_quest_answer_values qsv2
1662 where qsv2.quest_answer_val_id in
1663 (select qsv.quest_answer_val_id
1664 from hr_quest_answer_values qsv
1665 ,hr_quest_answers qsa
1666 ,per_participants par
1667 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
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;
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
1682 select 2
1683 into v_dummy
1684 from sys.dual
1685 where exists (
1686 select null
1687 from per_appraisals apr
1688 where (apr.appraiser_person_id = P_PERSON_ID
1689 or apr.appraisee_person_id = P_PERSON_ID));
1690
1691 if v_dummy = 2
1692 then
1693 v_dummy := null;
1694 delete from hr_quest_answer_values qsv2
1695 where qsv2.quest_answer_val_id in
1696 (select qsv.quest_answer_val_id
1697 from hr_quest_answer_values qsv
1698 ,hr_quest_answers qsa
1699 ,per_appraisals apr
1700 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
1701 and qsa.type_object_id = apr.appraisal_id
1702 and qsa.type='APPRAISAL'
1703 and (apr.appraisee_person_id = P_PERSON_ID
1704 or apr.appraiser_person_id = P_PERSON_ID));
1705 end if;
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.
1715 -- begin
1716 -- Delete from HR_QUEST_ANSWER_VALUES
1717 /* delete from hr_quest_answer_values qsv2
1718 where qsv2.quest_answer_val_id in (
1719 select qsv.quest_answer_val_id
1720 from hr_quest_answer_values qsv
1721 , hr_quest_answers qsa
1722 , per_appraisals apr
1723 , per_participants par
1724 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
1725 and (qsa.type_object_id = apr.appraisal_id
1726 and qsa.type='APPRAISAL'
1727 and (apr.appraisee_person_id = P_PERSON_ID
1728 or apr.appraiser_person_id = P_PERSON_ID))
1729 or (qsa.type_object_id = par.participant_id
1730 and qsa.type='PARTICIPANT'
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
1740 delete from hr_quest_answers qsa2
1741 where qsa2.questionnaire_answer_id in (
1742 select qsa.questionnaire_answer_id
1743 from hr_quest_answers qsa
1744 , per_participants par
1745 , per_appraisals apr
1746 where (qsa.type_object_id = apr.appraisal_id
1747 and qsa.type='APPRAISAL'
1748 and (apr.appraiser_person_id = P_PERSON_ID
1749 or apr.appraisee_person_id = P_PERSON_ID))
1750 or (qsa.type_object_id = par.participant_id
1751 and qsa.type='PARTICIPANT'
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
1761 begin
1762 DELETE
1763 FROM per_participants par2
1764 WHERE par2.person_id = P_PERSON_ID
1765 OR
1766 (
1767 par2.participation_in_column = 'APPRAISAL_ID'
1768 AND par2.participation_in_table = 'PER_APPRAISALS'
1769 AND par2.participation_in_id in
1770 (
1771 SELECT
1772 apr.appraisal_id
1773 FROM per_appraisals apr
1774 WHERE
1775 (
1776 apr.appraisee_person_id = P_PERSON_ID
1777 OR apr.appraiser_person_id = P_PERSON_ID
1778 )
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 --
1788 begin
1789 delete from per_appraisals apr
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 --
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
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 --
1814 begin
1815 update per_assignments_f ass
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 --
1825 begin
1826 update per_assignments_f a
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 --
1836 begin
1837 update per_assignments_f a
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 --
1847 -- LOCK ASSIGNMENTS NOW: have to use cursor as cannot return >1 row for
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
1857 -- deleting from the parent record in per_pay_proposals to
1858 -- maintain referential integrity, using the cursor DELETE_COMPONENTS
1859 -- and the original per_pay_proposals delete.
1860 --
1861 open DELETE_COMPONENTS;
1862 LOOP
1863 FETCH DELETE_COMPONENTS INTO v_proposal_id;
1864 EXIT WHEN DELETE_COMPONENTS%NOTFOUND;
1865 DELETE FROM per_pay_proposal_components
1866 WHERE pay_proposal_id = v_proposal_id;
1867 END LOOP;
1868 close DELETE_COMPONENTS;
1869 --
1870 -- Now delete the parent proposal record.
1871 --
1872 delete from per_pay_proposals p
1873 where exists (
1874 select null
1875 from per_assignments_f ass
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 --
1885 begin
1886 delete from pay_personal_payment_methods_f m
1887 where m.assignment_id in (
1888 select ass.assignment_id
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 --
1898 begin
1899 delete from per_assignment_budget_values_f a
1900 where a.assignment_id in (
1901 select ass.assignment_id
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 --
1911 begin
1912 delete from per_assignment_extra_info a
1913 where a.assignment_id in (
1914 select ass.assignment_id
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 --
1924 begin
1925 delete from per_secondary_ass_statuses a
1926 where a.assignment_id in (
1927 select ass.assignment_id
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 --
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.
1940 --
1941 begin
1942 delete from per_cobra_coverage_benefits c2
1943 where c2.cobra_coverage_enrollment_id in (
1944 select c.cobra_coverage_enrollment_id
1945 from per_cobra_cov_enrollments c
1946 where exists (
1947 select null
1948 from per_assignments_f ass
1949 where ass.assignment_id = c.assignment_id
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 --
1959 begin
1960 delete from per_cobra_coverage_benefits c2
1961 where c2.cobra_coverage_enrollment_id in (
1962 select c.cobra_coverage_enrollment_id
1963 from per_cobra_cov_enrollments c
1964 , per_contact_relationships r
1965 where r.contact_person_id = P_PERSON_ID
1966 and c.contact_relationship_id = r.contact_relationship_id
1967 and exists (
1968 select null
1969 from per_assignments_f ass
1970 where ass.assignment_id = c.assignment_id
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 --
1980 begin
1981 delete from per_cobra_coverage_statuses c2
1982 where c2.cobra_coverage_enrollment_id in (
1983 select c.cobra_coverage_enrollment_id
1984 from per_cobra_cov_enrollments c
1985 where exists (
1986 select null
1987 from per_assignments_f ass
1988 where ass.assignment_id = c.assignment_id
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 --
1998 begin
1999 delete from per_cobra_coverage_statuses c2
2000 where c2.cobra_coverage_enrollment_id in (
2001 select c.cobra_coverage_enrollment_id
2002 from per_cobra_cov_enrollments c
2003 , per_contact_relationships r
2004 where r.contact_person_id = P_PERSON_ID
2005 and c.contact_relationship_id = r.contact_relationship_id
2006 and exists (
2007 select null
2008 from per_assignments_f ass
2009 where ass.assignment_id = c.assignment_id
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 --
2019 begin
2020 delete from per_sched_cobra_payments c2
2021 where c2.cobra_coverage_enrollment_id in (
2022 select c.cobra_coverage_enrollment_id
2023 from per_cobra_cov_enrollments c
2024 where exists (
2025 select null
2026 from per_assignments_f ass
2027 where ass.assignment_id = c.assignment_id
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 --
2037 begin
2038 delete from per_sched_cobra_payments c2
2039 where c2.cobra_coverage_enrollment_id in (
2040 select c.cobra_coverage_enrollment_id
2041 from per_cobra_cov_enrollments c
2042 , per_contact_relationships r
2043 where r.contact_person_id = P_PERSON_ID
2044 and c.contact_relationship_id = r.contact_relationship_id
2045 and exists (
2046 select null
2047 from per_assignments_f ass
2048 where ass.assignment_id = c.assignment_id
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 --
2058 begin
2059 delete from per_cobra_cov_enrollments c
2060 where c.assignment_id in (
2061 select ass.assignment_id
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 --
2071 begin
2072 delete from per_cobra_cov_enrollments c
2073 where exists
2074 (select null
2075 from per_contact_relationships r
2076 where r.contact_person_id = P_PERSON_ID
2077 and c.contact_relationship_id = r.contact_relationship_id
2078 and exists (
2079 select null
2080 from per_assignments_f ass
2081 where ass.assignment_id = c.assignment_id
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
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 (
2099 select r.contact_relationship_id
2100 from per_contact_relationships r
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 --
2110
2111 --
2112 -- If this person has any contacts then check whether they have had any
2113 -- extra info entered for them. If they have not then delete the
2114 -- contacts as well. If they do have extra info then just delete the
2115 -- relationship.
2116 --
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
2126 from per_contact_relationships r
2127 where r.person_id = P_PERSON_ID;
2128 --
2129 if v_dummy > 0 then
2130 for EACH_CONTACT in THIS_PERSONS_CONTACTS loop
2131 --
2132 delete from per_contact_relationships r
2133 where (r.person_id = P_PERSON_ID
2134 and r.contact_person_id = EACH_CONTACT.CONTACT_PERSON_ID)
2135 or (r.person_id = EACH_CONTACT.CONTACT_PERSON_ID
2136 and r.contact_person_id = P_PERSON_ID);
2137 --
2138 hr_person_delete.check_contact(P_PERSON_ID,
2139 EACH_CONTACT.CONTACT_PERSON_ID,
2140 EACH_CONTACT.CONTACT_RELATIONSHIP_ID,
2141 P_SESSION_DATE);
2142 end loop;
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 --
2152 begin
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 --
2162 begin
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 --
2172 begin
2173 delete from per_phones a
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 --
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.
2186 -- if the table is not found then the error (which starts with 'ORA-00942') is ignored.
2187 begin
2188 v_review_cursor:=dbms_sql.open_cursor;
2189 dbms_sql.parse(v_review_cursor,'DELETE from PER_PERFORMANCE_REVIEWS
2190 where person_id=:x',dbms_sql.v7);
2191 dbms_sql.bind_variable(v_review_cursor, ':x',P_PERSON_ID);
2192 v_rows_processed:=dbms_sql.execute(v_review_cursor);
2193 dbms_sql.close_cursor(v_review_cursor);
2194 exception
2195 when NO_DATA_FOUND then dbms_sql.close_cursor(v_review_cursor);
2196 when OTHERS then
2197 dbms_sql.close_cursor(v_review_cursor);
2198 if(substr(sqlerrm,0,9)<>'ORA-00942') then
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 --
2208 begin
2209
2210 -- bug fix 3732129.
2211 -- Delete statement modified to improve performance.
2212
2213 forall i in 1..l_assignment_id.count
2214 delete from per_bookings b
2215 where b.event_id in (select e.event_id
2216 from per_events e
2217 where e.assignment_id = l_assignment_id(i));
2218
2219 /*delete from per_bookings b
2220 where b.event_id in
2221 (select e.event_id
2222 from per_events e
2223 where exists (
2224 select null
2225 from per_assignments_f ass
2226 where ass.assignment_id = e.assignment_id
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 --
2236 begin
2237 -- bug fix 3732129.
2238 -- Delete statement modified to improve performance.
2239
2240 forall i in 1..l_assignment_id.count
2241 delete from per_events e
2242 where e.assignment_id = l_assignment_id(i);
2243
2244 /* delete from per_events e
2245 where e.assignment_id in (
2246 select ass.assignment_id
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 --
2256 begin
2257 update per_events e
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 --
2267 begin
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 --
2277 begin
2278 delete from per_quickpaint_result_text q
2279 where q.assignment_id in (
2280 select ass.assignment_id
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 --
2290 -- Validation has already been performed against
2291 -- hr_assignment_set_amendments in weak_predel_validation.
2292 --
2293 begin
2294 delete from hr_assignment_set_amendments h
2295 where h.assignment_id in (
2296 select ass.assignment_id
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 --
2306 begin
2307 delete from pay_cost_allocations_f a
2308 where a.assignment_id in (
2309 select ass.assignment_id
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 --
2319 begin
2320 delete from per_spinal_point_placements_f p
2321 where p.assignment_id in (
2322 select ass.assignment_id
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 --
2332 -- Validation has already been performed against
2333 -- pay_assignment_actions in weak_predel_validation.
2334 --
2335 begin
2336 delete from pay_assignment_actions a
2337 where exists (
2338 select null
2339 from per_assignments_f ass
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 --
2349 begin
2350 delete from pay_assignment_latest_balances b
2351 where b.assignment_id in (
2352 select ass.assignment_id
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 --
2362 begin
2363 -- bug fix 3732129
2364 -- Delete statement modified to improve performance.
2365
2366 forall i in 1..l_assignment_id.count
2367 delete from pay_assignment_link_usages_f u
2368 where u.assignment_id = l_assignment_id(i);
2369
2370 /*delete from pay_assignment_link_usages_f u
2371 where
2372 u.assignment_id in (
2373 select ass.assignment_id
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 --
2383 begin
2384 delete from pay_element_entry_values_f v
2385 where v.element_entry_id in (
2386 select e.element_entry_id
2387 from pay_element_entries_f e
2388 where exists (
2389 select null
2390 from per_assignments_f ass
2391 where ass.assignment_id = e.assignment_id
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 --
2401 begin
2402 delete from pay_run_results r
2403 where r.source_type = 'E'
2404 and r.source_id in (
2405 select e.element_entry_id
2406 from pay_element_entries_f e
2407 where exists (
2408 select null
2409 from per_assignments_f ass
2410 where ass.assignment_id = e.assignment_id
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 --
2420 begin
2421 delete from pay_element_entries_f e
2422 where e.assignment_id in (
2423 select ass.assignment_id
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 --
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,
2436 -- pay_us_county_tax_rules_f, and pay_us_city_tax_rules_f.
2437 --
2438 begin
2439
2440 Delete pay_us_emp_fed_tax_rules_f peft
2441 Where peft.assignment_id in (
2442 select ass.assignment_id
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
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
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
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 --
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;
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 --
2508 begin
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 --
2518 begin
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
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;
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
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)
2548 into l_effective_date
2549 from per_person_type_usages_f ptu1
2550 where ptu1.person_type_usage_id = ptu_rec.person_type_usage_id;
2551
2552 select ptu2.object_version_number
2553 into l_object_version_number
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(
2563 -- p_person_id => p_person_id,
2564 -- p_action => 'DELETE',
2565 -- p_period_of_service_id => NULL,
2566 -- p_actual_termination_date => NULL,
2567 -- p_business_group_id => NULL,
2568 -- p_date_start => NULL,
2569 -- p_leaving_reason => NULL,
2570 -- p_old_date_start => NULL,
2571 -- p_old_leaving_reason => NULL);
2572
2573 hr_per_type_usage_internal.delete_person_type_usage
2574 (p_person_type_usage_id => ptu_rec.person_type_usage_id
2575 ,p_effective_date => l_effective_date
2576 ,p_datetrack_mode => 'ZAP'
2577 ,p_object_version_number => l_object_version_number
2578 ,p_effective_start_date => l_effective_start_date
2579 ,p_effective_end_date => l_effective_end_date
2580 );
2581 exception
2582 when NO_DATA_FOUND then null;
2583 end;
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;
2593 exception
2594 when NO_DATA_FOUND then null;
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
2604 when NO_DATA_FOUND then null;
2605 end;
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
2615 delete from per_subjects_taken_tl st
2616 where st.subjects_taken_id IN ( select s.subjects_taken_id
2617 from per_subjects_taken s
2618 , per_qualifications q
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
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
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;
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;
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 --
2665 hr_contract_api.maintain_contracts (
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
2675 --
2676 per_medical_assessment_api.delete_medical_assessment
2677 (FALSE
2678 ,mea_rec.medical_assessment_id
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 --
2688 open DISABILITIES;
2689 loop
2690 fetch DISABILITIES INTO v_disability_id, v_object_version_no, v_effective_start_date, v_effective_end_date;
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 --
2700 open WORK_INCIDENTS;
2701 loop
2702 fetch WORK_INCIDENTS INTO v_incident_id, v_object_version_number;
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 --
2712 open ROLES;
2713 loop
2714 fetch ROLES into v_role_id, v_ovn_roles;
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
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;
2732 -------------------- END: delete_a_person ----------------------------------
2733 --
2734 -------------------- BEGIN: people_default_deletes -------------------------
2735 /*
2736 NAME
2737 people_default_deletes
2738 DESCRIPTION
2739 Delete routine for deleting information set up as default when people
2740 are created. Used primarily for delete on PERPEEPI (Enter Person).
2741 The strong_predel_validation should first be performed to ensure that
2742 no additional info (apart from default) has been entered.
2743 NOTE
2744 See delete_a_person for p_form_call details. Further, p_form_call is
2745 set to TRUE when this procedure is called from check_contact as
2746 there is no need to check the existance of the contact.
2747 */
2748 --
2749 PROCEDURE people_default_deletes (p_person_id IN number,
2750 p_form_call IN boolean)
2751 IS
2752 --
2753 v_assignment_id number(15);
2754 --
2755 cursor LOCK_PERSON_ROWS is
2756 select person_id
2757 from per_people_f
2758 where person_id = P_PERSON_ID
2759 FOR UPDATE;
2760 --
2761 BEGIN
2762 --
2763 --
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.
2773 --
2774 -- Start of Fix for WWBUG 1294400
2775 -- All of benefits is a child of HR and PAY so its safe to delete
2776 -- benefits stuff first.
2777 --
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);
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).
2791 -- May not be assignments (for contacts, for eg) so exception.
2792 --
2793 begin
2794 select ass.assignment_id
2795 into v_assignment_id
2796 from per_assignments_f ass
2797 where ass.person_id = P_PERSON_ID
2798 FOR UPDATE;
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
2835 and a.parent_table = 'PER_ALL_PEOPLE_F';
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 (
2854 select null
2855 from pay_element_entries_f e
2856 where e.assignment_id = V_ASSIGNMENT_ID
2857 and e.element_entry_id = v.element_entry_id);
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'
2867 and EXISTS (
2868 select null
2869 from pay_element_entries_f e
2870 where e.assignment_id = V_ASSIGNMENT_ID
2871 and e.element_entry_id = r.source_id);
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;
2908 exception
2909 when NO_DATA_FOUND then null;
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
2919 when NO_DATA_FOUND then null;
2920 end;
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);
2930 --
2931 begin
2932 delete from per_people_f
2933 where person_id = P_PERSON_ID;
2934 exception
2935 when NO_DATA_FOUND then null;
2936 end;
2937 --
2938 begin
2939 delete from per_periods_of_placement p
2940 where p.person_id = P_PERSON_ID;
2941 exception
2942 when NO_DATA_FOUND then null;
2943 end;
2944 --
2945 --
2946 END people_default_deletes;
2947 -------------------- END: people_default_deletes --------------------------
2948 --
2949 -------------------- BEGIN: applicant_default_deletes ---------------------
2950 /*
2951 NAME
2952 applicant_default_deletes
2953 DESCRIPTION
2954 Delete routine for deleting information set up as default when
2955 applicants are entered. Used primarily for delete on PERREAQE
2956 (Applicant Quick Entry). The strong_predel_validation should first be
2957 performed to ensure that no additional info (apart from default) has
2958 been entered.
2959 NOTE
2960 See delete_a_person for p_form_call details.
2961 */
2962 --
2963 PROCEDURE applicant_default_deletes (p_person_id IN number,
2964 p_form_call IN boolean)
2965 IS
2966 --
2967 v_assignment_id number(15);
2968 --
2969 cursor LOCK_PERSON_ROWS is
2970 select person_id
2971 from per_people_f
2972 where person_id = P_PERSON_ID
2973 FOR UPDATE;
2974 --
2975 BEGIN
2976 --
2977 --
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.
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 --
3002 begin
3003 select ass.assignment_id
3004 into v_assignment_id
3005 from per_assignments_f ass
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
3024 and a.parent_table = 'PER_ALL_PEOPLE_F';
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;
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;
3041 exception
3042 when NO_DATA_FOUND then null;
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
3052 when NO_DATA_FOUND then null;
3053 end;
3054 --
3055 -- End addition for quickhire checklists
3056 --
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
3066 when NO_DATA_FOUND then null;
3067 end;
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;
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;
3083 exception
3084 when NO_DATA_FOUND then null;
3085 end;
3086 --
3087 END applicant_default_deletes;
3088 -------------------- END: applicant_default_deletes -----------------------
3089 --
3090 end hr_person_delete;
[Home] [Help]