DBA Data[Home] [Help]

PACKAGE BODY: APPS.HR_APPRAISALS_UTIL_SS

Source


1 Package Body hr_appraisals_util_ss as
2 /* $Header: hrapprss.pkb 120.9.12010000.2 2008/08/06 08:32:08 ubhat 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 -- table type for appraisal objectives
18    TYPE comp_ratings_table IS TABLE OF get_competence_ratings%ROWTYPE INDEX BY BINARY_INTEGER ;
19 
20 -- Global cursor for getting objective ratings
21    CURSOR get_objective_ratings(p_objective_id NUMBER,
22                                 p_assessment_id NUMBER) IS
23          select prl.step_value perf_value, pos.weighting_percent weigh_percent
24          from per_performance_ratings ppr, per_rating_levels_vl prl, per_assessments pas,
25           per_objectives pos
26          where pas.assessment_id = p_assessment_id
27 	 and ppr.objective_id = p_objective_id
28 	 and ppr.appraisal_id = pas.appraisal_id
29          and ppr.performance_level_id = prl.rating_level_id
30          and pos.appraisal_id = ppr.appraisal_id
31          and pos.objective_id =  ppr.objective_id;
32 
33 -- table type for appraisal objectives
34    TYPE obj_ratings_table IS TABLE OF get_objective_ratings%ROWTYPE INDEX BY BINARY_INTEGER ;
35 
36 --
37 -- Global cursor for getting the appraisee appraisal info
38 --
39    CURSOR get_appraisal_details(p_assessment_id NUMBER) IS
40 	select pap.appraisal_id,
41 	       pap.assignment_id,
42 	       pap.assignment_business_group_id,
43 	       pap.assignment_organization_id,
44 	       pap.appraisee_person_id,
45                pap.appraisal_template_id,
46                pap.system_type,
47                pap.type
48 	from per_appraisals pap,
49 	     per_assessments pas
50 	where pas.assessment_id = p_assessment_id
51 	and pap.appraisal_id = pas.appraisal_id;
52 
53 FUNCTION get_comp_line_score(p_line_formula IN VARCHAR,
54                              p_comp_ratings IN comp_ratings_table) return NUMBER IS
55 i INTEGER DEFAULT 0;
56 j INTEGER DEFAULT 0;
57 l_comp_score NUMBER := 0;
58 BEGIN
59     FOR i IN 1 ..p_comp_ratings.count LOOP
60         j := j+1;
61         if(p_line_formula = 'WEIGHTING*PROFICIENCY') then
62            l_comp_score := l_comp_score + (nvl(p_comp_ratings(i).prof_value,1) * nvl(p_comp_ratings(i).weigh_value,1));
63         elsif (p_line_formula = 'WEIGHTING*PERFORMANCE') then
64            l_comp_score := l_comp_score + (nvl(p_comp_ratings(i).perf_value,1) * nvl(p_comp_ratings(i).weigh_value,1));
65         elsif (p_line_formula = 'PERFORMANCE*PROFICIENCY') then
66            l_comp_score := l_comp_score + (nvl(p_comp_ratings(i).prof_value,1) * nvl(p_comp_ratings(i).perf_value,1));
67         elsif (p_line_formula = 'PERFORMANCE') then
68            l_comp_score := l_comp_score + nvl(p_comp_ratings(i).perf_value,0);
69         elsif (p_line_formula = 'PROFICIENCY') then
70            l_comp_score := l_comp_score + nvl(p_comp_ratings(i).prof_value,0);
71         end if;
72     END LOOP;
73 
74     if(j = 0) then
75       j := 1;
76     end if;
77 
78     return l_comp_score / j;
79 END;
80 
81 
82 FUNCTION get_ff_line_score (p_object_id in NUMBER,
83                             p_assessment_id in NUMBER,
84                             p_line_formula_id in NUMBER,
85 			    p_prof_value in NUMBER,
86                             p_perf_value in NUMBER,
87 	   		    p_weigh_value in NUMBER) return NUMBER IS
88 
89   l_effective_date     DATE := trunc(sysdate);
90   e_wrong_parameters   EXCEPTION;
91   l_line_formula_id    NUMBER;
92   l_formula_name       ff_formulas_f.formula_name%TYPE := '&formula_name';
93   l_inputs             ff_exec.inputs_t;
94   l_outputs            ff_exec.outputs_t;
95   l_line_score         NUMBER;
96 
97   l_business_group_id  per_appraisals.assignment_business_group_id%TYPE;
98   l_assignment_id      per_appraisals.assignment_id%TYPE;
99   l_organization_id    per_appraisals.assignment_organization_id%TYPE;
100   l_person_id          per_appraisals.appraisee_person_id%TYPE;
101   l_appraisal_id       per_appraisals.appraisal_id%TYPE;
102   l_appraisal_temp_id  per_appraisals.appraisal_template_id%TYPE;
103   l_appr_system_type   per_appraisals.system_type%TYPE;
104   l_appr_type          per_appraisals.type%TYPE;
105   --
106   -- Get the line FF.
107   --
108   CURSOR csr_get_line_ff
109   IS
110   SELECT ff.formula_id, ff.formula_name
111   FROM   ff_formulas_f ff
112   WHERE  l_effective_date BETWEEN
113          ff.effective_start_date AND ff.effective_end_date
114   AND    ff.formula_id = p_line_formula_id;
115 
116 BEGIN
117 
118   --
119   -- Fetch the line scoring formula ID.
120   --
121   OPEN  csr_get_line_ff;
122   FETCH csr_get_line_ff INTO l_line_formula_id
123                             ,l_formula_name;
124   CLOSE csr_get_line_ff;
125 
126   IF l_line_formula_id IS null THEN
127      fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
128      fnd_message.set_token('1', l_formula_name);
129      fnd_message.raise_error;
130   END IF;
131 
132   --
133   -- Initialize the Fast Formula.
134   --
135   ff_exec.init_formula
136       (p_formula_id     => l_line_formula_id
137       ,p_effective_date => l_effective_date
138       ,p_inputs         => l_inputs
139       ,p_outputs        => l_outputs);
140 
141   --
142   -- Get appraisee appraisal details
143   --
144   OPEN  get_appraisal_details(p_assessment_id);
145   FETCH get_appraisal_details INTO l_appraisal_id,
146                                     l_assignment_id,
147                                     l_business_group_id,
148 				    l_organization_id,
149 				    l_person_id,
150                                     l_appraisal_temp_id,
151                                     l_appr_system_type,
152                                     l_appr_type;
153   CLOSE get_appraisal_details;
154   --
155   -- Assign the FF inputs.
156   --
157   FOR i_input IN l_inputs.first..l_inputs.last LOOP
158 
159       IF l_inputs(i_input).name    = 'BUSINESS_GROUP_ID' THEN
160          l_inputs(i_input).value  := l_business_group_id;
161       ELSIF l_inputs(i_input).name = 'ASSIGNMENT_ID' THEN
162          l_inputs(i_input).value  := l_assignment_id;
163       ELSIF l_inputs(i_input).name = 'ORGANIZATION_ID' THEN
164          l_inputs(i_input).value  := l_organization_id;
165       ELSIF l_inputs(i_input).name = 'PERSON_ID' THEN
166          l_inputs(i_input).value  := l_person_id;
167       ELSIF l_inputs(i_input).name = 'DATE_EARNED' THEN
168          l_inputs(i_input).value  := fnd_date.date_to_canonical(l_effective_date);
169       ELSIF l_inputs(i_input).name = 'PERFORMANCE' THEN
170          l_inputs(i_input).value  := p_perf_value;
171       ELSIF l_inputs(i_input).name = 'PROFICIENCY' THEN
172          l_inputs(i_input).value  := p_prof_value;
173       ELSIF l_inputs(i_input).name = 'WEIGHTING' THEN
174          l_inputs(i_input).value  := p_weigh_value;
175       ELSIF l_inputs(i_input).name = 'LINE_OBJECT_ID' THEN
176          l_inputs(i_input).value  := p_object_id;
177       ELSIF l_inputs(i_input).name = 'APPRAISAL_ID' THEN
178          l_inputs(i_input).value  := l_appraisal_id;
179       ELSIF l_inputs(i_input).name = 'APPR_TEMPLATE_ID' THEN
180          l_inputs(i_input).value  := l_appraisal_temp_id;
181       ELSIF l_inputs(i_input).name = 'APPR_SYSTEM_TYPE' THEN
182          l_inputs(i_input).value  := l_appr_system_type;
183       ELSIF l_inputs(i_input).name = 'APPR_TYPE' THEN
184          l_inputs(i_input).value  := l_appr_type;
185       ELSE
186          raise e_wrong_parameters;
187       END IF;
188 
189   END LOOP;
190 
191   --
192   -- Run the FF.
193   --
194   ff_exec.run_formula(l_inputs, l_outputs);
195 
196   --
197   -- Assign the outputs.
198   --
199   FOR i_output in l_outputs.first..l_outputs.last LOOP
200 
201       IF l_outputs(i_output).name = 'LINE_SCORE' THEN
202         IF substr(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),1,1) = ',' then
203             l_line_score := replace(l_outputs(i_output).value,'.',',');
204         ELSE
205             l_line_score := l_outputs(i_output).value;
206         END IF;
207       ELSE
208         RAISE e_wrong_parameters;
209       END IF;
210 
211   END LOOP;
212 
213   return l_line_score;
214 
215 EXCEPTION
216 
217   WHEN e_wrong_parameters THEN
218     --
219     -- The inputs / outputs of the Fast Formula are incorrect
220     -- so raise an error.
221     --
222     hr_utility.set_message(800,'HR_34964_BAD_FF_DEFINITION');
223     hr_utility.raise_error;
224 
225   WHEN OTHERS THEN
226     RAISE;
227 
228 END;
229 
230 FUNCTION get_ff_overall_score (p_comp_asmt_score NUMBER,
231                                p_obj_asmt_score NUMBER,
232                                p_final_formula_id NUMBER,
233                                p_assessment_id NUMBER) return NUMBER IS
234 
235   l_effective_date     DATE := trunc(sysdate);
236   e_wrong_parameters   EXCEPTION;
237   l_final_formula_id    NUMBER;
238   l_formula_name       ff_formulas_f.formula_name%TYPE := '&formula_name';
239   l_inputs             ff_exec.inputs_t;
240   l_outputs            ff_exec.outputs_t;
241   l_line_score         NUMBER;
242 
243   l_business_group_id  per_appraisals.assignment_business_group_id%TYPE;
244   l_assignment_id      per_appraisals.assignment_id%TYPE;
245   l_organization_id    per_appraisals.assignment_organization_id%TYPE;
246   l_person_id          per_appraisals.appraisee_person_id%TYPE;
247   l_appraisal_id       per_appraisals.appraisal_id%TYPE;
248   l_appraisal_temp_id  per_appraisals.appraisal_template_id%TYPE;
249   l_appr_system_type   per_appraisals.system_type%TYPE;
250   l_appr_type          per_appraisals.type%TYPE;
251   --
252   -- Get the line FF.
253   --
254   CURSOR csr_get_line_ff
255   IS
256   SELECT ff.formula_id, ff.formula_name
257   FROM   ff_formulas_f ff
258   WHERE  l_effective_date BETWEEN
259          ff.effective_start_date AND ff.effective_end_date
260   AND    ff.formula_id = p_final_formula_id;
261 
262 BEGIN
263 
264   --
265   -- Fetch the line scoring formula ID.
266   --
267   OPEN  csr_get_line_ff;
268   FETCH csr_get_line_ff INTO l_final_formula_id
269                             ,l_formula_name;
270   CLOSE csr_get_line_ff;
271 
272   IF l_final_formula_id IS null THEN
273      fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
274      fnd_message.set_token('1', l_formula_name);
275      fnd_message.raise_error;
276   END IF;
277 
278   --
279   -- Initialize the Fast Formula.
280   --
281   ff_exec.init_formula
282       (p_formula_id     => l_final_formula_id
283       ,p_effective_date => l_effective_date
284       ,p_inputs         => l_inputs
285       ,p_outputs        => l_outputs);
286 
287   --
288   -- Get appraisee appraisal details
289   --
290   OPEN  get_appraisal_details(p_assessment_id);
291   FETCH get_appraisal_details INTO l_appraisal_id,
292                                     l_assignment_id,
293                                     l_business_group_id,
294 				    l_organization_id,
295 				    l_person_id,
296                                     l_appraisal_temp_id,
297                                     l_appr_system_type,
298                                     l_appr_type;
299   CLOSE get_appraisal_details;
300   --
301   -- Assign the FF inputs.
302   --
303   FOR i_input IN l_inputs.first..l_inputs.last LOOP
304 
305       IF l_inputs(i_input).name    = 'BUSINESS_GROUP_ID' THEN
306          l_inputs(i_input).value  := l_business_group_id;
307       ELSIF l_inputs(i_input).name = 'ASSIGNMENT_ID' THEN
308          l_inputs(i_input).value  := l_assignment_id;
309       ELSIF l_inputs(i_input).name = 'ORGANIZATION_ID' THEN
310          l_inputs(i_input).value  := l_organization_id;
311       ELSIF l_inputs(i_input).name = 'PERSON_ID' THEN
312          l_inputs(i_input).value  := l_person_id;
313       ELSIF l_inputs(i_input).name = 'DATE_EARNED' THEN
314          l_inputs(i_input).value  := fnd_date.date_to_canonical(l_effective_date);
315       ELSIF l_inputs(i_input).name = 'COMPETENCY_SCORE' THEN
316          l_inputs(i_input).value  := p_comp_asmt_score;
317       ELSIF l_inputs(i_input).name = 'OBJECTIVE_SCORE' THEN
318          l_inputs(i_input).value  := p_obj_asmt_score;
319       ELSIF l_inputs(i_input).name = 'APPRAISAL_ID' THEN
320          l_inputs(i_input).value  := l_appraisal_id;
321       ELSIF l_inputs(i_input).name = 'APPR_TEMPLATE_ID' THEN
322          l_inputs(i_input).value  := l_appraisal_temp_id;
323       ELSIF l_inputs(i_input).name = 'APPR_SYSTEM_TYPE' THEN
324          l_inputs(i_input).value  := l_appr_system_type;
325       ELSIF l_inputs(i_input).name = 'APPR_TYPE' THEN
326          l_inputs(i_input).value  := l_appr_type;
327       ELSE
328          raise e_wrong_parameters;
329       END IF;
330 
331   END LOOP;
332 
333   --
334   -- Run the FF.
335   --
336   ff_exec.run_formula(l_inputs, l_outputs);
337 
338   --
339   -- Assign the outputs.
340   --
341   FOR i_output in l_outputs.first..l_outputs.last LOOP
342 
343       IF l_outputs(i_output).name = 'FINAL_RATING' THEN
344         IF substr(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),1,1) = ',' then
345             l_line_score := replace(l_outputs(i_output).value,'.',',');
346         ELSE
347             l_line_score := l_outputs(i_output).value;
348         END IF;
349       ELSE
350         RAISE e_wrong_parameters;
351       END IF;
352 
353   END LOOP;
354 
355   return l_line_score;
356 
357 EXCEPTION
358 
359   WHEN e_wrong_parameters THEN
360     --
361     -- The inputs / outputs of the Fast Formula are incorrect
362     -- so raise an error.
363     --
364     hr_utility.set_message(800,'HR_34964_BAD_FF_DEFINITION');
365     hr_utility.raise_error;
366 
367   WHEN OTHERS THEN
368     RAISE;
369 
370 END;
371 
372 
373 function get_competence_score (p_competence_id NUMBER,
374                                p_assessment_id NUMBER) return NUMBER IS
375     cursor get_line_formula is
376           select line_score_formula, line_score_formula_id from per_assessment_types, per_assessments
377           where per_assessments.assessment_id = p_assessment_id
378           and per_assessments.assessment_type_id = per_assessment_types.assessment_type_id;
379 
380     l_num_part NUMBER := 0;
381     l_line_formula per_assessment_types.line_score_formula%type;
382     l_line_formula_id per_assessment_types.line_score_formula_id%type;
383     l_prof_value NUMBER;
384     l_perf_value NUMBER;
385     l_weigh_value NUMBER;
386     l_tot_prof_value NUMBER default 0;
387     l_tot_perf_value NUMBER default 0;
388     l_tot_weigh_value NUMBER default 0;
389     l_comp_ratings_table comp_ratings_table;
390     i INTEGER DEFAULT 0;
391     j INTEGER DEFAULT 0;
392 begin
393  open get_line_formula;
394  fetch get_line_formula into l_line_formula, l_line_formula_id;
395  close get_line_formula;
396 
397  -- get competence_ratings
398  OPEN get_competence_ratings(p_competence_id, p_assessment_id);
399  FETCH get_competence_ratings BULK COLLECT INTO l_comp_ratings_table;
400  CLOSE get_competence_ratings;
401 
402  IF (l_line_formula is not null) THEN
403     return get_comp_line_score(l_line_formula, l_comp_ratings_table);
404  ELSIF (l_line_formula_id is not null) THEN
405     FOR i IN 1 ..l_comp_ratings_table.count LOOP
406         j := j+1;
407         l_tot_prof_value := l_tot_prof_value + nvl(l_comp_ratings_table(i).prof_value,0);
408         l_tot_perf_value := l_tot_perf_value + nvl(l_comp_ratings_table(i).perf_value,0);
409         l_tot_weigh_value := l_tot_weigh_value + nvl(l_comp_ratings_table(i).weigh_value,0);
410     END LOOP;
411 
412     if(j = 0) then
413       j := 1;
414     end if;
415 
416     return get_ff_line_score(p_competence_id,
417 				  p_assessment_id,
418                                   l_line_formula_id,
419 			          l_tot_prof_value/j,
420                                   l_tot_perf_value/j,
421 	           	          l_tot_weigh_value/j);
422  ELSE
423     return 0;
424  END IF;
425 end get_competence_score;
426 
427 function get_objective_score (p_objective_id NUMBER,
428                               p_appraisal_id NUMBER) return NUMBER IS
429     cursor get_line_formula is
430           select pat.line_score_formula,
431 	         pat.line_score_formula_id,
432 	         pas.assessment_id
433 	  from per_assessment_types pat,
434 	       per_assessments pas
435           where pas.appraisal_id = p_appraisal_id
436           and pas.assessment_type_id = pat.assessment_type_id
437 	  and pat.type = 'OBJECTIVE';
438 
439     l_num_part NUMBER := 0;
440     l_line_formula per_assessment_types.line_score_formula%type;
441     l_line_formula_id per_assessment_types.line_score_formula_id%type;
442     l_assessment_id per_assessments.assessment_id%type;
443     l_perf_value NUMBER;
444     l_tot_prof_value NUMBER default 0;
445     l_tot_perf_value NUMBER default 0;
446     l_tot_weigh_value NUMBER default 0;
447     l_obj_ratings_table obj_ratings_table;
448     i INTEGER DEFAULT 0;
449     j INTEGER DEFAULT 0;
450 begin
451  open get_line_formula;
452  fetch get_line_formula into l_line_formula, l_line_formula_id, l_assessment_id;
453  close get_line_formula;
454 
455  -- get objective ratings
456  OPEN get_objective_ratings(p_objective_id, l_assessment_id);
457  FETCH get_objective_ratings BULK COLLECT INTO l_obj_ratings_table;
458  CLOSE get_objective_ratings;
459 
460  -- loop thru and get total performance rating
461  FOR i IN 1 ..l_obj_ratings_table.count LOOP
462   j := j+1;
463   l_tot_perf_value := l_tot_perf_value + nvl(l_obj_ratings_table(i).perf_value,0);
464   l_tot_weigh_value := nvl(l_obj_ratings_table(i).weigh_percent,l_tot_weigh_value);
465  END LOOP;
466 
467  IF(j = 0) THEN
468     j := 1;
469  END IF;
470 
471  IF (l_line_formula is not null) THEN
472     return l_tot_perf_value/j;
473  ELSIF (l_line_formula_id is not null) THEN
474     return get_ff_line_score(p_objective_id,
475 		             l_assessment_id,
476                              l_line_formula_id,
477 			     l_tot_prof_value/j,
478                              l_tot_perf_value/j,
479 	           	     l_tot_weigh_value);
480  ELSE
481     return 0;
482  END IF;
483 end get_objective_score;
484 
485 function get_assessment_score (p_assessment_id NUMBER) return NUMBER IS
486     cursor get_score_formula is
487           select total_score_formula from per_assessment_types, per_assessments
488           where per_assessments.assessment_id = p_assessment_id
489           and per_assessments.assessment_type_id = per_assessment_types.assessment_type_id;
490     cursor get_competences is
491           select distinct pce.competence_id
492           from per_competence_elements pce
493           where pce.assessment_id = p_assessment_id and pce.type='ASSESSMENT'
494           and pce.object_name = 'ASSESSOR_ID';
495     cursor get_objectives is
496           select po.objective_id
497           from per_objectives po,
498 	       per_assessments pa
499           where pa.assessment_id = p_assessment_id
500           and po.appraisal_id = pa.appraisal_id;
501     cursor get_asmnt_type is
502           select nvl(pst.type, 'COMPETENCE'),
503                  pa.appraisal_id
504           from per_assessment_types pst,
505 	       per_assessments pa
506           where pa.assessment_id = p_assessment_id
507           and pst.assessment_type_id = pa.assessment_type_id;
508     l_num_part NUMBER := 0;
509     l_score_formula per_assessment_types.total_score_formula%type;
510     l_total_score NUMBER := 0;
511     l_competence_id NUMBER;
512     l_objective_id NUMBER;
513     l_asmnt_type per_assessment_types.type%type;
514     l_appraisal_id per_appraisals.appraisal_id%type;
515 begin
516  open get_score_formula;
517  fetch get_score_formula into l_score_formula;
518  close get_score_formula;
519 
520  -- get assessment type
521  OPEN get_asmnt_type;
522  FETCH get_asmnt_type INTO l_asmnt_type, l_appraisal_id;
523  CLOSE get_asmnt_type;
524 
525  IF ('COMPETENCE' = l_asmnt_type) THEN
526     open get_competences;
527     loop
528       fetch get_competences into l_competence_id;
529       exit when get_competences%NOTFOUND;
530       l_num_part := l_num_part + 1;
531       l_total_score := l_total_score + get_competence_score(l_competence_id, p_assessment_id);
532      end loop;
533      close get_competences;
534  ELSIF ('OBJECTIVE' = l_asmnt_type) THEN
535     open get_objectives;
536     loop
537       fetch get_objectives into l_objective_id;
538       exit when get_objectives%NOTFOUND;
539       l_num_part := l_num_part + 1;
540       l_total_score := l_total_score + get_objective_score(l_objective_id, l_appraisal_id);
541      end loop;
542      close get_objectives;
543  END IF;
544 
545   if(l_num_part = 0) then
546     l_num_part := 1;
547   end if;
548 
549  if l_score_formula = 'TOTAL_LINES' then
550     return l_total_score;
551  elsif l_score_formula = 'AVERAGE_LINES' then
552     return l_total_score / l_num_part;
553  else
554     return null;
555  end if;
556   return l_total_score;
557 
558 end get_assessment_score;
559 
560 function get_overall_score (p_appraisal_id NUMBER,
561                             p_final_formula_id NUMBER) return NUMBER IS
562     cursor get_assessments is
563           select pa.assessment_id,
564                  nvl(pst.type, 'COMPETENCE')
565           from per_assessment_types pst,
566 	       per_assessments pa
567           where pa.appraisal_id = p_appraisal_id
568           and pst.assessment_type_id = pa.assessment_type_id;
569     l_assessment_id per_assessments.assessment_id%type;
570     l_comp_asmt_score NUMBER;
571     l_obj_asmt_score NUMBER;
572     l_asmnt_type per_assessment_types.type%type;
573 begin
574 
575     open get_assessments;
576     loop
577       fetch get_assessments into l_assessment_id, l_asmnt_type;
578       exit when get_assessments%NOTFOUND;
579       if ('COMPETENCE' = l_asmnt_type) then
580        l_comp_asmt_score := get_assessment_score(l_assessment_id);
581       elsif ('OBJECTIVE' = l_asmnt_type) then
582        l_obj_asmt_score := get_assessment_score(l_assessment_id);
583       end if;
584     end loop;
585     close get_assessments;
586 
587     if(l_assessment_id is not null) then
588       return get_ff_overall_score(l_comp_asmt_score, l_obj_asmt_score,
589                                 p_final_formula_id, l_assessment_id);
590     else
591       return null;
592     end if;
593 
594 end get_overall_score;
595 
596 end hr_appraisals_util_ss;