The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
rank.pei_information1,
fnd_date.canonical_to_date(rank.pei_information2) pei_information2,
fnd_date.canonical_to_date(rank.pei_information3) pei_information3,
rank.pei_information4 pei_information4,
fnd_date.canonical_to_date(rank.pei_information5) pei_information5,
to_number(rank.person_extra_info_id ) person_extra_info_id,
to_number(rank.object_version_number) object_version_number
from (
Select
max(pei_information1) pei_information1,
max(pei_information2) pei_information2,
max(pei_information3) pei_information3,
max(pei_information4) pei_information4,
max(pei_information5) pei_information5,
max(person_extra_info_id) person_extra_info_id,
max(object_version_number) object_version_number
from (
SELECT
decode(a.name, 'P_ACADEMIC_RANK' , a.varchar2_value,null) pei_information1,
decode(a.name, 'P_EFFECTIVE_START_DATE' , a.varchar2_value,null) pei_information2,
decode(a.name, 'P_EFFECTIVE_END_DATE' , a.varchar2_value ,null) pei_information3,
decode(a.name, 'P_NEXT_ACADEMIC_RANK' , a.varchar2_value ,null) pei_information4,
decode(a.name, 'P_PROJECTED_DATE' , a.varchar2_value ,null) pei_information5,
decode(a.name, 'P_PERSON_EXTRA_INFO_ID' , a.varchar2_value ,null) person_extra_info_id,
-- Bug# 5415237
-- Replaced a.varchar2_value with a.number_value.
decode(a.name, 'P_OBJECT_VERSION_NUMBER', a.number_value ,null) object_version_number
FROM hr_api_transaction_steps s,
hr_api_transaction_values a
WHERE s.transaction_step_id = a.transaction_step_id
and s.transaction_step_id = p_transaction_step_id
AND s.api_name = 'PQH_PROCESS_ACADEMIC_RANK.PROCESS_API'
)
) rank ;
HR_PERSON_EXTRA_INFO_API.update_person_extra_info (
p_person_extra_info_id => l_person_extra_info_id,
p_pei_information_category => 'PQH_ACADEMIC_RANK',
p_pei_information1 => l_pei_information1 ,
p_pei_information2 => l_pei_information2 ,
p_pei_information3 => l_pei_information3 ,
p_pei_information4 => l_pei_information4 ,
p_pei_information5 => l_pei_information5 ,
p_object_version_number => l_ovn );