1068: into l_delete_permitted
1069: from sys.dual
1070: where not exists (
1071: select null
1072: from per_appraisals apr
1073: where apr.appraisee_person_id = P_PERSON_ID
1074: or apr.appraiser_person_id = P_PERSON_ID);
1075: --
1076: exception
2241: --
2242: -- Bug 4873360 fix for performance repository sql id 14960331.
2243: -- Rewrote the delete query commented out below (and already once tuned for
2244: -- bug 3619599) to avoid a merge join cartesian and a full table scan on
2245: -- PER_PARTICIPANTS, HR_QUEST_ANSWER_VALUES and PER_APPRAISALS
2246: --
2247: -- Broke query into two peices using conditional logic in a pl/sql block to
2248: -- see if delete needs to be run.
2249: --
2288: into l_dummy
2289: from sys.dual
2290: where exists (
2291: select null
2292: from per_appraisals apr
2293: where (apr.appraiser_person_id = P_PERSON_ID
2294: or apr.appraisee_person_id = P_PERSON_ID));
2295: exception
2296: when NO_DATA_FOUND then
2306: where qsv2.quest_answer_val_id in
2307: (select qsv.quest_answer_val_id
2308: from hr_quest_answer_values qsv
2309: ,hr_quest_answers qsa
2310: ,per_appraisals apr
2311: where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2312: and qsa.type_object_id = apr.appraisal_id
2313: and qsa.type='APPRAISAL'
2314: and (apr.appraisee_person_id = P_PERSON_ID
2325: where qsv2.quest_answer_val_id in
2326: (select qsv.quest_answer_val_id
2327: from hr_quest_answer_values qsv
2328: ,hr_quest_answers qsa
2329: ,per_appraisals apr
2330: ,per_participants par
2331: where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2332: and (qsa.type_object_id = apr.appraisal_id
2333: and qsa.type='APPRAISAL'
2351: where qsa2.questionnaire_answer_id in
2352: (
2353: select qsa.questionnaire_answer_id
2354: from hr_quest_answers qsa
2355: ,per_appraisals apr
2356: where (qsa.type_object_id = apr.appraisal_id
2357: and qsa.type='APPRAISAL'
2358: and (apr.appraiser_person_id = p_person_id
2359: or apr.appraisee_person_id = p_person_id))
2386: where par.person_id = P_PERSON_Id
2387: union all
2388: select par.participant_id
2389: from per_participants par
2390: ,per_appraisals apr
2391: where
2392: (par.participation_in_column = 'APPRAISAL_ID'
2393: and par.participation_in_table = 'PER_APPRAISALS'
2394: and participation_in_id = apr.appraisal_id
2389: from per_participants par
2390: ,per_appraisals apr
2391: where
2392: (par.participation_in_column = 'APPRAISAL_ID'
2393: and par.participation_in_table = 'PER_APPRAISALS'
2394: and participation_in_id = apr.appraisal_id
2395: and (apr.appraisee_person_id = P_PERSON_ID
2396: or apr.appraiser_person_id = p_person_id)
2397: )
2403: hr_utility.set_location(l_proc, 240);
2404: end if;
2405: end;
2406: --
2407: -- Now delete from per_appraisals
2408: --
2409: begin
2410: --
2411: delete from per_appraisals apr
2407: -- Now delete from per_appraisals
2408: --
2409: begin
2410: --
2411: delete from per_appraisals apr
2412: where apr.appraiser_person_id = P_PERSON_ID
2413: or apr.appraisee_person_id = P_PERSON_ID;
2414: --
2415: exception