The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aei.assignment_extra_info_id
, aei.aei_information1
, aei.aei_information2
, aei.aei_information3
FROM per_assignment_extra_info aei
, per_all_assignments_f paf
WHERE aei.assignment_id = p_asg_id
AND aei.information_type = 'PAYROLL_CONTACT'
AND aei.aei_information_category = 'PAYROLL_CONTACT'
AND paf.assignment_id = aei.assignment_id
AND paf.business_group_id = p_bg_id
AND p_eff_date between paf.effective_start_date
and paf.effective_end_date;
hr_utility.trace('row not found so insert');
insert into per_assignment_extra_info
(ASSIGNMENT_EXTRA_INFO_ID
,ASSIGNMENT_ID
,INFORMATION_TYPE
,AEI_INFORMATION_CATEGORY
,AEI_INFORMATION1
,AEI_INFORMATION2
,AEI_INFORMATION3)
select per_assignment_extra_info_s.nextval
, p_assignment_id
, 'PAYROLL_CONTACT'
, 'PAYROLL_CONTACT'
, p_contact_name
, p_phone
, p_email
from dual
where not exists (select 1
from per_all_assignments_f paf
, per_assignment_extra_info aei
where paf.assignment_id = aei.assignment_id
and paf.business_group_id = p_business_group_id
and paf.assignment_id = p_assignment_id
and aei.aei_information_category = 'PAYROLL_CONTACT');
select per_assignment_extra_info_s.currval
into l_extra_info_id
from dual;
hr_utility.trace('row found so update');
update per_assignment_extra_info
set aei_information1 = p_contact_name
, aei_information2 = p_phone
, aei_information3 = p_email
where assignment_extra_info_id = l_extra_info_id
and information_type = 'PAYROLL_CONTACT'
and aei_information_category = 'PAYROLL_CONTACT';