1 PACKAGE BODY hr_person_delete AS
2 /* $Header: peperdel.pkb 120.9.12010000.2 2009/02/23 10:15:14 ghshanka ship $ */
3 /*
4 ******************************************************************
5 * *
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 115.46 23-Feb-2009 ghshanka 8265994 Modified the procedure delete_a_person added dmls to delete the personal score cards.
223
224 ========================================================================= */
225 --
226 -------------------- BEGIN: product_installed ------------------------------
227 /*
228 NAME
229 product_installed
230 DESCRIPTION
231 Returns 'Y' if this product is installed, 'N' if not in p_yes_no
232 and the ORACLEID of the application in p_oracle_username.
233 */
234 --
235 PROCEDURE product_installed (p_application_short_name IN varchar2,
236 p_status OUT NOCOPY varchar2,
237 p_yes_no OUT NOCOPY varchar2,
238 p_oracle_username OUT NOCOPY varchar2)
239 IS
240 --
241 BEGIN
242 --
243 p_yes_no := 'N';
244 p_oracle_username := 'DUMMY';
245 --
246 begin
247 select 'Y',
248 fpi.status
249 into p_yes_no,
250 p_status
251 from fnd_product_installations fpi
252 where fpi.status = 'I'
253 and fpi.application_id =
254 (select fa.application_id
255 from fnd_application fa
256 where fa.application_short_name = P_APPLICATION_SHORT_NAME
257 );
258 exception
259 when NO_DATA_FOUND then null;
260 end;
261 --
262 END product_installed;
263 -------------------- END: product_installed --------------------------------
264 --
265 -------------------- BEGIN: person_existance_check -------------------------
266 /*
267 NAME
268 person_existance_check
269 DESCRIPTION
270 Raises error (and hence falls right out of package) if this person does
271 not exist.
272 */
273 --
274 PROCEDURE person_existance_check (p_person_id number)
275 IS
276 --
277 v_dummy number(15);
278 --
279 BEGIN
280 select count(*)
281 into v_dummy
282 from per_all_people_f p
283 where p.person_id = P_PERSON_ID;
284 exception
285 when NO_DATA_FOUND then
286 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
287 hr_utility.set_message_token ('PROCEDURE',
288 'PERSON_EXISTANCE_CHECK');
289 hr_utility.set_message_token ('STEP', '1');
290 hr_utility.raise_error;
291 END person_existance_check;
292 -------------------- END: person_existance_check ---------------------------
293 --
294 -------------------- BEGIN: pay_predel_validation --------------------------
295 /*
296 NAME
297 pay_predel_validation
298 DESCRIPTION
299 Ensures that there are no assignments actions for this person other than
300 Purge actions. If there are then raise an error and disallow delete.
301 */
302 --
303 PROCEDURE pay_predel_validation (p_person_id number)
304 IS
305 --
306 v_delete_permitted varchar2(1);
307 --
308 BEGIN
309 --
310 begin
311 select 'Y'
312 into v_delete_permitted
313 from sys.dual
314 where not exists (
315 select null
316 from pay_assignment_actions paa,
317 per_assignments_f ass,
318 pay_payroll_actions ppa
319 where paa.assignment_id = ass.assignment_id
320 and ass.person_id = P_PERSON_ID
321 and ppa.payroll_action_id = paa.payroll_action_id
322 and ppa.action_type <> 'Z');
323 --
324 exception
325 when NO_DATA_FOUND then
326 hr_utility.set_message (801,'HR_6237_ALL_ASS_ACTIONS_EXIST');
327 hr_utility.raise_error;
328 end;
329 --
330 END pay_predel_validation;
331 -------------------- END: pay_predel_validation ---------------------------
332 --
333 -- Start of Fix for WWBUG 1294400
334 -------------------- BEGIN: ben_predel_validation --------------------------
335 /*
336 NAME
337 ben_predel_validation
338 DESCRIPTION
339 Ensures that there are no open life events for a person.
340 */
341 --
342 PROCEDURE ben_predel_validation (p_person_id NUMBER
343 ,p_effective_date DATE)
344 IS
345 --
346 --
347 BEGIN
348 --
349 ben_person_delete.check_ben_rows_before_delete(p_person_id
350 ,p_effective_date);
351 --
352 END ben_predel_validation;
353 -------------------- END: ben_predel_validation ---------------------------
354 -- End of Fix for WWBUG 1294400
355 -------------------- BEGIN: aol_predel_validation -------------------------
356 /*
357 NAME
358 aol_predel_validation
359 DESCRIPTION
360 Foreign key reference check.
361 */
362 --
363 PROCEDURE aol_predel_validation (p_person_id number)
364 IS
365 --
366 v_delete_permitted varchar2(1);
367 --
368 BEGIN
369 --
370 begin
371 select 'Y'
372 into v_delete_permitted
373 from sys.dual
374 where not exists (
375 select null
376 from fnd_user aol
377 where aol.employee_id = P_PERSON_ID
378 );
379 --
380 exception
381 when NO_DATA_FOUND then
382 hr_utility.set_message (801,'HR_6274_ALL_AOL_PER_NO_DEL');
383 hr_utility.raise_error;
384 end;
385 --
386 END aol_predel_validation;
387 -------------------- END: aol_predel_validation -------------------------
388 --
389 -------------------- BEGIN: assignment_set_check ----------------------------
390 /*
391 NAME
392 assignment_set_check
393 DESCRIPTION
394 Sets error code and status if this person has any assignments which are
395 the only ones in an assignment set and where that assginment is included.
396 */
397 PROCEDURE assignment_set_check (p_person_id IN number)
398 IS
399 --
400 v_delete_permitted varchar2(1);
401 --
402 BEGIN
403 select 'Y'
404 into v_delete_permitted
405 from sys.dual
406 where not exists (
407 select null
408 from per_assignments_f ass,
409 hr_assignment_set_amendments asa
410 where asa.assignment_id = ass.assignment_id
411 and ass.person_id = P_PERSON_ID
412 and asa.include_or_exclude = 'I'
413 and not exists (
414 select null
415 from hr_assignment_set_amendments asa2
416 where asa2.assignment_set_id = asa.assignment_set_id
417 and asa2.assignment_id <> asa.assignment_id)
418 );
419 EXCEPTION
420 when NO_DATA_FOUND then
421 hr_utility.set_message (801,'HR_6305_ALL_ASSGT_SET_NO_DEL');
422 hr_utility.raise_error;
423 --
424 END assignment_set_check;
425 -------------------- END: assignment_set_check -------------------------
426 --
427 -------------------- BEGIN: closed_element_entry_check --------------------
428 /*
429 NAME
430 closed_element_entry_check
431 DESCRIPTION
432 Check that for any element entries that are about to be deleted, the
433 element type is not closed for the duration of that entry. Also check
434 that if the assignment is to a payroll, the payroll period is not closed.
435 If any of these 2 checks fail, the delete is disallowed.
436 */
437 --
438 PROCEDURE closed_element_entry_check (p_person_id IN number,
439 p_session_date IN date)
440 IS
441 --
442 cursor THIS_PERSONS_ELEMENT_ENTRIES is
443 select l.element_type_id,
444 e.effective_start_date,
445 e.effective_end_date,
446 a.assignment_id
447 from pay_element_entries_f e,
448 per_assignments_f a,
449 pay_element_links_f l
450 where a.person_id = P_PERSON_ID
451 and a.assignment_id = e.assignment_id
452 and e.effective_start_date between
453 a.effective_start_date and a.effective_end_date
454 and e.element_link_id = l.element_link_id
455 and e.effective_start_date between
456 l.effective_start_date and l.effective_end_date;
457 --
458 BEGIN
459 --
460 hr_utility.set_location('closed_element_entry_check',1);
461 --
462 for EACH_ENTRY in THIS_PERSONS_ELEMENT_ENTRIES loop
463 hr_entry.chk_element_entry_open(EACH_ENTRY.ELEMENT_TYPE_ID,
464 EACH_ENTRY.EFFECTIVE_START_DATE,
465 EACH_ENTRY.EFFECTIVE_START_DATE,
466 EACH_ENTRY.EFFECTIVE_END_DATE,
467 EACH_ENTRY.ASSIGNMENT_ID);
468 end loop;
469 --
470 hr_utility.set_location('closed_element_entry_check',2);
471 --
472 END closed_element_entry_check;
473 -------------------- END: closed_element_entry_check -----------------------
474 --
475 -------------------- BEGIN: contact_cobra_validation -----------------------
476 /*
477 NAME
478 contact_cobra_validation
479 DESCRIPTION
480 Searches for any contacts of the person being deleted who have
481 COBRA Coverage Enrollments which are as a result of the Persons
482 Assignments.
483 */
484 --
485 PROCEDURE contact_cobra_validation (p_person_id number)
486 IS
487 --
488 v_delete_permitted varchar2(1);
489 --
490 BEGIN
491 --
492 begin
493 select 'Y'
494 into v_delete_permitted
495 from sys.dual
496 where not exists (
497 select null
498 from per_assignments_f a
499 , per_contact_relationships c
500 , per_cobra_cov_enrollments e
501 where a.person_id = P_PERSON_ID
502 and a.assignment_id = e.assignment_id
503 and c.person_id = P_PERSON_ID
504 and c.contact_relationship_id = e.contact_relationship_id);
505 --
506 exception
507 when NO_DATA_FOUND then
508 hr_utility.set_message (801,'HR_6981_ALL_CONT_COBRA_EXISTS');
509 hr_utility.raise_error;
510 end;
511 --
512 END contact_cobra_validation;
513 -------------------- END: pay_predel_validation ---------------------------
514 -------------------- BEGIN: contracts_check -----------------------
515 /*
516 NAME
517 contracts_check
518 DESCRIPTION
519 Raise an error if related contracts exist for the given person.
520 */
521 --
522 PROCEDURE contracts_check (p_person_id number)
523 IS
524 --
525 v_delete_permitted varchar2(1);
526 --
527 begin
528 --
529 hr_utility.set_location('contracts_check',10);
530 --
531 -- Check that no child records exist for the
532 -- person on per_contracts_f when
533 -- the person is deleted
534 --
535 select null
536 into v_delete_permitted
537 from sys.dual
538 where not exists(select null
539 from per_contracts_f
540 where person_id = p_person_id);
541 --
542 exception
543 when NO_DATA_FOUND then
544 hr_utility.set_message(800,'PER_52851_PER_NO_DEL_CONTRACTS');
545 hr_utility.raise_error;
546 --
547 END contracts_check;
548
549 --
550 -------------------- BEGIN: weak_predel_validation -------------------------
551 /*
552 NAME
553 weak_predel_validation
554 DESCRIPTION
555 Validates whether a person can be deleted from the HR database.
556 This is the weak validation performed prior to delete using the
557 Delete Person form.
558 */
559 --
560 PROCEDURE weak_predel_validation (p_person_id IN number,
561 p_session_date IN date,
562 p_dt_delete_mode IN varchar2) -- 4169275
563 IS
564 --
565 -- DECLARE THE LOCAL VARIABLES
566 --
567 v_pay_installed varchar2(1);
568 v_pay_status varchar2(1);
569 v_ben_installed varchar2(1);
570 v_ben_status varchar2(1);
571 v_oracle_id varchar2(30);
572 v_delete_permitted varchar2(1);
573 --
574 BEGIN
575 --
576 --
577 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',1);
578 --
579 hr_person_delete.person_existance_check(P_PERSON_ID);
580 --
581 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',2);
582 --
583 hr_person_delete.product_installed('PAY', v_pay_status,
584 v_pay_installed, v_oracle_id);
585 --
586 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',4);
587 --
588 -- 4169275 start
589 -- During deletion of next change or future change need not require
590 -- this validation
591 --
592 if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
593 hr_person_delete.aol_predel_validation(P_PERSON_ID);
594 end if;
595 -- 4169275 end
596 --
597 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',5);
598 --
599 hr_person_delete.assignment_set_check(P_PERSON_ID);
600 --
601 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',6);
602 --
603 if (v_pay_installed = 'Y') then
604 hr_person_delete.pay_predel_validation(P_PERSON_ID);
605 end if;
606 --
607 -- Removed check for ben install
608 -- as OSB can now have enrollment results
609 -- and unrestricted Life events in progress
610 --
611 hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
612 --
613 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',8);
614 --
615 hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
616 --
617 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);
618 --
619 hr_person_delete.contact_cobra_validation(P_PERSON_ID);
620 --
621 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);
622 --
623 hr_person_delete.contracts_check(P_PERSON_ID);
624 --
625 hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',11);
626 --
627
628 END weak_predel_validation;
629 -------------------- END: weak_predel_validation --------------------------
630 --
631 --
632 -------------------- BEGIN: moderate_predel_validation ---------------------
633 /*
634 NAME
635 moderate_predel_validation
636 DESCRIPTION
637 Moderate pre-delete validation called from the Stong_predel_validation
638 procedure and HR API's.
639
640 */
641 PROCEDURE moderate_predel_validation (p_person_id IN number,
642 p_session_date IN date,
643 p_dt_delete_mode IN varchar2) -- 4169275
644
645 IS
646 v_delete_permitted varchar2(1);
647 --
648 -- Bug 3524713 Starts Here
649 CURSOR ben_ext_chg_log (
650 p_person_id NUMBER
651 ) IS
652 SELECT ext_chg_evt_log_id
653 FROM ben_ext_chg_evt_log
654 WHERE person_id = p_person_id
655 FOR UPDATE OF ext_chg_evt_log_id;
656 --
657 l_id NUMBER;
658 -- Bug 3524713 Ends Here
659 --
660 begin
661 --
662 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 1);
663 --
664 hr_person_delete.person_existance_check(P_PERSON_ID);
665 --
666 hr_person_delete.assignment_set_check(P_PERSON_ID);
667 --
668 --
669 -- 4169275 start
670 -- During deletion of next change or future change need not require
671 -- this validation
672 --
673 if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
674 hr_person_delete.aol_predel_validation(P_PERSON_ID);
675 end if;
676 -- 4169275 end
677 --
678 --
679 hr_person_delete.pay_predel_validation(P_PERSON_ID);
680 --
681 hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);
682 --
683 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 2);
684 --
685 -- VT 1403481 09/19/00
686 begin
687 -- bug fix 3732129.
688 -- Select statement modified to improve performance.
689
690 select 'Y'
691 into v_delete_permitted
692 from sys.dual
693 where not exists (
694 select null
695 from per_letter_request_lines r
696 where r.person_id = P_PERSON_ID
697 and r.date_from >= P_SESSION_DATE );
698
699 select 'Y'
700 into v_delete_permitted
701 from sys.dual
702 where not exists (
703 select null
704 from per_letter_request_lines r
705 where exists (
706 select null
707 from per_assignments_f a
708 where a.person_id = P_PERSON_ID
709 and a.effective_start_date >= P_SESSION_DATE
710 and a.assignment_id = r.assignment_id));
711
712
713 /* select 'Y'
714 into v_delete_permitted
715 from sys.dual
716 where not exists (
717 select null
718 from per_letter_request_lines r
719 where r.person_id = P_PERSON_ID
720 and r.date_from >= P_SESSION_DATE
721 or exists (
722 select null
723 from per_assignments_f a
724 where a.person_id = P_PERSON_ID
725 and a.effective_start_date >= P_SESSION_DATE
726 and a.assignment_id = r.assignment_id));*/
727
728
729 exception
730 when NO_DATA_FOUND then
731 hr_utility.set_message (801,'HR_6325_ALL_PER_RL_NO_DEL');
732 hr_utility.raise_error;
733 end;
734 --
735 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 3);
736 --
737 begin
738 select 'Y'
739 into v_delete_permitted
740 from sys.dual
741 where not exists (
742 select null
743 from per_contact_relationships r
744 where r.person_id = P_PERSON_ID
745 or r.contact_person_id = P_PERSON_ID);
746 exception
747 when NO_DATA_FOUND then
748 hr_utility.set_message (801,'HR_6326_ALL_PER_CR_NO_DEL');
749 hr_utility.raise_error;
750 end;
751 --
752 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 5);
753 --
754 begin
755 select 'Y'
756 into v_delete_permitted
757 from sys.dual
758 where not exists (
759 select null
760 from per_events e
761 where e.internal_contact_person_id = P_PERSON_ID);
762 exception
763 when NO_DATA_FOUND then
764 hr_utility.set_message (801,'HR_6328_ALL_PER_EVENT_NO_DEL');
765 hr_utility.raise_error;
766 end;
767 --
768 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 6);
769 --
770 begin
771 select 'Y'
772 into v_delete_permitted
773 from sys.dual
774 where not exists (
775 select null
776 from per_bookings b
777 where b.person_id = P_PERSON_ID);
778 exception
779 when NO_DATA_FOUND then
780 hr_utility.set_message (801,'HR_6329_ALL_PER_BOOK_NO_DEL');
781 hr_utility.raise_error;
782 end;
783 --
784 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 7);
785 --
786 -- VT 1403481 09/19/00
787 begin
788 select 'Y'
789 into v_delete_permitted
790 from sys.dual
791 where 1 >= (
792 select count(*)
793 from per_assignments_f a
794 where a.person_id = P_PERSON_ID
795 and a.effective_start_date > P_SESSION_DATE);-- fix for bug 4238025
796 exception
797 when NO_DATA_FOUND then
798 hr_utility.set_message (801,'HR_6330_ALL_PER_ASSGT_NO_DEL');
799 hr_utility.raise_error;
800 end;
801 --
802 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 8);
803 --
804 begin
805 select 'Y'
806 into v_delete_permitted
807 from sys.dual
808 where not exists (
809 select null
810 from per_assignments_f a
811 where a.recruiter_id = P_PERSON_ID
812 or a.supervisor_id = P_PERSON_ID);
813 exception
814 when NO_DATA_FOUND then
815 hr_utility.set_message (801,'HR_6331_ALL_PER_RT_SUP_NO_DEL');
816 hr_utility.raise_error;
817 end;
818 --
819 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 9);
820 --
821 begin
822 select 'Y'
823 into v_delete_permitted
824 from sys.dual
825 where not exists (
826 select null
827 from per_periods_of_service p
828 where p.termination_accepted_person_id = P_PERSON_ID);
829 exception
830 when NO_DATA_FOUND then
831 hr_utility.set_message (801,'HR_6332_ALL_PER_TERM_NO_DEL');
832 hr_utility.raise_error;
833 end;
834 --
835 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 10);
836 --
837 begin
838 select 'Y'
839 into v_delete_permitted
840 from sys.dual
841 where not exists (
842 select null
843 from per_person_analyses a
844 where a.person_id = P_PERSON_ID);
845 exception
846 when NO_DATA_FOUND then
847 hr_utility.set_message (801,'HR_6334_ALL_PER_ANAL_NO_DEL');
848 hr_utility.raise_error;
849 end;
850 --
851 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 11);
852 --
853 begin
854 select 'Y'
855 into v_delete_permitted
856 from sys.dual
857 where not exists (
858 select null
859 from per_absence_attendances a
860 where a.person_id = P_PERSON_ID);
861 exception
862 when NO_DATA_FOUND then
863 hr_utility.set_message (801,'HR_6335_ALL_PER_ABS_ATT_NO_DEL');
864 hr_utility.raise_error;
865 end;
866 --
867 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 12);
868 --
869 begin
870 select 'Y'
871 into v_delete_permitted
872 from sys.dual
873 where not exists (
874 select null
875 from per_absence_attendances a
876 where a.authorising_person_id = P_PERSON_ID
877 or a.replacement_person_id = P_PERSON_ID);
878 exception
879 when NO_DATA_FOUND then
880 hr_utility.set_message (801,'HR_6336_ALL_PER_AUTH_NO_DEL');
881 hr_utility.raise_error;
882 end;
883 --
884 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 13);
885 --
886 begin
887 select 'Y'
888 into v_delete_permitted
889 from sys.dual
890 where not exists (
891 select null
892 from per_recruitment_activities r
893 where r.authorising_person_id = P_PERSON_ID
894 or r.internal_contact_person_id = P_PERSON_ID);
895 exception
896 when NO_DATA_FOUND then
897 hr_utility.set_message (801,'HR_6337_ALL_PER_REC_NO_DEL');
898 hr_utility.raise_error;
899 end;
900 --
901 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);
902 begin
903 select 'Y'
904 into v_delete_permitted
905 from sys.dual
906 where not exists (
907 select null
908 from per_appraisals apr
909 where apr.appraisee_person_id = P_PERSON_ID
910 or apr.appraiser_person_id = P_PERSON_ID);
911 exception
912 when NO_DATA_FOUND then
913 fnd_message.set_name(801,'PER_52467_APR_PAR_REC_NO_DEL');
914 fnd_message.raise_error;
915 end;
916 --
917 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);
918 begin
919 select 'Y'
920 into v_delete_permitted
921 from sys.dual
922 where not exists (
923 select null
924 from per_participants par
925 where par.person_id = P_PERSON_ID);
926 exception
927 when NO_DATA_FOUND then
928 fnd_message.set_name(801,'PER_52467_APR_PAR_REC_NO_DEL');
929 fnd_message.raise_error;
930 end;
931 --
932 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 14);
933 --
934 begin
935 select 'Y'
936 into v_delete_permitted
937 from sys.dual
938 where not exists (
939 select null
940 from per_requisitions r
941 where r.person_id = P_PERSON_ID);
942 exception
943 when NO_DATA_FOUND then
944 hr_utility.set_message (801,'HR_6338_ALL_PER_REQ_NO_DEL');
945 hr_utility.raise_error;
946 end;
947 --
948 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 15);
949 --
950 begin
951 select 'Y'
952 into v_delete_permitted
953 from sys.dual
954 where not exists (
955 select null
956 from per_vacancies v
957 where v.recruiter_id = P_PERSON_ID);
958 exception
959 when NO_DATA_FOUND then
960 hr_utility.set_message (801,'HR_6339_ALL_PER_VAC_NO_DEL');
961 hr_utility.raise_error;
962 end;
963 --
964 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 16);
965 --
966 -- Any discretionary link element entries?
967 --
968 begin
969 select 'Y'
970 into v_delete_permitted
971 from sys.dual
972 where not exists (
973 select null
974 from pay_element_entries_f e,
975 per_assignments_f a,
976 pay_element_links_f l
977 where a.person_id = P_PERSON_ID
978 and a.assignment_id = e.assignment_id
979 and e.element_link_id = l.element_link_id
980 and l.standard_link_flag = 'N');
981 exception
982 when NO_DATA_FOUND then
983 hr_utility.set_message (801,'HR_6340_ALL_PER_DISC_NO_DEL');
984 hr_utility.raise_error;
985 end;
986 --
987 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 17);
988 --
989 -- Any entry adjustments, overrides etc.?
990 -- (We cannot capture manual enty of standard link entries)
991 --
992 begin
993 select 'Y'
994 into v_delete_permitted
995 from sys.dual
996 where not exists (
997 select null
998 from pay_element_entries_f e,
999 per_assignments_f a
1000 where a.person_id = P_PERSON_ID
1001 and a.assignment_id = e.assignment_id
1002 and e.entry_type <> 'E');
1003 exception
1004 when NO_DATA_FOUND then
1005 hr_utility.set_message (801,'HR_6375_ALL_PER_ENTRY_NO_DEL');
1006 hr_utility.raise_error;
1007 end;
1008 --
1009 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 171);
1010 --
1011 -- Are the entries to be deleted in a closed period? If so cannot delete.
1012 --
1013 hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);
1014 --
1015 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 18);
1016 --
1017 begin
1018 select 'Y'
1019 into v_delete_permitted
1020 from sys.dual
1021 where not exists (
1022 select null
1023 from per_assignment_extra_info i
1024 where exists (
1025 select null
1026 from per_assignments_f a
1027 where a.person_id = P_PERSON_ID
1028 and a.assignment_id = i.assignment_id));
1029 exception
1030 when NO_DATA_FOUND then
1031 hr_utility.set_message (801,'HR_6341_ALL_PER_ASS_INFO_DEL');
1032 hr_utility.raise_error;
1033 end;
1034 --
1035 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 19);
1036 --
1037 begin
1038 select 'Y'
1039 into v_delete_permitted
1040 from sys.dual
1041 where not exists (
1042 select null
1043 from per_secondary_ass_statuses s
1044 where exists (
1045 select null
1046 from per_assignments_f a
1047 where a.person_id = P_PERSON_ID
1048 and a.assignment_id = s.assignment_id));
1049 exception
1050 when NO_DATA_FOUND then
1051 ---changed the message number from 6340 to 7407 for bug 5405424
1052 hr_utility.set_message (801,'HR_7407_ASG_NO_DEL_ASS_STATUS');
1053 hr_utility.raise_error;
1054 end;
1055 --
1056 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 20);
1057 --
1058 begin
1059 select 'Y'
1060 into v_delete_permitted
1061 from sys.dual
1062 where not exists (
1063 select null
1064 from per_events e
1065 where exists (
1066 select null
1067 from per_assignments_f a
1068 where a.person_id = P_PERSON_ID
1069 and a.assignment_id = e.assignment_id));
1070 exception
1071 when NO_DATA_FOUND then
1072 hr_utility.set_message (801,'HR_6344_ALL_PER_INT_NO_DEL');
1073 hr_utility.raise_error;
1074 end;
1075 --
1076 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 21);
1077 --
1078 begin
1079 select 'Y'
1080 into v_delete_permitted
1081 from sys.dual
1082 where not exists (
1083 select null
1084 from per_spinal_point_placements_f p
1085 where exists (
1086 select null
1087 from per_assignments_f a
1088 where a.person_id = P_PERSON_ID
1089 and a.assignment_id = p.assignment_id));
1090 exception
1091 when NO_DATA_FOUND then
1092 hr_utility.set_message (801,'HR_6374_ALL_PER_SPINE_NO_DEL');
1093 hr_utility.raise_error;
1094 end;
1095 --
1096 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 22);
1097 --
1098 begin
1099 select 'Y'
1100 into v_delete_permitted
1101 from sys.dual
1102 where not exists (
1103 select null
1104 from per_quickpaint_result_text t
1105 where exists (
1106 select null
1107 from per_assignments_f a
1108 where a.person_id = P_PERSON_ID
1109 and a.assignment_id = t.assignment_id));
1110 exception
1111 when NO_DATA_FOUND then
1112 hr_utility.set_message (801,'HR_6379_ALL_PER_QP_NO_DEL');
1113 hr_utility.raise_error;
1114 end;
1115 --
1116 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 26);
1117 --
1118 begin
1119 select 'Y'
1120 into v_delete_permitted
1121 from sys.dual
1122 where not exists (
1123 select null
1124 from per_cobra_cov_enrollments c
1125 where exists (
1126 select null
1127 from per_assignments_f a
1128 where a.person_id = P_PERSON_ID
1129 and a.assignment_id = c.assignment_id));
1130 exception
1131 when NO_DATA_FOUND then
1132 hr_utility.set_message (801,'HR_6476_ALL_PER_COB_NO_DEL');
1133 hr_utility.raise_error;
1134 end;
1135 --
1136 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 27);
1137 --
1138 hr_person_delete.contact_cobra_validation(P_PERSON_ID);
1139 --
1140 hr_person_delete.contracts_check(P_PERSON_ID);
1141 --
1142 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 28);
1143 -- Validation for BEN
1144 --
1145 -- Bug 3524713 Starts Here
1146 OPEN ben_ext_chg_log (
1147 p_person_id
1148 );
1149 --
1150 LOOP
1151 FETCH ben_ext_chg_log INTO l_id;
1152 EXIT WHEN ben_ext_chg_log%NOTFOUND;
1153 DELETE FROM ben_ext_chg_evt_log
1154 WHERE CURRENT OF ben_ext_chg_log;
1155 END LOOP;
1156 CLOSE ben_ext_chg_log;
1157 -- Bug 3524713 Ends Here
1158 --
1159 ben_person_delete.perform_ri_check(p_person_id);
1160 --
1161 -- Validation for OTA.
1162 --Added for bug 5945972
1163
1164 if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
1165 per_ota_predel_validation.ota_predel_per_validation(P_PERSON_ID);
1166 end if;
1167
1168 --
1169 -- validation for PA
1170 --
1171 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 29);
1172 --
1173 pa_person.pa_predel_validation(P_PERSON_ID);
1174 --
1175 -- validation for WIP
1176 --
1177 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 30);
1178 --
1179 wip_person.wip_predel_validation(P_PERSON_ID);
1180 --
1181 -- validation for ENG
1182 --
1183 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 31);
1184 --
1185 eng_person.eng_predel_validation(P_PERSON_ID);
1186 --
1187 -- validation for AP
1188 --
1189 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 32);
1190 --
1191 ap_person.ap_predel_validation(P_PERSON_ID);
1192 --
1193 -- validation for FA
1194 --
1195 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 33);
1196 --
1197 fa_person.fa_predel_validation(P_PERSON_ID);
1198 --
1199 -- validation for PO
1200 --
1201 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 34);
1202 --
1203 po_person.po_predel_validation(P_PERSON_ID);
1204 --
1205 -- validation for RCV
1206 --
1207 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 35);
1208 --
1209 rcv_person.rcv_predel_validation(P_PERSON_ID);
1210 --
1211 hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 36);
1212 --
1213 --
1214 end moderate_predel_validation;
1215 -------------------- END: moderate_predel_validation ---------------------
1216 --
1217 --
1218 -------------------- BEGIN: strong_predel_validation ---------------------
1219 /*
1220 NAME
1221 strong_predel_validation
1222 DESCRIPTION
1223 Called from PERREAQE and PERPEEPI. It performs many checks
1224 to find if additional data has been entered for this person. It is
1225 more stringent than weak_predel_validation and ensures that this
1226 person only has the default data set up by entering a person, contact
1227 or applicant afresh onto the system.
1228 If additional data is found then the delete of this person from
1229 the calling module is invalid as it is beyond its scope. The Delete
1230 Person form should therefore be used (which only performs
1231 weak_predel_validation) if a delete really is required.
1232 p_person_mode - 'A' check for applicants
1233 'E' check for employees
1234 'O' check for other types
1235
1236 NOTE
1237 No validation is required for security (PER_PERSON_LIST* tables) as
1238 this is implicit for the person via assignment criteria. The
1239 rows in these tables can just be deleted.
1240 */
1241 PROCEDURE strong_predel_validation (p_person_id IN number,
1242 p_session_date IN date,
1243 p_dt_delete_mode IN varchar2) -- 4169275
1244 IS
1245 --
1246 v_person_types number;
1247 --
1248 BEGIN
1249 --
1250 hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1251 --
1252 -- If >1 system person types then non default amendments have been made.
1253 -- If v_person_types = 0 then only 1 system person type else > 1.
1254 --
1255 -- VT 1403481 09/19/00
1256 select count(*)
1257 into v_person_types
1258 from per_people_f p,
1259 per_person_types ppt
1260 where p.person_id = P_PERSON_ID
1261 and p.effective_end_date >= P_SESSION_DATE
1262 and p.person_type_id = ppt.person_type_id
1263 and exists
1264 (select null
1265 from per_people_f p2,
1266 per_person_types ppt2
1267 where p2.person_id = p.person_id
1268 and p2.effective_end_date >= P_SESSION_DATE
1269 and p2.person_type_id = ppt2.person_type_id
1270 and ppt2.system_person_type <> ppt.system_person_type
1271 );
1272 --
1273 if v_person_types > 0 then
1274 hr_utility.set_message (801,'HR_6324_ALL_PER_ADD_NO_DEL');
1275 hr_utility.raise_error;
1276 end if;
1277 --
1278 hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);
1279 --
1280 -- fix for bug 4508139
1281 if p_dt_delete_mode = 'ZAP' then
1282 hr_person_delete.moderate_predel_validation(p_person_id => p_person_id
1283 ,p_session_date =>p_session_date
1284 ,p_dt_delete_mode => p_dt_delete_mode -- 4169275
1285 );
1286 end if;
1287 --
1288 END strong_predel_validation;
1289 -------------------- END: strong_predel_validation -----------------------
1290 --
1291 -------------------- BEGIN: check_contact ---------------------------------
1292 /*
1293 NAME
1294 check_contact
1295 DESCRIPTION
1296 Is this contact a contact for anybody else? If so then do nothing.
1297 If not then check if this person has ever been an employee or
1298 applicant. If they have not then check whether they have any extra
1299 info entered for them (other than default info). If they have not
1300 then delete this contact also. Otherwise do nothing.
1301 NOTES
1302 p_person_id non-contact in relationship
1303 p_contact_person_id contact in this relationship - the person
1304 who the check is performed against.
1305 p_contact_relationship_id relationship which is currently being
1306 considered for this contact.
1307 */
1308 --
1309 PROCEDURE check_contact (p_person_id IN number,
1310 p_contact_person_id IN number,
1311 p_contact_relationship_id IN number,
1312 p_session_date IN date)
1313 IS
1314 --
1315 v_contact_elsewhere varchar2(1);
1316 v_other_only varchar2(1);
1317 v_delete_contact varchar2(1);
1318 --
1319 BEGIN
1320 --
1321 hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 1);
1322 --
1323 hr_person_delete.person_existance_check(P_CONTACT_PERSON_ID);
1324 --
1325 begin
1326 select 'Y'
1327 into v_contact_elsewhere
1328 from sys.dual
1329 where exists (
1330 select null
1331 from per_contact_relationships r
1332 where r.contact_relationship_id <> P_CONTACT_RELATIONSHIP_ID
1333 and r.contact_person_id = P_CONTACT_PERSON_ID);
1334 exception
1335 when NO_DATA_FOUND then null;
1336 end;
1337 --
1338 if SQL%ROWCOUNT > 0 then
1339 return;
1340 end if;
1341 --
1342 hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 2);
1343 --
1344 begin
1345 select 'Y'
1346 into v_other_only
1347 from sys.dual
1348 where not exists
1349 (select null
1350 from per_people_f p
1351 where p.person_id = P_CONTACT_PERSON_ID
1352 and p.current_emp_or_apl_flag = 'Y');
1353 exception
1354 when NO_DATA_FOUND then return;
1355 end;
1356 --
1357 begin
1358 --
1359 -- Can contact be deleted? If strong val errors then just trap
1360 -- error as we will continue as usual. If it succeeds then delete
1361 -- contact.
1362 --
1363 begin
1364 v_delete_contact := 'Y';
1365 hr_person_delete.strong_predel_validation(P_CONTACT_PERSON_ID,
1366 P_SESSION_DATE);
1367 exception
1368 when hr_utility.hr_error then
1369 v_delete_contact := 'N';
1370 end;
1371 --
1372 if v_delete_contact = 'Y' then
1373 hr_person_delete.people_default_deletes(P_CONTACT_PERSON_ID,
1374 TRUE);
1375 end if;
1376 --
1377 end;
1378 --
1379 --
1380 END check_contact;
1381 -------------------- END: check_contact ---------------------------------
1382 --
1383 -------------------- BEGIN: delete_a_person --------------------------------
1384 /*
1385 NAME
1386 delete_a_person
1387 DESCRIPTION
1388 Validates whether a person can be deleted from the HR database.
1389 It is assumed that weak_predel_validation and the other application
1390 *_delete_person.*_predel_valdation procedures have been successfully
1391 completed first.
1392 Cascades are all performed according to the locking ladder.
1393 NOTE
1394 P_FORM_CALL is set to 'Y' if this procedure is called from a forms
1395 module. In this case, the deletes are performed post-delete and a
1396 row therefore may not exist in per_people_f (for this person_id).
1397 For this reason the existance check will be ignored.
1398 */
1399 --
1400 PROCEDURE delete_a_person (p_person_id IN number,
1401 p_form_call IN boolean,
1402 p_session_date IN date)
1403 IS
1404 --
1405 cursor THIS_PERSONS_CONTACTS is
1406 select contact_person_id,
1407 contact_relationship_id
1408 from per_contact_relationships
1409 where person_id = P_PERSON_ID;
1410 --
1411 cursor LOCK_PERSON_ROWS is
1412 select person_id
1413 from per_people_f
1414 where person_id = P_PERSON_ID
1415 FOR UPDATE;
1416 --
1417 cursor LOCK_ASSIGNMENT_ROWS is
1418 select assignment_id
1419 from per_assignments_f
1420 where person_id = P_PERSON_ID
1421 FOR UPDATE;
1422 --
1423 cursor DELETE_COMPONENTS is
1424 select pp.pay_proposal_id
1425 from per_pay_proposals pp,
1426 per_assignments_f pa
1427 where pa.person_id = P_PERSON_ID
1428 and pa.assignment_id = pp.assignment_id
1429 FOR UPDATE;
1430 --
1431 CURSOR medical_assessment_records IS
1432 SELECT medical_assessment_id,
1433 object_version_number
1434 FROM per_medical_Assessments pma
1435 WHERE pma.person_id = p_person_id;
1436 --
1437 cursor WORK_INCIDENTS is
1438 select incident_id, object_version_number
1439 from per_work_incidents
1440 where person_id = p_person_id;
1441 --
1442 cursor DISABILITIES is
1443 select disability_id, object_version_number, effective_start_date, effective_end_date
1444 from per_disabilities_f
1445 where person_id = p_person_id;
1446 --
1447 cursor ROLES is
1448 select role_id, object_version_number
1449 from per_roles
1450 where person_id= p_person_id;
1451
1452 cursor c_ptu is
1453 select distinct person_type_usage_id
1454 -- ,ptu.effective_start_date
1455 -- ,ptu.object_version_number
1456 from per_person_type_usages_f ptu
1457 where ptu.person_id = p_person_id
1458 order by person_type_usage_id;
1459
1460 --
1461 -- v_dummy varchar2(1);
1462 v_dummy number(3); /* Bug 941 591 and 4873360*/
1463 v_proposal_id number;
1464 v_review_cursor number;
1465 v_rows_processed number;
1466 v_incident_id per_work_incidents.person_id%TYPE;
1467 v_object_version_number per_work_incidents.object_version_number%TYPE;
1468 v_disability_id per_disabilities_f.disability_id%TYPE;
1469 v_object_version_no per_disabilities_f.object_version_number%TYPE;
1470 v_effective_start_date per_disabilities_f.effective_start_date%TYPE;
1471 v_effective_end_date per_disabilities_f.effective_end_date%TYPE;
1472 v_ovn_roles per_roles.object_version_number%TYPE;
1473 v_role_id per_roles.role_id%TYPE;
1474 --
1475 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
1476 l_effective_date per_person_type_usages_f.effective_start_date%TYPE;
1477 l_object_version_number per_person_type_usages_f.object_version_number%TYPE;
1478 l_effective_start_date per_person_type_usages_f.effective_start_date%TYPE;
1479 l_effective_end_date per_person_type_usages_f.effective_end_date%TYPE;
1480
1481 -- bug fix 3732129 starts here.
1482 -- to improve performance assignment id fetched into a pl/sql table.
1483
1484 type assignmentid is table of per_all_assignments_f.assignment_id%type index by binary_integer;
1485 l_assignment_id assignmentid;
1486
1487 Cursor c_asg is
1488 select distinct assignment_id
1489 from per_assignments_f
1490 where person_id = p_person_id;
1491 -- bug fix 3732129 ends here.
1492
1493 BEGIN
1494 --
1495 --
1496 --
1497 if P_FORM_CALL = FALSE then
1498 hr_person_delete.person_existance_check(P_PERSON_ID);
1499 end if;
1500 --
1501 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 0);
1502 --
1503 -- Lock person rows, delete at end of procedure.
1504 --
1505 open LOCK_PERSON_ROWS;
1506 --
1507 -- Now start cascade.
1508 --
1509 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1);
1510 --
1511 -- bug fix 3732129 starts here.
1512 -- fetching the assignment ids into a pl/sql table.
1513
1514 open c_asg;
1515 fetch c_asg bulk collect into l_assignment_id;
1516 close c_asg;
1517
1518 -- bug fix 3732129 ends here.
1519
1520 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 888);
1521
1522 begin
1523 update per_requisitions r
1524 set r.person_id = null
1525 where r.person_id = P_PERSON_ID;
1526 exception
1527 when NO_DATA_FOUND then
1528 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 101);
1529 end;
1530 --
1531 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2);
1532 --
1533 begin
1534 -- bug fix 3732129.
1535 -- Delete statement modified to improve performance.
1536
1537 delete from per_letter_request_lines l
1538 where l.person_id = P_PERSON_ID;
1539
1540 forall i in 1..l_assignment_id.count
1541 delete from per_letter_request_lines l
1542 where l.assignment_id = l_assignment_id(i);
1543
1544 /*
1545 delete from per_letter_request_lines l
1546 where l.person_id = P_PERSON_ID
1547 or exists (
1548 select null
1549 from per_assignments_f a
1550 where a.person_id = P_PERSON_ID
1551 and a.assignment_id = l.assignment_id);*/
1552 exception
1553 when NO_DATA_FOUND then
1554 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 201);
1555 end;
1556 --
1557 -- Leave per_letter_requests for the moment - may not be necessary to
1558 -- delete the parent with no children which requires some work with
1559 -- cursors.
1560 --
1561 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3);
1562 --
1563 begin
1564 delete from per_absence_attendances a
1565 where a.person_id = P_PERSON_ID;
1566 exception
1567 when NO_DATA_FOUND then
1568 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 301);
1569 end;
1570 --
1571 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4);
1572 --
1573 begin
1574 update per_absence_attendances a
1575 set a.authorising_person_id = null
1576 where a.authorising_person_id = P_PERSON_ID;
1577 exception
1578 when NO_DATA_FOUND then
1579 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 401);
1580 end;
1581 --
1582 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 5);
1583 --
1584 begin
1585 update per_absence_attendances a
1586 set a.replacement_person_id = null
1587 where a.replacement_person_id = P_PERSON_ID;
1588 exception
1589 when NO_DATA_FOUND then
1590 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 501);
1591 end;
1592 --
1593 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 6);
1594 --
1595 begin
1596 delete from per_person_analyses a
1597 where a.person_id = P_PERSON_ID;
1598 exception
1599 when NO_DATA_FOUND then
1600 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 601);
1601 end;
1602 --
1603 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 8);
1604 --
1605 -- Delete of per_periods_of_service at end after delete of
1606 -- per_assignments_f.
1607 --
1608 begin
1609 update per_periods_of_service p
1610 set p.termination_accepted_person_id = null
1611 where p.termination_accepted_person_id = P_PERSON_ID;
1612 exception
1613 when NO_DATA_FOUND then
1614 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 801);
1615 end;
1616 --
1617 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 9);
1618 --
1619 begin
1620 update per_recruitment_activities r
1621 set r.authorising_person_id = null
1622 where r.authorising_person_id = P_PERSON_ID;
1623 exception
1624 when NO_DATA_FOUND then
1625 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 901);
1626 end;
1627 --
1628 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 10);
1629 --
1630 begin
1631 update per_recruitment_activities r
1632 set r.internal_contact_person_id = null
1633 where r.internal_contact_person_id = P_PERSON_ID;
1634 exception
1635 when NO_DATA_FOUND then
1636 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1001);
1637 end;
1638 --
1639 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',11);
1640 --
1641 -- Bug 4873360 fix for performance repository sql id 14959971.
1642 -- Rewrote the delete query commented out below (and already once tuned for
1643 -- bug 3619599) to avoid a merge join cartesian and a full table scan on
1644 -- PER_PARTICIPANTS, HR_QUEST_ANSWER_VALUES and PER_APPRAISALS
1645 --
1646 -- Broke query into two peices using conditional logic in a pl/sql block to
1647 -- see if delete needs to be run.
1648 --
1649 begin -- Delete from HR_QUEST_ANSWER_VALUES
1650 begin -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
1651 select 1
1652 into v_dummy
1653 from sys.dual
1654 where exists (
1655 select null
1656 from per_participants par
1657 where par.person_id = P_PERSON_ID);
1658
1659 if v_dummy = 1
1660 then
1661 v_dummy := null;
1662 delete from hr_quest_answer_values qsv2
1663 where qsv2.quest_answer_val_id in
1664 (select qsv.quest_answer_val_id
1665 from hr_quest_answer_values qsv
1666 ,hr_quest_answers qsa
1667 ,per_participants par
1668 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
1669 and qsa.type_object_id = par.participant_id
1670 and qsa.type = 'PARTICIPANT'
1671 and par.person_id = P_PERSON_ID);
1672 end if;
1673 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 215); --added for bug 5464252
1674 /*Start of bug 5464252*/
1675 exception
1676 when NO_DATA_FOUND then
1677 v_dummy := null;
1678 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 211);
1679 /*End of bug 5464252*/
1680 end; -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
1681
1682 begin -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
1683 select 2
1684 into v_dummy
1685 from sys.dual
1686 where exists (
1687 select null
1688 from per_appraisals apr
1689 where (apr.appraiser_person_id = P_PERSON_ID
1690 or apr.appraisee_person_id = P_PERSON_ID));
1691
1692 if v_dummy = 2
1693 then
1694 v_dummy := null;
1695 delete from hr_quest_answer_values qsv2
1696 where qsv2.quest_answer_val_id in
1697 (select qsv.quest_answer_val_id
1698 from hr_quest_answer_values qsv
1699 ,hr_quest_answers qsa
1700 ,per_appraisals apr
1701 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
1702 and qsa.type_object_id = apr.appraisal_id
1703 and qsa.type='APPRAISAL'
1704 and (apr.appraisee_person_id = P_PERSON_ID
1705 or apr.appraiser_person_id = P_PERSON_ID));
1706 end if;
1707 /* start of bug 5464252 */
1708 exception
1709 when NO_DATA_FOUND then
1710 v_dummy := null;
1711 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 220);
1712 /* end of bug 5464252 */
1713 end; -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
1714 end; -- Delete from HR_QUEST_ANSWER_VALUES
1715 -- original sql.
1716 -- begin
1717 -- Delete from HR_QUEST_ANSWER_VALUES
1718 /* delete from hr_quest_answer_values qsv2
1719 where qsv2.quest_answer_val_id in (
1720 select qsv.quest_answer_val_id
1721 from hr_quest_answer_values qsv
1722 , hr_quest_answers qsa
1723 , per_appraisals apr
1724 , per_participants par
1725 where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
1726 and (qsa.type_object_id = apr.appraisal_id
1727 and qsa.type='APPRAISAL'
1728 and (apr.appraisee_person_id = P_PERSON_ID
1729 or apr.appraiser_person_id = P_PERSON_ID))
1730 or (qsa.type_object_id = par.participant_id
1731 and qsa.type='PARTICIPANT'
1732 and par.person_id = P_PERSON_ID)
1733 ); -- Fix 3619599
1734 exception
1735 when NO_DATA_FOUND then
1736 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1101);
1737 end;
1738 */
1739 -- Now delete from HR_QUEST_ANSWERS
1740 --
1741
1742 -- fix for the bug 8265994
1743 BEGIN
1744 DELETE
1745 FROM per_objectives
1746 WHERE scorecard_id IN (SELECT scorecard_id
1747 FROM per_personal_scorecards
1748 WHERE person_id = P_PERSON_ID)
1749 OR appraisal_id in (SELECT appraisal_id
1750 FROM per_appraisals
1751 WHERE appraisee_person_id = p_person_id) ;
1752
1753 DELETE
1754 FROM per_scorecard_sharing
1755 WHERE scorecard_id IN (SELECT scorecard_id
1756 FROM per_personal_scorecards
1757 WHERE person_id = P_PERSON_ID);
1758 DELETE
1759 FROM hr_api_transaction_steps
1760 WHERE transaction_id IN (SELECT a.transaction_id
1761 FROM hr_api_transactions a,
1762 per_personal_scorecards sc
1763 WHERE a.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
1764 AND a.transaction_ref_id = sc.scorecard_id
1765 AND sc.person_id = P_PERSON_ID);
1766
1767 DELETE
1768 FROM hr_api_transactions
1769 WHERE transaction_id IN (SELECT transaction_id
1770 FROM hr_api_transactions a,
1771 per_personal_scorecards sc
1772 WHERE a.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
1773 AND a.transaction_ref_id = sc.scorecard_id
1774 AND sc.person_id = P_PERSON_ID);
1775 DELETE
1776 FROM per_personal_scorecards
1777 WHERE person_id = P_PERSON_ID;
1778
1779 EXCEPTION
1780 WHEN Others Then
1781 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1111);
1782
1783 END;
1784 BEGIN
1785 DELETE
1786 FROM per_competence_elements
1787 WHERE assessment_id IN (SELECT assessment_id
1788 FROM per_assessments
1789 WHERE appraisal_id IN (SELECT appraisal_id
1790 FROM per_appraisals
1791 WHERE appraisee_person_id = P_PERSON_ID));
1792 DELETE
1793 FROM per_performance_ratings
1794 WHERE appraisal_id IN (SELECT appraisal_id
1795 FROM per_appraisals
1796 WHERE appraisee_person_id = P_PERSON_ID);
1797 DELETE
1798 FROM per_assessments
1799 WHERE appraisal_id IN (SELECT appraisal_id
1800 FROM per_appraisals
1801 WHERE appraisee_person_id = P_PERSON_ID);
1802
1803 EXCEPTION
1804 WHEN Others Then
1805 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1112);
1806 END;
1807 -- fix for the bug 8265994
1808 --
1809 begin
1810 delete from hr_quest_answers qsa2
1811 where qsa2.questionnaire_answer_id in (
1812 select qsa.questionnaire_answer_id
1813 from hr_quest_answers qsa
1814 , per_participants par
1815 , per_appraisals apr
1816 where (qsa.type_object_id = apr.appraisal_id
1817 and qsa.type='APPRAISAL'
1818 and (apr.appraiser_person_id = P_PERSON_ID
1819 or apr.appraisee_person_id = P_PERSON_ID))
1820 or (qsa.type_object_id = par.participant_id
1821 and qsa.type='PARTICIPANT'
1822 and par.person_id = P_PERSON_ID)
1823 ); -- Fix 3619599
1824 exception
1825 when NO_DATA_FOUND then
1826 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1102);
1827 end;
1828 --
1829 -- Now delete from per_participants
1830 -- SQL Fixed for Performance
1831 begin
1832 DELETE
1833 FROM per_participants par2
1834 WHERE par2.person_id = P_PERSON_ID
1835 OR
1836 (
1837 par2.participation_in_column = 'APPRAISAL_ID'
1838 AND par2.participation_in_table = 'PER_APPRAISALS'
1839 AND par2.participation_in_id in
1840 (
1841 SELECT
1842 apr.appraisal_id
1843 FROM per_appraisals apr
1844 WHERE
1845 (
1846 apr.appraisee_person_id = P_PERSON_ID
1847 OR apr.appraiser_person_id = P_PERSON_ID
1848 )
1849 )
1850 );
1851 exception
1852 when NO_DATA_FOUND then
1853 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1103);
1854 end;
1855 --
1856 -- Now delete from per_appraisals
1857 --
1858 begin
1859 delete from per_appraisals apr
1860 where --apr.appraiser_person_id = P_PERSON_ID or -- changed as part of bug#8265994
1861 apr.appraisee_person_id = P_PERSON_ID;
1862 exception
1863 when NO_DATA_FOUND then
1864 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1104);
1865 end;
1866
1867 --
1868 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 12);
1869 --
1870 hr_security.delete_per_from_list(P_PERSON_ID);
1871 --
1872 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 14);
1873 --
1874 begin
1875 update per_vacancies v
1876 set v.recruiter_id = null
1877 where v.recruiter_id = P_PERSON_ID;
1878 exception
1879 when NO_DATA_FOUND then
1880 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1401);
1881 end;
1882 --
1883 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 15);
1884 --
1885 begin
1886 update per_assignments_f ass
1887 set ass.person_referred_by_id = null
1888 where ass.person_referred_by_id = P_PERSON_ID;
1889 exception
1890 when NO_DATA_FOUND then
1891 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1501);
1892 end;
1893 --
1894 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 16);
1895 --
1896 begin
1897 update per_assignments_f a
1898 set a.recruiter_id = null
1899 where a.recruiter_id = P_PERSON_ID;
1900 exception
1901 when NO_DATA_FOUND then
1902 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1601);
1903 end;
1904 --
1905 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 17);
1906 --
1907 begin
1908 update per_assignments_f a
1909 set a.supervisor_id = null
1910 where a.supervisor_id = P_PERSON_ID;
1911 exception
1912 when NO_DATA_FOUND then
1913 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1701);
1914 end;
1915 --
1916 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 18);
1917 --
1918 -- LOCK ASSIGNMENTS NOW: have to use cursor as cannot return >1 row for
1919 -- 'into' part of PL/SQL.
1920 --
1921 open LOCK_ASSIGNMENT_ROWS;
1922 --
1923 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 185);
1924 --
1925 begin
1926 --
1927 -- Bug 349818. Delete from per_pay_proposal_components before
1928 -- deleting from the parent record in per_pay_proposals to
1929 -- maintain referential integrity, using the cursor DELETE_COMPONENTS
1930 -- and the original per_pay_proposals delete.
1931 --
1932 open DELETE_COMPONENTS;
1933 LOOP
1934 FETCH DELETE_COMPONENTS INTO v_proposal_id;
1935 EXIT WHEN DELETE_COMPONENTS%NOTFOUND;
1936 DELETE FROM per_pay_proposal_components
1937 WHERE pay_proposal_id = v_proposal_id;
1938 END LOOP;
1939 close DELETE_COMPONENTS;
1940 --
1941 -- Now delete the parent proposal record.
1942 --
1943 delete from per_pay_proposals p
1944 where exists (
1945 select null
1946 from per_assignments_f ass
1947 where ass.assignment_id = p.assignment_id
1948 and ass.person_id = P_PERSON_ID);
1949 exception
1950 when NO_DATA_FOUND then
1951 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1801);
1952 end;
1953 --
1954 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 19);
1955 --
1956 begin
1957 delete from pay_personal_payment_methods_f m
1958 where m.assignment_id in (
1959 select ass.assignment_id
1960 from per_assignments_f ass
1961 where ass.person_id = P_PERSON_ID);
1962 exception
1963 when NO_DATA_FOUND then
1964 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1901);
1965 end;
1966 --
1967 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 20);
1968 --
1969 begin
1970 delete from per_assignment_budget_values_f a
1971 where a.assignment_id in (
1972 select ass.assignment_id
1973 from per_assignments_f ass
1974 where ass.person_id = P_PERSON_ID);
1975 exception
1976 when NO_DATA_FOUND then
1977 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2001);
1978 end;
1979 --
1980 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 21);
1981 --
1982 begin
1983 delete from per_assignment_extra_info a
1984 where a.assignment_id in (
1985 select ass.assignment_id
1986 from per_assignments_f ass
1987 where ass.person_id = P_PERSON_ID);
1988 exception
1989 when NO_DATA_FOUND then
1990 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2101);
1991 end;
1992 --
1993 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 22);
1994 --
1995 begin
1996 delete from per_secondary_ass_statuses a
1997 where a.assignment_id in (
1998 select ass.assignment_id
1999 from per_assignments_f ass
2000 where ass.person_id = P_PERSON_ID);
2001 exception
2002 when NO_DATA_FOUND then
2003 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2201);
2004 end;
2005 --
2006 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23);
2007 --
2008 -- Delete COBRA references and then any contact relationships. COBRA
2009 -- must be deleted first as PER_COBRA_COV_ENROLLMENTS has a
2010 -- contact_relationship_id which may be constrained later.
2011 --
2012 begin
2013 delete from per_cobra_coverage_benefits c2
2014 where c2.cobra_coverage_enrollment_id in (
2015 select c.cobra_coverage_enrollment_id
2016 from per_cobra_cov_enrollments c
2017 where exists (
2018 select null
2019 from per_assignments_f ass
2020 where ass.assignment_id = c.assignment_id
2021 and ass.person_id = P_PERSON_ID)
2022 );
2023 exception
2024 when NO_DATA_FOUND then
2025 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2301);
2026 end;
2027 --
2028 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 230);
2029 --
2030 begin
2031 delete from per_cobra_coverage_benefits c2
2032 where c2.cobra_coverage_enrollment_id in (
2033 select c.cobra_coverage_enrollment_id
2034 from per_cobra_cov_enrollments c
2035 , per_contact_relationships r
2036 where r.contact_person_id = P_PERSON_ID
2037 and c.contact_relationship_id = r.contact_relationship_id
2038 and exists (
2039 select null
2040 from per_assignments_f ass
2041 where ass.assignment_id = c.assignment_id
2042 and ass.person_id = r.person_id)
2043 );
2044 exception
2045 when NO_DATA_FOUND then
2046 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23001);
2047 end;
2048 --
2049 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 231);
2050 --
2051 begin
2052 delete from per_cobra_coverage_statuses c2
2053 where c2.cobra_coverage_enrollment_id in (
2054 select c.cobra_coverage_enrollment_id
2055 from per_cobra_cov_enrollments c
2056 where exists (
2057 select null
2058 from per_assignments_f ass
2059 where ass.assignment_id = c.assignment_id
2060 and ass.person_id = P_PERSON_ID)
2061 );
2062 exception
2063 when NO_DATA_FOUND then
2064 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23101);
2065 end;
2066 --
2067 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 232);
2068 --
2069 begin
2070 delete from per_cobra_coverage_statuses c2
2071 where c2.cobra_coverage_enrollment_id in (
2072 select c.cobra_coverage_enrollment_id
2073 from per_cobra_cov_enrollments c
2074 , per_contact_relationships r
2075 where r.contact_person_id = P_PERSON_ID
2076 and c.contact_relationship_id = r.contact_relationship_id
2077 and exists (
2078 select null
2079 from per_assignments_f ass
2080 where ass.assignment_id = c.assignment_id
2081 and ass.person_id = r.person_id)
2082 );
2083 exception
2084 when NO_DATA_FOUND then
2085 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23201);
2086 end;
2087 --
2088 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 233);
2089 --
2090 begin
2091 delete from per_sched_cobra_payments c2
2092 where c2.cobra_coverage_enrollment_id in (
2093 select c.cobra_coverage_enrollment_id
2094 from per_cobra_cov_enrollments c
2095 where exists (
2096 select null
2097 from per_assignments_f ass
2098 where ass.assignment_id = c.assignment_id
2099 and ass.person_id = P_PERSON_ID)
2100 );
2101 exception
2102 when NO_DATA_FOUND then
2103 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23301);
2104 end;
2105 --
2106 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 234);
2107 --
2108 begin
2109 delete from per_sched_cobra_payments c2
2110 where c2.cobra_coverage_enrollment_id in (
2111 select c.cobra_coverage_enrollment_id
2112 from per_cobra_cov_enrollments c
2113 , per_contact_relationships r
2114 where r.contact_person_id = P_PERSON_ID
2115 and c.contact_relationship_id = r.contact_relationship_id
2116 and exists (
2117 select null
2118 from per_assignments_f ass
2119 where ass.assignment_id = c.assignment_id
2120 and ass.person_id = r.person_id)
2121 );
2122 exception
2123 when NO_DATA_FOUND then
2124 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23401);
2125 end;
2126 --
2127 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 235);
2128 --
2129 begin
2130 delete from per_cobra_cov_enrollments c
2131 where c.assignment_id in (
2132 select ass.assignment_id
2133 from per_assignments_f ass
2134 where ass.person_id = P_PERSON_ID);
2135 exception
2136 when NO_DATA_FOUND then
2137 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23501);
2138 end;
2139 --
2140 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 236);
2141 --
2142 begin
2143 delete from per_cobra_cov_enrollments c
2144 where exists
2145 (select null
2146 from per_contact_relationships r
2147 where r.contact_person_id = P_PERSON_ID
2148 and c.contact_relationship_id = r.contact_relationship_id
2149 and exists (
2150 select null
2151 from per_assignments_f ass
2152 where ass.assignment_id = c.assignment_id
2153 and ass.person_id = r.person_id)
2154 );
2155 exception
2156 when NO_DATA_FOUND then
2157 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23601);
2158 end;
2159 --
2160 --Bug# 3026024 Start Here
2161 --Description : Delete the entry in the table ben_covered_dependents_f for the
2162 -- contact person whom is getting deleted.
2163 --
2164 --
2165 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 237);
2166 --
2167 begin
2168 delete from ben_covered_dependents_f c
2169 where c.contact_relationship_id in (
2170 select r.contact_relationship_id
2171 from per_contact_relationships r
2172 where r.contact_person_id = p_person_id
2173 );
2174 exception
2175 when NO_DATA_FOUND then
2176 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);
2177 end;
2178 --
2179 --Bug# 3026024 End Here
2180 --
2181
2182 --
2183 -- If this person has any contacts then check whether they have had any
2184 -- extra info entered for them. If they have not then delete the
2185 -- contacts as well. If they do have extra info then just delete the
2186 -- relationship.
2187 --
2188 -- NB If b is created as a contact of b then 2 contact relationships are
2189 -- are created: a,b and b,a so that they can be queried in either
2190 -- direction. Hence must delete both here.
2191 --
2192 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 237);
2193 --
2194 begin
2195 select count(*)
2196 into v_dummy
2197 from per_contact_relationships r
2198 where r.person_id = P_PERSON_ID;
2199 --
2200 if v_dummy > 0 then
2201 for EACH_CONTACT in THIS_PERSONS_CONTACTS loop
2202 --
2203 delete from per_contact_relationships r
2204 where (r.person_id = P_PERSON_ID
2205 and r.contact_person_id = EACH_CONTACT.CONTACT_PERSON_ID)
2206 or (r.person_id = EACH_CONTACT.CONTACT_PERSON_ID
2207 and r.contact_person_id = P_PERSON_ID);
2208 --
2209 hr_person_delete.check_contact(P_PERSON_ID,
2210 EACH_CONTACT.CONTACT_PERSON_ID,
2211 EACH_CONTACT.CONTACT_RELATIONSHIP_ID,
2212 P_SESSION_DATE);
2213 end loop;
2214 end if;
2215 --
2216 exception
2217 when NO_DATA_FOUND then
2218 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);
2219 end;
2220 --
2221 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 24);
2222 --
2223 begin
2224 delete from per_contact_relationships r
2225 where r.contact_person_id = P_PERSON_ID;
2226 exception
2227 when NO_DATA_FOUND then
2228 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2401);
2229 end;
2230 --
2231 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25);
2232 --
2233 begin
2234 delete from per_addresses a
2235 where a.person_id = P_PERSON_ID;
2236 exception
2237 when NO_DATA_FOUND then
2238 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2501);
2239 end;
2240 --
2241 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25.5);
2242 --
2243 begin
2244 delete from per_phones a
2245 where a.parent_id = P_PERSON_ID
2246 and a.parent_table = 'PER_ALL_PEOPLE_F';
2247 exception
2248 when NO_DATA_FOUND then
2249 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25501);
2250 end;
2251 --
2252 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26);
2253 --
2254 -- we must do this delete in dynamic sql because the per_performance_reviews
2255 -- table will not exist if the database has not been upgraded to new salary admin
2256 -- (introduced April 1998). The procedure would not compile if this was not dynamic.
2257 -- if the table is not found then the error (which starts with 'ORA-00942') is ignored.
2258 begin
2259 v_review_cursor:=dbms_sql.open_cursor;
2260 dbms_sql.parse(v_review_cursor,'DELETE from PER_PERFORMANCE_REVIEWS
2261 where person_id=:x',dbms_sql.v7);
2262 dbms_sql.bind_variable(v_review_cursor, ':x',P_PERSON_ID);
2263 v_rows_processed:=dbms_sql.execute(v_review_cursor);
2264 dbms_sql.close_cursor(v_review_cursor);
2265 exception
2266 when NO_DATA_FOUND then dbms_sql.close_cursor(v_review_cursor);
2267 when OTHERS then
2268 dbms_sql.close_cursor(v_review_cursor);
2269 if(substr(sqlerrm,0,9)<>'ORA-00942') then
2270 raise;
2271 end if;
2272 end;
2273 --
2274 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 261);
2275 --
2276 -- About to delete interview events for assignments. However, must
2277 -- first delete bookings (interviewers) for those events.
2278 --
2279 begin
2280
2281 -- bug fix 3732129.
2282 -- Delete statement modified to improve performance.
2283
2284 forall i in 1..l_assignment_id.count
2285 delete from per_bookings b
2286 where b.event_id in (select e.event_id
2287 from per_events e
2288 where e.assignment_id = l_assignment_id(i));
2289
2290 /*delete from per_bookings b
2291 where b.event_id in
2292 (select e.event_id
2293 from per_events e
2294 where exists (
2295 select null
2296 from per_assignments_f ass
2297 where ass.assignment_id = e.assignment_id
2298 and ass.person_id = P_PERSON_ID)
2299 );*/
2300 exception
2301 when NO_DATA_FOUND then
2302 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26101);
2303 end;
2304 --
2305 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 262);
2306 --
2307 begin
2308 -- bug fix 3732129.
2309 -- Delete statement modified to improve performance.
2310
2311 forall i in 1..l_assignment_id.count
2312 delete from per_events e
2313 where e.assignment_id = l_assignment_id(i);
2314
2315 /* delete from per_events e
2316 where e.assignment_id in (
2317 select ass.assignment_id
2318 from per_assignments_f ass
2319 where ass.person_id = P_PERSON_ID);*/
2320 exception
2321 when NO_DATA_FOUND then
2322 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26201);
2323 end;
2324 --
2325 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 27);
2326 --
2327 begin
2328 update per_events e
2329 set e.internal_contact_person_id = null
2330 where e.internal_contact_person_id = P_PERSON_ID;
2331 exception
2332 when NO_DATA_FOUND then
2333 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2701);
2334 end;
2335 --
2336 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 28);
2337 --
2338 begin
2339 delete from per_bookings b
2340 where b.person_id = P_PERSON_ID;
2341 exception
2342 when NO_DATA_FOUND then
2343 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2801);
2344 end;
2345 --
2346 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 29);
2347 --
2348 begin
2349 delete from per_quickpaint_result_text q
2350 where q.assignment_id in (
2351 select ass.assignment_id
2352 from per_assignments_f ass
2353 where ass.person_id = P_PERSON_ID);
2354 exception
2355 when NO_DATA_FOUND then
2356 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2901);
2357 end;
2358 --
2359 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 30);
2360 --
2361 -- Validation has already been performed against
2362 -- hr_assignment_set_amendments in weak_predel_validation.
2363 --
2364 begin
2365 delete from hr_assignment_set_amendments h
2366 where h.assignment_id in (
2367 select ass.assignment_id
2368 from per_assignments_f ass
2369 where ass.person_id = P_PERSON_ID);
2370 exception
2371 when NO_DATA_FOUND then
2372 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3001);
2373 end;
2374 --
2375 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 31);
2376 --
2377 begin
2378 delete from pay_cost_allocations_f a
2379 where a.assignment_id in (
2380 select ass.assignment_id
2381 from per_assignments_f ass
2382 where ass.person_id = P_PERSON_ID);
2383 exception
2384 when NO_DATA_FOUND then
2385 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3101);
2386 end;
2387 --
2388 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 32);
2389 --
2390 begin
2391 delete from per_spinal_point_placements_f p
2392 where p.assignment_id in (
2393 select ass.assignment_id
2394 from per_assignments_f ass
2395 where ass.person_id = P_PERSON_ID);
2396 exception
2397 when NO_DATA_FOUND then
2398 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3201);
2399 end;
2400 --
2401 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 33);
2402 --
2403 -- Validation has already been performed against
2404 -- pay_assignment_actions in weak_predel_validation.
2405 --
2406 begin
2407 delete from pay_assignment_actions a
2408 where exists (
2409 select null
2410 from per_assignments_f ass
2411 where ass.person_id = P_PERSON_ID
2412 and ass.assignment_id = a.assignment_id);
2413 exception
2414 when NO_DATA_FOUND then
2415 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3301);
2416 end;
2417 --
2418 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 34);
2419 --
2420 begin
2421 delete from pay_assignment_latest_balances b
2422 where b.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', 3401);
2429 end;
2430 --
2431 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 35);
2432 --
2433 begin
2434 -- bug fix 3732129
2435 -- Delete statement modified to improve performance.
2436
2437 forall i in 1..l_assignment_id.count
2438 delete from pay_assignment_link_usages_f u
2439 where u.assignment_id = l_assignment_id(i);
2440
2441 /*delete from pay_assignment_link_usages_f u
2442 where
2443 u.assignment_id in (
2444 select ass.assignment_id
2445 from per_assignments_f ass
2446 where ass.person_id = P_PERSON_ID); */
2447 exception
2448 when NO_DATA_FOUND then
2449 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3501);
2450 end;
2451 --
2452 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 36);
2453 --
2454 begin
2455 delete from pay_element_entry_values_f v
2456 where v.element_entry_id in (
2457 select e.element_entry_id
2458 from pay_element_entries_f e
2459 where exists (
2460 select null
2461 from per_assignments_f ass
2462 where ass.assignment_id = e.assignment_id
2463 and ass.person_id = P_PERSON_ID)
2464 );
2465 exception
2466 when NO_DATA_FOUND then
2467 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3601);
2468 end;
2469 --
2470 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 37);
2471 --
2472 begin
2473 delete from pay_run_results r
2474 where r.source_type = 'E'
2475 and r.source_id in (
2476 select e.element_entry_id
2477 from pay_element_entries_f e
2478 where exists (
2479 select null
2480 from per_assignments_f ass
2481 where ass.assignment_id = e.assignment_id
2482 and ass.person_id = P_PERSON_ID)
2483 );
2484 exception
2485 when NO_DATA_FOUND then
2486 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3701);
2487 end;
2488 --
2489 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 38);
2490 --
2491 begin
2492 delete from pay_element_entries_f e
2493 where e.assignment_id in (
2494 select ass.assignment_id
2495 from per_assignments_f ass
2496 where ass.person_id = P_PERSON_ID);
2497 exception
2498 when NO_DATA_FOUND then
2499 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3801);
2500 end;
2501 --
2502 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 39);
2503 --
2504 -- Rmonge Bug 1686922 22-FEB-2002
2505 -- Tax records were not being deleted. Therefore, there were orphans rows in
2506 -- the pay_us_fed_tax_rules_f, pay_us_state_tax_rules_f,
2507 -- pay_us_county_tax_rules_f, and pay_us_city_tax_rules_f.
2508 --
2509 begin
2510
2511 Delete pay_us_emp_fed_tax_rules_f peft
2512 Where peft.assignment_id in (
2513 select ass.assignment_id
2514 from per_assignments_f ass
2515 where ass.person_id = p_person_id );
2516 exception
2517 when no_data_found then
2518 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3802);
2519 end;
2520
2521 begin
2522
2523 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 40);
2524
2525 Delete pay_us_emp_state_tax_rules_f pest
2526 Where pest.assignment_id in (
2527 select ass.assignment_id
2528 from per_assignments_f ass
2529 where ass.person_id = p_person_id );
2530 exception
2531 when no_data_found then
2532 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3803);
2533 end;
2534
2535 begin
2536
2537 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 41);
2538
2539 Delete pay_us_emp_county_tax_rules_f pect
2540 Where pect.assignment_id in (
2541 select ass.assignment_id
2542 from per_assignments_f ass
2543 where ass.person_id = p_person_id );
2544 exception
2545 when no_data_found then
2546 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3804);
2547 end;
2548
2549 begin
2550
2551 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 42);
2552
2553 Delete pay_us_emp_city_tax_rules_f pecit
2554 Where pecit.assignment_id in (
2555 select ass.assignment_id
2556 from per_assignments_f ass
2557 where ass.person_id = p_person_id );
2558 exception
2559 when no_data_found then
2560 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3805);
2561 end;
2562
2563 -- Finished, now unlock assignments and delete them.
2564 --
2565 close LOCK_ASSIGNMENT_ROWS;
2566 --
2567 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 43);
2568 --
2569 begin
2570 delete from per_all_assignments_f a
2571 where a.person_id = P_PERSON_ID;
2572 exception
2573 when NO_DATA_FOUND then
2574 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4001);
2575 end;
2576 --
2577 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 44);
2578 --
2579 begin
2580 delete from per_periods_of_service p
2581 where p.person_id = P_PERSON_ID;
2582 exception
2583 when NO_DATA_FOUND then
2584 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4101);
2585 end;
2586 --
2587 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 45);
2588 --
2589 begin
2590 delete from per_applications a
2591 where a.person_id = P_PERSON_ID;
2592 exception
2593 when NO_DATA_FOUND then
2594 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4201);
2595 end;
2596 --
2597 -- 03/18/98 Bug #642566
2598 -- delete per_people_extra_info records
2599 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 46);
2600 --
2601 begin
2602 delete from per_people_extra_info e
2603 where e.person_id = P_PERSON_ID;
2604 exception
2605 when NO_DATA_FOUND then
2606 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4301);
2607 end;
2608 -- 03/18/98 Change Ends
2609 --
2610 -- 03/18/98 Change Ends
2611 --
2612 -- 28/5/98
2613 -- Add delete from per_person_type_usages_f
2614 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',47);
2615
2616 for ptu_rec in c_ptu loop
2617
2618 select min(ptu1.effective_start_date)
2619 into l_effective_date
2620 from per_person_type_usages_f ptu1
2621 where ptu1.person_type_usage_id = ptu_rec.person_type_usage_id;
2622
2623 select ptu2.object_version_number
2624 into l_object_version_number
2625 from per_person_type_usages_f ptu2
2626 where ptu2.person_type_usage_id = ptu_rec.person_type_usage_id
2627 and ptu2.effective_start_date = l_effective_date;
2628
2629 hr_utility.set_location('l_person_type_usage_id = '||to_char(ptu_rec.person_type_usage_id),44);
2630 hr_utility.set_location('l_effective_date = '||to_char(l_effective_date,'DD/MM/YYYY'),44);
2631 hr_utility.set_location('l_object_version_number = '||to_char(l_object_version_number),44);
2632 begin
2633 -- hr_per_type_usage_internal.maintain_ptu(
2634 -- p_person_id => p_person_id,
2635 -- p_action => 'DELETE',
2636 -- p_period_of_service_id => NULL,
2637 -- p_actual_termination_date => NULL,
2638 -- p_business_group_id => NULL,
2639 -- p_date_start => NULL,
2640 -- p_leaving_reason => NULL,
2641 -- p_old_date_start => NULL,
2642 -- p_old_leaving_reason => NULL);
2643
2644 hr_per_type_usage_internal.delete_person_type_usage
2645 (p_person_type_usage_id => ptu_rec.person_type_usage_id
2646 ,p_effective_date => l_effective_date
2647 ,p_datetrack_mode => 'ZAP'
2648 ,p_object_version_number => l_object_version_number
2649 ,p_effective_start_date => l_effective_start_date
2650 ,p_effective_end_date => l_effective_end_date
2651 );
2652 exception
2653 when NO_DATA_FOUND then null;
2654 end;
2655
2656 end loop;
2657 --
2658 -- delete per_person_dlvry_methods
2659 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 48);
2660 --
2661 begin
2662 delete from per_person_dlvry_methods
2663 where person_id = P_PERSON_ID;
2664 exception
2665 when NO_DATA_FOUND then null;
2666 end;
2667 --
2668 -- Added this delete for quickhire checklists
2669 --
2670 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 49);
2671 begin
2672 delete from per_checklist_items
2673 where person_id = P_PERSON_ID;
2674 exception
2675 when NO_DATA_FOUND then null;
2676 end;
2677 --
2678 -- End addition for quickhire checklists
2679 --
2680 -- delete per_qualification and per_subjects_taken records
2681 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 50);
2682 --
2683 begin
2684
2685 --PMFLETCH Added delete from tl table
2686 delete from per_subjects_taken_tl st
2687 where st.subjects_taken_id IN ( select s.subjects_taken_id
2688 from per_subjects_taken s
2689 , per_qualifications q
2690 where q.person_id = P_PERSON_ID
2691 and s.qualification_id = q.qualification_id
2692 );
2693
2694 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4698);
2695
2696
2697 delete from per_subjects_taken s
2698 where s.qualification_id in ( select qualification_id
2699 from per_qualifications
2700 where person_id = P_PERSON_ID );
2701
2702 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4699);
2703
2704 --PMFLETCH Added delete from tl table
2705 delete from per_qualifications_tl qt
2706 where qt.qualification_id in ( select q.qualification_id
2707 from per_qualifications q
2708 where q.person_id = P_PERSON_ID
2709 );
2710
2711 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4700);
2712
2713 delete from per_qualifications q
2714 where q.person_id = P_PERSON_ID;
2715
2716 exception
2717 when NO_DATA_FOUND then
2718 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4701);
2719 end;
2720 --
2721
2722 close LOCK_PERSON_ROWS;
2723 --
2724 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 99);
2725 --
2726 begin
2727 delete from per_all_people_f
2728 where person_id = P_PERSON_ID;
2729 exception
2730 when NO_DATA_FOUND then
2731 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4401);
2732 end;
2733 --
2734 -- Now remove contracts
2735 --
2736 hr_contract_api.maintain_contracts (
2737 P_PERSON_ID,
2738 NULL,
2739 NULL);
2740 --
2741 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 100);
2742 --
2743 -- Now remove Medical Assessments
2744 --
2745 FOR mea_rec IN medical_assessment_records LOOP
2746 --
2747 per_medical_assessment_api.delete_medical_assessment
2748 (FALSE
2749 ,mea_rec.medical_assessment_id
2750 ,mea_rec.object_version_number);
2751 --
2752 END LOOP;
2753 --
2754 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 110);
2755 --
2756 --
2757 -- Now remove disabilities
2758 --
2759 open DISABILITIES;
2760 loop
2761 fetch DISABILITIES INTO v_disability_id, v_object_version_no, v_effective_start_date, v_effective_end_date;
2762 EXIT when DISABILITIES%NOTFOUND;
2763 per_disability_api.delete_disability(false,p_session_date ,'ZAP',v_disability_id, v_object_version_no, v_effective_start_date, v_effective_end_date);
2764 END LOOP;
2765 --
2766 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 120);
2767 --
2768 --
2769 -- Now remove Work incidences
2770 --
2771 open WORK_INCIDENTS;
2772 loop
2773 fetch WORK_INCIDENTS INTO v_incident_id, v_object_version_number;
2774 EXIT when WORK_INCIDENTS%NOTFOUND;
2775 per_work_incident_api.delete_work_incident(false,v_incident_id, v_object_version_number);
2776 END LOOP;
2777 --
2778 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 130);
2779 --
2780 --
2781 -- Now remove Supplementary Roles
2782 --
2783 open ROLES;
2784 loop
2785 fetch ROLES into v_role_id, v_ovn_roles;
2786 EXIT when ROLES%notfound;
2787 per_supplementary_role_api.delete_supplementary_role(false, v_role_id, v_ovn_roles);
2788 END LOOP;
2789 --
2790 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 150);
2791 --
2792 --
2793 begin
2794 delete from per_periods_of_placement p
2795 where p.person_id = P_PERSON_ID;
2796 exception
2797 when NO_DATA_FOUND then
2798 hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4501);
2799 end;
2800 --
2801 --
2802 --
2803 END delete_a_person;
2804 -------------------- END: delete_a_person ----------------------------------
2805 --
2806 -------------------- BEGIN: people_default_deletes -------------------------
2807 /*
2808 NAME
2809 people_default_deletes
2810 DESCRIPTION
2811 Delete routine for deleting information set up as default when people
2812 are created. Used primarily for delete on PERPEEPI (Enter Person).
2813 The strong_predel_validation should first be performed to ensure that
2814 no additional info (apart from default) has been entered.
2815 NOTE
2816 See delete_a_person for p_form_call details. Further, p_form_call is
2817 set to TRUE when this procedure is called from check_contact as
2818 there is no need to check the existance of the contact.
2819 */
2820 --
2821 PROCEDURE people_default_deletes (p_person_id IN number,
2822 p_form_call IN boolean)
2823 IS
2824 --
2825 v_assignment_id number(15);
2826 --
2827 cursor LOCK_PERSON_ROWS is
2828 select person_id
2829 from per_people_f
2830 where person_id = P_PERSON_ID
2831 FOR UPDATE;
2832 --
2833 BEGIN
2834 --
2835 --
2836 if P_FORM_CALL = FALSE then
2837 hr_person_delete.person_existance_check(P_PERSON_ID);
2838 end if;
2839 --
2840 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 1);
2841 --
2842 open LOCK_PERSON_ROWS;
2843 --
2844 -- Now start cascade.
2845 --
2846 -- Start of Fix for WWBUG 1294400
2847 -- All of benefits is a child of HR and PAY so its safe to delete
2848 -- benefits stuff first.
2849 --
2850 ben_person_delete.delete_ben_rows(p_person_id);
2851 --
2852 -- End of Fix for WWBUG 1294400
2853 --
2854 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 2);
2855 --
2856 hr_security.delete_per_from_list(P_PERSON_ID);
2857 --
2858 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 4);
2859 --
2860 -- Lock assignments now, delete at end.
2861 -- Can select into a variable as max one assignment should exist (as
2862 -- strong_predel_validation has already been performed).
2863 -- May not be assignments (for contacts, for eg) so exception.
2864 --
2865 begin
2866 select ass.assignment_id
2867 into v_assignment_id
2868 from per_assignments_f ass
2869 where ass.person_id = P_PERSON_ID
2870 FOR UPDATE;
2871 exception
2872 when NO_DATA_FOUND then null;
2873 end;
2874 --
2875 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 5);
2876 --
2877 begin
2878 delete from pay_personal_payment_methods p
2879 where p.assignment_id = V_ASSIGNMENT_ID;
2880 exception
2881 when NO_DATA_FOUND then null;
2882 end;
2883 --
2884 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 6);
2885 --
2886 begin
2887 delete from per_assignment_budget_values_f v
2888 where v.assignment_id = V_ASSIGNMENT_ID;
2889 exception
2890 when NO_DATA_FOUND then null;
2891 end;
2892 --
2893 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 7);
2894 --
2895 begin
2896 delete from per_addresses a
2897 where a.person_id = P_PERSON_ID;
2898 exception
2899 when NO_DATA_FOUND then null;
2900 end;
2901 --
2902 hr_utility.set_location('HR_PERSON_DELETE.DELETE_DEFAULT_DELETES', 7.5);
2903 --
2904 begin
2905 delete from per_phones a
2906 where a.parent_id = P_PERSON_ID
2907 and a.parent_table = 'PER_ALL_PEOPLE_F';
2908 exception
2909 when NO_DATA_FOUND then null;
2910 end;
2911 --
2912 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 8);
2913 --
2914 begin
2915 delete from pay_cost_allocations_f a
2916 where a.assignment_id = V_ASSIGNMENT_ID;
2917 exception
2918 when NO_DATA_FOUND then null;
2919 end;
2920 --
2921 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 9);
2922 --
2923 begin
2924 delete from pay_element_entry_values_f v
2925 where exists (
2926 select null
2927 from pay_element_entries_f e
2928 where e.assignment_id = V_ASSIGNMENT_ID
2929 and e.element_entry_id = v.element_entry_id);
2930 exception
2931 when NO_DATA_FOUND then null;
2932 end;
2933 --
2934 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 10);
2935 --
2936 begin
2937 delete from pay_run_results r
2938 where r.source_type = 'E'
2939 and EXISTS (
2940 select null
2941 from pay_element_entries_f e
2942 where e.assignment_id = V_ASSIGNMENT_ID
2943 and e.element_entry_id = r.source_id);
2944 exception
2945 when NO_DATA_FOUND then null;
2946 end;
2947 --
2948 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 11);
2949 --
2950 begin
2951 delete from pay_element_entries_f e
2952 where e.assignment_id = V_ASSIGNMENT_ID;
2953 exception
2954 when NO_DATA_FOUND then null;
2955 end;
2956 --
2957 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 12);
2958 --
2959 -- No exception, should succeed.
2960 --
2961 begin
2962 delete from per_assignments_f ass
2963 where ass.assignment_id = V_ASSIGNMENT_ID;
2964 end;
2965 --
2966 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 13);
2967 --
2968 begin
2969 delete from per_periods_of_service p
2970 where p.person_id = P_PERSON_ID;
2971 exception
2972 when NO_DATA_FOUND then null;
2973 end;
2974 --
2975 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 14);
2976 --
2977 begin
2978 delete from per_applications a
2979 where a.person_id = P_PERSON_ID;
2980 exception
2981 when NO_DATA_FOUND then null;
2982 end;
2983 --
2984 -- Added this delete for quickhire checklists
2985 --
2986 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 17);
2987 begin
2988 delete from per_checklist_items
2989 where person_id = P_PERSON_ID;
2990 exception
2991 when NO_DATA_FOUND then null;
2992 end;
2993 --
2994 -- End addition for quickhire checklists
2995 --
2996 --
2997 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 15);
2998 --
2999 close LOCK_PERSON_ROWS;
3000 --
3001 hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 16);
3002 --
3003 begin
3004 delete from per_people_f
3005 where person_id = P_PERSON_ID;
3006 exception
3007 when NO_DATA_FOUND then null;
3008 end;
3009 --
3010 begin
3011 delete from per_periods_of_placement p
3012 where p.person_id = P_PERSON_ID;
3013 exception
3014 when NO_DATA_FOUND then null;
3015 end;
3016 --
3017 --
3018 END people_default_deletes;
3019 -------------------- END: people_default_deletes --------------------------
3020 --
3021 -------------------- BEGIN: applicant_default_deletes ---------------------
3022 /*
3023 NAME
3024 applicant_default_deletes
3025 DESCRIPTION
3026 Delete routine for deleting information set up as default when
3027 applicants are entered. Used primarily for delete on PERREAQE
3028 (Applicant Quick Entry). The strong_predel_validation should first be
3029 performed to ensure that no additional info (apart from default) has
3030 been entered.
3031 NOTE
3032 See delete_a_person for p_form_call details.
3033 */
3034 --
3035 PROCEDURE applicant_default_deletes (p_person_id IN number,
3036 p_form_call IN boolean)
3037 IS
3038 --
3039 v_assignment_id number(15);
3040 --
3041 cursor LOCK_PERSON_ROWS is
3042 select person_id
3043 from per_people_f
3044 where person_id = P_PERSON_ID
3045 FOR UPDATE;
3046 --
3047 BEGIN
3048 --
3049 --
3050 if P_FORM_CALL = FALSE then
3051 hr_person_delete.person_existance_check(P_PERSON_ID);
3052 end if;
3053 --
3054 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 1);
3055 --
3056 open LOCK_PERSON_ROWS;
3057 --
3058 -- Now start cascade.
3059 --
3060 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 3);
3061 --
3062 begin
3063 delete from per_person_list l
3064 where l.person_id = P_PERSON_ID;
3065 exception
3066 when NO_DATA_FOUND then null;
3067 end;
3068 --
3069 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 5);
3070 --
3071 -- Can select into a variable as only one assignment should exist (as
3072 -- strong_predel_validation has already been performed).
3073 --
3074 begin
3075 select ass.assignment_id
3076 into v_assignment_id
3077 from per_assignments_f ass
3078 where ass.person_id = P_PERSON_ID
3079 FOR UPDATE;
3080 end;
3081 --
3082 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 6);
3083 --
3084 begin
3085 delete from per_addresses a
3086 where a.person_id = P_PERSON_ID;
3087 exception
3088 when NO_DATA_FOUND then null;
3089 end;
3090 --
3091 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 6.5);
3092 --
3093 begin
3094 delete from per_phones a
3095 where a.parent_id = P_PERSON_ID
3096 and a.parent_table = 'PER_ALL_PEOPLE_F';
3097 exception
3098 when NO_DATA_FOUND then null;
3099 end;
3100 --
3101 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 7);
3102 --
3103 begin
3104 delete from per_assignments_f ass
3105 where ass.assignment_id = V_ASSIGNMENT_ID;
3106 end;
3107 --
3108 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 8);
3109 --
3110 begin
3111 delete from per_applications a
3112 where a.person_id = P_PERSON_ID;
3113 exception
3114 when NO_DATA_FOUND then null;
3115 end;
3116 --
3117 -- Added this delete for quickhire checklists
3118 --
3119 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 15);
3120 begin
3121 delete from per_checklist_items
3122 where person_id = P_PERSON_ID;
3123 exception
3124 when NO_DATA_FOUND then null;
3125 end;
3126 --
3127 -- End addition for quickhire checklists
3128 --
3129 --
3130 --
3131 -- Added this delete for PTU
3132 --
3133 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 16);
3134 begin
3135 delete from per_person_type_usages_f
3136 where person_id = P_PERSON_ID;
3137 exception
3138 when NO_DATA_FOUND then null;
3139 end;
3140 --
3141 -- End addition for PTU
3142 --
3143 --
3144 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3145 --
3146 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);
3147 --
3148 close LOCK_PERSON_ROWS;
3149 --
3150 hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 10);
3151 --
3152 begin
3153 delete from per_people_f
3154 where person_id = P_PERSON_ID;
3155 exception
3156 when NO_DATA_FOUND then null;
3157 end;
3158 --
3159 END applicant_default_deletes;
3160 -------------------- END: applicant_default_deletes -----------------------
3161 --
3162 end hr_person_delete;
[Home] [Help]