[Home] [Help]
PACKAGE BODY: APPS.HR_APPRAISALS_UTIL_SS
Source
1 Package Body hr_appraisals_util_ss as
2 /* $Header: hrapprss.pkb 120.17.12020000.4 2012/10/12 07:14:34 schowdhu ship $ */
3
4 -- Global cursor for getting competence ratings
5 CURSOR get_competence_ratings(p_competence_id NUMBER,
6 p_assessment_id NUMBER) IS
7 select prl1.step_value prof_value, prl2.step_value perf_value, prl3.step_value weigh_value
8 from per_competence_elements pce, per_rating_levels_vl prl1, per_rating_levels_vl prl2,
9 per_rating_levels_vl prl3
10 where pce.assessment_id = p_assessment_id and pce.competence_id = p_competence_id
11 and pce.type='ASSESSMENT' and pce.object_name='ASSESSOR_ID'
12 and pce.proficiency_level_id = prl1.rating_level_id(+)
13 and pce.rating_level_id = prl2.rating_level_id (+)
14 and pce.weighting_level_id = prl3.rating_level_id(+)
15 and (prl1.step_value is not null or prl2.step_value is not null or
16 prl3.step_value is not null)
17 and exists (select * from per_participants pp where pp.PARTICIPATION_STATUS='COMPLETED'
18 AND pp.PARTICIPATION_IN_COLUMN='APPRAISAL_ID'
19 AND pp.PARTICIPATION_IN_ID=(select APPRAISAL_ID from per_assessments
20 where assessment_id=p_assessment_id)
21 AND pp.PERSON_ID=pce.object_id );
22 -- table type for appraisal objectives
23 TYPE comp_ratings_table IS TABLE OF get_competence_ratings%ROWTYPE INDEX BY BINARY_INTEGER ;
24
25 -- Global cursor for getting objective ratings
26 CURSOR get_objective_ratings(p_objective_id NUMBER,
27 p_assessment_id NUMBER) IS
28 select prl.step_value perf_value, pos.weighting_percent weigh_percent
29 from per_performance_ratings ppr, per_rating_levels_vl prl, per_assessments pas,
30 per_objectives pos
31 where pas.assessment_id = p_assessment_id
32 and ppr.objective_id = p_objective_id
33 and ppr.appraisal_id = pas.appraisal_id
34 and ppr.performance_level_id = prl.rating_level_id
35 and pos.appraisal_id = ppr.appraisal_id
36 and pos.objective_id = ppr.objective_id
37 and exists (select * from per_participants pp where pp.PARTICIPATION_STATUS='COMPLETED'
38 AND pp.PARTICIPATION_IN_COLUMN='APPRAISAL_ID'
39 AND pp.PARTICIPATION_IN_ID=(select APPRAISAL_ID from per_assessments
40 where assessment_id=p_assessment_id)
41 AND pp.PERSON_ID=ppr.PERSON_ID);
42
43 -- table type for appraisal objectives
44 TYPE obj_ratings_table IS TABLE OF get_objective_ratings%ROWTYPE INDEX BY BINARY_INTEGER ;
45
46 --
47 -- Global cursor for getting the appraisee appraisal info
48 --
49 CURSOR get_appraisal_details(p_assessment_id NUMBER) IS
50 select pap.appraisal_id,
51 pap.assignment_id,
52 pap.assignment_business_group_id,
53 pap.assignment_organization_id,
54 pap.appraisee_person_id,
55 pap.appraisal_template_id,
56 pap.system_type,
57 pap.type
58 from per_appraisals pap,
59 per_assessments pas
60 where pas.assessment_id = p_assessment_id
61 and pap.appraisal_id = pas.appraisal_id;
62
63 FUNCTION get_comp_line_score(p_line_formula IN VARCHAR,
64 p_comp_ratings IN comp_ratings_table) return NUMBER IS
65 i INTEGER DEFAULT 0;
66 j INTEGER DEFAULT 0;
67 l_comp_score NUMBER := 0;
68 BEGIN
69 FOR i IN 1 ..p_comp_ratings.count LOOP
70 j := j+1;
71 if(p_line_formula = 'WEIGHTING*PROFICIENCY') then
72 l_comp_score := l_comp_score + (nvl(p_comp_ratings(i).prof_value,1) * nvl(p_comp_ratings(i).weigh_value,1));
73 elsif (p_line_formula = 'WEIGHTING*PERFORMANCE') then
74 l_comp_score := l_comp_score + (nvl(p_comp_ratings(i).perf_value,1) * nvl(p_comp_ratings(i).weigh_value,1));
75 elsif (p_line_formula = 'PERFORMANCE*PROFICIENCY') then
76 l_comp_score := l_comp_score + (nvl(p_comp_ratings(i).prof_value,1) * nvl(p_comp_ratings(i).perf_value,1));
77 elsif (p_line_formula = 'PERFORMANCE') then
78 l_comp_score := l_comp_score + nvl(p_comp_ratings(i).perf_value,0);
79 elsif (p_line_formula = 'PROFICIENCY') then
80 l_comp_score := l_comp_score + nvl(p_comp_ratings(i).prof_value,0);
81 end if;
82 END LOOP;
83
84 if(j = 0) then
85 j := 1;
86 end if;
87
88 return l_comp_score / j;
89 END;
90
91
92 FUNCTION get_ff_line_score (p_object_id in NUMBER,
93 p_assessment_id in NUMBER,
94 p_line_formula_id in NUMBER,
95 p_prof_value in NUMBER,
96 p_perf_value in NUMBER,
97 p_weigh_value in NUMBER) return NUMBER IS
98
99 l_effective_date DATE := trunc(sysdate);
100 e_wrong_parameters EXCEPTION;
101 l_line_formula_id NUMBER;
102 l_formula_name ff_formulas_f.formula_name%TYPE := '&formula_name';
103 l_inputs ff_exec.inputs_t;
104 l_outputs ff_exec.outputs_t;
105 l_line_score NUMBER;
106
107 l_business_group_id per_appraisals.assignment_business_group_id%TYPE;
108 l_assignment_id per_appraisals.assignment_id%TYPE;
109 l_organization_id per_appraisals.assignment_organization_id%TYPE;
110 l_person_id per_appraisals.appraisee_person_id%TYPE;
111 l_appraisal_id per_appraisals.appraisal_id%TYPE;
112 l_appraisal_temp_id per_appraisals.appraisal_template_id%TYPE;
113 l_appr_system_type per_appraisals.system_type%TYPE;
114 l_appr_type per_appraisals.type%TYPE;
115 --
116 -- Get the line FF.
117 --
118 CURSOR csr_get_line_ff
119 IS
120 SELECT ff.formula_id, ff.formula_name
121 FROM ff_formulas_f ff
122 WHERE l_effective_date BETWEEN
123 ff.effective_start_date AND ff.effective_end_date
124 AND ff.formula_id = p_line_formula_id;
125
126 BEGIN
127
128 --
129 -- Fetch the line scoring formula ID.
130 --
131 OPEN csr_get_line_ff;
132 FETCH csr_get_line_ff INTO l_line_formula_id
133 ,l_formula_name;
134 CLOSE csr_get_line_ff;
135
136 IF l_line_formula_id IS null THEN
137 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
138 fnd_message.set_token('1', l_formula_name);
139 fnd_message.raise_error;
140 END IF;
141
142 --
143 -- Initialize the Fast Formula.
144 --
145 ff_exec.init_formula
146 (p_formula_id => l_line_formula_id
147 ,p_effective_date => l_effective_date
148 ,p_inputs => l_inputs
149 ,p_outputs => l_outputs);
150
151 --
152 -- Get appraisee appraisal details
153 --
154 OPEN get_appraisal_details(p_assessment_id);
155 FETCH get_appraisal_details INTO l_appraisal_id,
156 l_assignment_id,
157 l_business_group_id,
158 l_organization_id,
159 l_person_id,
160 l_appraisal_temp_id,
161 l_appr_system_type,
162 l_appr_type;
163 CLOSE get_appraisal_details;
164 --
165 -- Assign the FF inputs.
166 --
167 if (l_inputs.count <> 0) then
168 FOR i_input IN l_inputs.first..l_inputs.last LOOP
169
170 IF l_inputs(i_input).name = 'BUSINESS_GROUP_ID' THEN
171 l_inputs(i_input).value := l_business_group_id;
172 ELSIF l_inputs(i_input).name = 'ASSIGNMENT_ID' THEN
173 l_inputs(i_input).value := l_assignment_id;
174 ELSIF l_inputs(i_input).name = 'ORGANIZATION_ID' THEN
175 l_inputs(i_input).value := l_organization_id;
176 ELSIF l_inputs(i_input).name = 'PERSON_ID' THEN
177 l_inputs(i_input).value := l_person_id;
178 ELSIF l_inputs(i_input).name = 'DATE_EARNED' THEN
179 l_inputs(i_input).value := fnd_date.date_to_canonical(l_effective_date);
180 ELSIF l_inputs(i_input).name = 'PERFORMANCE' THEN
181 l_inputs(i_input).value := p_perf_value;
182 ELSIF l_inputs(i_input).name = 'PROFICIENCY' THEN
183 l_inputs(i_input).value := p_prof_value;
184 ELSIF l_inputs(i_input).name = 'WEIGHTING' THEN
185 l_inputs(i_input).value := p_weigh_value;
186 ELSIF l_inputs(i_input).name = 'LINE_OBJECT_ID' THEN
187 l_inputs(i_input).value := p_object_id;
188 ELSIF l_inputs(i_input).name = 'APPRAISAL_ID' THEN
189 l_inputs(i_input).value := l_appraisal_id;
190 ELSIF l_inputs(i_input).name = 'APPR_TEMPLATE_ID' THEN
191 l_inputs(i_input).value := l_appraisal_temp_id;
192 ELSIF l_inputs(i_input).name = 'APPR_SYSTEM_TYPE' THEN
193 l_inputs(i_input).value := l_appr_system_type;
194 ELSIF l_inputs(i_input).name = 'APPR_TYPE' THEN
195 l_inputs(i_input).value := l_appr_type;
196 ELSE
197 raise e_wrong_parameters;
198 END IF;
199
200 END LOOP;
201 END IF;
202
203 --
204 -- Run the FF.
205 --
206 ff_exec.run_formula(l_inputs, l_outputs);
207
208 --
209 -- Assign the outputs.
210 --
211 FOR i_output in l_outputs.first..l_outputs.last LOOP
212
213 IF l_outputs(i_output).name = 'LINE_SCORE' THEN
214 IF substr(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),1,1) = ',' then
215 l_line_score := replace(l_outputs(i_output).value,'.',',');
216 ELSE
217 l_line_score := l_outputs(i_output).value;
218 END IF;
219 ELSE
220 RAISE e_wrong_parameters;
221 END IF;
222
223 END LOOP;
224
225 return l_line_score;
226
227 EXCEPTION
228
229 WHEN e_wrong_parameters THEN
230 --
231 -- The inputs / outputs of the Fast Formula are incorrect
232 -- so raise an error.
233 --
234 hr_utility.set_message(800,'HR_34964_BAD_FF_DEFINITION');
235 hr_utility.raise_error;
236
237 WHEN OTHERS THEN
238 RAISE;
239
240 END;
241
242 FUNCTION get_ff_overall_score (p_comp_asmt_score NUMBER,
243 p_obj_asmt_score NUMBER,
244 p_final_formula_id NUMBER,
245 p_assessment_id NUMBER) return NUMBER IS
246
247 l_effective_date DATE := trunc(sysdate);
248 e_wrong_parameters EXCEPTION;
249 l_final_formula_id NUMBER;
250 l_formula_name ff_formulas_f.formula_name%TYPE := '&formula_name';
251 l_inputs ff_exec.inputs_t;
252 l_outputs ff_exec.outputs_t;
253 l_line_score NUMBER;
254
255 l_business_group_id per_appraisals.assignment_business_group_id%TYPE;
256 l_assignment_id per_appraisals.assignment_id%TYPE;
257 l_organization_id per_appraisals.assignment_organization_id%TYPE;
258 l_person_id per_appraisals.appraisee_person_id%TYPE;
259 l_appraisal_id per_appraisals.appraisal_id%TYPE;
260 l_appraisal_temp_id per_appraisals.appraisal_template_id%TYPE;
261 l_appr_system_type per_appraisals.system_type%TYPE;
262 l_appr_type per_appraisals.type%TYPE;
263 --
264 -- Get the line FF.
265 --
266 CURSOR csr_get_line_ff
267 IS
268 SELECT ff.formula_id, ff.formula_name
269 FROM ff_formulas_f ff
270 WHERE l_effective_date BETWEEN
271 ff.effective_start_date AND ff.effective_end_date
272 AND ff.formula_id = p_final_formula_id;
273
274 BEGIN
275
276 --
277 -- Fetch the line scoring formula ID.
278 --
279 OPEN csr_get_line_ff;
280 FETCH csr_get_line_ff INTO l_final_formula_id
281 ,l_formula_name;
282 CLOSE csr_get_line_ff;
283
284 IF l_final_formula_id IS null THEN
285 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
286 fnd_message.set_token('1', l_formula_name);
287 fnd_message.raise_error;
288 END IF;
289
290 --
291 -- Initialize the Fast Formula.
292 --
293 ff_exec.init_formula
294 (p_formula_id => l_final_formula_id
295 ,p_effective_date => l_effective_date
296 ,p_inputs => l_inputs
297 ,p_outputs => l_outputs);
298
299 --
300 -- Get appraisee appraisal details
301 --
302 OPEN get_appraisal_details(p_assessment_id);
303 FETCH get_appraisal_details INTO l_appraisal_id,
304 l_assignment_id,
305 l_business_group_id,
306 l_organization_id,
307 l_person_id,
308 l_appraisal_temp_id,
309 l_appr_system_type,
310 l_appr_type;
311 CLOSE get_appraisal_details;
312 --
313 -- Assign the FF inputs.
314 --
315 if (l_inputs.count <> 0) then
316 FOR i_input IN l_inputs.first..l_inputs.last LOOP
317
318 IF l_inputs(i_input).name = 'BUSINESS_GROUP_ID' THEN
319 l_inputs(i_input).value := l_business_group_id;
320 ELSIF l_inputs(i_input).name = 'ASSIGNMENT_ID' THEN
321 l_inputs(i_input).value := l_assignment_id;
322 ELSIF l_inputs(i_input).name = 'ORGANIZATION_ID' THEN
323 l_inputs(i_input).value := l_organization_id;
324 ELSIF l_inputs(i_input).name = 'PERSON_ID' THEN
325 l_inputs(i_input).value := l_person_id;
326 ELSIF l_inputs(i_input).name = 'DATE_EARNED' THEN
327 l_inputs(i_input).value := fnd_date.date_to_canonical(l_effective_date);
328 ELSIF l_inputs(i_input).name = 'COMPETENCY_SCORE' THEN
329 l_inputs(i_input).value := p_comp_asmt_score;
330 ELSIF l_inputs(i_input).name = 'OBJECTIVE_SCORE' THEN
331 l_inputs(i_input).value := p_obj_asmt_score;
332 ELSIF l_inputs(i_input).name = 'APPRAISAL_ID' THEN
333 l_inputs(i_input).value := l_appraisal_id;
334 ELSIF l_inputs(i_input).name = 'APPR_TEMPLATE_ID' THEN
335 l_inputs(i_input).value := l_appraisal_temp_id;
336 ELSIF l_inputs(i_input).name = 'APPR_SYSTEM_TYPE' THEN
337 l_inputs(i_input).value := l_appr_system_type;
338 ELSIF l_inputs(i_input).name = 'APPR_TYPE' THEN
339 l_inputs(i_input).value := l_appr_type;
340 ELSE
341 raise e_wrong_parameters;
342 END IF;
343
344 END LOOP;
345 END IF;
346
347 --
348 -- Run the FF.
349 --
350 ff_exec.run_formula(l_inputs, l_outputs);
351
352 --
353 -- Assign the outputs.
354 --
355 FOR i_output in l_outputs.first..l_outputs.last LOOP
356
357 IF l_outputs(i_output).name = 'FINAL_RATING' THEN
358 IF substr(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),1,1) = ',' then
359 l_line_score := replace(l_outputs(i_output).value,'.',',');
360 ELSE
361 l_line_score := l_outputs(i_output).value;
362 END IF;
363 ELSE
364 RAISE e_wrong_parameters;
365 END IF;
366
367 END LOOP;
368
369 return l_line_score;
370
371 EXCEPTION
372
373 WHEN e_wrong_parameters THEN
374 --
375 -- The inputs / outputs of the Fast Formula are incorrect
376 -- so raise an error.
377 --
378 hr_utility.set_message(800,'HR_34964_BAD_FF_DEFINITION');
379 hr_utility.raise_error;
380
381 WHEN OTHERS THEN
382 RAISE;
383
384 END;
385
386
387 function get_competence_score (p_competence_id NUMBER,
388 p_assessment_id NUMBER) return NUMBER IS
389 cursor get_line_formula is
390 select line_score_formula, line_score_formula_id from per_assessment_types, per_assessments
391 where per_assessments.assessment_id = p_assessment_id
392 and per_assessments.assessment_type_id = per_assessment_types.assessment_type_id;
393
394 l_num_part NUMBER := 0;
395 l_line_formula per_assessment_types.line_score_formula%type;
396 l_line_formula_id per_assessment_types.line_score_formula_id%type;
397 l_prof_value NUMBER;
398 l_perf_value NUMBER;
399 l_weigh_value NUMBER;
400 l_tot_prof_value NUMBER default 0;
401 l_tot_perf_value NUMBER default 0;
402 l_tot_weigh_value NUMBER default 0;
403 l_comp_ratings_table comp_ratings_table;
404 i INTEGER DEFAULT 0;
405 j INTEGER DEFAULT 0;
406 begin
407 open get_line_formula;
408 fetch get_line_formula into l_line_formula, l_line_formula_id;
409 close get_line_formula;
410
411 -- get competence_ratings
412 OPEN get_competence_ratings(p_competence_id, p_assessment_id);
413 FETCH get_competence_ratings BULK COLLECT INTO l_comp_ratings_table;
414 CLOSE get_competence_ratings;
415
416 IF (l_line_formula is not null) THEN
417 return get_comp_line_score(l_line_formula, l_comp_ratings_table);
418 ELSIF (l_line_formula_id is not null) THEN
419 FOR i IN 1 ..l_comp_ratings_table.count LOOP
420 j := j+1;
421 l_tot_prof_value := l_tot_prof_value + nvl(l_comp_ratings_table(i).prof_value,0);
422 l_tot_perf_value := l_tot_perf_value + nvl(l_comp_ratings_table(i).perf_value,0);
423 l_tot_weigh_value := l_tot_weigh_value + nvl(l_comp_ratings_table(i).weigh_value,0);
424 END LOOP;
425
426 if(j = 0) then
427 j := 1;
428 end if;
429
430 return get_ff_line_score(p_competence_id,
431 p_assessment_id,
432 l_line_formula_id,
433 l_tot_prof_value/j,
434 l_tot_perf_value/j,
435 l_tot_weigh_value/j);
436 ELSE
437 return 0;
438 END IF;
439 end get_competence_score;
440
441 function get_objective_score (p_objective_id NUMBER,
442 p_appraisal_id NUMBER) return NUMBER IS
443 cursor get_line_formula is
444 select pat.line_score_formula,
445 pat.line_score_formula_id,
446 pas.assessment_id
447 from per_assessment_types pat,
448 per_assessments pas
449 where pas.appraisal_id = p_appraisal_id
450 and pas.assessment_type_id = pat.assessment_type_id
451 and pat.type = 'OBJECTIVE';
452
453 l_num_part NUMBER := 0;
454 l_line_formula per_assessment_types.line_score_formula%type;
455 l_line_formula_id per_assessment_types.line_score_formula_id%type;
456 l_assessment_id per_assessments.assessment_id%type;
457 l_perf_value NUMBER;
458 l_tot_prof_value NUMBER default 0;
459 l_tot_perf_value NUMBER default 0;
460 l_tot_weigh_value NUMBER default 0;
461 l_obj_ratings_table obj_ratings_table;
462 i INTEGER DEFAULT 0;
463 j INTEGER DEFAULT 0;
464 begin
465 open get_line_formula;
466 fetch get_line_formula into l_line_formula, l_line_formula_id, l_assessment_id;
467 close get_line_formula;
468
469 -- get objective ratings
470 OPEN get_objective_ratings(p_objective_id, l_assessment_id);
471 FETCH get_objective_ratings BULK COLLECT INTO l_obj_ratings_table;
472 CLOSE get_objective_ratings;
473
474 -- loop thru and get total performance rating
475 FOR i IN 1 ..l_obj_ratings_table.count LOOP
476 j := j+1;
477 l_tot_perf_value := l_tot_perf_value + nvl(l_obj_ratings_table(i).perf_value,0);
478 l_tot_weigh_value := nvl(l_obj_ratings_table(i).weigh_percent,l_tot_weigh_value);
479 END LOOP;
480
481 IF(j = 0) THEN
482 j := 1;
483 END IF;
484
485 IF (l_line_formula is not null) THEN
486 return l_tot_perf_value/j;
487 ELSIF (l_line_formula_id is not null) THEN
488 return get_ff_line_score(p_objective_id,
489 l_assessment_id,
490 l_line_formula_id,
491 l_tot_prof_value/j,
492 l_tot_perf_value/j,
493 l_tot_weigh_value);
494 ELSE
495 return 0;
496 END IF;
497 end get_objective_score;
498
499 function get_assessment_score (p_assessment_id NUMBER) return NUMBER IS
500 cursor get_score_formula is
501 select total_score_formula from per_assessment_types, per_assessments
502 where per_assessments.assessment_id = p_assessment_id
503 and per_assessments.assessment_type_id = per_assessment_types.assessment_type_id;
504 cursor get_competences is
505 select distinct pce.competence_id
506 from per_competence_elements pce
507 where pce.assessment_id = p_assessment_id and pce.type='ASSESSMENT'
508 and pce.object_name = 'ASSESSOR_ID';
509 cursor get_objectives is
510 select po.objective_id
511 from per_objectives po,
512 per_assessments pa
513 where pa.assessment_id = p_assessment_id
514 and po.appraisal_id = pa.appraisal_id;
515 cursor get_asmnt_type is
516 select nvl(pst.type, 'COMPETENCE'),
517 pa.appraisal_id
518 from per_assessment_types pst,
519 per_assessments pa
520 where pa.assessment_id = p_assessment_id
521 and pst.assessment_type_id = pa.assessment_type_id;
522 l_num_part NUMBER := 0;
523 l_score_formula per_assessment_types.total_score_formula%type;
524 l_total_score NUMBER := 0;
525 l_competence_id NUMBER;
526 l_objective_id NUMBER;
527 l_asmnt_type per_assessment_types.type%type;
528 l_appraisal_id per_appraisals.appraisal_id%type;
529 begin
530 open get_score_formula;
531 fetch get_score_formula into l_score_formula;
532 close get_score_formula;
533
534 -- get assessment type
535 OPEN get_asmnt_type;
536 FETCH get_asmnt_type INTO l_asmnt_type, l_appraisal_id;
537 CLOSE get_asmnt_type;
538
539 IF ('COMPETENCE' = l_asmnt_type) THEN
540 open get_competences;
541 loop
542 fetch get_competences into l_competence_id;
543 exit when get_competences%NOTFOUND;
544 l_num_part := l_num_part + 1;
545 l_total_score := l_total_score + get_competence_score(l_competence_id, p_assessment_id);
546 end loop;
547 close get_competences;
548 ELSIF ('OBJECTIVE' = l_asmnt_type) THEN
549 open get_objectives;
550 loop
551 fetch get_objectives into l_objective_id;
552 exit when get_objectives%NOTFOUND;
553 l_num_part := l_num_part + 1;
554 l_total_score := l_total_score + get_objective_score(l_objective_id, l_appraisal_id);
555 end loop;
556 close get_objectives;
557 END IF;
558
559 if(l_num_part = 0) then
560 l_num_part := 1;
561 end if;
562
563 if l_score_formula = 'TOTAL_LINES' then
564 return l_total_score;
565 elsif l_score_formula = 'AVERAGE_LINES' then
566 return l_total_score / l_num_part;
567 else
568 return null;
569 end if;
570 return l_total_score;
571
572 end get_assessment_score;
573
574 function get_overall_score (p_appraisal_id NUMBER,
575 p_final_formula_id NUMBER) return NUMBER IS
576 cursor get_assessments is
577 select pa.assessment_id,
578 nvl(pst.type, 'COMPETENCE')
579 from per_assessment_types pst,
580 per_assessments pa
581 where pa.appraisal_id = p_appraisal_id
582 and pst.assessment_type_id = pa.assessment_type_id;
583 cursor chk_pce(p_assessment_id NUMBER) IS
584 SELECT 'Y'
585 FROM dual
586 WHERE EXISTS
587 (
588 SELECT 'Y'
589 FROM per_competence_elements pce
590 WHERE pce.assessment_id = p_assessment_id
591 AND pce.type = 'ASSESSMENT'
592 AND pce.object_name = 'ASSESSOR_ID');
593 l_dummy varchar2(1);
594 l_assessment_id per_assessments.assessment_id%type;
595 l_comp_asmt_score NUMBER;
596 l_obj_asmt_score NUMBER;
597 l_asmnt_type per_assessment_types.type%type;
598 begin
599
600 open get_assessments;
601 loop
602 fetch get_assessments into l_assessment_id, l_asmnt_type;
603 exit when get_assessments%NOTFOUND;
604 if ('COMPETENCE' = l_asmnt_type) then
605 OPEN chk_pce(l_assessment_id);
606 FETCH chk_pce INTO l_dummy;
607 IF chk_pce%FOUND THEN
608 l_comp_asmt_score := get_assessment_score(l_assessment_id);
609 END IF;
610 CLOSE chk_pce;
611 elsif ('OBJECTIVE' = l_asmnt_type) then
612 l_obj_asmt_score := get_assessment_score(l_assessment_id);
613 end if;
614 end loop;
615 close get_assessments;
616
617 if(l_assessment_id is not null) then
618 return get_ff_overall_score(l_comp_asmt_score, l_obj_asmt_score,
619 p_final_formula_id, l_assessment_id);
620 else
621 return null;
622 end if;
623
624 end get_overall_score;
625
626
627 procedure send_notification( p_fromPersonId VARCHAR2, p_toPersonId VARCHAR2,p_comment VARCHAR2,p_mainAprId VARCHAR2,p_actionType VARCHAR2) AS
628 ln_notification_id NUMBER;
629 ln_MAnotification_id NUMBER;
630
631 CURSOR get_role (person_id per_all_people_f.person_id%TYPE)
632 IS
633 SELECT wf.NAME role_name
634 FROM wf_roles wf
635 WHERE wf.orig_system = 'PER' AND wf.orig_system_id = person_id;
636
637 CURSOR get_global_name (p_person_id per_all_people_f.person_id%TYPE)
638 IS
639 SELECT NVL (GLOBAL_NAME, first_name || ', ' || last_name)
640 FROM per_all_people_f
641 WHERE person_id = p_person_id
642 AND TRUNC (SYSDATE) BETWEEN effective_start_date AND effective_end_date;
643
644 from_role wf_local_roles.NAME%TYPE DEFAULT NULL;
645 to_role wf_local_roles.NAME%TYPE DEFAULT NULL;
646 from_name per_all_people_f.GLOBAL_NAME%TYPE;
647 appraisee_name per_all_people_f.GLOBAL_NAME%TYPE;
648 msg_name varchar2(100);
649 BEGIN
650
651 OPEN get_role (p_fromPersonId);
652 FETCH get_role
653 INTO from_role;
654 CLOSE get_role;
655
656 OPEN get_role (p_toPersonId);
657 FETCH get_role
658 INTO to_role;
659 CLOSE get_role;
660
661 OPEN get_global_name (p_fromPersonId);
662 FETCH get_global_name
663 INTO from_name;
664 CLOSE get_global_name;
665
666
667
668
669 IF p_actionType = 'MACHG' THEN
670 msg_name := 'HR_APPRAISAL_MACHANGE_MSG';
671 ELSIF p_actionType = 'HRSYSCOMPNOFEED' THEN
672 msg_name := 'HR_APPRAISAL_COMPLETE_MSG';
673 ELSIF p_actionType = 'HRSYSCOMPFEED' THEN
674 msg_name := 'HR_APPRAISAL_FEEDBACK_MSG';
675 END IF;
676
677 ln_notification_id :=
678 wf_notification.send (ROLE => to_role,
679 msg_type => 'HRSSA',
680 msg_name => msg_name,
681 callback => NULL,
682 CONTEXT => NULL,
683 send_comment => NULL,
684 priority => 50
685 );
686
687 wf_notification.setattrtext (ln_notification_id, '#FROM_ROLE', from_role);
688 wf_notification.setattrtext (ln_notification_id, 'HRPROFNAME', from_name);
689 wf_notification.setattrtext (ln_notification_id, 'APPROVAL_COMMENT', p_comment);
690
691 IF msg_name = 'HR_APPRAISAL_MACHANGE_MSG' THEN
692 msg_name := 'HR_APPRAISAL_MACHANGE_MGR_MSG';
693 ELSIF msg_name = 'HR_APPRAISAL_FEEDBACK_MSG' OR msg_name = 'HR_APPRAISAL_COMPLETE_MSG' THEN
694 msg_name := 'HR_APPRAISAL_COMP_MGR_MSG';
695 END IF;
696
697 OPEN get_role (p_mainAprId);
698 FETCH get_role
699 INTO to_role;
700 CLOSE get_role;
701
702
703 OPEN get_global_name (p_toPersonId);
704 FETCH get_global_name
705 INTO appraisee_name;
706 CLOSE get_global_name;
707
708
709 ln_MAnotification_id :=
710 wf_notification.send (ROLE => to_role,
711 msg_type => 'HRSSA',
712 msg_name => msg_name,
713 callback => NULL,
714 CONTEXT => NULL,
715 send_comment => NULL,
716 priority => 50
717 );
718
719 wf_notification.setattrtext (ln_MAnotification_id, '#FROM_ROLE', from_role);
720 wf_notification.setattrtext (ln_MAnotification_id, 'HRPROFNAME', from_name);
721 wf_notification.setattrtext (ln_MAnotification_id, 'APPROVAL_COMMENT', p_comment);
722 wf_notification.setattrtext (ln_MAnotification_id, 'APPRAISEE', appraisee_name);
723
724 wf_notification.denormalize_notification(ln_MAnotification_id);
725
726 Exception
727 when others then
728 raise;
729
730 END;
731
732 function is_maiappraiser_terminated(p_person_id varchar2) return varchar2 is
733 cursor csr_wkr_status is
734 SELECT
735 nvl(nvl(current_employee_flag
736 ,current_npw_flag)
737 ,'N')
738 FROM
739 per_people_f
740 WHERE person_id = p_person_id
741 AND sysdate BETWEEN effective_start_date AND effective_end_date;
742
743 current_wkr_flag VARCHAR2(2);
744 begin
745 open csr_wkr_status;
746 fetch csr_wkr_status into current_wkr_flag;
747 close csr_wkr_status;
748 if current_wkr_flag='Y' then
749 return 'N';
750 else
751 return 'Y';
752 end if;
753
754 Exception
755 when others then
756 return 'N';
757 end is_maiappraiser_terminated;
758
759
760 function is_worker_terminated(p_person_id varchar2) return varchar2 is
761 cursor csr_wkr_status is
762 SELECT
763 nvl(nvl(current_employee_flag
764 ,current_npw_flag)
765 ,'N')
766 FROM
767 per_people_f
768 WHERE person_id = p_person_id
769 AND sysdate BETWEEN effective_start_date AND effective_end_date;
770
771 current_wkr_flag VARCHAR2(2);
772 begin
773 open csr_wkr_status;
774 fetch csr_wkr_status into current_wkr_flag;
775 close csr_wkr_status;
776 if current_wkr_flag='Y' then
777 return 'N';
778 else
779 return 'Y';
780 end if;
781 Exception
782 when others then
783 return 'N';
784 end is_worker_terminated;
785
786 function is_approver_terminated(p_person_id varchar2) return varchar2 is
787 cursor csr_wkr_status (p_approver_id in varchar2) is
788 SELECT
789 nvl(nvl(current_employee_flag
790 ,current_npw_flag)
791 ,'N')
792 FROM
793 per_people_f
794 WHERE person_id = p_approver_id
795 AND sysdate BETWEEN effective_start_date AND effective_end_date;
796
797 current_wkr_flag VARCHAR2(2);
798 approverId VARCHAR2(20);
799 begin
800
801
802 approverId := hr_approval_custom.get_next_approver (p_person_id);
803 if approverId = null then
804 return 'N';
805 end if;
806 open csr_wkr_status(approverId);
807 fetch csr_wkr_status into current_wkr_flag;
808 close csr_wkr_status;
809
810 if current_wkr_flag = 'Y' then
811 return 'N';
812 else
813 return 'Y';
814 end if;
815
816 Exception
817 when others then
818 return 'N';
819
820 end is_approver_terminated;
821
822 function is_approver_terminated(p_item_type IN VARCHAR2,p_item_key IN VARCHAR2) return varchar2 is
823 cursor csr_wkr_status (p_approver_id in varchar2) is
824 SELECT
825 nvl(nvl(current_employee_flag
826 ,current_npw_flag)
827 ,'N')
828 FROM
829 per_people_f
830 WHERE person_id = p_approver_id
831 AND sysdate BETWEEN effective_start_date AND effective_end_date;
832
833 cursor csr_approver_id ( p_role in varchar2 ) is
834 SELECT wf.orig_system_id from wf_roles wf
835 WHERE wf.orig_system = 'PER'
836 AND wf.name = p_role;
837
838 current_wkr_flag VARCHAR2(2);
839 approverId VARCHAR2(20);
840 approverRole VARCHAR2(100);
841 begin
842 --Works Only for appraisals with pending approval does not work for other statuses
843 --When the appraisal is waiting for pending approval get to whom the appraisal forwarded and checks if the pprover terminatted
844 approverRole := wf_engine.GetItemAttrText(p_item_type,p_item_key,'FORWARD_TO_USERNAME',TRUE);
845
846 open csr_approver_id(approverRole);
847 fetch csr_approver_id into approverId;
848 if csr_approver_id%notfound then
849 return 'N';
850 end if;
851 close csr_approver_id;
852 if approverId = null then
853 return 'N';
854 end if;
855
856 open csr_wkr_status(approverId);
857 fetch csr_wkr_status into current_wkr_flag;
858 if csr_wkr_status%notfound then
859 hr_utility.set_location('Point 3.1 ', 1);
860 return 'N';
861 end if;
862 close csr_wkr_status;
863
864
865 if current_wkr_flag = 'Y' then
866 return 'N';
867 else
868 return 'Y';
869 end if;
870
871 Exception
872 when others then
873 return 'N';
874
875 end is_approver_terminated;
876
877
878 function get_item_key(p_appraisal_id VARCHAR2) return varchar2 is
879 l_itemkey varchar2(1000);
880 begin
881
882 select substr(system_params,instr(system_params,'pItemKey=')+9) into l_itemkey from per_appraisals where appraisal_id=p_appraisal_id;
883 return l_itemkey;
884
885 Exception
886 when others then
887 return '';
888 end;
889
890 end hr_appraisals_util_ss;