The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select petf.element_name,
pee.eei_information8 scheme_type
From pay_element_classifications pec,
pay_element_types_f petf,
pay_element_type_extra_info pee
Where pec.classification_name ='Pre Tax Deductions'
and pec.legislation_code='GB'
and petf.classification_id = pec.classification_id
and sysdate between
petf.effective_start_date and petf.effective_end_date
and petf.business_group_id =p_business_group_id
and pee.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
and pee.eei_information_category= 'PQP_GB_PENSION_SCHEME_INFO'
and pee.eei_information4 = 'AVC'
and petf.element_type_id = pee.element_type_id
and pee.eei_information12 is null;
Procedure update_scheme_type is
--update element eit eei_information8 with
--pension scheme type picked from lookup.
Cursor csr_element_details is
select petf.element_type_id,petf.element_name,
upper(hr.description) description
from hr_lookups hr,
pay_element_types_f petf,
pay_element_classifications pec
where hr.lookup_type='PQP_GB_PENSION_SCHEME_UPDATE'
and hr.enabled_flag='Y'
and (upper(hr.description) in ('COMP','COSR')
or hr.description is null)
and petf.element_name = hr.meaning
and sysdate between --to restrict rows to 1
petf.effective_start_date and petf.effective_end_date
and petf.business_group_id=p_business_group_id
and petf.classification_id = pec.classification_id
and pec.classification_name='Pre Tax Deductions'
and pec.legislation_code='GB';
Begin --update_scheme_type
fnd_file.put_line(fnd_file.output,
'List of Elements Updated with Pension Scheme Types: ');
update pay_element_type_extra_info pee
set pee.eei_information8 = element_details_tab(i).description
where pee.element_type_id = element_details_tab(i).element_type_id
and pee.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
and pee.eei_information_category= 'PQP_GB_PENSION_SCHEME_INFO'
and pee.eei_information4 = 'AVC'
and pee.eei_information12 is null;
End Update_scheme_type;
select name
into l_bg_name
from per_business_groups_perf
where business_group_id = p_business_group_id;
elsif p_execution_mode ='UPDATE'
then
update_scheme_type;
END PQP_GB_PENSION_SCHEME_UPDATE;