[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_next_levels (
p_plan_id NUMBER,
p_supervisor_id NUMBER,
p_sup_chain t_sup_level,
p_level_num NUMBER
)
IS
CURSOR csr_directs (p_plan_id NUMBER, p_supervisor_id NUMBER)
IS
SELECT DISTINCT ppf.full_name employee_name,
ppf.person_id employee_id,
ppf2.full_name supervisor_name,
ppf2.person_id supervisor_id,
pmp.plan_id
FROM per_perf_mgmt_plans pmp,
per_assignments_f paf,
per_people_f ppf,
per_people_f ppf2
WHERE pmp.plan_id = p_plan_id
AND paf.supervisor_id = p_supervisor_id
AND paf.primary_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.person_id = ppf.person_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND paf.supervisor_id = ppf2.person_id
AND TRUNC (SYSDATE) BETWEEN ppf2.effective_start_date AND ppf2.effective_end_date
AND paf.person_id IN (SELECT person_id
FROM per_personal_scorecards
WHERE plan_id = p_plan_id);
l_proc VARCHAR2 (80) := g_package || 'insert_next_levels';
hr_utility.TRACE ('INSIDE insert_next_levels: ' || l_sup_level.COUNT);
hr_utility.TRACE ('INSIDE insert_next_levels: ' || p_level_num);
hr_utility.TRACE ('Inserting directs for:' || i.supervisor_name);
hr_utility.TRACE ('Inserting direct:' || i.employee_name);
INSERT INTO per_wpm_plan_hierarchy
(wpm_plan_hierarchy_id,
plan_id,
employee_person_id,
employee_name,
supervisor_person_id,
supervisor_name,
level_num
)
VALUES (per_wpm_plan_hierarchy_s.NEXTVAL,
i.plan_id,
i.employee_id,
i.employee_name,
i.supervisor_id,
i.supervisor_name,
1
);
INSERT INTO per_wpm_plan_hierarchy
(wpm_plan_hierarchy_id,
plan_id,
employee_person_id,
employee_name,
supervisor_person_id,
supervisor_name,
level_num
)
VALUES (per_wpm_plan_hierarchy_s.NEXTVAL,
i.plan_id,
i.employee_id,
i.employee_name,
l_sup_level (j).supervisor_id,
l_sup_level (j).supervisor_name,
(l_max_level - l_counter) + 1
);
hr_utility.TRACE ('INSIDE insert_next_levels: ' || l_sup_level.COUNT);
hr_utility.TRACE ('INSIDE insert_next_levels: ' || l_sup_level (l_last + 1).level_num);
insert_next_levels (p_plan_id,
i.employee_id,
l_sup_level,
l_sup_level (l_last + 1).level_num
);
l_sup_level.DELETE;
END insert_next_levels;
SELECT *
FROM per_perf_mgmt_plans pmp
WHERE plan_id = p_plan_id;
SELECT sc.scorecard_id,
sc.person_id,
sc.assignment_id,
ppf.full_name
FROM per_personal_scorecards sc, per_people_f ppf
WHERE scorecard_id = p_sc_id
AND sc.person_id = ppf.person_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
SELECT sc.scorecard_id,
sc.person_id,
sc.assignment_id,
ppf.full_name
FROM per_personal_scorecards sc, per_people_f ppf
WHERE sc.plan_id = p_plan_id
AND sc.person_id = ppf.person_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
SELECT level
,e.*
FROM (
SELECT sc.assignment_id
,paf.person_id child_id
,paf.supervisor_id parent_id
,ppf2.full_name "SUPERVISOR_NAME"
,paf.position_id
,paf.organization_id
,ppf.full_name "EMPLOYEE_NAME"
FROM per_people_f ppf
,per_all_people_f ppf2
,per_assignments_f paf
,per_personal_scorecards sc
,per_perf_mgmt_plans pln
WHERE sc.plan_id = p_plan_id
AND sc.plan_id = pln.plan_id
AND sc.assignment_id = paf.assignment_id
AND paf.supervisor_id = ppf2.person_id
AND paf.person_id = ppf.person_id
AND trunc (sysdate) BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND trunc (sysdate) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND trunc (sysdate) BETWEEN ppf2.effective_start_date
AND ppf2.effective_end_date
AND pln.hierarchy_type_code = 'SUP'
UNION ALL
SELECT sc.assignment_id
,paf.assignment_id child_id
,paf.supervisor_assignment_id parent_id
,ppf2.full_name "SUPERVISOR_NAME"
,paf.position_id
,paf.organization_id
,ppf.full_name "EMPLOYEE_NAME"
FROM per_people_f ppf
,per_all_people_f ppf2
,per_assignments_f paf
,per_personal_scorecards sc
,per_perf_mgmt_plans pln
WHERE sc.plan_id = p_plan_id
AND sc.plan_id = pln.plan_id
AND sc.assignment_id = paf.assignment_id
AND paf.supervisor_id = ppf2.person_id
AND paf.person_id = ppf.person_id
AND trunc (sysdate) BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND trunc (sysdate) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND trunc (sysdate) BETWEEN ppf2.effective_start_date
AND ppf2.effective_end_date
AND pln.hierarchy_type_code = 'SUP_ASG'
AND paf.supervisor_assignment_id IS NOT NULL
UNION ALL
SELECT sc.assignment_id
,paf.person_id child_id
,paf.supervisor_id parent_id
,ppf2.full_name "SUPERVISOR_NAME"
,paf.position_id
,paf.organization_id
,ppf.full_name "EMPLOYEE_NAME"
FROM per_people_f ppf
,per_all_people_f ppf2
,per_assignments_f paf
,per_personal_scorecards sc
,per_perf_mgmt_plans pln
WHERE sc.plan_id = p_plan_id
AND sc.plan_id = pln.plan_id
AND sc.assignment_id = paf.assignment_id
AND paf.supervisor_id = ppf2.person_id
AND paf.person_id = ppf.person_id
AND trunc (sysdate) BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND trunc (sysdate) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND trunc (sysdate) BETWEEN ppf2.effective_start_date
AND ppf2.effective_end_date
AND pln.hierarchy_type_code NOT IN ('SUP','SUP_ASG')
) e
START WITH assignment_id = p_assignment_id
CONNECT BY PRIOR parent_id = child_id;
l_sc_hrchy.DELETE;
INSERT INTO per_wpm_plan_hierarchy
(wpm_plan_hierarchy_id,
plan_id,
employee_person_id,
employee_name,
supervisor_person_id,
supervisor_name,
level_num
)
VALUES (per_wpm_plan_hierarchy_s.NEXTVAL,
p_plan_id,
i.person_id,
i.full_name,
l_sc_hrchy (j).supervisor_id,
l_sc_hrchy (j).supervisor_name,
l_sc_hrchy (j).LEVEL
);
l_sc_hrchy.DELETE;
INSERT INTO per_wpm_plan_hierarchy
(wpm_plan_hierarchy_id,
plan_id,
employee_person_id,
employee_name,
supervisor_person_id,
supervisor_name,
level_num
)
VALUES (per_wpm_plan_hierarchy_s.NEXTVAL,
p_plan_id,
i.person_id,
i.full_name,
l_sc_hrchy (j).supervisor_id,
l_sc_hrchy (j).supervisor_name,
l_sc_hrchy (j).LEVEL
);
SELECT ppf.full_name employee_name,
ppf.person_id employee_id,
ppf2.full_name supervisor_name,
ppf2.person_id supervisor_id,
pmp.plan_id plan_id
FROM per_perf_mgmt_plans pmp, per_assignments_f paf, per_people_f ppf, per_people_f ppf2
WHERE pmp.plan_id = p_plan_id
AND pmp.supervisor_id = paf.supervisor_id
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND paf.person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND paf.supervisor_id = ppf2.person_id
AND p_effective_date BETWEEN ppf2.effective_start_date AND ppf2.effective_end_date
AND paf.person_id IN (SELECT person_id
FROM per_personal_scorecards
WHERE plan_id = p_plan_id);
DELETE per_wpm_plan_hierarchy
WHERE plan_id = p_plan_id;
INSERT INTO per_wpm_plan_hierarchy
(wpm_plan_hierarchy_id
,plan_id
,employee_person_id
,employee_name
,supervisor_person_id
,supervisor_name
,level_num)
VALUES (per_wpm_plan_hierarchy_s.nextval
,i.plan_id
,i.employee_id
,i.employee_name
,i.supervisor_id
,i.supervisor_name
,1);
insert_next_levels(p_plan_id, i.employee_id,l_sup_level,1);
SELECT COUNT (*)
FROM (SELECT pa.appraisal_id
FROM per_appraisals pa,
per_wpm_plan_hierarchy wph,
per_people_f ppf,
per_all_assignments_f paf,
per_periods_of_service pps
WHERE wph.plan_id = p_plan_id
AND wph.supervisor_person_id = p_supervisor_id
AND wph.level_num = 1
AND wph.employee_person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pa.plan_id = p_plan_id
AND pa.appraisal_template_id = p_appraisal_template_id
AND wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND paf.assignment_type = 'E'
AND paf.person_id = ppf.person_id
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
p_effective_date
)
AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
OR pa.appraisal_period_start_date = pps.date_start
)
AND pa.overall_performance_level_id = p_rating_level_id
UNION
SELECT pa.appraisal_id
FROM per_appraisals pa,
per_wpm_plan_hierarchy wph,
per_people_f ppf,
per_all_assignments_f paf,
per_periods_of_placement pps
WHERE wph.plan_id = p_plan_id
AND wph.supervisor_person_id = p_supervisor_id
AND wph.level_num = 1
AND wph.employee_person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pa.plan_id = p_plan_id
AND pa.appraisal_template_id = p_appraisal_template_id
AND wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND paf.assignment_type = 'C'
AND paf.person_id = ppf.person_id
AND pps.date_start = paf.period_of_placement_date_start
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
p_effective_date
)
AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
OR pa.appraisal_period_start_date = pps.date_start
)
AND pa.overall_performance_level_id = p_rating_level_id);
SELECT COUNT (*)
FROM (SELECT pa.appraisal_id
FROM per_appraisals pa,
per_wpm_plan_hierarchy wph,
per_people_f ppf,
per_all_assignments_f paf,
per_periods_of_service pps
WHERE wph.plan_id = p_plan_id
AND wph.supervisor_person_id = p_supervisor_id
AND wph.employee_person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pa.plan_id = p_plan_id
AND pa.appraisal_template_id = p_appraisal_template_id
AND wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND paf.assignment_type = 'E'
AND paf.person_id = ppf.person_id
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
p_effective_date
)
AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
OR pa.appraisal_period_start_date = pps.date_start
)
AND pa.overall_performance_level_id = p_rating_level_id
UNION
SELECT pa.appraisal_id
FROM per_appraisals pa,
per_wpm_plan_hierarchy wph,
per_people_f ppf,
per_all_assignments_f paf,
per_periods_of_placement pps
WHERE wph.plan_id = p_plan_id
AND wph.supervisor_person_id = p_supervisor_id
AND wph.employee_person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pa.plan_id = p_plan_id
AND pa.appraisal_template_id = p_appraisal_template_id
AND wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND paf.assignment_type = 'C'
AND paf.person_id = ppf.person_id
AND pps.date_start = paf.period_of_placement_date_start
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
p_effective_date
)
AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
OR pa.appraisal_period_start_date = pps.date_start
)
AND pa.overall_performance_level_id = p_rating_level_id);
SELECT COUNT (*)
FROM (SELECT pa.appraisal_id
FROM per_appraisals pa,
per_wpm_plan_hierarchy wph,
per_people_f ppf,
per_all_assignments_f paf,
per_periods_of_service pps
WHERE wph.plan_id = p_plan_id
AND wph.supervisor_person_id = p_supervisor_id
AND wph.level_num = 1
AND wph.employee_person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pa.plan_id = p_plan_id
AND pa.appraisal_template_id = p_appraisal_template_id
AND wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND paf.assignment_type = 'E'
AND paf.person_id = ppf.person_id
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
p_effective_date
)
AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
OR pa.appraisal_period_start_date = pps.date_start
)
AND pa.overall_performance_level_id IS NULL
UNION
SELECT pa.appraisal_id
FROM per_appraisals pa,
per_wpm_plan_hierarchy wph,
per_people_f ppf,
per_all_assignments_f paf,
per_periods_of_placement pps
WHERE wph.plan_id = p_plan_id
AND wph.supervisor_person_id = p_supervisor_id
AND wph.level_num = 1
AND wph.employee_person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pa.plan_id = p_plan_id
AND pa.appraisal_template_id = p_appraisal_template_id
AND wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND paf.assignment_type = 'C'
AND paf.person_id = ppf.person_id
AND pps.date_start = paf.period_of_placement_date_start
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
p_effective_date
)
AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
OR pa.appraisal_period_start_date = pps.date_start
)
AND pa.overall_performance_level_id IS NULL);
SELECT COUNT (*)
FROM (SELECT pa.appraisal_id
FROM per_appraisals pa,
per_wpm_plan_hierarchy wph,
per_people_f ppf,
per_all_assignments_f paf,
per_periods_of_service pps
WHERE wph.plan_id = p_plan_id
AND wph.supervisor_person_id = p_supervisor_id
AND wph.employee_person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pa.plan_id = p_plan_id
AND pa.appraisal_template_id = p_appraisal_template_id
AND wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND paf.assignment_type = 'E'
AND paf.person_id = ppf.person_id
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
p_effective_date
)
AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
OR pa.appraisal_period_start_date = pps.date_start
)
AND pa.overall_performance_level_id IS NULL
UNION
SELECT pa.appraisal_id
FROM per_appraisals pa,
per_wpm_plan_hierarchy wph,
per_people_f ppf,
per_all_assignments_f paf,
per_periods_of_placement pps
WHERE wph.plan_id = p_plan_id
AND wph.supervisor_person_id = p_supervisor_id
AND wph.employee_person_id = ppf.person_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pa.plan_id = p_plan_id
AND pa.appraisal_template_id = p_appraisal_template_id
AND wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pps.person_id
AND paf.assignment_type = 'C'
AND paf.person_id = ppf.person_id
AND pps.date_start = paf.period_of_placement_date_start
AND paf.primary_flag = 'Y'
AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
p_effective_date
)
AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
OR pa.appraisal_period_start_date = pps.date_start
)
AND pa.overall_performance_level_id IS NULL);
hr_utility.TRACE ('Appraisal Tempate or Appraisal Period not selected. Returning');
INSERT INTO per_wpm_appraisal_summary
(wpm_appraisal_summary_id,
plan_id,
appraisal_period_id,
supervisor_person_id,
supervisor_name,
level_1_id,
level_1_name,
level_1_direct_count,
level_1_total_count,
level_2_id,
level_2_name,
level_2_direct_count,
level_2_total_count,
level_3_id,
level_3_name,
level_3_direct_count,
level_3_total_count,
level_4_id,
level_4_name,
level_4_direct_count,
level_4_total_count,
level_5_id,
level_5_name,
level_5_direct_count,
level_5_total_count,
level_6_id,
level_6_name,
level_6_direct_count,
level_6_total_count,
level_7_id,
level_7_name,
level_7_direct_count,
level_7_total_count,
level_8_id,
level_8_name,
level_8_direct_count,
level_8_total_count,
level_9_id,
level_9_name,
level_9_direct_count,
level_9_total_count,
level_10_id,
level_10_name,
level_10_direct_count,
level_10_total_count,
level_11_id,
level_11_name,
level_11_direct_count,
level_11_total_count,
level_12_id,
level_12_name,
level_12_direct_count,
level_12_total_count,
level_13_id,
level_13_name,
level_13_direct_count,
level_13_total_count,
level_14_id,
level_14_name,
level_14_direct_count,
level_14_total_count,
level_15_id,
level_15_name,
level_15_direct_count,
level_15_total_count,
level_16_id,
level_16_name,
level_16_direct_count,
level_16_total_count,
level_17_id,
level_17_name,
level_17_direct_count,
level_17_total_count,
level_18_id,
level_18_name,
level_18_direct_count,
level_18_total_count,
level_19_id,
level_19_name,
level_19_direct_count,
level_19_total_count,
level_20_id,
level_20_name,
level_20_direct_count,
level_20_total_count,
norating_direct_count,
norating_total_count
)
VALUES (per_wpm_appraisal_summary_s.NEXTVAL,
p_plan_id,
g_appr_period_rec.appraisal_period_id,
p_supervisor_id,
p_supervisor_name,
l_rating_summary (1).rating_level_id,
l_rating_summary (1).rating_level_name,
l_rating_summary (1).direct_count,
l_rating_summary (1).total_count,
l_rating_summary (2).rating_level_id,
l_rating_summary (2).rating_level_name,
l_rating_summary (2).direct_count,
l_rating_summary (2).total_count,
l_rating_summary (3).rating_level_id,
l_rating_summary (3).rating_level_name,
l_rating_summary (3).direct_count,
l_rating_summary (3).total_count,
l_rating_summary (4).rating_level_id,
l_rating_summary (4).rating_level_name,
l_rating_summary (4).direct_count,
l_rating_summary (4).total_count,
l_rating_summary (5).rating_level_id,
l_rating_summary (5).rating_level_name,
l_rating_summary (5).direct_count,
l_rating_summary (5).total_count,
l_rating_summary (6).rating_level_id,
l_rating_summary (6).rating_level_name,
l_rating_summary (6).direct_count,
l_rating_summary (6).total_count,
l_rating_summary (7).rating_level_id,
l_rating_summary (7).rating_level_name,
l_rating_summary (7).direct_count,
l_rating_summary (7).total_count,
l_rating_summary (8).rating_level_id,
l_rating_summary (8).rating_level_name,
l_rating_summary (8).direct_count,
l_rating_summary (8).total_count,
l_rating_summary (9).rating_level_id,
l_rating_summary (9).rating_level_name,
l_rating_summary (9).direct_count,
l_rating_summary (9).total_count,
l_rating_summary (10).rating_level_id,
l_rating_summary (10).rating_level_name,
l_rating_summary (10).direct_count,
l_rating_summary (10).total_count,
l_rating_summary (11).rating_level_id,
l_rating_summary (11).rating_level_name,
l_rating_summary (11).direct_count,
l_rating_summary (11).total_count,
l_rating_summary (12).rating_level_id,
l_rating_summary (12).rating_level_name,
l_rating_summary (12).direct_count,
l_rating_summary (12).total_count,
l_rating_summary (13).rating_level_id,
l_rating_summary (13).rating_level_name,
l_rating_summary (13).direct_count,
l_rating_summary (13).total_count,
l_rating_summary (14).rating_level_id,
l_rating_summary (14).rating_level_name,
l_rating_summary (14).direct_count,
l_rating_summary (14).total_count,
l_rating_summary (15).rating_level_id,
l_rating_summary (15).rating_level_name,
l_rating_summary (15).direct_count,
l_rating_summary (15).total_count,
l_rating_summary (16).rating_level_id,
l_rating_summary (16).rating_level_name,
l_rating_summary (16).direct_count,
l_rating_summary (16).total_count,
l_rating_summary (17).rating_level_id,
l_rating_summary (17).rating_level_name,
l_rating_summary (17).direct_count,
l_rating_summary (17).total_count,
l_rating_summary (18).rating_level_id,
l_rating_summary (18).rating_level_name,
l_rating_summary (18).direct_count,
l_rating_summary (18).total_count,
l_rating_summary (19).rating_level_id,
l_rating_summary (19).rating_level_name,
l_rating_summary (19).direct_count,
l_rating_summary (19).total_count,
l_rating_summary (20).rating_level_id,
l_rating_summary (20).rating_level_name,
l_rating_summary (20).direct_count,
l_rating_summary (20).total_count,
l_direct_unrated_count,
l_total_unrated_count
);
SELECT pap.appraisal_period_id,
pap.start_date,
pap.end_date,
pat.rating_scale_id,
pat.appraisal_template_id
FROM per_appraisal_periods pap, per_appraisal_templates pat
WHERE pap.appraisal_period_id = p_appraisal_period_id
AND pap.appraisal_template_id = pat.appraisal_template_id;
SELECT rating_level_id,
step_value || '-' || NAME "LEVEL_NAME"
FROM per_rating_levels
WHERE rating_scale_id = p_rating_scale_id
ORDER BY step_value;
SELECT DISTINCT supervisor_person_id,
supervisor_name,
pap.appraisal_period_id,
pap.appraisal_template_id
FROM per_wpm_plan_hierarchy wph, per_appraisal_periods pap
WHERE wph.plan_id = p_plan_id
AND pap.appraisal_period_id = p_appraisal_period_id
AND pap.plan_id = wph.plan_id;
fnd_file.put_line (fnd_file.LOG, 'Invalid Appraisal Period selected. Cannot Proceed.');
g_errmsg := 'Invalid Appraisal Period selected. Cannot Proceed.';
DELETE per_wpm_appraisal_summary
WHERE plan_id = p_plan_id
AND appraisal_period_id = g_appr_period_rec.appraisal_period_id;
g_rating_levels.DELETE;
INSERT INTO per_wpm_appraisal_summary
(wpm_appraisal_summary_id,
plan_id,
appraisal_period_id,
supervisor_person_id,
supervisor_name,
level_1_id
)
VALUES (per_wpm_appraisal_summary_s.NEXTVAL,
p_plan_id,
g_appr_period_rec.appraisal_period_id,
-1,
fnd_date.date_to_canonical (SYSDATE),
fnd_global.conc_request_id
);
SELECT NVL (fnd_date.canonical_to_date (supervisor_name), SYSDATE)
-- supervisor_name is used to store the run date with id as -1.
FROM per_wpm_appraisal_summary
WHERE plan_id = p_plan_id AND appraisal_period_id = p_period_id
AND supervisor_person_id = -1;