[Home] [Help]
PACKAGE BODY: APPS.HR_COMPLETE_APPRAISAL_SS
Source
1 PACKAGE BODY hr_complete_appraisal_ss AS
2 /* $Header: hrcpappr.pkb 120.14.12020000.4 2013/03/07 11:27:45 sussuman ship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13 lv_no_appraisal EXCEPTION;
14
15 TYPE cur_typ IS REF CURSOR;
16
17 -- Global cursor for getting objectives for appraisal_id
18 CURSOR get_appr_objectives (p_appraisal_id IN NUMBER)
19 IS
20 SELECT objective_id,
21 NAME,
22 target_date,
23 start_date,
24 business_group_id,
25 object_version_number,
26 owning_person_id,
27 achievement_date,
28 detail,
29 comments,
30 success_criteria,
31 appraisal_id,
32 attribute_category,
33 attribute1,
34 attribute2,
35 attribute3,
36 attribute4,
37 attribute5,
38 attribute6,
39 attribute7,
40 attribute8,
41 attribute9,
42 attribute10,
43 attribute11,
44 attribute12,
45 attribute13,
46 attribute14,
47 attribute15,
48 attribute16,
49 attribute17,
50 attribute18,
51 attribute19,
52 attribute20,
53 attribute21,
54 attribute22,
55 attribute23,
56 attribute24,
57 attribute25,
58 attribute26,
59 attribute27,
60 attribute28,
61 attribute29,
62 attribute30,
63 scorecard_id,
64 copied_from_library_id,
65 copied_from_objective_id,
66 aligned_with_objective_id,
67 next_review_date,
68 group_code,
69 priority_code,
70 appraise_flag,
71 verified_flag,
72 weighting_percent,
73 complete_percent,
74 target_value,
75 actual_value,
76 uom_code,
77 measurement_style_code,
78 measure_name,
79 measure_type_code,
80 measure_comments,
81 sharing_access_code
82 FROM per_objectives
83 WHERE appraisal_id = p_appraisal_id;
84
85 -- table type for appraisal objectives
86 TYPE appr_obj_table IS TABLE OF get_appr_objectives%ROWTYPE
87 INDEX BY BINARY_INTEGER;
88
89 PROCEDURE set_appr_status_log (
90 item_type IN VARCHAR2,
91 item_key IN VARCHAR2,
92 status IN VARCHAR2,
93 attr_name IN VARCHAR2,
94 l_log IN VARCHAR2
95 )
96 IS
97 lv_status VARCHAR2 (4000);
98 BEGIN
99 IF ( (wf_engine.getitemattrtext (item_type, item_key, gv_appr_compl_status, TRUE) IS NULL)
100 OR (wf_engine.getitemattrtext (item_type, item_key, gv_appr_compl_status, TRUE) = 'W')
101 )
102 THEN
103 wf_engine.setitemattrtext (item_type, item_key, gv_appr_compl_status, status);
104 END IF;
105
106 wf_engine.setitemattrtext (item_type, item_key, attr_name, l_log);
107 EXCEPTION
108 WHEN OTHERS
109 THEN
110 RAISE;
111 END;
112
113 FUNCTION is_new_appraisal (item_type IN VARCHAR2, item_key IN VARCHAR2)
114 RETURN BOOLEAN
115 IS
116 BEGIN
117 IF hr_workflow_service.item_attribute_exists (p_item_type => item_type,
118 p_item_key => item_key,
119 p_name => 'HR_COMPETENCE_ENHANCEMENT_SS'
120 )
121 THEN
122 RETURN TRUE;
123 END IF;
124
125 RETURN FALSE;
126 EXCEPTION
127 WHEN OTHERS
128 THEN
129 RAISE;
130 END;
131
132 PROCEDURE generate_event (
133 p_overall_perf_rating VARCHAR2,
134 p_review_date DATE,
135 p_result VARCHAR2,
136 p_reason VARCHAR2,
137 p_log IN OUT NOCOPY VARCHAR2,
138 p_new_appraisal IN BOOLEAN DEFAULT TRUE
139 )
140 IS
141 BEGIN
142 IF p_new_appraisal
143 THEN
144 p_log :=
145 p_overall_perf_rating || '^' || p_review_date || '^' || p_result || '^' || p_reason;
146 ELSE
147 p_log :=
148 p_log
149 || '<br> <br> <table width="100%" summary="" border="0" cellspacing="0" cellpadding="0"> <tr valign="top">'
150 || '<td align="left" width="70%">'
151 || '<div><div class="x60">'
152 || '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">'
153 || '<tr><td width="100%"> <h1 class="x18">'
154 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPRAISAL_EVENT',
155 p_application_id => 'PER'
156 )
157 || '</h1></td></tr>'
158 || '<tr><td class="x2i"></td></tr>'
159 || '</table></div>'
160 || '<script>t(void 0,''5'')</script>'
161 || '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">'
162 || '<tr>'
163 || '<td>'
164 || '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">'
165 || '<tr>'
166 || '<th scope="col" class="x1r">'
167 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_PERF_LEVEL',
168 p_application_id => 'PER'
169 )
170 || '</th>'
171 || '<th scope="col" class="x1r x4j">'
172 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_PERF_REVIEW_DATE',
173 p_application_id => 'PER'
174 )
175 || ' </th>'
176 || '<th scope="col" class="x1r x4j">'
177 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_RESULT',
178 p_application_id => 'PER'
179 )
180 || '</th>'
181 || '<th scope="col" class="x1r x4j">'
182 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_REASON',
183 p_application_id => 'PER'
184 )
185 || '</th>'
186 || '</tr>'
187 || '<tr> '
188 || '<td class="x1l x4x" nowrap>'
189 || p_overall_perf_rating
190 || '</td>'
191 || '<td class="x1l x4x" nowrap>'
192 || p_review_date
193 || '</td>'
194 || '<td class="x1l x4x">'
195 || p_result
196 || '</td>'
197 || '<td class="x1l x4x">'
198 || p_reason
199 || '</td>'
200 || '</tr>'
201 || '</table>'
202 || '</td>'
203 || '</tr>'
204 || '</table>'
205 || '</div> <div></div>'
206 || '<script>t(''10'')</script>'
207 || '</td>'
208 || '</tr>'
209 || '</table>';
210 END IF;
211 END;
212
213 PROCEDURE generate_appraisal_status (
214 appraisal_date per_appraisals.appraisal_date%TYPE,
215 appraisee_name per_all_people_f.full_name%TYPE,
216 appraisal_status hr_lookups.meaning%TYPE,
217 appraisal_type hr_lookups.meaning%TYPE,
218 RESULT fnd_new_messages.MESSAGE_TEXT%TYPE,
219 reason VARCHAR2,
220 p_log IN OUT NOCOPY VARCHAR2
221 )
222 IS
223 BEGIN
224 p_log :=
225 p_log
226 || '<br> <br> <table width="100%" summary="" border="0" cellspacing="0" cellpadding="0"> <tr valign="top">'
227 || '<td align="left" width="70%">'
228 || '<div><div class="x60">'
229 || '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">'
230 || '<tr><td width="100%"> <h1 class="x18">'
231 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPRAISAL_STATUS',
232 p_application_id => 'PER'
233 )
234 || '</h1></td></tr>'
235 || '<tr><td class="x2i"></td></tr>'
236 || '</table></div>'
237 || '<script>t(void 0,''5'')</script>'
238 || '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">'
239 || '<tr>'
240 || '<td>'
241 || '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">'
242 || '<tr>'
243 || '<th scope="col" class="x1r">'
244 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPRAISAL_DATE',
245 p_application_id => 'PER'
246 )
247 || '</th>'
248 || '<th scope="col" class="x1r x4j">'
249 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPRAISEE',
250 p_application_id => 'PER'
251 )
252 || ' </th>'
253 || '<th scope="col" class="x1r x4j">'
254 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPRAISAL_TYPE',
255 p_application_id => 'PER'
256 )
257 || '</th>'
258 || '<th scope="col" class="x1r x4j">'
259 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_STATUS',
260 p_application_id => 'PER'
261 )
262 || '</th>'
263 || '<th scope="col" class="x1r x4j">'
264 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_RESULT',
265 p_application_id => 'PER'
266 )
267 || '</th>'
268 || '<th scope="col" class="x1r x4j">'
269 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_REASON',
270 p_application_id => 'PER'
271 )
272 || '</th>'
273 || '</tr>'
274 || '<tr> '
275 || '<td class="x1l x4x" nowrap>'
276 || appraisal_date
277 || '</td>'
278 || '<td class="x1l x4x" nowrap>'
279 || appraisee_name
280 || '</td>'
281 || '<td class="x1l x4x">'
282 || appraisal_type
283 || '</td>'
284 || '<td class="x1l x4x">'
285 || appraisal_status
286 || '</td>'
287 || '<td class="x1l x4x">'
288 || RESULT
289 || '</td>'
290 || '<td class="x1l x4x">'
291 || reason
292 || '</td>'
293 || '</tr>'
294 || '</table>'
295 || '</td>'
296 || '</tr>'
297 || '</table>'
298 || '</div> <div></div>'
299 || '<script>t(''10'')</script>'
300 || '</td>'
301 || '</tr>'
302 || '</table>';
303 END;
304
305 PROCEDURE change_appr_status (
306 appr_id per_appraisals.appraisal_id%TYPE,
307 item_type IN VARCHAR2,
308 item_key IN VARCHAR2,
309 p_log IN OUT NOCOPY VARCHAR2,
310 chg_appr_status IN OUT NOCOPY VARCHAR2
311 )
312 IS
313 CURSOR get_appraisal_info (appr_id per_appraisals.appraisal_id%TYPE)
314 IS
315 SELECT appr.appraisal_id,
316 appr.object_version_number,
317 appr.appraiser_person_id,
318 appr.appraisee_person_id,
319 ppf.full_name,
320 appr.appraisal_date,
321 apprstatus.meaning appraisal_status,
322 apprtype.meaning appraisal_type,
323 NVL (appr.provide_overall_feedback, 'N') provide_overall_feedback,
324 appr.appraisal_system_status
325 FROM per_appraisals appr,
326 per_all_people_f ppf,
327 hr_lookups apprstatus,
328 hr_lookups apprtype
329 WHERE appr.appraisal_id = appr_id
330 AND ppf.person_id = appr.appraisee_person_id
331 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
332 AND apprstatus.lookup_type = 'APPRAISAL_SYSTEM_STATUS'
333 AND apprstatus.lookup_code = appr.appraisal_system_status
334 AND apprtype.lookup_type = 'APPRAISAL_SYS_TYPE'
335 AND apprtype.lookup_code = appr.system_type;
336
337 CURSOR get_appraisal_status (appr_id per_appraisals.appraisal_id%TYPE)
338 IS
339 SELECT hrl.meaning
340 FROM per_appraisals appr, hr_lookups hrl
341 WHERE appraisal_id = appr_id
342 AND hrl.lookup_type = 'APPRAISAL_SYSTEM_STATUS'
343 AND appr.appraisal_system_status = hrl.lookup_code;
344
345 TYPE appraisal_rec IS RECORD (
346 appraisal_id per_appraisals.appraisal_id%TYPE,
347 object_version_number per_appraisals.object_version_number%TYPE,
348 appraiser_person_id per_appraisals.appraiser_person_id%TYPE,
349 appraisee_person_id per_appraisals.appraisee_person_id%TYPE,
350 full_name per_all_people_f.full_name%TYPE,
351 appraisal_date per_appraisals.appraisal_date%TYPE,
352 apprstatus hr_lookups.meaning%TYPE,
353 apprtype hr_lookups.meaning%TYPE,
354 provide_overall_feedback per_appraisals.provide_overall_feedback%TYPE,
355 appraisal_system_status per_appraisals.appraisal_system_status%TYPE
356 );
357
358 appraisal_record appraisal_rec;
359 error_message VARCHAR2 (500) DEFAULT NULL;
360 lv_appr_result_status hr_lookups.meaning%TYPE;
361 BEGIN
362 hr_multi_message.enable_message_list;
363
364 OPEN get_appraisal_info (appr_id);
365
366 FETCH get_appraisal_info
367 INTO appraisal_record;
368
369 IF get_appraisal_info%NOTFOUND
370 THEN
371 CLOSE get_appraisal_info;
372
373 RAISE lv_no_appraisal;
374 ELSE
375 CLOSE get_appraisal_info;
376
377 -- update the appraisal status to completed.
378 IF (appraisal_record.appraisal_system_status <> 'APPRFEEDBACK')
379 THEN
380 IF (appraisal_record.provide_overall_feedback <> 'Y')
381 THEN
382 chg_appr_status := 'N';
383 hr_appraisals_api.update_appraisal
384 (p_effective_date => TRUNC (SYSDATE),
385 p_appraisal_id => appraisal_record.appraisal_id,
386 p_object_version_number => appraisal_record.object_version_number,
387 p_appraiser_person_id => appraisal_record.appraiser_person_id,
388 p_appraisal_system_status => 'COMPLETED'
389 );
390 ELSE
391 chg_appr_status := 'Y';
392 hr_appraisals_api.update_appraisal
393 (p_effective_date => TRUNC (SYSDATE),
394 p_appraisal_id => appraisal_record.appraisal_id,
395 p_object_version_number => appraisal_record.object_version_number,
396 p_appraiser_person_id => appraisal_record.appraiser_person_id,
397 p_appraisal_system_status => 'APPRFEEDBACK'
398 );
399 END IF;
400 ELSIF (appraisal_record.appraisal_system_status <> 'COMPLETED')
401 THEN
402 hr_appraisals_api.update_appraisal
403 (p_effective_date => TRUNC (SYSDATE),
404 p_appraisal_id => appraisal_record.appraisal_id,
405 p_object_version_number => appraisal_record.object_version_number,
406 p_appraiser_person_id => appraisal_record.appraiser_person_id,
407 p_appraisal_system_status => 'COMPLETED'
408 );
409 END IF;
410 END IF;
411
412 OPEN get_appraisal_status (appr_id);
413
414 FETCH get_appraisal_status
415 INTO lv_appr_result_status;
416
417 IF is_new_appraisal (item_type, item_key)
418 THEN
419 p_log :=
420 hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
421 p_application_id => 'PER'
422 )
423 || '^'
424 || NULL;
425 ELSE
426 generate_appraisal_status
427 (appraisal_record.appraisal_date,
428 appraisal_record.full_name,
429 lv_appr_result_status,
430 appraisal_record.apprtype,
431 hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
432 p_application_id => 'PER'
433 ),
434 NULL,
435 p_log
436 );
437 END IF;
438 EXCEPTION
439 WHEN lv_no_appraisal
440 THEN
441 IF is_new_appraisal (item_type, item_key)
442 THEN
443 p_log :=
444 hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
445 p_application_id => 'PER'
446 )
447 || '^'
448 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_NO_APPRAISAL_RECORD',
449 p_application_id => 'PER'
450 );
451 ELSE
452 generate_appraisal_status
453 (appraisal_record.appraisal_date,
454 appraisal_record.full_name,
455 appraisal_record.apprstatus,
456 appraisal_record.apprtype,
457 hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
458 p_application_id => 'PER'
459 ),
460 hr_util_misc_web.return_msg_text (p_message_name => 'HR_NO_APPRAISAL_RECORD',
461 p_application_id => 'PER'
462 ),
463 p_log
464 );
465 END IF;
466
467 chg_appr_status := 'E';
468 RAISE;
469 WHEN hr_multi_message.error_message_exist
470 THEN
471 FOR i IN 1 .. fnd_msg_pub.count_msg
472 LOOP
473 error_message :=
474 error_message || fnd_msg_pub.get (p_msg_index => i,
475 p_encoded => 'F');
476 END LOOP;
477
478 --bug#3767915
479 fnd_msg_pub.delete_msg;
480
481 IF is_new_appraisal (item_type, item_key)
482 THEN
483 p_log :=
484 hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
485 p_application_id => 'PER'
486 )
487 || '^'
488 || error_message;
489 ELSE
490 generate_appraisal_status
491 (appraisal_record.appraisal_date,
492 appraisal_record.full_name,
493 appraisal_record.apprstatus,
494 appraisal_record.apprtype,
495 hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
496 p_application_id => 'PER'
497 ),
498 error_message,
499 p_log
500 );
501 END IF;
502
503 chg_appr_status := 'E';
504 RAISE;
505 WHEN OTHERS
506 THEN
507 error_message := error_message || SQLERRM;
508
509 IF is_new_appraisal (item_type, item_key)
510 THEN
511 p_log :=
512 hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
513 p_application_id => 'PER'
514 )
515 || '^'
516 || error_message;
517 ELSE
518 generate_appraisal_status
519 (appraisal_record.appraisal_date,
520 appraisal_record.full_name,
521 appraisal_record.apprstatus,
522 appraisal_record.apprtype,
523 hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
524 p_application_id => 'PER'
525 ),
526 error_message,
527 p_log
528 );
529 END IF;
530
531 --set_appr_status_log(item_type, item_key, 'E',p_log);
532 chg_appr_status := 'E';
533 RAISE;
534 END;
535
536 PROCEDURE apply_to_personal_profile (
537 appr_id per_appraisals.appraisal_id%TYPE,
538 p_log IN OUT NOCOPY VARCHAR2,
539 apply_pers_profile_status IN OUT NOCOPY VARCHAR2,
540 p_new_appraisal IN BOOLEAN DEFAULT TRUE,
541 p_talent_mang_src_typ per_appraisal_templates.comp_profile_source_type%TYPE
542 )
543 IS
544 CURSOR get_assessment_competences (appr_id IN NUMBER)
545 IS
546 SELECT pce.competence_id,
547 pc.NAME,
548 pce.competence_element_id,
549 pce.proficiency_level_id,
550 pce.business_group_id,
551 pce.enterprise_id,
552 pce.effective_date_from,
553 pce.effective_date_to,
554 pa.appraisal_id,
555 pa.appraisee_person_id,
556 ppf.party_id,
557 DECODE (rating.step_value, NULL, NULL, rating.step_value || ' - ' || rating.NAME)
558 prof_level
559 FROM per_competence_elements pce,
560 per_appraisals pa,
561 per_all_people_f ppf,
562 per_competences pc,
563 per_rating_levels rating
564 WHERE pce.TYPE = 'ASSESSMENT'
565 AND pce.object_name = 'APPRAISAL_ID'
566 AND pce.object_id = appr_id
567 AND pa.appraisal_id = pce.object_id
568 AND pa.appraisee_person_id = ppf.person_id
569 AND pce.competence_id = pc.competence_id
570 AND pce.proficiency_level_id = rating.rating_level_id(+)
571 AND pce.proficiency_level_id IS NOT NULL
572 AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (ppf.effective_start_date), TRUNC (SYSDATE))
573 AND NVL (TRUNC (ppf.effective_end_date), TRUNC (SYSDATE));
574
575 CURSOR get_personal_competences (appr_id NUMBER)
576 IS
577 SELECT appr.appraisee_person_id,
578 pce.competence_element_id,
579 pce.object_version_number,
580 pce.TYPE,
581 pce.business_group_id,
582 pce.enterprise_id,
583 pce.competence_id,
584 pce.proficiency_level_id,
585 pce.high_proficiency_level_id,
586 pce.weighting_level_id,
587 pce.rating_level_id,
588 pce.person_id,
589 pce.job_id,
590 pce.valid_grade_id,
591 pce.position_id,
592 pce.organization_id,
593 pce.parent_competence_element_id,
594 pce.activity_version_id,
595 pce.assessment_id,
596 pce.assessment_type_id,
597 pce.mandatory,
598 pce.effective_date_from,
599 pce.effective_date_to,
600 pce.group_competence_type,
601 pce.competence_type,
602 pce.normal_elapse_duration,
603 pce.normal_elapse_duration_unit,
604 pce.sequence_number,
605 pce.source_of_proficiency_level,
606 pce.line_score,
607 pce.certification_date,
608 pce.certification_method,
609 pce.next_certification_date,
610 pce.comments,
611 pce.attribute_category,
612 pce.attribute1,
613 pce.attribute2,
614 pce.attribute3,
615 pce.attribute4,
616 pce.attribute5,
617 pce.attribute6,
618 pce.attribute7,
619 pce.attribute8,
620 pce.attribute9,
621 pce.attribute10,
622 pce.attribute11,
623 pce.attribute12,
624 pce.attribute13,
625 pce.attribute14,
626 pce.attribute15,
627 pce.attribute16,
628 pce.attribute17,
629 pce.attribute18,
630 pce.attribute19,
631 pce.attribute20,
632 pce.object_id,
633 pce.object_name,
634 pce.party_id
635 FROM per_appraisals appr, per_competence_elements pce
636 WHERE appr.appraisal_id = appr_id
637 AND appr.appraisee_person_id = pce.person_id
638 AND pce.TYPE = 'PERSONAL'
639 AND TRUNC (SYSDATE) BETWEEN pce.effective_date_from
640 AND NVL (pce.effective_date_to, TRUNC (SYSDATE));
641
642 CURSOR get_outcomes_rec (p_competence_element_id IN NUMBER, p_effective_date IN DATE)
643 IS
644 SELECT ceo.comp_element_outcome_id,
645 ceo.competence_element_id,
646 ceo.outcome_id,
647 ceo.date_from,
648 ceo.date_to,
649 ceo.object_version_number,
650 ceo.attribute_category,
651 ceo.attribute1,
652 ceo.attribute2,
653 ceo.attribute3,
654 ceo.attribute4,
655 ceo.attribute5,
656 ceo.attribute6,
657 ceo.attribute7,
658 ceo.attribute8,
659 ceo.attribute9,
660 ceo.attribute10,
661 ceo.attribute11,
662 ceo.attribute12,
663 ceo.attribute13,
664 ceo.attribute14,
665 ceo.attribute15,
666 ceo.attribute16,
667 ceo.attribute17,
668 ceo.attribute18,
669 ceo.attribute19,
670 ceo.attribute20,
671 ceo.information_category,
672 ceo.information1,
673 ceo.information2,
674 ceo.information3,
675 ceo.information4,
676 ceo.information5,
677 ceo.information6,
678 ceo.information7,
679 ceo.information8,
680 ceo.information9,
681 ceo.information10,
682 ceo.information11,
683 ceo.information12,
684 ceo.information13,
685 ceo.information14,
686 ceo.information15,
687 ceo.information16,
688 ceo.information17,
689 ceo.information18,
690 ceo.information19,
691 ceo.information20
692 FROM per_comp_element_outcomes ceo, per_competence_outcomes co
693 WHERE ceo.competence_element_id = p_competence_element_id
694 AND co.outcome_id = ceo.outcome_id
695 AND co.date_from <= ceo.date_from
696 AND NVL (co.date_to, NVL (ceo.date_to, TRUNC (SYSDATE))) >=
697 NVL (ceo.date_to, TRUNC (SYSDATE));
698
699 -- AND ceo.DATE_FROM <= p_effective_date
700 -- And nvl(ceo.DATE_TO,p_effective_date) >= p_effective_date ;
701 match_found BOOLEAN DEFAULT FALSE;
702 l_old_ovn per_competence_elements.object_version_number%TYPE;
703 l_new_ovn per_competence_elements.object_version_number%TYPE;
704 l_comp_ele_id per_competence_elements.competence_element_id%TYPE;
705 talent_mang_src_typ VARCHAR2 (100);
706 each_comp_status VARCHAR2 (10);
707 error_message VARCHAR2 (500) DEFAULT NULL;
708 assessed_comps NUMBER DEFAULT 0;
709 l_comp_log VARCHAR2 (32767);
710 l_out_from_date per_comp_element_outcomes.date_from%TYPE;
711 l_out_ovn per_comp_element_outcomes.object_version_number%TYPE;
712 l_comp_ele_out_id per_comp_element_outcomes.comp_element_outcome_id%TYPE;
713 l_comp_status per_competence_elements.status%TYPE;
714 l_achieved_date per_competence_elements.achieved_date%TYPE;
715 BEGIN
716 -- write an utility to get the value to apply the changes or not
717 hr_multi_message.enable_message_list;
718
719 FOR assess_comps IN get_assessment_competences (appr_id)
720 LOOP
721 assessed_comps := assessed_comps + 1;
722
723 IF (NOT p_new_appraisal) AND (assessed_comps = 1)
724 THEN
725 l_comp_log :=
726 l_comp_log
727 || '<br> <br> <table width="100%" summary="" border="0" cellspacing="0" cellpadding="0">'
728 || '<tr valign="top">'
729 || '<td align="left" width="70%">'
730 || '<div><div class="x60">'
731 || '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">'
732 || '<tr>'
733 || '<td width="100%"> <h1 class="x18">'
734 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_APPLY_ASSESS_COMPS',
735 p_application_id => 'PER'
736 )
737 || '</h1></td>'
738 || '</tr>'
739 || '<tr>'
740 || '<td class="x2i"></td>'
741 || '</tr>'
742 || '</table>'
743 || '</div>'
744 || '<script>t(void 0,''5'')</script>'
745 || '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">'
746 || '<tr>'
747 || '<td>'
748 || '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">'
749 || '<tr>'
750 || '<th scope="col" class="x1r">'
751 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_COMPETENCY',
752 p_application_id => 'PER'
753 )
754 || '</th>'
755 || '<th scope="col" class="x1r x4j">'
756 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_LEVEL',
757 p_application_id => 'PER'
758 )
759 || ' </th>'
760 || '<th scope="col" class="x1r x4j">'
761 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_RESULT',
762 p_application_id => 'PER'
763 )
764 || '</th>'
765 || '<th scope="col" class="x1r x4j">'
766 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_REASON',
767 p_application_id => 'PER'
768 )
769 || '</th>'
770 || '</tr>';
771
772 IF (LENGTH (l_comp_log) <= 4000)
773 THEN
774 p_log := l_comp_log;
775 END IF;
776 END IF;
777
778 match_found := FALSE;
779 each_comp_status := '';
780
781 FOR pers_comps IN get_personal_competences (appr_id)
782 LOOP
783 IF ( assess_comps.competence_id = pers_comps.competence_id
784 AND assess_comps.effective_date_from > pers_comps.effective_date_from
785 )
786 THEN
787 BEGIN
788 --bug#3767915
789 match_found := TRUE;
790 -- end date the element and create a new record
791 l_old_ovn := pers_comps.object_version_number;
792 hr_competence_element_api.update_competence_element
793 (p_competence_element_id => pers_comps.competence_element_id,
794 p_object_version_number => l_old_ovn,
795 p_effective_date_to => TRUNC (SYSDATE) - 1,
796 p_effective_date => TRUNC (SYSDATE),
797 p_validate => FALSE
798 );
799 -- Start Added for competence Qualification link enhancement
800 l_comp_status :=
801 percompstatus.get_competence_status (pers_comps.competence_id,
802 pers_comps.competence_element_id,
803 NULL,
804 NULL,
805 NULL,
806 TRUNC (SYSDATE)
807 );
808
809 IF l_comp_status = 'ACHIEVED'
810 THEN
811 l_achieved_date := TRUNC (SYSDATE);
812 ELSE
813 l_achieved_date := NULL;
814 END IF;
815
816 -- create a new sequence
817 hr_competence_element_api.create_competence_element
818 (p_competence_element_id => l_comp_ele_id,
819 p_object_version_number => l_new_ovn,
820 p_type => 'PERSONAL',
821 p_competence_id => pers_comps.competence_id,
822 p_effective_date => TRUNC (SYSDATE),
823 p_effective_date_from => TRUNC (SYSDATE),
824 p_proficiency_level_id => assess_comps.proficiency_level_id,
825 p_business_group_id => pers_comps.business_group_id,
826 p_source_of_proficiency_level => p_talent_mang_src_typ,
827 p_party_id => pers_comps.party_id,
828 p_person_id => pers_comps.appraisee_person_id,
829 p_attribute_category => pers_comps.attribute_category,
830 p_attribute1 => pers_comps.attribute1,
831 p_attribute2 => pers_comps.attribute2,
832 p_attribute3 => pers_comps.attribute3,
833 p_attribute4 => pers_comps.attribute4,
834 p_attribute5 => pers_comps.attribute5,
835 p_attribute6 => pers_comps.attribute6,
836 p_attribute7 => pers_comps.attribute7,
837 p_attribute8 => pers_comps.attribute8,
838 p_attribute9 => pers_comps.attribute9,
839 p_attribute10 => pers_comps.attribute10,
840 p_attribute11 => pers_comps.attribute11,
841 p_attribute12 => pers_comps.attribute12,
842 p_attribute13 => pers_comps.attribute13,
843 p_attribute14 => pers_comps.attribute14,
844 p_attribute15 => pers_comps.attribute15,
845 p_attribute16 => pers_comps.attribute16,
846 p_attribute17 => pers_comps.attribute17,
847 p_attribute18 => pers_comps.attribute18,
848 p_attribute19 => pers_comps.attribute19,
849 p_attribute20 => pers_comps.attribute20,
850 p_status => l_comp_status
851 -- added for competence qual enhancement
852 ,
853 p_achieved_date => l_achieved_date
854 -- added for competence qual enhancement
855 );
856
857 -- Added for competence qualification link enhancement
858 FOR outcome_rec IN get_outcomes_rec (pers_comps.competence_element_id,
859 TRUNC (SYSDATE)
860 )
861 LOOP
862 l_out_ovn := NULL;
863 l_comp_ele_out_id := NULL;
864 -- IF Outcome_rec.DATE_FROM < trunc(sysdate) Then
865 -- l_out_from_date := trunc(sysdate);
866 -- else
867 l_out_from_date := outcome_rec.date_from;
868 -- END if;
869 hr_comp_element_outcome_api.create_element_outcome
870 (p_comp_element_outcome_id => l_comp_ele_out_id,
871 p_object_version_number => l_out_ovn,
872 p_effective_date => TRUNC (SYSDATE),
873 p_competence_element_id => l_comp_ele_id,
874 p_outcome_id => outcome_rec.outcome_id,
875 p_date_from => l_out_from_date,
876 p_date_to => outcome_rec.date_to,
877 p_attribute_category => outcome_rec.attribute_category,
878 p_attribute1 => outcome_rec.attribute1,
879 p_attribute2 => outcome_rec.attribute2,
880 p_attribute3 => outcome_rec.attribute3,
881 p_attribute4 => outcome_rec.attribute4,
882 p_attribute5 => outcome_rec.attribute5,
883 p_attribute6 => outcome_rec.attribute6,
884 p_attribute7 => outcome_rec.attribute7,
885 p_attribute8 => outcome_rec.attribute8,
886 p_attribute9 => outcome_rec.attribute9,
887 p_attribute10 => outcome_rec.attribute10,
888 p_attribute11 => outcome_rec.attribute11,
889 p_attribute12 => outcome_rec.attribute12,
890 p_attribute13 => outcome_rec.attribute13,
891 p_attribute14 => outcome_rec.attribute14,
892 p_attribute15 => outcome_rec.attribute15,
893 p_attribute16 => outcome_rec.attribute16,
894 p_attribute17 => outcome_rec.attribute17,
895 p_attribute18 => outcome_rec.attribute18,
896 p_attribute19 => outcome_rec.attribute19,
897 p_attribute20 => outcome_rec.attribute20,
898 p_information_category => outcome_rec.information_category,
899 p_information1 => outcome_rec.information1,
900 p_information2 => outcome_rec.information2,
901 p_information3 => outcome_rec.information3,
902 p_information4 => outcome_rec.information4,
903 p_information5 => outcome_rec.information5,
904 p_information6 => outcome_rec.information6,
905 p_information7 => outcome_rec.information7,
906 p_information8 => outcome_rec.information8,
907 p_information9 => outcome_rec.information9,
908 p_information10 => outcome_rec.information10,
909 p_information11 => outcome_rec.information11,
910 p_information12 => outcome_rec.information12,
911 p_information13 => outcome_rec.information13,
912 p_information14 => outcome_rec.information14,
913 p_information15 => outcome_rec.information15,
914 p_information16 => outcome_rec.information16,
915 p_information17 => outcome_rec.information17,
916 p_information18 => outcome_rec.information18,
917 p_information19 => outcome_rec.information19,
918 p_information20 => outcome_rec.information20
919 );
920 END LOOP;
921
922 -- End for competence qualification link enhancement
923 each_comp_status := 'S';
924
925 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ' || hr_util_misc_web.return_msg_text(
926 -- p_message_name=>'HR_ASSESS_COMP_SUCC',
927 -- p_Application_id=>'PER') ||' </A> ';
928 IF p_new_appraisal
929 THEN
930 l_comp_log :=
931 l_comp_log
932 || assess_comps.competence_element_id
933 || '^'
934 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
935 p_application_id => 'PER'
936 )
937 || '~';
938 ELSE
939 l_comp_log :=
940 l_comp_log
941 || '<tr>
942 <td class="x1l x4x" nowrap>'
943 || assess_comps.NAME
944 || '</td>
945 <td class="x1l x4x" nowrap>'
946 || assess_comps.prof_level
947 || '</td>
948 <td class="x1l x4x">'
949 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
950 p_application_id => 'PER'
951 )
952 || '</td>
953 <td class="x1l x4x">'
954 || '</td>
955 </tr>';
956 END IF;
957
958 IF (LENGTH (l_comp_log) <= 4000)
959 THEN
960 p_log := l_comp_log;
961 END IF;
962 EXCEPTION
963 WHEN hr_multi_message.error_message_exist
964 THEN
965 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ';
966 FOR i IN 1 .. fnd_msg_pub.count_msg
967 LOOP
968 error_message :=
969 error_message || fnd_msg_pub.get (p_msg_index => i,
970 p_encoded => 'F');
971 END LOOP;
972
973 --bug#3767915
974 fnd_msg_pub.delete_msg;
975
976 IF p_new_appraisal
977 THEN
978 l_comp_log :=
979 l_comp_log
980 || assess_comps.competence_element_id
981 || '^'
982 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
983 p_application_id => 'PER'
984 )
985 || '^'
986 || error_message
987 || '~';
988 ELSE
989 l_comp_log :=
990 l_comp_log
991 || '<tr>
992 <td class="x1l x4x" nowrap>'
993 || assess_comps.NAME
994 || '</td>
995 <td class="x1l x4x" nowrap>'
996 || assess_comps.prof_level
997 || '</td>
998 <td class="x1l x4x">'
999 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1000 p_application_id => 'PER'
1001 )
1002 || '</td>
1003 <td class="x1l x4x">'
1004 || error_message
1005 || '</td>
1006 </tr>';
1007 END IF;
1008
1009 IF (LENGTH (l_comp_log) <= 4000)
1010 THEN
1011 p_log := l_comp_log;
1012 END IF;
1013
1014 error_message := NULL;
1015 --p_log := p_log || '</A>';
1016 --set_appr_status_log(item_type, item_key, 'W',p_log);
1017 apply_pers_profile_status := 'W';
1018 each_comp_status := 'E';
1019 WHEN OTHERS
1020 THEN
1021 error_message := error_message || SQLERRM;
1022
1023 IF p_new_appraisal
1024 THEN
1025 l_comp_log :=
1026 l_comp_log
1027 || assess_comps.competence_element_id
1028 || '^'
1029 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1030 p_application_id => 'PER'
1031 )
1032 || '^'
1033 || error_message
1034 || '~';
1035 ELSE
1036 l_comp_log :=
1037 l_comp_log
1038 || '<tr>
1039 <td class="x1l x4x" nowrap>'
1040 || assess_comps.NAME
1041 || '</td>
1042 <td class="x1l x4x" nowrap>'
1043 || assess_comps.prof_level
1044 || '</td>
1045 <td class="x1l x4x">'
1046 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1047 p_application_id => 'PER'
1048 )
1049 || '</td>
1050 <td class="x1l x4x">'
1051 || error_message
1052 || '</td>
1053 </tr>';
1054 END IF;
1055
1056 IF (LENGTH (l_comp_log) <= 4000)
1057 THEN
1058 p_log := l_comp_log;
1059 END IF;
1060
1061 error_message := NULL;
1062 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ' || sqlcode || sqlerrm || ' </A> ';
1063
1064 --set_appr_status_log(item_type, item_key, 'W',p_log);
1065 apply_pers_profile_status := 'W';
1066 each_comp_status := 'E';
1067 END;
1068 END IF;
1069 END LOOP;
1070
1071 IF (match_found = FALSE)
1072 THEN
1073 BEGIN
1074 l_comp_ele_id := NULL;
1075 -- since we r not supporting the flex in appraisal
1076 -- we wont be having flex parameters to update
1077 -- Start Added for competence Qualification link enhancement
1078 l_comp_status :=
1079 percompstatus.get_competence_status (assess_comps.competence_id,
1080 NULL,
1081 NULL,
1082 NULL,
1083 NULL,
1084 TRUNC (SYSDATE)
1085 );
1086
1087 IF l_comp_status = 'ACHIEVED'
1088 THEN
1089 l_achieved_date := TRUNC (SYSDATE);
1090 ELSE
1091 l_achieved_date := NULL;
1092 END IF;
1093
1094 hr_competence_element_api.create_competence_element
1095 (p_competence_element_id => l_comp_ele_id,
1096 p_object_version_number => l_new_ovn,
1097 p_type => 'PERSONAL',
1098 p_business_group_id => assess_comps.business_group_id,
1099 p_competence_id => assess_comps.competence_id,
1100 p_proficiency_level_id => assess_comps.proficiency_level_id
1101 -- Modified from prof_level for competence qual enhanc
1102 ,
1103 p_person_id => assess_comps.appraisee_person_id,
1104 p_effective_date_from => TRUNC (SYSDATE),
1105 p_effective_date => TRUNC (SYSDATE),
1106 p_party_id => assess_comps.party_id,
1107 p_source_of_proficiency_level => p_talent_mang_src_typ,
1108 p_status => l_comp_status
1109 -- added for competence qual enhancement
1110 ,
1111 p_achieved_date => l_achieved_date
1112 -- added for competence qual enhancement
1113 );
1114 each_comp_status := 'S';
1115
1116 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ' || hr_util_misc_web.return_msg_text(
1117 -- p_message_name=>'HR_ASSESS_COMP_SUCC',
1118 -- p_Application_id=>'PER') || '</A>';
1119 IF p_new_appraisal
1120 THEN
1121 l_comp_log :=
1122 l_comp_log
1123 || assess_comps.competence_element_id
1124 || '^'
1125 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
1126 p_application_id => 'PER'
1127 )
1128 || '~';
1129 ELSE
1130 l_comp_log :=
1131 l_comp_log
1132 || '<tr>
1133 <td class="x1l x4x" nowrap>'
1134 || assess_comps.NAME
1135 || '</td>
1136 <td class="x1l x4x" nowrap>'
1137 || assess_comps.prof_level
1138 || '</td>
1139 <td class="x1l x4x">'
1140 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
1141 p_application_id => 'PER'
1142 )
1143 || '</td>
1144 <td class="x1l x4x">'
1145 || '</td>
1146 </tr>';
1147 END IF;
1148
1149 IF (LENGTH (l_comp_log) <= 4000)
1150 THEN
1151 p_log := l_comp_log;
1152 END IF;
1153 EXCEPTION
1154 WHEN hr_multi_message.error_message_exist
1155 THEN
1156 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ';
1157 FOR i IN 1 .. fnd_msg_pub.count_msg
1158 LOOP
1159 error_message :=
1160 error_message || fnd_msg_pub.get (p_msg_index => i,
1161 p_encoded => 'F');
1162 END LOOP;
1163
1164 --bug#3767915
1165 fnd_msg_pub.delete_msg;
1166
1167 --p_log := p_log || ' </A> ';
1168 IF p_new_appraisal
1169 THEN
1170 l_comp_log :=
1171 l_comp_log
1172 || assess_comps.competence_element_id
1173 || '^'
1174 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1175 p_application_id => 'PER'
1176 )
1177 || '^'
1178 || error_message
1179 || '~';
1180 ELSE
1181 l_comp_log :=
1182 l_comp_log
1183 || '<tr>
1184 <td class="x1l x4x" nowrap>'
1185 || assess_comps.NAME
1186 || '</td>
1187 <td class="x1l x4x" nowrap>'
1188 || assess_comps.prof_level
1189 || '</td>
1190 <td class="x1l x4x">'
1191 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1192 p_application_id => 'PER'
1193 )
1194 || '</td>
1195 <td class="x1l x4x">'
1196 || error_message
1197 || '</td>
1198 </tr>';
1199 END IF;
1200
1201 IF (LENGTH (l_comp_log) <= 4000)
1202 THEN
1203 p_log := l_comp_log;
1204 END IF;
1205
1206 error_message := NULL;
1207 --set_appr_status_log(item_type, item_key, 'W',p_log);
1208 apply_pers_profile_status := 'W';
1209 each_comp_status := 'E';
1210 WHEN OTHERS
1211 THEN
1212 error_message := error_message || SQLERRM;
1213
1214 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ' || sqlcode || sqlerrm || '</A>';
1215 IF p_new_appraisal
1216 THEN
1217 l_comp_log :=
1218 l_comp_log
1219 || assess_comps.competence_element_id
1220 || '^'
1221 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1222 p_application_id => 'PER'
1223 )
1224 || '^'
1225 || error_message
1226 || '~';
1227 ELSE
1228 l_comp_log :=
1229 l_comp_log
1230 || '<tr>
1231 <td class="x1l x4x" nowrap>'
1232 || assess_comps.NAME
1233 || '</td>
1234 <td class="x1l x4x" nowrap>'
1235 || assess_comps.prof_level
1236 || '</td>
1237 <td class="x1l x4x">'
1238 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1239 p_application_id => 'PER'
1240 )
1241 || '</td>
1242 <td class="x1l x4x">'
1243 || error_message
1244 || '</td>
1245 </tr>';
1246 END IF;
1247
1248 IF (LENGTH (l_comp_log) <= 4000)
1249 THEN
1250 p_log := l_comp_log;
1251 END IF;
1252
1253 --set_appr_status_log(item_type, item_key, 'W',p_log);
1254 apply_pers_profile_status := 'W';
1255 each_comp_status := 'E';
1256 END;
1257 END IF;
1258 END LOOP;
1259
1260 IF (NOT p_new_appraisal)
1261 THEN
1262 l_comp_log :=
1263 l_comp_log
1264 || '</table></td></tr></table></div><div></div><script>t(''10'')</script></td></tr></table>';
1265 END IF;
1266
1267 IF (LENGTH (l_comp_log) <= 4000)
1268 THEN
1269 p_log := l_comp_log;
1270 END IF;
1271
1272 IF NOT apply_pers_profile_status = 'W'
1273 THEN
1274 apply_pers_profile_status := 'S';
1275 END IF;
1276 -- check for any training plan members with appraisal id
1277 -- if exists then change the status of Training Plan members
1278 -- to planned
1279
1280 -- change the status in per_appraisals to completed.
1281 EXCEPTION
1282 WHEN OTHERS
1283 THEN
1284 error_message := error_message || SQLERRM;
1285
1286 IF p_new_appraisal
1287 THEN
1288 l_comp_log :=
1289 l_comp_log
1290 || '-1'
1291 || '^'
1292 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1293 p_application_id => 'PER'
1294 )
1295 || '^'
1296 || error_message
1297 || '~';
1298 ELSE
1299 l_comp_log :=
1300 l_comp_log
1301 || '<tr>
1302 <td class="x1l x4x" nowrap>'
1303 || '</td>
1304 <td class="x1l x4x" >'
1305 || '</td>
1306 <td class="x1l x4x">'
1307 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1308 p_application_id => 'PER'
1309 )
1310 || '</td>
1311 <td class="x1l x4x">'
1312 || error_message
1313 || '</td>
1314 </tr>';
1315 END IF;
1316
1317 IF (LENGTH (l_comp_log) <= 4000)
1318 THEN
1319 p_log := l_comp_log;
1320 END IF;
1321
1322 IF (NOT p_new_appraisal)
1323 THEN
1324 l_comp_log :=
1325 l_comp_log
1326 || '</table></td></tr></table></div><div></div><script>t(''10'')</script></td></tr></table>';
1327 END IF;
1328
1329 IF (LENGTH (l_comp_log) <= 4000)
1330 THEN
1331 p_log := l_comp_log;
1332 END IF;
1333
1334 error_message := NULL;
1335 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || sqlcode || sqlerrm || '</A> ';
1336 --set_appr_status_log(item_type, item_key, 'W',p_log);
1337 apply_pers_profile_status := 'W';
1338 END;
1339
1340 PROCEDURE generate_lp_courses (
1341 p_lp_id NUMBER,
1342 p_log IN OUT NOCOPY VARCHAR2,
1343 p_new_appraisal IN BOOLEAN DEFAULT TRUE
1344 )
1345 IS
1346 query_lp_courses VARCHAR2 (4000)
1347 := ' select tav.version_name course_name, hrl.meaning member_status, '
1348 || ' lpme.completion_target_date, lpme.completion_date '
1349 || ' from ota_learning_path_members lpm, ota_lp_member_enrollments lpme, '
1350 || ' ota_activity_versions tav, hr_lookups hrl '
1351 || ' where lpm.learning_path_id = :1 '
1352 || ' and lpme.learning_path_member_id = lpm.learning_path_member_id '
1353 || ' and hrl.lookup_code = lpme.member_status_code '
1354 || ' and hrl.lookup_type = ''OTA_LP_MEMBER_STATUS'''
1355 || ' and tav.activity_version_id = lpm.activity_version_id ';
1356 l_lp_courses cur_typ;
1357 lv_course_name VARCHAR2 (80);
1358 lv_course_status hr_lookups.meaning%TYPE;
1359 lv_course_targe_date DATE;
1360 lv_course_compl_date DATE;
1361 ln_count NUMBER DEFAULT 0;
1362 BEGIN
1363 OPEN l_lp_courses FOR query_lp_courses USING p_lp_id;
1364
1365 LOOP
1366 FETCH l_lp_courses
1367 INTO lv_course_name,
1368 lv_course_status,
1369 lv_course_targe_date,
1370 lv_course_compl_date;
1371
1372 EXIT WHEN l_lp_courses%NOTFOUND;
1373
1374 BEGIN
1375 ln_count := ln_count + 1;
1376
1377 IF (NOT p_new_appraisal)
1378 THEN
1379 IF ln_count = 1
1380 THEN
1381 p_log :=
1382 p_log
1383 || '<br>'
1384 || '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">'
1385 || '<tr>'
1386 || '<td>'
1387 || '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">'
1388 || '<tr>'
1389 || '<th scope="col" class="x1r">'
1390 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_LP_COURSE',
1391 p_application_id => 'PER'
1392 )
1393 || '</th>'
1394 || '<th scope="col" class="x1r x4j">'
1395 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_STATUS',
1396 p_application_id => 'PER'
1397 )
1398 || '</th>'
1399 || '<th scope="col" class="x1r x4j">'
1400 || hr_util_misc_web.return_msg_text
1401 (p_message_name => 'HR_LP_COURSE_TARGET_DATE',
1402 p_application_id => 'PER'
1403 )
1404 || '</th>'
1405 || '<th scope="col" class="x1r x4j">'
1406 || hr_util_misc_web.return_msg_text
1407 (p_message_name => 'HR_LP_COURSE_COMP_DATE',
1408 p_application_id => 'PER'
1409 )
1410 || '</th>'
1411 || '</tr>';
1412 p_log :=
1413 p_log
1414 || '<tr> '
1415 || '<td class="x1l x4x" nowrap>'
1416 || lv_course_name
1417 || '</td>'
1418 || '<td class="x1l x4x" nowrap>'
1419 || lv_course_status
1420 || '</td>'
1421 || '<td class="x1l x4x" nowrap>'
1422 || lv_course_targe_date
1423 || '</td>'
1424 || '<td class="x1l x4x" nowrap>'
1425 || lv_course_compl_date
1426 || '</td>'
1427 || '</tr>';
1428 ELSE
1429 p_log :=
1430 p_log
1431 || '<tr> '
1432 || '<td class="x1l x4x" nowrap>'
1433 || lv_course_name
1434 || '</td>'
1435 || '<td class="x1l x4x" nowrap>'
1436 || lv_course_status
1437 || '</td>'
1438 || '<td class="x1l x4x" nowrap>'
1439 || lv_course_targe_date
1440 || '</td>'
1441 || '<td class="x1l x4x" nowrap>'
1442 || lv_course_compl_date
1443 || '</td>'
1444 || '</tr>';
1445 END IF;
1446 END IF;
1447 END;
1448 END LOOP;
1449
1450 CLOSE l_lp_courses;
1451
1452 IF (ln_count > 0) AND (NOT p_new_appraisal)
1453 THEN
1454 p_log := p_log || '</table>' || '</td>' || '</tr>' || '</table>';
1455 END IF;
1456 EXCEPTION
1457 WHEN OTHERS
1458 THEN
1459 p_log := p_log || ' Error in Courses generation ';
1460
1461 CLOSE l_lp_courses;
1462 END;
1463
1464 PROCEDURE update_train_component_status (
1465 appr_id IN per_appraisals.appraisal_id%TYPE,
1466 p_log IN OUT NOCOPY VARCHAR2,
1467 upd_train_comps_status IN OUT NOCOPY VARCHAR2,
1468 p_new_appraisal IN BOOLEAN DEFAULT TRUE
1469 )
1470 IS
1471 l_cursor cur_typ;
1472 l_status_cursor cur_typ;
1473 l_lp_id NUMBER;
1474 l_lp_ovn NUMBER;
1475 l_display_to_learner_flag VARCHAR2 (10);
1476 l_lpe_id NUMBER;
1477 l_lpe_ovn NUMBER;
1478 l_path_status VARCHAR2 (100);
1479 l_completion_status VARCHAR2 (20);
1480 l_lp_name VARCHAR2 (80);
1481 query_str VARCHAR2 (4000)
1482 := ' select lp.learning_path_id, lp.display_to_learner_flag, lp.object_version_number, '
1483 || ' lpe.lp_enrollment_id,lpe.object_version_number,hrl.meaning , lptl.name '
1484 || ' from ota_learning_paths lp, ota_learning_paths_tl lptl, ota_lp_enrollments lpe, '
1485 || ' hr_lookups hrl '
1486 || ' where lp.source_id = :1 and lp.path_source_code= :2 '
1487 || ' and lp.source_function_code = :3 '
1488 || ' and lptl.learning_path_id = lp.learning_path_id '
1489 || ' and lptl.language = userenv(''lang'') '
1490 || ' and lpe.learning_path_id = lp.learning_path_id '
1491 || ' and hrl.lookup_code = lpe.path_status_code '
1492 || ' and hrl.lookup_type = ''OTA_LEARNING_PATH_STATUS''';
1493 query_lp_status VARCHAR2 (4000)
1494 := ' select hrl.meaning path_status_code '
1495 || ' from ota_learning_paths lp, '
1496 || ' ota_lp_enrollments lpe, hr_lookups hrl '
1497 || ' where lp.learning_path_id = :1 '
1498 || ' and lpe.learning_path_id = lp.learning_path_id '
1499 || ' and lpe.path_status_code = hrl.lookup_code '
1500 || ' and hrl.lookup_type = ''OTA_LEARNING_PATH_STATUS''';
1501 l_stmt VARCHAR2 (2000);
1502 found_training_activities BOOLEAN DEFAULT FALSE;
1503 error_message VARCHAR2 (500) DEFAULT NULL;
1504 l_lp_status_cursor cur_typ;
1505 BEGIN
1506 hr_multi_message.enable_message_list;
1507
1508 BEGIN
1509 OPEN l_cursor FOR query_str USING appr_id, 'TALENT_MGMT', 'APPRAISAL';
1510 EXCEPTION
1511 WHEN OTHERS
1512 THEN
1513 CLOSE l_cursor;
1514
1515 p_log := '';
1516 upd_train_comps_status := 'S';
1517 RETURN;
1518 END;
1519
1520 FETCH l_cursor
1521 INTO l_lp_id,
1522 l_display_to_learner_flag,
1523 l_lp_ovn,
1524 l_lpe_id,
1525 l_lpe_ovn,
1526 l_path_status,
1527 l_lp_name;
1528
1529 IF l_cursor%FOUND
1530 THEN
1531 IF (NOT p_new_appraisal)
1532 THEN
1533 p_log :=
1534 p_log
1535 || '<br> <br> <table width="100%" summary="" border="0" cellspacing="0" cellpadding="0">'
1536 || '<tr valign="top">'
1537 || '<td align="left" width="70%">'
1538 || '<div><div class="x60">'
1539 || '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">'
1540 || '<tr>'
1541 || '<td width="100%"> <h1 class="x18">'
1542 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_LEARNING_PATH',
1543 p_application_id => 'PER'
1544 )
1545 || '</h1></td>'
1546 || '</tr>'
1547 || '<tr>'
1548 || '<td class="x2i"></td>'
1549 || '</tr>'
1550 || '</table>'
1551 || '</div>'
1552 || '<script>t(void 0,''5'')</script>'
1553 || '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">'
1554 || '<tr>'
1555 || '<td>'
1556 || '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">'
1557 || '<tr>'
1558 || '<th scope="col" class="x1r">'
1559 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_LEARNING_PATH',
1560 p_application_id => 'PER'
1561 )
1562 || '</th>'
1563 || '<th scope="col" class="x1r x4j">'
1564 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_STATUS',
1565 p_application_id => 'PER'
1566 )
1567 || ' </th>'
1568 || '<th scope="col" class="x1r x4j">'
1569 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_RESULT',
1570 p_application_id => 'PER'
1571 )
1572 || '</th>'
1573 || '<th scope="col" class="x1r x4j">'
1574 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_REASON',
1575 p_application_id => 'PER'
1576 )
1577 || '</th>'
1578 || '</tr>';
1579 END IF;
1580
1581 found_training_activities := TRUE;
1582
1583 -- If DisplayToLearner is unchecked then check it
1584 IF (l_display_to_learner_flag <> 'Y')
1585 THEN
1586 BEGIN
1587 l_stmt :=
1588 'begin ota_learning_path_api.update_learning_path( '
1589 || 'p_effective_date => trunc(sysdate) , '
1590 || 'p_learning_path_id => :1,'
1591 || 'p_object_version_number => :2 ,'
1592 || 'p_display_to_learner_flag => :3 ); end;';
1593
1594 EXECUTE IMMEDIATE l_stmt
1595 USING IN l_lp_id, IN OUT l_lp_ovn, IN 'Y';
1596 EXCEPTION
1597 WHEN hr_multi_message.error_message_exist
1598 THEN
1599 error_message :=
1600 error_message
1601 || hr_util_misc_web.return_msg_text
1602 (p_message_name => 'HR_APPR_DISP_TO_LEARNER',
1603 p_application_id => 'PER'
1604 )
1605 || ' => ';
1606
1607 FOR i IN 1 .. fnd_msg_pub.count_msg
1608 LOOP
1609 error_message :=
1610 error_message || fnd_msg_pub.get (p_msg_index => i,
1611 p_encoded => 'F');
1612 END LOOP;
1613
1614 --bug#3767915
1615 fnd_msg_pub.delete_msg;
1616 --set_appr_status_log(item_type, item_key, 'W',p_log);
1617 upd_train_comps_status := 'W';
1618 WHEN OTHERS
1619 THEN
1620 error_message :=
1621 error_message
1622 || hr_util_misc_web.return_msg_text
1623 (p_message_name => 'HR_APPR_DISP_TO_LEARNER',
1624 p_application_id => 'PER'
1625 )
1626 || ' => '
1627 || SQLERRM;
1628 upd_train_comps_status := 'W';
1629 END;
1630 END IF; -- Display To Learner Flag
1631
1632 -- If Learning Path Status is not ACTIVE then make it ACTIVE so
1633 -- that it triggers the completion process.
1634 IF (l_path_status <> 'ACTIVE')
1635 THEN
1636 BEGIN
1637 l_stmt :=
1638 'begin ota_lp_enrollment_api.update_lp_enrollment( '
1639 || 'p_effective_date => trunc(sysdate) , '
1640 || 'p_lp_enrollment_id => :1 ,'
1641 || 'p_object_version_number => :2 ,'
1642 || 'p_path_status_code => :3); end;';
1643
1644 EXECUTE IMMEDIATE l_stmt
1645 USING IN l_lpe_id, IN OUT l_lpe_ovn, IN 'ACTIVE';
1646 EXCEPTION
1647 WHEN hr_multi_message.error_message_exist
1648 THEN
1649 error_message :=
1650 error_message
1651 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_LP_STATUS',
1652 p_application_id => 'PER'
1653 )
1654 || ' => ';
1655
1656 FOR i IN 1 .. fnd_msg_pub.count_msg
1657 LOOP
1658 error_message :=
1659 error_message || fnd_msg_pub.get (p_msg_index => i,
1660 p_encoded => 'F');
1661 END LOOP;
1662
1663 --bug#3767915
1664 fnd_msg_pub.delete_msg;
1665 --set_appr_status_log(item_type, item_key, 'W',p_log);
1666 upd_train_comps_status := 'W';
1667 WHEN OTHERS
1668 THEN
1669 error_message :=
1670 error_message
1671 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_LP_STATUS',
1672 p_application_id => 'PER'
1673 )
1674 || ' => '
1675 || SQLERRM;
1676 upd_train_comps_status := 'W';
1677 END;
1678 END IF; -- Learning Path Status change
1679
1680 -- try to find out if the LP can be completed at this juncture or not
1681 BEGIN
1682 query_str :=
1683 'SELECT ota_lrng_path_util.chk_complete_path_ok(:1) from dual';
1684
1685 OPEN l_status_cursor FOR query_str USING l_lpe_id;
1686 EXCEPTION
1687 WHEN hr_multi_message.error_message_exist
1688 THEN
1689 error_message :=
1690 error_message
1691 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_LP_COMPLETE',
1692 p_application_id => 'PER'
1693 )
1694 || ' => ';
1695
1696 FOR i IN 1 .. fnd_msg_pub.count_msg
1697 LOOP
1698 error_message :=
1699 error_message || fnd_msg_pub.get (p_msg_index => i,
1700 p_encoded => 'F');
1701 END LOOP;
1702 WHEN OTHERS
1703 THEN
1704 error_message :=
1705 error_message
1706 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_APPR_LP_COMPLETE',
1707 p_application_id => 'PER'
1708 )
1709 || ' => '
1710 || SQLERRM;
1711 upd_train_comps_status := 'S';
1712 END;
1713
1714 FETCH l_status_cursor
1715 INTO l_completion_status;
1716
1717 IF l_status_cursor%FOUND
1718 THEN
1719 IF (l_completion_status = 'S')
1720 THEN
1721 --if returned code is 'S' , complete the LP
1722 BEGIN
1723 l_stmt :=
1724 'begin ota_lrng_path_util.complete_path(p_lp_enrollment_id => :1 ); end;';
1725
1726 EXECUTE IMMEDIATE l_stmt
1727 USING IN l_lpe_id;
1728 EXCEPTION
1729 WHEN hr_multi_message.error_message_exist
1730 THEN
1731 error_message :=
1732 error_message
1733 || hr_util_misc_web.return_msg_text
1734 (p_message_name => 'HR_APPR_LP_COMPLETE',
1735 p_application_id => 'PER'
1736 )
1737 || ' => ';
1738
1739 FOR i IN 1 .. fnd_msg_pub.count_msg
1740 LOOP
1741 error_message :=
1742 error_message || fnd_msg_pub.get (p_msg_index => i,
1743 p_encoded => 'F');
1744 END LOOP;
1745
1746 --bug#3767915
1747 fnd_msg_pub.delete_msg;
1748 --set_appr_status_log(item_type, item_key, 'W',p_log);
1749 upd_train_comps_status := 'W';
1750 WHEN OTHERS
1751 THEN
1752 error_message :=
1753 error_message
1754 || hr_util_misc_web.return_msg_text
1755 (p_message_name => 'HR_APPR_LP_COMPLETE',
1756 p_application_id => 'PER'
1757 )
1758 || ' => '
1759 || SQLERRM;
1760 upd_train_comps_status := 'W';
1761 END;
1762 END IF;
1763 END IF; -- Complete Learning Path
1764
1765 -- The LP status can be completed with above step
1766 -- or it remains in the previous step.
1767 BEGIN
1768 OPEN l_lp_status_cursor FOR query_lp_status USING l_lp_id;
1769 EXCEPTION
1770 WHEN OTHERS
1771 THEN
1772 CLOSE l_lp_status_cursor;
1773 END;
1774
1775 FETCH l_lp_status_cursor
1776 INTO l_completion_status;
1777
1778 IF p_new_appraisal
1779 THEN
1780 IF (error_message IS NULL)
1781 THEN
1782 p_log :=
1783 l_lp_id
1784 || '^'
1785 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
1786 p_application_id => 'PER'
1787 )
1788 || '^';
1789 ELSE
1790 p_log :=
1791 l_lp_id
1792 || '^'
1793 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1794 p_application_id => 'PER'
1795 )
1796 || '^'
1797 || error_message;
1798 END IF;
1799 ELSE
1800 IF (error_message IS NULL)
1801 THEN
1802 p_log :=
1803 p_log
1804 || '<tr> '
1805 || '<td class="x1l x4x" nowrap>'
1806 || l_lp_name
1807 || '</td>'
1808 || '<td class="x1l x4x" nowrap>'
1809 || l_completion_status
1810 || '</td>'
1811 || '<td class="x1l x4x">'
1812 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
1813 p_application_id => 'PER'
1814 )
1815 || '</td>'
1816 || '<td class="x1l x4x">'
1817 || '</td>'
1818 || '</tr>'
1819 || '</table>'
1820 || '</td>'
1821 || '</tr>'
1822 || '</table>'
1823 || '</div>'
1824 || '<div></div>'
1825 || '<script>t(''10'')</script>'
1826 || '</td>'
1827 || '</tr>'
1828 || '</table>';
1829 ELSE
1830 p_log :=
1831 p_log
1832 || '<tr> '
1833 || '<td class="x1l x4x" nowrap>'
1834 || l_lp_name
1835 || '</td>'
1836 || '<td class="x1l x4x" nowrap>'
1837 || l_completion_status
1838 || '</td>'
1839 || '<td class="x1l x4x">'
1840 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1841 p_application_id => 'PER'
1842 )
1843 || '</td>'
1844 || '<td class="x1l x4x">'
1845 || error_message
1846 || '</td>'
1847 || '</tr>'
1848 || '</table>'
1849 || '</td>'
1850 || '</tr>'
1851 || '</table>'
1852 || '</div>'
1853 || '<div></div>'
1854 || '<script>t(''10'')</script>'
1855 || '</td>'
1856 || '</tr>'
1857 || '</table>';
1858 END IF; -- Error Message
1859 END IF;
1860
1861 -- generate courses table
1862 generate_lp_courses (l_lp_id, p_log, p_new_appraisal);
1863 END IF; -- If Learning Path Exists
1864
1865 IF l_cursor%ISOPEN
1866 THEN
1867 CLOSE l_cursor;
1868 END IF;
1869
1870 IF l_status_cursor%ISOPEN
1871 THEN
1872 CLOSE l_status_cursor;
1873 END IF;
1874
1875 IF found_training_activities = FALSE
1876 THEN
1877 p_log := '';
1878 END IF;
1879
1880 IF NOT upd_train_comps_status = 'W'
1881 THEN
1882 upd_train_comps_status := 'S';
1883 END IF;
1884 EXCEPTION
1885 WHEN OTHERS
1886 THEN
1887 CLOSE l_cursor;
1888
1889 error_message := error_message || SQLERRM;
1890
1891 IF p_new_appraisal
1892 THEN
1893 p_log :=
1894 l_lp_id
1895 || '^'
1896 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1897 p_application_id => 'PER'
1898 )
1899 || '^'
1900 || error_message;
1901 ELSE
1902 p_log :=
1903 p_log
1904 || '<tr> '
1905 || '<td class="x1l x4x" nowrap>'
1906 || l_lp_name
1907 || '</td>'
1908 || '<td class="x1l x4x" nowrap>'
1909 || l_path_status
1910 || '</td>'
1911 || '<td class="x1l x4x">'
1912 || hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
1913 p_application_id => 'PER'
1914 )
1915 || '</td>'
1916 || '<td class="x1l x4x">'
1917 || error_message
1918 || '</td>'
1919 || '</tr>'
1920 || '</table>'
1921 || '</td>'
1922 || '</tr>'
1923 || '</table>'
1924 || '</div>'
1925 || '<div></div>'
1926 || '<script>t(''10'')</script>'
1927 || '</td>'
1928 || '</tr>'
1929 || '</table>';
1930 END IF;
1931
1932 --set_appr_status_log(item_type, item_key, 'W',p_log);
1933 upd_train_comps_status := 'W';
1934 END;
1935
1936 PROCEDURE create_event (
1937 appr_id per_appraisals.appraisal_id%TYPE,
1938 p_log IN OUT NOCOPY VARCHAR2,
1939 upd_create_event_status IN OUT NOCOPY VARCHAR2,
1940 p_new_appraisal IN BOOLEAN DEFAULT TRUE
1941 )
1942 IS
1943 --
1944 -- To modify this template, edit file PROC.TXT in TEMPLATE
1945 -- directory of SQL Navigator
1946 --
1947 -- Purpose: Briefly explain the functionality of the procedure
1948 --
1949 -- MODIFICATION HISTORY
1950 -- Person Date Comments
1951 -- --------- ------ -------------------------------------------
1952 l_ovn NUMBER;
1953 l_event_id per_events.event_id%TYPE;
1954 l_perf_rev_id NUMBER;
1955 l_next_rev_date_warn BOOLEAN;
1956 l_sql_err VARCHAR2 (10000);
1957
1958 CURSOR get_appr_overall_perf (appr_id per_appraisals.appraisal_id%TYPE)
1959 IS
1960 SELECT appr.appraisal_id,
1961 appr.object_version_number,
1962 appr.appraiser_person_id,
1963 appr.appraisee_person_id,
1964 appr.overall_performance_level_id,
1965 prl.step_value,
1966 (prl.step_value || ' - ' || prl.NAME) overall_rating
1967 FROM per_appraisals appr, per_rating_levels prl, hr_lookups hrl
1968 WHERE appraisal_id = appr_id
1969 AND appr.overall_performance_level_id = prl.rating_level_id
1970 AND hrl.lookup_code = TO_CHAR (prl.step_value)
1971 AND hrl.lookup_type = 'PERFORMANCE_RATING'
1972 AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, TRUNC (SYSDATE))
1973 AND NVL (end_date_active, TRUNC (SYSDATE));
1974
1975 TYPE appraisal_record IS RECORD (
1976 appraisal_id per_appraisals.appraisal_id%TYPE,
1977 object_version_number per_appraisals.object_version_number%TYPE,
1978 appraiser_person_id per_appraisals.appraiser_person_id%TYPE,
1979 appraisee_person_id per_appraisals.appraisee_person_id%TYPE,
1980 overall_performance_level_id per_appraisals.appraisee_person_id%TYPE,
1981 step_value per_rating_levels.step_value%TYPE,
1982 overall_perf_rating VARCHAR2 (100)
1983 );
1984
1985 appraisal_rec appraisal_record;
1986
1987 CURSOR check_contingent_worker (appraisee_person_id per_all_people_f.person_id%TYPE)
1988 IS
1989 SELECT current_npw_flag
1990 FROM per_all_people_f
1991 WHERE person_id = appraisee_person_id
1992 AND TRUNC (SYSDATE) BETWEEN effective_start_date AND effective_end_date;
1993
1994 current_npw_flag per_all_people_f.current_npw_flag%TYPE DEFAULT '';
1995 found_overall_performance BOOLEAN := FALSE;
1996 error_message VARCHAR2 (500) DEFAULT NULL;
1997 -- Declare program variables as shown above
1998 BEGIN
1999 -- if there is no overall_performance entered then
2000 -- there is no need of creating an event or performance review
2001 -- as event is tied up to performance review for this release.
2002 -- this behavior might change in future. As per functional
2003 -- discussion with caroline.
2004 hr_multi_message.enable_message_list;
2005
2006 OPEN get_appr_overall_perf (appr_id);
2007
2008 FETCH get_appr_overall_perf
2009 INTO appraisal_rec;
2010
2011 IF get_appr_overall_perf%FOUND
2012 THEN
2013 OPEN check_contingent_worker (appraisal_rec.appraisee_person_id);
2014
2015 FETCH check_contingent_worker
2016 INTO current_npw_flag;
2017
2018 -- Incase of CWK dont create an event
2019 IF ((current_npw_flag IS NULL) OR (NOT current_npw_flag = 'Y'))
2020 THEN
2021 per_events_api.create_event (p_date_start => TRUNC (SYSDATE),
2022 p_type => 'APPRAISAL',
2023 p_event_id => l_event_id,
2024 p_object_version_number => l_ovn
2025 );
2026 -- retrieve
2027 hr_perf_review_api.create_perf_review
2028 (p_performance_review_id => l_perf_rev_id,
2029 p_person_id => appraisal_rec.appraisee_person_id,
2030 p_event_id => l_event_id,
2031 p_review_date => TRUNC (SYSDATE),
2032 p_performance_rating => appraisal_rec.step_value,
2033 p_object_version_number => l_ovn,
2034 p_next_review_date_warning => l_next_rev_date_warn
2035 );
2036 hr_appraisals_api.update_appraisal
2037 (p_effective_date => TRUNC (SYSDATE),
2038 p_appraisal_id => appr_id,
2039 p_appraiser_person_id => appraisal_rec.appraiser_person_id,
2040 p_object_version_number => appraisal_rec.object_version_number,
2041 p_event_id => l_event_id
2042 );
2043 found_overall_performance := TRUE;
2044 generate_event (appraisal_rec.overall_perf_rating,
2045 TRUNC (SYSDATE),
2046 hr_util_misc_web.return_msg_text (p_message_name => 'HR_SUCCESS',
2047 p_application_id => 'PER'
2048 ),
2049 NULL,
2050 p_log,
2051 p_new_appraisal
2052 );
2053 END IF;
2054
2055 CLOSE check_contingent_worker;
2056 END IF;
2057
2058 CLOSE get_appr_overall_perf;
2059
2060 IF (NOT found_overall_performance)
2061 THEN
2062 p_log := '';
2063 END IF;
2064
2065 IF NOT upd_create_event_status = 'W'
2066 THEN
2067 upd_create_event_status := 'S';
2068 END IF;
2069 EXCEPTION
2070 WHEN hr_multi_message.error_message_exist
2071 THEN
2072 CLOSE check_contingent_worker;
2073
2074 CLOSE get_appr_overall_perf;
2075
2076 FOR i IN 1 .. fnd_msg_pub.count_msg
2077 LOOP
2078 error_message :=
2079 error_message || fnd_msg_pub.get (p_msg_index => i,
2080 p_encoded => 'F');
2081 END LOOP;
2082
2083 generate_event (appraisal_rec.overall_perf_rating,
2084 TRUNC (SYSDATE),
2085 hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
2086 p_application_id => 'PER'
2087 ),
2088 error_message,
2089 p_log,
2090 p_new_appraisal
2091 );
2092 --bug#3767915
2093 fnd_msg_pub.delete_msg;
2094 --set_appr_status_log(item_type, item_key, 'W',p_log);
2095 upd_create_event_status := 'E';
2096 WHEN OTHERS
2097 THEN
2098 error_message := error_message || SQLERRM;
2099 upd_create_event_status := 'W';
2100 generate_event (appraisal_rec.overall_perf_rating,
2101 TRUNC (SYSDATE),
2102 hr_util_misc_web.return_msg_text (p_message_name => 'HR_FAILURE',
2103 p_application_id => 'PER'
2104 ),
2105 error_message,
2106 p_log,
2107 p_new_appraisal
2108 );
2109 END; -- Procedure
2110
2111 PROCEDURE check_item_attribute (p_item_type VARCHAR2, p_item_key VARCHAR2, p_attr_name VARCHAR2)
2112 IS
2113 BEGIN
2114 IF NOT hr_workflow_service.item_attribute_exists (p_item_type => p_item_type,
2115 p_item_key => p_item_key,
2116 p_name => p_attr_name
2117 )
2118 THEN
2119 -- the item attribute does not exist so create it
2120 wf_engine.additemattr (itemtype => p_item_type,
2121 itemkey => p_item_key,
2122 aname => p_attr_name
2123 );
2124 END IF;
2125 END;
2126
2127 PROCEDURE create_new_objectives (p_appr_objs IN appr_obj_table)
2128 IS
2129 i INTEGER DEFAULT 0;
2130 l_objective_id NUMBER;
2131 --
2132 -- Variables for IN/OUT parameters
2133 l_weighting_over_100_warning BOOLEAN;
2134 l_weighting_appraisal_warning BOOLEAN;
2135 l_object_version_number NUMBER;
2136 BEGIN
2137 FOR i IN 1 .. p_appr_objs.COUNT
2138 LOOP
2139 --
2140 -- Call API
2141 --
2142 hr_objectives_api.create_objective
2143 (p_validate => FALSE,
2144 p_effective_date => TRUNC (SYSDATE),
2145 p_business_group_id => p_appr_objs (i).business_group_id,
2146 p_name => p_appr_objs (i).NAME,
2147 p_start_date => p_appr_objs (i).start_date,
2148 p_target_date => p_appr_objs (i).target_date,
2149 p_owning_person_id => p_appr_objs (i).owning_person_id,
2150 p_achievement_date => p_appr_objs (i).achievement_date,
2151 p_detail => p_appr_objs (i).detail,
2152 p_comments => p_appr_objs (i).comments,
2153 p_success_criteria => p_appr_objs (i).success_criteria,
2154 p_attribute_category => p_appr_objs (i).attribute_category,
2155 p_attribute1 => p_appr_objs (i).attribute1,
2156 p_attribute2 => p_appr_objs (i).attribute2,
2157 p_attribute3 => p_appr_objs (i).attribute3,
2158 p_attribute4 => p_appr_objs (i).attribute4,
2159 p_attribute5 => p_appr_objs (i).attribute5,
2160 p_attribute6 => p_appr_objs (i).attribute6,
2161 p_attribute7 => p_appr_objs (i).attribute7,
2162 p_attribute8 => p_appr_objs (i).attribute8,
2163 p_attribute9 => p_appr_objs (i).attribute9,
2164 p_attribute10 => p_appr_objs (i).attribute10,
2165 p_attribute11 => p_appr_objs (i).attribute11,
2166 p_attribute12 => p_appr_objs (i).attribute12,
2167 p_attribute13 => p_appr_objs (i).attribute13,
2168 p_attribute14 => p_appr_objs (i).attribute14,
2169 p_attribute15 => p_appr_objs (i).attribute15,
2170 p_attribute16 => p_appr_objs (i).attribute16,
2171 p_attribute17 => p_appr_objs (i).attribute17,
2172 p_attribute18 => p_appr_objs (i).attribute18,
2173 p_attribute19 => p_appr_objs (i).attribute19,
2174 p_attribute20 => p_appr_objs (i).attribute20,
2175 p_attribute21 => p_appr_objs (i).attribute21,
2176 p_attribute22 => p_appr_objs (i).attribute22,
2177 p_attribute23 => p_appr_objs (i).attribute23,
2178 p_attribute24 => p_appr_objs (i).attribute24,
2179 p_attribute25 => p_appr_objs (i).attribute25,
2180 p_attribute26 => p_appr_objs (i).attribute26,
2181 p_attribute27 => p_appr_objs (i).attribute27,
2182 p_attribute28 => p_appr_objs (i).attribute28,
2183 p_attribute29 => p_appr_objs (i).attribute29,
2184 p_attribute30 => p_appr_objs (i).attribute30,
2185 p_scorecard_id => p_appr_objs (i).scorecard_id,
2186 p_copied_from_library_id => p_appr_objs (i).copied_from_library_id,
2187 p_copied_from_objective_id => p_appr_objs (i).objective_id,
2188 p_aligned_with_objective_id => p_appr_objs (i).aligned_with_objective_id,
2189 p_next_review_date => p_appr_objs (i).next_review_date,
2190 p_group_code => p_appr_objs (i).group_code,
2191 p_priority_code => p_appr_objs (i).priority_code,
2192 p_appraise_flag => p_appr_objs (i).appraise_flag,
2193 p_verified_flag => p_appr_objs (i).verified_flag,
2194 p_target_value => p_appr_objs (i).target_value,
2195 p_weighting_percent => p_appr_objs (i).weighting_percent,
2196 p_complete_percent => p_appr_objs (i).complete_percent,
2197 p_uom_code => p_appr_objs (i).uom_code,
2198 p_measurement_style_code => p_appr_objs (i).measurement_style_code,
2199 p_measure_name => p_appr_objs (i).measure_name,
2200 p_measure_type_code => p_appr_objs (i).measure_type_code,
2201 p_measure_comments => p_appr_objs (i).measure_comments,
2202 p_sharing_access_code => p_appr_objs (i).sharing_access_code,
2203 p_weighting_over_100_warning => l_weighting_over_100_warning,
2204 p_weighting_appraisal_warning => l_weighting_appraisal_warning,
2205 p_objective_id => l_objective_id,
2206 p_object_version_number => l_object_version_number
2207 );
2208
2209 --
2210 -- Convert API warning boolean parameter values to specific
2211 -- messages and add them to Multiple Message List
2212 --
2213 IF l_weighting_over_100_warning
2214 THEN
2215 fnd_message.set_name ('PER', 'HR_50198_WPM_WEIGHT_WARN');
2216 hr_multi_message.ADD (p_message_type => hr_multi_message.g_warning_msg);
2217 END IF;
2218
2219 IF l_weighting_appraisal_warning
2220 THEN
2221 fnd_message.set_name ('PER', 'HR_50223_WPM_APPRAISE_WARN');
2222 hr_multi_message.ADD (p_message_type => hr_multi_message.g_warning_msg);
2223 END IF;
2224 END LOOP;
2225 EXCEPTION
2226 WHEN OTHERS
2227 THEN
2228 RAISE;
2229 END;
2230
2231 PROCEDURE update_appr_objectives (p_appr_objs IN appr_obj_table)
2232 IS
2233 i INTEGER DEFAULT 0;
2234 l_objective_id NUMBER;
2235 --
2236 -- Variables for IN/OUT parameters
2237 l_weighting_over_100_warning BOOLEAN;
2238 l_weighting_appraisal_warning BOOLEAN;
2239 l_object_version_number NUMBER;
2240 BEGIN
2241 FOR i IN 1 .. p_appr_objs.COUNT
2242 LOOP
2243 --
2244 -- Call API
2245 --
2246 l_object_version_number := p_appr_objs (i).object_version_number;
2247 hr_objectives_api.update_objective
2248 (p_validate => FALSE,
2249 p_effective_date => TRUNC (SYSDATE) --<== ?
2250 ,
2251 p_objective_id => p_appr_objs (i).objective_id,
2252 p_object_version_number => l_object_version_number,
2253 p_scorecard_id => NULL,
2254 p_weighting_over_100_warning => l_weighting_over_100_warning,
2255 p_weighting_appraisal_warning => l_weighting_appraisal_warning
2256 );
2257
2258 --
2259 -- Convert API warning boolean parameter values to specific
2260 -- messages and add them to Multiple Message List
2261 --
2262 IF l_weighting_over_100_warning
2263 THEN
2264 fnd_message.set_name ('PER', 'HR_50198_WPM_WEIGHT_WARN');
2265 hr_multi_message.ADD (p_message_type => hr_multi_message.g_warning_msg);
2266 END IF;
2267
2268 IF l_weighting_appraisal_warning
2269 THEN
2270 fnd_message.set_name ('PER', 'HR_50223_WPM_APPRAISE_WARN');
2271 hr_multi_message.ADD (p_message_type => hr_multi_message.g_warning_msg);
2272 END IF;
2273 END LOOP;
2274 EXCEPTION
2275 WHEN OTHERS
2276 THEN
2277 RAISE;
2278 END;
2279
2280 PROCEDURE post_appraisal_completion (p_appraisal_id IN NUMBER)
2281 IS
2282 l_plan_id per_perf_mgmt_plans.plan_id%TYPE;
2283 l_curr_appr_tmplt_id per_appraisal_templates.appraisal_template_id%TYPE;
2284
2285 CURSOR get_plan_template_id
2286 IS
2287 SELECT plan_id,
2288 appraisal_template_id
2289 FROM per_appraisals
2290 WHERE appraisal_id = p_appraisal_id;
2291
2292 l_appr_objs appr_obj_table;
2293 BEGIN
2294 -- first check if appraisal is part of a plan
2295 OPEN get_plan_template_id;
2296
2297 FETCH get_plan_template_id
2298 INTO l_plan_id,
2299 l_curr_appr_tmplt_id;
2300
2301 CLOSE get_plan_template_id; -- close cursor variable
2302
2303 IF (l_plan_id IS NOT NULL)
2304 THEN
2305 -- bulk fetch all existing objectives and scorecard_id with appraisalId as input into table
2306 OPEN get_appr_objectives (p_appraisal_id);
2307
2308 FETCH get_appr_objectives
2309 BULK COLLECT INTO l_appr_objs;
2310
2311 CLOSE get_appr_objectives; -- close cursor variable
2312
2313 -- Loop thru the table and create objective with copied objectiveid and scorecardid
2314 create_new_objectives (l_appr_objs);
2315 -- Loop thru the rows and update all existing objectives with null scorecardid
2316 update_appr_objectives (l_appr_objs);
2317 END IF;
2318 EXCEPTION
2319 WHEN OTHERS
2320 THEN
2321 RAISE;
2322 END;
2323
2324 PROCEDURE update_succ_plan_eit (p_appraisal_id IN NUMBER)
2325 IS
2326 l_proc VARCHAR2 (100);
2327
2328 CURSOR csr_succ_details (p_appraisal_id NUMBER)
2329 IS
2330 SELECT appraisal_id,
2331 appraisee_person_id,
2332 appraisal_period_start_date,
2333 appraisal_period_end_date,
2334 potential_readiness_level,
2335 retention_potential
2336 FROM per_appraisals
2337 WHERE appraisal_id = p_appraisal_id;
2338
2339 l_person_extra_info_id NUMBER;
2340 l_ovn NUMBER;
2341 BEGIN
2342 l_proc := 'HR_COMPLETE_APPRAISAL_SS.UPDATE_SUCC_PLAN_EIT';
2343 hr_utility.set_location ('Entering:' || l_proc, 10);
2344
2345 FOR i IN csr_succ_details (p_appraisal_id)
2346 LOOP
2347 --insert only if potential data entered in appraisal
2348 IF i.potential_readiness_level IS NOT NULL OR i.retention_potential IS NOT NULL
2349 THEN
2350 BEGIN
2351 hr_person_extra_info_api.create_person_extra_info
2352 (p_person_id => i.appraisee_person_id,
2353 p_information_type => 'PER_SUCCESSION_MGMT_INFO',
2354 p_pei_information_category => 'PER_SUCCESSION_MGMT_INFO',
2355 p_pei_information1 => i.potential_readiness_level,
2356 p_pei_information4 => i.retention_potential,
2357 p_pei_information5 => fnd_date.date_to_canonical
2358 (i.appraisal_period_start_date),
2359 p_pei_information6 => fnd_date.date_to_canonical
2360 (i.appraisal_period_end_date),
2361 p_pei_information7 => i.appraisal_id,
2362 p_pei_information8 => fnd_date.date_to_canonical (SYSDATE),
2363 p_person_extra_info_id => l_person_extra_info_id,
2364 p_object_version_number => l_ovn
2365 );
2366 EXCEPTION
2367 WHEN OTHERS
2368 THEN
2369 hr_utility.set_location ('Error in :' || l_proc, 90);
2370 hr_utility.TRACE (SUBSTR (SQLERRM, 1, 240));
2371 RAISE;
2372 END;
2373 END IF;
2374 END LOOP;
2375
2376 hr_utility.set_location ('Leaving:' || l_proc, 100);
2377 END update_succ_plan_eit;
2378
2379 --
2380 PROCEDURE validate_mandatory_questions (
2381 appraisal_id IN VARCHAR2,
2382 p_result_out IN OUT NOCOPY VARCHAR2
2383 )
2384 IS
2385 appr_id NUMBER;
2386 l_main_appr_mandate_quest VARCHAR2 (1);
2387 l_particip_mandate_quest VARCHAR2 (150);
2388 l_appraisee_mandate_quest VARCHAR2 (1);
2389 l_proc VARCHAR2 (100);
2390 l_result VARCHAR2 (450);
2391
2392 CURSOR c_main_appr_mandate_quest (c_appraisal_id IN NUMBER)
2393 IS
2394 SELECT 'Y'
2395 FROM dual
2396 WHERE EXISTS
2397 (
2398 SELECT 'x'
2399 FROM per_appraisals pa
2400 ,hr_questionnaires hq
2401 ,hr_quest_fields hqf
2402 ,per_participants pp
2403 WHERE pa.appraisal_id = c_appraisal_id
2404 AND pp.participation_in_table = 'PER_APPRAISALS'
2405 AND pp.participation_in_id = pa.appraisal_id
2406 AND pp.participation_type = 'MAINAP'
2407 AND pp.questionnaire_template_id = hq.questionnaire_template_id
2408 AND nvl (hq.quest_type_code
2409 ,'OLDUI') = 'NEWUI'
2410 AND hq.questionnaire_template_id = hqf.questionnaire_template_id
2411 AND nvl (hqf.sql_required_flag
2412 ,'N') = 'Y'
2413 AND NOT EXISTS
2414 (
2415 SELECT hqav.field_id
2416 FROM hr_quest_answers hqa
2417 ,hr_quest_answer_values hqav
2418 WHERE hqa.questionnaire_template_id = hq.questionnaire_template_id
2419 AND hqa.type = 'PARTICIPANT'
2420 AND hqa.type_object_id = pp.participant_id
2421 AND hqa.questionnaire_answer_id = hqav.questionnaire_answer_id
2422 AND hqav.field_id = hqf.field_id
2423 AND hqav.value IS NOT NULL
2424 )
2425 );
2426
2427 CURSOR c_particip_mandate_quest (c_appraisal_id IN NUMBER)
2428 IS
2429 SELECT hr_general.decode_person_name (person_id) person
2430 FROM (
2431 SELECT DISTINCT(pp.person_id) person_id
2432 FROM per_appraisals pa
2433 ,hr_questionnaires hq
2434 ,hr_quest_fields hqf
2435 ,per_participants pp
2436 WHERE pa.appraisal_id = c_appraisal_id
2437 AND pp.participation_in_table = 'PER_APPRAISALS'
2438 AND pp.participation_in_id = pa.appraisal_id
2439 AND pp.participation_type <> 'MAINAP'
2440 AND pp.questionnaire_template_id = hq.questionnaire_template_id
2441 AND nvl (hq.quest_type_code
2442 ,'OLDUI') = 'NEWUI'
2443 AND hq.questionnaire_template_id = hqf.questionnaire_template_id
2444 AND nvl (hqf.sql_required_flag
2445 ,'N') = 'Y'
2446 AND NOT EXISTS
2447 (
2448 SELECT hqav.field_id
2449 FROM hr_quest_answers hqa
2450 ,hr_quest_answer_values hqav
2451 WHERE hqa.questionnaire_template_id = hq.questionnaire_template_id
2452 AND hqa.type = 'PARTICIPANT'
2453 AND hqa.type_object_id = pp.participant_id
2454 AND hqa.questionnaire_answer_id = hqav.questionnaire_answer_id
2455 AND hqav.field_id = hqf.field_id
2456 AND hqav.value IS NOT NULL
2457 )
2458 );
2459
2460 CURSOR c_appraisee_mandate_quest (c_appraisal_id IN NUMBER)
2461 IS
2462 SELECT 'Y'
2463 FROM dual
2464 WHERE EXISTS
2465 (
2466 SELECT 'x'
2467 FROM per_appraisals pa
2468 ,per_appraisal_templates pat
2469 ,hr_questionnaires hq
2470 ,hr_quest_fields hqf
2471 WHERE pa.appraisal_id = c_appraisal_id
2472 AND pa.appraisal_template_id = pat.appraisal_template_id
2473 AND pat.questionnaire_template_id = hq.questionnaire_template_id
2474 AND nvl (hq.quest_type_code
2475 ,'OLDUI') = 'NEWUI'
2476 AND hq.questionnaire_template_id = hqf.questionnaire_template_id
2477 AND nvl (hqf.sql_required_flag
2478 ,'N') = 'Y'
2479 AND NOT EXISTS
2480 (
2481 SELECT hqav.field_id
2482 FROM hr_quest_answers hqa
2483 ,hr_quest_answer_values hqav
2484 WHERE hqa.questionnaire_template_id = hq.questionnaire_template_id
2485 AND hqa.type = 'APPRAISAL'
2486 AND hqa.type_object_id = pa.appraisal_id
2487 AND hqa.questionnaire_answer_id = hqav.questionnaire_answer_id
2488 AND hqav.field_id = hqf.field_id
2489 AND hqav.value IS NOT NULL
2490 )
2491 );
2492 BEGIN
2493
2494 l_proc := 'HR_COMPLETE_APPRAISAL_SS.VALIDATE_MANDTORY_QUESTIONS';
2495 hr_utility.set_location (' Entering:' || l_proc, 5);
2496
2497 l_result := 'E';
2498
2499 appr_id := to_number(appraisal_id);
2500 IF appr_id IS NULL
2501 THEN
2502 hr_utility.set_location('Not a valid Appraisal ID.', 300);
2503 RAISE lv_no_appraisal;
2504 END IF;
2505
2506 OPEN c_main_appr_mandate_quest (appr_id);
2507 FETCH c_main_appr_mandate_quest
2508 INTO l_main_appr_mandate_quest;
2509
2510 IF(c_main_appr_mandate_quest%FOUND)
2511 THEN
2512 hr_utility.set_location('Main Appraiser(s) have not answered mandatory question(s).', 7777);
2513 IF(l_result = 'E')
2514 THEN
2515 l_result := 'M';
2516 ELSE
2517 l_result := l_result || '~M';
2518 END IF;
2519 hr_utility.set_location('l_result : ' || l_result, 7777);
2520 END IF;
2521
2522 OPEN c_particip_mandate_quest (appr_id);
2523 LOOP
2524 FETCH c_particip_mandate_quest
2525 INTO l_particip_mandate_quest;
2526 EXIT WHEN c_particip_mandate_quest%NOTFOUND;
2527
2528 IF(c_particip_mandate_quest%FOUND)
2529 THEN
2530 hr_utility.set_location('Participant(s) have not answered mandatory question(s).', 7777);
2531 hr_utility.set_location('l_particip_mandate_quest : ' || l_particip_mandate_quest, 7777);
2532 IF(l_result = 'E')
2533 THEN
2534 l_result := 'P~' || l_particip_mandate_quest;
2535 ELSE
2536 l_result := l_result || '~P~' || l_particip_mandate_quest;
2537 END IF;
2538 hr_utility.set_location('l_result : ' || l_result, 7777);
2539 END IF;
2540 END LOOP;
2541
2542 OPEN c_appraisee_mandate_quest (appr_id);
2543 FETCH c_appraisee_mandate_quest
2544 INTO l_appraisee_mandate_quest;
2545
2546 IF(c_appraisee_mandate_quest%FOUND)
2547 THEN
2548 hr_utility.set_location('Appraisee has not answered mandatory question(s).', 7777);
2549 IF(l_result = 'E')
2550 THEN
2551 l_result := 'A';
2552 ELSE
2553 l_result := l_result || '~A';
2554 END IF;
2555 hr_utility.set_location('l_result : ' || l_result, 7777);
2556 END IF;
2557
2558 IF(l_result = 'E')
2559 THEN
2560 hr_utility.set_location('Main Appraiser(s) Participant(s) and Appraisee have answered all mandatory question(s).', 7777);
2561 l_result := 'S';
2562 END IF;
2563
2564 p_result_out := l_result;
2565 hr_utility.set_location('p_result_out : ' || p_result_out, 7777);
2566
2567 CLOSE c_main_appr_mandate_quest;
2568 CLOSE c_particip_mandate_quest;
2569 CLOSE c_appraisee_mandate_quest;
2570 hr_utility.set_location(' Leaving: ' || l_proc, 20);
2571
2572 EXCEPTION
2573 WHEN lv_no_appraisal
2574 THEN
2575 hr_utility.set_location(' Invalid Appraisal Id Passed. ', 300);
2576 p_result_out := 'E';
2577 WHEN OTHERS
2578 THEN
2579 hr_utility.set_location(' Erroring out on procedure : ' || l_proc, 300);
2580 p_result_out := 'E';
2581 END validate_mandatory_questions;
2582
2583 --
2584 PROCEDURE complete_appr (
2585 item_type IN VARCHAR2,
2586 item_key IN VARCHAR2,
2587 p_result_out IN OUT NOCOPY VARCHAR2
2588 )
2589 IS
2590 appraisal_id per_appraisals.appraisal_id%TYPE;
2591 l_ins_ovn NUMBER;
2592 l_ins_comp_id NUMBER;
2593 next_comp_ele_id per_competence_elements.competence_element_id%TYPE;
2594 l_person_id NUMBER;
2595 l_log VARCHAR2 (4000);
2596 chg_appr_status VARCHAR2 (2);
2597 apply_pers_profile_status VARCHAR2 (2);
2598 upd_train_comps_status VARCHAR2 (2);
2599 upd_create_event_status VARCHAR2 (2);
2600 lv_chg_appr_status_log wf_item_attributes.text_default%TYPE;
2601 lv_apply_pers_comps_log wf_item_attributes.text_default%TYPE;
2602 lv_upd_train_comps_status_log wf_item_attributes.text_default%TYPE;
2603 lv_upd_create_event_status_log wf_item_attributes.text_default%TYPE;
2604 p_new_appraisal BOOLEAN;
2605 l_talent_mang_src_typ per_appraisal_templates.comp_profile_source_type%TYPE;
2606 l_available_flag per_appraisal_templates.available_flag%TYPE;
2607 update_personal_profile VARCHAR2 (100) DEFAULT NULL;
2608 l_proc VARCHAR2 (100);
2609
2610 CURSOR c_appr_template_details (c_appraisal_id IN NUMBER)
2611 IS
2612 SELECT available_flag,
2613 update_personal_comp_profile,
2614 comp_profile_source_type
2615 FROM per_appraisal_templates pat, per_appraisals pa
2616 WHERE pa.appraisal_template_id = pat.appraisal_template_id
2617 AND pa.appraisal_id = c_appraisal_id;
2618
2619 --
2620 -- SSHR Attachment feature changes : 8814550
2621 --
2622 l_sel_person_id NUMBER;
2623 l_attach_status VARCHAR2 (80);
2624 l_appraisal_id NUMBER;
2625 l_source_pk1_value NUMBER;
2626 BEGIN
2627 l_proc := 'HR_COMPLETE_APPRAISAL_SS.COMPLETE_APPR';
2628 --10060770
2629 hr_utility.set_location (' Entering:' || l_proc, 5);
2630 appraisal_id :=
2631 wf_engine.getitemattrnumber (itemtype => item_type,
2632 itemkey => item_key,
2633 aname => 'APPRAISAL_ID',
2634 ignore_notfound => TRUE
2635 );
2636 hr_utility.set_location (' Entering:' || l_proc, 10);
2637
2638 IF appraisal_id IS NULL
2639 THEN
2640 hr_utility.set_location (' Entering:' || l_proc, 20);
2641 l_log := l_log || 'No Appraisal Id for this WorkFlow Transaction';
2642 RAISE lv_no_appraisal;
2643 END IF;
2644 SAVEPOINT complete_appraisal_status;
2645
2646
2647 -- SSHR Attachment feature changes : 8814550
2648 l_appraisal_id := appraisal_id;
2649
2650 SELECT pa.appraisee_person_id
2651 INTO l_sel_person_id
2652 FROM per_appraisals pa
2653 WHERE pa.appraisal_id = l_appraisal_id;
2654 --10060770
2655 SELECT transaction_id
2656 INTO l_source_pk1_value
2657 FROM hr_api_transactions
2658 WHERE transaction_ref_table = 'PER_APPRAISALS' AND transaction_ref_id = l_appraisal_id
2659 AND item_type = COMPLETE_APPR.item_type
2660 AND item_key = complete_appr.item_key;
2661
2662 OPEN c_appr_template_details (appraisal_id);
2663
2664 FETCH c_appr_template_details
2665 INTO l_available_flag,
2666 update_personal_profile,
2667 l_talent_mang_src_typ;
2668
2669 IF (c_appr_template_details%NOTFOUND)
2670 THEN
2671 hr_utility.set_message (800, 'HR_52256_APR_TEMP_MANDATORY');
2672 hr_utility.raise_error;
2673 END IF;
2674
2675 hr_utility.set_location (' Entering:' || l_proc, 30);
2676 check_item_attribute (item_type, item_key, gv_appr_compl_status);
2677 hr_utility.set_location (' Entering:' || l_proc, 35);
2678 check_item_attribute (item_type, item_key, gv_upd_appr_status_log);
2679 hr_utility.set_location (' Entering:' || l_proc, 40);
2680 check_item_attribute (item_type, item_key, gv_apply_asses_comps_log);
2681 hr_utility.set_location (' Entering:' || l_proc, 45);
2682 check_item_attribute (item_type, item_key, gv_create_event_log);
2683 hr_utility.set_location (' Entering:' || l_proc, 50);
2684 check_item_attribute (item_type, item_key, gv_upd_trn_act_status_log);
2685 hr_utility.set_location (' Entering:' || l_proc, 55);
2686
2687 -- change the appraisal system status to completed
2688 -- if this errors out then exit from the procudure
2689 -- with out processing furhter
2690 hr_utility.set_location (' Entering:' || l_proc, 60);
2691 change_appr_status (appraisal_id, item_type, item_key, lv_chg_appr_status_log,
2692 chg_appr_status);
2693 hr_utility.set_location (' Entering:' || l_proc, 70);
2694 set_appr_status_log (item_type,
2695 item_key,
2696 chg_appr_status,
2697 gv_upd_appr_status_log,
2698 lv_chg_appr_status_log
2699 );
2700 hr_utility.set_location (' Entering:' || l_proc, 75);
2701 -- SSHR Attachment feature changes : 8814550
2702 hr_utility.set_location ( 'merge_attachments Start : l_sel_person_id = '
2703 || l_sel_person_id
2704 || ' '
2705 || l_proc,
2706 76
2707 );
2708 hr_util_misc_ss.merge_attachments (p_dest_entity_name => 'PER_PEOPLE_F',
2709 p_source_pk1_value => l_source_pk1_value,
2710 p_dest_pk1_value => l_sel_person_id,
2711 p_return_status => l_attach_status
2712 );
2713 hr_utility.set_location ( 'merge_attachments End: l_attach_status = '
2714 || l_attach_status
2715 || ' '
2716 || l_proc,
2717 76
2718 );
2719
2720 -- create and event and add a performance record
2721 IF l_available_flag IS NULL
2722 THEN
2723 hr_utility.set_location (' Entering:' || l_proc, 77);
2724 update_personal_profile := fnd_profile.VALUE ('HR_APPLY_COMPETENCIES_TO_PERSON');
2725 l_talent_mang_src_typ := fnd_profile.VALUE ('HR_TALENT_MGMT_SRC_TYPE');
2726 END IF;
2727
2728 hr_utility.set_location (' Entering:' || l_proc, 80);
2729 p_new_appraisal := is_new_appraisal (item_type, item_key);
2730
2731 IF (update_personal_profile IS NOT NULL AND update_personal_profile = 'Y')
2732 THEN
2733 hr_utility.set_location (' Entering:' || l_proc, 85);
2734 apply_to_personal_profile (appraisal_id,
2735 lv_apply_pers_comps_log,
2736 apply_pers_profile_status,
2737 p_new_appraisal,
2738 l_talent_mang_src_typ
2739 );
2740 hr_utility.set_location (' Entering:' || l_proc, 90);
2741 set_appr_status_log (item_type,
2742 item_key,
2743 apply_pers_profile_status,
2744 gv_apply_asses_comps_log,
2745 lv_apply_pers_comps_log
2746 );
2747 hr_utility.set_location (' Entering:' || l_proc, 95);
2748 END IF;
2749
2750 ------------ Update Succession Plan Details
2751 IF NVL (fnd_profile.VALUE ('HR_SUCCESSION_MGMT_LICENSED'), 'N') = 'Y'
2752 THEN
2753 hr_utility.set_location ('before update_succ_plan_eit:' || l_proc, 96);
2754 update_succ_plan_eit (appraisal_id);
2755 hr_utility.set_location ('before update_succ_plan_eit:' || l_proc, 96);
2756 END IF;
2757
2758 ------------
2759
2760 -- update the OTA status
2761 --update_train_component_status(appraisal_id, item_type, item_key, l_log, p_result_out);
2762 hr_utility.set_location (' Entering:' || l_proc, 100);
2763 update_train_component_status (appraisal_id,
2764 lv_upd_train_comps_status_log,
2765 upd_train_comps_status,
2766 p_new_appraisal
2767 );
2768 hr_utility.set_location (' Entering:' || l_proc, 105);
2769 set_appr_status_log (item_type,
2770 item_key,
2771 upd_train_comps_status,
2772 gv_upd_trn_act_status_log,
2773 lv_upd_train_comps_status_log
2774 );
2775 hr_utility.set_location (' Entering:' || l_proc, 110);
2776 create_event (appraisal_id,
2777 lv_upd_create_event_status_log,
2778 upd_create_event_status,
2779 p_new_appraisal
2780 );
2781 hr_utility.set_location (' Entering:' || l_proc, 115);
2782 set_appr_status_log (item_type,
2783 item_key,
2784 upd_create_event_status,
2785 gv_create_event_log,
2786 lv_upd_create_event_status_log
2787 );
2788 hr_utility.set_location (' Entering:' || l_proc, 120);
2789 -- add the following line to disable multi messaging for fixing bug#5947176
2790 hr_multi_message.disable_message_list;
2791 -- END changes for bug#5947176
2792 post_appraisal_completion (appraisal_id);
2793
2794 IF ( chg_appr_status = 'W'
2795 OR apply_pers_profile_status = 'W'
2796 OR upd_train_comps_status = 'W'
2797 OR upd_create_event_status = 'W'
2798 )
2799 THEN
2800 hr_utility.set_location (' Entering:' || l_proc, 125);
2801 p_result_out := 'W';
2802 ELSE
2803 hr_utility.set_location (' Entering:' || l_proc, 130);
2804 p_result_out := 'S';
2805 END IF;
2806 EXCEPTION
2807 -- when there is an exception it is due to change appraisal status
2808 -- for all other tasks it there wont be any exception raise,
2809 -- instead the errors are written to WorkFlow so that appraisal
2810 -- can read and display corresponding message / warning.
2811 WHEN lv_no_appraisal
2812 THEN
2813 p_result_out := 'E';
2814 hr_utility.set_location (' Entering:' || l_proc, 200);
2815 set_appr_status_log (item_type,
2816 item_key,
2817 'E',
2818 gv_upd_appr_status_log,
2819 lv_chg_appr_status_log
2820 );
2821 WHEN OTHERS
2822 THEN
2823 hr_utility.set_location (' Entering:' || l_proc, 300);
2824 ROLLBACK TO complete_appraisal_status;
2825 p_result_out := 'E';
2826 set_appr_status_log (item_type,
2827 item_key,
2828 'E',
2829 gv_upd_appr_status_log,
2830 lv_chg_appr_status_log
2831 );
2832 END; -- Procedure
2833
2834 PROCEDURE send_notification (
2835 p_item_type IN VARCHAR2,
2836 p_item_key IN VARCHAR2,
2837 p_result_out IN OUT NOCOPY VARCHAR2
2838 )
2839 IS
2840 l_appraisal_id per_appraisals.appraisal_id%TYPE;
2841 update_personal_profile VARCHAR2 (100) DEFAULT NULL;
2842
2843 CURSOR get_appraisee_access (appr_id per_appraisals.appraisal_id%TYPE)
2844 IS
2845 SELECT appraisal_id,
2846 appraisee_access,
2847 system_type
2848 FROM per_appraisals
2849 WHERE appraisal_id = appr_id;
2850
2851 TYPE appraisal_access_info IS RECORD (
2852 appraisal_id per_appraisals.appraisal_id%TYPE,
2853 appraisee_access per_appraisals.appraisee_access%TYPE,
2854 system_type per_appraisals.system_type%TYPE
2855 );
2856
2857 appr_access_info appraisal_access_info;
2858 BEGIN
2859 p_result_out := 'N';
2860 l_appraisal_id :=
2861 wf_engine.getitemattrnumber (itemtype => p_item_type,
2862 itemkey => p_item_key,
2863 aname => 'APPRAISAL_ID',
2864 ignore_notfound => FALSE
2865 );
2866
2867 OPEN get_appraisee_access (l_appraisal_id);
2868
2869 FETCH get_appraisee_access
2870 INTO appr_access_info;
2871
2872 IF get_appraisee_access%FOUND
2873 THEN
2874 IF appr_access_info.system_type IS NOT NULL
2875 THEN
2876 --Bug 8659708
2877 IF NOT (appr_access_info.system_type = 'SELF')
2878 THEN
2879 --if( appr_access_info.system_type = 'MGR360' or appr_access_info.system_type = 'MGRSTD' ) then
2880 IF NOT ( appr_access_info.appraisee_access = 'NONE'
2881 OR appr_access_info.appraisee_access IS NULL
2882 )
2883 THEN
2884 p_result_out := 'Y';
2885 END IF;
2886 ELSE
2887 p_result_out := 'Y';
2888 END IF;
2889 END IF;
2890 END IF;
2891
2892 CLOSE get_appraisee_access;
2893 EXCEPTION
2894 WHEN OTHERS
2895 THEN
2896 RAISE;
2897 END;
2898
2899 PROCEDURE complete_appr_hr (
2900 item_type IN VARCHAR2,
2901 item_key IN VARCHAR2,
2902 p_result_out IN OUT NOCOPY VARCHAR2
2903 )
2904 IS
2905 lv_provide_overall_feedback per_appraisals.provide_overall_feedback%TYPE;
2906 l_appraisal_id per_appraisals.appraisal_id%TYPE;
2907 BEGIN
2908 l_appraisal_id :=
2909 wf_engine.getitemattrnumber (itemtype => item_type,
2910 itemkey => item_key,
2911 aname => 'APPRAISAL_ID',
2912 ignore_notfound => TRUE
2913 );
2914
2915 SELECT provide_overall_feedback
2916 INTO lv_provide_overall_feedback
2917 FROM per_appraisals
2918 WHERE appraisal_id = l_appraisal_id;
2919
2920 IF (lv_provide_overall_feedback = 'Y')
2921 THEN
2922 UPDATE per_appraisals
2923 SET provide_overall_feedback = 'N'
2924 WHERE appraisal_id = l_appraisal_id;
2925 END IF;
2926
2927 hr_complete_appraisal_ss.complete_appr (item_type => item_type,
2928 item_key => item_key,
2929 p_result_out => p_result_out
2930 );
2931
2932 IF (lv_provide_overall_feedback = 'Y')
2933 THEN
2934 UPDATE per_appraisals
2935 SET provide_overall_feedback = 'Y'
2936 WHERE appraisal_id = l_appraisal_id;
2937 END IF;
2938
2939 COMMIT;
2940 EXCEPTION
2941 WHEN OTHERS
2942 THEN
2943 ROLLBACK;
2944 p_result_out := 'E';
2945 END complete_appr_hr;
2946 END hr_complete_appraisal_ss;