The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure chk_non_updateable_args(p_rec in per_apt_shd.g_rec_type) is
l_proc varchar2(72) := g_package||'chk_non_updateable_args';
end chk_non_updateable_args;
select business_group_id
from per_appraisal_templates apt
where ( (p_appraisal_template_id is null)
or(p_appraisal_template_id <> apt.appraisal_template_id)
)
and apt.name = p_name and
( business_group_id is null or p_business_group_id is null or business_group_id = p_business_group_id);
select 'Y'
from per_appraisals apr
where ( apr.appraisal_period_start_date < nvl(p_date_from,hr_api.g_sot)
or apr.appraisal_period_end_date > nvl(p_date_to,hr_api.g_eot)
)
and apr.appraisal_template_id = p_appraisal_template_id;
select business_group_id
from per_assessment_types
where assessment_type_id = p_assessment_type_id
and type = 'COMPETENCE';
select 'Y'
from per_assessment_types
where assessment_type_id = p_assessment_type_id
and nvl(date_from,hr_api.g_sot) <= nvl(p_date_from,hr_api.g_eot)
and nvl(date_to,hr_api.g_eot) >= nvl(p_date_to,hr_api.g_sot) ;
select business_group_id
from per_assessment_types
where assessment_type_id = p_objective_asmnt_type_id
and type = 'OBJECTIVE';
select 'Y'
from per_assessment_types
where assessment_type_id = p_objective_asmnt_type_id
and nvl(date_from,hr_api.g_sot) <= nvl(p_date_from,hr_api.g_eot)
and nvl(date_to,hr_api.g_eot) >= nvl(p_date_to,hr_api.g_sot) ;
select business_group_id
from hr_questionnaires qsn
where qsn.questionnaire_template_id = p_questionnaire_template_id;
-- can be updated
-- Cursor csr_question_update
-- is
-- select 'Y'
-- from per_appraisals apr
-- ,per_assign_proposal_answers apa
-- where apr.appraisal_id = apa.answer_for_key
-- and apa.type = 'APPRAISAL'
-- and apr.appraisal_template_id = p_appraisal_template_id;
cursor csr_question_update1 is
select 'Y'
from hr_quest_answers hqa
where hqa.type = 'APPRAISAL'
and hqa.type_object_id in (select appraisal_id
from per_appraisals apr
where apr.appraisal_template_id
= p_appraisal_template_id);
cursor csr_question_update2 is
select 'Y'
from per_participants par
where par.participation_in_table = 'PER_APPRAISALS'
and par.participation_in_id in (select appraisal_id
from per_appraisals apr
where apr.appraisal_template_id
= p_appraisal_template_id);
open csr_question_update1;
fetch csr_question_update1 into l_exists;
if csr_question_update1%found then
close csr_question_update1;
close csr_question_update1;
open csr_question_update2;
fetch csr_question_update2 into l_exists;
if csr_question_update2%found then
close csr_question_update2;
close csr_question_update2;
select business_group_id
from hr_questionnaires qsn
where qsn.questionnaire_template_id = p_ma_quest_template_id;
-- can be updated
-- Cursor csr_question_update
-- is
-- select 'Y'
-- from per_appraisals apr
-- ,per_assign_proposal_answers apa
-- where apr.appraisal_id = apa.answer_for_key
-- and apa.type = 'APPRAISAL'
-- and apr.appraisal_template_id = p_appraisal_template_id;
cursor csr_question_update1 is
select 'Y'
from hr_quest_answers hqa
where hqa.type = 'APPRAISAL'
and hqa.type_object_id in (select appraisal_id
from per_appraisals apr
where apr.appraisal_template_id
= p_appraisal_template_id);
cursor csr_question_update2 is
select 'Y'
from per_participants par
where par.participation_in_table = 'PER_APPRAISALS'
and par.participation_in_id in (select appraisal_id
from per_appraisals apr
where apr.appraisal_template_id
= p_appraisal_template_id);
open csr_question_update1;
fetch csr_question_update1 into l_exists;
if csr_question_update1%found then
close csr_question_update1;
close csr_question_update1;
open csr_question_update2;
fetch csr_question_update2 into l_exists;
if csr_question_update2%found then
close csr_question_update2;
close csr_question_update2;
select business_group_id,type
from per_rating_scales
where rating_scale_id = p_rating_scale_id;
Cursor csr_rating_scale_update1
is
select overall_performance_level_id
from per_appraisals
where appraisal_template_id = p_appraisal_template_id;
Cursor csr_rating_scale_update2
is
select 'Y'
from per_appraisals apr
,per_performance_ratings prt
where prt.appraisal_id = apr.appraisal_id
and apr.appraisal_template_id = p_appraisal_template_id;
open csr_rating_scale_update1;
fetch csr_rating_scale_update1 into l_overall_performance_level_id;
close csr_rating_scale_update1;
open csr_rating_scale_update2;
fetch csr_rating_scale_update2 into l_exists;
if csr_rating_scale_update2%found then
close csr_rating_scale_update2;
close csr_rating_scale_update2;
procedure chk_template_delete
(p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
)
is
l_exists varchar2(1);
l_proc varchar2(72) := g_package||'chk_template_delete';
Cursor csr_template_delete
is
select 'Y'
from per_appraisals
where appraisal_template_id = p_appraisal_template_id;
open csr_template_delete;
fetch csr_template_delete into l_exists;
if csr_template_delete%found then
close csr_template_delete;
close csr_template_delete;
end chk_template_delete;
Procedure chk_update_comp_profile
(
p_update_personal_comp_profile in per_appraisal_templates.update_personal_comp_profile%TYPE
,p_assessment_type_id in per_appraisal_templates.assessment_type_id%TYPE
,p_effective_date in date
,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
)
is
--
l_proc varchar2(72):=g_package||'chk_update_comp_profile';
hr_utility.trace('Inside the chk_update_comp_profile procedure');
if ( p_assessment_type_id is null and p_update_personal_comp_profile is not null ) then
hr_utility.set_location(l_proc, 2);
if ((l_api_updating and nvl(per_apt_shd.g_old_rec.update_personal_comp_profile,
hr_api.g_varchar2)
<> nvl(p_update_personal_comp_profile, hr_api.g_varchar2))
or
(NOT l_api_updating)) then
--
hr_utility.set_location(l_proc, 5);
,p_lookup_code => p_update_personal_comp_profile
) then
hr_utility.set_location(l_proc, 10);
end chk_update_comp_profile;
,p_update_personal_comp_profile in per_appraisal_templates.update_personal_comp_profile%TYPE
,p_effective_date in date
,p_object_version_number in per_appraisal_templates.object_version_number%TYPE
,p_appraisal_template_id in per_appraisal_templates.appraisal_template_id%TYPE
)
is
--
l_proc varchar2(72):=g_package||'chk_comp_profile_source_type';
if ( p_comp_profile_source_type is not null and ( p_update_personal_comp_profile is null or p_update_personal_comp_profile = 'N') ) then
hr_utility.set_location(l_proc, 2);
Procedure insert_validate(p_rec in per_apt_shd.g_rec_type
,p_effective_date in date) is
l_proc varchar2(72) := g_package||'insert_validate';
if p_rec.update_personal_comp_profile is not null then
per_apt_bus.chk_update_comp_profile
(
p_update_personal_comp_profile => p_rec.update_personal_comp_profile
,p_assessment_type_id => p_rec.assessment_type_id
,p_effective_date => p_effective_date
,p_object_version_number => p_rec.object_version_number
,p_appraisal_template_id => p_rec.appraisal_template_id
);
,p_update_personal_comp_profile => p_rec.update_personal_comp_profile
,p_effective_date => p_effective_date
,p_object_version_number => p_rec.object_version_number
,p_appraisal_template_id => p_rec.appraisal_template_id
);
End insert_validate;
Procedure update_validate(p_rec in per_apt_shd.g_rec_type
,p_effective_date in date) is
l_proc varchar2(72) := g_package||'update_validate';
chk_non_updateable_args(p_rec => p_rec);
if p_rec.update_personal_comp_profile is not null then
per_apt_bus.chk_update_comp_profile
(
p_update_personal_comp_profile => p_rec.update_personal_comp_profile
,p_assessment_type_id => p_rec.assessment_type_id
,p_effective_date => p_effective_date
,p_object_version_number => p_rec.object_version_number
,p_appraisal_template_id => p_rec.appraisal_template_id
);
,p_update_personal_comp_profile => p_rec.update_personal_comp_profile
,p_effective_date => p_effective_date
,p_object_version_number => p_rec.object_version_number
,p_appraisal_template_id => p_rec.appraisal_template_id
);
End update_validate;
Procedure delete_validate(p_rec in per_apt_shd.g_rec_type) is
l_proc varchar2(72) := g_package||'delete_validate';
per_apt_bus.chk_template_delete
(p_appraisal_template_id => p_rec.appraisal_template_id
,p_object_version_number => p_rec.object_version_number
);
End delete_validate;
select legislation_code
from per_business_groups pbg,
per_appraisal_templates pat
where pat.appraisal_template_id = p_appraisal_template_id
and pbg.business_group_id = pat.business_group_id;