[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 hierarchy_type_code, supervisor_id, supervisor_assignment_id, assignment_types_code
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 'x'
FROM per_all_people_f ppf, per_periods_of_service pps
WHERE ppf.person_id = p_supervisor_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND TRUNC (SYSDATE) BETWEEN pps.date_start AND NVL (pps.actual_termination_date, TRUNC (SYSDATE)));
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 'x'
FROM per_all_assignments_f paaf, per_periods_of_service pps
WHERE paaf.assignment_id = p_supervisor_asg_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.period_of_service_id = pps.period_of_service_id
AND TRUNC (SYSDATE) BETWEEN pps.date_start AND NVL (pps.actual_termination_date, TRUNC (SYSDATE)));
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 'x'
FROM per_all_people_f ppf, per_periods_of_placement ppp
WHERE ppf.person_id = p_supervisor_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = ppp.person_id
AND TRUNC (SYSDATE) BETWEEN ppp.date_start AND NVL (ppp.actual_termination_date, TRUNC (SYSDATE)));
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 'x'
FROM per_all_assignments_f paaf, per_periods_of_placement ppp
WHERE paaf.assignment_id = p_supervisor_asg_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.person_id = ppp.person_id
AND paaf.period_of_placement_date_start = ppp.date_start
AND TRUNC (SYSDATE) BETWEEN ppp.date_start AND NVL (ppp.actual_termination_date, TRUNC (SYSDATE)));
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);
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;
op (' Selected Population is not in Appraisal Template BG ', g_regular_log, 90);
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
-- added 23-Jun-2009 schowdhu
AND TRUNC (SYSDATE) BETWEEN elig.effective_start_date AND elig.effective_end_date;
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;
-- |----------------------< update_scorecard_for_person >---------------------|
-- ----------------------------------------------------------------------------
-- {Start Of Comments}
--
-- Description:
-- Updates the given scorecard when plan is republished.
--
-- Prerequisites:
-- None.
--
-- In Arguments:
--
--
-- Post Success:
-- Processing continues if scorecard is updated.
--
-- Post Failure:
-- An application error is raised if scorecard is not updated.
--
-- Access Status:
-- Internal Only.
--
-- {End Of Comments}
-- ----------------------------------------------------------------------------
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,
p_supervisor_id IN NUMBER DEFAULT NULL,
p_supervisor_assignment_id IN NUMBER DEFAULT NULL
)
IS
-- Declare local variables
l_proc VARCHAR2 (72) := g_package || 'update_scorecard_for_person';
-- Call update_scorecard
--
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,
p_supervisor_id => p_supervisor_id,
p_supervisor_assignment_id => p_supervisor_assignment_id
);
END update_scorecard_for_person;
-- |----------------------< delete_scorecard_for_person >---------------------|
-- ----------------------------------------------------------------------------
-- {Start Of Comments}
--
-- Description:
-- Delete the given scorecard.
--
-- Prerequisites:
-- None.
--
-- In Arguments:
--
--
-- Post Success:
-- Processing continues if scorecard is deleted.
--
-- Post Failure:
-- An application error is raised if scorecard is not deleted.
--
-- Access Status:
-- Internal Only.
--
-- {End Of Comments}
-- ----------------------------------------------------------------------------
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';
-- Call update_scorecard
--
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;
-- |----------------------< update_scorecard_objective >----------------------|
-- ----------------------------------------------------------------------------
-- {Start Of Comments}
--
-- Description:
-- Updates objective for a given scorecard when plan is republished.
--
-- Prerequisites:
-- None.
--
-- In Arguments:
--
--
-- Post Success:
-- Processing continues if objective is updated.
--
-- Post Failure:
-- An application error is raised if objective is not updated.
--
-- Access Status:
-- Internal Only.
--
-- {End Of Comments}
-- ----------------------------------------------------------------------------
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';
-- Call update_objective
--
/* KGOWRIPE: removing the update API call for bug#10289224. This is to take care of the OVN getting
incremented when the plan is republished for Library objectives.
*/
/*
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
);
UPDATE per_objectives
SET scorecard_id = decode(p_scorecard_id,hr_api.g_number,per_obj_shd.g_old_rec.scorecard_id,p_scorecard_id)
,start_date = decode(p_start_date,hr_api.g_date,per_obj_shd.g_old_rec.start_date,p_start_date)
,name = decode(p_objective_name,hr_api.g_varchar2,per_obj_shd.g_old_rec.name,p_objective_name)
,target_date = l_target_date
,copied_from_library_id = decode(p_copied_from_library_id,hr_api.g_number,per_obj_shd.g_old_rec.copied_from_library_id,p_copied_from_library_id)
,next_review_date = l_next_review_date
,group_code = decode(p_group_code,hr_api.g_varchar2,per_obj_shd.g_old_rec.group_code,p_group_code)
,priority_code = decode(p_priority_code,hr_api.g_varchar2,per_obj_shd.g_old_rec.priority_code,p_priority_code)
,appraise_flag = decode(p_appraise_flag,hr_api.g_varchar2,per_obj_shd.g_old_rec.appraise_flag,p_appraise_flag)
,weighting_percent = decode(p_weighting_percent,hr_api.g_number,per_obj_shd.g_old_rec.weighting_percent,p_weighting_percent)
,target_value = decode(p_target_value,hr_api.g_number,per_obj_shd.g_old_rec.target_value,p_target_value)
,uom_code = decode(p_uom_code,hr_api.g_varchar2,per_obj_shd.g_old_rec.uom_code,p_uom_code)
,measurement_style_code = decode(p_measurement_style_code,hr_api.g_varchar2,per_obj_shd.g_old_rec.measurement_style_code,p_measurement_style_code)
,measure_name = decode(p_measure_name,hr_api.g_varchar2,per_obj_shd.g_old_rec.measure_name,p_measure_name)
,measure_type_code = decode(p_measure_type_code,hr_api.g_varchar2,per_obj_shd.g_old_rec.measure_type_code,p_measure_type_code)
,measure_comments = decode(p_measure_comments,hr_api.g_varchar2,per_obj_shd.g_old_rec.measure_comments,p_measure_comments)
,detail = decode(p_details,hr_api.g_varchar2,per_obj_shd.g_old_rec.detail,p_details)
,comments = decode(p_comments,hr_api.g_varchar2,per_obj_shd.g_old_rec.comments,p_comments)
,success_criteria = decode(p_success_criteria,hr_api.g_varchar2,per_obj_shd.g_old_rec.success_criteria,p_success_criteria)
WHERE objective_id = p_objective_id;
END update_scorecard_objective;
-- |----------------------< delete_scorecard_objective >---------------------|
-- ----------------------------------------------------------------------------
-- {Start Of Comments}
--
-- Description:
-- Deletes a given objective.
--
-- Prerequisites:
-- None.
--
-- In Arguments:
--
--
-- Post Success:
-- Processing continues if objective is deleted.
--
-- Post Failure:
-- An application error is raised if objective is not deleted.
--
-- Access Status:
-- Internal Only.
--
-- {End Of Comments}
-- ----------------------------------------------------------------------------
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';
-- Call delete_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;
op ('l_selected_menu = ' || l_selected_menu, g_debug_log);
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
);
-- to be added a message to identify update objective error
IF g_dbg
THEN
op (SQLERRM, g_regular_log);
-- |----------------------< update_appraisal_for_person >---------------------|
-- ----------------------------------------------------------------------------
-- {Start Of Comments}
--
-- Description:
-- Updates appraisal for a given person when plan is published.
--
-- Prerequisites:
-- None.
--
-- In Arguments:
--
--
-- Post Success:
-- Processing continues if appraisal is updated.
--
-- Post Failure:
-- An application error is raised if appraisal is not updated.
--
-- Access Status:
-- Internal Only.
--
-- {End Of Comments}
-- ----------------------------------------------------------------------------
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 ';
-- Call update_appraisal
--
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;
-- |----------------------< delete_appraisal_for_person >---------------------|
-- ----------------------------------------------------------------------------
-- {Start Of Comments}
--
-- Description:
-- Deletes appraisal for a given person when plan is published.
--
-- Prerequisites:
-- None.
--
-- In Arguments:
--
--
-- Post Success:
-- Processing continues if appraisal is deleted.
--
-- Post Failure:
-- An application error is raised if appraisal is not deleted.
--
-- Access Status:
-- Internal Only.
--
-- {End Of Comments}
-- ----------------------------------------------------------------------------
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;
-- Call delete_appraisal
--
FOR assess_records IN get_assessm_for_apprl (p_appraisal_id)
LOOP
FOR assess_comps IN get_competences (assess_records.assessment_id)
LOOP
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 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM per_personal_scorecards
WHERE plan_id = p_plan_id AND assignment_id = p_assignment_id);
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
-- Update the status of plan
IF (p_status_code = 'DRAFT')
THEN
l_status_code := 'SUBMITTED';
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;
SELECT per_wpm_batch_actions_s.NEXTVAL
INTO l_wpm_batch_action_id
FROM DUAL;
INSERT INTO per_wpm_batch_actions
(wpm_batch_action_id, conc_request_id, conc_program_name, plan_id, appraisal_period_id, status, start_date,
end_date
)
VALUES (l_wpm_batch_action_id, fnd_global.conc_request_id, 'PERPLNPUB', p_plan_id, NULL, 'PENDING', SYSDATE,
--p_effective_date, -- trunc(sysdate)
NULL
);
-- get the new ovn number to pass it to publish_plan function as update is called before this once
OPEN csr_plan_ovn;
hr_wpm_mass_apr_push.g_wpm_person_actions.DELETE;
UPDATE per_wpm_batch_actions
SET end_date = SYSDATE,
status = DECODE (g_retcode, 0, 'SUCCESS', 'WARNING')
WHERE wpm_batch_action_id = hr_wpm_mass_apr_push.l_current_wpm_batch_action_id;
UPDATE per_wpm_batch_actions
SET status = 'ERROR',
end_date = SYSDATE
WHERE wpm_batch_action_id = hr_wpm_mass_apr_push.l_current_wpm_batch_action_id;
-- update status of the plan to 'Failed'
--
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 cr.request_id, cr.phase_code
FROM fnd_concurrent_programs cp, fnd_concurrent_requests cr
WHERE cp.concurrent_program_name = 'WPMAPRPUSH'
AND cp.application_id = 800
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.argument2 = TO_CHAR (plan_id)
AND cr.argument3 = TO_CHAR (appraisal_period_id)
AND cr.actual_start_date IS NULL
AND cr.actual_completion_date IS NULL
AND cr.phase_code = 'P';
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;
SELECT elig.elig_obj_id
FROM ben_elig_obj_f elig
WHERE elig.table_name = 'PER_PERF_MGMT_PLANS'
AND elig.column_name = 'PLAN_ID'
AND elig.COLUMN_VALUE = p_plan_id
AND TRUNC (SYSDATE) BETWEEN elig.effective_start_date AND elig.effective_end_date;
SELECT full_name
FROM per_all_people_f ppf
WHERE ppf.person_id = p_person_id AND l_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
IF (l_plan_rec.status_code IN ('UPDATED', 'RESUBMITTED', 'PUBLISHED'))
THEN
--
IF g_dbg
THEN
op (l_proc, g_debug_log, 50);
-- Loop through plan population to create/update/delete scorecards, objectives and appraisal
--
l_qual_pop_index := g_qual_pop_tbl.FIRST;
OR (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
--Check for the elibility of the employee/assignment
--23-Jun-2009 schowdhu Eligibility Profile Enhc.- start
OPEN get_elig_obj_id_for_person (l_plan_rec.plan_id);
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);
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
);
-- if it's newly qualified objective then create else update
--
IF (NOT g_curr_sc_obj_tbl.EXISTS (g_qual_obj_tbl (l_qual_obj_index).objective_id))
THEN
-- Create the objective
create_scorecard_objective
(p_effective_date => l_effective_date,
p_scorecard_id => l_scorecard_id,
p_business_group_id => g_qual_pop_tbl (l_qual_pop_index).business_group_id,
p_person_id => g_qual_pop_tbl (l_qual_pop_index).person_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
);
IF (NVL (l_plan_rec.update_library_objectives, 'N') = 'Y')
THEN -- 8740021 bug fix
-- Update the objective
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
);
-- scorecard update, hence commenting it
/* IF (l_plan_rec.copy_past_objectives_flag = 'Y' AND l_plan_rec.previous_plan_id is not NULL)
THEN
--
copy_past_objectives
(p_effective_date => l_effective_date
,p_business_group_id => g_qual_pop_tbl(l_qual_pop_index).business_group_id
,p_person_id => g_qual_pop_tbl(l_qual_pop_index).person_id
,p_scorecard_id => l_scorecard_id
,p_start_date => l_plan_rec.start_date
,p_end_date => l_plan_rec.end_date
,p_target_date => null
,p_assignemnt_id => g_qual_pop_tbl(l_qual_pop_index).assignment_id
,p_prev_plan_id => l_plan_rec.previous_plan_id
,p_curr_plan_id => l_plan_rec.plan_id);
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);
--- DELETE the non qualifying scorecards and the appraisals( for terminated employees) changes for 6460457
l_curr_sc_pop_index := g_curr_sc_pop_tbl.FIRST;
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);
-- 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);
--- delete the appraisals for the person
IF (l_plan_rec.include_appraisals_flag = 'Y')
THEN
-- FOR plan_appraisals IN csr_plan_appraisals(p_plan_id)
OPEN csr_find_appr_for_scorecard (p_plan_id, g_curr_sc_pop_tbl (l_curr_sc_pop_index).scorecard_id);
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;
-- we are doing a direct update as update API will not work for terminated and traansfer employees
-- update it to transfer out so as to not show the details anywhere
END IF;
-- 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'
);
--- DELETE logic ends( replaced by 7321947 changes)
--============================================================================================
IF g_dbg
THEN
op (l_proc, g_debug_log, 140);
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);
-- Update the plan status
--
l_object_version_number := l_plan_rec.object_version_number;
OR (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.organization_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_person_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;
--- logic for status code updated to published if already inactive
ELSIF (l_status_code = 'INACTIVE')
THEN
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 any transactions from HR_API_Transactions and transaction steps
FORALL i IN l_sc_ids.FIRST .. l_sc_ids.LAST
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;
-- hr_appraisals_api.delete_appraisal
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 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM per_personal_scorecards
WHERE plan_id = p_plan_id);
SELECT person_id
FROM per_personal_scorecards
WHERE plan_id = p_plan_id;
--Update the plan status to SUBMITTED so that no further updates can happen to the plan
l_object_version_number := l_plan_rec.object_version_number;
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
);
argument3 => p_selected_entities_list,
argument4 => p_task_code
);
FUNCTION string_to_array (p_selected_entities_list IN VARCHAR2)
RETURN NUMBER
IS
i NUMBER;
-- Delete existing pl/sql table, if any.
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 elig.elig_obj_id
FROM ben_elig_obj_f elig
WHERE elig.table_name = 'PER_APPRAISAL_PERIODS'
AND elig.column_name = 'APPRAISAL_PERIOD_ID'
AND elig.COLUMN_VALUE = p_appraisal_period_id
AND TRUNC (SYSDATE) BETWEEN elig.effective_start_date AND elig.effective_end_date;
-- Loop through plan population to create/update/delete scorecards, objectives and appraisal
--
l_qual_pop_index := p_assignment_id;
-- Now update the plan hierarchy for this person
g_target_sc_id := l_scorecard_id; --- set the sc id for the calling routine
-- added the above line to update hierarchy for this sc.
END IF;
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(+)
--8632500 Modified
AND p_effective_date BETWEEN suppapf.effective_start_date(+) AND suppapf.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 AND status_code <> 'TRANSFER_OUT';
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;
-- Delete the event that is created and the performance review row as well
FOR i IN csr_event_dtls (p_appraisal_id)
LOOP
l_appraisee_person_id := i.appraisee_person_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);
-- Update the scorecard_id back in all the appraisal objectives
-- and delete the duplicated objectives from the sc
IF l_sc_id IS NOT NULL
THEN
FOR j IN csr_appr_objs (p_appraisal_id)
LOOP
l_object_version_number := j.object_version_number;
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'
);
--now delete the duplicated objective from the SC
FOR i IN csr_sc_obj (l_sc_id, j.objective_id)
LOOP
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;
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_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));
-- UPdate the status of score card to DELETED
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);
-- No need remove details. reevaluate objectives eligibility and insert any not existing ones
-- remove_scorecard_details(l_scorecard_id);
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;
SELECT pps.scorecard_id, pps.person_id, pps.assignment_id, NVL (pps.supervisor_id, paf.supervisor_id) supervisor_id,
NVL (pps.supervisor_assignment_id, paf.supervisor_assignment_id) supervisor_assignment_id,
pps.object_version_number, hr_general.decode_person_name (pps.person_id) worker_name
FROM per_personal_scorecards pps, per_assignments_f paf
WHERE pps.plan_id = p_plan_id
AND pps.assignment_id = paf.assignment_id
AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
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 ( ( g_plan_dtls (1).assignment_types_code IN ('E', 'C')
AND asg.assignment_type = g_plan_dtls (1).assignment_types_code
)
OR (g_plan_dtls (1).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 ( (g_plan_dtls (1).primary_asg_only_flag = 'N')
OR g_plan_dtls (1).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 (g_plan_dtls (1).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 (g_plan_dtls (1).hierarchy_levels, LEVEL) + 1
START WITH o.person_id = p_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 ( ( g_plan_dtls (1).assignment_types_code IN ('E', 'C')
AND asg.assignment_type = g_plan_dtls (1).assignment_types_code
)
OR (g_plan_dtls (1).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 (g_plan_dtls (1).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 (g_plan_dtls (1).hierarchy_levels, LEVEL) + 1
START WITH o.assignment_id = p_supervisor_assignment_id;
l_temp_pop_tbl.DELETE;
l_temp_prop_pop_tbl.DELETE;
-- Now update the plan with new supervisor id
IF g_plan_dtls (1).hierarchy_type_code = 'SUP'
THEN
UPDATE per_perf_mgmt_plans
SET supervisor_id = p_supervisor_id
WHERE plan_id = p_plan_id;
log_message ('Update plan sup to:' || p_supervisor_id);
UPDATE per_perf_mgmt_plans
SET supervisor_assignment_id = p_supervisor_assignment_id
WHERE plan_id = p_plan_id;
log_message ('Update plan supasg to:' || p_supervisor_assignment_id);
UPDATE per_personal_scorecards
SET status_code = 'TRANSFER_OUT'
WHERE scorecard_id = l_curr_pop (l_asg_index).scorecard_id;
SELECT *
FROM per_objectives
WHERE scorecard_id = p_sc_id;
SELECT 'Y'
INTO l_exists
FROM DUAL
WHERE EXISTS (Select 'x'
FROM per_objectives
WHERE scorecard_id = p_target_sc_id
AND (copied_from_library_id = i.copied_from_library_id
OR copied_from_objective_id = i.objective_id));
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 'x'
FROM per_personal_scorecards sc, per_perf_mgmt_plans pl, per_appraisal_periods ap
WHERE sc.scorecard_id = p_scorecard_id
AND sc.plan_id = pl.plan_id
AND pl.plan_id = ap.plan_id
AND p_eff_date BETWEEN ap.task_start_date AND ap.task_end_date);
SELECT *
FROM per_personal_scorecards
WHERE scorecard_id = p_sc_id;
SELECT supervisor_id
FROM per_personal_scorecards
WHERE scorecard_id = l_target_sc_id;
SELECT hr_person_name.get_list_name(ppf.global_name, ppf.local_name) full_name
FROM per_all_people_f ppf, per_personal_scorecards pps
WHERE ppf.person_id = pps.person_id
AND pps.scorecard_id = p_sc_id
AND TRUNC(SYSDATE) between ppf.effective_start_date and ppf.effective_end_date;
SELECT scorecard_id
FROM per_personal_scorecards
WHERE plan_id = p_plan_id AND assignment_id = p_assignment_id;
SELECT appraisal_id
FROM per_appraisals
WHERE plan_id = p_plan_id AND appraisee_person_id = p_person_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;
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 'Y'
FROM dual
WHERE EXISTS (SELECT 'X'
FROM per_appraisals
WHERE plan_id = p_plan_id
AND appraisal_system_status <> 'DELETED');
FOR i IN g_selected_entities.FIRST .. g_selected_entities.LAST
LOOP
BEGIN
g_target_sc_id := -1;
OPEN csr_chk_appr_in_progress (g_selected_entities (i), p_effective_date);
OPEN csr_sc_dtls (g_selected_entities (i));
OPEN csr_person_full_name (g_selected_entities (i));
log_message ('Invalid Scorecard: ' || g_selected_entities (i) || '.Cannot Transfer.');
log_message('Selected Assignment:'||l_sc_rec.assignment_id||' doesn''t qualify for the target plan. Cannot Transfer.' );
-- Now update the current scorecard and appraisals (if any) to TRANSFER_OUT
--
--- delete the appraisals for the person
IF (g_plan_dtls (1).include_appraisals_flag = 'Y')
THEN
-- FOR plan_appraisals IN csr_plan_appraisals(p_plan_id)
OPEN csr_find_appr_for_scorecard (l_sc_rec.plan_id, l_sc_rec.scorecard_id);
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;
-- we are doing a direct update as update API will not work for terminated and traansfer employees
-- update it to transfer out so as to not show the details anywhere
END IF;
hr_personal_scorecard_api.update_scorecard
(p_effective_date => TRUNC (SYSDATE),
p_scorecard_id =>l_sc_rec.scorecard_id,
p_object_version_number => l_sc_rec.object_version_number,
p_duplicate_name_warning => l_dummy,
p_status_code => 'TRANSFER_OUT'
);
log_message ('Error while transferring scorecard: ' || g_selected_entities (i));
log_message ('Invalid Sub task code selected for transfer action:' || p_sub_taskcode);
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.' || l_task_code);
l_supervisor_id := SUBSTR (p_selected_entities_list, 1, INSTR (p_selected_entities_list, ',') - 1);
l_supervisor_assignment_id := SUBSTR (p_selected_entities_list, 1, INSTR (p_selected_entities_list, ',') - 1);
l_commit_mode := SUBSTR (p_selected_entities_list, INSTR (p_selected_entities_list, ',') + 1, 1);
l_sup_rollback_req_id := SUBSTR (p_selected_entities_list, INSTR (p_selected_entities_list, ',', 1, 2) + 1);
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.' || l_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 full_name
FROM per_all_people_f
WHERE person_id = p_person_id AND TRUNC (SYSDATE) BETWEEN effective_start_date AND effective_end_date;
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 assignment_id = p_assignment_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'X'
FROM per_personal_scorecards
WHERE plan_id = p_plan_id AND assignment_id = p_assignment_id);
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM per_personal_scorecards
WHERE plan_id = p_plan_id);
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
-- Update the status of plan
IF (p_status_code = 'DRAFT')
THEN
l_status_code := 'SUBMITTED';
ELSIF (p_status_code = 'UPDATED' OR p_status_code = 'FAILED')
THEN
l_status_code := 'RESUBMITTED';
SELECT pc.scorecard_id, pc.person_id, pc.object_version_number, 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;
SELECT objective_id, object_version_number
FROM per_objectives po
WHERE po.scorecard_id = p_scorecard_id AND po.copied_from_library_id = p_objective_id;
log_message ('select scorecard_id rec:' || l_sc_rec.scorecard_owner);
update_scorecard_objective
(p_effective_date => l_obj_date,
p_objective_id => l_objective_id,
p_scorecard_id => l_scorecard_id,
p_object_version_number => l_obj_ovn,
p_start_date => g_plan_dtls (1).start_date,
p_end_date => g_plan_dtls (1).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
);
SELECT pc.scorecard_id, pc.plan_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.assignment_id = paaf.assignment_id
AND pc.person_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
SELECT pc.scorecard_id, pc.plan_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.assignment_id = paaf.assignment_id
AND pc.person_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
UPDATE per_personal_scorecards
SET status_code = l_scorecard_status_code
WHERE scorecard_id = g_curr_sc_pop_tbl (l_curr_sc_pop_index).scorecard_id;
SELECT pc.scorecard_id, pc.plan_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.assignment_id = paaf.assignment_id
AND pc.person_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
SELECT pc.scorecard_id, pc.plan_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.assignment_id = paaf.assignment_id
AND pc.person_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date;
SELECT paf.supervisor_id, paf.supervisor_assignment_id, sc.object_version_number
FROM per_personal_scorecards sc, per_assignments_f paf
WHERE sc.scorecard_id = p_sc_id
AND sc.assignment_id = paf.assignment_id
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date;
hr_personal_scorecard_api.update_scorecard (p_effective_date => TRUNC (SYSDATE),
p_scorecard_id => p_scorecard_id,
p_object_version_number => l_sc_sup.object_version_number,
p_supervisor_id => l_sc_sup.supervisor_id,
p_supervisor_assignment_id => l_sc_sup.supervisor_assignment_id,
p_duplicate_name_warning => l_dup_warn
);
SELECT elig.elig_obj_id
FROM ben_elig_obj_f elig
WHERE elig.table_name = 'PER_PERF_MGMT_PLANS'
AND elig.column_name = 'PLAN_ID'
AND elig.COLUMN_VALUE = p_plan_id
AND TRUNC (SYSDATE) BETWEEN elig.effective_start_date AND elig.effective_end_date;
SELECT full_name
FROM per_all_people_f ppf
WHERE ppf.person_id = p_person_id AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
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;
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';
IF (g_plan_dtls (1).status_code IN ('UPDATED', 'RESUBMITTED') AND NOT g_qual_pop_tbl.EXISTS (l_curr_sc_pop_index))
THEN
--- delete the appraisals for the person
IF (g_plan_dtls (1).include_appraisals_flag = 'Y')
THEN
-- FOR plan_appraisals IN csr_plan_appraisals(p_plan_id)
OPEN csr_find_appr_for_scorecard (p_plan_id, g_curr_sc_pop_tbl (l_curr_sc_pop_index).scorecard_id);
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;
-- we are doing a direct update as update API will not work for terminated and traansfer employees
-- update it to transfer out so as to not show the details anywhere
END IF;
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'
);
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;
SELECT elig.elig_obj_id
FROM ben_elig_obj_f elig
WHERE elig.table_name = 'PER_PERF_MGMT_PLANS'
AND elig.column_name = 'PLAN_ID'
AND elig.COLUMN_VALUE = p_plan_id
AND TRUNC (SYSDATE) BETWEEN elig.effective_start_date AND elig.effective_end_date;
SELECT full_name
FROM per_all_people_f ppf
WHERE ppf.person_id = p_person_id AND l_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
hr_utility.set_location('Delete Existing qualified plan Population.', 777);
g_qual_pop_tbl.DELETE;
SELECT *
FROM per_perf_mgmt_plans
WHERE plan_id = p_plan_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'x'
FROM per_personal_scorecards
WHERE plan_id = p_plan_id);
SELECT per_wpm_batch_actions_s.NEXTVAL
INTO l_wpm_batch_action_id
FROM DUAL;
INSERT INTO per_wpm_batch_actions
(wpm_batch_action_id, conc_request_id, conc_program_name, plan_id, appraisal_period_id, status, start_date,
end_date
)
VALUES (l_wpm_batch_action_id, fnd_global.conc_request_id, 'PERPLNREPUB', p_plan_id, NULL, 'PENDING', SYSDATE,
NULL
);
hr_wpm_mass_apr_push.g_wpm_person_actions.DELETE;
UPDATE per_wpm_batch_actions
SET end_date = SYSDATE,
status = DECODE (g_retcode, 0, 'SUCCESS', 'WARNING')
WHERE wpm_batch_action_id = hr_wpm_mass_apr_push.l_current_wpm_batch_action_id;
UPDATE per_perf_mgmt_plans
SET status_code = 'PUBLISHED'
WHERE plan_id = p_plan_id;
UPDATE per_wpm_batch_actions
SET status = 'ERROR',
end_date = SYSDATE
WHERE wpm_batch_action_id = hr_wpm_mass_apr_push.l_current_wpm_batch_action_id;
-- update status of the plan to 'Failed'
--
UPDATE per_perf_mgmt_plans
SET status_code = 'FAILED'
WHERE plan_id = p_plan_id;