[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE t_selected_entities IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
g_selected_entities t_selected_entities;
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 * from
( SELECT asg.assignment_id, asg.person_id, asg.business_group_id,
asg.supervisor_id, asg.supervisor_assignment_id, asg.organization_id, asg.position_id
FROM per_all_assignments_f asg
WHERE ((p_plan_rec.assignment_types_code in ('E', 'C')
and asg.assignment_type = p_plan_rec.assignment_types_code) OR
(p_plan_rec.assignment_types_code = 'EC'
and asg.assignment_type IN ('E', 'C')))
AND p_effective_date BETWEEN
asg.effective_start_date AND asg.effective_end_date
AND ((p_plan_rec.primary_asg_only_flag = 'N') OR
p_plan_rec.primary_asg_only_flag = 'Y' AND
asg.primary_flag = 'Y')
AND 'x' = ( select 'x' from per_all_people_f ppf
where asg.person_id = ppf.person_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and ('Y' = decode( p_plan_rec.assignment_types_code, 'E', ppf.current_employee_flag, 'C',
ppf.current_npw_flag, 'EC', (select 'Y' from dual where
ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y') )) )
) o
CONNECT BY o.supervisor_id = PRIOR o.person_id
AND LEVEL <= NVL(p_plan_rec.hierarchy_levels, LEVEL) + 1
START WITH o.person_id = p_plan_rec.supervisor_id;
SELECT * from
(SELECT asg.assignment_id, asg.person_id, asg.business_group_id, asg.supervisor_id,
asg.supervisor_assignment_id, asg.organization_id, asg.position_id
FROM per_all_assignments_f asg
WHERE ((p_plan_rec.assignment_types_code in ('E', 'C')
and asg.assignment_type = p_plan_rec.assignment_types_code) OR
(p_plan_rec.assignment_types_code = 'EC'
and asg.assignment_type IN ('E', 'C')))
AND p_effective_date BETWEEN
asg.effective_start_date AND asg.effective_end_date
AND 'x' = ( select 'x' from per_all_people_f ppf
where asg.person_id = ppf.person_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and ('Y' = decode( p_plan_rec.assignment_types_code, 'E', ppf.current_employee_flag, 'C',
ppf.current_npw_flag, 'EC', (select 'Y' from dual where
ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y') )) )
) o
CONNECT BY o.supervisor_assignment_id = PRIOR o.assignment_id
AND LEVEL <= NVL(p_plan_rec.hierarchy_levels, LEVEL) + 1
START WITH o.assignment_id = p_plan_rec.supervisor_assignment_id;
SELECT asg.assignment_id, asg.person_id, asg.business_group_id, asg.supervisor_id,
asg.supervisor_assignment_id, asg.organization_id, asg.position_id
FROM per_all_assignments_f asg
WHERE ((p_plan_rec.assignment_types_code in ('E', 'C')
and asg.assignment_type = 'E') OR
(p_plan_rec.assignment_types_code = 'EC'
and asg.assignment_type IN ('E', 'C')))
AND p_effective_date BETWEEN
asg.effective_start_date AND asg.effective_end_date
AND ((p_plan_rec.primary_asg_only_flag = 'N') OR
p_plan_rec.primary_asg_only_flag = 'Y' AND
asg.primary_flag = 'Y')
AND 'x' = ( select 'x' from per_all_people_f ppf
where asg.person_id = ppf.person_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and ('Y' = decode( p_plan_rec.assignment_types_code, 'E', ppf.current_employee_flag, 'C',
ppf.current_npw_flag, 'EC', (select 'Y' from dual where
ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y') )) )
AND asg.organization_id in
(select o.organization_id_child from
(SELECT o.organization_id_child
FROM per_org_structure_elements o
CONNECT BY o.organization_id_parent = PRIOR o.organization_id_child
AND o.org_structure_version_id = PRIOR o.org_structure_version_id
AND LEVEL <= NVL(p_plan_rec.hierarchy_levels, LEVEL)
START WITH o.organization_id_parent = p_plan_rec.top_organization_id
AND o.org_structure_version_id = p_plan_rec.org_structure_version_id
UNION
SELECT p_plan_rec.top_organization_id organization_id_child from dual) o, hr_organization_units org
where o.organization_id_child = org.organization_id
and p_effective_date between org.date_from and nvl(org.date_to, p_effective_date));
SELECT asg.assignment_id, asg.person_id, asg.business_group_id, asg.supervisor_id,
asg.supervisor_assignment_id, asg.organization_id, asg.position_id
FROM per_all_assignments_f asg
WHERE ((p_plan_rec.assignment_types_code in ('E', 'C')
and asg.assignment_type = p_plan_rec.assignment_types_code) OR
(p_plan_rec.assignment_types_code = 'EC'
and asg.assignment_type IN ('E', 'C')))
AND p_effective_date BETWEEN
asg.effective_start_date AND asg.effective_end_date
AND ((p_plan_rec.primary_asg_only_flag = 'N') OR
p_plan_rec.primary_asg_only_flag = 'Y' AND
asg.primary_flag = 'Y')
AND 'x' = ( select 'x' from per_all_people_f ppf
where asg.person_id = ppf.person_id
and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and ('Y' = decode( p_plan_rec.assignment_types_code, 'E', ppf.current_employee_flag, 'C',
ppf.current_npw_flag, 'EC', (select 'Y' from dual where
ppf.current_employee_flag = 'Y' or ppf.current_npw_flag = 'Y') )) )
AND ( asg.position_id in
(select o.subordinate_position_id from (SELECT p.subordinate_position_id
FROM per_pos_structure_elements p
CONNECT BY p.parent_position_id = PRIOR p.subordinate_position_id
AND p.pos_structure_version_id = PRIOR p.pos_structure_version_id
AND LEVEL <= NVL(p_plan_rec.hierarchy_levels, LEVEL)
START WITH p.parent_position_id = p_plan_rec.top_position_id
AND p.pos_structure_version_id = p_plan_rec.pos_structure_version_id) o, per_positions pos
where o.subordinate_position_id = pos.position_id
and p_effective_date between pos.date_effective and nvl(pos.date_end,p_effective_date)
UNION
select p_plan_rec.top_position_id subordinate_position_id from dual));
select prds.appraisal_period_id, prds.appraisal_template_id, templ.business_group_id
from per_appraisal_periods prds, per_appraisal_templates templ
where prds.plan_id = p_pln_id
and templ.appraisal_template_id = prds.appraisal_template_id;
g_plan_pop_known_t.delete;
IF g_dbg THEN op(' Selected Population is not in Appraisal Template BG ', g_REGULAR_LOG, 90); END IF;
select assignment_id, scorecard_id, object_version_number, status_code
from per_personal_scorecards
where creator_type = 'AUTO'
and plan_id = p_plan_id
and status_code <> 'TRANSFER_OUT';
select objective_id, objective_name, valid_from, valid_to, target_date, next_review_date, group_code,
priority_code, appraise_flag, weighting_percent, target_value, uom_code, measurement_style_code,
measure_name, measure_type_code, measure_comments, details, success_criteria, comments,
elig.elig_obj_id
from per_objectives_library pol,
ben_elig_obj_f elig
where (pol.valid_from between p_start_date and p_end_date OR
p_start_date between pol.valid_from and nvl(pol.valid_to,to_date('31-12-4712', 'DD-MM-YYYY')))
and POL.ELIGIBILITY_TYPE_CODE <> 'N_P'
and elig.table_name = 'PER_OBJECTIVES_LIBRARY'
and column_name = 'OBJECTIVE_ID'
and elig.column_value = pol.objective_id;
select copied_from_library_id, objective_id, object_version_number
from per_objectives pob
where pob.copied_from_library_id IS NOT NULL
and pob.scorecard_id = p_scorecard_id;
g_curr_sc_obj_tbl.DELETE;
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;
procedure update_scorecard_for_person
(p_effective_date in date
,p_scorecard_id in number
,p_object_version_number in number
,p_scorecard_name in varchar2 default hr_api.g_varchar2
,p_start_date in date default hr_api.g_date
,p_end_date in date default hr_api.g_date
,p_status_code in varchar2 default hr_api.g_varchar2
) is
-- Declare local variables
l_proc varchar2(72) := g_package||'update_scorecard_for_person';
hr_personal_scorecard_api.update_scorecard(
p_effective_date => p_effective_date
,p_scorecard_id => p_scorecard_id
,p_object_version_number => l_object_version_number
,p_scorecard_name => p_scorecard_name
,p_start_date => p_start_date
,p_end_date => p_end_date
,p_status_code => p_status_code
,p_duplicate_name_warning => l_duplicate_name_warning
);
End update_scorecard_for_person;
procedure delete_scorecard_for_person
(p_scorecard_id in number
,p_object_version_number in number
) is
-- Declare local variables
l_proc varchar2(72) := g_package||'delete_scorecard_for_person';
hr_personal_scorecard_api.delete_scorecard(
p_scorecard_id => p_scorecard_id
,p_object_version_number => p_object_version_number
,p_created_by_plan_warning => l_created_by_plan_warning
);
End delete_scorecard_for_person;
procedure update_scorecard_objective
(p_effective_date in date
,p_objective_id in number
,p_object_version_number in number
,p_scorecard_id in number default hr_api.g_number
,p_start_date in date default hr_api.g_date
,p_end_date in date default hr_api.g_date
,p_objective_name in varchar2 default hr_api.g_varchar2
,p_valid_from in date default hr_api.g_date
,p_valid_to in date default hr_api.g_date
,p_target_date in date default hr_api.g_date
,p_copied_from_library_id in number default hr_api.g_number
,p_next_review_date in date default hr_api.g_date
,p_group_code in varchar2 default hr_api.g_varchar2
,p_priority_code in varchar2 default hr_api.g_varchar2
,p_appraise_flag in varchar2 default hr_api.g_varchar2
,p_weighting_percent in number default hr_api.g_number
,p_target_value in number default hr_api.g_number
,p_uom_code in varchar2 default hr_api.g_varchar2
,p_measurement_style_code in varchar2 default hr_api.g_varchar2
,p_measure_name in varchar2 default hr_api.g_varchar2
,p_measure_type_code in varchar2 default hr_api.g_varchar2
,p_measure_comments in varchar2 default hr_api.g_varchar2
,p_details in varchar2 default hr_api.g_varchar2
,p_success_criteria in varchar2 default hr_api.g_varchar2
,p_comments in varchar2 default hr_api.g_varchar2
)
is
-- Declare local variables
l_proc varchar2(72) := g_package||'update_scorecard_objective';
hr_objectives_api.update_objective(
p_effective_date => p_effective_date
,p_objective_id => p_objective_id
,p_object_version_number => l_object_version_number
,p_scorecard_id => p_scorecard_id
,p_start_date => p_start_date
,p_name => p_objective_name
,p_target_date => l_target_date
,p_copied_from_library_id => p_copied_from_library_id
,p_next_review_date => l_next_review_date
,p_group_code => p_group_code
,p_priority_code => p_priority_code
,p_appraise_flag => p_appraise_flag
,p_weighting_percent => p_weighting_percent
,p_target_value => p_target_value
,p_uom_code => p_uom_code
,p_measurement_style_code => p_measurement_style_code
,p_measure_name => p_measure_name
,p_measure_type_code => p_measure_type_code
,p_measure_comments => p_measure_comments
,p_detail => p_details
,p_comments => p_comments
,p_success_criteria => p_success_criteria
-- ,p_duplicate_name_warning => l_duplicate_name_warning
,p_weighting_over_100_warning => l_comb_weight_over_100_warning
-- ,p_comb_weight_over_100_warning => l_comb_weight_over_100_warning
,p_weighting_appraisal_warning => l_weighting_appraisal_warning
-- ,p_pc_v_act_mismatch_warning => l_pc_v_act_mismatch_warning
-- ,p_quant_met_not_pc_warning => l_quant_met_not_pc_warning
-- ,p_qual_met_not_pc_warning => l_qual_met_not_pc_warning
);
End update_scorecard_objective;
procedure delete_scorecard_objective
(p_objective_id in number
,p_object_version_number in number
)
is
-- Declare local variables
l_proc varchar2(72) := g_package||'delete_scorecard_objective';
hr_objectives_api.delete_objective(
p_objective_id => p_objective_id
,p_object_version_number => p_object_version_number
);
End delete_scorecard_objective;
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 = enterprise_id
and( pce.enterprise_id = nvl(enterprise_id, -1) or pce.organization_id = nvl(org_id, -1) or pce.job_id = nvl(job_id, -1) or pce.position_id = nvl(pos_id,-1) );
SELECT scorecard_id, assignment_id, person_id, scorecard_name FROM per_personal_scorecards
WHERE scorecard_id = p_scorecard_id;
SELECT assignment_id, business_group_id, grade_id, position_id, job_id, organization_id, supervisor_id, effective_start_date
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
and trunc(sysdate) between effective_start_date and effective_end_date;
SELECT appraisal_template_id, assessment_type_id, objective_asmnt_type_id, business_group_id
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;
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
);
procedure update_appraisal_for_person
(p_effective_date in date
,p_appraisal_id in number
,p_object_version_number in number
,p_appraiser_person_id in number
,p_appraisal_period_start_date in date
,p_appraisal_period_end_date in date
) is
-- Declare local variables
l_proc varchar2(72) := g_package||'update_appraisal_for_person ';
hr_appraisals_api.update_appraisal(
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_period_start_date => p_appraisal_period_start_date
,p_appraisal_period_end_date => p_appraisal_period_end_date
);
End update_appraisal_for_person;
procedure delete_appraisal_for_person
(p_appraisal_id in number
,p_object_version_number in number
) is
-- Declare local variables
l_proc varchar2(72) := g_package||'delete_appraisal_for_person ';
select assessment_id, object_version_number from per_assessments
where appraisal_id = apprl_id;
select competence_element_id, object_version_number from per_competence_elements
where assessment_id = assess_id order by competence_element_id desc ;
hr_competence_element_api.delete_competence_element
(p_validate => false,
p_competence_element_id => assess_comps.competence_element_id,
p_object_version_number => assess_comps.object_version_number);
hr_assessments_api.delete_assessment
(p_validate => false,
p_assessment_id => assess_records.assessment_id,
p_object_version_number => assess_records.object_version_number);
hr_appraisals_api.delete_appraisal
(p_validate => false,
p_appraisal_id => p_appraisal_id
,p_object_version_number => p_object_version_number
);
End delete_appraisal_for_person;
select hr_workflow_item_key_s.nextval
into l_item_key
from dual;
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
elsif (p_status_code = 'UPDATED' or p_status_code = 'FAILED') then
l_status_code := 'RESUBMITTED';
select object_version_number
from per_perf_mgmt_plans
where plan_id = p_plan_id;
select status_code
from per_perf_mgmt_plans
where plan_id = p_plan_id;
update per_perf_mgmt_plans
set status_code = 'FAILED'
where plan_id = p_plan_id;
select PARTICIPANT_ID ,OBJECT_VERSION_NUMBER,PARTICIPATION_TYPE from per_participants
where participation_in_id =p_appraisal_id
and participation_in_table = 'PER_APPRAISALS'
and participation_in_column = 'APPRAISAL_ID'
and PARTICIPATION_TYPE <> 'MAINAP'
and PARTICIPATION_STATUS = 'OPEN';
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 pa.appraisal_id, pa.object_version_number ,pa.appraisal_system_status
from per_appraisals pa,per_personal_scorecards pps
where pa.plan_id = p_plan_id
and APPRAISEE_PERSON_ID =pps.person_id
and pps.scorecard_id =p_scorecard_id;
IF (l_plan_rec.status_code IN ('UPDATED','RESUBMITTED','PUBLISHED')) THEN
--
IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 50); END IF;
(l_plan_rec.status_code in ('UPDATED','RESUBMITTED') AND NOT g_curr_sc_pop_tbl.EXISTS(l_qual_pop_index)))
THEN
-- If objective setting flag is set
IF ((l_plan_rec.include_obj_setting_flag = 'Y') OR
(l_plan_rec.include_appraisals_flag = 'Y'))
THEN
-- Create the scorecard for this assignment
IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 80); END IF;
ELSIF (l_plan_rec.status_code in ('UPDATED','RESUBMITTED') AND g_curr_sc_pop_tbl.EXISTS(l_qual_pop_index)) THEN
-- If objective setting flag is set
IF ((l_plan_rec.include_obj_setting_flag = 'Y') OR
(l_plan_rec.include_appraisals_flag = 'Y'))
THEN
-- Update the scorecard for this assignment
IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 90); END IF;
update_scorecard_for_person(
p_effective_date => l_effective_date
,p_scorecard_id => l_scorecard_id
,p_object_version_number => g_curr_sc_pop_tbl(l_qual_pop_index).object_version_number
,p_scorecard_name => l_plan_rec.plan_name
,p_start_date => l_plan_rec.start_date
,p_end_date => l_plan_rec.end_date
,p_status_code => l_scorecard_status_code
);
update_scorecard_for_person(
p_effective_date => l_effective_date
,p_scorecard_id => l_scorecard_id
,p_object_version_number => g_curr_sc_pop_tbl(l_qual_pop_index).object_version_number
,p_scorecard_name => l_plan_rec.plan_name
,p_start_date => l_plan_rec.start_date
,p_end_date => l_plan_rec.end_date);
update_scorecard_objective(
p_effective_date => l_effective_date
,p_objective_id => g_curr_sc_obj_tbl(l_qual_obj_index).objective_id
,p_object_version_number => g_curr_sc_obj_tbl(l_qual_obj_index).object_version_number
,p_scorecard_id => l_scorecard_id
,p_start_date => l_plan_rec.start_date
,p_end_date => l_plan_rec.end_date
,p_objective_name => g_qual_obj_tbl(l_qual_obj_index).objective_name
,p_valid_from => g_qual_obj_tbl(l_qual_obj_index).valid_from
,p_valid_to => g_qual_obj_tbl(l_qual_obj_index).valid_to
,p_target_date => g_qual_obj_tbl(l_qual_obj_index).target_date
,p_copied_from_library_id => g_qual_obj_tbl(l_qual_obj_index).objective_id
,p_next_review_date => g_qual_obj_tbl(l_qual_obj_index).NEXT_review_date
,p_group_code => g_qual_obj_tbl(l_qual_obj_index).group_code
,p_priority_code => g_qual_obj_tbl(l_qual_obj_index).priority_code
,p_appraise_flag => g_qual_obj_tbl(l_qual_obj_index).appraise_flag
,p_weighting_percent => g_qual_obj_tbl(l_qual_obj_index).weighting_percent
,p_target_value => g_qual_obj_tbl(l_qual_obj_index).target_value
,p_uom_code => g_qual_obj_tbl(l_qual_obj_index).uom_code
,p_measurement_style_code => g_qual_obj_tbl(l_qual_obj_index).measurement_style_code
,p_measure_name => g_qual_obj_tbl(l_qual_obj_index).measure_name
,p_measure_type_code => g_qual_obj_tbl(l_qual_obj_index).measure_type_code
,p_measure_comments => g_qual_obj_tbl(l_qual_obj_index).measure_comments
,p_details => g_qual_obj_tbl(l_qual_obj_index).details
,p_success_criteria => g_qual_obj_tbl(l_qual_obj_index).success_criteria
,p_comments => g_qual_obj_tbl(l_qual_obj_index).comments);
update_scorecard_objective(
p_effective_date => l_effective_date
,p_start_date => l_plan_rec.start_date
,p_objective_id => g_curr_sc_obj_tbl(l_qual_obj_index).objective_id
,p_object_version_number => g_curr_sc_obj_tbl(l_qual_obj_index).object_version_number
,p_end_date => l_effective_date);
update_scorecard_objective(
p_effective_date => l_effective_date
,p_start_date => l_plan_rec.start_date
,p_objective_id => g_curr_sc_obj_tbl(l_curr_sc_obj_index).objective_id
,p_object_version_number => g_curr_sc_obj_tbl(l_curr_sc_obj_index).object_version_number
,p_end_date => l_effective_date);
-- Loop though and delete the objectives for scorecard
FOR obj_rec IN csr_sc_objectives(g_curr_sc_pop_tbl(l_qual_pop_index).scorecard_id)
LOOP
delete_scorecard_objective(p_objective_id => obj_rec.objective_id
,p_object_version_number => obj_rec.object_version_number);
-- Delete the scorecard
delete_scorecard_for_person(g_curr_sc_pop_tbl(l_qual_pop_index).scorecard_id
,g_curr_sc_pop_tbl(l_qual_pop_index).object_version_number);
delete_appraisal_for_person(plan_appraisals.appraisal_id,plan_appraisals.object_version_number);
IF (l_plan_rec.status_code in ('UPDATED','RESUBMITTED') AND NOT g_qual_pop_tbl.EXISTS(l_curr_sc_pop_index))
THEN
-- If objective setting flag is set
--IF (l_plan_rec.include_obj_setting_flag = 'Y') THEN
IF g_dbg THEN op(l_proc, g_DEBUG_LOG, 130); END IF;
-- Loop though and delete the objectives for scorecard
-- 7321947 will not delete any record for non-qualifying scorecards.
/* FOR obj_rec IN csr_sc_objectives(g_curr_sc_pop_tbl(l_curr_sc_pop_index).scorecard_id)
LOOP
delete_scorecard_objective(p_objective_id => obj_rec.objective_id
,p_object_version_number => obj_rec.object_version_number);
UPDATE per_appraisals
SET appraisal_system_status = 'TRANSFER_OUT'
WHERE appraisal_id =l_appr_id;
UPDATE PER_PARTICIPANTS
SET PARTICIPATION_STATUS ='CLOSED'
WHERE PARTICIPANT_ID =i.PARTICIPANT_ID ;
-- Delete the scorecard
/* delete_scorecard_for_person(g_curr_sc_pop_tbl(l_curr_sc_pop_index).scorecard_id
,g_curr_sc_pop_tbl(l_curr_sc_pop_index).object_version_number); */
hr_personal_scorecard_api.update_scorecard
(p_effective_date => trunc(sysdate)
,p_scorecard_id => g_curr_sc_pop_tbl(l_curr_sc_pop_index).scorecard_id
,p_object_version_number => g_curr_sc_pop_tbl(l_curr_sc_pop_index).object_version_number
,p_duplicate_name_warning => l_dummy
,p_status_code => 'TRANSFER_OUT' );
IF (l_plan_rec.status_code in ('UPDATED','RESUBMITTED') AND l_plan_rec.include_obj_setting_flag = 'Y') THEN
--
l_curr_sc_pop_index := g_curr_sc_pop_tbl.FIRST;
update_scorecard_objective(
p_effective_date => l_effective_date
,p_start_date => l_plan_rec.start_date
,p_objective_id => obj_rec.objective_id
,p_object_version_number => obj_rec.object_version_number
,p_end_date => l_effective_date);
update_scorecard_for_person(
p_effective_date => l_effective_date
,p_start_date => l_plan_rec.start_date --Added for 5725110
,p_scorecard_id => g_curr_sc_pop_tbl(l_curr_sc_pop_index).scorecard_id
,p_object_version_number => g_curr_sc_pop_tbl(l_curr_sc_pop_index).object_version_number
,p_end_date => l_effective_date);
(l_plan_rec.status_code in ('UPDATED','RESUBMITTED') AND NOT g_curr_sc_pop_tbl.EXISTS(l_qual_pop_index)))
THEN
--
-- Create Appraisals if flag is set
--
IF (l_plan_rec.include_appraisals_flag = 'Y') THEN
--
l_plan_aprsl_pds_index := g_plan_aprsl_pds_tbl.FIRST;
SELECT
administrator_person_id
,supervisor_id
FROM
PER_PERF_MGMT_PLANS
WHERE plan_id = l_plan_id;
SELECT obj.*
FROM per_objectives obj
,per_personal_scorecards psc
WHERE psc.plan_id = p_prev_plan_id
AND psc.assignment_id = p_assignemnt_id
AND psc.scorecard_id = obj.scorecard_id
AND obj.achievement_date is null
AND obj.appraisal_id is null
AND not exists (select 'X'
from per_personal_scorecards psc1, per_objectives pobj1
where psc1.plan_id = p_curr_plan_id
and psc1.assignment_id = psc.assignment_id
and psc1.scorecard_id = pobj1.scorecard_id
and pobj1.copied_from_objective_id = obj.objective_id);
select
paa.person_id,
paa.ORGANIZATION_ID,
paa.SUPERVISOR_ID
from
per_all_assignments_f paa
where
( trunc(sysdate) between paa.effective_start_date and paa.effective_end_date)
and paa.person_id = p_supervisor_id
and paa.PRIMARY_FLAG = 'Y';
select status_code into l_status_code from per_perf_mgmt_plans where plan_id =p_plan_id;
UPDATE per_perf_mgmt_plans
SET STATUS_CODE = 'INACTIVE'
WHERE plan_id =p_plan_id;
UPDATE per_perf_mgmt_plans
SET STATUS_CODE = 'PUBLISHED'
WHERE plan_id =p_plan_id;
SELECT hr_general.decode_lookup('HR_WPM_SCORECARD_STATUS',status_code)
STATUS
, count(*) COUNT
FROM per_personal_scorecards
WHERE plan_id = p_plan_id
GROUP BY hr_general.decode_lookup('HR_WPM_SCORECARD_STATUS',status_code)
ORDER BY hr_general.decode_lookup('HR_WPM_SCORECARD_STATUS',status_code);
SELECT
hr_general.decode_lookup('APPRAISAL_SYSTEM_STATUS',appraisal_system_status)
STATUS
,pat.name APPRAISAL_TEMPLATE_NAME
,pa.appraisal_period_start_date
,pa.appraisal_period_end_date
, count(*) COUNT
FROM per_appraisals pa
,per_appraisal_templates pat
WHERE pa.plan_id = p_plan_id
AND pa.appraisal_template_id = pat.appraisal_template_id
GROUP BY
hr_general.decode_lookup('APPRAISAL_SYSTEM_STATUS',appraisal_system_status)
,pat.name
,pa.appraisal_period_start_date
,pa.appraisal_period_end_date
ORDER BY
hr_general.decode_lookup('APPRAISAL_SYSTEM_STATUS',appraisal_system_status)
,pat.name
,pa.appraisal_period_start_date
,pa.appraisal_period_end_date;
PROCEDURE delete_scorecards(p_plan_id IN NUMBER) IS
CURSOR csr_sc_ids (p_plan_id IN NUMBER) IS
SELECT scorecard_id
FROM per_personal_scorecards
WHERE plan_id = p_plan_id;
DELETE
FROM hr_api_transaction_steps step
WHERE step.transaction_id IN (SELECT trn.transaction_id
FROM hr_api_transactions trn
WHERE trn.transaction_ref_id =
l_sc_ids(i)
AND trn.transaction_ref_table =
'PER_PERSONAL_SCORECARDS');
DELETE
FROM hr_api_transactions
WHERE transaction_ref_id = l_sc_ids(i)
AND transaction_ref_table = 'PER_PERSONAL_SCORECARDS';
-- Delete all score card objectives
FORALL i in l_sc_ids.FIRST .. l_sc_ids.LAST
DELETE
FROM per_objectives
WHERE scorecard_id = l_sc_ids(i);
-- Delete all score cards now
FORALL i in l_sc_ids.FIRST .. l_sc_ids.LAST
DELETE
FROM per_personal_scorecards
WHERE scorecard_id = l_sc_ids(i);
log_message('Score cards deleted successfully for the plan');
END delete_scorecards;
PROCEDURE delete_appraisals(p_plan_id IN NUMBER) IS
CURSOR csr_appr(p_plan_id IN NUMBER) IS
SELECT appraisal_id
,object_version_number
FROM per_appraisals
WHERE plan_id = p_plan_id;
delete_appraisal_for_person(p_appraisal_id => i.appraisal_id
,p_object_version_number =>
i.object_version_number);
log_message('Appraisals deleted successfully for the plan');
END delete_appraisals;
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
SELECT person_id
FROM per_personal_scorecards
WHERE plan_id = p_plan_id;
delete_scorecards(p_plan_id);
delete_appraisals(p_plan_id);
delete PER_WPM_PLAN_HIERARCHY
where PLAN_ID = p_plan_id;
delete PER_WPM_APPRAISAL_SUMMARY
where PLAN_ID = p_plan_id;
,p_selected_entities_list in varchar2
,p_task_code in varchar2)
RETURN NUMBER IS
l_request_id NUMBER;
IF p_selected_entities_list IS NULL THEN
hr_api.mandatory_arg_error(p_api_name => 'PLAN_ADMIN_ACTIONS_CP'
,p_argument => 'P_SELECTED_ENTITIES_LIST'
,p_argument_value => p_selected_entities_list);
log_message('Invalid task code selected.'||p_task_code);
,argument3 => p_selected_entities_list
,argument4 => p_task_code);
FUNCTION string_to_array(p_selected_entities_list IN VARCHAR2)
RETURN NUMBER IS
i NUMBER;
g_selected_entities.DELETE;
l_sel_list := p_selected_entities_list;
g_selected_entities(i) := SUBSTR(l_sel_list,1,l_pos-1);
g_selected_entities(i) := l_sel_list;
RETURN g_selected_entities.COUNT;
SELECT papf.person_id
,papf.full_name FULL_NAME
,paaf.assignment_id
,paaf.assignment_number
,paaf.position_id
,paaf.organization_id
,paaf.supervisor_id
,paaf.supervisor_assignment_id
,suppapf.full_name SUPERVISOR_NAME
FROM per_people_f papf
,per_assignments_f paaf
,per_all_people_f suppapf
WHERE paaf.assignment_id = p_assignment_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND p_effective_date BETWEEN paaf.effective_start_date AND
paaf.effective_end_date
AND paaf.supervisor_id = suppapf.person_id(+)
AND p_effective_date BETWEEN suppapf.effective_start_date(+) AND
papf.effective_end_date(+);
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 'x'
FROM per_appraisals
WHERE plan_id = p_plan_id);
SELECT 'Y'
FROM per_personal_scorecards
WHERE plan_id = p_plan_id
AND assignment_id = p_assignment_id;
g_qual_pop_tbl.DELETE;
FOR i in g_selected_entities.FIRST .. g_selected_entities.LAST
LOOP
hr_utility.set_location(l_proc,14);
OPEN csr_asg_enrolled(g_selected_entities(i),p_plan_id);
FOR j IN csr_person_dtls(g_selected_entities(i),p_effective_date)
LOOP
l_chk_exists := chk_assignment_in_population(j.assignment_id);
SELECT pa.appraisee_person_id
,pe.event_id
,pe.object_version_number EVENT_OVN
,ppr.performance_review_id
,ppr.object_version_number REVIEW_OVN
FROM per_appraisals pa
,per_events pe
,per_performance_reviews ppr
WHERE pa.appraisal_id = p_appraisal_id
AND pa.event_id = pe.event_id
AND pe.event_id = ppr.event_id;
SELECT po.objective_id
,po.object_version_number
FROM per_objectives po
WHERE appraisal_id = p_appraisal_id;
SELECT po.objective_id
,po.object_version_number
FROM per_objectives po
WHERE scorecard_id = p_sc_id
AND copied_from_objective_id = p_objective_id;
SELECT scorecard_id
FROM per_personal_scorecards sc
WHERE person_id = p_appraisee_person_id
AND plan_id = p_plan_id;
hr_perf_review_api.delete_perf_review
(
p_performance_review_id => i.performance_review_id
,p_object_version_number => i.review_ovn
);
per_events_api.delete_event
(
p_event_id => i.event_id
,p_object_version_number => i.event_ovn
);
hr_objectives_api.update_objective
(p_validate => false
,p_effective_date => p_effective_date
,p_objective_id => j.objective_id
,p_object_version_number => l_object_version_number
,p_scorecard_id => l_sc_id
,p_weighting_over_100_warning => l_weighting_over_100_warning
,p_weighting_appraisal_warning => l_weighting_appraisal_warning
,p_appraise_flag => 'Y'
);
hr_objectives_api.delete_objective
(p_validate => false
,p_objective_id => i.objective_id
,p_object_version_number => i.object_version_number);
SELECT pa.APPRAISAL_ID
,pa.main_appraiser_id
,pa.appraisee_person_id
,papf.full_name "MAIN_APPRAISER_NAME"
,papf1.full_name "APPRAISEE_NAME"
,to_char(appraisal_period_start_date,'DD-MON-YYYY')
appraisal_period_start_date
,to_char(appraisal_period_end_date,'DD-MON-YYYY')
appraisal_period_end_date
,to_char(appraisal_date,'DD-MON-YYYY') appraisal_date
,pa.status
,pa.assignment_id
FROM per_appraisals pa
,per_people_f papf
,per_people_f papf1
WHERE pa.appraisal_id = p_appraisal_id
AND pa.plan_id = p_plan_id
AND pa.main_appraiser_id = papf.person_id
AND p_effective_date BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND pa.appraisee_person_id = papf1.person_id
AND p_effective_date BETWEEN papf1.effective_start_date AND
papf1.effective_end_date;
FOR i in g_selected_entities.FIRST .. g_selected_entities.LAST
LOOP
BEGIN
FOR j IN csr_appraisal_dtls(g_selected_entities(i),p_plan_id,p_effective_date)
LOOP
log_message('Opening appraisal for: '||j.appraisee_name);
revert_appraisal_details(p_appraisal_id => g_selected_entities(i)
,p_plan_id => p_plan_id
,p_effective_date => p_effective_date);
UPDATE per_appraisals pa
SET system_params = SUBSTR(system_params,1,instr(pa.system_params,'pItemKey=')-2),
appraisal_system_status = 'ONGOING' ,
event_id = NULL
WHERE pa.appraisal_id = g_selected_entities(i);
DELETE
FROM hr_api_transaction_steps step
WHERE step.transaction_id IN (SELECT trn.transaction_id
FROM hr_api_transactions trn
WHERE trn.transaction_ref_id =
p_scorecard_id
AND trn.transaction_ref_table =
'PER_PERSONAL_SCORECARDS');
DELETE
FROM hr_api_transactions
WHERE transaction_ref_id = p_scorecard_id
AND transaction_ref_table = 'PER_PERSONAL_SCORECARDS';
DELETE
FROM per_objectives
WHERE scorecard_id = p_scorecard_id;
SELECT pc.scorecard_id
,pc.person_id
,papf.full_name scorecard_owner
,pc.status_code
FROM per_personal_scorecards pc
,per_people_f papf
WHERE pc.scorecard_id = p_sc_id
AND pc.plan_id = p_plan_id
AND pc.person_id = papf.person_id
AND p_effective_date BETWEEN papf.effective_start_date AND
papf.effective_end_date;
SELECT appraisal_id,object_version_number
FROM per_appraisals
WHERE plan_id = p_plan_id
AND appraisee_person_id = p_person_id;
FOR i in g_selected_entities.FIRST .. g_selected_entities.LAST
LOOP
BEGIN
FOR j IN csr_sc_dtls(g_selected_entities(i),p_plan_id,p_effective_date)
LOOP
log_message('Removing scorecard for: '||j.scorecard_owner);
remove_scorecard_details(g_selected_entities(i));
DELETE per_personal_scorecards
WHERE scorecard_id = g_selected_entities(i);
delete_appraisal_for_person(k.appraisal_id,k.object_version_number);
DELETE
FROM per_wpm_plan_hierarchy
WHERE employee_person_id = j.person_id;
SELECT pc.scorecard_id
,pc.person_id
,papf.full_name scorecard_owner
,pc.status_code
,papf.business_group_id
,paaf.organization_id
,paaf.position_id
,paaf.job_id
,pc.assignment_id
,paaf.supervisor_id -- Bug7567079
FROM per_personal_scorecards pc
,per_people_f papf
,per_assignments_f paaf
WHERE pc.scorecard_id = p_sc_id
AND pc.plan_id = p_plan_id
AND pc.assignment_id = paaf.assignment_id
AND pc.person_id = papf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date AND
paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date AND
papf.effective_end_date;
SELECT 'Y'
FROM per_appraisal_periods
WHERE plan_id = p_plan_id
AND p_effective_date BETWEEN task_start_date AND task_end_date;
FOR i in g_selected_entities.FIRST .. g_selected_entities.LAST
LOOP
FOR j IN csr_sc_dtls(g_selected_entities(i),p_plan_id,p_effective_date)
LOOP
BEGIN
log_message('Reopening scorecard for: '||j.scorecard_owner);
UPDATE per_personal_scorecards
SET status_code = l_scorecard_status_code
WHERE scorecard_id = g_selected_entities(i);
SELECT pc.scorecard_id
,pc.person_id
,papf.full_name scorecard_owner
,pc.status_code
,papf.business_group_id
,paaf.organization_id
,paaf.position_id
,paaf.job_id
,pc.assignment_id
,paaf.supervisor_id -- Bug7567079
FROM per_personal_scorecards pc
,per_people_f papf
,per_assignments_f paaf
WHERE pc.scorecard_id = p_sc_id
AND pc.plan_id = p_plan_id
AND pc.assignment_id = paaf.assignment_id
AND pc.person_id = papf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date AND
paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date AND
papf.effective_end_date;
SELECT 'Y'
FROM per_objectives
WHERE scorecard_id = p_scorecard_id
AND copied_from_library_id = p_library_id;
FOR i in g_selected_entities.FIRST .. g_selected_entities.LAST
LOOP
l_scorecard_id :=g_selected_entities(i);
log_message('select scorecard_id rec:'||l_sc_rec.scorecard_owner);
UPDATE per_personal_scorecards
SET status_code = l_scorecard_status_code
WHERE scorecard_id = l_scorecard_id;
,p_selected_entities_list in varchar2
,p_task_code in varchar2) IS
l_proc VARCHAR2(72) := g_package||'plan_admin_actions';
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
g_plan_dtls.delete;
IF p_selected_entities_list IS NULL THEN
hr_api.mandatory_arg_error(p_api_name => 'PLAN_ADMIN_ACTIONS_CP'
,p_argument => 'P_SELECTED_ENTITIES_LIST'
,p_argument_value => p_selected_entities_list);
log_message('Invalid task code selected.'||p_task_code);
l_person_count := string_to_array(p_selected_entities_list =>
p_selected_entities_list);
log_message('Number of selected persons: '||l_person_count);
log_message('Invalid task code selected.'||p_task_code);
errbuf := 'Errors occured processing the selected persons. Pl. check the
concurrent log for details.';
select wf.name role_name
from wf_roles wf
where wf.orig_system = 'PER'
and wf.orig_system_id = person_id;
select plan_name,administrator_person_id
from per_perf_mgmt_plans
where plan_id = p_plan_id;
select supervisor_id from per_all_assignments_f
where person_id=p_person_id
and trunc(sysdate) between effective_start_date and effective_end_date
and primary_flag = 'Y';