1 PACKAGE BODY HRI_BPL_PROFICIENCY AS
2 /* $Header: hribcprf.pkb 120.1 2005/12/22 05:58:35 smohapat noship $ */
3 --
4 -- get_old_proficiency_level
5 --
6 /**************************************************************************
7 Description : Function to get the competence proficiency level for a
8 person before the appraisal was done
9 Preconditions : None
10 In Parameters : p_competence_id IN NUMBER
11 p_person_id IN NUMBER
12 p_appraisal_id IN NUMBER
13 p_main_appraiser_id IN NUMBER
14 Post Sucess : Returns the Proficiency Level before the appraisal was done
15 Post Failure : NULL
16 ***************************************************************************/
17 FUNCTION get_old_proficiency_level(p_competence_id IN NUMBER, p_person_id IN NUMBER
18 , p_appraisal_id IN NUMBER, p_main_appraiser_id IN NUMBER)
19 RETURN VARCHAR2 IS
20 --
21 cursor c_old_lvl (p_competence_id IN NUMBER, p_person_id IN NUMBER
22 , p_appraisal_id IN NUMBER, p_main_appraiser_id IN NUMBER) is
23 SELECT decode(pce.proficiency_level_id,'','',rtl.step_value||'-'||rtx.name) Old_Level
24 FROM PER_COMPETENCE_ELEMENTS pce,
25 PER_RATING_LEVELS rtl ,
26 PER_RATING_LEVELS_TL rtx,
27 PER_APPRAISALS apr
28 WHERE pce.type = 'ASSESSMENT'
29 and pce.object_name = 'ASSESSOR_ID'
30 and object_id = p_main_appraiser_id
31 AND pce.competence_id = p_competence_id
32 AND pce.proficiency_level_id = rtl.rating_level_id (+)
33 AND rtl.rating_level_id = rtx.rating_level_id (+)
34 AND rtx.language(+) = userenv('LANG')
35 AND (apr.appraisee_person_id, apr.appraisal_period_end_date) IN
36 (SELECT prv.appraisee_person_id, MAX(prv.appraisal_period_end_date)
37 FROM PER_APPRAISALS prv,
38 PER_APPRAISALS cur,
39 PER_COMPETENCE_ELEMENTS cpn
40 WHERE prv.appraisee_person_id = p_person_id
41 AND prv.appraisal_period_end_date < cur.appraisal_period_start_date
42 AND cur.appraisal_id = p_appraisal_id
43 AND cpn.object_name = 'APPRAISAL_ID'
44 AND cpn.object_id = prv.appraisal_id
45 GROUP BY prv.appraisee_person_id
46 )
47 AND (ASSESSMENT_ID, PCE.COMPETENCE_ID) IN
48 (select assessment_id, competence_id
49 from PER_COMPETENCE_ELEMENTS
50 where competence_id = p_competence_id
51 AND type = 'ASSESSMENT'
52 AND object_name = 'APPRAISAL_ID'
53 AND object_id = apr.appraisal_id
54 INTERSECT
55 SELECT assessment_id, COMPETENCE_ID
56 FROM PER_COMPETENCE_ELEMENTS
57 WHERE competence_id = p_competence_id
58 AND type = 'ASSESSMENT'
59 AND object_name = 'ASSESSOR_ID'
60 AND object_id = p_main_appraiser_id);
61 --
62 --
63 l_old_prof_lvl varchar2(2000);
64 l_old_lvl c_old_lvl%ROWTYPE;
65 --
66 --
67 begin
68 --
69 --
70 open c_old_lvl (p_competence_id, p_person_id, p_appraisal_id, p_main_appraiser_id);
71 fetch c_old_lvl into l_old_lvl;
72 --
73 --
74 l_old_prof_lvl := l_old_lvl.Old_Level;
75 --
76 --
77 close c_old_lvl;
78 --
79 --
80 return(l_old_prof_lvl);
81 --
82 --
83 exception
84 --
85 --
86 when others then
87 return ('');
88 --
89 --
90 END get_old_proficiency_level;
91 --
92 --
93 -- get_new_proficiency_level
94 --
95 /**************************************************************************
96 Description : Function to get the competence proficiency level for a
97 person after the appraisal was done
98 Preconditions : None
99 In Parameters : p_competence_id IN NUMBER
100 p_main_appraiser_id IN NUMBER
101 p_appraisal_id IN NUMBER
102 Post Sucess : Returns the Proficiency Level after the appraisal was done
103 Post Failure : NULL
104 ***************************************************************************/
105 FUNCTION get_new_proficiency_level(p_competence_id IN NUMBER,
106 p_main_appraiser_id IN NUMBER,
107 p_appraisal_id IN NUMBER)
108 RETURN VARCHAR2 IS
109 --
110 cursor c_new_lvl (p_competence_id IN NUMBER, p_main_appraiser_id IN NUMBER
111 , p_appraisal_id IN NUMBER) is
112 SELECT decode(pce.proficiency_level_id,'','',rtl.step_value||'-'||rtx.name) New_Level
113 FROM PER_COMPETENCE_ELEMENTS pce,
114 PER_RATING_LEVELS rtl ,
115 PER_RATING_LEVELS_TL rtx
116 WHERE pce.type = 'ASSESSMENT'
117 AND object_name = 'ASSESSOR_ID'
118 AND pce.competence_id = p_competence_id
119 AND pce.proficiency_level_id = rtl.rating_level_id (+)
120 AND rtl.rating_level_id = rtx.rating_level_id (+)
121 AND rtx.language(+) = userenv('LANG')
122 AND (pce.assessment_id, pce.competence_id) IN
123 (SELECT assessment_id, competence_id
124 FROM PER_COMPETENCE_ELEMENTS
125 WHERE competence_id = p_competence_id
126 AND type = 'ASSESSMENT'
127 AND object_name = 'APPRAISAL_ID'
128 AND object_id = p_appraisal_id
129 INTERSECT
130 SELECT assessment_id, competence_id
131 FROM PER_COMPETENCE_ELEMENTS
132 WHERE competence_id = p_competence_id
133 AND type = 'ASSESSMENT'
134 AND object_name = 'ASSESSOR_ID'
135 AND object_id = p_main_appraiser_id);
136 --
137 --
138 l_new_prof_lvl varchar2(2000);
139 l_new_lvl c_new_lvl%ROWTYPE;
140 --
141 --
142 begin
143 --
144 --
145 open c_new_lvl (p_competence_id, p_main_appraiser_id, p_appraisal_id);
146 fetch c_new_lvl into l_new_lvl;
147 --
148 --
149 l_new_prof_lvl := l_new_lvl.New_Level;
150 --
151 --
152 close c_new_lvl;
153 --
154 --
155 return(l_new_prof_lvl);
156 --
157 --
158 exception
159 --
160 --
161 when others then
162 return ('');
163 --
164 --
165 END get_new_proficiency_level;
166 --
167 --
168 -- get_competence_appraisal_flag
169 --
170 /**************************************************************************
171 Description : Function to chech whether the given competence has been updates
172 by an appraisal
173 Preconditions : None
174 In Parameters : p_competence_id IN NUMBER
175 p_appraisal_id IN NUMBER,
176 p_person_id IN NUBMER
177 Post Sucess : Returns the 'Y' If the competence being updated by appraisal
178 else returns 'N'
179 Post Failure : 'NULL'
180 ***************************************************************************/
181 --
182 FUNCTION get_competence_appraisal_flag(p_competence_id IN NUMBER,
183 p_appraisal_id IN NUMBER,
184 p_person_id IN NUMBER)
185 RETURN VARCHAR2
186 IS
187 --
188 Cursor c_flag(p_competence_id IN NUMBER, p_appraisal_id IN NUMBER, p_person_id IN NUMBER) is
189 SELECT 'Y' flag
190 FROM PER_COMPETENCE_ELEMENTS pce,
191 PER_APPRAISALS apr
192 WHERE apr.appraisal_id = p_appraisal_id
193 AND pce.competence_id = p_competence_id
194 AND pce.object_id = p_appraisal_id
195 AND apr.appraisee_person_id = p_person_id
196 AND PCE.object_name = 'APPRAISAL_ID'
197 AND APR.appraisal_system_status = 'COMPLETED';
198 --
199 l_flag c_flag%ROWTYPE;
200 --
201 --
202 begin
203 --
204 --
205 open c_flag (p_competence_id, p_appraisal_id, p_person_id);
206 fetch c_flag into l_flag;
207 --
208 --
209 IF (c_flag%NOTFOUND) THEN
210 return('N');
211 END IF;
212 --
213 return('Y');
214 --
215 exception
216 --
217 --
218 when others then
219 return ('');
220 --
221 --
222 END get_competence_appraisal_flag;
223 --
224 --
225 END HRI_BPL_PROFICIENCY;
226 --
[Home] [Help]