[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_appraisal in varchar2 default null,
p_provide_overall_feedback in varchar2 default null,
p_appraisee_comments in varchar2 default null,
p_plan_id in number default null,
p_offline_status in varchar2 default null,
p_retention_potential in varchar2 default null,
p_show_participant_comments in varchar2 default null -- 8651478 bug fix
)
is
--
-- Declare cursors and local variables
--
--
l_proc varchar2(72) := g_package||'create_appraisal';
p_update_appraisal => p_update_appraisal,
p_provide_overall_feedback => p_provide_overall_feedback,
p_appraisee_comments => p_appraisee_comments,
p_plan_id => p_plan_id,
p_offline_status => p_offline_status,
p_retention_potential => p_retention_potential,
p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
);
p_update_appraisal => p_update_appraisal,
p_provide_overall_feedback => p_provide_overall_feedback,
p_appraisee_comments => p_appraisee_comments,
p_plan_id => p_plan_id,
p_offline_status => p_offline_status,
p_retention_potential => p_retention_potential,
p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
);
p_update_appraisal => p_update_appraisal,
p_provide_overall_feedback => p_provide_overall_feedback,
p_appraisee_comments => p_appraisee_comments,
p_plan_id => p_plan_id,
p_offline_status => p_offline_status,
p_retention_potential => p_retention_potential,
p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
);
procedure update_learning_path
(p_appraisal_id in number,
p_appraisal_system_status in varchar2)
is
l_ota_error_num NUMBER;
dynamic_ota_lpme_query varchar2(3600) := ' select lpm.learning_path_member_id, lpme.lp_member_enrollment_id, ' ||
' lpm.activity_version_id, tav.version_name , lpe.lp_enrollment_id, lptl.name, ' ||
' lpme.object_version_number, lpe.object_version_number ' ||
' from ota_learning_path_members lpm, ota_learning_paths lp,ota_lp_sections lps, ' ||
' ota_lp_enrollments lpe, ota_lp_member_enrollments lpme, ota_activity_versions tav, ' ||
' ota_learning_paths_tl lptl ' ||
' where lp.source_id = :1 ' ||
' and source_function_code = ''APPRAISAL''' ||
' and lptl.learning_path_id = lp.learning_path_id ' ||
' and lptl.language = userenv(''lang'') ' ||
' and lpe.learning_path_id = lp.learning_path_id ' ||
' and lps.learning_path_id = lp.learning_path_id ' ||
' and lpm.learning_path_section_id = lps.learning_path_section_id ' ||
' and lpme.learning_path_member_id = lpm.learning_path_member_id ' ||
' and tav.activity_version_id = lpm.activity_version_id ';
dynamic_ota_lpe_query varchar2(3600) := ' select lp.learning_path_id, lpe.lp_enrollment_id, ' ||
' lpe.object_version_number ' ||
' from ota_learning_paths lp, ota_lp_enrollments lpe ' ||
' where lp.source_id = :1 ' ||
' and lp.source_function_code = ''APPRAISAL''' ||
' and lpe.learning_path_id = lp.learning_path_id ';
dynamic_ota_lpme_upd varchar2(3600) := 'begin ota_lp_member_enrollment_api.update_lp_member_enrollment( ' ||
'p_effective_date => trunc(sysdate) , ' ||
'p_lp_member_enrollment_id => :1 ,' ||
'p_object_version_number => :2 ,' ||
'p_member_status_code => :3); end;';
dynamic_ota_lpe_upd varchar2(3600) := ' BEGIN ota_lp_enrollment_api.update_lp_enrollment( ' ||
'p_effective_date => trunc(sysdate) ' ||
',p_lp_enrollment_id => :1 ' ||
',p_path_status_code => :2 ' ||
',p_object_version_number => :3); END;';
if (p_appraisal_system_status = 'DELETED') then
begin
open dynamic_ota_cursor for dynamic_ota_lpme_query USING p_appraisal_id;
hr_utility.set_location('Exception raised in update_learning_path while executing dynamic sql dynamic_ota_lpme_query' || sqlerrm, 1);
if (p_appraisal_system_status = 'DELETED') then
begin
open dynamic_ota_cursor for dynamic_ota_lpe_query USING p_appraisal_id;
hr_utility.set_location('Exception raised in update_learning_path while executing dynamic sql dynamic_ota_lpe_query' || sqlerrm, 1);
procedure update_appraisal
(p_validate in boolean default false,
p_effective_date in date,
p_appraisal_id in number,
p_object_version_number in out nocopy number,
p_appraiser_person_id in number,
p_appraisal_date in date default hr_api.g_date,
p_appraisal_period_end_date in date default hr_api.g_date,
p_appraisal_period_start_date in date default hr_api.g_date,
p_type in varchar2 default hr_api.g_varchar2,
p_next_appraisal_date in date default hr_api.g_date,
p_status in varchar2 default hr_api.g_varchar2,
p_comments in varchar2 default hr_api.g_varchar2,
p_overall_performance_level_id in number default hr_api.g_number,
p_open in varchar2 default hr_api.g_varchar2,
p_attribute_category in varchar2 default hr_api.g_varchar2,
p_attribute1 in varchar2 default hr_api.g_varchar2,
p_attribute2 in varchar2 default hr_api.g_varchar2,
p_attribute3 in varchar2 default hr_api.g_varchar2,
p_attribute4 in varchar2 default hr_api.g_varchar2,
p_attribute5 in varchar2 default hr_api.g_varchar2,
p_attribute6 in varchar2 default hr_api.g_varchar2,
p_attribute7 in varchar2 default hr_api.g_varchar2,
p_attribute8 in varchar2 default hr_api.g_varchar2,
p_attribute9 in varchar2 default hr_api.g_varchar2,
p_attribute10 in varchar2 default hr_api.g_varchar2,
p_attribute11 in varchar2 default hr_api.g_varchar2,
p_attribute12 in varchar2 default hr_api.g_varchar2,
p_attribute13 in varchar2 default hr_api.g_varchar2,
p_attribute14 in varchar2 default hr_api.g_varchar2,
p_attribute15 in varchar2 default hr_api.g_varchar2,
p_attribute16 in varchar2 default hr_api.g_varchar2,
p_attribute17 in varchar2 default hr_api.g_varchar2,
p_attribute18 in varchar2 default hr_api.g_varchar2,
p_attribute19 in varchar2 default hr_api.g_varchar2,
p_attribute20 in varchar2 default hr_api.g_varchar2,
p_system_type in varchar2 default hr_api.g_varchar2,
p_system_params in varchar2 default hr_api.g_varchar2,
p_appraisee_access in varchar2 default hr_api.g_varchar2,
p_main_appraiser_id in number default hr_api.g_number,
p_assignment_id in number default hr_api.g_number,
p_assignment_start_date in date default hr_api.g_date,
p_asg_business_group_id in number default hr_api.g_number,
p_assignment_organization_id in number default hr_api.g_number,
p_assignment_job_id in number default hr_api.g_number,
p_assignment_position_id in number default hr_api.g_number,
p_assignment_grade_id in number default hr_api.g_number,
p_appraisal_system_status in varchar2 default hr_api.g_varchar2,
p_potential_readiness_level in varchar2 default hr_api.g_varchar2,
p_potential_short_term_workopp in varchar2 default hr_api.g_varchar2,
p_potential_long_term_workopp in varchar2 default hr_api.g_varchar2,
p_potential_details in varchar2 default hr_api.g_varchar2,
p_event_id in number default hr_api.g_number,
p_show_competency_ratings in varchar2 default hr_api.g_varchar2,
p_show_objective_ratings in varchar2 default hr_api.g_varchar2,
p_show_questionnaire_info in varchar2 default hr_api.g_varchar2,
p_show_participant_details in varchar2 default hr_api.g_varchar2,
p_show_participant_ratings in varchar2 default hr_api.g_varchar2,
p_show_participant_names in varchar2 default hr_api.g_varchar2,
p_show_overall_ratings in varchar2 default hr_api.g_varchar2,
p_show_overall_comments in varchar2 default hr_api.g_varchar2,
p_update_appraisal in varchar2 default hr_api.g_varchar2,
p_provide_overall_feedback in varchar2 default hr_api.g_varchar2,
p_appraisee_comments in varchar2 default hr_api.g_varchar2,
p_plan_id in number default hr_api.g_number,
p_offline_status in varchar2 default hr_api.g_varchar2,
p_retention_potential in varchar2 default hr_api.g_varchar2,
p_show_participant_comments in varchar2 default hr_api.g_varchar2 -- 8651478 bug fix
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'update_appraisal';
SELECT appraisee_person_id
FROM per_appraisals
WHERE appraisal_id = p_appraisal_id;
SELECT assessment_id , object_version_number
FROM per_assessments
WHERE appraisal_id = p_appraisal_id;
SELECT asn.assessment_period_start_date, asn.assessment_period_end_date,
asn.assessment_date
FROM per_assessments asn
WHERE asn.appraisal_id = p_appraisal_id;
SELECT par.participant_id, par.object_version_number
FROM per_participants par
WHERE par.participation_in_id = p_appraisal_id
and par.participation_in_table = 'PER_APPRAISALS'
and par.participation_in_column = 'APPRAISAL_ID'
and par.person_id = p_person_id;
SELECT apr.main_appraiser_id
FROM per_appraisals apr
WHERE apr.appraisal_id = p_appraisal_id;
savepoint update_appraisal;
hr_appraisals_bk2.update_appraisal_b (
p_effective_date => p_effective_date,
p_appraisal_id => p_appraisal_id,
p_object_version_number => p_object_version_number,
p_appraiser_person_id => p_appraiser_person_id,
p_appraisal_date => p_appraisal_date,
p_appraisal_period_start_date => p_appraisal_period_start_date,
p_appraisal_period_end_date => p_appraisal_period_end_date,
p_type => p_type,
p_next_appraisal_date => p_next_appraisal_date,
p_status => p_status,
p_comments => p_comments,
p_overall_performance_level_id => p_overall_performance_level_id,
p_open => p_open,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_system_type => p_system_type ,
p_system_params => p_system_params,
p_appraisee_access => p_appraisee_access ,
p_main_appraiser_id => p_main_appraiser_id ,
p_assignment_id => p_assignment_id ,
p_assignment_start_date => p_assignment_start_date ,
p_asg_business_group_id => p_asg_business_group_id ,
p_assignment_organization_id => p_assignment_organization_id ,
p_assignment_job_id => p_assignment_job_id ,
p_assignment_position_id => p_assignment_position_id ,
p_assignment_grade_id => p_assignment_grade_id,
p_appraisal_system_status => p_appraisal_system_status,
p_potential_readiness_level => p_potential_readiness_level,
p_potential_short_term_workopp => p_potential_short_term_workopp,
p_potential_long_term_workopp => p_potential_long_term_workopp,
p_potential_details => p_potential_details,
p_event_id => p_event_id,
p_show_competency_ratings => p_show_competency_ratings,
p_show_objective_ratings => p_show_objective_ratings,
p_show_questionnaire_info => p_show_questionnaire_info,
p_show_participant_details => p_show_participant_details,
p_show_participant_ratings => p_show_participant_ratings,
p_show_participant_names => p_show_participant_names,
p_show_overall_ratings => p_show_overall_ratings,
p_show_overall_comments => p_show_overall_comments,
p_update_appraisal => p_update_appraisal,
p_provide_overall_feedback => p_provide_overall_feedback,
p_appraisee_comments => p_appraisee_comments,
p_plan_id => p_plan_id,
p_offline_status => p_offline_status,
p_retention_potential => p_retention_potential,
p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
);
(p_module_name => 'update_appraisal',
p_hook_type => 'BP'
);
hr_participants_api.update_participant(p_validate => p_validate,
p_effective_date => p_effective_date,
p_participant_id => l_participant_id_1,
p_object_version_number => l_part_object_version_number_1,
p_participation_type => 'GROUPAPPRAISER'
);
hr_participants_api.update_participant(p_validate => p_validate,
p_effective_date => p_effective_date,
p_participant_id => l_participant_id_2,
p_object_version_number => l_part_object_version_number_2,
p_participation_type => 'MAINAP'
);
update_learning_path(p_appraisal_id, p_appraisal_system_status);
p_update_appraisal => p_update_appraisal,
p_provide_overall_feedback => p_provide_overall_feedback,
p_appraisee_comments => p_appraisee_comments,
p_plan_id => p_plan_id,
p_offline_status => p_offline_status,
p_retention_potential => p_retention_potential,
p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
);
hr_assessments_api.update_assessment ( p_assessment_id => l_assessment_id
, p_object_version_number => l_asn_object_version_number
, p_assessment_period_start_date => p_appraisal_period_start_date
, p_assessment_period_end_date => p_appraisal_period_end_date
, p_assessment_date => p_appraisal_date
, p_validate => p_validate
, p_effective_date => p_effective_date);
hr_appraisals_bk2.update_appraisal_a (
p_effective_date => p_effective_date,
p_appraisal_id => p_appraisal_id,
p_object_version_number => l_object_version_number,
p_appraiser_person_id => p_appraiser_person_id,
p_appraisal_date => p_appraisal_date,
p_appraisal_period_start_date => p_appraisal_period_start_date,
p_appraisal_period_end_date => p_appraisal_period_end_date,
p_type => p_type,
p_next_appraisal_date => p_next_appraisal_date,
p_status => p_status,
p_comments => p_comments,
p_overall_performance_level_id => p_overall_performance_level_id,
p_open => p_open,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_system_type => p_system_type ,
p_system_params => p_system_params,
p_appraisee_access => p_appraisee_access ,
p_main_appraiser_id => p_main_appraiser_id ,
p_assignment_id => p_assignment_id ,
p_assignment_start_date => p_assignment_start_date ,
p_asg_business_group_id => p_asg_business_group_id ,
p_assignment_organization_id => p_assignment_organization_id ,
p_assignment_job_id => p_assignment_job_id ,
p_assignment_position_id => p_assignment_position_id ,
p_assignment_grade_id => p_assignment_grade_id,
p_appraisal_system_status => p_appraisal_system_status,
p_potential_readiness_level => p_potential_readiness_level,
p_potential_short_term_workopp => p_potential_short_term_workopp,
p_potential_long_term_workopp => p_potential_long_term_workopp,
p_potential_details => p_potential_details,
p_event_id => p_event_id,
p_show_competency_ratings => p_show_competency_ratings,
p_show_objective_ratings => p_show_objective_ratings,
p_show_questionnaire_info => p_show_questionnaire_info,
p_show_participant_details => p_show_participant_details,
p_show_participant_ratings => p_show_participant_ratings,
p_show_participant_names => p_show_participant_names,
p_show_overall_ratings => p_show_overall_ratings,
p_show_overall_comments => p_show_overall_comments,
p_update_appraisal => p_update_appraisal,
p_provide_overall_feedback => p_provide_overall_feedback,
p_appraisee_comments => p_appraisee_comments,
p_plan_id => p_plan_id,
p_offline_status => p_offline_status,
p_retention_potential => p_retention_potential,
p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
);
(p_module_name => 'update_appraisal',
p_hook_type => 'AP'
);
ROLLBACK TO update_appraisal;
ROLLBACK TO update_appraisal;
end update_appraisal;
procedure delete_appraisal
(p_validate in boolean default false,
p_appraisal_id in number,
p_object_version_number in number
) is
--
-- Declare cursors and local variables
--
--
cursor c_quest_ans_id
is
select questionnaire_answer_id
from hr_quest_answers
where hr_quest_answers.type = 'APPRAISAL' and
hr_quest_answers.type_object_id = p_appraisal_id; -- Fix for Bug No.1386826
select participant_id,object_version_number from per_participants
where participation_in_id = p_appraisal_id
and participation_in_table = 'PER_APPRAISALS'
and participation_in_column = 'APPRAISAL_ID';
select objective_id ,object_version_number from per_objectives
where appraisal_id = p_appraisal_id;
select assessment_id ,object_version_number from per_assessments
where appraisal_id = p_appraisal_id;
select performance_rating_id ,object_version_number from per_performance_ratings
where appraisal_id = p_appraisal_id;
select competence_element_id ,object_version_number
from per_competence_elements
where assessment_id in
(select assessment_id from per_assessments where appraisal_id = p_appraisal_id);
select performance_review_id, object_version_number
from per_performance_reviews
where event_id in (select event_id from per_appraisals where appraisal_id = p_appraisal_id);
select event_id, object_version_number
from per_events
where event_id in (select event_id from per_appraisals where appraisal_id = p_appraisal_id);
select quest_answer_val_id from
hr_quest_answer_values where questionnaire_answer_id in
(select questionnaire_answer_id from hr_quest_answers
where type = 'APPRAISAL' and type_object_id=p_appraisal_id );
select quest_answer_val_id from
hr_quest_answer_values where questionnaire_answer_id in
(select questionnaire_answer_id from hr_quest_answers
where type = 'PARTICIPANT' and type_object_id in
(select participant_id from per_participants where
participation_in_table='PER_APPRAISALS' and participation_in_column='APPRAISAL_ID' and
participation_in_id=p_appraisal_id
)
);
l_proc varchar2(72) := g_package||'delete_appraisal';
savepoint delete_appraisal;
hr_appraisals_bk3.delete_appraisal_b
(
p_appraisal_id => p_appraisal_id,
p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_appraisal',
p_hook_type => 'BP'
);
hr_quest_perform_web.delete_quest_answer(p_questionnaire_answer_id => l_quest_ans_id);
Order of delete
a) delete_competence_elements based on assessment_id
b) delete_assessment
c) delete_performance_ratings
d) delete_objectives
e) delete_appraisal_quest_answer_values -
f) delete_appraisal_quest_answer -
g) delete_participant_quest_answer_values
- Done internally, after calling hr_quest_perform_web.delete_quest_answer
h) delete_participant_quest_answers
- Done in Participants API
i) delete_participants
j) delete_ota_training_plan_members
- this is under review as to how to be deleted . so nothing for this now
k) hr_perf_review_api.delete_perf_review
(we create an entry in per_performance_reviews table and this has the column
Event_Id which will be same as Event_Id in Per_Appraisals table)
l) per_events_api.delete_event
(we added an Event_id in Per_Appraisals, using that event_id we go here and delete it)
m) delete_appraisal finally .
*/
FOR comp_elmnt_rec in cs_get_comp_elmnt_rec LOOP
hr_competence_element_api.delete_competence_element
(
p_validate => p_validate
,p_competence_element_id => comp_elmnt_rec.competence_element_id
,p_object_version_number => comp_elmnt_rec.object_version_number
);
hr_assessments_api.delete_assessment
(p_validate => p_validate
,p_assessment_id => assessments_rec.assessment_id
,p_object_version_number => assessments_rec.object_version_number
);
hr_performance_ratings_api.delete_performance_rating
(p_validate => p_validate
,p_performance_rating_id => perf_rating_rec.performance_rating_id
,p_object_version_number => perf_rating_rec.object_version_number
);
hr_objectives_api.delete_objective
(p_validate => p_validate
,p_objective_id => objectives_rec.objective_id
,p_object_version_number => objectives_rec.object_version_number
);
hr_participants_api.delete_participant
(p_validate => p_validate
,p_participant_id => participants_rec.participant_id
,p_object_version_number => participants_rec.object_version_number
);
update_learning_path(p_appraisal_id, 'DELETED');
hr_perf_review_api.delete_perf_review
(
p_validate => p_validate
,p_performance_review_id => perf_review_rec.performance_review_id
,p_object_version_number => perf_review_rec.object_version_number
);
per_events_api.delete_event
(
p_validate => p_validate
,p_event_id => per_events_rec.event_id
,p_object_version_number => per_events_rec.object_version_number
);
hr_appraisals_bk3.delete_appraisal_a (
p_appraisal_id => p_appraisal_id,
p_object_version_number => p_object_version_number
);
(p_module_name => 'delete_appraisal',
p_hook_type => 'AP'
);
ROLLBACK TO delete_appraisal;
ROLLBACK TO delete_appraisal;
end delete_appraisal;