[Home] [Help]
PACKAGE BODY: APPS.PER_WPM_SUMMARY_PKG
Source
1 PACKAGE BODY per_wpm_summary_pkg AS
2 /* $Header: pewpmsum.pkb 120.8.12020000.4 2013/02/08 09:03:57 schowdhu ship $ */
3 --
4 --
5 g_package VARCHAR2 (40) := 'per_wpm_summary_pkg.';
6
7 TYPE sup_level_rec IS RECORD (
8 supervisor_id NUMBER (15),
9 supervisor_name VARCHAR2 (240),
10 level_num NUMBER (15)
11 );
12
13 TYPE t_sup_level IS TABLE OF sup_level_rec
14 INDEX BY BINARY_INTEGER;
15
16 TYPE t_appr_period_rec IS RECORD (
17 appraisal_period_id NUMBER (15),
18 start_date DATE,
19 end_date DATE,
20 rating_scale_id NUMBER (15),
21 appraisal_template_id NUMBER (15)
22 );
23
24 --
25 g_appr_period_rec t_appr_period_rec;
26
27 --
28 TYPE rating_level_rec IS RECORD (
29 rating_level_id NUMBER (15),
30 level_name VARCHAR2 (100)
31 );
32
33 TYPE t_rating_levels IS TABLE OF rating_level_rec
34 INDEX BY BINARY_INTEGER;
35
36 g_rating_levels t_rating_levels;
37 --
38 g_errmsg VARCHAR2 (2000);
39
40 --
41 --
42 PROCEDURE populate_plan_hierarchy_cp (
43 errbuf OUT NOCOPY VARCHAR2,
44 retcode OUT NOCOPY NUMBER,
45 p_plan_id IN NUMBER,
46 p_effective_date IN VARCHAR2
47 )
48 IS
49 l_effective_date DATE;
50 l_proc VARCHAR2 (80) := g_package || 'main';
51 BEGIN
52 hr_utility.set_location ('Entering : ' || l_proc, 10);
53 l_effective_date := fnd_date.canonical_to_date (p_effective_date);
54 populate_plan_hierarchy (p_plan_id => p_plan_id, p_effective_date => l_effective_date);
58 THEN
55 hr_utility.set_location ('Leaving : ' || l_proc, 10);
56 EXCEPTION
57 WHEN OTHERS
59 fnd_file.put_line (fnd_file.LOG, SQLERRM);
60 retcode := 2;
61 errbuf := SQLERRM;
62 RAISE;
63 END populate_plan_hierarchy_cp;
64
65 --
66 -- This procedure is obsolete and is not used
67 PROCEDURE insert_next_levels (
68 p_plan_id NUMBER,
69 p_supervisor_id NUMBER,
70 p_sup_chain t_sup_level,
71 p_level_num NUMBER
72 )
73 IS
74 CURSOR csr_directs (p_plan_id NUMBER, p_supervisor_id NUMBER)
75 IS
76 SELECT DISTINCT ppf.full_name employee_name,
77 ppf.person_id employee_id,
78 ppf2.full_name supervisor_name,
79 ppf2.person_id supervisor_id,
80 pmp.plan_id
81 FROM per_perf_mgmt_plans pmp,
82 per_assignments_f paf,
83 per_people_f ppf,
84 per_people_f ppf2
85 WHERE pmp.plan_id = p_plan_id
86 AND paf.supervisor_id = p_supervisor_id
87 AND paf.primary_flag = 'Y'
88 AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
89 AND paf.person_id = ppf.person_id
90 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
91 AND paf.supervisor_id = ppf2.person_id
92 AND TRUNC (SYSDATE) BETWEEN ppf2.effective_start_date AND ppf2.effective_end_date
93 AND paf.person_id IN (SELECT person_id
94 FROM per_personal_scorecards
95 WHERE plan_id = p_plan_id);
96
97 l_sup_level t_sup_level;
98 l_last NUMBER;
99 l_max_level NUMBER;
100 l_counter NUMBER;
101 l_proc VARCHAR2 (80) := g_package || 'insert_next_levels';
102 BEGIN
103 hr_utility.set_location ('Entering:' || l_proc, 10);
104
105 --
106
107 --
108 FOR i IN csr_directs (p_plan_id, p_supervisor_id)
109 LOOP
110 l_sup_level := p_sup_chain;
111 hr_utility.TRACE ('INSIDE insert_next_levels: ' || l_sup_level.COUNT);
112 hr_utility.TRACE ('INSIDE insert_next_levels: ' || p_level_num);
113 hr_utility.TRACE ('Inserting directs for:' || i.supervisor_name);
114 hr_utility.TRACE ('Inserting direct:' || i.employee_name);
115
116 INSERT INTO per_wpm_plan_hierarchy
117 (wpm_plan_hierarchy_id,
118 plan_id,
119 employee_person_id,
120 employee_name,
121 supervisor_person_id,
122 supervisor_name,
123 level_num
124 )
125 VALUES (per_wpm_plan_hierarchy_s.NEXTVAL,
126 i.plan_id,
127 i.employee_id,
128 i.employee_name,
129 i.supervisor_id,
130 i.supervisor_name,
131 1
132 );
133
134 l_max_level := l_sup_level (l_sup_level.LAST).level_num;
135 l_counter := 0;
136
137 FOR j IN l_sup_level.FIRST .. l_sup_level.LAST
138 LOOP
139 hr_utility.TRACE ('Looping for supervisors for :' || i.employee_name);
140 hr_utility.TRACE ('Supervisor:' || l_sup_level (j).supervisor_name);
141 hr_utility.TRACE ('Supervisor level:' || ((l_max_level - l_counter) + 1));
142
143 INSERT INTO per_wpm_plan_hierarchy
144 (wpm_plan_hierarchy_id,
145 plan_id,
146 employee_person_id,
147 employee_name,
148 supervisor_person_id,
149 supervisor_name,
150 level_num
151 )
152 VALUES (per_wpm_plan_hierarchy_s.NEXTVAL,
153 i.plan_id,
154 i.employee_id,
155 i.employee_name,
156 l_sup_level (j).supervisor_id,
157 l_sup_level (j).supervisor_name,
158 (l_max_level - l_counter) + 1
159 );
160
161 l_counter := l_counter + 1;
162 END LOOP;
163
164 l_last := NVL (l_sup_level.LAST, 0);
165 l_sup_level (l_last + 1).supervisor_id := i.supervisor_id;
166 l_sup_level (l_last + 1).supervisor_name := i.supervisor_name;
167 l_sup_level (l_last + 1).level_num := NVL (p_level_num, 1) + 1;
168 hr_utility.TRACE ('INSIDE insert_next_levels: ' || l_sup_level.COUNT);
169 hr_utility.TRACE ('INSIDE insert_next_levels: ' || l_sup_level (l_last + 1).level_num);
170 insert_next_levels (p_plan_id,
171 i.employee_id,
172 l_sup_level,
173 l_sup_level (l_last + 1).level_num
174 );
175 l_sup_level.DELETE;
176 END LOOP;
177
178 hr_utility.set_location ('Leaving:' || l_proc, 100);
179 END insert_next_levels;
180
181 --
182 --
186 IS
183 PROCEDURE build_hierarchy_for_sc (p_plan_id IN NUMBER, p_sc_id IN NUMBER DEFAULT NULL)
184 IS
185 CURSOR csr_plan_dtls (p_plan_id IN NUMBER)
187 SELECT *
188 FROM per_perf_mgmt_plans pmp
189 WHERE plan_id = p_plan_id;
190
191 --
192 l_pl_rec per_perf_mgmt_plans%ROWTYPE;
193
194 --
195 CURSOR csr_sc_dtls (p_sc_id IN NUMBER)
196 IS
197 SELECT sc.scorecard_id,
198 sc.person_id,
199 sc.assignment_id,
200 ppf.full_name
201 FROM per_personal_scorecards sc, per_people_f ppf
202 WHERE scorecard_id = p_sc_id
203 AND sc.person_id = ppf.person_id
204 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
205
206 --
207 CURSOR csr_all_scs (p_plan_id IN NUMBER)
208 IS
209 SELECT sc.scorecard_id,
210 sc.person_id,
211 sc.assignment_id,
212 ppf.full_name
213 FROM per_personal_scorecards sc, per_people_f ppf
214 WHERE sc.plan_id = p_plan_id
215 AND sc.person_id = ppf.person_id
216 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
217
218 --
219 --
220 CURSOR csr_sc_hrchy (p_plan_id IN NUMBER, p_assignment_id IN NUMBER)
221 IS
222 SELECT level
223 ,e.*
224 FROM (
225 SELECT sc.assignment_id
226 ,paf.person_id child_id
227 ,paf.supervisor_id parent_id
228 ,ppf2.full_name "SUPERVISOR_NAME"
229 ,paf.position_id
230 ,paf.organization_id
231 ,ppf.full_name "EMPLOYEE_NAME"
232 FROM per_people_f ppf
233 ,per_all_people_f ppf2
234 ,per_assignments_f paf
235 ,per_personal_scorecards sc
236 ,per_perf_mgmt_plans pln
237 WHERE sc.plan_id = p_plan_id
238 AND sc.plan_id = pln.plan_id
239 AND sc.assignment_id = paf.assignment_id
240 AND paf.supervisor_id = ppf2.person_id
241 AND paf.person_id = ppf.person_id
242 AND trunc (sysdate) BETWEEN paf.effective_start_date
243 AND paf.effective_end_date
244 AND trunc (sysdate) BETWEEN ppf.effective_start_date
245 AND ppf.effective_end_date
246 AND trunc (sysdate) BETWEEN ppf2.effective_start_date
247 AND ppf2.effective_end_date
248 AND pln.hierarchy_type_code = 'SUP'
249 UNION ALL
250 SELECT sc.assignment_id
251 ,paf.assignment_id child_id
252 ,paf.supervisor_assignment_id parent_id
253 ,ppf2.full_name "SUPERVISOR_NAME"
254 ,paf.position_id
255 ,paf.organization_id
256 ,ppf.full_name "EMPLOYEE_NAME"
257 FROM per_people_f ppf
258 ,per_all_people_f ppf2
259 ,per_assignments_f paf
260 ,per_personal_scorecards sc
261 ,per_perf_mgmt_plans pln
262 WHERE sc.plan_id = p_plan_id
263 AND sc.plan_id = pln.plan_id
264 AND sc.assignment_id = paf.assignment_id
265 AND paf.supervisor_id = ppf2.person_id
266 AND paf.person_id = ppf.person_id
267 AND trunc (sysdate) BETWEEN paf.effective_start_date
268 AND paf.effective_end_date
269 AND trunc (sysdate) BETWEEN ppf.effective_start_date
270 AND ppf.effective_end_date
271 AND trunc (sysdate) BETWEEN ppf2.effective_start_date
272 AND ppf2.effective_end_date
273 AND pln.hierarchy_type_code = 'SUP_ASG'
274 AND paf.supervisor_assignment_id IS NOT NULL
275 UNION ALL
276 SELECT sc.assignment_id
277 ,paf.person_id child_id
278 ,paf.supervisor_id parent_id
279 ,ppf2.full_name "SUPERVISOR_NAME"
280 ,paf.position_id
281 ,paf.organization_id
282 ,ppf.full_name "EMPLOYEE_NAME"
283 FROM per_people_f ppf
284 ,per_all_people_f ppf2
285 ,per_assignments_f paf
286 ,per_personal_scorecards sc
287 ,per_perf_mgmt_plans pln
288 WHERE sc.plan_id = p_plan_id
289 AND sc.plan_id = pln.plan_id
290 AND sc.assignment_id = paf.assignment_id
291 AND paf.supervisor_id = ppf2.person_id
292 AND paf.person_id = ppf.person_id
293 AND trunc (sysdate) BETWEEN paf.effective_start_date
294 AND paf.effective_end_date
295 AND trunc (sysdate) BETWEEN ppf.effective_start_date
296 AND ppf.effective_end_date
297 AND trunc (sysdate) BETWEEN ppf2.effective_start_date
298 AND ppf2.effective_end_date
299 AND pln.hierarchy_type_code NOT IN ('SUP','SUP_ASG')
300 ) e
301 START WITH assignment_id = p_assignment_id
302 CONNECT BY PRIOR parent_id = child_id;
303
304 --
305 TYPE r_sc_hrchy IS RECORD (
306 LEVEL NUMBER (15),
307 assignment_id NUMBER (15),
308 person_id NUMBER (15),
309 supervisor_id NUMBER (15),
310 supervisor_name per_people_f.full_name%TYPE,
311 position_id NUMBER (15),
312 organization_id NUMBER (15),
313 employee_name per_people_f.full_name%TYPE
314 );
315
316 TYPE t_sc_hrchy IS TABLE OF r_sc_hrchy
317 INDEX BY BINARY_INTEGER;
318
322
319 l_sc_hrchy t_sc_hrchy;
320 BEGIN
321 OPEN csr_plan_dtls (p_plan_id);
323 FETCH csr_plan_dtls
324 INTO l_pl_rec;
325
326 CLOSE csr_plan_dtls;
327
328 IF p_sc_id IS NOT NULL
329 THEN
330 FOR i IN csr_sc_dtls (p_sc_id)
331 LOOP
332 l_sc_hrchy.DELETE;
333
334 OPEN csr_sc_hrchy (p_plan_id, i.assignment_id);
335
336 FETCH csr_sc_hrchy
337 BULK COLLECT INTO l_sc_hrchy;
338
339 CLOSE csr_sc_hrchy;
340
341 IF l_sc_hrchy.COUNT > 0
342 THEN
343 FOR j IN l_sc_hrchy.FIRST .. l_sc_hrchy.LAST
344 LOOP
345 IF ( l_sc_hrchy (j).person_id = l_pl_rec.supervisor_id
346 OR l_sc_hrchy (j).assignment_id = l_pl_rec.supervisor_assignment_id
347 OR l_sc_hrchy (j).organization_id = l_pl_rec.top_organization_id
348 OR l_sc_hrchy (j).position_id = l_pl_rec.top_position_id
349 )
350 THEN
351 -- No need to insert anything as this is the top most record in the hierarchy
352 NULL;
353 ELSE
354 INSERT INTO per_wpm_plan_hierarchy
355 (wpm_plan_hierarchy_id,
356 plan_id,
357 employee_person_id,
358 employee_name,
359 supervisor_person_id,
360 supervisor_name,
361 level_num
362 )
363 VALUES (per_wpm_plan_hierarchy_s.NEXTVAL,
364 p_plan_id,
365 i.person_id,
366 i.full_name,
367 l_sc_hrchy (j).supervisor_id,
368 l_sc_hrchy (j).supervisor_name,
369 l_sc_hrchy (j).LEVEL
370 );
371 END IF;
372 END LOOP;
373 END IF; ---count > 0
374 END LOOP;
375 ELSE
376 FOR i IN csr_all_scs (p_plan_id)
377 LOOP
378 l_sc_hrchy.DELETE;
379
380 OPEN csr_sc_hrchy (p_plan_id, i.assignment_id);
381
382 FETCH csr_sc_hrchy
383 BULK COLLECT INTO l_sc_hrchy;
384
385 CLOSE csr_sc_hrchy;
386
387 IF l_sc_hrchy.COUNT > 0
388 THEN
389 FOR j IN l_sc_hrchy.FIRST .. l_sc_hrchy.LAST
390 LOOP
391 IF ( l_sc_hrchy (j).person_id = l_pl_rec.supervisor_id
392 OR l_sc_hrchy (j).assignment_id = l_pl_rec.supervisor_assignment_id
393 OR l_sc_hrchy (j).organization_id = l_pl_rec.top_organization_id
394 OR l_sc_hrchy (j).position_id = l_pl_rec.top_position_id
395 )
396 THEN
397 -- No need to insert anything as this is the top most record in the hierarchy
398 NULL;
399 ELSE
400 INSERT INTO per_wpm_plan_hierarchy
401 (wpm_plan_hierarchy_id,
402 plan_id,
403 employee_person_id,
404 employee_name,
405 supervisor_person_id,
406 supervisor_name,
407 level_num
408 )
409 VALUES (per_wpm_plan_hierarchy_s.NEXTVAL,
410 p_plan_id,
411 i.person_id,
412 i.full_name,
413 l_sc_hrchy (j).supervisor_id,
414 l_sc_hrchy (j).supervisor_name,
415 l_sc_hrchy (j).LEVEL
416 );
417 END IF;
418 END LOOP;
419 END IF; -- count > 0
420 END LOOP;
421 END IF;
422 END build_hierarchy_for_sc;
423
424 --
425 --
426 PROCEDURE populate_plan_hierarchy (p_plan_id IN NUMBER, p_effective_date IN DATE)
427 IS
428 --
429 CURSOR csr_plan_sup_directs (p_plan_id NUMBER, p_effective_date DATE)
430 IS
431 SELECT ppf.full_name employee_name,
432 ppf.person_id employee_id,
433 ppf2.full_name supervisor_name,
434 ppf2.person_id supervisor_id,
435 pmp.plan_id plan_id
436 FROM per_perf_mgmt_plans pmp, per_assignments_f paf, per_people_f ppf, per_people_f ppf2
437 WHERE pmp.plan_id = p_plan_id
438 AND pmp.supervisor_id = paf.supervisor_id
439 AND paf.primary_flag = 'Y'
440 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
441 AND paf.person_id = ppf.person_id
442 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
443 AND paf.supervisor_id = ppf2.person_id
444 AND p_effective_date BETWEEN ppf2.effective_start_date AND ppf2.effective_end_date
445 AND paf.person_id IN (SELECT person_id
449 l_effective_date DATE;
446 FROM per_personal_scorecards
447 WHERE plan_id = p_plan_id);
448
450 l_sup_level t_sup_level;
451 l_proc VARCHAR2 (80) := g_package || 'populate_plan_hierarchy';
452 BEGIN
453 --
454 hr_utility.set_location ('Entering:' || l_proc, 10);
455
456 DELETE per_wpm_plan_hierarchy
457 WHERE plan_id = p_plan_id;
458
459 l_effective_date := NVL (p_effective_date, TRUNC (SYSDATE));
460 /* changed the logic using build_hierarchy_for_sc
461 * FOR i IN csr_plan_sup_directs(p_plan_id,l_effective_date)
462 LOOP
463 l_sup_level(1).supervisor_id := i.supervisor_id;
464 l_sup_level(1).supervisor_name := i.supervisor_name;
465 l_sup_level(1).level_num :=1;
466 INSERT INTO per_wpm_plan_hierarchy
467 (wpm_plan_hierarchy_id
468 ,plan_id
469 ,employee_person_id
470 ,employee_name
471 ,supervisor_person_id
472 ,supervisor_name
473 ,level_num)
474 VALUES (per_wpm_plan_hierarchy_s.nextval
475 ,i.plan_id
476 ,i.employee_id
477 ,i.employee_name
478 ,i.supervisor_id
479 ,i.supervisor_name
480 ,1);
481 insert_next_levels(p_plan_id, i.employee_id,l_sup_level,1);
482 END LOOP;
483 */
484 build_hierarchy_for_sc (p_plan_id => p_plan_id);
485 COMMIT;
486 hr_utility.set_location ('Leaving:' || l_proc, 100);
487 --
488 --
489 END populate_plan_hierarchy;
490
491 --
492 --
493 PROCEDURE submit_refreshapprsummary_cp (
494 p_plan_id IN NUMBER,
495 p_appraisal_period_id IN NUMBER,
496 p_request_id OUT NOCOPY NUMBER
497 )
498 IS
499 --
500 l_request_id NUMBER;
501 l_effective_date VARCHAR2 (30) := fnd_date.date_to_canonical (TRUNC (SYSDATE));
502 --
503 BEGIN
504 -- Submit the request
505 l_request_id :=
506 fnd_request.submit_request (application => 'PER',
507 program => 'PERAPPRSUM',
508 sub_request => FALSE
509 -- ,start_time => l_effective_date
510 ,
511 argument1 => p_plan_id,
512 argument2 => p_appraisal_period_id,
513 argument3 => l_effective_date
514 );
515
516 --
517 IF l_request_id > 0
518 THEN
519 NULL;
520 END IF;
521
522 p_request_id := l_request_id;
523 COMMIT;
524 --
525 END submit_refreshapprsummary_cp;
526
527 PROCEDURE populate_appraisal_summary_cp (
528 errbuf OUT NOCOPY VARCHAR2,
529 retcode OUT NOCOPY NUMBER,
530 p_plan_id IN NUMBER,
531 p_appraisal_period_id IN NUMBER,
532 p_effective_date IN VARCHAR2
533 )
534 IS
535 l_proc VARCHAR2 (80) := g_package || 'populate_appraisal_summary_cp';
536 l_effective_date DATE;
537 BEGIN
538 hr_utility.set_location ('Entering:' || l_proc, 10);
539 l_effective_date :=
540 NVL (fnd_date.canonical_to_date (p_effective_date), TRUNC (SYSDATE));
541 populate_appraisal_summary (p_plan_id => p_plan_id,
542 p_appraisal_period_id => p_appraisal_period_id,
543 p_effective_date => l_effective_date
544 );
545 hr_utility.set_location ('Leaving:' || l_proc, 100);
546 EXCEPTION
547 WHEN OTHERS
548 THEN
549 fnd_file.put_line (fnd_file.LOG, SQLERRM);
550 retcode := 2;
551 errbuf := SQLERRM;
552 hr_utility.set_location ('Leaving:' || l_proc, 110);
553 RAISE;
554 END populate_appraisal_summary_cp;
555
556 --
557 --
558 PROCEDURE compute_summary_for_supervisor (
559 p_plan_id IN NUMBER,
560 p_effective_date IN DATE,
561 p_supervisor_id IN NUMBER,
562 p_supervisor_name IN VARCHAR2,
563 p_appraisal_period_id IN NUMBER,
564 p_appraisal_template_id IN NUMBER
565 )
566 IS
567 --
568 l_proc VARCHAR2 (80) := g_package || 'populate_appraisal_summary';
569
570 --
571 CURSOR csr_direct_summary (
572 p_plan_id NUMBER,
573 p_supervisor_id NUMBER,
574 p_effective_date DATE,
575 p_rating_level_id NUMBER,
576 p_appraisal_template_id NUMBER
577 )
578 IS
579 SELECT COUNT (*)
580 FROM (SELECT pa.appraisal_id
581 FROM per_appraisals pa,
585 per_periods_of_service pps
582 per_wpm_plan_hierarchy wph,
583 per_people_f ppf,
584 per_all_assignments_f paf,
586 WHERE wph.plan_id = p_plan_id
587 AND wph.supervisor_person_id = p_supervisor_id
588 AND wph.level_num = 1
589 AND wph.employee_person_id = ppf.person_id
590 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
591 AND pa.plan_id = p_plan_id
592 AND pa.appraisal_template_id = p_appraisal_template_id
593 AND wph.employee_person_id = pa.appraisee_person_id
594 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
595 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
596 AND ppf.person_id = pps.person_id
597 AND paf.assignment_type = 'E'
598 AND paf.person_id = ppf.person_id
599 AND pps.period_of_service_id = paf.period_of_service_id
600 AND paf.primary_flag = 'Y'
601 AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
602 p_effective_date
603 )
604 AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
605 OR pa.appraisal_period_start_date = pps.date_start
606 )
607 AND pa.overall_performance_level_id = p_rating_level_id
608 UNION
609 SELECT pa.appraisal_id
610 FROM per_appraisals pa,
611 per_wpm_plan_hierarchy wph,
612 per_people_f ppf,
613 per_all_assignments_f paf,
614 per_periods_of_placement pps
615 WHERE wph.plan_id = p_plan_id
616 AND wph.supervisor_person_id = p_supervisor_id
617 AND wph.level_num = 1
618 AND wph.employee_person_id = ppf.person_id
619 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
620 AND pa.plan_id = p_plan_id
621 AND pa.appraisal_template_id = p_appraisal_template_id
622 AND wph.employee_person_id = pa.appraisee_person_id
623 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
624 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
625 AND ppf.person_id = pps.person_id
626 AND paf.assignment_type = 'C'
627 AND paf.person_id = ppf.person_id
628 AND pps.date_start = paf.period_of_placement_date_start
629 AND paf.primary_flag = 'Y'
630 AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
631 p_effective_date
632 )
633 AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
634 OR pa.appraisal_period_start_date = pps.date_start
635 )
636 AND pa.overall_performance_level_id = p_rating_level_id);
637
638 --
639 --
640 CURSOR csr_total_summary (
641 p_plan_id NUMBER,
642 p_supervisor_id NUMBER,
643 p_effective_date DATE,
644 p_rating_level_id NUMBER,
645 p_appraisal_template_id NUMBER
646 )
647 IS
648 SELECT COUNT (*)
649 FROM (SELECT pa.appraisal_id
650 FROM per_appraisals pa,
651 per_wpm_plan_hierarchy wph,
652 per_people_f ppf,
653 per_all_assignments_f paf,
654 per_periods_of_service pps
655 WHERE wph.plan_id = p_plan_id
656 AND wph.supervisor_person_id = p_supervisor_id
657 AND wph.employee_person_id = ppf.person_id
658 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
659 AND pa.plan_id = p_plan_id
660 AND pa.appraisal_template_id = p_appraisal_template_id
661 AND wph.employee_person_id = pa.appraisee_person_id
662 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
663 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
664 AND ppf.person_id = pps.person_id
665 AND paf.assignment_type = 'E'
666 AND paf.person_id = ppf.person_id
667 AND pps.period_of_service_id = paf.period_of_service_id
668 AND paf.primary_flag = 'Y'
669 AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
670 p_effective_date
671 )
672 AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
673 OR pa.appraisal_period_start_date = pps.date_start
674 )
675 AND pa.overall_performance_level_id = p_rating_level_id
676 UNION
677 SELECT pa.appraisal_id
678 FROM per_appraisals pa,
679 per_wpm_plan_hierarchy wph,
680 per_people_f ppf,
681 per_all_assignments_f paf,
682 per_periods_of_placement pps
683 WHERE wph.plan_id = p_plan_id
684 AND wph.supervisor_person_id = p_supervisor_id
685 AND wph.employee_person_id = ppf.person_id
686 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
687 AND pa.plan_id = p_plan_id
688 AND pa.appraisal_template_id = p_appraisal_template_id
689 AND wph.employee_person_id = pa.appraisee_person_id
693 AND paf.assignment_type = 'C'
690 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
691 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
692 AND ppf.person_id = pps.person_id
694 AND paf.person_id = ppf.person_id
695 AND pps.date_start = paf.period_of_placement_date_start
696 AND paf.primary_flag = 'Y'
697 AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
698 p_effective_date
699 )
700 AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
701 OR pa.appraisal_period_start_date = pps.date_start
702 )
703 AND pa.overall_performance_level_id = p_rating_level_id);
704
705
706 --
707 --
708 CURSOR csr_directs_unrated (
709 p_plan_id NUMBER,
710 p_supervisor_id NUMBER,
711 p_effective_date DATE,
712 p_appraisal_template_id NUMBER
713 )
714 IS
715 SELECT COUNT (*)
716 FROM (SELECT pa.appraisal_id
717 FROM per_appraisals pa,
718 per_wpm_plan_hierarchy wph,
719 per_people_f ppf,
720 per_all_assignments_f paf,
721 per_periods_of_service pps
722 WHERE wph.plan_id = p_plan_id
723 AND wph.supervisor_person_id = p_supervisor_id
724 AND wph.level_num = 1
725 AND wph.employee_person_id = ppf.person_id
726 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
727 AND pa.plan_id = p_plan_id
728 AND pa.appraisal_template_id = p_appraisal_template_id
729 AND wph.employee_person_id = pa.appraisee_person_id
730 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
731 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
732 AND ppf.person_id = pps.person_id
733 AND paf.assignment_type = 'E'
734 AND paf.person_id = ppf.person_id
735 AND pps.period_of_service_id = paf.period_of_service_id
736 AND paf.primary_flag = 'Y'
737 AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
738 p_effective_date
739 )
740 AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
741 OR pa.appraisal_period_start_date = pps.date_start
742 )
743 AND pa.overall_performance_level_id IS NULL
744 UNION
745 SELECT pa.appraisal_id
746 FROM per_appraisals pa,
747 per_wpm_plan_hierarchy wph,
748 per_people_f ppf,
749 per_all_assignments_f paf,
750 per_periods_of_placement pps
751 WHERE wph.plan_id = p_plan_id
752 AND wph.supervisor_person_id = p_supervisor_id
753 AND wph.level_num = 1
754 AND wph.employee_person_id = ppf.person_id
755 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
756 AND pa.plan_id = p_plan_id
757 AND pa.appraisal_template_id = p_appraisal_template_id
758 AND wph.employee_person_id = pa.appraisee_person_id
759 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
760 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
761 AND ppf.person_id = pps.person_id
762 AND paf.assignment_type = 'C'
763 AND paf.person_id = ppf.person_id
764 AND pps.date_start = paf.period_of_placement_date_start
765 AND paf.primary_flag = 'Y'
766 AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
767 p_effective_date
768 )
769 AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
770 OR pa.appraisal_period_start_date = pps.date_start
771 )
772 AND pa.overall_performance_level_id IS NULL);
773
774
775 CURSOR csr_total_unrated (
776 p_plan_id NUMBER,
777 p_supervisor_id NUMBER,
778 p_effective_date DATE,
779 p_appraisal_template_id NUMBER
780 )
781 IS
782 SELECT COUNT (*)
783 FROM (SELECT pa.appraisal_id
784 FROM per_appraisals pa,
785 per_wpm_plan_hierarchy wph,
786 per_people_f ppf,
787 per_all_assignments_f paf,
788 per_periods_of_service pps
789 WHERE wph.plan_id = p_plan_id
790 AND wph.supervisor_person_id = p_supervisor_id
791 AND wph.employee_person_id = ppf.person_id
792 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
793 AND pa.plan_id = p_plan_id
794 AND pa.appraisal_template_id = p_appraisal_template_id
795 AND wph.employee_person_id = pa.appraisee_person_id
796 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
797 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
798 AND ppf.person_id = pps.person_id
799 AND paf.assignment_type = 'E'
800 AND paf.person_id = ppf.person_id
804 p_effective_date
801 AND pps.period_of_service_id = paf.period_of_service_id
802 AND paf.primary_flag = 'Y'
803 AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
805 )
806 AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
807 OR pa.appraisal_period_start_date = pps.date_start
808 )
809 AND pa.overall_performance_level_id IS NULL
810 UNION
811 SELECT pa.appraisal_id
812 FROM per_appraisals pa,
813 per_wpm_plan_hierarchy wph,
814 per_people_f ppf,
815 per_all_assignments_f paf,
816 per_periods_of_placement pps
817 WHERE wph.plan_id = p_plan_id
818 AND wph.supervisor_person_id = p_supervisor_id
819 AND wph.employee_person_id = ppf.person_id
820 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
821 AND pa.plan_id = p_plan_id
822 AND pa.appraisal_template_id = p_appraisal_template_id
823 AND wph.employee_person_id = pa.appraisee_person_id
824 AND pa.appraisal_period_end_date = g_appr_period_rec.end_date
825 AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
826 AND ppf.person_id = pps.person_id
827 AND paf.assignment_type = 'C'
828 AND paf.person_id = ppf.person_id
829 AND pps.date_start = paf.period_of_placement_date_start
830 AND paf.primary_flag = 'Y'
831 AND p_effective_date BETWEEN date_start AND NVL (actual_termination_date,
832 p_effective_date
833 )
834 AND ( pa.appraisal_period_start_date = g_appr_period_rec.start_date
835 OR pa.appraisal_period_start_date = pps.date_start
836 )
837 AND pa.overall_performance_level_id IS NULL);
838
839
840 --
841 --
842 TYPE r_rating_summary IS RECORD (
843 rating_level_id NUMBER (15),
844 rating_level_name VARCHAR2 (100),
845 direct_count NUMBER (15),
846 total_count NUMBER (15)
847 );
848
849 TYPE t_rating_summary IS TABLE OF r_rating_summary
850 INDEX BY BINARY_INTEGER;
851
852 l_rating_summary t_rating_summary;
853 l_direct_count NUMBER (15);
854 l_tot_count NUMBER (15);
855 l_total_unrated_count NUMBER (15);
856 l_direct_unrated_count NUMBER (15);
857 --
858 BEGIN
859 --
860 hr_utility.set_location ('Entering:' || l_proc, 10);
861
862 IF p_appraisal_template_id IS NULL OR p_appraisal_period_id IS NULL
863 THEN
864 hr_utility.TRACE ('Appraisal Tempate or Appraisal Period not selected. Returning');
865 RETURN;
866 END IF;
867
868 FOR i IN g_rating_levels.FIRST .. LEAST (g_rating_levels.LAST, 20) -- only upto 20 levels
869 LOOP
870 l_rating_summary (i).rating_level_id := g_rating_levels (i).rating_level_id;
871 l_rating_summary (i).rating_level_name := g_rating_levels (i).level_name;
872
873 OPEN csr_direct_summary (p_plan_id,
874 p_supervisor_id,
875 p_effective_date,
876 g_rating_levels (i).rating_level_id,
877 p_appraisal_template_id
878 );
879
880 FETCH csr_direct_summary
881 INTO l_direct_count;
882
883 CLOSE csr_direct_summary;
884
885 OPEN csr_total_summary (p_plan_id,
886 p_supervisor_id,
887 p_effective_date,
888 g_rating_levels (i).rating_level_id,
889 p_appraisal_template_id
890 );
891
892 FETCH csr_total_summary
893 INTO l_tot_count;
894
895 CLOSE csr_total_summary;
896
897 l_rating_summary (i).direct_count := NVL (l_direct_count, 0);
898 l_rating_summary (i).total_count := NVL (l_tot_count, 0);
899 END LOOP;
900
901 FOR i IN (l_rating_summary.COUNT + 1) .. 20
902 LOOP
903 l_rating_summary (i).rating_level_id := NULL;
904 l_rating_summary (i).rating_level_name := NULL;
905 l_rating_summary (i).direct_count := 0;
906 l_rating_summary (i).total_count := 0;
907 END LOOP;
908
909 OPEN csr_total_unrated (p_plan_id, p_supervisor_id, p_effective_date, p_appraisal_template_id);
910
911 FETCH csr_total_unrated
912 INTO l_total_unrated_count;
913
914 CLOSE csr_total_unrated;
915
916 OPEN csr_directs_unrated (p_plan_id,
917 p_supervisor_id,
918 p_effective_date,
919 p_appraisal_template_id
920 );
921
922 FETCH csr_directs_unrated
923 INTO l_direct_unrated_count;
924
925 CLOSE csr_directs_unrated;
926
927 --
928 INSERT INTO per_wpm_appraisal_summary
929 (wpm_appraisal_summary_id,
933 supervisor_name,
930 plan_id,
931 appraisal_period_id,
932 supervisor_person_id,
934 level_1_id,
935 level_1_name,
936 level_1_direct_count,
937 level_1_total_count,
938 level_2_id,
939 level_2_name,
940 level_2_direct_count,
941 level_2_total_count,
942 level_3_id,
943 level_3_name,
944 level_3_direct_count,
945 level_3_total_count,
946 level_4_id,
947 level_4_name,
948 level_4_direct_count,
949 level_4_total_count,
950 level_5_id,
951 level_5_name,
952 level_5_direct_count,
953 level_5_total_count,
954 level_6_id,
955 level_6_name,
956 level_6_direct_count,
957 level_6_total_count,
958 level_7_id,
959 level_7_name,
960 level_7_direct_count,
961 level_7_total_count,
962 level_8_id,
963 level_8_name,
964 level_8_direct_count,
965 level_8_total_count,
966 level_9_id,
967 level_9_name,
968 level_9_direct_count,
969 level_9_total_count,
970 level_10_id,
971 level_10_name,
972 level_10_direct_count,
973 level_10_total_count,
974 level_11_id,
975 level_11_name,
976 level_11_direct_count,
977 level_11_total_count,
978 level_12_id,
979 level_12_name,
980 level_12_direct_count,
981 level_12_total_count,
982 level_13_id,
983 level_13_name,
984 level_13_direct_count,
985 level_13_total_count,
986 level_14_id,
987 level_14_name,
988 level_14_direct_count,
989 level_14_total_count,
990 level_15_id,
991 level_15_name,
992 level_15_direct_count,
993 level_15_total_count,
994 level_16_id,
995 level_16_name,
996 level_16_direct_count,
997 level_16_total_count,
998 level_17_id,
999 level_17_name,
1000 level_17_direct_count,
1001 level_17_total_count,
1002 level_18_id,
1003 level_18_name,
1004 level_18_direct_count,
1005 level_18_total_count,
1006 level_19_id,
1007 level_19_name,
1008 level_19_direct_count,
1009 level_19_total_count,
1010 level_20_id,
1011 level_20_name,
1012 level_20_direct_count,
1013 level_20_total_count,
1014 norating_direct_count,
1015 norating_total_count
1016 )
1017 VALUES (per_wpm_appraisal_summary_s.NEXTVAL,
1018 p_plan_id,
1019 g_appr_period_rec.appraisal_period_id,
1020 p_supervisor_id,
1021 p_supervisor_name,
1022 l_rating_summary (1).rating_level_id,
1023 l_rating_summary (1).rating_level_name,
1024 l_rating_summary (1).direct_count,
1025 l_rating_summary (1).total_count,
1026 l_rating_summary (2).rating_level_id,
1027 l_rating_summary (2).rating_level_name,
1028 l_rating_summary (2).direct_count,
1029 l_rating_summary (2).total_count,
1030 l_rating_summary (3).rating_level_id,
1031 l_rating_summary (3).rating_level_name,
1032 l_rating_summary (3).direct_count,
1033 l_rating_summary (3).total_count,
1034 l_rating_summary (4).rating_level_id,
1035 l_rating_summary (4).rating_level_name,
1036 l_rating_summary (4).direct_count,
1037 l_rating_summary (4).total_count,
1038 l_rating_summary (5).rating_level_id,
1039 l_rating_summary (5).rating_level_name,
1040 l_rating_summary (5).direct_count,
1041 l_rating_summary (5).total_count,
1042 l_rating_summary (6).rating_level_id,
1043 l_rating_summary (6).rating_level_name,
1044 l_rating_summary (6).direct_count,
1045 l_rating_summary (6).total_count,
1046 l_rating_summary (7).rating_level_id,
1047 l_rating_summary (7).rating_level_name,
1048 l_rating_summary (7).direct_count,
1049 l_rating_summary (7).total_count,
1050 l_rating_summary (8).rating_level_id,
1054 l_rating_summary (9).rating_level_id,
1051 l_rating_summary (8).rating_level_name,
1052 l_rating_summary (8).direct_count,
1053 l_rating_summary (8).total_count,
1055 l_rating_summary (9).rating_level_name,
1056 l_rating_summary (9).direct_count,
1057 l_rating_summary (9).total_count,
1058 l_rating_summary (10).rating_level_id,
1059 l_rating_summary (10).rating_level_name,
1060 l_rating_summary (10).direct_count,
1061 l_rating_summary (10).total_count,
1062 l_rating_summary (11).rating_level_id,
1063 l_rating_summary (11).rating_level_name,
1064 l_rating_summary (11).direct_count,
1065 l_rating_summary (11).total_count,
1066 l_rating_summary (12).rating_level_id,
1067 l_rating_summary (12).rating_level_name,
1068 l_rating_summary (12).direct_count,
1069 l_rating_summary (12).total_count,
1070 l_rating_summary (13).rating_level_id,
1071 l_rating_summary (13).rating_level_name,
1072 l_rating_summary (13).direct_count,
1073 l_rating_summary (13).total_count,
1074 l_rating_summary (14).rating_level_id,
1075 l_rating_summary (14).rating_level_name,
1076 l_rating_summary (14).direct_count,
1077 l_rating_summary (14).total_count,
1078 l_rating_summary (15).rating_level_id,
1079 l_rating_summary (15).rating_level_name,
1080 l_rating_summary (15).direct_count,
1081 l_rating_summary (15).total_count,
1082 l_rating_summary (16).rating_level_id,
1083 l_rating_summary (16).rating_level_name,
1084 l_rating_summary (16).direct_count,
1085 l_rating_summary (16).total_count,
1086 l_rating_summary (17).rating_level_id,
1087 l_rating_summary (17).rating_level_name,
1088 l_rating_summary (17).direct_count,
1089 l_rating_summary (17).total_count,
1090 l_rating_summary (18).rating_level_id,
1091 l_rating_summary (18).rating_level_name,
1092 l_rating_summary (18).direct_count,
1093 l_rating_summary (18).total_count,
1094 l_rating_summary (19).rating_level_id,
1095 l_rating_summary (19).rating_level_name,
1096 l_rating_summary (19).direct_count,
1097 l_rating_summary (19).total_count,
1098 l_rating_summary (20).rating_level_id,
1099 l_rating_summary (20).rating_level_name,
1100 l_rating_summary (20).direct_count,
1101 l_rating_summary (20).total_count,
1102 l_direct_unrated_count,
1103 l_total_unrated_count
1104 );
1105
1106 --
1107 hr_utility.set_location ('Leaving:' || l_proc, 100);
1108 --
1109 END compute_summary_for_supervisor;
1110
1111 --
1112 --
1113 PROCEDURE populate_appraisal_summary (
1114 p_plan_id IN NUMBER,
1115 p_appraisal_period_id IN NUMBER,
1116 p_effective_date IN DATE
1117 )
1118 IS
1119 l_proc VARCHAR2 (80) := g_package || 'populate_appraisal_summary';
1120
1121 --
1122 CURSOR csr_appr_period_dtls (p_appraisal_period_id IN NUMBER)
1123 IS
1124 SELECT pap.appraisal_period_id,
1125 pap.start_date,
1126 pap.end_date,
1127 pat.rating_scale_id,
1128 pat.appraisal_template_id
1129 FROM per_appraisal_periods pap, per_appraisal_templates pat
1130 WHERE pap.appraisal_period_id = p_appraisal_period_id
1131 AND pap.appraisal_template_id = pat.appraisal_template_id;
1132
1133 --
1134 --
1135 CURSOR csr_appr_levels (p_rating_scale_id IN NUMBER)
1136 IS
1137 SELECT rating_level_id,
1138 step_value || '-' || NAME "LEVEL_NAME"
1139 FROM per_rating_levels
1140 WHERE rating_scale_id = p_rating_scale_id
1141 ORDER BY step_value;
1142
1143 --
1144 --
1145 CURSOR csr_plan_managers (p_plan_id IN NUMBER, p_appraisal_period_id IN NUMBER)
1146 IS
1147 SELECT DISTINCT supervisor_person_id,
1148 supervisor_name,
1149 pap.appraisal_period_id,
1150 pap.appraisal_template_id
1151 FROM per_wpm_plan_hierarchy wph, per_appraisal_periods pap
1152 WHERE wph.plan_id = p_plan_id
1153 AND pap.appraisal_period_id = p_appraisal_period_id
1154 AND pap.plan_id = wph.plan_id;
1155 BEGIN
1156 hr_utility.set_location ('Entering:' || l_proc, 10);
1157
1158 OPEN csr_appr_period_dtls (p_appraisal_period_id);
1159
1160 FETCH csr_appr_period_dtls
1161 INTO g_appr_period_rec;
1162
1163 IF csr_appr_period_dtls%NOTFOUND
1164 THEN
1165 fnd_file.put_line (fnd_file.LOG, 'Invalid Appraisal Period selected. Cannot Proceed.');
1166 g_errmsg := 'Invalid Appraisal Period selected. Cannot Proceed.';
1167
1168 --- New message to be created.
1169 CLOSE csr_appr_period_dtls;
1170
1171 RETURN;
1172 END IF;
1173
1174 CLOSE csr_appr_period_dtls;
1175
1176 DELETE per_wpm_appraisal_summary
1177 WHERE plan_id = p_plan_id
1178 AND appraisal_period_id = g_appr_period_rec.appraisal_period_id;
1179
1180 COMMIT;
1181 g_rating_levels.DELETE;
1182
1183 OPEN csr_appr_levels (g_appr_period_rec.rating_scale_id);
1184
1185 FETCH csr_appr_levels
1186 BULK COLLECT INTO g_rating_levels;
1187
1188 CLOSE csr_appr_levels;
1189
1190 FOR i IN csr_plan_managers (p_plan_id, p_appraisal_period_id)
1191 LOOP
1192 hr_utility.TRACE ('Computing totals for : ' || i.supervisor_name);
1193 compute_summary_for_supervisor (p_plan_id => p_plan_id,
1194 p_effective_date => p_effective_date,
1195 p_supervisor_id => i.supervisor_person_id,
1196 p_supervisor_name => i.supervisor_name,
1197 p_appraisal_period_id => i.appraisal_period_id,
1198 p_appraisal_template_id => i.appraisal_template_id
1199 );
1200 END LOOP;
1201
1202 /*
1203 create a row with supervisor_person_id as -1 to store the run_date into the
1204 supervisor_name column in canonical format. level_1_id will store the request Id.
1205 */
1206 INSERT INTO per_wpm_appraisal_summary
1207 (wpm_appraisal_summary_id,
1208 plan_id,
1209 appraisal_period_id,
1210 supervisor_person_id,
1211 supervisor_name,
1212 level_1_id
1213 )
1214 VALUES (per_wpm_appraisal_summary_s.NEXTVAL,
1215 p_plan_id,
1216 g_appr_period_rec.appraisal_period_id,
1217 -1,
1218 fnd_date.date_to_canonical (SYSDATE),
1219 fnd_global.conc_request_id
1220 );
1221
1222 --
1223 COMMIT;
1224 --
1225 hr_utility.set_location ('Leaving:' || l_proc, 100);
1226 END populate_appraisal_summary;
1227
1228 --
1229 --
1230 FUNCTION get_summary_date (p_plan_id IN NUMBER, p_appraisal_period_id IN NUMBER)
1231 RETURN DATE
1232 IS
1233 CURSOR csr_get_date (p_plan_id NUMBER, p_period_id NUMBER)
1234 IS
1235 SELECT NVL (fnd_date.canonical_to_date (supervisor_name), SYSDATE)
1236 -- supervisor_name is used to store the run date with id as -1.
1237 FROM per_wpm_appraisal_summary
1238 WHERE plan_id = p_plan_id AND appraisal_period_id = p_period_id
1239 AND supervisor_person_id = -1;
1240
1241 l_summary_date DATE;
1242 BEGIN
1243 OPEN csr_get_date (p_plan_id, p_appraisal_period_id);
1244
1245 FETCH csr_get_date
1246 INTO l_summary_date;
1247
1248 CLOSE csr_get_date;
1249
1250 RETURN l_summary_date;
1251 END get_summary_date;
1252 END per_wpm_summary_pkg;