[Home] [Help]
PACKAGE BODY: APPS.PER_WPM_SUMMARY_PKG
Source
1 PACKAGE BODY PER_WPM_SUMMARY_PKG AS
2 /* $Header: pewpmsum.pkb 120.2.12010000.4 2008/10/21 05:52:57 rvagvala ship $ */
3 --
4 --
5 g_package VARCHAR2(40) := 'per_wpm_summary_pkg.';
6 TYPE sup_level_rec IS RECORD
7 (supervisor_id NUMBER(15),
8 supervisor_name VARCHAR2(240),
9 level_num NUMBER(15) );
10 TYPE t_sup_level IS TABLE OF sup_level_rec INDEX BY BINARY_INTEGER;
11 TYPE t_appr_period_rec IS RECORD
12 (appraisal_period_id NUMBER(15),
13 start_date DATE,
14 end_date DATE,
15 rating_scale_id NUMBER(15) );
16 --
17 g_appr_period_rec t_appr_period_rec;
18 --
19 TYPE rating_level_rec IS RECORD
20 (rating_level_id NUMBER(15),
21 level_name VARCHAR2(100) );
22 TYPE t_rating_levels IS TABLE OF rating_level_rec INDEX BY BINARY_INTEGER;
23 g_rating_levels t_rating_levels ;
24 --
25 g_errmsg VARCHAR2(2000);
26 --
27 --
28 PROCEDURE populate_plan_hierarchy_cp(errbuf OUT NOCOPY VARCHAR2
29 ,retcode OUT NOCOPY NUMBER
30 ,p_plan_id IN number
31 ,p_effective_date IN VARCHAR2) IS
32 l_effective_date DATE;
33 l_proc VARCHAR2(80) := g_package||'main';
34 BEGIN
35 hr_utility.set_location('Entering : '||l_proc,10);
36 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
37 populate_plan_hierarchy(p_plan_id => p_plan_id
38 ,p_effective_date => l_effective_date);
39 hr_utility.set_location('Leaving : '||l_proc,10);
40 EXCEPTION
41 WHEN OTHERS THEN
42 fnd_file.put_line(fnd_file.log,Sqlerrm);
43 retcode := 2;
44 errbuf := SQLERRM;
45 RAISE;
46 END populate_plan_hierarchy_cp;
47 --
48 -- This procedure is obsolete and is not used
49 PROCEDURE insert_next_levels(p_plan_id NUMBER
50 ,p_supervisor_id NUMBER
51 ,p_sup_chain t_sup_level
52 ,p_level_num NUMBER) IS
53 CURSOR csr_directs (p_plan_id NUMBER, p_supervisor_id NUMBER) IS
54 SELECT DISTINCT
55 ppf.full_name employee_name
56 ,ppf.person_id employee_id
57 ,ppf2.full_name supervisor_name
58 ,ppf2.person_id supervisor_id
59 ,pmp.plan_id
60 FROM per_perf_mgmt_plans pmp
61 ,per_assignments_f paf
62 ,per_people_f ppf
63 ,per_people_f ppf2
64 WHERE pmp.plan_id = p_plan_id
65 AND paf.supervisor_id = p_supervisor_id
66 AND paf.primary_flag = 'Y'
67 AND trunc(sysdate) between paf.effective_start_date AND paf.effective_end_date
68 AND paf.person_id = ppf.person_id
69 AND trunc(sysdate) between ppf.effective_start_date AND ppf.effective_end_date
70 AND paf.supervisor_id = ppf2.person_id
71 AND trunc(sysdate) between ppf2.effective_start_date AND ppf2.effective_end_date
72 AND paf.person_id IN (select person_id FROM per_personal_scorecards WHERE plan_id = p_plan_id);
73 l_sup_level t_sup_level;
74 l_last NUMBER;
75 l_max_level NUMBER;
76 l_counter NUMBER;
77 l_proc VARCHAR2(80) := g_package||'insert_next_levels';
78 BEGIN
79 hr_utility.set_location('Entering:'||l_proc,10);
80 --
81
82 --
83 FOR i in csr_directs(p_plan_id, p_supervisor_id)
84 LOOP
85 l_sup_level := p_sup_chain;
86 hr_utility.trace('INSIDE insert_next_levels: '||l_sup_level.count);
87 hr_utility.trace('INSIDE insert_next_levels: '||p_level_num);
88 hr_utility.trace('Inserting directs for:'||i.supervisor_name);
89 hr_utility.trace('Inserting direct:'||i.employee_name);
90 INSERT INTO per_wpm_plan_hierarchy
91 (wpm_plan_hierarchy_id
92 ,plan_id
93 ,employee_person_id
94 ,employee_name
95 ,supervisor_person_id
96 ,supervisor_name
97 ,level_num)
98 VALUES (per_wpm_plan_hierarchy_s.nextval
99 ,i.plan_id
100 ,i.employee_id
101 ,i.employee_name
102 ,i.supervisor_id
103 ,i.supervisor_name
104 ,1);
105 l_max_level := l_sup_level(l_sup_level.LAST).level_num;
106 l_counter := 0;
107 FOR j in l_sup_level.FIRST .. l_sup_level.LAST
108 LOOP
109 hr_utility.trace('Looping for supervisors for :'||i.employee_name);
110 hr_utility.trace('Supervisor:'||l_sup_level(j).supervisor_name);
111 hr_utility.trace('Supervisor level:'||((l_max_level - l_counter)+1));
112 INSERT INTO per_wpm_plan_hierarchy
113 (wpm_plan_hierarchy_id
114 ,plan_id
115 ,employee_person_id
116 ,employee_name
117 ,supervisor_person_id
118 ,supervisor_name
119 ,level_num)
120 VALUES (per_wpm_plan_hierarchy_s.nextval
121 ,i.plan_id
122 ,i.employee_id
123 ,i.employee_name
124 ,l_sup_level(j).supervisor_id
125 ,l_sup_level(j).supervisor_name
126 ,(l_max_level - l_counter)+1 );
127 l_counter := l_counter+1;
128 END LOOP;
129 l_last := NVL(l_sup_level.LAST,0);
130 l_sup_level(l_last+1).supervisor_id := i.supervisor_id;
131 l_sup_level(l_last+1).supervisor_name := i.supervisor_name;
132 l_sup_level(l_last+1).level_num := NVL(p_level_num,1)+1;
133 hr_utility.trace('INSIDE insert_next_levels: '||l_sup_level.count);
134 hr_utility.trace('INSIDE insert_next_levels: '||l_sup_level(l_last+1).level_num);
135 insert_next_levels(p_plan_id, i.employee_id,l_sup_level, l_sup_level(l_last+1).level_num );
136 l_sup_level.DELETE;
137 END LOOP;
138 hr_utility.set_location('Leaving:'||l_proc,100);
139 END insert_next_levels;
140 --
141 --
142 PROCEDURE build_hierarchy_for_sc(p_plan_id IN NUMBER,
143 p_sc_id IN NUMBER DEFAULT NULL) IS
144 CURSOR csr_plan_dtls (p_plan_id IN NUMBER) IS
145 SELECT *
146 FROM per_perf_mgmt_plans pmp
147 WHERE plan_id = p_plan_id;
148 --
149 l_pl_rec per_perf_mgmt_plans%ROWTYPE;
150 --
151 CURSOR csr_sc_dtls(p_sc_id IN NUMBER) IS
152 SELECT sc.scorecard_id
153 ,sc.person_id
154 ,sc.assignment_id
155 ,ppf.full_name
156 FROM per_personal_scorecards sc,
157 per_people_f ppf
158 WHERE scorecard_id = p_sc_id
159 AND sc.person_id = ppf.person_id
160 AND trunc(sysdate) between ppf.effective_start_date AND
161 ppf.effective_end_date;
162 --
163 CURSOR csr_all_scs(p_plan_id IN NUMBER) IS
164 SELECT sc.scorecard_id
165 ,sc.person_id
166 ,sc.assignment_id
167 ,ppf.full_name
168 FROM per_personal_scorecards sc
169 ,per_people_f ppf
170 WHERE sc.plan_id = p_plan_id
171 AND sc.person_id = ppf.person_id
172 AND trunc(sysdate) between ppf.effective_start_date AND
173 ppf.effective_end_date;
174 --
175 --
176 CURSOR csr_sc_hrchy(p_plan_id IN NUMBER,p_assignment_id IN NUMBER) IS
177 SELECT level, e.*
178 FROM
179 (SELECT paf.assignment_id
180 ,paf.person_id
181 ,paf.supervisor_id
182 ,ppf2.full_name "SUPERVISOR_NAME"
183 ,paf.position_id
184 ,paf.organization_id
185 ,ppf.full_name "EMPLOYEE_NAME"
186 FROM per_people_f ppf
187 ,per_all_people_f ppf2
188 ,per_assignments_f paf
189 ,per_personal_scorecards sc
190 WHERE sc.plan_id = p_plan_id
191 AND sc.assignment_id = paf.assignment_id
192 AND paf.supervisor_id = ppf2.person_id
193 AND paf.person_id = ppf.person_id
194 AND trunc(sysdate) between paf.effective_start_date AND
195 paf.effective_end_date
196 AND trunc(sysdate) between ppf.effective_start_date AND
197 ppf.effective_end_date
198 AND trunc(sysdate) between ppf2.effective_start_date AND
199 ppf2.effective_end_date) e
200 START WITH assignment_id = p_assignment_id
201 CONNECT BY prior supervisor_id = person_id;
202 --
203 TYPE r_sc_hrchy IS RECORD (level NUMBER(15)
204 ,assignment_id number(15)
205 ,person_id number(15)
206 ,supervisor_id number(15)
207 ,supervisor_name per_people_f.full_name%TYPE
208 ,position_id number(15)
209 ,organization_id number(15)
210 ,employee_name per_people_f.full_name%TYPE);
211 TYPE t_sc_hrchy IS TABLE OF r_sc_hrchy INDEX BY BINARY_INTEGER;
212 l_sc_hrchy t_sc_hrchy;
213 BEGIN
214 OPEN csr_plan_dtls(p_plan_id);
215 FETCH csr_plan_dtls INTO l_pl_rec;
216 CLOSE csr_plan_dtls;
217 IF p_sc_id IS NOT NULL THEN
218 FOR i IN csr_sc_dtls(p_sc_id)
219 LOOP
220 l_sc_hrchy.DELETE;
221 OPEN csr_sc_hrchy(p_plan_id,i.assignment_id);
222 FETCH csr_sc_hrchy BULK COLLECT INTO l_sc_hrchy;
223 CLOSE csr_sc_hrchy;
224 IF l_sc_hrchy.COUNT > 0 THEN
225 FOR j IN l_sc_hrchy.FIRST .. l_sc_hrchy.LAST
226 LOOP
227 IF (l_sc_hrchy(j).person_id = l_pl_rec.supervisor_id OR
228 l_sc_hrchy(j).assignment_id = l_pl_rec.supervisor_assignment_id
229 OR
230 l_sc_hrchy(j).organization_id = l_pl_rec.top_organization_id OR
231 l_sc_hrchy(j).position_id = l_pl_rec.top_position_id ) THEN
232 -- No need to insert anything as this is the top most record in the hierarchy
233 NULL;
234 ELSE
235 INSERT INTO per_wpm_plan_hierarchy
236 (wpm_plan_hierarchy_id
237 ,plan_id
238 ,employee_person_id
239 ,employee_name
240 ,supervisor_person_id
241 ,supervisor_name
242 ,level_num)
243 VALUES (per_wpm_plan_hierarchy_s.nextval
244 ,p_plan_id
245 ,i.person_id
246 ,i.full_name
247 ,l_sc_hrchy(j).supervisor_id
248 ,l_sc_hrchy(j).supervisor_name
249 ,l_sc_hrchy(j).level);
250 END IF;
251 END LOOP;
252 END IF; ---count > 0
253 END LOOP;
254 ELSE
255 FOR i IN csr_all_scs(p_plan_id)
256 LOOP
257 l_sc_hrchy.DELETE;
258 OPEN csr_sc_hrchy(p_plan_id,i.assignment_id);
259 FETCH csr_sc_hrchy BULK COLLECT INTO l_sc_hrchy;
260 CLOSE csr_sc_hrchy;
261 IF l_sc_hrchy.count > 0 THEN
262 FOR j IN l_sc_hrchy.FIRST .. l_sc_hrchy.LAST
263 LOOP
264 IF (l_sc_hrchy(j).person_id = l_pl_rec.supervisor_id OR
265 l_sc_hrchy(j).assignment_id = l_pl_rec.supervisor_assignment_id
266 OR
267 l_sc_hrchy(j).organization_id = l_pl_rec.top_organization_id OR
268 l_sc_hrchy(j).position_id = l_pl_rec.top_position_id ) THEN
269 -- No need to insert anything as this is the top most record in the hierarchy
270 NULL;
271 ELSE
272 INSERT INTO per_wpm_plan_hierarchy
273 (wpm_plan_hierarchy_id
274 ,plan_id
275 ,employee_person_id
276 ,employee_name
277 ,supervisor_person_id
278 ,supervisor_name
279 ,level_num)
280 VALUES (per_wpm_plan_hierarchy_s.nextval
281 ,p_plan_id
282 ,i.person_id
283 ,i.full_name
284 ,l_sc_hrchy(j).supervisor_id
285 ,l_sc_hrchy(j).supervisor_name
286 ,l_sc_hrchy(j).level);
287 END IF;
288 END LOOP;
289 END IF; -- count > 0
290 END LOOP;
291 END IF;
292 END build_hierarchy_for_sc;
293 --
294 --
295 PROCEDURE populate_plan_hierarchy(p_plan_id IN NUMBER
296 ,p_effective_date IN DATE) IS
297 --
298 CURSOR csr_plan_sup_directs(p_plan_id NUMBER, p_effective_date DATE) IS
299 SELECT ppf.full_name employee_name
300 ,ppf.person_id employee_id
301 ,ppf2.full_name supervisor_name
302 ,ppf2.person_id supervisor_id
303 ,pmp.plan_id PLAN_ID
304 FROM per_perf_mgmt_plans pmp
305 ,per_assignments_f paf
306 ,per_people_f ppf
307 ,per_people_f ppf2
308 WHERE pmp.plan_id = p_plan_id
309 AND pmp.supervisor_id = paf.supervisor_id
310 AND paf.primary_flag = 'Y'
311 AND p_effective_date between paf.effective_start_date AND paf.effective_end_date
312 AND paf.person_id = ppf.person_id
313 AND p_effective_date between ppf.effective_start_date AND ppf.effective_end_date
314 AND paf.supervisor_id = ppf2.person_id
315 AND p_effective_date between ppf2.effective_start_date AND ppf2.effective_end_date
316 AND paf.person_id IN (select person_id FROM per_personal_scorecards WHERE plan_id = p_plan_id);
317 l_effective_date DATE;
318 l_sup_level t_sup_level;
319 l_proc VARCHAR2(80) := g_package||'populate_plan_hierarchy';
320 BEGIN
321 --
322 hr_utility.set_location('Entering:'||l_proc,10);
323 DELETE per_wpm_plan_hierarchy
324 WHERE plan_id = p_plan_id;
328 LOOP
325 l_effective_date := NVL(p_effective_date,TRUNC(SYSDATE));
326 /* changed the logic using build_hierarchy_for_sc
327 * FOR i IN csr_plan_sup_directs(p_plan_id,l_effective_date)
329 l_sup_level(1).supervisor_id := i.supervisor_id;
330 l_sup_level(1).supervisor_name := i.supervisor_name;
331 l_sup_level(1).level_num :=1;
332 INSERT INTO per_wpm_plan_hierarchy
333 (wpm_plan_hierarchy_id
334 ,plan_id
335 ,employee_person_id
336 ,employee_name
337 ,supervisor_person_id
338 ,supervisor_name
339 ,level_num)
340 VALUES (per_wpm_plan_hierarchy_s.nextval
341 ,i.plan_id
342 ,i.employee_id
343 ,i.employee_name
344 ,i.supervisor_id
345 ,i.supervisor_name
346 ,1);
347 insert_next_levels(p_plan_id, i.employee_id,l_sup_level,1);
348 END LOOP;
349 */
350 build_hierarchy_for_sc(p_plan_id => p_plan_id);
351 COMMIT;
352 hr_utility.set_location('Leaving:'||l_proc,100);
353 --
354 --
355 END populate_plan_hierarchy;
356 --
357 --
358
359
360
361 procedure submit_refreshApprSummary_cp
362 (p_plan_id in number
363 ,p_appraisal_period_id in number
364 ,p_request_id out NOCOPY number
365 )
366 is
367 --
368
369 l_request_id number ;
370 l_effective_date varchar2(30) := fnd_date.date_to_canonical(trunc(sysdate));
371
372 --
373 begin
374 -- Submit the request
375 l_request_id := fnd_request.submit_request(
376 application => 'PER'
377 ,program => 'PERAPPRSUM'
378 ,sub_request => FALSE
379 -- ,start_time => l_effective_date
380 ,argument1 => p_plan_id
381 ,argument2 => p_appraisal_period_id
382 ,argument3 => l_effective_date
383 );
384 --
385
386 if l_request_id > 0 then
387 null;
388 end if;
389
390 p_request_id := l_request_id;
391 commit;
392 --
393 end submit_refreshApprSummary_cp;
394
395
396
397 PROCEDURE populate_appraisal_summary_cp(errbuf OUT NOCOPY VARCHAR2
398 ,retcode OUT NOCOPY NUMBER
399 ,p_plan_id IN NUMBER
400 ,p_appraisal_period_id IN NUMBER
401 ,p_effective_date IN VARCHAR2) IS
402 l_proc VARCHAR2(80) := g_package||'populate_appraisal_summary_cp';
403 l_effective_date DATE;
404 BEGIN
405 hr_utility.set_location('Entering:'||l_proc,10);
406 l_effective_date := NVL(fnd_date.canonical_to_date(p_effective_date),TRUNC(SYSDATE));
407 populate_appraisal_summary(p_plan_id => p_plan_id
408 ,p_appraisal_period_id => p_appraisal_period_id
409 ,p_effective_date => l_effective_date);
410 hr_utility.set_location('Leaving:'||l_proc,100);
411 EXCEPTION
412 WHEN OTHERS THEN
413 fnd_file.put_line(fnd_file.log,Sqlerrm);
414 retcode := 2;
415 errbuf := SQLERRM;
416 hr_utility.set_location('Leaving:'||l_proc,110);
417 RAISE;
418 END populate_appraisal_summary_cp;
419 --
420 --
421 PROCEDURE compute_summary_for_supervisor(p_plan_id IN NUMBER
422 ,p_effective_date IN DATE
423 ,p_supervisor_id IN NUMBER
424 ,p_supervisor_name IN VARCHAR2) IS
425 --
426 l_proc VARCHAR2(80) := g_package||'populate_appraisal_summary';
427 --
428 CURSOR csr_direct_summary(p_plan_id NUMBER
429 ,p_supervisor_id NUMBER
430 ,p_effective_date DATE
431 ,p_rating_level_id NUMBER) IS
432 SELECT COUNT(*)
433 FROM per_appraisals pa
434 ,per_wpm_plan_hierarchy wph
435 ,per_people_f ppf
436 WHERE wph.plan_id = p_plan_id
437 AND wph.supervisor_person_id = p_supervisor_id
438 AND wph.level_num = 1
439 AND wph.employee_person_id = ppf.person_id
440 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
441 AND pa.plan_id = p_plan_id
442 AND wph.employee_person_id = pa.appraisee_person_id
443 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
444 AND (pa.appraisal_period_start_date = g_appr_period_rec.start_date OR pa.appraisal_period_start_date = ppf.start_date)
445 AND pa.overall_performance_level_id = p_rating_level_id;
446 --
447 --
448 CURSOR csr_total_summary(p_plan_id NUMBER
449 ,p_supervisor_id NUMBER
453 FROM per_appraisals pa
450 ,p_effective_date DATE
451 ,p_rating_level_id NUMBER) IS
452 SELECT COUNT(*)
454 ,per_wpm_plan_hierarchy wph
455 ,per_people_f ppf
456 WHERE wph.plan_id = p_plan_id
457 AND wph.supervisor_person_id = p_supervisor_id
458 AND wph.employee_person_id = ppf.person_id
459 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
460 AND pa.plan_id = p_plan_id
461 AND wph.employee_person_id = pa.appraisee_person_id
462 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
463 AND (pa.appraisal_period_start_date = g_appr_period_rec.start_date OR pa.appraisal_period_start_date = ppf.start_date)
464 AND pa.overall_performance_level_id = p_rating_level_id;
465 --
466 --
467 CURSOR csr_directs_unrated(p_plan_id NUMBER
468 ,p_supervisor_id NUMBER
469 ,p_effective_date DATE) IS
470 SELECT COUNT(*)
471 FROM per_appraisals pa
472 ,per_wpm_plan_hierarchy wph
473 ,per_people_f ppf
474 WHERE wph.plan_id = p_plan_id
475 AND wph.level_num = 1
476 AND wph.supervisor_person_id = p_supervisor_id
477 AND wph.employee_person_id = ppf.person_id
478 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
479 AND pa.plan_id = p_plan_id
480 AND wph.employee_person_id = pa.appraisee_person_id
481 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
482 AND (pa.appraisal_period_start_date = g_appr_period_rec.start_date OR pa.appraisal_period_start_date = ppf.start_date)
483 AND pa.overall_performance_level_id IS NULL;
484 CURSOR csr_total_unrated(p_plan_id NUMBER
485 ,p_supervisor_id NUMBER
486 ,p_effective_date DATE) IS
487 SELECT COUNT(*)
488 FROM per_appraisals pa
489 ,per_wpm_plan_hierarchy wph
490 ,per_people_f ppf
491 WHERE wph.plan_id = p_plan_id
492 AND wph.supervisor_person_id = p_supervisor_id
493 AND wph.employee_person_id = ppf.person_id
494 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
495 AND pa.plan_id = p_plan_id
496 AND wph.employee_person_id = pa.appraisee_person_id
497 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
498 AND (pa.appraisal_period_start_date = g_appr_period_rec.start_date OR pa.appraisal_period_start_date = ppf.start_date)
499 AND pa.overall_performance_level_id IS NULL;
500 --
501 --
502 TYPE r_rating_summary IS RECORD (rating_level_id NUMBER(15),
503 rating_level_name VARCHAR2(100),
504 direct_count NUMBER(15),
505 total_count NUMBER(15) );
506 TYPE t_rating_summary IS TABLE OF r_rating_summary INDEX BY BINARY_INTEGER;
507 l_rating_summary t_rating_summary;
508 l_direct_count NUMBER(15);
509 l_tot_count NUMBER(15);
510 l_total_unrated_count NUMBER(15);
511 l_direct_unrated_count NUMBER(15);
512 --
513 BEGIN
514 --
515 hr_utility.set_location('Entering:'||l_proc,10);
516 FOR i IN g_rating_levels.FIRST .. LEAST(g_rating_levels.LAST,20)-- only upto 20 levels
517 LOOP
518 l_rating_summary(i).rating_level_id := g_rating_levels(i).rating_level_id;
519 l_rating_summary(i).rating_level_name := g_rating_levels(i).level_name;
520 OPEN csr_direct_summary(p_plan_id
521 ,p_supervisor_id
522 ,p_effective_date
523 ,g_rating_levels(i).rating_level_id);
524 FETCH csr_direct_summary INTO l_direct_count;
525 CLOSE csr_direct_summary;
526 OPEN csr_total_summary(p_plan_id
527 ,p_supervisor_id
528 ,p_effective_date
529 ,g_rating_levels(i).rating_level_id);
530 FETCH csr_total_summary INTO l_tot_count;
531 CLOSE csr_total_summary;
532 l_rating_summary(i).direct_count := NVL(l_direct_count,0);
533 l_rating_summary(i).total_count := NVL(l_tot_count,0);
534 END LOOP;
535 FOR i IN (l_rating_summary.COUNT+1) .. 20
536 LOOP
537 l_rating_summary(i).rating_level_id := NULL;
538 l_rating_summary(i).rating_level_name:= NULL;
539 l_rating_summary(i).direct_count := 0;
540 l_rating_summary(i).total_count := 0;
541 END LOOP;
542 OPEN csr_total_unrated(p_plan_id
543 ,p_supervisor_id
544 ,p_effective_date);
545 FETCH csr_total_unrated INTO l_total_unrated_count;
546 CLOSE csr_total_unrated;
547 OPEN csr_directs_unrated(p_plan_id
548 ,p_supervisor_id
549 ,p_effective_date);
550 FETCH csr_directs_unrated INTO l_direct_unrated_count;
551 CLOSE csr_directs_unrated;
552
553 --
554 INSERT INTO PER_WPM_APPRAISAL_SUMMARY
555 (
556 wpm_appraisal_summary_id ,
557 plan_id ,
558 appraisal_period_id ,
559 supervisor_person_id ,
560 supervisor_name ,
561 level_1_id ,
562 level_1_name ,
566 level_2_name ,
563 level_1_direct_count ,
564 level_1_total_count ,
565 level_2_id ,
567 level_2_direct_count ,
568 level_2_total_count ,
569 level_3_id ,
570 level_3_name ,
571 level_3_direct_count ,
572 level_3_total_count ,
573 level_4_id ,
574 level_4_name ,
575 level_4_direct_count ,
576 level_4_total_count ,
577 level_5_id ,
578 level_5_name ,
579 level_5_direct_count ,
580 level_5_total_count ,
581 level_6_id ,
582 level_6_name ,
583 level_6_direct_count ,
584 level_6_total_count ,
585 level_7_id ,
586 level_7_name ,
587 level_7_direct_count ,
588 level_7_total_count ,
589 level_8_id ,
590 level_8_name ,
591 level_8_direct_count ,
592 level_8_total_count ,
593 level_9_id ,
594 level_9_name ,
595 level_9_direct_count ,
596 level_9_total_count ,
597 level_10_id ,
598 level_10_name ,
599 level_10_direct_count ,
600 level_10_total_count ,
601 level_11_id ,
602 level_11_name ,
603 level_11_direct_count ,
604 level_11_total_count ,
605 level_12_id ,
606 level_12_name ,
607 level_12_direct_count ,
608 level_12_total_count ,
609 level_13_id ,
610 level_13_name ,
611 level_13_direct_count ,
612 level_13_total_count ,
613 level_14_id ,
614 level_14_name ,
615 level_14_direct_count ,
616 level_14_total_count ,
617 level_15_id ,
618 level_15_name ,
619 level_15_direct_count ,
620 level_15_total_count ,
621 level_16_id ,
622 level_16_name ,
623 level_16_direct_count ,
624 level_16_total_count ,
625 level_17_id ,
626 level_17_name ,
627 level_17_direct_count ,
628 level_17_total_count ,
629 level_18_id ,
630 level_18_name ,
631 level_18_direct_count ,
632 level_18_total_count ,
633 level_19_id ,
634 level_19_name ,
635 level_19_direct_count ,
636 level_19_total_count ,
637 level_20_id ,
638 level_20_name ,
639 level_20_direct_count ,
640 level_20_total_count ,
641 norating_direct_count ,
642 norating_total_count )
643 VALUES
644 (
645 PER_WPM_APPRAISAL_SUMMARY_S.nextval
646 ,p_plan_id
647 ,g_appr_period_rec.appraisal_period_id
648 ,p_supervisor_id
649 ,p_supervisor_name
650 ,l_rating_summary(1).rating_level_id
651 ,l_rating_summary(1).rating_level_name
652 ,l_rating_summary(1).direct_count
653 ,l_rating_summary(1).total_count
654 ,l_rating_summary(2).rating_level_id
655 ,l_rating_summary(2).rating_level_name
656 ,l_rating_summary(2).direct_count
657 ,l_rating_summary(2).total_count
658 ,l_rating_summary(3).rating_level_id
659 ,l_rating_summary(3).rating_level_name
660 ,l_rating_summary(3).direct_count
661 ,l_rating_summary(3).total_count
662 ,l_rating_summary(4).rating_level_id
663 ,l_rating_summary(4).rating_level_name
664 ,l_rating_summary(4).direct_count
665 ,l_rating_summary(4).total_count
666 ,l_rating_summary(5).rating_level_id
667 ,l_rating_summary(5).rating_level_name
668 ,l_rating_summary(5).direct_count
669 ,l_rating_summary(5).total_count
670 ,l_rating_summary(6).rating_level_id
671 ,l_rating_summary(6).rating_level_name
672 ,l_rating_summary(6).direct_count
673 ,l_rating_summary(6).total_count
674 ,l_rating_summary(7).rating_level_id
675 ,l_rating_summary(7).rating_level_name
676 ,l_rating_summary(7).direct_count
677 ,l_rating_summary(7).total_count
678 ,l_rating_summary(8).rating_level_id
679 ,l_rating_summary(8).rating_level_name
680 ,l_rating_summary(8).direct_count
681 ,l_rating_summary(8).total_count
682 ,l_rating_summary(9).rating_level_id
683 ,l_rating_summary(9).rating_level_name
684 ,l_rating_summary(9).direct_count
685 ,l_rating_summary(9).total_count
686 ,l_rating_summary(10).rating_level_id
687 ,l_rating_summary(10).rating_level_name
688 ,l_rating_summary(10).direct_count
689 ,l_rating_summary(10).total_count
690 ,l_rating_summary(11).rating_level_id
691 ,l_rating_summary(11).rating_level_name
692 ,l_rating_summary(11).direct_count
693 ,l_rating_summary(11).total_count
694 ,l_rating_summary(12).rating_level_id
695 ,l_rating_summary(12).rating_level_name
696 ,l_rating_summary(12).direct_count
697 ,l_rating_summary(12).total_count
698 ,l_rating_summary(13).rating_level_id
699 ,l_rating_summary(13).rating_level_name
700 ,l_rating_summary(13).direct_count
701 ,l_rating_summary(13).total_count
702 ,l_rating_summary(14).rating_level_id
703 ,l_rating_summary(14).rating_level_name
704 ,l_rating_summary(14).direct_count
705 ,l_rating_summary(14).total_count
706 ,l_rating_summary(15).rating_level_id
707 ,l_rating_summary(15).rating_level_name
708 ,l_rating_summary(15).direct_count
709 ,l_rating_summary(15).total_count
710 ,l_rating_summary(16).rating_level_id
711 ,l_rating_summary(16).rating_level_name
712 ,l_rating_summary(16).direct_count
713 ,l_rating_summary(16).total_count
714 ,l_rating_summary(17).rating_level_id
715 ,l_rating_summary(17).rating_level_name
716 ,l_rating_summary(17).direct_count
717 ,l_rating_summary(17).total_count
718 ,l_rating_summary(18).rating_level_id
719 ,l_rating_summary(18).rating_level_name
720 ,l_rating_summary(18).direct_count
721 ,l_rating_summary(18).total_count
722 ,l_rating_summary(19).rating_level_id
723 ,l_rating_summary(19).rating_level_name
724 ,l_rating_summary(19).direct_count
725 ,l_rating_summary(19).total_count
726 ,l_rating_summary(20).rating_level_id
727 ,l_rating_summary(20).rating_level_name
728 ,l_rating_summary(20).direct_count
729 ,l_rating_summary(20).total_count
730 ,l_direct_unrated_count
731 ,l_total_unrated_count);
732 --
733 hr_utility.set_location('Leaving:'||l_proc,100);
734 --
735 END compute_summary_for_supervisor;
736 --
737 --
738 PROCEDURE populate_appraisal_summary(p_plan_id IN NUMBER
739 ,p_appraisal_period_id IN NUMBER
740 ,p_effective_date IN DATE) IS
741 l_proc VARCHAR2(80) := g_package||'populate_appraisal_summary';
742 --
743 CURSOR csr_appr_period_dtls(p_appraisal_period_id IN NUMBER) IS
744 SELECT pap.appraisal_period_id
745 ,pap.start_date
746 ,pap.end_date
747 ,pat.rating_scale_id
748 FROM per_appraisal_periods pap
749 ,per_appraisal_templates pat
750 WHERE pap.appraisal_period_id = p_appraisal_period_id
751 AND pap.appraisal_template_id = pat.appraisal_template_id;
752 --
753 --
754 CURSOR csr_appr_levels (p_rating_scale_id IN NUMBER) IS
755 SELECT rating_level_id
756 ,step_value||'-'||name "LEVEL_NAME"
757 FROM per_rating_levels
758 WHERE rating_scale_id = p_rating_scale_id
759 ORDER BY step_value;
760 --
761 --
762 CURSOR csr_plan_managers(p_plan_id IN NUMBER) IS
763 SELECT distinct supervisor_person_id
764 , supervisor_name
765 FROM per_wpm_plan_hierarchy
766 WHERE plan_id = p_plan_id;
767 BEGIN
768 hr_utility.set_location('Entering:'||l_proc,10);
769 OPEN csr_appr_period_dtls(p_appraisal_period_id);
770 FETCH csr_appr_period_dtls INTO g_appr_period_rec;
771 IF csr_appr_period_dtls%NOTFOUND THEN
772 fnd_file.put_line(fnd_file.LOG,'Invalid Appraisal Period selected. Cannot Proceed.');
773 g_errmsg := 'Invalid Appraisal Period selected. Cannot Proceed.';--- New message to be created.
774 CLOSE csr_appr_period_dtls;
775 RETURN;
776 END IF;
777 CLOSE csr_appr_period_dtls;
778 DELETE PER_WPM_APPRAISAL_SUMMARY
779 WHERE plan_id = p_plan_id
780 AND appraisal_period_id = g_appr_period_rec.appraisal_period_id;
781 COMMIT;
782 g_rating_levels.DELETE;
783 OPEN csr_appr_levels(g_appr_period_rec.rating_scale_id);
784 FETCH csr_appr_levels BULK COLLECT INTO g_rating_levels;
785 CLOSE csr_appr_levels;
786 FOR i IN csr_plan_managers(p_plan_id)
787 LOOP
788 hr_utility.trace('Computing totals for : '||i.supervisor_name);
789 compute_summary_for_supervisor(p_plan_id => p_plan_id
790 ,p_effective_date => p_effective_date
791 ,p_supervisor_id => i.supervisor_person_id
792 ,p_supervisor_name => i.supervisor_name);
793 END LOOP;
794 /*
795 create a row with supervisor_person_id as -1 to store the run_date into the
796 supervisor_name column in canonical format. level_1_id will store the request Id.
797 */
798 INSERT INTO PER_WPM_APPRAISAL_SUMMARY
799 (
800 wpm_appraisal_summary_id ,
801 plan_id ,
802 appraisal_period_id ,
803 supervisor_person_id ,
804 supervisor_name ,
805 level_1_id )
806 VALUES
807 (PER_WPM_APPRAISAL_SUMMARY_S.nextval
808 ,p_plan_id
809 ,g_appr_period_rec.appraisal_period_id
810 ,-1
811 ,fnd_date.date_to_canonical(sysdate)
812 ,fnd_global.conc_request_id);
813 --
814 COMMIT;
815 --
816 hr_utility.set_location('Leaving:'||l_proc,100);
817 END populate_appraisal_summary;
818 --
819 --
820 FUNCTION get_summary_date(p_plan_id IN NUMBER
821 ,p_appraisal_period_id IN NUMBER) RETURN DATE IS
822 CURSOR csr_get_date(p_plan_id NUMBER
823 ,p_period_id NUMBER) IS
824 SELECT NVL(fnd_date.canonical_to_date(supervisor_name),SYSDATE) -- supervisor_name is used to store the run date with id as -1.
825 FROM per_wpm_appraisal_summary
826 WHERE plan_id = p_plan_id
827 AND appraisal_period_id = p_period_id
828 AND supervisor_person_id = -1;
829 l_summary_date DATE;
830 BEGIN
831 OPEN csr_get_date(p_plan_id,p_appraisal_period_id);
832 FETCH csr_get_date INTO l_summary_date;
833 CLOSE csr_get_date;
834
835 RETURN l_summary_date;
836 END get_summary_date;
837 END PER_WPM_SUMMARY_PKG;