1 PACKAGE BODY hr_wpm_util AS
2 /* $Header: hrwpmutl.pkb 120.38.12020000.4 2012/10/18 12:59:55 venksund ship $*/
3 CURSOR get_latest_appraisal_info (p_person_id IN NUMBER)
4 IS
5 SELECT appraisal_id,
6 overall_performance_level_id,
7 appraisal_date,
8 appraisal_system_status
9 FROM per_appraisals
10 WHERE appraisee_person_id = p_person_id AND appraisal_date <= TRUNC (SYSDATE)
11 ORDER BY appraisal_date DESC;
12
13 TYPE get_appraisal_info_rec IS RECORD (
14 appraisal_id per_appraisals.appraisal_id%TYPE,
15 overall_performance_level_id per_appraisals.overall_performance_level_id%TYPE,
16 appraisal_date per_appraisals.appraisal_date%TYPE,
17 appraisal_system_status per_appraisals.appraisal_system_status%TYPE
18 );
19
20 FUNCTION get_latest_appraisal_id (p_person_id IN NUMBER)
21 RETURN NUMBER
22 IS
23 l_rec get_appraisal_info_rec;
24 l_appraisal_id NUMBER;
25 BEGIN
26 OPEN get_latest_appraisal_info (p_person_id);
27
28 FETCH get_latest_appraisal_info
29 INTO l_rec;
30
31 IF get_latest_appraisal_info%FOUND
32 THEN
33 l_appraisal_id := l_rec.appraisal_id;
34 END IF;
35
36 CLOSE get_latest_appraisal_info;
37
38 RETURN l_appraisal_id;
39 EXCEPTION
40 WHEN OTHERS
41 THEN
42 RETURN NULL;
43 END;
44
45 FUNCTION get_latest_appraisal_rating (p_person_id IN NUMBER)
46 RETURN VARCHAR2
47 IS
48 CURSOR get_appraisal_rating (l_overall_performance_level_id NUMBER)
49 IS
50 SELECT step_value || ' - ' || NAME "APPRAISAL_RATING"
51 FROM per_rating_levels
52 WHERE rating_level_id = l_overall_performance_level_id;
53
54 l_rec get_appraisal_info_rec;
55 l_overall_performance_level_id NUMBER;
56 RESULT VARCHAR2 (100);
57 BEGIN
58 OPEN get_latest_appraisal_info (p_person_id);
59
60 FETCH get_latest_appraisal_info
61 INTO l_rec;
62
63 IF get_latest_appraisal_info%FOUND
64 THEN
65 l_overall_performance_level_id := l_rec.overall_performance_level_id;
66 END IF;
67
68 CLOSE get_latest_appraisal_info;
69
70 IF l_overall_performance_level_id IS NOT NULL
71 THEN
72 OPEN get_appraisal_rating (l_overall_performance_level_id);
73
74 FETCH get_appraisal_rating
75 INTO RESULT;
76
77 CLOSE get_appraisal_rating;
78 END IF;
79
80 RETURN RESULT;
81 EXCEPTION
82 WHEN OTHERS
83 THEN
84 RETURN NULL;
85 END;
86
87 FUNCTION get_latest_appraisal_date (p_person_id IN NUMBER)
88 RETURN DATE
89 IS
90 l_rec get_appraisal_info_rec;
91 l_appraisal_date DATE;
92 BEGIN
93 OPEN get_latest_appraisal_info (p_person_id);
94
95 FETCH get_latest_appraisal_info
96 INTO l_rec;
97
98 IF get_latest_appraisal_info%FOUND
99 THEN
100 l_appraisal_date := l_rec.appraisal_date;
101 END IF;
102
103 CLOSE get_latest_appraisal_info;
104
105 RETURN l_appraisal_date;
106 EXCEPTION
107 WHEN OTHERS
108 THEN
109 RETURN NULL;
110 END;
111
112 FUNCTION get_latest_appraisal_status (p_person_id IN NUMBER)
113 RETURN VARCHAR2
114 IS
115 CURSOR get_appraisal_status (l_appraisal_system_status VARCHAR2)
116 IS
117 SELECT meaning
118 FROM hr_lookups
119 WHERE lookup_type = 'APPRAISAL_SYSTEM_STATUS' AND lookup_code = l_appraisal_system_status;
120
121 l_rec get_appraisal_info_rec;
122 l_appraisal_system_status VARCHAR2 (80);
123 RESULT VARCHAR2 (80) DEFAULT NULL;
124 BEGIN
125 OPEN get_latest_appraisal_info (p_person_id);
126
127 FETCH get_latest_appraisal_info
128 INTO l_rec;
129
130 IF get_latest_appraisal_info%FOUND
131 THEN
132 l_appraisal_system_status := l_rec.appraisal_system_status;
133 END IF;
134
135 CLOSE get_latest_appraisal_info;
136
137 IF l_appraisal_system_status IS NOT NULL
138 THEN
139 OPEN get_appraisal_status (l_appraisal_system_status);
140
141 FETCH get_appraisal_status
142 INTO RESULT;
143
144 CLOSE get_appraisal_status;
145 END IF;
146
147 RETURN RESULT;
148 EXCEPTION
149 WHEN OTHERS
150 THEN
151 RETURN NULL;
152 END;
153
154 FUNCTION is_appraisal_started (p_plan_id IN per_perf_mgmt_plans.plan_id%TYPE)
155 RETURN VARCHAR2
156 IS
157 CURSOR get_current_plan_appraisals (c_plan_id per_perf_mgmt_plans.plan_id%TYPE)
158 IS
159 SELECT DISTINCT 'Y' AS if_current
160 FROM per_appraisal_periods
161 WHERE plan_id = c_plan_id
162 AND TRUNC (SYSDATE) BETWEEN NVL (task_start_date, SYSDATE)
163 AND NVL (task_end_date, SYSDATE);
164
165 RESULT VARCHAR2 (1) DEFAULT NULL;
166 BEGIN
167 OPEN get_current_plan_appraisals (p_plan_id);
168
169 FETCH get_current_plan_appraisals
170 INTO RESULT;
171
172 CLOSE get_current_plan_appraisals;
173
174 RETURN RESULT;
175 EXCEPTION
176 WHEN OTHERS
177 THEN
178 RAISE;
179 END;
180
181 ------
182 -- Function to return the LOS icon enabled/disabled from view and track objective.
183 ------
184 FUNCTION is_los_enabled (
185 p_obj_id IN per_objectives.objective_id%TYPE,
186 p_align_id IN per_objectives.aligned_with_objective_id%TYPE
187 )
188 RETURN VARCHAR2
189 IS
190 l_up_hierarchy_enable VARCHAR2 (1);
191 l_down_hierarchy_enable VARCHAR2 (1);
192 RESULT VARCHAR2 (1) DEFAULT NULL;
193 BEGIN
194 l_up_hierarchy_enable := is_up_hierarchy_enabled (p_align_id);
195 l_down_hierarchy_enable := is_down_hierarchy_enabled (p_obj_id);
196
197 IF (l_up_hierarchy_enable = 'Y' OR l_down_hierarchy_enable = 'Y')
198 THEN
199 RESULT := 'Y';
200 END IF;
201
202 RETURN RESULT;
203 EXCEPTION
204 WHEN OTHERS
205 THEN
206 RAISE;
207 END;
208
209 ------
210 -- Function to return if there is objective hierarchy DOWN the LOS
211 ------
212 FUNCTION is_down_hierarchy_enabled (p_obj_id IN per_objectives.objective_id%TYPE)
213 RETURN VARCHAR2
214 IS
215 CURSOR get_objectives_down (c_obj_id IN per_objectives.objective_id%TYPE)
216 IS
217 SELECT 'Y'
218 FROM DUAL
219 WHERE EXISTS (SELECT 'X'
220 FROM per_objectives
221 WHERE aligned_with_objective_id = c_obj_id); -- 8789635 bug fix changes
222
223 RESULT VARCHAR2 (1) DEFAULT NULL;
224 BEGIN
225 OPEN get_objectives_down (p_obj_id);
226
227 FETCH get_objectives_down
228 INTO RESULT;
229
230 CLOSE get_objectives_down;
231
232 RETURN RESULT;
233 EXCEPTION
234 WHEN OTHERS
235 THEN
236 RAISE;
237 END;
238
239 ------
240 -- Function to return if there is objective hierarchy UP the LOS
241 ------
242 FUNCTION is_up_hierarchy_enabled (p_align_id IN per_objectives.objective_id%TYPE)
243 RETURN VARCHAR2
244 IS
245 CURSOR get_objectives_up (c_align_id IN per_objectives.aligned_with_objective_id%TYPE)
246 IS
247 SELECT DISTINCT 'Y' AS enabled
248 FROM per_objectives
249 WHERE objective_id = c_align_id;
250
251 RESULT VARCHAR2 (1) DEFAULT NULL;
252 BEGIN
253 OPEN get_objectives_up (p_align_id);
254
255 FETCH get_objectives_up
256 INTO RESULT;
257
258 CLOSE get_objectives_up;
259
260 RETURN RESULT;
261 EXCEPTION
262 WHEN OTHERS
263 THEN
264 RAISE;
265 END;
266
267 FUNCTION enable_share_for_topsupervisor (
268 p_planid IN per_perf_mgmt_plans.plan_id%TYPE,
269 p_personid IN per_personal_scorecards.person_id%TYPE,
270 p_lookupcode IN hr_lookups.lookup_code%TYPE
271 )
272 RETURN VARCHAR2
273 IS
274 RESULT VARCHAR2 (1) := 'N';
275 l_personid NUMBER (15, 0) := -1;
276 BEGIN
277 SELECT supervisor_id
278 INTO l_personid
279 FROM per_perf_mgmt_plans
280 WHERE plan_id = p_planid;
281
282 IF p_personid = l_personid AND p_lookupcode = '3_SHA'
283 THEN
284 RESULT := 'Y';
285 END IF;
286
287 RETURN RESULT;
288 END;
289
290 FUNCTION get_value_for_9box (p_person_id IN NUMBER, p_effective_date IN DATE, p_type IN VARCHAR2)
291 RETURN NUMBER
292 IS
293 l_value NUMBER (15) := 0;
294 l_performance NUMBER (15);
295 l_potential NUMBER (15);
296 l_retention NUMBER (15);
297 l_iol NUMBER (15);
298 BEGIN
299 l_performance := get_performance_for_9box (p_person_id, p_effective_date);
300 l_potential := get_potential_for_9box (p_person_id, p_effective_date);
301 l_retention := get_retention_for_9box (p_person_id, p_effective_date);
302 l_iol := get_iol_for_9box (p_person_id, p_effective_date);
303
304 IF l_performance > 0
305 THEN
306 IF (p_type = 'POT' AND l_potential > 0)
307 THEN
308 l_value := ((l_potential - 1) * 3 + l_performance);
309 ELSIF (p_type = 'RET' AND l_retention > 0)
310 THEN
311 l_value := ((l_retention - 1) * 3 + l_performance);
312 ELSIF(p_type = 'IOL' AND l_iol > 0)
313 THEN
314 l_value := ((l_retention - 1) * 3 + l_iol);
315 END IF;
316 END IF;
317
318 IF ( l_value < 0 OR l_value > 9) THEN
319 l_value := 0;
320 END IF;
321
322 RETURN l_value;
323 END get_value_for_9box;
324
325 FUNCTION get_potential_for_9box (p_person_id IN NUMBER, p_effective_date IN DATE)
326 RETURN NUMBER
327 IS
328 l_eit_potential VARCHAR2 (30);
329 l_num_potential NUMBER (15);
330
331 CURSOR csr_pot_new_eit (p_person_id NUMBER, p_effective_date DATE)
332 IS
333 SELECT potential
334 FROM (SELECT pei_information1 potential,
335 fnd_date.canonical_to_date (pei_information5) start_date,
336 fnd_date.canonical_to_date (pei_information6) end_date,
337 fnd_date.canonical_to_date (pei_information8) completion_date
338 FROM per_people_extra_info
339 WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_MGMT_INFO')
340 WHERE (NVL (start_date, p_effective_date) <= p_effective_date)
341 ORDER BY NVL (completion_date, start_date) DESC;
342
343 --
344 CURSOR csr_pot_old_eit (p_person_id NUMBER, p_effective_date DATE)
345 IS
346 SELECT pei_information1 potential
347 FROM per_people_extra_info
348 WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_PLANNING';
349
350 --
351 CURSOR csr_9box_new_potential (p_potential VARCHAR2)
352 IS
353 SELECT information1 --- return from BG specific. If not defined then Global
354 FROM per_shared_types
355 WHERE lookup_type = 'READINESS_LEVEL'
356 AND system_type_cd = p_potential
357 AND business_group_id = hr_general.get_business_group_id
358 UNION ALL
359 SELECT information1
360 FROM per_shared_types
361 WHERE lookup_type = 'READINESS_LEVEL'
362 AND system_type_cd = p_potential
363 AND business_group_id IS NULL;
364
365 --
366 CURSOR csr_9box_old_potential (p_potential VARCHAR2)
367 IS
368 SELECT information1 --- return from BG specific. If not defined then Global
369 FROM per_shared_types
370 WHERE lookup_type = 'PER_SUCC_PLAN_POTENTIAL'
371 AND system_type_cd = p_potential
372 AND business_group_id = hr_general.get_business_group_id
373 UNION ALL
374 SELECT information1
375 FROM per_shared_types
376 WHERE lookup_type = 'PER_SUCC_PLAN_POTENTIAL'
377 AND system_type_cd = p_potential
378 AND business_group_id IS NULL;
379 BEGIN
380 IF NVL (fnd_profile.VALUE ('HR_SUCCESSION_MGMT_LICENSED'), 'N') = 'Y'
381 THEN
382 OPEN csr_pot_new_eit (p_person_id, p_effective_date);
383
384 FETCH csr_pot_new_eit
385 INTO l_eit_potential;
386
387 CLOSE csr_pot_new_eit;
388
389 IF l_eit_potential IS NULL
390 THEN
391 l_num_potential := -1; --- not found
392 --
393 RETURN l_num_potential;
394 END IF;
395
396 OPEN csr_9box_new_potential (l_eit_potential);
397
398 FETCH csr_9box_new_potential
399 INTO l_num_potential;
400
401 CLOSE csr_9box_new_potential;
402
403 IF l_num_potential IS NULL
404 THEN
405 l_num_potential := -2; -- Shared type not setup
406 END IF;
407
408 RETURN l_num_potential;
409 ELSE
410 OPEN csr_pot_old_eit (p_person_id, p_effective_date);
411
412 FETCH csr_pot_old_eit
413 INTO l_eit_potential;
414
415 CLOSE csr_pot_old_eit;
416
417 IF l_eit_potential IS NULL
418 THEN
419 l_num_potential := -1; --- not found
420 --
421 RETURN l_num_potential;
422 END IF;
423
424 OPEN csr_9box_old_potential (l_eit_potential);
425
426 FETCH csr_9box_old_potential
427 INTO l_num_potential;
428
429 CLOSE csr_9box_old_potential;
430
431 IF l_num_potential IS NULL
432 THEN
433 l_num_potential := -2; -- Shared type not setup
434 END IF;
435
436 RETURN l_num_potential;
437 END IF;
438 --
439 END get_potential_for_9box;
440
441 --
442 FUNCTION get_performance_for_9box (p_person_id IN NUMBER, p_effective_date IN DATE)
443 RETURN NUMBER
444 IS
445 l_eit_performance VARCHAR2 (30);
446 l_num_performance NUMBER (15);
447
448 CURSOR csr_performance (p_person_id NUMBER, p_effective_date DATE)
449 IS
450 SELECT performance_rating
451 FROM per_performance_reviews
452 WHERE person_id = p_person_id AND review_date <= p_effective_date
453 ORDER BY review_date DESC;
454
455 CURSOR csr_9box_perf (p_perf VARCHAR2)
456 IS
457 SELECT information1 --- return from BG specific. If not defined then
458 GLOBAL
459 FROM per_shared_types
460 WHERE lookup_type = 'PERFORMANCE_RATING'
461 AND system_type_cd = p_perf
462 AND NVL (business_group_id, -1) =
463 NVL2 (business_group_id, hr_general.get_business_group_id,
464 -1)
465 UNION ALL
466 SELECT information1
467 FROM per_shared_types
468 WHERE lookup_type = 'PERFORMANCE_RATING'
469 AND system_type_cd = p_perf
470 AND business_group_id IS NULL;
471 BEGIN
472 OPEN csr_performance (p_person_id, p_effective_date);
473
474 FETCH csr_performance
475 INTO l_eit_performance;
476
477 CLOSE csr_performance;
478
479 IF l_eit_performance IS NULL
480 THEN
481 l_num_performance := -1; --- not found
482 --
483 RETURN l_num_performance;
484 END IF;
485
486 OPEN csr_9box_perf (l_eit_performance);
487
488 FETCH csr_9box_perf
489 INTO l_num_performance;
490
491 CLOSE csr_9box_perf;
492
493 IF l_num_performance IS NULL
494 THEN
495 l_num_performance := -2; -- Shared type not setup
496 END IF;
497
498 RETURN l_num_performance;
499 --
500 END get_performance_for_9box;
501
502 --
503 FUNCTION get_retention_for_9box (p_person_id IN NUMBER, p_effective_date IN DATE)
504 RETURN NUMBER
505 IS
506 l_eit_retention VARCHAR2 (30);
507 l_num_retention NUMBER (15);
508
509 CURSOR csr_ret_new_eit (p_person_id NUMBER, p_effective_date DATE)
510 IS
511 SELECT RETENTION
512 FROM (SELECT pei_information4 RETENTION,
513 fnd_date.canonical_to_date (pei_information5) start_date,
514 fnd_date.canonical_to_date (pei_information6) end_date,
515 fnd_date.canonical_to_date (pei_information8) completion_date
516 FROM per_people_extra_info
517 WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_MGMT_INFO')
518 WHERE (NVL (start_date, p_effective_date) <= p_effective_date)
519 ORDER BY NVL (completion_date, start_date) DESC;
520
521 --
522 CURSOR csr_ret_old_eit (p_person_id NUMBER, p_effective_date DATE)
523 IS
524 SELECT pei_information2 RETENTION
525 FROM per_people_extra_info
526 WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_PLANNING';
527
528 --
529 CURSOR csr_9box_new_retention (p_retention VARCHAR2)
530 IS
531 SELECT information1 --- return from BG specific. If not defined then Global
532 FROM per_shared_types
533 WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
534 AND system_type_cd = p_retention
535 AND business_group_id = hr_general.get_business_group_id
536 UNION ALL
537 SELECT information1
538 FROM per_shared_types
539 WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
540 AND system_type_cd = p_retention
541 AND business_group_id IS NULL;
542
543 --
544 CURSOR csr_9box_old_retention (p_retention VARCHAR2)
545 IS
546 SELECT information1 --- return from BG specific. If not defined then Global
547 FROM per_shared_types
548 WHERE lookup_type = 'PER_SUCC_PLAN_RISK_LEVEL'
549 AND system_type_cd = p_retention
550 AND business_group_id = hr_general.get_business_group_id
551 UNION ALL
552 SELECT information1
553 FROM per_shared_types
554 WHERE lookup_type = 'PER_SUCC_PLAN_RISK_LEVEL'
555 AND system_type_cd = p_retention
556 AND business_group_id IS NULL;
557 BEGIN
558 IF NVL (fnd_profile.VALUE ('HR_SUCCESSION_MGMT_LICENSED'), 'N') = 'Y'
559 THEN
560 OPEN csr_ret_new_eit (p_person_id, p_effective_date);
561
562 FETCH csr_ret_new_eit
563 INTO l_eit_retention;
564
565 CLOSE csr_ret_new_eit;
566
567 IF l_eit_retention IS NULL
568 THEN
569 l_num_retention := -1; --- not found
570 --
571 RETURN l_num_retention;
572 END IF;
573
574 OPEN csr_9box_new_retention (l_eit_retention);
575
576 FETCH csr_9box_new_retention
577 INTO l_num_retention;
578
579 CLOSE csr_9box_new_retention;
580
581 IF l_num_retention IS NULL
582 THEN
583 l_num_retention := -2; -- Shared type not setup
584 END IF;
585
586 RETURN l_num_retention;
587 ELSE
588 OPEN csr_ret_old_eit (p_person_id, p_effective_date);
589
590 FETCH csr_ret_old_eit
591 INTO l_eit_retention;
592
593 CLOSE csr_ret_old_eit;
594
595 IF l_eit_retention IS NULL
596 THEN
597 l_num_retention := -1; --- not found
598 --
599 RETURN l_num_retention;
600 END IF;
601
602 OPEN csr_9box_old_retention (l_eit_retention);
603
604 FETCH csr_9box_old_retention
605 INTO l_num_retention;
606
607 CLOSE csr_9box_old_retention;
608
609 IF l_num_retention IS NULL
610 THEN
611 l_num_retention := -2; -- Shared type not setup
612 END IF;
613
614 RETURN l_num_retention;
615 END IF;
616 --
617 END get_retention_for_9box;
618
619 --
620 PROCEDURE get_9box_details_for_person (
621 p_person_id IN NUMBER,
622 p_effective_date IN DATE,
623 p_get_performance IN VARCHAR2 DEFAULT 'Y',
624 p_get_potential IN VARCHAR2 DEFAULT 'Y',
625 p_get_retention IN VARCHAR2 DEFAULT 'Y',
626 p_performance OUT NOCOPY NUMBER,
627 p_potential OUT NOCOPY NUMBER,
628 p_retention OUT NOCOPY NUMBER
629 )
630 IS
631 BEGIN
632 IF p_get_performance = 'Y'
633 THEN
634 p_performance := get_performance_for_9box (p_person_id, p_effective_date);
635 END IF;
636
637 IF p_get_potential = 'Y'
638 THEN
639 p_potential := get_potential_for_9box (p_person_id, p_effective_date);
640 END IF;
641
642 IF p_get_retention = 'Y'
643 THEN
644 p_retention := get_retention_for_9box (p_person_id, p_effective_date);
645 END IF;
646 END get_9box_details_for_person;
647
648 -- new function added for bug9849172 - schowdhu
649 FUNCTION get_potential_for_9box (
650 p_person_id IN NUMBER,
651 p_potential IN VARCHAR2,
652 p_performance IN NUMBER
653 )
654 RETURN NUMBER
655 IS
656 l_num_potential NUMBER (15);
657 l_performance NUMBER (15);
658
659 CURSOR csr_9box_potential (p_potential VARCHAR2)
660 IS
661 SELECT information1 --- return from BG specific. If not defined then Global
662 FROM per_shared_types
663 WHERE lookup_type = 'READINESS_LEVEL'
664 AND system_type_cd = p_potential
665 AND business_group_id = hr_general.get_business_group_id
666 UNION ALL
667 SELECT information1
668 FROM per_shared_types
669 WHERE lookup_type = 'READINESS_LEVEL'
670 AND system_type_cd = p_potential
671 AND business_group_id IS NULL;
672
673 BEGIN
674 l_performance := p_performance;
675
676 IF (p_performance IS NULL OR l_performance < 0)
677 THEN
678 l_performance := get_performance_for_9box (p_person_id, TRUNC (SYSDATE));
679 END IF;
680
681 IF (l_performance IS NULL OR l_performance < 0)
682 THEN
683 RETURN 0; -- not shown in Perf Matrix for value 0
684 END IF;
685
686 IF(p_potential IS NULL)
687 THEN
688 l_num_potential := get_potential_for_9box (p_person_id, TRUNC (SYSDATE));
689 ELSE
690 OPEN csr_9box_potential (p_potential);
691
692 FETCH csr_9box_potential
693 INTO l_num_potential;
694
695 CLOSE csr_9box_potential;
696 END IF;
697
698 IF l_num_potential IS NULL OR l_num_potential < 0
699 THEN
700 RETURN 0; -- Shared type not setup
701 END IF;
702
703 RETURN ((l_num_potential - 1) * 3 + l_performance);
704 END get_potential_for_9box;
705
706 FUNCTION get_iol_for_9box (p_person_id IN NUMBER, p_effective_date IN DATE)
707 RETURN NUMBER
708 IS
709 l_eit_iol VARCHAR2 (30);
710 l_num_iol NUMBER (15);
711
712 CURSOR csr_iol_eit (p_person_id NUMBER, p_effective_date DATE)
713 IS
714 SELECT iol
715 FROM (SELECT pei_information9 iol,
716 fnd_date.canonical_to_date (pei_information5) start_date,
717 fnd_date.canonical_to_date (pei_information6) end_date,
718 fnd_date.canonical_to_date (pei_information8) completion_date
719 FROM per_people_extra_info
720 WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_MGMT_INFO')
721 WHERE (NVL (start_date, p_effective_date) <= p_effective_date)
722 ORDER BY NVL (completion_date, start_date) DESC;
723
724 --
725 CURSOR csr_9box_iol (p_iol VARCHAR2)
726 IS
727 SELECT information1 --- return from BG specific. If not defined then Global
728 FROM per_shared_types
729 WHERE lookup_type = 'PER_SP_IMPACT_OF_LOSS'
730 AND system_type_cd = p_iol
731 AND business_group_id = hr_general.get_business_group_id
732 UNION ALL
733 SELECT information1
734 FROM per_shared_types
735 WHERE lookup_type = 'PER_SP_IMPACT_OF_LOSS'
736 AND system_type_cd = p_iol
737 AND business_group_id IS NULL;
738 BEGIN
739 OPEN csr_iol_eit (p_person_id, p_effective_date);
740
741 FETCH csr_iol_eit
742 INTO l_eit_iol;
743
744 CLOSE csr_iol_eit;
745
746 IF l_eit_iol IS NULL
747 THEN
748 l_num_iol := -1; --- not found
749 --
750 RETURN l_num_iol;
751 END IF;
752
753 OPEN csr_9box_iol (l_eit_iol);
754
755 FETCH csr_9box_iol
756 INTO l_num_iol;
757
758 CLOSE csr_9box_iol;
759
760 IF l_num_iol IS NULL
761 THEN
762 l_num_iol := -2; -- Shared type not setup
763 END IF;
764
765 RETURN l_num_iol;
766 --
767 END get_iol_for_9box;
768
769 --Added this function for bug 13731815
770 FUNCTION get_performance_for_9box (p_perf IN VARCHAR2)
771 RETURN NUMBER
772 IS
773 l_num_performance NUMBER (15);
774
775 CURSOR csr_9box_perf (p_perf VARCHAR2)
776 IS
777 SELECT information1 --- return from BG specific. If not defined then
778 GLOBAL
779 FROM per_shared_types
780 WHERE lookup_type = 'PERFORMANCE_RATING'
781 AND system_type_cd = p_perf
782 AND NVL (business_group_id, -1) =
783 NVL2 (business_group_id, hr_general.get_business_group_id,
784 -1)
785 UNION ALL
786 SELECT information1
787 FROM per_shared_types
788 WHERE lookup_type = 'PERFORMANCE_RATING'
789 AND system_type_cd = p_perf
790 AND business_group_id IS NULL;
791 BEGIN
792
793 IF p_perf IS NULL
794 THEN
795 l_num_performance := -1; --- not found
796 --
797 RETURN l_num_performance;
798 END IF;
799
800 OPEN csr_9box_perf (p_perf);
801
802 FETCH csr_9box_perf
803 INTO l_num_performance;
804
805 CLOSE csr_9box_perf;
806
807 IF l_num_performance IS NULL
808 THEN
809 l_num_performance := -2; -- Shared type not setup
810 END IF;
811
812 RETURN l_num_performance;
813 --
814 END get_performance_for_9box;
815
816 -- Added this function for bug 13731815
817 FUNCTION get_retention_for_9box (
818 p_person_id IN NUMBER,
819 p_retention IN VARCHAR2,
820 p_performance IN NUMBER
821 )
822 RETURN NUMBER
823 IS
824 l_num_retention NUMBER (15);
825 l_performance NUMBER (15);
826
827 CURSOR csr_9box_retention (p_retention VARCHAR2)
828 IS
829 SELECT information1 --- return from BG specific. If not defined then Global
830 FROM per_shared_types
831 WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
832 AND system_type_cd = p_retention
833 AND business_group_id = hr_general.get_business_group_id
834 UNION ALL
835 SELECT information1
836 FROM per_shared_types
837 WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
838 AND system_type_cd = p_retention
839 AND business_group_id IS NULL;
840
841 BEGIN
842 l_performance := p_performance;
843
844 IF (p_performance IS NULL OR l_performance < 0)
845 THEN
846 l_performance := get_performance_for_9box (p_person_id, TRUNC (SYSDATE));
847 END IF;
848
849 IF (l_performance IS NULL OR l_performance < 0)
850 THEN
851 RETURN 0; -- not shown in Perf Matrix for value 0
852 END IF;
853
854 IF(p_retention IS NULL)
855 THEN
856 l_num_retention := get_retention_for_9box (p_person_id, TRUNC (SYSDATE));
857 ELSE
858
859 OPEN csr_9box_retention (p_retention);
860
861 FETCH csr_9box_retention
862 INTO l_num_retention;
863
864 CLOSE csr_9box_retention;
865 END IF;
866
867 IF l_num_retention IS NULL OR l_num_retention < 0
868 THEN
869 RETURN 0; -- Shared type not setup
870 END IF;
871
872 RETURN ((l_num_retention - 1) * 3 + l_performance);
873 END get_retention_for_9box;
874
875 -- Added this function for bug 13731815
876 FUNCTION get_iol_for_9box (
877 p_person_id IN NUMBER,
878 p_iol IN VARCHAR2,
879 p_retention IN VARCHAR2
880 )
881 RETURN NUMBER
882 IS
883 l_num_iol NUMBER (15);
884 l_num_retention NUMBER (15);
885
886 CURSOR csr_9box_iol (p_iol VARCHAR2)
887 IS
888 SELECT information1 --- return from BG specific. If not defined then Global
889 FROM per_shared_types
890 WHERE lookup_type = 'PER_SP_IMPACT_OF_LOSS'
891 AND system_type_cd = p_iol
892 AND business_group_id = hr_general.get_business_group_id
893 UNION ALL
894 SELECT information1
895 FROM per_shared_types
896 WHERE lookup_type = 'PER_SP_IMPACT_OF_LOSS'
897 AND system_type_cd = p_iol
898 AND business_group_id IS NULL;
899
900 CURSOR csr_9box_retention (p_retention VARCHAR2)
901 IS
902 SELECT information1 --- return from BG specific. If not defined then Global
903 FROM per_shared_types
904 WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
905 AND system_type_cd = p_retention
906 AND business_group_id = hr_general.get_business_group_id
907 UNION ALL
908 SELECT information1
909 FROM per_shared_types
910 WHERE lookup_type = 'PER_RETENTION_POTENTIAL'
911 AND system_type_cd = p_retention
912 AND business_group_id IS NULL;
913
914 BEGIN
915
916 IF(p_iol IS NULL)
917 THEN
918 l_num_iol := get_iol_for_9box (p_person_id, TRUNC (SYSDATE));
919 ELSE
920
921 OPEN csr_9box_iol (p_iol);
922
923 FETCH csr_9box_iol
924 INTO l_num_iol;
925
926 CLOSE csr_9box_iol;
927 END IF;
928
929 IF(p_retention IS NULL)
930 THEN
931 l_num_retention := get_retention_for_9box (p_person_id, TRUNC (SYSDATE));
932 ELSE
933
934 OPEN csr_9box_retention (p_retention);
935
936 FETCH csr_9box_retention
937 INTO l_num_retention;
938
939 CLOSE csr_9box_retention;
940 END IF;
941
942 IF (l_num_iol IS NULL OR l_num_iol < 0)
943 THEN
944 RETURN 0; -- Shared type not setup
945 END IF;
946
947 IF (l_num_retention IS NULL OR l_num_retention < 0)
948 THEN
949 RETURN 0; -- Shared type not setup
950 END IF;
951
952 RETURN ((l_num_retention - 1) * 3 + l_num_iol);
953 END get_iol_for_9box;
954
955 FUNCTION is_hipo_key_inplan_worker (p_person_id IN NUMBER, p_effective_date IN DATE)
956 RETURN VARCHAR2
957 IS
958 l_eit_potential NUMBER := -9;
959 l_eit_key VARCHAR2 (30) := 'N';
960 l_eit_plan VARCHAR2 (30) := 'N';
961 l_successor VARCHAR2 (30) := 'N';
962 l_ret VARCHAR2 (2) := 'X';
963 l_active_employee VARCHAR2 (2) := 'X';
964 --
965 CURSOR csr_key_worker_eit (p_person_id NUMBER, p_effective_date DATE)
966 IS
967 SELECT key_worker
968 FROM (SELECT pei_information3 key_worker,
969 fnd_date.canonical_to_date (pei_information5) start_date,
970 fnd_date.canonical_to_date (pei_information6) end_date,
971 fnd_date.canonical_to_date (pei_information8) completion_date
972 FROM per_people_extra_info
973 WHERE person_id = p_person_id AND information_type = 'PER_SUCCESSION_MGMT_INFO')
974 WHERE (NVL (start_date, p_effective_date) <= p_effective_date)
975 ORDER BY NVL (completion_date, start_date) DESC;
976
977 CURSOR csr_plan_exists (p_person_id NUMBER, p_effective_date DATE)
978 IS
979 SELECT 'Y'
980 FROM DUAL
981 WHERE EXISTS (
982 SELECT NULL
983 FROM per_sp_plan plans, per_sp_successor_in_plan succ
984 WHERE plans.successee_id = p_person_id
985 AND succ.status = 'A'
986 AND plans.status = 'A'
987 AND plans.plan_type = 'EMP'
988 AND succ.plan_id = plans.plan_id
989 AND p_effective_date BETWEEN TRUNC(start_date) AND NVL(end_date, p_effective_date));
990
991 CURSOR csr_is_a_successor (p_person_id NUMBER, p_effective_date DATE)
992 IS
993 SELECT 'Y'
994 FROM DUAL
995 WHERE EXISTS (
996 SELECT NULL
997 FROM per_sp_plan plans, per_sp_successor_in_plan succ
998 WHERE succ.successor_id = p_person_id
999 AND succ.status = 'A'
1000 AND plans.status = 'A'
1001 AND succ.plan_id = plans.plan_id
1002 AND p_effective_date BETWEEN TRUNC(start_date) AND NVL(end_date, p_effective_date));
1003
1004 CURSOR csr_active_employee (p_person_id NUMBER)
1005 IS
1006 SELECT 'Y'
1007 FROM SYS.DUAL
1008 WHERE EXISTS (
1009 SELECT NULL
1010 FROM per_person_types typ, per_person_type_usages_f ptu
1011 WHERE typ.system_person_type IN ('EMP', 'CWK', 'EMP_APL', 'APL'
1012 ,decode (fnd_profile.value ('PER_SP_SHOW_TERMINATED')
1013 ,'Y'
1014 ,'EX_EMP')
1015 ,decode (fnd_profile.value ('PER_SP_SHOW_TERMINATED')
1016 ,'Y'
1017 ,'EX_CWK'))
1018 AND typ.person_type_id = ptu.person_type_id
1019 AND TRUNC (SYSDATE) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
1020 AND ptu.person_id = p_person_id);
1021
1022 BEGIN
1023 OPEN csr_active_employee (p_person_id);
1024
1025 FETCH csr_active_employee
1026 INTO l_active_employee;
1027
1028 CLOSE csr_active_employee;
1029
1030 IF (l_active_employee <> 'Y')
1031 THEN
1032 RETURN 'X';
1033 END IF;
1034
1035 l_eit_potential := get_potential_for_9box (p_person_id, p_effective_date);
1036
1037 OPEN csr_key_worker_eit (p_person_id, p_effective_date);
1038
1039 FETCH csr_key_worker_eit
1040 INTO l_eit_key;
1041
1042 CLOSE csr_key_worker_eit;
1043
1044 OPEN csr_plan_exists (p_person_id, p_effective_date);
1045
1046 FETCH csr_plan_exists
1047 INTO l_eit_plan;
1048
1049 CLOSE csr_plan_exists;
1050
1051 OPEN csr_is_a_successor (p_person_id, p_effective_date);
1052
1053 FETCH csr_is_a_successor
1054 INTO l_successor;
1055
1056 CLOSE csr_is_a_successor;
1057
1058 IF l_eit_potential <> 3 AND l_eit_key = 'Y' -- Key but not High Potential Workers
1059 THEN
1060 l_ret := 'NP';
1061 ELSIF l_eit_potential = 3 AND l_eit_key = 'Y' AND l_eit_plan = 'Y' -- Key and High Potential Workers WITH Succession Plans
1062 THEN
1063 l_ret := 'Y';
1064 ELSIF (l_eit_potential = 3 AND l_eit_key = 'Y' AND l_eit_plan <> 'Y') -- Key and High Potential Workers with no Succession Plans
1065 THEN
1066 l_ret := 'N';
1067 ELSIF (l_eit_potential = 3 AND l_eit_key = 'Y' AND l_successor <> 'Y') -- Key and High Potential Workers with no Plans as successors
1068 THEN
1069 l_ret := 'NS';
1070 ELSIF (l_eit_potential = 3 AND l_eit_key = 'Y' AND l_successor = 'Y') -- Key and High Potential Workers WITH Plans as successors
1071 THEN
1072 l_ret := 'S';
1073 END IF;
1074
1075 -- Not a key HIPO
1076 RETURN l_ret;
1077 --
1078 END is_hipo_key_inplan_worker;
1079
1080 ------
1081 -- Function to return the consolidated overall readiness
1082 ------
1083
1084 FUNCTION get_overall_readiness (
1085 p_legislation_code IN VARCHAR2,
1086 p_business_group_id IN NUMBER,
1087 p_mode IN VARCHAR2
1088 )
1089 RETURN NUMBER
1090 IS
1091 CURSOR csr_overall_ready IS
1092 SELECT legislation_code,
1093 COUNT (*)
1094 FROM (SELECT legislation_code,
1095 CASE
1096 WHEN (100 - average_readiness) < 25
1097 THEN 'L'
1098 WHEN (100 - average_readiness) BETWEEN 25 AND 75
1099 THEN 'M'
1100 WHEN (100 - average_readiness) > 75
1101 THEN 'H'
1102 END overall_readiness
1103 FROM (SELECT sp.plan_id,
1104 bg.org_information9 legislation_code,
1105 NVL (ssd.plan_readiness_rule, 'AVG') plan_readiness_rule,
1106 DECODE (NVL (ssd.plan_readiness_rule, 'AVG'),
1107 'AVG', NVL (AVG (readiness_pct), 0),
1108 'MIN', NVL (MIN (readiness_pct), 0),
1109 'MAX', NVL (MAX (readiness_pct), 0)
1110 ) average_readiness
1111 FROM per_sp_plan sp,
1112 per_sp_successor_in_plan ssp,
1113 per_sp_successee_details ssd,
1114 hr_organization_information bg,
1115 per_people_f ppf,
1116 per_assignments_f paf,
1117 per_assignments_f pa1,
1118 per_assignment_status_types pas
1119 WHERE ppf.person_id <> fnd_global.employee_id
1120 AND ppf.person_id = paf.person_id
1121 AND paf.primary_flag = 'Y'
1122 AND pa1.primary_flag = 'Y'
1123 AND paf.assignment_type IN ('E','C')
1124 AND pa1.assignment_type IN ('E','C','A')
1125 AND pa1.assignment_status_type_id = pas.assignment_status_type_id
1126 AND pas.per_system_status <> 'TERM_ASSIGN'
1127 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1128 AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
1129 AND TRUNC(SYSDATE) BETWEEN pa1.effective_start_date AND pa1.effective_end_date
1130 AND paf.business_group_id = bg.organization_id
1131 AND bg.org_information_context = 'Business Group Information'
1132 AND sp.plan_type = 'EMP'
1133 AND sp.successee_id = ppf.person_id
1134 AND sp.status = 'A'
1135 AND ssp.status = 'A'
1136 AND sp.plan_id = ssp.plan_id
1137 AND ssd.successee_type(+) = 'EMP'
1138 AND sp.successee_id = ssd.successee_id(+)
1139 AND ssp.successor_id = pa1.person_id
1140 AND hr_wpm_util.is_hipo_key_inplan_worker (ppf.person_id, TRUNC (SYSDATE)) IN
1141 ('Y', 'N')
1142 GROUP BY sp.plan_id, bg.org_information9, NVL (ssd.plan_readiness_rule, 'AVG')))
1143 WHERE legislation_code = p_legislation_code AND overall_readiness = p_mode
1144 GROUP BY legislation_code;
1145
1146 l_leg_code varchar2(10);
1147 l_overall_readiness NUMBER;
1148 l_proc varchar2(80) :='hr_wpm_util.get_overall_readiness';
1149 BEGIN
1150 hr_utility.set_location('Entering:'||l_proc,10);
1151 hr_utility.trace('p_legislation_code:'||p_legislation_code);
1152 hr_utility.trace('Mode:'||p_mode);
1153 OPEN csr_overall_ready;
1154 FETCH csr_overall_ready INTO l_leg_code, l_overall_readiness;
1155 CLOSE csr_overall_ready;
1156 hr_utility.trace('l_overall-readiness:'||l_overall_readiness);
1157 hr_utility.set_location('Leaving:'||l_proc,20);
1158 RETURN ROUND (NVL (l_overall_readiness, 0), 2);
1159 END get_overall_readiness;
1160
1161 ------
1162 -- Function to return the consolidated overall readiness
1163 -- by plan. Only active successors are considered.
1164 ------
1165
1166 FUNCTION get_readiness_by_plan (p_plan_id IN NUMBER)
1167 RETURN NUMBER
1168 IS
1169 CURSOR csr_overall_ready
1170 IS
1171 SELECT DECODE (NVL (ssd.plan_readiness_rule, 'AVG'),
1172 'AVG', NVL (AVG (readiness_pct), 0),
1173 'MIN', NVL (MIN (readiness_pct), 0),
1174 'MAX', NVL (MAX (readiness_pct), 0)
1175 ) average_readiness
1176 FROM per_sp_plan sp,
1177 per_sp_successor_in_plan ssp,
1178 per_sp_successee_details ssd,
1179 per_people_f ppf,
1180 per_assignments_f paf,
1181 per_assignments_f pa1,
1182 per_assignment_status_types pas
1183 WHERE sp.plan_id = p_plan_id
1184 AND ppf.person_id <> fnd_global.employee_id
1185 AND ppf.person_id = paf.person_id
1186 AND paf.primary_flag = 'Y'
1187 AND paf.assignment_type IN ('E','C')
1188 AND (pa1.assignment_type='A' OR (pa1.assignment_type IN ('E','C') AND pa1.primary_flag = 'Y'))
1189 AND pa1.assignment_status_type_id = pas.assignment_status_type_id
1190 AND pas.per_system_status <> 'TERM_ASSIGN'
1191 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1192 AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
1193 AND TRUNC(SYSDATE) BETWEEN pa1.effective_start_date AND pa1.effective_end_date
1194 -- AND sp.plan_type = 'EMP'
1195 -- AND sp.successee_id = ppf.person_id
1196 AND ssp.status <> 'I'
1197 AND sp.plan_id = ssp.plan_id
1198 AND ssd.successee_type(+) = 'EMP'
1199 AND sp.successee_id = ssd.successee_id(+)
1200 AND ssp.successor_id = pa1.person_id
1201 GROUP BY sp.plan_id, NVL (ssd.plan_readiness_rule, 'AVG');
1202
1203 l_overall_readiness NUMBER;
1204 l_proc VARCHAR2 (80) := 'hr_wpm_util.get_overall_readiness';
1205 BEGIN
1206 hr_utility.set_location ('Entering:' || l_proc, 10);
1207 hr_utility.TRACE ('p_plan_id:' || p_plan_id);
1208
1209 OPEN csr_overall_ready;
1210
1211 FETCH csr_overall_ready
1212 INTO l_overall_readiness;
1213
1214 CLOSE csr_overall_ready;
1215
1216 hr_utility.TRACE ('l_overall-readiness:' || l_overall_readiness);
1217 hr_utility.set_location ('Leaving:' || l_proc, 20);
1218 RETURN ROUND (NVL (l_overall_readiness, 0), 2);
1219 END get_readiness_by_plan;
1220
1221
1222 ------
1223 -- Function to return whether the Succession Planning data is upgraded
1224 ------
1225 FUNCTION is_sp_data_upgraded
1226 RETURN VARCHAR2
1227 IS
1228 BEGIN
1229 RETURN 'Y';
1230 EXCEPTION
1231 WHEN OTHERS
1232 THEN
1233 RAISE;
1234 END;
1235
1236 FUNCTION is_obj_setting_open (p_plan_id NUMBER, p_manager_person_id NUMBER)
1237 RETURN VARCHAR2
1238 IS
1239 CURSOR csr_is_obj_open
1240 IS
1241 SELECT 'Y'
1242 FROM DUAL
1243 WHERE EXISTS (
1244 SELECT 'x'
1245 FROM per_personal_scorecards pps, per_assignments_f paf,
1246 per_perf_mgmt_plans pmp
1247 WHERE paf.supervisor_id = p_manager_person_id
1248 AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND
1249 paf.effective_end_date
1250 AND paf.assignment_id = pps.assignment_id
1251 AND pps.plan_id = p_plan_id
1252 AND pmp.plan_id = p_plan_id
1253 AND TRUNC (SYSDATE) BETWEEN pmp.obj_setting_start_date AND
1254 NVL (pps.obj_setting_deadline,
1255 pmp.obj_setting_deadline
1256 ));
1257
1258 l_return VARCHAR2 (1);
1259 BEGIN
1260 OPEN csr_is_obj_open;
1261
1262 FETCH csr_is_obj_open
1263 INTO l_return;
1264
1265 CLOSE csr_is_obj_open;
1266
1267 RETURN NVL (l_return, 'N');
1268 END is_obj_setting_open;
1269
1270
1271 END hr_wpm_util; -- Package spec
[Home] [Help]