The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure dt_delete_dml
(p_rec in out nocopy per_per_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'dt_delete_dml';
If (p_datetrack_mode = 'DELETE_NEXT_CHANGE') then
hr_utility.set_location(l_proc, 10);
delete from per_all_people_f
where person_id = p_rec.person_id
and effective_start_date = p_validation_start_date;
delete from per_all_people_f
where person_id = p_rec.person_id
and effective_start_date >= p_validation_start_date;
End dt_delete_dml;
Procedure delete_dml
(p_rec in out nocopy per_per_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'delete_dml';
dt_delete_dml(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
End delete_dml;
Procedure dt_pre_delete
(p_rec in out nocopy per_per_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'dt_pre_delete';
If (p_datetrack_mode = 'DELETE') then
p_rec.effective_end_date := p_validation_start_date - 1;
End dt_pre_delete;
Procedure pre_delete
(p_rec in out nocopy per_per_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'pre_delete';
select *
from per_all_people_f
where person_id = p_rec.person_id
and effective_start_date = p_rec.effective_start_date
and effective_end_date = p_rec.effective_end_date;
select *
from per_all_people_f
where person_id = p_rec.person_id;
select t1.comment_id
from per_all_people_f t1
where t1.comment_id is not null
and t1.person_id = p_rec.person_id
and t1.effective_start_date <= p_validation_end_date
and t1.effective_end_date >= p_validation_start_date
and not exists
(select 1
from per_all_people_f t2
where t2.comment_id = t1.comment_id
and t2.person_id = t1.person_id
and (t2.effective_start_date > p_validation_end_date
or t2.effective_end_date < p_validation_start_date));
dt_pre_delete
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
p_action => 'DELETE');
End pre_delete;
Procedure post_delete
(p_rec in per_per_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'post_delete';
per_per_rkd.after_delete
(p_effective_date => p_effective_date
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
,p_datetrack_mode => p_datetrack_mode
,p_person_id => p_rec.person_id
,p_effective_start_date => p_rec.effective_start_date
,p_effective_end_date => p_rec.effective_end_date
,p_object_version_number => p_rec.object_version_number
,p_effective_start_date_o
=> per_per_shd.g_old_rec.effective_start_date
,p_effective_end_date_o
=> per_per_shd.g_old_rec.effective_end_date
,p_business_group_id_o
=> per_per_shd.g_old_rec.business_group_id
,p_person_type_id_o
=> per_per_shd.g_old_rec.person_type_id
,p_last_name_o
=> per_per_shd.g_old_rec.last_name
,p_start_date_o
=> per_per_shd.g_old_rec.start_date
,p_applicant_number_o
=> per_per_shd.g_old_rec.applicant_number
,p_comment_id_o
=> per_per_shd.g_old_rec.comment_id
,p_current_applicant_flag_o
=> per_per_shd.g_old_rec.current_applicant_flag
,p_current_emp_or_apl_flag_o
=> per_per_shd.g_old_rec.current_emp_or_apl_flag
,p_current_employee_flag_o
=> per_per_shd.g_old_rec.current_employee_flag
,p_date_employee_data_verifie_o
=> per_per_shd.g_old_rec.date_employee_data_verified
,p_date_of_birth_o
=> per_per_shd.g_old_rec.date_of_birth
,p_email_address_o
=> per_per_shd.g_old_rec.email_address
,p_employee_number_o
=> per_per_shd.g_old_rec.employee_number
,p_expense_check_send_to_addr_o
=> per_per_shd.g_old_rec.expense_check_send_to_address
,p_first_name_o
=> per_per_shd.g_old_rec.first_name
,p_full_name_o
=> per_per_shd.g_old_rec.full_name
,p_known_as_o
=> per_per_shd.g_old_rec.known_as
,p_marital_status_o
=> per_per_shd.g_old_rec.marital_status
,p_middle_names_o
=> per_per_shd.g_old_rec.middle_names
,p_nationality_o
=> per_per_shd.g_old_rec.nationality
,p_national_identifier_o
=> per_per_shd.g_old_rec.national_identifier
,p_previous_last_name_o
=> per_per_shd.g_old_rec.previous_last_name
,p_registered_disabled_flag_o
=> per_per_shd.g_old_rec.registered_disabled_flag
,p_sex_o
=> per_per_shd.g_old_rec.sex
,p_title_o
=> per_per_shd.g_old_rec.title
,p_vendor_id_o
=> per_per_shd.g_old_rec.vendor_id
,p_work_telephone_o
=> per_per_shd.g_old_rec.work_telephone
,p_request_id_o
=> per_per_shd.g_old_rec.request_id
,p_program_application_id_o
=> per_per_shd.g_old_rec.program_application_id
,p_program_id_o
=> per_per_shd.g_old_rec.program_id
,p_program_update_date_o
=> per_per_shd.g_old_rec.program_update_date
,p_attribute_category_o
=> per_per_shd.g_old_rec.attribute_category
,p_attribute1_o
=> per_per_shd.g_old_rec.attribute1
,p_attribute2_o
=> per_per_shd.g_old_rec.attribute2
,p_attribute3_o
=> per_per_shd.g_old_rec.attribute3
,p_attribute4_o
=> per_per_shd.g_old_rec.attribute4
,p_attribute5_o
=> per_per_shd.g_old_rec.attribute5
,p_attribute6_o
=> per_per_shd.g_old_rec.attribute6
,p_attribute7_o
=> per_per_shd.g_old_rec.attribute7
,p_attribute8_o
=> per_per_shd.g_old_rec.attribute8
,p_attribute9_o
=> per_per_shd.g_old_rec.attribute9
,p_attribute10_o
=> per_per_shd.g_old_rec.attribute10
,p_attribute11_o
=> per_per_shd.g_old_rec.attribute11
,p_attribute12_o
=> per_per_shd.g_old_rec.attribute12
,p_attribute13_o
=> per_per_shd.g_old_rec.attribute13
,p_attribute14_o
=> per_per_shd.g_old_rec.attribute14
,p_attribute15_o
=> per_per_shd.g_old_rec.attribute15
,p_attribute16_o
=> per_per_shd.g_old_rec.attribute16
,p_attribute17_o
=> per_per_shd.g_old_rec.attribute17
,p_attribute18_o
=> per_per_shd.g_old_rec.attribute18
,p_attribute19_o
=> per_per_shd.g_old_rec.attribute19
,p_attribute20_o
=> per_per_shd.g_old_rec.attribute20
,p_attribute21_o
=> per_per_shd.g_old_rec.attribute21
,p_attribute22_o
=> per_per_shd.g_old_rec.attribute22
,p_attribute23_o
=> per_per_shd.g_old_rec.attribute23
,p_attribute24_o
=> per_per_shd.g_old_rec.attribute24
,p_attribute25_o
=> per_per_shd.g_old_rec.attribute25
,p_attribute26_o
=> per_per_shd.g_old_rec.attribute26
,p_attribute27_o
=> per_per_shd.g_old_rec.attribute27
,p_attribute28_o
=> per_per_shd.g_old_rec.attribute28
,p_attribute29_o
=> per_per_shd.g_old_rec.attribute29
,p_attribute30_o
=> per_per_shd.g_old_rec.attribute30
,p_per_information_category_o
=> per_per_shd.g_old_rec.per_information_category
,p_per_information1_o
=> per_per_shd.g_old_rec.per_information1
,p_per_information2_o
=> per_per_shd.g_old_rec.per_information2
,p_per_information3_o
=> per_per_shd.g_old_rec.per_information3
,p_per_information4_o
=> per_per_shd.g_old_rec.per_information4
,p_per_information5_o
=> per_per_shd.g_old_rec.per_information5
,p_per_information6_o
=> per_per_shd.g_old_rec.per_information6
,p_per_information7_o
=> per_per_shd.g_old_rec.per_information7
,p_per_information8_o
=> per_per_shd.g_old_rec.per_information8
,p_per_information9_o
=> per_per_shd.g_old_rec.per_information9
,p_per_information10_o
=> per_per_shd.g_old_rec.per_information10
,p_per_information11_o
=> per_per_shd.g_old_rec.per_information11
,p_per_information12_o
=> per_per_shd.g_old_rec.per_information12
,p_per_information13_o
=> per_per_shd.g_old_rec.per_information13
,p_per_information14_o
=> per_per_shd.g_old_rec.per_information14
,p_per_information15_o
=> per_per_shd.g_old_rec.per_information15
,p_per_information16_o
=> per_per_shd.g_old_rec.per_information16
,p_per_information17_o
=> per_per_shd.g_old_rec.per_information17
,p_per_information18_o
=> per_per_shd.g_old_rec.per_information18
,p_per_information19_o
=> per_per_shd.g_old_rec.per_information19
,p_per_information20_o
=> per_per_shd.g_old_rec.per_information20
,p_suffix_o
=> per_per_shd.g_old_rec.suffix
,p_DATE_OF_DEATH_o
=> per_per_shd.g_old_rec.DATE_OF_DEATH
,p_BACKGROUND_CHECK_STATUS_o
=> per_per_shd.g_old_rec.BACKGROUND_CHECK_STATUS
,p_BACKGROUND_DATE_CHECK_o
=> per_per_shd.g_old_rec.BACKGROUND_DATE_CHECK
,p_BLOOD_TYPE_o
=> per_per_shd.g_old_rec.BLOOD_TYPE
,p_CORRESPONDENCE_LANGUAGE_o
=> per_per_shd.g_old_rec.CORRESPONDENCE_LANGUAGE
,p_FAST_PATH_EMPLOYEE_o
=> per_per_shd.g_old_rec.FAST_PATH_EMPLOYEE
,p_FTE_CAPACITY_o
=> per_per_shd.g_old_rec.FTE_CAPACITY
,p_HOLD_APPLICANT_DATE_UNTIL_o
=> per_per_shd.g_old_rec.HOLD_APPLICANT_DATE_UNTIL
,p_HONORS_o
=> per_per_shd.g_old_rec.HONORS
,p_INTERNAL_LOCATION_o
=> per_per_shd.g_old_rec.INTERNAL_LOCATION
,p_LAST_MEDICAL_TEST_BY_o
=> per_per_shd.g_old_rec.LAST_MEDICAL_TEST_BY
,p_LAST_MEDICAL_TEST_DATE_o
=> per_per_shd.g_old_rec.LAST_MEDICAL_TEST_DATE
,p_MAILSTOP_o
=> per_per_shd.g_old_rec.MAILSTOP
,p_OFFICE_NUMBER_o
=> per_per_shd.g_old_rec.OFFICE_NUMBER
,p_ON_MILITARY_SERVICE_o
=> per_per_shd.g_old_rec.ON_MILITARY_SERVICE
,p_ORDER_NAME_o
=> per_per_shd.g_old_rec.ORDER_NAME
,p_PRE_NAME_ADJUNCT_o
=> per_per_shd.g_old_rec.PRE_NAME_ADJUNCT
,p_PROJECTED_START_DATE_o
=> per_per_shd.g_old_rec.PROJECTED_START_DATE
,p_REHIRE_AUTHORIZOR_o
=> per_per_shd.g_old_rec.REHIRE_AUTHORIZOR
,p_REHIRE_RECOMMENDATION_o
=> per_per_shd.g_old_rec.REHIRE_RECOMMENDATION
,p_RESUME_EXISTS_o
=> per_per_shd.g_old_rec.RESUME_EXISTS
,p_RESUME_LAST_UPDATED_o
=> per_per_shd.g_old_rec.RESUME_LAST_UPDATED
,p_SECOND_PASSPORT_EXISTS_o
=> per_per_shd.g_old_rec.SECOND_PASSPORT_EXISTS
,p_STUDENT_STATUS_o
=> per_per_shd.g_old_rec.STUDENT_STATUS
,p_WORK_SCHEDULE_o
=> per_per_shd.g_old_rec.WORK_SCHEDULE
,p_PER_INFORMATION21_o
=> per_per_shd.g_old_rec.PER_INFORMATION21
,p_PER_INFORMATION22_o
=> per_per_shd.g_old_rec.PER_INFORMATION22
,p_PER_INFORMATION23_o
=> per_per_shd.g_old_rec.PER_INFORMATION23
,p_PER_INFORMATION24_o
=> per_per_shd.g_old_rec.PER_INFORMATION24
,p_PER_INFORMATION25_o
=> per_per_shd.g_old_rec.PER_INFORMATION25
,p_PER_INFORMATION26_o
=> per_per_shd.g_old_rec.PER_INFORMATION26
,p_PER_INFORMATION27_o
=> per_per_shd.g_old_rec.PER_INFORMATION27
,p_PER_INFORMATION28_o
=> per_per_shd.g_old_rec.PER_INFORMATION28
,p_PER_INFORMATION29_o
=> per_per_shd.g_old_rec.PER_INFORMATION29
,p_PER_INFORMATION30_o
=> per_per_shd.g_old_rec.PER_INFORMATION30
,p_REHIRE_REASON_o
=> per_per_shd.g_old_rec.REHIRE_REASON
,p_BENEFIT_GROUP_ID_o
=> per_per_shd.g_old_rec.BENEFIT_GROUP_ID
,p_RECEIPT_OF_DEATH_CERT_DATE_o
=> per_per_shd.g_old_rec.RECEIPT_OF_DEATH_CERT_DATE
,p_COORD_BEN_MED_PLN_NO_o
=> per_per_shd.g_old_rec.COORD_BEN_MED_PLN_NO
,p_COORD_BEN_NO_CVG_FLAG_o
=> per_per_shd.g_old_rec.COORD_BEN_NO_CVG_FLAG
,p_coord_ben_med_ext_er_o
=> per_per_shd.g_old_rec.coord_ben_med_ext_er
,p_coord_ben_med_pl_name_o
=> per_per_shd.g_old_rec.coord_ben_med_pl_name
,p_coord_ben_med_insr_crr_nam_o
=> per_per_shd.g_old_rec.coord_ben_med_insr_crr_name
,p_coord_ben_med_insr_crr_ide_o
=> per_per_shd.g_old_rec.coord_ben_med_insr_crr_ident
,p_coord_ben_med_cvg_strt_dt_o
=> per_per_shd.g_old_rec.coord_ben_med_cvg_strt_dt
,p_coord_ben_med_cvg_end_dt_o
=> per_per_shd.g_old_rec.coord_ben_med_cvg_end_dt
,p_USES_TOBACCO_FLAG_o
=> per_per_shd.g_old_rec.USES_TOBACCO_FLAG
,p_DPDNT_ADOPTION_DATE_o
=> per_per_shd.g_old_rec.DPDNT_ADOPTION_DATE
,p_DPDNT_VLNTRY_SVCE_FLAG_o
=> per_per_shd.g_old_rec.DPDNT_VLNTRY_SVCE_FLAG
,p_ORIGINAL_DATE_OF_HIRE_o
=> per_per_shd.g_old_rec.ORIGINAL_DATE_OF_HIRE
,p_town_of_birth_o
=> per_per_shd.g_old_rec.town_of_birth
,p_region_of_birth_o
=> per_per_shd.g_old_rec.region_of_birth
,p_country_of_birth_o
=> per_per_shd.g_old_rec.country_of_birth
,p_global_person_id_o
=> per_per_shd.g_old_rec.global_person_id
,p_party_id_o
=> per_per_shd.g_old_rec.party_id
,p_npw_number_o
=> per_per_shd.g_old_rec.npw_number
,p_current_npw_flag_o
=> per_per_shd.g_old_rec.current_npw_flag
,p_global_name_o
=> per_per_shd.g_old_rec.global_name
,p_local_name_o
=> per_per_shd.g_old_rec.local_name
,p_object_version_number_o
=> per_per_shd.g_old_rec.object_version_number
);
End post_delete;
per_per_bus.delete_validate
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date);
pre_delete
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date);
delete_dml
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date);
post_delete
(p_rec => p_rec,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => l_validation_start_date,
p_validation_end_date => l_validation_end_date);