[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pap.parameter_value
FROM pay_action_parameters pap
WHERE pap.parameter_name = p_parameter_name;
IF (p_reverse_mode = 'N' and p_status_code NOT IN ('DRAFT', 'UPDATED','SUBMITTED','RESUBMITTED')) THEN
-- Set the message name, so that exception handler can get translated text
IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 20); END IF;
select appraisal_period_id, appraisal_template_id, start_date, end_date, task_start_date,
task_end_date, initiator_code, appraisal_system_type, auto_conc_process, days_before_task_st_dt,
appraisal_assmt_status, appraisal_type
from per_appraisal_periods pap
where pap.plan_id = p_plan_id
and pap.appraisal_period_id = p_appr_period_id;
l_selected_menu fnd_menus.menu_name%TYPE default null;
select menu_functions.function_id, ff.function_name, ff.parameters, menu_id from FND_COMPILED_MENU_FUNCTIONS menu_functions, fnd_form_functions ff
where menu_id = (select menu_id from fnd_menus where menu_name = p_menu_name)
and ff.function_id = menu_functions.function_id
and ff.parameters like p_search_func;
l_selected_menu := l_appraisal_mgr_menu;
l_selected_menu := l_appraisal_empl_menu;
IF g_dbg THEN op('l_selected_menu = ' || l_selected_menu, g_DEBUG_LOG); END IF;
open get_appraisal_function(l_selected_menu, l_system_type);
SELECT pc.name competence_name,
pce.competence_id,
pce.competence_element_id,
pce.mandatory,
pce.proficiency_level_id,
pce.high_proficiency_level_id,
pce.organization_id,
nvl(pce.job_id, -1) job_id,
nvl(pce.position_id, -1) position_id,
pce.valid_grade_id,
nvl(pce.business_group_id, -1) business_group_id,
pce.enterprise_id,
hrl.meaning structure_type,
decode(job_id, null, decode(position_id, null, 1, 0), 0) read_only_attr,
0 detail_attr,
pc.competence_alias,
decode(pc.business_group_id, null, 'Y', 'N') global,
pc.description,
pc.date_from,
pc.certification_required,
pc.behavioural_indicator,
r1.step_value low_step_value,
r1.name low_step_name,
r2.step_value high_step_value,
r2.name high_step_name,
hrl.lookup_code,
decode(r1.step_value, null, null, r1.step_value||' - '||r1.name) minimum_proficiency,
decode(r2.step_value, null, null, r2.step_value||' - '||r2.name) maximum_proficiency,
pce.type
FROM per_competence_elements pce,
per_competences_vl pc,
hr_lookups hrl,
per_rating_levels_vl r1,
per_rating_levels_vl r2
WHERE pce.type = 'REQUIREMENT'
AND pce.competence_id = pc.competence_id
AND trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
and nvl(pce.effective_date_to, trunc(sysdate))
AND hrl.lookup_type (+) = 'STRUCTURE_TYPE'
AND hrl.lookup_code (+) = decode(pce.organization_id, null, (decode(pce.job_id, null, (decode(pce.position_id, null, 'BUS', 'POS')), 'JOB')), 'ORG')
AND pce.proficiency_level_id = r1.rating_level_id(+)
AND pce.high_proficiency_level_id = r2.rating_level_id(+)
AND pce.business_group_id = enter_price_id
and( pce.enterprise_id = nvl(enter_price_id, -1) or pce.organization_id = nvl(p_in_org_id, -1) or pce.job_id = nvl(p_in_job_id, -1) or pce.position_id = nvl(p_in_pos_id,-1) );
SELECT scorecard_id, assignment_id, person_id, scorecard_name FROM per_personal_scorecards
WHERE scorecard_id = p_scorecard_id;
SELECT paf.assignment_id, paf.business_group_id, paf.grade_id, paf.position_id,
paf.job_id,
paf.organization_id, paf.supervisor_id, paf.effective_start_date,
pps.date_start empl_start_date,
ppf.effective_end_date empl_end_date, ppf.person_id
FROM per_all_assignments_f paf, per_all_people_f ppf, per_periods_of_service
pps
WHERE paf.assignment_id = p_assignment_id
and trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and paf.person_id = ppf.person_id
and paf.assignment_type = 'E'
and trunc(sysdate) between ppf.effective_start_date and
ppf.effective_end_date
and pps.period_of_service_id = paf.period_of_service_id
UNION ALL
SELECT paf.assignment_id, paf.business_group_id, paf.grade_id, paf.position_id,
paf.job_id,
paf.organization_id, paf.supervisor_id, paf.effective_start_date,
pps.date_start empl_start_date,
ppf.effective_end_date empl_end_date, ppf.person_id
FROM per_all_assignments_f paf, per_all_people_f ppf,
per_periods_of_placement pps
WHERE paf.assignment_id = p_assignment_id
and trunc(sysdate) between paf.effective_start_date and
paf.effective_end_date
and paf.assignment_type = 'C'
and paf.person_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and
ppf.effective_end_date
and pps.date_start= paf.PERIOD_OF_PLACEMENT_DATE_START
and pps.person_id = paf.person_id;
SELECT appraisal_template_id, assessment_type_id, objective_asmnt_type_id, business_group_id,
show_competency_ratings ,
show_objective_ratings ,
show_questionnaire_info ,
show_participant_details,
show_participant_ratings,
show_participant_names ,
show_overall_ratings ,
provide_overall_feedback, -- Bug7393131
show_overall_comments
FROM per_appraisal_templates
WHERE appraisal_template_id = p_appraisal_templ_id;
select ce.competence_id, ce.competence_element_id, ce.type, ce.parent_competence_element_id, c.name,
rank() over (partition by ce.competence_id order by ce.competence_element_id) rank
from per_competence_elements a, per_competence_elements ce, per_competences_vl c
where a.assessment_type_id = p_assess_type_id
and a.type = 'ASSESSMENT_GROUP'
and (nvl(c.date_from,trunc(sysdate)) <= trunc(sysdate))
and nvl(c.date_to,trunc(sysdate)) >= trunc(sysdate)
and a.competence_element_id = ce.parent_competence_element_id
and ce.competence_id = c.competence_id;
select DEFAULT_JOB_COMPETENCIES from per_assessment_types
where assessment_type_id = p_assessment_type_id;
select objective_id, scorecard_id, object_version_number, name from per_objectives
where scorecard_id = p_scorecard_id
and APPRAISE_FLAG = 'Y';
select DEFAULT_JOB_COMPETENCIES, ASSESSMENT_TYPE_ID from per_assessment_types
where ASSESSMENT_TYPE_ID = p_assess_templ;
select appraisal_id,appraisal_system_status from per_appraisals
where plan_id = p_plan_id
and appraisal_period_start_date = p_appr_prd_st_dt --5194541 to_date(p_appr_prd_st_dt,'RRRR-MM-DD')
and appraisal_period_end_date = p_appr_prd_ed_dt --5194541 to_date(p_appr_prd_ed_dt,'RRRR-MM-DD')
and appraisee_person_id = p_appraisee_person_id
and appraisal_system_status <> 'TRANSFER_OUT' -- 7321947
-- and appraiser_person_id = p_appraiser_person_id
-- and main_appraiser_id = p_main_appraiser_id
and appraisal_template_id = p_appr_templ_id;
hr_objectives_api.update_objective(p_validate => false,
p_objective_id => objectives.objective_id,
p_object_version_number => objectives.object_version_number,
p_effective_date => p_effective_date,
p_appraisal_id => l_found_appraisal_id,-- modified by AM --
p_weighting_over_100_warning => l_weighting_over_100_warning,
p_weighting_appraisal_warning => l_weighting_appraisal_warning
);
p_update_appraisal => 'Y',
p_appraisee_person_id => l_scorecard_info.person_id,
p_appraiser_person_id => l_appraiser_id, --to be changed for position
p_appraisal_date => l_appraisal_date,
p_appraisal_period_start_date => l_appraisal_period_start_date ,
p_appraisal_period_end_date => p_appraisal_end_date,
p_type => p_type, -- ANN,
p_next_appraisal_date => p_next_appraisal_date,
p_status => p_status, -- PLANNED,TRANSFER,RFC,
p_comments => p_comments,
p_system_type => l_appraisal_sys_type, --MGR360 EMP360
p_system_params => l_func_params,
--p_appraisee_access,
p_main_appraiser_id => l_main_appraiser_id, --to be changed for position
p_assignment_id => l_assignment_info.assignment_id ,
p_assignment_start_date => l_assignment_info.effective_state_date,
p_asg_business_group_id => l_assignment_info.business_group_id ,
p_assignment_organization_id => l_assignment_info.org_id ,
p_assignment_job_id => l_assignment_info.job_id ,
--p_assignment_position_id = l_assignment_info.position_id ,
p_assignment_grade_id => l_assignment_info.grade_id ,
p_appraisal_id => l_apprl_id,
p_object_version_number => l_appraisal_ovn ,
p_appraisal_system_status => p_appraisal_status ,
p_plan_id => p_plan_id);
hr_objectives_api.update_objective(p_validate => false,
p_objective_id => objectives.objective_id,
p_object_version_number => objectives.object_version_number,
p_effective_date => p_effective_date,
p_appraisal_id => l_apprl_id, -- to be changed in SWI,API,RHI
p_weighting_over_100_warning => l_weighting_over_100_warning,
p_weighting_appraisal_warning => l_weighting_appraisal_warning
);
,p_delete_pending_trans IN VARCHAR2 DEFAULT 'N'
) is
--
cursor csr_plan_ovn is
select object_version_number
from per_perf_mgmt_plans
where plan_id = p_plan_id;
SELECT 'x'
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM hr_api_transactions t
,per_personal_scorecards sc
WHERE t.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
AND t.transaction_ref_id = sc.scorecard_id
AND sc.plan_id = p_plan_id);
IF nvl(p_delete_pending_trans,'N') ='N' THEN
OPEN csr_pend_trans(p_plan_id);
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
select objective_id, object_version_number
from per_objectives
where scorecard_id = p_scorecard_id;
select appraisal_id, object_version_number from per_appraisals
where plan_id = plan_id;
SELECT scorecard_id,object_version_number,status_code
FROM per_personal_scorecards
WHERE plan_id = p_plan_id;
SELECT transaction_id
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
AND transaction_ref_id = p_sc_card_id;
delete from hr_api_transaction_steps
where transaction_id = l_trx_id;
delete from hr_api_transactions
where transaction_id = l_trx_id;
hr_personal_scorecard_api.update_scorecard_status
(p_effective_date => trunc(sysdate)
,p_scorecard_id => curr_scorecard.scorecard_id
,p_object_version_number => l_sc_ovn
,p_status_code => 'PUBLISHED'
);