[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y', fpi.status
into p_yes_no, p_status
from fnd_product_installations fpi
where fpi.status = 'I'
and fpi.application_id =
(select fa.application_id
from fnd_application fa
where fa.application_short_name = P_APPLICATION_SHORT_NAME
);
select count(*)
into l_dummy
from per_all_people_f p
where p.person_id = P_PERSON_ID;
l_delete_permitted varchar2(1);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists
(select null
from fnd_user aol
where aol.employee_id = P_PERSON_ID
);
l_delete_permitted varchar2(1);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists
(select null
from per_assignments_f ass,
hr_assignment_set_amendments asa
where asa.assignment_id = ass.assignment_id
and ass.person_id = P_PERSON_ID
and asa.include_or_exclude = 'I'
and not exists
(select null
from hr_assignment_set_amendments asa2
where asa2.assignment_set_id = asa.assignment_set_id
and asa2.assignment_id <> asa.assignment_id)
);
l_delete_permitted varchar2(1);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists
(select null
from pay_assignment_actions paa
,per_assignments_f ass
,pay_payroll_actions ppa
where paa.assignment_id = ass.assignment_id
and ass.person_id = P_PERSON_ID
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type <> 'Z');
ben_person_delete.check_ben_rows_before_delete(p_person_id
,p_effective_date);
SELECT l.element_type_id, e.effective_start_date,
e.effective_end_date, a.assignment_id
FROM pay_element_entries_f e,
per_assignments_f a,
pay_element_links_f l
WHERE a.person_id = P_PERSON_ID
and a.assignment_id = e.assignment_id
and e.effective_start_date between
a.effective_start_date and a.effective_end_date
and e.element_link_id = l.element_link_id
and e.effective_start_date between
l.effective_start_date and l.effective_end_date;
l_delete_permitted varchar2(1);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists
(select null
from per_assignments_f a
,per_contact_relationships c
,per_cobra_cov_enrollments e
where a.person_id = P_PERSON_ID
and a.assignment_id = e.assignment_id
and c.person_id = P_PERSON_ID
and c.contact_relationship_id = e.contact_relationship_id);
l_delete_permitted varchar2(1);
select null
into l_delete_permitted
from sys.dual
where not exists
(select null
from per_contracts_f
where person_id = p_person_id);
l_delete_contact varchar2(1);
select 'Y'
into l_contact_elsewhere
from sys.dual
where exists
(select null
from per_contact_relationships r
where r.contact_relationship_id <> P_CONTACT_RELATIONSHIP_ID
and r.contact_person_id = P_CONTACT_PERSON_ID);
select 'Y'
into l_other_only
from sys.dual
where not exists
(select null
from per_people_f p
where p.person_id = P_CONTACT_PERSON_ID
and p.current_emp_or_apl_flag = 'Y');
l_delete_contact := 'Y';
l_delete_contact := 'N';
if l_delete_contact = 'Y' then
hr_person_internal.people_default_deletes(P_CONTACT_PERSON_ID);
PROCEDURE delete_org_manager(p_person_id in number
,p_effective_date in date
,p_person_org_manager_warning out nocopy varchar2) IS
--
cursor csr_org_details(p_organization_id number) is
select org_info.organization_id,
org_info.org_information_id,
org_info.org_information_context,
org_info.org_information1,
org_info.org_information2,
org_info.org_information3,
org_info.org_information4,
org_info.org_information5,
org_info.org_information6,
org_info.org_information7,
org_info.org_information8,
org_info.org_information9,
org_info.org_information10,
org_info.org_information11,
org_info.org_information12,
org_info.org_information13,
org_info.org_information14,
org_info.org_information15,
org_info.org_information16,
org_info.org_information17,
org_info.org_information18,
org_info.org_information19,
org_info.org_information20,
org_info.object_version_number,
org_info.attribute_category,
org_info.attribute1,
org_info.attribute2,
org_info.attribute3,
org_info.attribute4,
org_info.attribute5,
org_info.attribute6,
org_info.attribute7,
org_info.attribute8,
org_info.attribute9,
org_info.attribute10,
org_info.attribute11,
org_info.attribute12,
org_info.attribute13,
org_info.attribute14,
org_info.attribute15,
org_info.attribute16,
org_info.attribute17,
org_info.attribute18,
org_info.attribute19,
org_info.attribute20
from hr_organization_information org_info
where org_info.organization_id = p_organization_id
and org_info.org_information_context = 'Organization Name Alias'
and org_info.org_information2 = to_char(p_person_id);
select org.organization_id,
org_tl.name
from hr_all_organization_units org,
hr_all_organization_units_tl org_tl
where org.organization_id = org_tl.organization_id
and exists (select null
from hr_organization_information org_info
where org_info.organization_id = org.organization_id
and org_info.org_information_context = 'Organization Name Alias'
and org_info.org_information2 = to_char(p_person_id));
hr_organization_api.delete_org_manager
(p_org_information_id => l_org_details.org_information_id,
p_object_version_number => l_org_details.object_version_number);
hr_organization_api.update_org_manager
(p_effective_date => p_effective_date
,p_organization_id => l_org_details.organization_id
,p_org_information_id => l_org_details.org_information_id
,p_org_info_type_code => l_org_details.org_information_context
,p_org_information1 => l_org_details.org_information1
,p_org_information2 => null
,p_org_information3 => null
,p_org_information4 => null
,p_org_information5 => l_org_details.org_information5
,p_org_information6 => l_org_details.org_information6
,p_org_information7 => l_org_details.org_information7
,p_org_information8 => l_org_details.org_information8
,p_org_information9 => l_org_details.org_information9
,p_org_information10 => l_org_details.org_information10
,p_org_information11 => l_org_details.org_information11
,p_org_information12 => l_org_details.org_information12
,p_org_information13 => l_org_details.org_information13
,p_org_information14 => l_org_details.org_information14
,p_org_information15 => l_org_details.org_information15
,p_org_information16 => l_org_details.org_information16
,p_org_information17 => l_org_details.org_information17
,p_org_information18 => l_org_details.org_information18
,p_org_information19 => l_org_details.org_information19
,p_org_information20 => l_org_details.org_information20
,p_attribute_category => l_org_details.attribute_category
,p_attribute1 => l_org_details.attribute1
,p_attribute2 => l_org_details.attribute2
,p_attribute3 => l_org_details.attribute3
,p_attribute4 => l_org_details.attribute4
,p_attribute5 => l_org_details.attribute5
,p_attribute6 => l_org_details.attribute6
,p_attribute7 => l_org_details.attribute7
,p_attribute8 => l_org_details.attribute8
,p_attribute9 => l_org_details.attribute9
,p_attribute10 => l_org_details.attribute10
,p_attribute11 => l_org_details.attribute11
,p_attribute12 => l_org_details.attribute12
,p_attribute13 => l_org_details.attribute13
,p_attribute14 => l_org_details.attribute14
,p_attribute15 => l_org_details.attribute15
,p_attribute16 => l_org_details.attribute16
,p_attribute17 => l_org_details.attribute17
,p_attribute18 => l_org_details.attribute18
,p_attribute19 => l_org_details.attribute19
,p_attribute20 => l_org_details.attribute20
,p_object_version_number => l_org_details.object_version_number
,p_warning => l_warning);
END delete_org_manager;
l_delete_permitted varchar2(1);
SELECT ext_chg_evt_log_id
FROM ben_ext_chg_evt_log
WHERE person_id = p_person_id
FOR UPDATE OF ext_chg_evt_log_id;
DELETE FROM ben_ext_chg_evt_log
WHERE CURRENT OF ben_ext_chg_log;
l_delete_permitted varchar2(1);
SELECT ext_chg_evt_log_id
FROM ben_ext_chg_evt_log
WHERE person_id = p_person_id
FOR UPDATE OF ext_chg_evt_log_id;
/*select count(*)
into l_person_types
from per_people_f ppf,
per_person_types ppt
where ppf.person_id = P_PERSON_ID
and ppf.effective_end_date >= p_effective_date
and ppf.person_type_id = ppt.person_type_id
and ( exists
(select null
from per_people_f ppf2,
per_person_types ppt2
where ppf2.person_id = ppf.person_id
and ppf2.effective_end_date >= p_effective_date
and ppf2.person_type_id = ppt2.person_type_id
and ppt2.system_person_type <> ppt.system_person_type
)
or exists
(select null
from per_periods_of_placement ppp
where ppp.person_id=ppf.person_id
and actual_termination_date>=p_effective_date
and actual_termination_date is not null)
or exists
(select null
from per_periods_of_placement ppp
where ppp.person_id=ppf.person_id
and ppp.date_start>p_effective_date
)
); */ --fix for bug 6730008.
select count(*)
into l_person_types
from per_person_type_usages_f ptu
where ptu.person_id = P_PERSON_ID
and ptu.effective_start_date > p_effective_date;
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_letter_request_lines r
where r.person_id = P_PERSON_ID
and r.date_from >= p_effective_date );
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_letter_request_lines r
where exists (
select null
from per_assignments_f a
where a.person_id = P_PERSON_ID
and a.effective_start_date >= p_effective_date
and a.assignment_id = r.assignment_id));
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_contact_relationships r
where r.person_id = P_PERSON_ID
or r.contact_person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_events e
where e.internal_contact_person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_bookings b
where b.person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where 1 >= (
select count(*)
from per_assignments_f a
where a.person_id = P_PERSON_ID
and a.effective_start_date >= p_effective_date);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_assignments_f a
where a.recruiter_id = P_PERSON_ID
or a.supervisor_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_periods_of_service p
where p.termination_accepted_person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_person_analyses a
where a.person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_absence_attendances a
where a.person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_absence_attendances a
where a.authorising_person_id = P_PERSON_ID
or a.replacement_person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_recruitment_activities r
where r.authorising_person_id = P_PERSON_ID
or r.internal_contact_person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_appraisals apr
where apr.appraisee_person_id = P_PERSON_ID
or apr.appraiser_person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_participants par
where par.person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_requisitions r
where r.person_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_vacancies v
where v.recruiter_id = P_PERSON_ID);
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from pay_element_entries_f e,
per_assignments_f a,
pay_element_links_f l
where a.person_id = P_PERSON_ID
and a.assignment_id = e.assignment_id
and e.element_link_id = l.element_link_id
and l.standard_link_flag = 'N');
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from pay_element_entries_f e,
per_assignments_f a
where a.person_id = P_PERSON_ID
and a.assignment_id = e.assignment_id
and e.entry_type <> 'E');
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_assignment_extra_info i
where exists (
select null
from per_assignments_f a
where a.person_id = P_PERSON_ID
and a.assignment_id = i.assignment_id));
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_secondary_ass_statuses s
where exists (
select null
from per_assignments_f a
where a.person_id = P_PERSON_ID
and a.assignment_id = s.assignment_id));
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_events e
where exists (
select null
from per_assignments_f a
where a.person_id = P_PERSON_ID
and a.assignment_id = e.assignment_id));
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_spinal_point_placements_f p
where exists (
select null
from per_assignments_f a
where a.person_id = P_PERSON_ID
and a.assignment_id = p.assignment_id));
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_quickpaint_result_text t
where exists (
select null
from per_assignments_f a
where a.person_id = P_PERSON_ID
and a.assignment_id = t.assignment_id));
select 'Y'
into l_delete_permitted
from sys.dual
where not exists (
select null
from per_cobra_cov_enrollments c
where exists (
select null
from per_assignments_f a
where a.person_id = P_PERSON_ID
and a.assignment_id = c.assignment_id));
DELETE FROM ben_ext_chg_evt_log
WHERE CURRENT OF ben_ext_chg_log;
ben_person_delete.perform_ri_check(p_person_id);
PROCEDURE people_default_deletes (p_person_id IN number)
IS
--
l_assignment_id number(15);
l_proc varchar2(72) := g_package||'people_default_deletes';
select person_id
from per_people_f
where person_id = P_PERSON_ID
FOR UPDATE;
SELECT attached_document_id
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value1
AND entity_name IN('PER_ALL_PEOPLE_F','PER_PEOPLE_F'); -- added for bug 9411745
select datatype_id
from fnd_attached_docs_form_vl
where attached_document_id = x_attached_document_id;
ben_person_delete.delete_ben_rows(p_person_id);
hr_security.delete_per_from_list(P_PERSON_ID);
select ass.assignment_id
into l_assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID
FOR UPDATE;
delete from pay_personal_payment_methods p
where p.assignment_id = l_assignment_id;
delete from per_assignment_budget_values_f v
where v.assignment_id = l_assignment_id;
delete from per_addresses a
where a.person_id = P_PERSON_ID;
delete from per_phones a
where a.parent_id = P_PERSON_ID
and a.parent_table = 'PER_ALL_PEOPLE_F';
delete from pay_cost_allocations_f a
where a.assignment_id = l_assignment_id;
delete from pay_element_entry_values_f v
where v.element_entry_id in
(select e.element_entry_id
from pay_element_entries_f e
where e.assignment_id = l_assignment_id);
delete from pay_run_results r
where r.source_type = 'E'
and r.source_id in
(select e.element_entry_id
from pay_element_entries_f e
where e.assignment_id = l_assignment_id);
delete from pay_element_entries_f e
where e.assignment_id = l_assignment_id;
delete from per_assignments_f ass
where ass.assignment_id = l_assignment_id;
delete from per_periods_of_service p
where p.person_id = P_PERSON_ID;
delete from per_applications a
where a.person_id = P_PERSON_ID;
delete from per_checklist_items
where person_id = P_PERSON_ID;
delete from per_people_f
where person_id = P_PERSON_ID;
delete from per_periods_of_placement p
where p.person_id = P_PERSON_ID;
FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
l_datatype_id,
'Y' );
END people_default_deletes;
PROCEDURE applicant_default_deletes(p_person_id IN number)
IS
--
l_assignment_id number(15);
l_proc varchar2(72) := g_package||'applicant_default_deletes';
SELECT person_id
FROM per_people_f
WHERE person_id = P_PERSON_ID
FOR UPDATE;
SELECT attached_document_id
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value2
AND entity_name IN('PER_ALL_PEOPLE_F','PER_PEOPLE_F'); -- added for bug 9411745
select datatype_id
from fnd_attached_docs_form_vl
where attached_document_id = x_attached_document_id;
delete from per_person_list l
where l.person_id = P_PERSON_ID;
select ass.assignment_id
into l_assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID
FOR UPDATE;
delete from per_addresses a
where a.person_id = P_PERSON_ID;
delete from per_phones a
where a.parent_id = P_PERSON_ID
and a.parent_table = 'PER_ALL_PEOPLE_F';
delete from per_assignments_f ass
where ass.assignment_id = l_assignment_id;
delete from per_applications a
where a.person_id = P_PERSON_ID;
delete from per_checklist_items
where person_id = P_PERSON_ID;
delete from per_person_type_usages_f
where person_id = P_PERSON_ID;
delete from per_people_f
where person_id = P_PERSON_ID;
FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
l_datatype_id,
'Y' );
END applicant_default_deletes;
PROCEDURE delete_person (p_person_id IN number
,p_effective_date IN date)
IS
--
l_pk1_value3 varchar2(72) := p_person_id;
SELECT contact_person_id,
contact_relationship_id
FROM per_contact_relationships
WHERE person_id = P_PERSON_ID;
SELECT person_id
FROM per_people_f
WHERE person_id = P_PERSON_ID
FOR UPDATE;
SELECT assignment_id
FROM per_assignments_f
WHERE person_id = P_PERSON_ID
FOR UPDATE;
CURSOR csr_delete_components IS
SELECT pp.pay_proposal_id
FROM per_pay_proposals pp,
per_assignments_f pa
WHERE pa.person_id = P_PERSON_ID
AND pa.assignment_id = pp.assignment_id
FOR UPDATE;
SELECT medical_assessment_id,
object_version_number
FROM per_medical_Assessments pma
WHERE pma.person_id = p_person_id;
SELECT incident_id, object_version_number
FROM per_work_incidents
WHERE person_id = p_person_id;
SELECT disability_id, object_version_number,
effective_start_date, effective_end_date
FROM per_disabilities_f
WHERE person_id = p_person_id;
SELECT role_id, object_version_number
FROM per_roles
WHERE person_id= p_person_id;
SELECT distinct person_type_usage_id
FROM per_person_type_usages_f ptu
WHERE ptu.person_id = p_person_id
ORDER BY person_type_usage_id;
SELECT distinct assignment_id
FROM per_assignments_f
WHERE person_id = p_person_id;
SELECT attached_document_id
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value3
AND entity_name IN('PER_ALL_PEOPLE_F','PER_PEOPLE_F'); -- added for bug 9411745
select datatype_id
from fnd_attached_docs_form_vl
where attached_document_id = x_attached_document_id;
l_proc varchar2(72) := g_package||'delete_person';
update per_requisitions r
set r.person_id = null
where r.person_id = P_PERSON_ID;
delete from per_letter_request_lines l
where l.person_id = P_PERSON_ID;
delete from per_letter_request_lines l
where l.assignment_id = l_assignment_id(i);
delete from per_absence_attendances a
where a.person_id = P_PERSON_ID;
update per_absence_attendances a
set a.authorising_person_id = null
where a.authorising_person_id = P_PERSON_ID;
update per_absence_attendances a
set a.replacement_person_id = null
where a.replacement_person_id = P_PERSON_ID;
delete from per_person_analyses a
where a.person_id = P_PERSON_ID;
update per_periods_of_service p
set p.termination_accepted_person_id = null
where p.termination_accepted_person_id = P_PERSON_ID;
update per_recruitment_activities r
set r.authorising_person_id = null
where r.authorising_person_id = P_PERSON_ID;
update per_recruitment_activities r
set r.internal_contact_person_id = null
where r.internal_contact_person_id = P_PERSON_ID;
begin -- Delete from HR_QUEST_ANSWER_VALUES
begin -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
begin
select 1
into l_dummy
from sys.dual
where exists (
select null
from per_participants par
where par.person_id = P_PERSON_ID);
delete from hr_quest_answer_values qsv2
where qsv2.quest_answer_val_id in
(select qsv.quest_answer_val_id
from hr_quest_answer_values qsv
,hr_quest_answers qsa
,per_participants par
where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
and qsa.type_object_id = par.participant_id
and qsa.type = 'PARTICIPANT'
and par.person_id = P_PERSON_ID);
end; -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
begin -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
begin
select 2
into l_dummy
from sys.dual
where exists (
select null
from per_appraisals apr
where (apr.appraiser_person_id = P_PERSON_ID
or apr.appraisee_person_id = P_PERSON_ID));
delete from hr_quest_answer_values qsv2
where qsv2.quest_answer_val_id in
(select qsv.quest_answer_val_id
from hr_quest_answer_values qsv
,hr_quest_answers qsa
,per_appraisals apr
where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
and qsa.type_object_id = apr.appraisal_id
and qsa.type='APPRAISAL'
and (apr.appraisee_person_id = P_PERSON_ID
or apr.appraiser_person_id = P_PERSON_ID));
end; -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
end; -- Delete from HR_QUEST_ANSWER_VALUES
/* -- Delete from HR_QUEST_ANSWER_VALUES
delete from hr_quest_answer_values qsv2
where qsv2.quest_answer_val_id in
(select qsv.quest_answer_val_id
from hr_quest_answer_values qsv
,hr_quest_answers qsa
,per_appraisals apr
,per_participants par
where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
and (qsa.type_object_id = apr.appraisal_id
and qsa.type='APPRAISAL'
and (apr.appraisee_person_id = P_PERSON_ID
or apr.appraiser_person_id = P_PERSON_ID))
or (qsa.type_object_id = par.participant_id
and qsa.type='PARTICIPANT'
and par.person_id = P_PERSON_ID)
); -- Fix 3619599
delete from hr_quest_answers qsa2
where qsa2.questionnaire_answer_id in
(
select qsa.questionnaire_answer_id
from hr_quest_answers qsa
,per_appraisals apr
where (qsa.type_object_id = apr.appraisal_id
and qsa.type='APPRAISAL'
and (apr.appraiser_person_id = p_person_id
or apr.appraisee_person_id = p_person_id))
Union All
select qsa.questionnaire_answer_id
from hr_quest_answers qsa
,per_participants par
where (qsa.type_object_id = par.participant_id
and qsa.type='PARTICIPANT'
and par.person_id = p_person_id )
) ;
delete from per_participants par2
where par2.participant_id in
( select par.participant_id
from per_participants par
where par.person_id = P_PERSON_Id
union all
select par.participant_id
from per_participants par
,per_appraisals apr
where
(par.participation_in_column = 'APPRAISAL_ID'
and par.participation_in_table = 'PER_APPRAISALS'
and participation_in_id = apr.appraisal_id
and (apr.appraisee_person_id = P_PERSON_ID
or apr.appraiser_person_id = p_person_id)
)
);
delete from per_appraisals apr
where -- apr.appraiser_person_id = P_PERSON_ID or
-- changed as part of bug#8865114
apr.appraisee_person_id = P_PERSON_ID;
delete from per_competence_elements
where person_id = p_person_id;
hr_security.delete_per_from_list(P_PERSON_ID);
update per_vacancies v
set v.recruiter_id = null
where v.recruiter_id = P_PERSON_ID;
update per_assignments_f ass
set ass.person_referred_by_id = null
where ass.person_referred_by_id = P_PERSON_ID;
update per_assignments_f a
set a.recruiter_id = null
where a.recruiter_id = P_PERSON_ID;
update per_assignments_f a
set a.supervisor_id = null
where a.supervisor_id = P_PERSON_ID;
OPEN csr_delete_components;
FETCH csr_delete_components INTO l_proposal_id;
EXIT WHEN csr_delete_components%NOTFOUND;
DELETE FROM per_pay_proposal_components
WHERE pay_proposal_id = l_proposal_id;
CLOSE csr_delete_components;
delete from per_pay_proposals p
where exists (
select null
from per_assignments_f ass
where ass.assignment_id = p.assignment_id
and ass.person_id = P_PERSON_ID);
delete from pay_personal_payment_methods_f m
where m.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from per_assignment_budget_values_f a
where a.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from per_assignment_extra_info a
where a.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from per_secondary_ass_statuses a
where a.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from per_cobra_coverage_benefits c2
where c2.cobra_coverage_enrollment_id in
(select c.cobra_coverage_enrollment_id
from per_cobra_cov_enrollments c
where exists
(select null
from per_assignments_f ass
where ass.assignment_id = c.assignment_id
and ass.person_id = P_PERSON_ID)
);
delete from per_cobra_coverage_benefits c2
where c2.cobra_coverage_enrollment_id in
(select c.cobra_coverage_enrollment_id
from per_cobra_cov_enrollments c
,per_contact_relationships r
where r.contact_person_id = P_PERSON_ID
and c.contact_relationship_id = r.contact_relationship_id
and exists
(select null
from per_assignments_f ass
where ass.assignment_id = c.assignment_id
and ass.person_id = r.person_id)
);
delete from per_cobra_coverage_statuses c2
where c2.cobra_coverage_enrollment_id in
(select c.cobra_coverage_enrollment_id
from per_cobra_cov_enrollments c
where exists
(select null
from per_assignments_f ass
where ass.assignment_id = c.assignment_id
and ass.person_id = P_PERSON_ID)
);
delete from per_cobra_coverage_statuses c2
where c2.cobra_coverage_enrollment_id in
(select c.cobra_coverage_enrollment_id
from per_cobra_cov_enrollments c
,per_contact_relationships r
where r.contact_person_id = P_PERSON_ID
and c.contact_relationship_id = r.contact_relationship_id
and exists
(select null
from per_assignments_f ass
where ass.assignment_id = c.assignment_id
and ass.person_id = r.person_id)
);
delete from per_sched_cobra_payments c2
where c2.cobra_coverage_enrollment_id in
(select c.cobra_coverage_enrollment_id
from per_cobra_cov_enrollments c
where exists
(select null
from per_assignments_f ass
where ass.assignment_id = c.assignment_id
and ass.person_id = P_PERSON_ID)
);
delete from per_sched_cobra_payments c2
where c2.cobra_coverage_enrollment_id in
(select c.cobra_coverage_enrollment_id
from per_cobra_cov_enrollments c
,per_contact_relationships r
where r.contact_person_id = P_PERSON_ID
and c.contact_relationship_id = r.contact_relationship_id
and exists
(select null
from per_assignments_f ass
where ass.assignment_id = c.assignment_id
and ass.person_id = r.person_id)
);
delete from per_cobra_cov_enrollments c
where c.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from per_cobra_cov_enrollments c
where exists
(select null
from per_contact_relationships r
where r.contact_person_id = P_PERSON_ID
and c.contact_relationship_id = r.contact_relationship_id
and exists
(select null
from per_assignments_f ass
where ass.assignment_id = c.assignment_id
and ass.person_id = r.person_id)
);
delete from ben_covered_dependents_f c
where c.contact_relationship_id in
(select r.contact_relationship_id
from per_contact_relationships r
where r.contact_person_id = p_person_id
);
select count(*)
into l_dummy
from per_contact_relationships r
where r.person_id = P_PERSON_ID;
delete from per_contact_relationships r
where (r.person_id = P_PERSON_ID
and r.contact_person_id = EACH_CONTACT.CONTACT_PERSON_ID)
or (r.person_id = EACH_CONTACT.CONTACT_PERSON_ID
and r.contact_person_id = P_PERSON_ID);
delete from per_contact_relationships r
where r.contact_person_id = P_PERSON_ID;
delete from per_addresses a
where a.person_id = P_PERSON_ID;
delete from per_phones a
where a.parent_id = P_PERSON_ID
and a.parent_table = 'PER_ALL_PEOPLE_F';
dbms_sql.parse(l_review_cursor,'DELETE from PER_PERFORMANCE_REVIEWS
where person_id=:x',dbms_sql.v7);
delete from per_bookings b
where b.event_id in
(select e.event_id
from per_events e
where e.assignment_id = l_assignment_id(i));
/*delete from per_bookings b
where b.event_id in
(select e.event_id
from per_events e
where exists (
select null
from per_assignments_f ass
where ass.assignment_id = e.assignment_id
and ass.person_id = P_PERSON_ID)
);*/
delete from per_events e
where e.assignment_id = l_assignment_id(i);
/* delete from per_events e
where e.assignment_id in (
select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);*/
update per_events e
set e.internal_contact_person_id = null
where e.internal_contact_person_id = P_PERSON_ID;
delete from per_bookings b
where b.person_id = P_PERSON_ID;
delete from per_quickpaint_result_text q
where q.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from hr_assignment_set_amendments h
where h.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from pay_cost_allocations_f a
where a.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from per_spinal_point_placements_f p
where p.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from pay_assignment_actions a
where a.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from pay_assignment_latest_balances b
where b.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
delete from pay_assignment_link_usages_f u
where u.assignment_id = l_assignment_id(i);
/*delete from pay_assignment_link_usages_f u
where
u.assignment_id in (
select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID); */
delete from pay_element_entry_values_f v
where v.element_entry_id in
(select e.element_entry_id
from pay_element_entries_f e
where exists
(select null
from per_assignments_f ass
where ass.assignment_id = e.assignment_id
and ass.person_id = P_PERSON_ID)
);
delete from pay_run_results r
where r.source_type = 'E'
and r.source_id in
(select e.element_entry_id
from pay_element_entries_f e
where exists
(select null
from per_assignments_f ass
where ass.assignment_id = e.assignment_id
and ass.person_id = P_PERSON_ID)
);
delete from pay_element_entries_f e
where e.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = P_PERSON_ID);
Delete pay_us_emp_fed_tax_rules_f peft
Where peft.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = p_person_id );
Delete pay_us_emp_state_tax_rules_f pest
Where pest.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = p_person_id );
Delete pay_us_emp_county_tax_rules_f pect
Where pect.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = p_person_id );
Delete pay_us_emp_city_tax_rules_f pecit
Where pecit.assignment_id in
(select ass.assignment_id
from per_assignments_f ass
where ass.person_id = p_person_id );
delete from per_all_assignments_f a
where a.person_id = P_PERSON_ID;
delete from per_periods_of_service p
where p.person_id = P_PERSON_ID;
delete from per_applications a
where a.person_id = P_PERSON_ID;
delete from per_people_extra_info e
where e.person_id = P_PERSON_ID;
select min(ptu1.effective_start_date)
into l_effective_date
from per_person_type_usages_f ptu1
where ptu1.person_type_usage_id = ptu_rec.person_type_usage_id;
select ptu2.object_version_number
into l_object_version_number
from per_person_type_usages_f ptu2
where ptu2.person_type_usage_id = ptu_rec.person_type_usage_id
and ptu2.effective_start_date = l_effective_date;
hr_per_type_usage_internal.delete_person_type_usage
(p_person_type_usage_id => ptu_rec.person_type_usage_id
,p_effective_date => l_effective_date
,p_datetrack_mode => 'ZAP'
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
delete from per_person_dlvry_methods
where person_id = P_PERSON_ID;
delete from per_checklist_items
where person_id = P_PERSON_ID;
select distinct party_id into l_party_id
from per_all_people_f
where person_id = p_person_id;
select count(distinct person_id) into l_count
from per_all_people_f
where party_id = l_party_id;
delete from per_subjects_taken_tl st
where st.subjects_taken_id IN
(select s.subjects_taken_id
from per_subjects_taken s
,per_qualifications q
where q.party_id = l_party_id
and s.qualification_id = q.qualification_id
);
delete from per_subjects_taken s
where s.qualification_id in
(select qualification_id
from per_qualifications
where party_id = l_party_id );
delete from per_qualifications_tl qt
where qt.qualification_id in
(select q.qualification_id
from per_qualifications q
where q.party_id = l_party_id);
delete from per_qualifications q
where q.party_id = l_party_id;
/* delete from per_subjects_taken_tl st
where st.subjects_taken_id IN
(select s.subjects_taken_id
from per_subjects_taken s
,per_qualifications q
where q.person_id = P_PERSON_ID
and s.qualification_id = q.qualification_id
);
delete from per_subjects_taken s
where s.qualification_id in
(select qualification_id
from per_qualifications
where person_id = P_PERSON_ID );
delete from per_qualifications_tl qt
where qt.qualification_id in
(select q.qualification_id
from per_qualifications q
where q.person_id = P_PERSON_ID);
delete from per_qualifications q
where q.person_id = P_PERSON_ID;
ben_person_delete.delete_ben_rows(P_PERSON_ID);
delete from per_all_people_f
where person_id = P_PERSON_ID;
per_medical_assessment_api.delete_medical_assessment
(FALSE
,mea_rec.medical_assessment_id
,mea_rec.object_version_number);
per_disability_api.delete_disability(false,p_effective_date ,'ZAP',l_disability_id, l_object_version_no, l_effective_start_date, l_effective_end_date);
per_work_incident_api.delete_work_incident(false,l_incident_id, l_object_version_number);
per_supplementary_role_api.delete_supplementary_role(false, l_role_id, l_ovn_roles);
delete from per_periods_of_placement p
where p.person_id = P_PERSON_ID;
FND_ATTACHED_DOCUMENTS3_PKG.delete_row (l_attached_document_id,
l_datatype_id,
'Y' );
DELETE
FROM per_objectives
WHERE scorecard_id IN (SELECT scorecard_id
FROM per_personal_scorecards
WHERE person_id = P_PERSON_ID)
OR appraisal_id in (SELECT appraisal_id
FROM per_appraisals
WHERE appraisee_person_id = p_person_id) ;
DELETE
FROM per_scorecard_sharing
WHERE scorecard_id IN (SELECT scorecard_id
FROM per_personal_scorecards
WHERE person_id = P_PERSON_ID);
DELETE
FROM hr_api_transaction_steps
WHERE transaction_id IN (SELECT a.transaction_id
FROM hr_api_transactions a,
per_personal_scorecards sc
WHERE a.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
AND a.transaction_ref_id = sc.scorecard_id
AND sc.person_id = P_PERSON_ID);
DELETE
FROM hr_api_transactions
WHERE transaction_id IN (SELECT transaction_id
FROM hr_api_transactions a,
per_personal_scorecards sc
WHERE a.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
AND a.transaction_ref_id = sc.scorecard_id
AND sc.person_id = P_PERSON_ID);
DELETE
FROM per_personal_scorecards
WHERE person_id = P_PERSON_ID;
hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1111);
DELETE
FROM per_competence_elements
WHERE assessment_id IN (SELECT assessment_id
FROM per_assessments
WHERE appraisal_id IN (SELECT appraisal_id
FROM per_appraisals
WHERE appraisee_person_id = P_PERSON_ID));
DELETE
FROM per_performance_ratings
WHERE appraisal_id IN (SELECT appraisal_id
FROM per_appraisals
WHERE appraisee_person_id = P_PERSON_ID);
DELETE
FROM per_assessments
WHERE appraisal_id IN (SELECT appraisal_id
FROM per_appraisals
WHERE appraisee_person_id = P_PERSON_ID);
hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1112);
delete from per_appraisals apr
where -- apr.appraiser_person_id = P_PERSON_ID or
-- changed as part of bug#8865114
apr.appraisee_person_id = P_PERSON_ID;
END delete_person;