[Home] [Help]
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;
:= ' 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;
:= ' 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''';
'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;
'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;
'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, hr_lookups hrl
WHERE appraisal_id = appr_id
AND appr.overall_performance_level_id = prl.rating_level_id
AND hrl.lookup_code = TO_CHAR (prl.step_value)
AND hrl.lookup_type = 'PERFORMANCE_RATING'
AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, TRUNC (SYSDATE))
AND NVL (end_date_active, TRUNC (SYSDATE));
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);
PROCEDURE update_succ_plan_eit (p_appraisal_id IN NUMBER)
IS
l_proc VARCHAR2 (100);
SELECT appraisal_id,
appraisee_person_id,
appraisal_period_start_date,
appraisal_period_end_date,
potential_readiness_level,
retention_potential
FROM per_appraisals
WHERE appraisal_id = p_appraisal_id;
l_proc := 'HR_COMPLETE_APPRAISAL_SS.UPDATE_SUCC_PLAN_EIT';
END update_succ_plan_eit;
SELECT 'Y'
FROM dual
WHERE EXISTS
(
SELECT 'x'
FROM per_appraisals pa
,hr_questionnaires hq
,hr_quest_fields hqf
,per_participants pp
WHERE pa.appraisal_id = c_appraisal_id
AND pp.participation_in_table = 'PER_APPRAISALS'
AND pp.participation_in_id = pa.appraisal_id
AND pp.participation_type = 'MAINAP'
AND pp.questionnaire_template_id = hq.questionnaire_template_id
AND nvl (hq.quest_type_code
,'OLDUI') = 'NEWUI'
AND hq.questionnaire_template_id = hqf.questionnaire_template_id
AND nvl (hqf.sql_required_flag
,'N') = 'Y'
AND NOT EXISTS
(
SELECT hqav.field_id
FROM hr_quest_answers hqa
,hr_quest_answer_values hqav
WHERE hqa.questionnaire_template_id = hq.questionnaire_template_id
AND hqa.type = 'PARTICIPANT'
AND hqa.type_object_id = pp.participant_id
AND hqa.questionnaire_answer_id = hqav.questionnaire_answer_id
AND hqav.field_id = hqf.field_id
AND hqav.value IS NOT NULL
)
);
SELECT hr_general.decode_person_name (person_id) person
FROM (
SELECT DISTINCT(pp.person_id) person_id
FROM per_appraisals pa
,hr_questionnaires hq
,hr_quest_fields hqf
,per_participants pp
WHERE pa.appraisal_id = c_appraisal_id
AND pp.participation_in_table = 'PER_APPRAISALS'
AND pp.participation_in_id = pa.appraisal_id
AND pp.participation_type <> 'MAINAP'
AND pp.questionnaire_template_id = hq.questionnaire_template_id
AND nvl (hq.quest_type_code
,'OLDUI') = 'NEWUI'
AND hq.questionnaire_template_id = hqf.questionnaire_template_id
AND nvl (hqf.sql_required_flag
,'N') = 'Y'
AND NOT EXISTS
(
SELECT hqav.field_id
FROM hr_quest_answers hqa
,hr_quest_answer_values hqav
WHERE hqa.questionnaire_template_id = hq.questionnaire_template_id
AND hqa.type = 'PARTICIPANT'
AND hqa.type_object_id = pp.participant_id
AND hqa.questionnaire_answer_id = hqav.questionnaire_answer_id
AND hqav.field_id = hqf.field_id
AND hqav.value IS NOT NULL
)
);
SELECT 'Y'
FROM dual
WHERE EXISTS
(
SELECT 'x'
FROM per_appraisals pa
,per_appraisal_templates pat
,hr_questionnaires hq
,hr_quest_fields hqf
WHERE pa.appraisal_id = c_appraisal_id
AND pa.appraisal_template_id = pat.appraisal_template_id
AND pat.questionnaire_template_id = hq.questionnaire_template_id
AND nvl (hq.quest_type_code
,'OLDUI') = 'NEWUI'
AND hq.questionnaire_template_id = hqf.questionnaire_template_id
AND nvl (hqf.sql_required_flag
,'N') = 'Y'
AND NOT EXISTS
(
SELECT hqav.field_id
FROM hr_quest_answers hqa
,hr_quest_answer_values hqav
WHERE hqa.questionnaire_template_id = hq.questionnaire_template_id
AND hqa.type = 'APPRAISAL'
AND hqa.type_object_id = pa.appraisal_id
AND hqa.questionnaire_answer_id = hqav.questionnaire_answer_id
AND hqav.field_id = hqf.field_id
AND hqav.value IS NOT NULL
)
);
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;
SELECT pa.appraisee_person_id
INTO l_sel_person_id
FROM per_appraisals pa
WHERE pa.appraisal_id = l_appraisal_id;
SELECT transaction_id
INTO l_source_pk1_value
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_APPRAISALS' AND transaction_ref_id = l_appraisal_id
AND item_type = COMPLETE_APPR.item_type
AND item_key = complete_appr.item_key;
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);
hr_utility.set_location ('before update_succ_plan_eit:' || l_proc, 96);
update_succ_plan_eit (appraisal_id);
hr_utility.set_location ('before update_succ_plan_eit:' || l_proc, 96);
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;
SELECT provide_overall_feedback
INTO lv_provide_overall_feedback
FROM per_appraisals
WHERE appraisal_id = l_appraisal_id;
UPDATE per_appraisals
SET provide_overall_feedback = 'N'
WHERE appraisal_id = l_appraisal_id;
UPDATE per_appraisals
SET provide_overall_feedback = 'Y'
WHERE appraisal_id = l_appraisal_id;