[Home] [Help]
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
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.
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.
919: into v_delete_permitted
920: from sys.dual
921: where not exists (
922: select null
923: from per_participants par
924: where par.person_id = P_PERSON_ID);
925: exception
926: when NO_DATA_FOUND then
927: fnd_message.set_name(801,'PER_52467_APR_PAR_REC_NO_DEL');
1639: --
1640: -- Bug 4873360 fix for performance repository sql id 14959971.
1641: -- Rewrote the delete query commented out below (and already once tuned for
1642: -- bug 3619599) to avoid a merge join cartesian and a full table scan on
1643: -- PER_PARTICIPANTS, HR_QUEST_ANSWER_VALUES and PER_APPRAISALS
1644: --
1645: -- Broke query into two peices using conditional logic in a pl/sql block to
1646: -- see if delete needs to be run.
1647: --
1651: into v_dummy
1652: from sys.dual
1653: where exists (
1654: select null
1655: from per_participants par
1656: where par.person_id = P_PERSON_ID);
1657:
1658: if v_dummy = 1
1659: then
1662: where qsv2.quest_answer_val_id in
1663: (select qsv.quest_answer_val_id
1664: from hr_quest_answer_values qsv
1665: ,hr_quest_answers qsa
1666: ,per_participants par
1667: where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
1668: and qsa.type_object_id = par.participant_id
1669: and qsa.type = 'PARTICIPANT'
1670: and par.person_id = P_PERSON_ID);
1719: select qsv.quest_answer_val_id
1720: from hr_quest_answer_values qsv
1721: , hr_quest_answers qsa
1722: , per_appraisals apr
1723: , per_participants par
1724: where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
1725: and (qsa.type_object_id = apr.appraisal_id
1726: and qsa.type='APPRAISAL'
1727: and (apr.appraisee_person_id = P_PERSON_ID
1740: delete from hr_quest_answers qsa2
1741: where qsa2.questionnaire_answer_id in (
1742: select qsa.questionnaire_answer_id
1743: from hr_quest_answers qsa
1744: , per_participants par
1745: , per_appraisals apr
1746: where (qsa.type_object_id = apr.appraisal_id
1747: and qsa.type='APPRAISAL'
1748: and (apr.appraiser_person_id = P_PERSON_ID
1755: when NO_DATA_FOUND then
1756: hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1102);
1757: end;
1758: --
1759: -- Now delete from per_participants
1760: -- SQL Fixed for Performance
1761: begin
1762: DELETE
1763: FROM per_participants par2
1759: -- Now delete from per_participants
1760: -- SQL Fixed for Performance
1761: begin
1762: DELETE
1763: FROM per_participants par2
1764: WHERE par2.person_id = P_PERSON_ID
1765: OR
1766: (
1767: par2.participation_in_column = 'APPRAISAL_ID'