[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 paf.assignment_id
,paf.person_id
,paf.supervisor_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
WHERE sc.plan_id = p_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) e
START WITH assignment_id = p_assignment_id
CONNECT BY prior supervisor_id = person_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 per_appraisals pa
,per_wpm_plan_hierarchy wph
,per_people_f ppf
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 wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND (pa.appraisal_period_start_date = g_appr_period_rec.start_date OR pa.appraisal_period_start_date = ppf.start_date)
AND pa.overall_performance_level_id = p_rating_level_id;
SELECT COUNT(*)
FROM per_appraisals pa
,per_wpm_plan_hierarchy wph
,per_people_f ppf
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 wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND (pa.appraisal_period_start_date = g_appr_period_rec.start_date OR pa.appraisal_period_start_date = ppf.start_date)
AND pa.overall_performance_level_id = p_rating_level_id;
SELECT COUNT(*)
FROM per_appraisals pa
,per_wpm_plan_hierarchy wph
,per_people_f ppf
WHERE wph.plan_id = p_plan_id
AND wph.level_num = 1
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 wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND (pa.appraisal_period_start_date = g_appr_period_rec.start_date OR pa.appraisal_period_start_date = ppf.start_date)
AND pa.overall_performance_level_id IS NULL;
SELECT COUNT(*)
FROM per_appraisals pa
,per_wpm_plan_hierarchy wph
,per_people_f ppf
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 wph.employee_person_id = pa.appraisee_person_id
AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
AND (pa.appraisal_period_start_date = g_appr_period_rec.start_date OR pa.appraisal_period_start_date = ppf.start_date)
AND pa.overall_performance_level_id IS NULL;
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
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
FROM per_wpm_plan_hierarchy
WHERE plan_id = p_plan_id;
fnd_file.put_line(fnd_file.LOG,'Invalid Appraisal Period selected. Cannot Proceed.');
g_errmsg := 'Invalid Appraisal Period selected. Cannot Proceed.';--- New message to be created.
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;