The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT objective_id,
name,
target_date,
start_date,
business_group_id,
object_version_number,
owning_person_id,
achievement_date,
detail,
comments,
success_criteria,
appraisal_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
scorecard_id,
copied_from_library_id,
copied_from_objective_id,
aligned_with_objective_id,
next_review_date,
group_code,
priority_code,
appraise_flag,
verified_flag,
weighting_percent,
complete_percent,
target_value,
actual_value,
uom_code,
measurement_style_code,
measure_name,
measure_type_code,
measure_comments,
sharing_access_code
FROM per_objectives
WHERE appraisal_id = p_appraisal_id;
select appr.appraisal_id, appr.object_version_number,
appr.appraiser_person_id, appr.appraisee_person_id, ppf.full_name,
appr.appraisal_date, apprstatus.meaning appraisal_status, apprtype.meaning appraisal_type,
nvl(appr.provide_overall_feedback,'N') provide_overall_feedback, appr.appraisal_system_status
from per_appraisals appr, per_all_people_f ppf, hr_lookups apprstatus,
hr_lookups apprtype
where appr.appraisal_id = appr_id
and ppf.person_id = appr.appraisee_person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and apprstatus.lookup_type = 'APPRAISAL_SYSTEM_STATUS'
and apprstatus.lookup_code = appr.appraisal_system_status
and apprtype.lookup_type = 'APPRAISAL_SYS_TYPE'
and apprtype.lookup_code = appr.system_type;
select hrl.meaning
from per_appraisals appr, hr_lookups hrl
where appraisal_id = appr_id
and hrl.lookup_type = 'APPRAISAL_SYSTEM_STATUS'
and appr.appraisal_system_status = hrl.lookup_code;
hr_appraisals_api.update_appraisal
(p_effective_date => trunc(sysdate)
,p_appraisal_id => appraisal_record.appraisal_id
,p_object_version_number => appraisal_record.object_version_number
,p_appraiser_person_id => appraisal_record.appraiser_person_id
,p_appraisal_system_status => 'COMPLETED');
hr_appraisals_api.update_appraisal
(p_effective_date => trunc(sysdate)
,p_appraisal_id => appraisal_record.appraisal_id
,p_object_version_number => appraisal_record.object_version_number
,p_appraiser_person_id => appraisal_record.appraiser_person_id
,p_appraisal_system_status => 'APPRFEEDBACK');
hr_appraisals_api.update_appraisal
(p_effective_date => trunc(sysdate)
,p_appraisal_id => appraisal_record.appraisal_id
,p_object_version_number => appraisal_record.object_version_number
,p_appraiser_person_id => appraisal_record.appraiser_person_id
,p_appraisal_system_status => 'COMPLETED');
fnd_msg_pub.Delete_Msg;
select pce.competence_id, pc.name, pce.competence_element_id, pce.proficiency_level_id,
pce.business_group_id, pce.enterprise_id,pce.effective_date_from, pce.effective_date_to, pa.appraisal_id, pa.appraisee_person_id,
ppf.party_id, decode(rating.step_value, null, null, rating.step_value||' - '||rating.name) prof_level
from per_competence_elements pce, per_appraisals pa, per_all_people_f ppf, per_competences pc,
per_rating_levels rating
where pce.type='ASSESSMENT'
and pce.object_name = 'APPRAISAL_ID'
and pce.object_id = appr_id
and pa.appraisal_id = pce.object_id
and pa.appraisee_person_id = ppf.person_id
and pce.competence_id = pc.competence_id
and pce.proficiency_level_id = rating.rating_level_id (+)
and pce.proficiency_level_id is not null
and trunc(sysdate) between nvl(trunc(ppf.effective_start_date),trunc(sysdate))
and nvl(trunc(ppf.effective_end_date),trunc(sysdate));
select appr.appraisee_person_id,
pce.competence_element_id, pce.object_version_number, pce.type,
pce.business_group_id,
pce.enterprise_id, pce.competence_id, pce.proficiency_level_id,
pce.high_proficiency_level_id, pce.weighting_level_id, pce.rating_level_id,
pce.person_id, pce.job_id , pce.valid_grade_id, pce.position_id,
pce.organization_id, pce.parent_competence_element_id, pce.activity_version_id,
pce.assessment_id, pce.assessment_type_id, pce.mandatory,
pce.effective_date_from, pce.effective_date_to, pce.group_competence_type,
pce.competence_type, pce.normal_elapse_duration, pce.normal_elapse_duration_unit,
pce.sequence_number, pce.source_of_proficiency_level,
pce.line_score, pce.certification_date ,
pce.certification_method, pce.next_certification_date,
pce.comments, pce.attribute_category, pce.attribute1,
pce.attribute2, pce.attribute3, pce.attribute4, pce.attribute5,
pce.attribute6, pce.attribute7, pce.attribute8, pce.attribute9,
pce.attribute10, pce.attribute11, pce.attribute12,
pce.attribute13, pce.attribute14, pce.attribute15, pce.attribute16,
pce.attribute17, pce.attribute18, pce.attribute19,
pce.attribute20, pce.object_id,
pce.object_name, pce.party_id
from per_appraisals appr, per_competence_elements pce
where appr.appraisal_id = appr_id
and appr.appraisee_person_id = pce.person_id
and pce.type = 'PERSONAL'
and trunc(sysdate) between pce.effective_date_from and
nvl(pce.effective_date_to, trunc(sysdate));
Select ceo.COMP_ELEMENT_OUTCOME_ID, ceo.COMPETENCE_ELEMENT_ID, ceo.OUTCOME_ID,
ceo.DATE_FROM, ceo.DATE_TO, ceo.OBJECT_VERSION_NUMBER, ceo.ATTRIBUTE_CATEGORY,
ceo.ATTRIBUTE1, ceo.ATTRIBUTE2, ceo.ATTRIBUTE3, ceo.ATTRIBUTE4, ceo.ATTRIBUTE5,
ceo.ATTRIBUTE6, ceo.ATTRIBUTE7, ceo.ATTRIBUTE8, ceo.ATTRIBUTE9, ceo.ATTRIBUTE10,
ceo.ATTRIBUTE11, ceo.ATTRIBUTE12, ceo.ATTRIBUTE13, ceo.ATTRIBUTE14, ceo.ATTRIBUTE15,
ceo.ATTRIBUTE16, ceo.ATTRIBUTE17, ceo.ATTRIBUTE18, ceo.ATTRIBUTE19, ceo.ATTRIBUTE20,
ceo.INFORMATION_CATEGORY, ceo.INFORMATION1, ceo.INFORMATION2, ceo.INFORMATION3,
ceo.INFORMATION4, ceo.INFORMATION5, ceo.INFORMATION6, ceo.INFORMATION7, ceo.INFORMATION8,
ceo.INFORMATION9, ceo.INFORMATION10, ceo.INFORMATION11, ceo.INFORMATION12,
ceo.INFORMATION13, ceo.INFORMATION14, ceo.INFORMATION15, ceo.INFORMATION16,
ceo.INFORMATION17, ceo.INFORMATION18, ceo.INFORMATION19, ceo.INFORMATION20
From per_comp_element_outcomes ceo,
per_competence_outcomes co
Where ceo.Competence_element_id = p_competence_element_id
AND co.outcome_id = ceo.outcome_id
AND co.DATE_FROM <= ceo.DATE_FROM
AND nvl(co.date_to,nvl(ceo.date_to,trunc(sysdate))) >= nvl(ceo.date_to,trunc(sysdate));
hr_competence_element_api.update_competence_element
(p_competence_element_id => pers_comps.competence_element_id
,p_object_version_number => l_old_ovn
,p_effective_date_to => trunc(sysdate) - 1
,p_effective_date => trunc(sysdate)
,p_validate => false
);
fnd_msg_pub.Delete_Msg;
fnd_msg_pub.Delete_Msg;
query_lp_courses VARCHAR2(4000) := ' select tav.version_name course_name, hrl.meaning member_status, ' ||
' lpme.completion_target_date, lpme.completion_date ' ||
' from ota_learning_path_members lpm, ota_lp_member_enrollments lpme, ' ||
' ota_activity_versions tav, hr_lookups hrl ' ||
' where lpm.learning_path_id = :1 ' ||
' and lpme.learning_path_member_id = lpm.learning_path_member_id ' ||
' and hrl.lookup_code = lpme.member_status_code ' ||
' and hrl.lookup_type = ''OTA_LP_MEMBER_STATUS''' ||
' and tav.activity_version_id = lpm.activity_version_id ';
PROCEDURE update_train_component_status
( appr_id IN per_appraisals.appraisal_id%TYPE,
p_log in out nocopy varchar2,
upd_train_comps_status in out nocopy varchar2,
p_new_appraisal in boolean DEFAULT true)
IS
l_cursor cur_typ;
query_str VARCHAR2(4000) := ' select lp.learning_path_id, lp.display_to_learner_flag, lp.object_version_number, ' ||
' lpe.lp_enrollment_id,lpe.object_version_number,hrl.meaning , lptl.name ' ||
' from ota_learning_paths lp, ota_learning_paths_tl lptl, ota_lp_enrollments lpe, ' ||
' hr_lookups hrl ' ||
' where lp.source_id = :1 and lp.path_source_code= :2 ' ||
' and lp.source_function_code = :3 ' ||
' and lptl.learning_path_id = lp.learning_path_id ' ||
' and lptl.language = userenv(''lang'') ' ||
' and lpe.learning_path_id = lp.learning_path_id ' ||
' and hrl.lookup_code = lpe.path_status_code ' ||
' and hrl.lookup_type = ''OTA_LEARNING_PATH_STATUS''';
' select hrl.meaning path_status_code ' ||
' from ota_learning_paths lp, ' ||
' ota_lp_enrollments lpe, hr_lookups hrl ' ||
' where lp.learning_path_id = :1 ' ||
' and lpe.learning_path_id = lp.learning_path_id ' ||
' and lpe.path_status_code = hrl.lookup_code ' ||
' and hrl.lookup_type = ''OTA_LEARNING_PATH_STATUS''';
l_stmt := 'begin ota_learning_path_api.update_learning_path( ' ||
'p_effective_date => trunc(sysdate) , ' ||
'p_learning_path_id => :1,' ||
'p_object_version_number => :2 ,' ||
'p_display_to_learner_flag => :3 ); end;';
fnd_msg_pub.Delete_Msg;
l_stmt := 'begin ota_lp_enrollment_api.update_lp_enrollment( ' ||
'p_effective_date => trunc(sysdate) , ' ||
'p_lp_enrollment_id => :1 ,' ||
'p_object_version_number => :2 ,' ||
'p_path_status_code => :3); end;';
fnd_msg_pub.Delete_Msg;
query_str := 'SELECT ota_lrng_path_util.chk_complete_path_ok(:1) from dual';
fnd_msg_pub.Delete_Msg;
select appr.appraisal_id, appr.object_version_number, appr.appraiser_person_id,
appr.appraisee_person_id, appr.overall_performance_level_id, prl.step_value,
(prl.step_value||' - '||prl.name) overall_rating
from per_appraisals appr, per_rating_levels prl
where appraisal_id = appr_id
and appr.overall_performance_level_id = prl.rating_level_id;
select current_npw_flag from per_all_people_f
where person_id = appraisee_person_id
and trunc(sysdate) between effective_start_date and effective_end_date;
hr_appraisals_api.update_appraisal
(p_effective_date => trunc(sysdate)
,p_appraisal_id => appr_id
,p_appraiser_person_id => appraisal_rec.appraiser_person_id
,p_object_version_number => appraisal_rec.object_version_number
,p_event_id => l_event_id);
fnd_msg_pub.Delete_Msg;
PROCEDURE update_appr_objectives(p_appr_objs in appr_obj_table)
IS
I INTEGER default 0;
hr_objectives_api.update_objective
(p_validate => false
,p_effective_date => trunc(sysdate) --<== ?
,p_objective_id => p_appr_objs(I).objective_id
,p_object_version_number => l_object_version_number
,p_scorecard_id => null
,p_weighting_over_100_warning => l_weighting_over_100_warning
,p_weighting_appraisal_warning => l_weighting_appraisal_warning
);
SELECT plan_id, appraisal_template_id
FROM per_appraisals
WHERE appraisal_id = p_appraisal_id;
update_appr_objectives(l_appr_objs);
update_personal_profile varchar2(100) default null;
select available_flag ,update_personal_comp_profile,comp_profile_source_type
from per_appraisal_templates pat ,per_appraisals pa where
pa.appraisal_template_id = pat.appraisal_template_id
and pa.appraisal_id = c_appraisal_id;
fetch c_appr_template_details into l_available_flag,update_personal_profile,l_talent_mang_src_typ;
update_personal_profile := fnd_profile.value('HR_APPLY_COMPETENCIES_TO_PERSON');
if(update_personal_profile is not null and update_personal_profile = 'Y') then
hr_utility.set_location(' Entering:' || l_proc,85);
update_train_component_status(appraisal_id, lv_upd_train_comps_status_log, upd_train_comps_status, p_new_appraisal);
update_personal_profile varchar2(100) default null;
select appraisal_id, appraisee_access, system_type from per_appraisals where appraisal_id = appr_id;