1089: into l_delete_permitted
1090: from sys.dual
1091: where not exists (
1092: select null
1093: from per_participants par
1094: where par.person_id = P_PERSON_ID);
1095: --
1096: exception
1097: when NO_DATA_FOUND then
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: --
2254: into l_dummy
2255: from sys.dual
2256: where exists (
2257: select null
2258: from per_participants par
2259: where par.person_id = P_PERSON_ID);
2260: exception
2261: when NO_DATA_FOUND then
2262: l_dummy := null;
2271: where qsv2.quest_answer_val_id in
2272: (select qsv.quest_answer_val_id
2273: from hr_quest_answer_values qsv
2274: ,hr_quest_answers qsa
2275: ,per_participants par
2276: where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
2277: and qsa.type_object_id = par.participant_id
2278: and qsa.type = 'PARTICIPANT'
2279: and par.person_id = P_PERSON_ID);
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'
2334: and (apr.appraisee_person_id = P_PERSON_ID
2360: Union All
2361:
2362: select qsa.questionnaire_answer_id
2363: from hr_quest_answers qsa
2364: ,per_participants par
2365: where (qsa.type_object_id = par.participant_id
2366: and qsa.type='PARTICIPANT'
2367: and par.person_id = p_person_id )
2368: ) ;
2374: hr_utility.set_location(l_proc, 230);
2375: end if;
2376: end;
2377: --
2378: -- Now delete from per_participants
2379: begin
2380:
2381: -- Fix 4894116
2382: delete from per_participants par2
2378: -- Now delete from per_participants
2379: begin
2380:
2381: -- Fix 4894116
2382: delete from per_participants par2
2383: where par2.participant_id in
2384: ( select par.participant_id
2385: from per_participants par
2386: where par.person_id = P_PERSON_Id
2381: -- Fix 4894116
2382: delete from per_participants par2
2383: where par2.participant_id in
2384: ( select par.participant_id
2385: from per_participants par
2386: where par.person_id = P_PERSON_Id
2387: union all
2388: select par.participant_id
2389: from per_participants par
2385: from per_participants par
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'