[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.
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');
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: --
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
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);
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
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
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
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'