[Home] [Help]
PACKAGE BODY: APPS.HR_COMPLETE_APPRAISAL_SS
Source
1 PACKAGE BODY HR_COMPLETE_APPRAISAL_SS AS
2 /* $Header: hrcpappr.pkb 120.5 2007/08/20 05:12:23 rvagvala 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
14 lv_no_appraisal exception;
15 TYPE cur_typ IS REF CURSOR;
16 -- Global cursor for getting objectives for appraisal_id
17 CURSOR get_appr_objectives(p_appraisal_id in number) IS
18 SELECT objective_id,
19 name,
20 target_date,
21 start_date,
22 business_group_id,
23 object_version_number,
24 owning_person_id,
25 achievement_date,
26 detail,
27 comments,
28 success_criteria,
29 appraisal_id,
30 attribute_category,
31 attribute1,
32 attribute2,
33 attribute3,
34 attribute4,
35 attribute5,
36 attribute6,
37 attribute7,
38 attribute8,
39 attribute9,
40 attribute10,
41 attribute11,
42 attribute12,
43 attribute13,
44 attribute14,
45 attribute15,
46 attribute16,
47 attribute17,
48 attribute18,
49 attribute19,
50 attribute20,
51 attribute21,
52 attribute22,
53 attribute23,
54 attribute24,
55 attribute25,
56 attribute26,
57 attribute27,
58 attribute28,
59 attribute29,
60 attribute30,
61 scorecard_id,
62 copied_from_library_id,
63 copied_from_objective_id,
64 aligned_with_objective_id,
65 next_review_date,
66 group_code,
67 priority_code,
68 appraise_flag,
69 verified_flag,
70 weighting_percent,
71 complete_percent,
72 target_value,
73 actual_value,
74 uom_code,
75 measurement_style_code,
76 measure_name,
77 measure_type_code,
78 measure_comments,
79 sharing_access_code
80 FROM per_objectives
81 WHERE appraisal_id = p_appraisal_id;
82 -- table type for appraisal objectives
83 TYPE appr_obj_table IS TABLE OF get_appr_objectives%ROWTYPE INDEX BY BINARY_INTEGER ;
84
85 PROCEDURE set_appr_status_log (item_type IN varchar2,
86 item_key IN varchar2,
87 status IN varchar2,
88 attr_name IN varchar2,
89 l_log IN varchar2 )
90 IS
91 lv_status varchar2(4000);
92
93 BEGIN
94 if( (wf_engine.GetItemAttrText(item_type, item_key, gv_appr_compl_status, true) is null) or
95 (wf_engine.GetItemAttrText(item_type, item_key, gv_appr_compl_status, true) = 'W')) then
96 wf_engine.setitemattrtext(item_type, item_key, gv_appr_compl_status, status);
97 end if;
98 wf_engine.setitemattrtext(item_type, item_key, attr_name, l_log);
99 EXCEPTION
100 WHEN others then
101 raise;
102 END;
103
104
105 FUNCTION is_new_appraisal (item_type IN varchar2,
106 item_key IN varchar2)
107 RETURN Boolean IS
108 BEGIN
109 if hr_workflow_service.item_attribute_exists
110 (p_item_type => item_type
111 ,p_item_key => item_key
112 ,p_name => 'HR_COMPETENCE_ENHANCEMENT_SS') then
113 RETURN true;
114 end if;
115
116 RETURN false;
117 EXCEPTION
118 WHEN others THEN
119 raise;
120 END;
121
122
123 PROCEDURE generate_event
124 (
125 p_overall_perf_rating varchar2,
126 p_review_date date,
127 p_result varchar2,
128 p_reason varchar2,
129 p_log in out nocopy varchar2,
130 p_new_appraisal in boolean DEFAULT true
131 )
132 IS
133 BEGIN
134 if p_new_appraisal then
135 p_log := p_overall_perf_rating || '^' || p_review_date || '^' || p_result || '^' || p_reason;
136 else
137 p_log := p_log ||
138 '<br> <br> <table width="100%" summary="" border="0" cellspacing="0" cellpadding="0"> <tr valign="top">' ||
139 '<td align="left" width="70%">' ||
140 '<div><div class="x60">' ||
141 '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">' ||
142 '<tr><td width="100%"> <h1 class="x18">'|| hr_util_misc_web.return_msg_text(
143 p_message_name =>'HR_APPRAISAL_EVENT',
144 p_Application_id =>'PER') || '</h1></td></tr>' ||
145 '<tr><td class="x2i"></td></tr>' ||
146 '</table></div>'||
147 '<script>t(void 0,''5'')</script>' ||
148 '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">' ||
149 '<tr>' ||
150 '<td>' ||
151 '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">' ||
152 '<tr>' ||
153 '<th scope="col" class="x1r">' || hr_util_misc_web.return_msg_text(
154 p_message_name =>'HR_APPR_PERF_LEVEL',
155 p_Application_id =>'PER') ||'</th>' ||
156 '<th scope="col" class="x1r x4j">' || hr_util_misc_web.return_msg_text(
157 p_message_name =>'HR_PERF_REVIEW_DATE',
158 p_Application_id =>'PER') ||' </th>' ||
159 '<th scope="col" class="x1r x4j">' || hr_util_misc_web.return_msg_text(
160 p_message_name =>'HR_RESULT',
161 p_Application_id =>'PER') ||'</th>' ||
162 '<th scope="col" class="x1r x4j">' || hr_util_misc_web.return_msg_text(
163 p_message_name =>'HR_REASON',
164 p_Application_id =>'PER') ||'</th>' ||
165 '</tr>' ||
166 '<tr> ' ||
167 '<td class="x1l x4x" nowrap>' || p_overall_perf_rating || '</td>' ||
168 '<td class="x1l x4x" nowrap>' || p_review_date || '</td>' ||
169 '<td class="x1l x4x">' || p_result || '</td>' ||
170 '<td class="x1l x4x">' || p_reason || '</td>' ||
171 '</tr>' ||
172 '</table>' ||
173 '</td>' ||
174 '</tr>' ||
175 '</table>' ||
176 '</div> <div></div>' ||
177 '<script>t(''10'')</script>' ||
178 '</td>' ||
179 '</tr>' ||
180 '</table>';
181 end if;
182 END;
183
184
185 PROCEDURE generate_appraisal_status
186 (appraisal_date per_appraisals.appraisal_date%TYPE,
187 appraisee_name per_all_people_f.full_name%TYPE,
188 appraisal_status hr_lookups.meaning%TYPE,
189 appraisal_type hr_lookups.meaning%TYPE,
190 result fnd_new_messages.message_text%TYPE,
191 reason varchar2,
192 p_log in out nocopy varchar2
193 )
194 IS
195 BEGIN
196
197 p_log := p_log ||
198 '<br> <br> <table width="100%" summary="" border="0" cellspacing="0" cellpadding="0"> <tr valign="top">' ||
199 '<td align="left" width="70%">' ||
200 '<div><div class="x60">' ||
201 '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">' ||
202 '<tr><td width="100%"> <h1 class="x18">'|| hr_util_misc_web.return_msg_text(
203 p_message_name =>'HR_APPRAISAL_STATUS',
204 p_Application_id =>'PER') || '</h1></td></tr>' ||
205 '<tr><td class="x2i"></td></tr>' ||
206 '</table></div>'||
207 '<script>t(void 0,''5'')</script>' ||
208 '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">' ||
209 '<tr>' ||
210 '<td>' ||
211 '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">' ||
212 '<tr>' ||
213 '<th scope="col" class="x1r">' || hr_util_misc_web.return_msg_text(
214 p_message_name =>'HR_APPRAISAL_DATE',
215 p_Application_id =>'PER') ||'</th>' ||
216 '<th scope="col" class="x1r x4j">' || hr_util_misc_web.return_msg_text(
217 p_message_name =>'HR_APPRAISEE',
218 p_Application_id =>'PER') ||' </th>' ||
219 '<th scope="col" class="x1r x4j">' || hr_util_misc_web.return_msg_text(
220 p_message_name =>'HR_APPRAISAL_TYPE',
221 p_Application_id =>'PER') ||'</th>' ||
222 '<th scope="col" class="x1r x4j">' || hr_util_misc_web.return_msg_text(
223 p_message_name =>'HR_STATUS',
224 p_Application_id =>'PER') ||'</th>' ||
225 '<th scope="col" class="x1r x4j">' || hr_util_misc_web.return_msg_text(
226 p_message_name =>'HR_RESULT',
227 p_Application_id =>'PER') ||'</th>' ||
228 '<th scope="col" class="x1r x4j">' || hr_util_misc_web.return_msg_text(
229 p_message_name =>'HR_REASON',
230 p_Application_id =>'PER') ||'</th>' ||
231 '</tr>' ||
232 '<tr> ' ||
233 '<td class="x1l x4x" nowrap>' || appraisal_date || '</td>' ||
234 '<td class="x1l x4x" nowrap>' || appraisee_name || '</td>' ||
235 '<td class="x1l x4x">' || appraisal_type || '</td>' ||
236 '<td class="x1l x4x">' || appraisal_status || '</td>' ||
237 '<td class="x1l x4x">' || result || '</td>' ||
238 '<td class="x1l x4x">' || reason || '</td>' ||
239 '</tr>' ||
240 '</table>' ||
241 '</td>' ||
242 '</tr>' ||
243 '</table>' ||
244 '</div> <div></div>' ||
245 '<script>t(''10'')</script>' ||
246 '</td>' ||
247 '</tr>' ||
248 '</table>';
249
250 END;
251
252 PROCEDURE change_appr_status
253 ( appr_id per_appraisals.appraisal_id%TYPE,
254 item_type IN varchar2,
255 item_key IN varchar2,
256 p_log in out nocopy varchar2,
257 chg_appr_status in out nocopy varchar2 )
258 IS
259
260 cursor get_appraisal_info(appr_id per_appraisals.appraisal_id%TYPE) IS
261 select appr.appraisal_id, appr.object_version_number,
262 appr.appraiser_person_id, appr.appraisee_person_id, ppf.full_name,
263 appr.appraisal_date, apprstatus.meaning appraisal_status, apprtype.meaning appraisal_type,
264 nvl(appr.provide_overall_feedback,'N') provide_overall_feedback, appr.appraisal_system_status
265 from per_appraisals appr, per_all_people_f ppf, hr_lookups apprstatus,
266 hr_lookups apprtype
267 where appr.appraisal_id = appr_id
268 and ppf.person_id = appr.appraisee_person_id
269 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
270 and apprstatus.lookup_type = 'APPRAISAL_SYSTEM_STATUS'
271 and apprstatus.lookup_code = appr.appraisal_system_status
272 and apprtype.lookup_type = 'APPRAISAL_SYS_TYPE'
273 and apprtype.lookup_code = appr.system_type;
274
275 cursor get_appraisal_status(appr_id per_appraisals.appraisal_id%TYPE) IS
276 select hrl.meaning
277 from per_appraisals appr, hr_lookups hrl
278 where appraisal_id = appr_id
279 and hrl.lookup_type = 'APPRAISAL_SYSTEM_STATUS'
280 and appr.appraisal_system_status = hrl.lookup_code;
281
282
283 TYPE appraisal_rec is RECORD (
284 appraisal_id per_appraisals.appraisal_id%TYPE,
285 object_version_number per_appraisals.object_version_number%TYPE,
286 appraiser_person_id per_appraisals.appraiser_person_id%TYPE,
287 appraisee_person_id per_appraisals.appraisee_person_id%TYPE,
288 full_name per_all_people_f.full_name%TYPE,
289 appraisal_date per_appraisals.appraisal_date%TYPE,
290 apprstatus hr_lookups.meaning%TYPE,
291 apprtype hr_lookups.meaning%TYPE,
292 provide_overall_feedback per_appraisals.provide_overall_feedback%TYPE,
293 appraisal_system_status per_appraisals.appraisal_system_status%TYPE
294 );
295
296 appraisal_record appraisal_rec;
297 error_message varchar2(500) default null;
298 lv_appr_result_status hr_lookups.meaning%TYPE;
299
300 BEGIN
301
302 hr_multi_message.enable_message_list;
303
304 open get_appraisal_info(appr_id);
305 fetch get_appraisal_info into appraisal_record;
306 if get_appraisal_info%NOTFOUND then
307 close get_appraisal_info;
308 raise lv_no_appraisal;
309 else
310 close get_appraisal_info;
311 -- update the appraisal status to completed.
312
313 if( appraisal_record.appraisal_system_status <> 'APPRFEEDBACK')
314 then
315 if( appraisal_record.provide_overall_feedback <> 'Y' )
316 then
317 chg_appr_status := 'N' ;
318 hr_appraisals_api.update_appraisal
319 (p_effective_date => trunc(sysdate)
320 ,p_appraisal_id => appraisal_record.appraisal_id
321 ,p_object_version_number => appraisal_record.object_version_number
322 ,p_appraiser_person_id => appraisal_record.appraiser_person_id
323 ,p_appraisal_system_status => 'COMPLETED');
324 else
325 chg_appr_status := 'Y' ;
326 hr_appraisals_api.update_appraisal
327 (p_effective_date => trunc(sysdate)
328 ,p_appraisal_id => appraisal_record.appraisal_id
329 ,p_object_version_number => appraisal_record.object_version_number
330 ,p_appraiser_person_id => appraisal_record.appraiser_person_id
331 ,p_appraisal_system_status => 'APPRFEEDBACK');
332 end if ;
333 elsif( appraisal_record.appraisal_system_status <> 'COMPLETED')
334 then
335 hr_appraisals_api.update_appraisal
336 (p_effective_date => trunc(sysdate)
337 ,p_appraisal_id => appraisal_record.appraisal_id
338 ,p_object_version_number => appraisal_record.object_version_number
339 ,p_appraiser_person_id => appraisal_record.appraiser_person_id
340 ,p_appraisal_system_status => 'COMPLETED');
341
342 end if ;
343 end if;
344
345 open get_appraisal_status(appr_id);
346 fetch get_appraisal_status into lv_appr_result_status;
347 if is_new_appraisal (item_type, item_key) then
348 p_log := hr_util_misc_web.return_msg_text(
349 p_message_name =>'HR_SUCCESS',
350 p_Application_id =>'PER')
351 || '^' || null;
352 else
353 generate_appraisal_status(appraisal_record.appraisal_date,
354 appraisal_record.full_name,
355 lv_appr_result_status,
356 appraisal_record.apprtype,
357 hr_util_misc_web.return_msg_text(
358 p_message_name =>'HR_SUCCESS',
359 p_Application_id =>'PER'),
360 null,
361 p_log);
362 end if;
363
364 EXCEPTION
365 WHEN lv_no_appraisal then
366 if is_new_appraisal (item_type, item_key) then
367 p_log := hr_util_misc_web.return_msg_text(
368 p_message_name =>'HR_FAILURE',
369 p_Application_id =>'PER')
370 || '^' ||
371 hr_util_misc_web.return_msg_text(
372 p_message_name=>'HR_NO_APPRAISAL_RECORD',
373 p_Application_id=>'PER');
374 else
375 generate_appraisal_status(appraisal_record.appraisal_date,
376 appraisal_record.full_name,
377 appraisal_record.apprstatus,
378 appraisal_record.apprtype,
379 hr_util_misc_web.return_msg_text(
380 p_message_name =>'HR_FAILURE',
381 p_Application_id =>'PER'),
382 hr_util_misc_web.return_msg_text(
383 p_message_name=>'HR_NO_APPRAISAL_RECORD',
384 p_Application_id=>'PER'),
385 p_log);
386 end if;
387
388 chg_appr_status := 'E';
389 raise ;
390 when hr_multi_message.error_message_exist then
391 for i in 1 .. fnd_msg_pub.count_msg Loop
392 error_message := error_message || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
393 end loop;
394 --bug#3767915
395 fnd_msg_pub.Delete_Msg;
396 if is_new_appraisal (item_type, item_key) then
397 p_log := hr_util_misc_web.return_msg_text(
398 p_message_name =>'HR_FAILURE',
399 p_Application_id =>'PER')
400 || '^' || error_message;
401 else
402 generate_appraisal_status(appraisal_record.appraisal_date,
403 appraisal_record.full_name,
404 appraisal_record.apprstatus,
405 appraisal_record.apprtype,
406 hr_util_misc_web.return_msg_text(
407 p_message_name =>'HR_FAILURE',
408 p_Application_id =>'PER'),
409 error_message,
410 p_log);
411 end if;
412 chg_appr_status := 'E';
413 raise ;
414 when others then
415 error_message := error_message || sqlerrm;
416 if is_new_appraisal (item_type, item_key) then
417 p_log := hr_util_misc_web.return_msg_text(
418 p_message_name =>'HR_FAILURE',
419 p_Application_id =>'PER')
420 || '^' || error_message;
421 else
422 generate_appraisal_status(appraisal_record.appraisal_date,
423 appraisal_record.full_name,
424 appraisal_record.apprstatus,
425 appraisal_record.apprtype,
426 hr_util_misc_web.return_msg_text(
427 p_message_name =>'HR_FAILURE',
428 p_Application_id =>'PER'),
429 error_message,
430 p_log);
431 end if;
432
433 --set_appr_status_log(item_type, item_key, 'E',p_log);
434 chg_appr_status := 'E';
435 raise ;
436 END;
437
438 PROCEDURE apply_to_personal_profile
439 ( appr_id per_appraisals.appraisal_id%TYPE,
440 p_log in out nocopy varchar2,
441 apply_pers_profile_status in out nocopy varchar2,
442 p_new_appraisal in boolean DEFAULT true,
443 p_talent_mang_src_typ per_appraisal_templates.COMP_PROFILE_SOURCE_TYPE%TYPE)
444 IS
445 cursor get_assessment_competences (appr_id IN number) is
446 select pce.competence_id, pc.name, pce.competence_element_id, pce.proficiency_level_id,
447 pce.business_group_id, pce.enterprise_id,pce.effective_date_from, pce.effective_date_to, pa.appraisal_id, pa.appraisee_person_id,
448 ppf.party_id, decode(rating.step_value, null, null, rating.step_value||' - '||rating.name) prof_level
449 from per_competence_elements pce, per_appraisals pa, per_all_people_f ppf, per_competences pc,
450 per_rating_levels rating
451 where pce.type='ASSESSMENT'
452 and pce.object_name = 'APPRAISAL_ID'
453 and pce.object_id = appr_id
454 and pa.appraisal_id = pce.object_id
455 and pa.appraisee_person_id = ppf.person_id
456 and pce.competence_id = pc.competence_id
457 and pce.proficiency_level_id = rating.rating_level_id (+)
458 and pce.proficiency_level_id is not null
459 and trunc(sysdate) between nvl(trunc(ppf.effective_start_date),trunc(sysdate))
460 and nvl(trunc(ppf.effective_end_date),trunc(sysdate));
461
462
463 cursor get_personal_competences (appr_id number) is
464 select appr.appraisee_person_id,
465 pce.competence_element_id, pce.object_version_number, pce.type,
466 pce.business_group_id,
467 pce.enterprise_id, pce.competence_id, pce.proficiency_level_id,
468 pce.high_proficiency_level_id, pce.weighting_level_id, pce.rating_level_id,
469 pce.person_id, pce.job_id , pce.valid_grade_id, pce.position_id,
470 pce.organization_id, pce.parent_competence_element_id, pce.activity_version_id,
471 pce.assessment_id, pce.assessment_type_id, pce.mandatory,
472 pce.effective_date_from, pce.effective_date_to, pce.group_competence_type,
473 pce.competence_type, pce.normal_elapse_duration, pce.normal_elapse_duration_unit,
474 pce.sequence_number, pce.source_of_proficiency_level,
475 pce.line_score, pce.certification_date ,
476 pce.certification_method, pce.next_certification_date,
477 pce.comments, pce.attribute_category, pce.attribute1,
478 pce.attribute2, pce.attribute3, pce.attribute4, pce.attribute5,
479 pce.attribute6, pce.attribute7, pce.attribute8, pce.attribute9,
480 pce.attribute10, pce.attribute11, pce.attribute12,
481 pce.attribute13, pce.attribute14, pce.attribute15, pce.attribute16,
482 pce.attribute17, pce.attribute18, pce.attribute19,
483 pce.attribute20, pce.object_id,
484 pce.object_name, pce.party_id
485 from per_appraisals appr, per_competence_elements pce
486 where appr.appraisal_id = appr_id
487 and appr.appraisee_person_id = pce.person_id
488 and pce.type = 'PERSONAL'
489 and trunc(sysdate) between pce.effective_date_from and
490 nvl(pce.effective_date_to, trunc(sysdate));
491
492 cursor get_outcomes_rec (p_competence_element_id IN number, p_effective_date IN date) is
493 Select ceo.COMP_ELEMENT_OUTCOME_ID, ceo.COMPETENCE_ELEMENT_ID, ceo.OUTCOME_ID,
494 ceo.DATE_FROM, ceo.DATE_TO, ceo.OBJECT_VERSION_NUMBER, ceo.ATTRIBUTE_CATEGORY,
495 ceo.ATTRIBUTE1, ceo.ATTRIBUTE2, ceo.ATTRIBUTE3, ceo.ATTRIBUTE4, ceo.ATTRIBUTE5,
496 ceo.ATTRIBUTE6, ceo.ATTRIBUTE7, ceo.ATTRIBUTE8, ceo.ATTRIBUTE9, ceo.ATTRIBUTE10,
497 ceo.ATTRIBUTE11, ceo.ATTRIBUTE12, ceo.ATTRIBUTE13, ceo.ATTRIBUTE14, ceo.ATTRIBUTE15,
498 ceo.ATTRIBUTE16, ceo.ATTRIBUTE17, ceo.ATTRIBUTE18, ceo.ATTRIBUTE19, ceo.ATTRIBUTE20,
499 ceo.INFORMATION_CATEGORY, ceo.INFORMATION1, ceo.INFORMATION2, ceo.INFORMATION3,
500 ceo.INFORMATION4, ceo.INFORMATION5, ceo.INFORMATION6, ceo.INFORMATION7, ceo.INFORMATION8,
501 ceo.INFORMATION9, ceo.INFORMATION10, ceo.INFORMATION11, ceo.INFORMATION12,
502 ceo.INFORMATION13, ceo.INFORMATION14, ceo.INFORMATION15, ceo.INFORMATION16,
503 ceo.INFORMATION17, ceo.INFORMATION18, ceo.INFORMATION19, ceo.INFORMATION20
504 From per_comp_element_outcomes ceo,
505 per_competence_outcomes co
506 Where ceo.Competence_element_id = p_competence_element_id
507 AND co.outcome_id = ceo.outcome_id
508 AND co.DATE_FROM <= ceo.DATE_FROM
509 AND nvl(co.date_to,nvl(ceo.date_to,trunc(sysdate))) >= nvl(ceo.date_to,trunc(sysdate));
510 -- AND ceo.DATE_FROM <= p_effective_date
511 -- And nvl(ceo.DATE_TO,p_effective_date) >= p_effective_date ;
512
513 match_found boolean default false;
514 l_old_ovn per_competence_elements.object_version_number%TYPE;
515 l_new_ovn per_competence_elements.object_version_number%TYPE;
516 l_comp_ele_id per_competence_elements.competence_element_id%TYPE;
517 talent_mang_src_typ varchar2(100);
518 each_comp_status varchar2(10);
519 error_message varchar2(500) default null;
520 assessed_comps number default 0;
521 l_comp_log varchar2(32767);
522
523 l_out_from_date per_comp_element_outcomes.DATE_FROM%type;
524 l_out_ovn per_comp_element_outcomes.OBJECT_VERSION_NUMBER%type;
525 l_comp_ele_out_id per_comp_element_outcomes.COMP_ELEMENT_OUTCOME_ID%type;
526 l_comp_status per_competence_elements.status%type;
527 l_achieved_date per_competence_elements.ACHIEVED_DATE%type;
528
529 BEGIN
530
531 -- write an utility to get the value to apply the changes or not
532
533 hr_multi_message.enable_message_list;
534
535
536
537 for assess_comps in get_assessment_competences(appr_id)
538 loop
539 assessed_comps := assessed_comps + 1;
540
541 if (not p_new_appraisal) and (assessed_comps = 1) then
542 l_comp_log := l_comp_log ||
543 '<br> <br> <table width="100%" summary="" border="0" cellspacing="0" cellpadding="0">' ||
544 '<tr valign="top">' ||
545 '<td align="left" width="70%">' ||
546 '<div><div class="x60">' ||
547 '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">' ||
548 '<tr>' ||
549 '<td width="100%"> <h1 class="x18">'|| hr_util_misc_web.return_msg_text(
550 p_message_name =>'HR_APPR_APPLY_ASSESS_COMPS',
551 p_Application_id =>'PER') || '</h1></td>' ||
552 '</tr>' ||
553 '<tr>' ||
554 '<td class="x2i"></td>' ||
555 '</tr>' ||
556 '</table>' ||
557 '</div>' ||
558 '<script>t(void 0,''5'')</script>' ||
559 '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">' ||
560 '<tr>' ||
561 '<td>' ||
562 '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">' ||
563 '<tr>' ||
564 '<th scope="col" class="x1r">'|| hr_util_misc_web.return_msg_text(
565 p_message_name =>'HR_APPR_COMPETENCY',
566 p_Application_id =>'PER') || '</th>' ||
567 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
568 p_message_name =>'HR_APPR_LEVEL',
569 p_Application_id =>'PER') || ' </th>' ||
570 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
571 p_message_name =>'HR_RESULT',
572 p_Application_id =>'PER') || '</th>' ||
573 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
574 p_message_name =>'HR_REASON',
575 p_Application_id =>'PER') || '</th>' ||
576 '</tr>';
577 if (length(l_comp_log) <=4000) then
578 p_log := l_comp_log;
579 end if;
580 end if;
581
582 match_found := false;
583 each_comp_status := '';
584 for pers_comps in get_personal_competences(appr_id)
585 loop
586 if(assess_comps.competence_id = pers_comps.competence_id
587 and assess_comps.effective_date_from > pers_comps.effective_date_from) then
588 begin
589 --bug#3767915
590 match_found := true;
591 -- end date the element and create a new record
592 l_old_ovn := pers_comps.object_version_number;
593 hr_competence_element_api.update_competence_element
594 (p_competence_element_id => pers_comps.competence_element_id
595 ,p_object_version_number => l_old_ovn
596 ,p_effective_date_to => trunc(sysdate) - 1
597 ,p_effective_date => trunc(sysdate)
598 ,p_validate => false
599 );
600
601 -- Start Added for competence Qualification link enhancement
602 l_comp_status := PerCompStatus.Get_Competence_Status(pers_comps.competence_id,
603 pers_comps.competence_element_id,
604 null,
605 null,
606 null,
607 trunc(sysdate));
608 IF l_comp_status = 'ACHIEVED' then
609 l_achieved_date := trunc(sysdate);
610 else
611 l_achieved_date := null;
612 END if;
613 -- create a new sequence
614 hr_competence_element_api.create_competence_element
615 (p_competence_element_id => l_comp_ele_id
616 ,p_object_version_number => l_new_ovn
617 ,p_type => 'PERSONAL'
618 ,p_competence_id => pers_comps.competence_id
619 ,p_effective_date => trunc(sysdate)
620 ,p_effective_date_from => trunc(sysdate)
621 ,p_proficiency_level_id => assess_comps.proficiency_level_id
622 ,p_business_group_id => pers_comps.business_group_id
623 ,p_source_of_proficiency_level => p_talent_mang_src_typ
624 ,p_party_id => pers_comps.party_id
625 ,p_person_id => pers_comps.appraisee_person_id
626 ,p_attribute_category => pers_comps.attribute_category
627 ,p_attribute1 => pers_comps.attribute1
628 ,p_attribute2 => pers_comps.attribute2
629 ,p_attribute3 => pers_comps.attribute3
630 ,p_attribute4 => pers_comps.attribute4
631 ,p_attribute5 => pers_comps.attribute5
632 ,p_attribute6 => pers_comps.attribute6
633 ,p_attribute7 => pers_comps.attribute7
634 ,p_attribute8 => pers_comps.attribute8
635 ,p_attribute9 => pers_comps.attribute9
636 ,p_attribute10 => pers_comps.attribute10
637 ,p_attribute11 => pers_comps.attribute11
638 ,p_attribute12 => pers_comps.attribute12
639 ,p_attribute13 => pers_comps.attribute13
640 ,p_attribute14 => pers_comps.attribute14
641 ,p_attribute15 => pers_comps.attribute15
642 ,p_attribute16 => pers_comps.attribute16
643 ,p_attribute17 => pers_comps.attribute17
644 ,p_attribute18 => pers_comps.attribute18
645 ,p_attribute19 => pers_comps.attribute19
646 ,p_attribute20 => pers_comps.attribute20
647 ,p_status => l_comp_status -- added for competence qual enhancement
648 ,p_achieved_date => l_achieved_date -- added for competence qual enhancement
649 );
650
651 -- Added for competence qualification link enhancement
652 FOR Outcome_rec IN get_outcomes_rec( pers_comps.competence_element_id, trunc(sysdate))
653 loop
654 l_out_ovn := null;
655 l_comp_ele_out_id := null;
656 -- IF Outcome_rec.DATE_FROM < trunc(sysdate) Then
657 -- l_out_from_date := trunc(sysdate);
658 -- else
659 l_out_from_date := Outcome_rec.DATE_FROM;
660 -- END if;
661 hr_comp_element_outcome_api.create_element_outcome(
662 p_comp_element_outcome_id => l_comp_ele_out_id
663 ,p_object_version_number => l_out_ovn
664 ,p_effective_date => trunc(sysdate)
665 ,p_competence_element_id => l_comp_ele_id
666 ,p_outcome_id => Outcome_rec.outcome_id
667 ,p_date_from => l_out_from_date
668 ,p_date_to => Outcome_rec.DATE_TO
669 ,p_attribute_category => Outcome_rec.ATTRIBUTE_CATEGORY
670 ,p_ATTRIBUTE1 => Outcome_rec.ATTRIBUTE1
671 ,p_ATTRIBUTE2 => Outcome_rec.ATTRIBUTE2
672 ,p_ATTRIBUTE3 => Outcome_rec.ATTRIBUTE3
673 ,p_ATTRIBUTE4 => Outcome_rec.ATTRIBUTE4
674 ,p_ATTRIBUTE5 => Outcome_rec.ATTRIBUTE5
675 ,p_ATTRIBUTE6 => Outcome_rec.ATTRIBUTE6
676 ,p_ATTRIBUTE7 => Outcome_rec.ATTRIBUTE7
677 ,p_ATTRIBUTE8 => Outcome_rec.ATTRIBUTE8
678 ,p_ATTRIBUTE9 => Outcome_rec.ATTRIBUTE9
679 ,p_ATTRIBUTE10 => Outcome_rec.ATTRIBUTE10
680 ,p_ATTRIBUTE11 => Outcome_rec.ATTRIBUTE11
681 ,p_ATTRIBUTE12 => Outcome_rec.ATTRIBUTE12
682 ,p_ATTRIBUTE13 => Outcome_rec.ATTRIBUTE13
683 ,p_ATTRIBUTE14 => Outcome_rec.ATTRIBUTE14
684 ,p_ATTRIBUTE15 => Outcome_rec.ATTRIBUTE15
685 ,p_ATTRIBUTE16 => Outcome_rec.ATTRIBUTE16
686 ,p_ATTRIBUTE17 => Outcome_rec.ATTRIBUTE17
687 ,p_ATTRIBUTE18 => Outcome_rec.ATTRIBUTE18
688 ,p_ATTRIBUTE19 => Outcome_rec.ATTRIBUTE19
689 ,p_ATTRIBUTE20 => Outcome_rec.ATTRIBUTE20
690 ,p_INFORMATION_CATEGORY => Outcome_rec.INFORMATION_CATEGORY
691 ,p_INFORMATION1 => Outcome_rec.INFORMATION1
692 ,p_INFORMATION2 => Outcome_rec.INFORMATION2
693 ,p_INFORMATION3 => Outcome_rec.INFORMATION3
694 ,p_INFORMATION4 => Outcome_rec.INFORMATION4
695 ,p_INFORMATION5 => Outcome_rec.INFORMATION5
696 ,p_INFORMATION6 => Outcome_rec.INFORMATION6
697 ,p_INFORMATION7 => Outcome_rec.INFORMATION7
698 ,p_INFORMATION8 => Outcome_rec.INFORMATION8
699 ,p_INFORMATION9 => Outcome_rec.INFORMATION9
700 ,p_INFORMATION10 => Outcome_rec.INFORMATION10
701 ,p_INFORMATION11 => Outcome_rec.INFORMATION11
702 ,p_INFORMATION12 => Outcome_rec.INFORMATION12
703 ,p_INFORMATION13 => Outcome_rec.INFORMATION13
704 ,p_INFORMATION14 => Outcome_rec.INFORMATION14
705 ,p_INFORMATION15 => Outcome_rec.INFORMATION15
706 ,p_INFORMATION16 => Outcome_rec.INFORMATION16
707 ,p_INFORMATION17 => Outcome_rec.INFORMATION17
708 ,p_INFORMATION18 => Outcome_rec.INFORMATION18
709 ,p_INFORMATION19 => Outcome_rec.INFORMATION19
710 ,p_INFORMATION20 => Outcome_rec.INFORMATION20 );
711 END loop;
712 -- End for competence qualification link enhancement
713
714 each_comp_status := 'S';
715 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ' || hr_util_misc_web.return_msg_text(
716 -- p_message_name=>'HR_ASSESS_COMP_SUCC',
717 -- p_Application_id=>'PER') ||' </A> ';
718
719 if p_new_appraisal then
720 l_comp_log := l_comp_log || assess_comps.competence_element_id || '^'
721 || hr_util_misc_web.return_msg_text(p_message_name =>'HR_SUCCESS', p_Application_id =>'PER') || '~';
722 else
723 l_comp_log := l_comp_log ||
724 '<tr>
725 <td class="x1l x4x" nowrap>' || assess_comps.name || '</td>
726 <td class="x1l x4x" nowrap>' || assess_comps.prof_level || '</td>
727 <td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
728 p_message_name =>'HR_SUCCESS',
729 p_Application_id =>'PER') || '</td>
730 <td class="x1l x4x">' || '</td>
731 </tr>';
732 end if;
733 if (length(l_comp_log) <=4000) then
734 p_log := l_comp_log;
735 end if;
736
737 exception
738 when hr_multi_message.error_message_exist then
739 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ';
740 for i in 1 .. fnd_msg_pub.count_msg Loop
741 error_message := error_message || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
742 end loop;
743 --bug#3767915
744 fnd_msg_pub.Delete_Msg;
745
746 if p_new_appraisal then
747 l_comp_log := l_comp_log || assess_comps.competence_element_id || '^'
748 || hr_util_misc_web.return_msg_text(p_message_name =>'HR_FAILURE', p_Application_id =>'PER') || '^' || error_message || '~';
749 else
750 l_comp_log := l_comp_log ||
751 '<tr>
752 <td class="x1l x4x" nowrap>' || assess_comps.name || '</td>
753 <td class="x1l x4x" nowrap>' || assess_comps.prof_level || '</td>
754 <td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
755 p_message_name =>'HR_FAILURE',
756 p_Application_id =>'PER') || '</td>
757 <td class="x1l x4x">' || error_message || '</td>
758 </tr>';
759 end if;
760 if (length(l_comp_log) <=4000) then
761 p_log := l_comp_log;
762 end if;
763 error_message := null;
764 --p_log := p_log || '</A>';
765 --set_appr_status_log(item_type, item_key, 'W',p_log);
766 apply_pers_profile_status := 'W';
767 each_comp_status := 'E';
768 when others then
769 error_message := error_message || sqlerrm;
770
771 if p_new_appraisal then
772 l_comp_log := l_comp_log || assess_comps.competence_element_id || '^'
773 || hr_util_misc_web.return_msg_text(p_message_name =>'HR_FAILURE', p_Application_id =>'PER') || '^' || error_message || '~';
774 else
775 l_comp_log := l_comp_log ||
776 '<tr>
777 <td class="x1l x4x" nowrap>' || assess_comps.name || '</td>
778 <td class="x1l x4x" nowrap>' || assess_comps.prof_level || '</td>
779 <td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
780 p_message_name =>'HR_FAILURE',
781 p_Application_id =>'PER') || '</td>
782 <td class="x1l x4x">' || error_message || '</td>
783 </tr>';
784 end if;
785 if (length(l_comp_log) <=4000) then
786 p_log := l_comp_log;
787 end if;
788 error_message := null;
789 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ' || sqlcode || sqlerrm || ' </A> ';
790
791 --set_appr_status_log(item_type, item_key, 'W',p_log);
792 apply_pers_profile_status := 'W';
793 each_comp_status := 'E';
794 end;
795 end if;
796 end loop;
797
798
799 if( match_found = false ) then
800 begin
801
802 l_comp_ele_id := null;
803
804 -- since we r not supporting the flex in appraisal
805 -- we wont be having flex parameters to update
806 -- Start Added for competence Qualification link enhancement
807 l_comp_status := PerCompStatus.Get_Competence_Status(assess_comps.competence_id,
808 null,
809 null,
810 null,
811 null,
812 trunc(sysdate));
813 IF l_comp_status = 'ACHIEVED' then
814 l_achieved_date := trunc(sysdate);
815 else
816 l_achieved_date := null;
817 END if;
818 hr_competence_element_api.create_competence_element
819 (
820 p_competence_element_id => l_comp_ele_id
821 ,p_object_version_number => l_new_ovn
822 ,p_type => 'PERSONAL'
823 ,p_business_group_id => assess_comps.business_group_id
824 ,p_competence_id => assess_comps.competence_id
825 ,p_proficiency_level_id => assess_comps.proficiency_level_id -- Modified from prof_level for competence qual enhanc
826 ,p_person_id => assess_comps.appraisee_person_id
827 ,p_effective_date_from => trunc(sysdate)
828 ,p_effective_date => trunc(sysdate)
829 ,p_party_id => assess_comps.party_id
830 ,p_source_of_proficiency_level => p_talent_mang_src_typ
831 ,p_status => l_comp_status -- added for competence qual enhancement
832 ,p_achieved_date => l_achieved_date -- added for competence qual enhancement
833 );
834
835 each_comp_status := 'S';
836 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ' || hr_util_misc_web.return_msg_text(
837 -- p_message_name=>'HR_ASSESS_COMP_SUCC',
838 -- p_Application_id=>'PER') || '</A>';
839
840 if p_new_appraisal then
841 l_comp_log := l_comp_log || assess_comps.competence_element_id || '^'
842 || hr_util_misc_web.return_msg_text(p_message_name =>'HR_SUCCESS', p_Application_id =>'PER') || '~';
843 else
844 l_comp_log := l_comp_log ||
845 '<tr>
846 <td class="x1l x4x" nowrap>' || assess_comps.name || '</td>
847 <td class="x1l x4x" nowrap>' || assess_comps.prof_level || '</td>
848 <td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
849 p_message_name =>'HR_SUCCESS',
850 p_Application_id =>'PER') || '</td>
851 <td class="x1l x4x">' || '</td>
852 </tr>';
853 end if;
854 if (length(l_comp_log) <=4000) then
855 p_log := l_comp_log;
856 end if;
857
858 exception
859 when hr_multi_message.error_message_exist then
860 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ';
861 for i in 1 .. fnd_msg_pub.count_msg Loop
862 error_message := error_message || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
863 end loop;
864 --bug#3767915
865 fnd_msg_pub.Delete_Msg;
866 --p_log := p_log || ' </A> ';
867
868 if p_new_appraisal then
869 l_comp_log := l_comp_log || assess_comps.competence_element_id || '^'
870 || hr_util_misc_web.return_msg_text(p_message_name =>'HR_FAILURE', p_Application_id =>'PER') || '^' || error_message || '~';
871 else
872 l_comp_log := l_comp_log ||
873 '<tr>
874 <td class="x1l x4x" nowrap>' || assess_comps.name || '</td>
875 <td class="x1l x4x" nowrap>' || assess_comps.prof_level || '</td>
876 <td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
877 p_message_name =>'HR_FAILURE',
878 p_Application_id =>'PER') || '</td>
879 <td class="x1l x4x">' || error_message || '</td>
880 </tr>';
881 end if;
882 if (length(l_comp_log) <=4000) then
883 p_log := l_comp_log;
884 end if;
885 error_message := null;
886 --set_appr_status_log(item_type, item_key, 'W',p_log);
887 apply_pers_profile_status := 'W';
888 each_comp_status := 'E';
889 when others then
890 error_message := error_message ||sqlerrm;
891 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || assess_comps.name || ' => ' || sqlcode || sqlerrm || '</A>';
892
893 if p_new_appraisal then
894 l_comp_log := l_comp_log || assess_comps.competence_element_id || '^'
895 || hr_util_misc_web.return_msg_text(p_message_name =>'HR_FAILURE', p_Application_id =>'PER') || '^' || error_message || '~';
896 else
897 l_comp_log := l_comp_log ||
898 '<tr>
899 <td class="x1l x4x" nowrap>' || assess_comps.name || '</td>
900 <td class="x1l x4x" nowrap>' || assess_comps.prof_level || '</td>
901 <td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
902 p_message_name =>'HR_FAILURE',
903 p_Application_id =>'PER') || '</td>
904 <td class="x1l x4x">' || error_message || '</td>
905 </tr>';
906 end if;
907 if (length(l_comp_log) <=4000) then
908 p_log := l_comp_log;
909 end if;
910 --set_appr_status_log(item_type, item_key, 'W',p_log);
911 apply_pers_profile_status := 'W';
912 each_comp_status := 'E';
913 end;
914 end if;
915
916 end loop;
917
918
919 if (not p_new_appraisal) then
920 l_comp_log := l_comp_log || '</table></td></tr></table></div><div></div><script>t(''10'')</script></td></tr></table>';
921 end if;
922 if (length(l_comp_log) <=4000) then
923 p_log := l_comp_log;
924 end if;
925 if not apply_pers_profile_status = 'W' then
926 apply_pers_profile_status := 'S';
927 end if;
928
929 -- check for any training plan members with appraisal id
930 -- if exists then change the status of Training Plan members
931 -- to planned
932
933 -- change the status in per_appraisals to completed.
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 error_message := error_message || sqlerrm;
938
939 if p_new_appraisal then
940 l_comp_log := l_comp_log || '-1' || '^'
941 || hr_util_misc_web.return_msg_text(p_message_name =>'HR_FAILURE', p_Application_id =>'PER') || '^' || error_message || '~';
942 else
943 l_comp_log := l_comp_log ||
944 '<tr>
945 <td class="x1l x4x" nowrap>' || '</td>
946 <td class="x1l x4x" >' || '</td>
947 <td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
948 p_message_name =>'HR_FAILURE',
949 p_Application_id =>'PER') || '</td>
950 <td class="x1l x4x">' || error_message || '</td>
951 </tr>';
952 end if;
953 if (length(l_comp_log) <=4000) then
954 p_log := l_comp_log;
955 end if;
956
957
958 if (not p_new_appraisal) then
959 l_comp_log := l_comp_log || '</table></td></tr></table></div><div></div><script>t(''10'')</script></td></tr></table>';
960 end if;
961 if (length(l_comp_log) <=4000) then
962 p_log := l_comp_log;
963 end if;
964
965 error_message := null;
966 --p_log := p_log || ' <BR> <A class="OraInstructionText"> ' || sqlcode || sqlerrm || '</A> ';
967 --set_appr_status_log(item_type, item_key, 'W',p_log);
968 apply_pers_profile_status := 'W';
969 END;
970
971
972 PROCEDURE generate_lp_courses (p_lp_id number, p_log in out nocopy varchar2, p_new_appraisal in boolean DEFAULT true)
973 IS
974
975 query_lp_courses VARCHAR2(4000) := ' select tav.version_name course_name, hrl.meaning member_status, ' ||
976 ' lpme.completion_target_date, lpme.completion_date ' ||
977 ' from ota_learning_path_members lpm, ota_lp_member_enrollments lpme, ' ||
978 ' ota_activity_versions tav, hr_lookups hrl ' ||
979 ' where lpm.learning_path_id = :1 ' ||
980 ' and lpme.learning_path_member_id = lpm.learning_path_member_id ' ||
981 ' and hrl.lookup_code = lpme.member_status_code ' ||
982 ' and hrl.lookup_type = ''OTA_LP_MEMBER_STATUS''' ||
983 ' and tav.activity_version_id = lpm.activity_version_id ';
984
985 l_lp_courses cur_typ;
986 lv_course_name varchar2(80);
987 lv_course_status hr_lookups.meaning%TYPE;
988 lv_course_targe_date date;
989 lv_course_compl_date date;
990 ln_count number default 0;
991 BEGIN
992
993 OPEN l_lp_courses FOR query_lp_courses USING p_lp_id;
994 LOOP
995 FETCH l_lp_courses INTO lv_course_name, lv_course_status, lv_course_targe_date, lv_course_compl_date;
996 EXIT WHEN l_lp_courses%NOTFOUND;
997 BEGIN
998 ln_count := ln_count + 1;
999 if (not p_new_appraisal) then
1000 IF ln_count = 1 THEN
1001 p_log := p_log ||
1002 '<br>' ||
1003 '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">'||
1004 '<tr>'||
1005 '<td>' ||
1006 '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">' ||
1007 '<tr>' ||
1008 '<th scope="col" class="x1r">'|| hr_util_misc_web.return_msg_text(
1009 p_message_name =>'HR_LP_COURSE',
1010 p_Application_id =>'PER') || '</th>' ||
1011 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
1012 p_message_name =>'HR_STATUS',
1013 p_Application_id =>'PER') || '</th>' ||
1014 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
1015 p_message_name =>'HR_LP_COURSE_TARGET_DATE',
1016 p_Application_id =>'PER') || '</th>' ||
1017 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
1018 p_message_name =>'HR_LP_COURSE_COMP_DATE',
1019 p_Application_id =>'PER') || '</th>' ||
1020 '</tr>';
1021
1022 p_log := p_log ||
1023 '<tr> ' ||
1024 '<td class="x1l x4x" nowrap>' || lv_course_name || '</td>' ||
1025 '<td class="x1l x4x" nowrap>' || lv_course_status || '</td>' ||
1026 '<td class="x1l x4x" nowrap>' || lv_course_targe_date || '</td>' ||
1027 '<td class="x1l x4x" nowrap>' || lv_course_compl_date || '</td>' ||
1028 '</tr>';
1029 ELSE
1030 p_log := p_log ||
1031 '<tr> ' ||
1032 '<td class="x1l x4x" nowrap>' || lv_course_name || '</td>' ||
1033 '<td class="x1l x4x" nowrap>' || lv_course_status || '</td>' ||
1034 '<td class="x1l x4x" nowrap>' || lv_course_targe_date || '</td>' ||
1035 '<td class="x1l x4x" nowrap>' || lv_course_compl_date || '</td>' ||
1036 '</tr>';
1037 END IF;
1038 end if;
1039 END;
1040 END LOOP;
1041
1042 CLOSE l_lp_courses;
1043
1044 IF (ln_count > 0) and (not p_new_appraisal) THEN
1045 p_log := p_log ||
1046 '</table>' ||
1047 '</td>' ||
1048 '</tr>' ||
1049 '</table>';
1050 END IF;
1051
1052 EXCEPTION
1053 WHEN Others THEN
1054 p_log := p_log || ' Error in Courses generation ';
1055 CLOSE l_lp_courses;
1056 END;
1057
1058 PROCEDURE update_train_component_status
1059 ( appr_id IN per_appraisals.appraisal_id%TYPE,
1060 p_log in out nocopy varchar2,
1061 upd_train_comps_status in out nocopy varchar2,
1062 p_new_appraisal in boolean DEFAULT true)
1063 IS
1064
1065 l_cursor cur_typ;
1066 l_status_cursor cur_typ;
1067 l_lp_id number;
1068 l_lp_ovn number;
1069 l_display_to_learner_flag varchar2(10);
1070 l_lpe_id number;
1071 l_lpe_ovn number;
1072 l_path_status varchar2(100);
1073 l_completion_status varchar2(20);
1074 l_lp_name varchar2(80);
1075
1076 query_str VARCHAR2(4000) := ' select lp.learning_path_id, lp.display_to_learner_flag, lp.object_version_number, ' ||
1077 ' lpe.lp_enrollment_id,lpe.object_version_number,hrl.meaning , lptl.name ' ||
1078 ' from ota_learning_paths lp, ota_learning_paths_tl lptl, ota_lp_enrollments lpe, ' ||
1079 ' hr_lookups hrl ' ||
1080 ' where lp.source_id = :1 and lp.path_source_code= :2 ' ||
1081 ' and lp.source_function_code = :3 ' ||
1082 ' and lptl.learning_path_id = lp.learning_path_id ' ||
1083 ' and lptl.language = userenv(''lang'') ' ||
1084 ' and lpe.learning_path_id = lp.learning_path_id ' ||
1085 ' and hrl.lookup_code = lpe.path_status_code ' ||
1086 ' and hrl.lookup_type = ''OTA_LEARNING_PATH_STATUS''';
1087
1088 query_lp_status VARCHAR2(4000) :=
1089 ' select hrl.meaning path_status_code ' ||
1090 ' from ota_learning_paths lp, ' ||
1091 ' ota_lp_enrollments lpe, hr_lookups hrl ' ||
1092 ' where lp.learning_path_id = :1 ' ||
1093 ' and lpe.learning_path_id = lp.learning_path_id ' ||
1094 ' and lpe.path_status_code = hrl.lookup_code ' ||
1095 ' and hrl.lookup_type = ''OTA_LEARNING_PATH_STATUS''';
1096
1097
1098
1099 l_stmt VARCHAR2(2000);
1100 found_training_activities boolean default false;
1101 error_message varchar2(500) default null;
1102 l_lp_status_cursor cur_typ;
1103 BEGIN
1104
1105 hr_multi_message.enable_message_list;
1106
1107 BEGIN
1108 OPEN l_cursor FOR query_str USING appr_id, 'TALENT_MGMT','APPRAISAL';
1109 EXCEPTION
1110 WHEN Others THEN
1111 CLOSE l_cursor;
1112 p_log := '';
1113 upd_train_comps_status := 'S';
1114 RETURN;
1115 END;
1116
1117 FETCH l_cursor INTO l_lp_id ,l_display_to_learner_flag,l_lp_ovn, l_lpe_id,l_lpe_ovn,l_path_status, l_lp_name ;
1118 IF l_cursor%FOUND then
1119
1120 if (not p_new_appraisal) then
1121 p_log := p_log ||
1122 '<br> <br> <table width="100%" summary="" border="0" cellspacing="0" cellpadding="0">' ||
1123 '<tr valign="top">' ||
1124 '<td align="left" width="70%">' ||
1125 '<div><div class="x60">' ||
1126 '<table cellpadding="0" cellspacing="0" border="0" width="100%" summary="">' ||
1127 '<tr>' ||
1128 '<td width="100%"> <h1 class="x18">'|| hr_util_misc_web.return_msg_text(
1129 p_message_name =>'HR_APPR_LEARNING_PATH',
1130 p_Application_id =>'PER') || '</h1></td>' ||
1131 '</tr>' ||
1132 '<tr>' ||
1133 '<td class="x2i"></td>' ||
1134 '</tr>' ||
1135 '</table>' ||
1136 '</div>' ||
1137 '<script>t(void 0,''5'')</script>' ||
1138 '<table cellpadding="0" cellspacing="0" border="0" width="80%" summary="">' ||
1139 '<tr>' ||
1140 '<td>' ||
1141 '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%">' ||
1142 '<tr>' ||
1143 '<th scope="col" class="x1r">'|| hr_util_misc_web.return_msg_text(
1144 p_message_name =>'HR_APPR_LEARNING_PATH',
1145 p_Application_id =>'PER') || '</th>' ||
1146 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
1147 p_message_name =>'HR_STATUS',
1148 p_Application_id =>'PER') || ' </th>' ||
1149 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
1150 p_message_name =>'HR_RESULT',
1151 p_Application_id =>'PER') || '</th>' ||
1152 '<th scope="col" class="x1r x4j">'|| hr_util_misc_web.return_msg_text(
1153 p_message_name =>'HR_REASON',
1154 p_Application_id =>'PER') || '</th>' ||
1155 '</tr>';
1156 end if;
1157
1158 found_training_activities := true;
1159
1160 -- If DisplayToLearner is unchecked then check it
1161
1162 IF (l_display_to_learner_flag <> 'Y' )then
1163 BEGIN
1164 l_stmt := 'begin ota_learning_path_api.update_learning_path( ' ||
1165 'p_effective_date => trunc(sysdate) , ' ||
1166 'p_learning_path_id => :1,' ||
1167 'p_object_version_number => :2 ,' ||
1168 'p_display_to_learner_flag => :3 ); end;';
1169
1170 EXECUTE IMMEDIATE l_stmt using IN l_lp_id,IN OUT l_lp_ovn,IN 'Y';
1171
1172 EXCEPTION
1173 WHEN hr_multi_message.error_message_exist THEN
1174 error_message := error_message || hr_util_misc_web.return_msg_text(
1175 p_message_name =>'HR_APPR_DISP_TO_LEARNER',
1176 p_Application_id =>'PER') || ' => ';
1177 FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
1178 error_message := error_message || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
1179 END LOOP;
1180 --bug#3767915
1181 fnd_msg_pub.Delete_Msg;
1182 --set_appr_status_log(item_type, item_key, 'W',p_log);
1183 upd_train_comps_status := 'W';
1184 WHEN others THEN
1185 error_message := error_message || hr_util_misc_web.return_msg_text(
1186 p_message_name =>'HR_APPR_DISP_TO_LEARNER',
1187 p_Application_id =>'PER') || ' => ' || sqlerrm ;
1188 upd_train_comps_status := 'W';
1189 END;
1190 END IF; -- Display To Learner Flag
1191
1192 -- If Learning Path Status is not ACTIVE then make it ACTIVE so
1193 -- that it triggers the completion process.
1194
1195 IF ( l_path_status <> 'ACTIVE' ) THEN
1196 BEGIN
1197 l_stmt := 'begin ota_lp_enrollment_api.update_lp_enrollment( ' ||
1198 'p_effective_date => trunc(sysdate) , ' ||
1199 'p_lp_enrollment_id => :1 ,' ||
1200 'p_object_version_number => :2 ,' ||
1201 'p_path_status_code => :3); end;';
1202
1203 EXECUTE IMMEDIATE l_stmt using in l_lpe_id, in out l_lpe_ovn, in 'ACTIVE';
1204
1205 EXCEPTION
1206 WHEN hr_multi_message.error_message_exist THEN
1207
1208 error_message := error_message || hr_util_misc_web.return_msg_text(
1209 p_message_name =>'HR_APPR_LP_STATUS',
1210 p_Application_id =>'PER') || ' => ';
1211 FOR i in 1 .. fnd_msg_pub.count_msg LOOP
1212 error_message := error_message || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
1213 END LOOP;
1214 --bug#3767915
1215 fnd_msg_pub.Delete_Msg;
1216 --set_appr_status_log(item_type, item_key, 'W',p_log);
1217 upd_train_comps_status := 'W';
1218 WHEN others THEN
1219 error_message := error_message || hr_util_misc_web.return_msg_text(
1220 p_message_name =>'HR_APPR_LP_STATUS',
1221 p_Application_id =>'PER') || ' => ' || sqlerrm;
1222 upd_train_comps_status := 'W';
1223 END;
1224 END IF; -- Learning Path Status change
1225
1226 -- try to find out if the LP can be completed at this juncture or not
1227
1228 BEGIN
1229 query_str := 'SELECT ota_lrng_path_util.chk_complete_path_ok(:1) from dual';
1230 OPEN l_status_cursor FOR query_str USING l_lpe_id;
1231 EXCEPTION
1232 WHEN hr_multi_message.error_message_exist THEN
1233 error_message := error_message || hr_util_misc_web.return_msg_text(
1234 p_message_name =>'HR_APPR_LP_COMPLETE',
1235 p_Application_id =>'PER') || ' => ';
1236 FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
1237 error_message := error_message || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
1238 END LOOP;
1239
1240 WHEN Others THEN
1241
1242 error_message := error_message || hr_util_misc_web.return_msg_text(
1243 p_message_name =>'HR_APPR_LP_COMPLETE',
1244 p_Application_id =>'PER') || ' => ' || sqlerrm;
1245 upd_train_comps_status := 'S';
1246 END;
1247
1248
1249 FETCH l_status_cursor INTO l_completion_status;
1250 IF l_status_cursor%FOUND THEN
1251 IF (l_completion_status = 'S' ) THEN
1252 --if returned code is 'S' , complete the LP
1253 BEGIN
1254 l_stmt := 'begin ota_lrng_path_util.complete_path(p_lp_enrollment_id => :1 ); end;';
1255 EXECUTE IMMEDIATE l_stmt using IN l_lpe_id;
1256
1257 EXCEPTION
1258 WHEN hr_multi_message.error_message_exist THEN
1259 error_message := error_message || hr_util_misc_web.return_msg_text(
1260 p_message_name =>'HR_APPR_LP_COMPLETE',
1261 p_Application_id =>'PER') || ' => ';
1262 FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
1263 error_message := error_message || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
1264 END LOOP;
1265 --bug#3767915
1266 fnd_msg_pub.Delete_Msg;
1267 --set_appr_status_log(item_type, item_key, 'W',p_log);
1268 upd_train_comps_status := 'W';
1269 WHEN others THEN
1270 error_message := error_message || hr_util_misc_web.return_msg_text(
1271 p_message_name =>'HR_APPR_LP_COMPLETE',
1272 p_Application_id =>'PER') || ' => ' || sqlerrm;
1273 upd_train_comps_status := 'W';
1274 END;
1275 END IF;
1276 END IF; -- Complete Learning Path
1277
1278 -- The LP status can be completed with above step
1279 -- or it remains in the previous step.
1280
1281 BEGIN
1282 OPEN l_lp_status_cursor FOR query_lp_status USING l_lp_id;
1283 EXCEPTION
1284 WHEN Others THEN
1285 CLOSE l_lp_status_cursor;
1286 END;
1287 FETCH l_lp_status_cursor INTO l_completion_status;
1288
1289
1290 if p_new_appraisal then
1291 if (error_message is null) then
1292 p_log := l_lp_id || '^'
1293 || hr_util_misc_web.return_msg_text(
1294 p_message_name =>'HR_SUCCESS',
1295 p_Application_id =>'PER')
1296 || '^';
1297 else
1298 p_log := l_lp_id || '^'
1299 || hr_util_misc_web.return_msg_text(
1300 p_message_name =>'HR_FAILURE',
1301 p_Application_id =>'PER')
1302 || '^' || error_message;
1303 end if;
1304 else
1305 IF(error_message is null ) THEN
1306 p_log := p_log ||
1307 '<tr> ' ||
1308 '<td class="x1l x4x" nowrap>' || l_lp_name || '</td>' ||
1309 '<td class="x1l x4x" nowrap>' || l_completion_status || '</td>' ||
1310 '<td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
1311 p_message_name =>'HR_SUCCESS',
1312 p_Application_id =>'PER') || '</td>' ||
1313 '<td class="x1l x4x">' || '</td>' ||
1314 '</tr>' ||
1315 '</table>' ||
1316 '</td>' ||
1317 '</tr>' ||
1318 '</table>' ||
1319 '</div>' ||
1320 '<div></div>' ||
1321 '<script>t(''10'')</script>' ||
1322 '</td>' ||
1323 '</tr>' ||
1324 '</table>';
1325 ELSE
1326 p_log := p_log ||
1327 '<tr> ' ||
1328 '<td class="x1l x4x" nowrap>' || l_lp_name || '</td>' ||
1329 '<td class="x1l x4x" nowrap>' || l_completion_status || '</td>' ||
1330 '<td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
1331 p_message_name =>'HR_FAILURE',
1332 p_Application_id =>'PER') || '</td>' ||
1333 '<td class="x1l x4x">' || error_message || '</td>' ||
1334 '</tr>' ||
1335 '</table>' ||
1336 '</td>' ||
1337 '</tr>' ||
1338 '</table>' ||
1339 '</div>' ||
1340 '<div></div>' ||
1341 '<script>t(''10'')</script>' ||
1342 '</td>' ||
1343 '</tr>' ||
1344 '</table>';
1345 END IF; -- Error Message
1346 end if;
1347
1348 -- generate courses table
1349 generate_lp_courses(l_lp_id, p_log, p_new_appraisal);
1350
1351 END IF; -- If Learning Path Exists
1352
1353 IF l_cursor%ISOPEN then
1354 CLOSE l_cursor;
1355 END if;
1356 IF l_status_cursor%ISOPEN then
1357 CLOSE l_status_cursor;
1358 END if;
1359
1360 IF found_training_activities = false then
1361 p_log := '';
1362 END IF;
1363
1364 IF NOT upd_train_comps_status = 'W' then
1365 upd_train_comps_status := 'S';
1366 END IF;
1367
1368
1369 EXCEPTION
1370 WHEN others THEN
1371 CLOSE l_cursor;
1372 error_message := error_message || sqlerrm ;
1373 if p_new_appraisal then
1374 p_log := l_lp_id || '^'
1375 || hr_util_misc_web.return_msg_text(
1376 p_message_name =>'HR_FAILURE',
1377 p_Application_id =>'PER')
1378 || '^' || error_message;
1379 else
1380 p_log := p_log ||
1381 '<tr> ' ||
1382 '<td class="x1l x4x" nowrap>' || l_lp_name || '</td>' ||
1383 '<td class="x1l x4x" nowrap>' || l_path_status || '</td>' ||
1384 '<td class="x1l x4x">' || hr_util_misc_web.return_msg_text(
1385 p_message_name =>'HR_FAILURE',
1386 p_Application_id =>'PER') || '</td>' ||
1387 '<td class="x1l x4x">' || error_message || '</td>' ||
1388 '</tr>' ||
1389 '</table>' ||
1390 '</td>' ||
1391 '</tr>' ||
1392 '</table>' ||
1393 '</div>' ||
1394 '<div></div>' ||
1395 '<script>t(''10'')</script>' ||
1396 '</td>' ||
1397 '</tr>' ||
1398 '</table>';
1399 end if;
1400 --set_appr_status_log(item_type, item_key, 'W',p_log);
1401 upd_train_comps_status := 'W';
1402 END;
1403
1404
1405 PROCEDURE create_event (appr_id per_appraisals.appraisal_id%TYPE,
1406 p_log in out nocopy varchar2,
1407 upd_create_event_status in out nocopy varchar2,
1408 p_new_appraisal in boolean DEFAULT true)
1409 IS
1410 --
1411 -- To modify this template, edit file PROC.TXT in TEMPLATE
1412 -- directory of SQL Navigator
1413 --
1414 -- Purpose: Briefly explain the functionality of the procedure
1415 --
1416 -- MODIFICATION HISTORY
1417 -- Person Date Comments
1418 -- --------- ------ -------------------------------------------
1419 l_ovn number;
1420 l_event_id per_events.event_id%TYPE;
1421 l_perf_rev_id number;
1422 l_next_rev_date_warn boolean;
1423 l_sql_err varchar2(10000);
1424
1425 cursor get_appr_overall_perf(appr_id per_appraisals.appraisal_id%TYPE) IS
1426 select appr.appraisal_id, appr.object_version_number, appr.appraiser_person_id,
1427 appr.appraisee_person_id, appr.overall_performance_level_id, prl.step_value,
1428 (prl.step_value||' - '||prl.name) overall_rating
1429 from per_appraisals appr, per_rating_levels prl
1430 where appraisal_id = appr_id
1431 and appr.overall_performance_level_id = prl.rating_level_id;
1432
1433 TYPE appraisal_record is RECORD (
1434 appraisal_id per_appraisals.appraisal_id%TYPE,
1435 object_version_number per_appraisals.object_version_number%TYPE,
1436 appraiser_person_id per_appraisals.appraiser_person_id%TYPE,
1437 appraisee_person_id per_appraisals.appraisee_person_id%TYPE,
1438 overall_performance_level_id per_appraisals.appraisee_person_id%TYPE,
1439 step_value per_rating_levels.step_value%TYPE,
1440 overall_perf_rating varchar2(100)
1441 );
1442
1443 appraisal_rec appraisal_record;
1444
1445 cursor check_contingent_worker(appraisee_person_id per_all_people_f.person_id%TYPE) IS
1446 select current_npw_flag from per_all_people_f
1447 where person_id = appraisee_person_id
1448 and trunc(sysdate) between effective_start_date and effective_end_date;
1449
1450 current_npw_flag per_all_people_f.current_npw_flag%TYPE default '';
1451 found_overall_performance boolean := false;
1452 error_message varchar2(500) default null;
1453
1454 -- Declare program variables as shown above
1455 BEGIN
1456
1457 -- if there is no overall_performance entered then
1458 -- there is no need of creating an event or performance review
1459 -- as event is tied up to performance review for this release.
1460 -- this behavior might change in future. As per functional
1461 -- discussion with caroline.
1462
1463 hr_multi_message.enable_message_list;
1464
1465 open get_appr_overall_perf(appr_id);
1466 fetch get_appr_overall_perf into appraisal_rec;
1467 if get_appr_overall_perf%FOUND then
1468
1469 open check_contingent_worker(appraisal_rec.appraisee_person_id);
1470 fetch check_contingent_worker into current_npw_flag;
1471
1472 -- Incase of CWK dont create an event
1473 if ((current_npw_flag is null) or (not current_npw_flag = 'Y')) then
1474
1475
1476 per_events_api.create_event
1477 (p_date_start => trunc(sysdate)
1478 ,p_type => 'APPRAISAL'
1479 ,p_event_id => l_event_id
1480 ,p_object_version_number => l_ovn
1481 );
1482
1483 -- retrieve
1484 hr_perf_review_api.create_perf_review
1485 (p_performance_review_id => l_perf_rev_id
1486 ,p_person_id => appraisal_rec.appraisee_person_id
1487 ,p_event_id => l_event_id
1488 ,p_review_date => trunc(sysdate)
1489 ,p_performance_rating => appraisal_rec.step_value
1490 ,p_object_version_number => l_ovn
1491 ,p_next_review_date_warning => l_next_rev_date_warn
1492 );
1493
1494
1495 hr_appraisals_api.update_appraisal
1496 (p_effective_date => trunc(sysdate)
1497 ,p_appraisal_id => appr_id
1498 ,p_appraiser_person_id => appraisal_rec.appraiser_person_id
1499 ,p_object_version_number => appraisal_rec.object_version_number
1500 ,p_event_id => l_event_id);
1501
1502 found_overall_performance := true;
1503
1504 generate_event(appraisal_rec.overall_perf_rating, trunc(sysdate),
1505 hr_util_misc_web.return_msg_text(
1506 p_message_name =>'HR_SUCCESS',
1507 p_Application_id =>'PER') , null, p_log, p_new_appraisal);
1508
1509 end if;
1510 close check_contingent_worker;
1511 end if;
1512 close get_appr_overall_perf;
1513
1514 if(not found_overall_performance) then
1515 p_log := '';
1516 end if;
1517
1518 if not upd_create_event_status = 'W' then
1519 upd_create_event_status := 'S';
1520 end if;
1521
1522 EXCEPTION
1523 WHEN hr_multi_message.error_message_exist then
1524 close check_contingent_worker;
1525 close get_appr_overall_perf;
1526 for i in 1 .. fnd_msg_pub.count_msg Loop
1527 error_message := error_message || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
1528 end loop;
1529 generate_event(appraisal_rec.overall_perf_rating,
1530 trunc(sysdate),
1531 hr_util_misc_web.return_msg_text(
1532 p_message_name =>'HR_FAILURE',
1533 p_Application_id =>'PER') ,
1534 error_message,
1535 p_log,
1536 p_new_appraisal);
1537 --bug#3767915
1538 fnd_msg_pub.Delete_Msg;
1539 --set_appr_status_log(item_type, item_key, 'W',p_log);
1540 upd_create_event_status := 'E';
1541
1542 WHEN others THEN
1543 error_message := error_message || sqlerrm;
1544 upd_create_event_status := 'W';
1545 generate_event(appraisal_rec.overall_perf_rating,
1546 trunc(sysdate),
1547 hr_util_misc_web.return_msg_text(
1548 p_message_name =>'HR_FAILURE',
1549 p_Application_id =>'PER'),
1550 error_message,
1551 p_log,
1552 p_new_appraisal);
1553 END; -- Procedure
1554
1555 PROCEDURE check_item_attribute ( p_item_type varchar2, p_item_key varchar2, p_attr_name varchar2 )
1556 IS
1557 BEGIN
1558 if not hr_workflow_service.item_attribute_exists
1559 (p_item_type => p_item_type
1560 ,p_item_key => p_item_key
1561 ,p_name => p_attr_name) then
1562 -- the item attribute does not exist so create it
1563 wf_engine.additemattr
1564 (itemtype => p_item_type
1565 ,itemkey => p_item_key
1566 ,aname => p_attr_name);
1567 end if;
1568
1569 END;
1570
1571 PROCEDURE create_new_objectives(p_appr_objs in appr_obj_table)
1572 IS
1573
1574 I INTEGER default 0;
1575 l_objective_id number;
1576 --
1577 -- Variables for IN/OUT parameters
1578 l_weighting_over_100_warning boolean;
1579 l_weighting_appraisal_warning boolean;
1580 l_object_version_number number;
1581
1582 BEGIN
1583
1584 FOR I IN 1 ..p_appr_objs.count LOOP
1585 --
1586 -- Call API
1587 --
1588 hr_objectives_api.create_objective
1589 (p_validate => false
1590 ,p_effective_date => trunc(sysdate)
1591 ,p_business_group_id => p_appr_objs(I).business_group_id
1592 ,p_name => p_appr_objs(I).name
1593 ,p_start_date => p_appr_objs(I).start_date
1594 ,p_target_date => p_appr_objs(I).target_date
1595 ,p_owning_person_id => p_appr_objs(I).owning_person_id
1596 ,p_achievement_date => p_appr_objs(I).achievement_date
1597 ,p_detail => p_appr_objs(I).detail
1598 ,p_comments => p_appr_objs(I).comments
1599 ,p_success_criteria => p_appr_objs(I).success_criteria
1600 ,p_attribute_category => p_appr_objs(I).attribute_category
1601 ,p_attribute1 => p_appr_objs(I).attribute1
1602 ,p_attribute2 => p_appr_objs(I).attribute2
1603 ,p_attribute3 => p_appr_objs(I).attribute3
1604 ,p_attribute4 => p_appr_objs(I).attribute4
1605 ,p_attribute5 => p_appr_objs(I).attribute5
1606 ,p_attribute6 => p_appr_objs(I).attribute6
1607 ,p_attribute7 => p_appr_objs(I).attribute7
1608 ,p_attribute8 => p_appr_objs(I).attribute8
1609 ,p_attribute9 => p_appr_objs(I).attribute9
1610 ,p_attribute10 => p_appr_objs(I).attribute10
1611 ,p_attribute11 => p_appr_objs(I).attribute11
1612 ,p_attribute12 => p_appr_objs(I).attribute12
1613 ,p_attribute13 => p_appr_objs(I).attribute13
1614 ,p_attribute14 => p_appr_objs(I).attribute14
1615 ,p_attribute15 => p_appr_objs(I).attribute15
1616 ,p_attribute16 => p_appr_objs(I).attribute16
1617 ,p_attribute17 => p_appr_objs(I).attribute17
1618 ,p_attribute18 => p_appr_objs(I).attribute18
1619 ,p_attribute19 => p_appr_objs(I).attribute19
1620 ,p_attribute20 => p_appr_objs(I).attribute20
1621 ,p_attribute21 => p_appr_objs(I).attribute21
1622 ,p_attribute22 => p_appr_objs(I).attribute22
1623 ,p_attribute23 => p_appr_objs(I).attribute23
1624 ,p_attribute24 => p_appr_objs(I).attribute24
1625 ,p_attribute25 => p_appr_objs(I).attribute25
1626 ,p_attribute26 => p_appr_objs(I).attribute26
1627 ,p_attribute27 => p_appr_objs(I).attribute27
1628 ,p_attribute28 => p_appr_objs(I).attribute28
1629 ,p_attribute29 => p_appr_objs(I).attribute29
1630 ,p_attribute30 => p_appr_objs(I).attribute30
1631 ,p_scorecard_id => p_appr_objs(I).scorecard_id
1632 ,p_copied_from_library_id => p_appr_objs(I).copied_from_library_id
1633 ,p_copied_from_objective_id => p_appr_objs(I).objective_id
1634 ,p_aligned_with_objective_id => p_appr_objs(I).aligned_with_objective_id
1635 ,p_next_review_date => p_appr_objs(I).next_review_date
1636 ,p_group_code => p_appr_objs(I).group_code
1637 ,p_priority_code => p_appr_objs(I).priority_code
1638 ,p_appraise_flag => p_appr_objs(I).appraise_flag
1639 ,p_verified_flag => p_appr_objs(I).verified_flag
1640 ,p_target_value => p_appr_objs(I).target_value
1641 ,p_weighting_percent => p_appr_objs(I).weighting_percent
1642 ,p_complete_percent => p_appr_objs(I).complete_percent
1643 ,p_uom_code => p_appr_objs(I).uom_code
1644 ,p_measurement_style_code => p_appr_objs(I).measurement_style_code
1645 ,p_measure_name => p_appr_objs(I).measure_name
1646 ,p_measure_type_code => p_appr_objs(I).measure_type_code
1647 ,p_measure_comments => p_appr_objs(I).measure_comments
1648 ,p_sharing_access_code => p_appr_objs(I).sharing_access_code
1649 ,p_weighting_over_100_warning => l_weighting_over_100_warning
1650 ,p_weighting_appraisal_warning => l_weighting_appraisal_warning
1651 ,p_objective_id => l_objective_id
1652 ,p_object_version_number => l_object_version_number
1653 );
1654 --
1655 -- Convert API warning boolean parameter values to specific
1656 -- messages and add them to Multiple Message List
1657 --
1658 if l_weighting_over_100_warning then
1659 fnd_message.set_name('PER', 'HR_50198_WPM_WEIGHT_WARN');
1660 hr_multi_message.add
1661 (p_message_type => hr_multi_message.g_warning_msg);
1662 end if;
1663 if l_weighting_appraisal_warning then
1664 fnd_message.set_name('PER', 'HR_50223_WPM_APPRAISE_WARN');
1665 hr_multi_message.add
1666 (p_message_type => hr_multi_message.g_warning_msg);
1667 end if;
1668
1669 END LOOP;
1670
1671 EXCEPTION WHEN OTHERS THEN
1672 raise;
1673 END;
1674
1675 PROCEDURE update_appr_objectives(p_appr_objs in appr_obj_table)
1676 IS
1677
1678 I INTEGER default 0;
1679 l_objective_id number;
1680 --
1681 -- Variables for IN/OUT parameters
1682 l_weighting_over_100_warning boolean;
1683 l_weighting_appraisal_warning boolean;
1684 l_object_version_number number;
1685
1686 BEGIN
1687
1688 FOR I IN 1 ..p_appr_objs.count LOOP
1689 --
1690 -- Call API
1691 --
1692 l_object_version_number := p_appr_objs(I).object_version_number;
1693 hr_objectives_api.update_objective
1694 (p_validate => false
1695 ,p_effective_date => trunc(sysdate) --<== ?
1696 ,p_objective_id => p_appr_objs(I).objective_id
1697 ,p_object_version_number => l_object_version_number
1698 ,p_scorecard_id => null
1699 ,p_weighting_over_100_warning => l_weighting_over_100_warning
1700 ,p_weighting_appraisal_warning => l_weighting_appraisal_warning
1701 );
1702 --
1703 -- Convert API warning boolean parameter values to specific
1704 -- messages and add them to Multiple Message List
1705 --
1706 if l_weighting_over_100_warning then
1707 fnd_message.set_name('PER', 'HR_50198_WPM_WEIGHT_WARN');
1708 hr_multi_message.add
1709 (p_message_type => hr_multi_message.g_warning_msg);
1710 end if;
1711 if l_weighting_appraisal_warning then
1712 fnd_message.set_name('PER', 'HR_50223_WPM_APPRAISE_WARN');
1713 hr_multi_message.add
1714 (p_message_type => hr_multi_message.g_warning_msg);
1715 end if;
1716
1717 END LOOP;
1718
1719 EXCEPTION WHEN OTHERS THEN
1720 raise;
1721 END;
1722
1723
1724
1725 PROCEDURE post_appraisal_completion(p_appraisal_id in number)
1726 IS
1727 l_plan_id per_perf_mgmt_plans.plan_id%type;
1728 l_curr_appr_tmplt_id per_appraisal_templates.appraisal_template_id%type;
1729
1730 CURSOR get_plan_template_id IS
1731 SELECT plan_id, appraisal_template_id
1732 FROM per_appraisals
1733 WHERE appraisal_id = p_appraisal_id;
1734
1735 l_appr_objs appr_obj_table;
1736
1737 BEGIN
1738
1739 -- first check if appraisal is part of a plan
1740 OPEN get_plan_template_id;
1741 FETCH get_plan_template_id into l_plan_id, l_curr_appr_tmplt_id;
1742 CLOSE get_plan_template_id; -- close cursor variable
1743 IF (l_plan_id is not null) THEN
1744
1745 -- bulk fetch all existing objectives and scorecard_id with appraisalId as input into table
1746 OPEN get_appr_objectives(p_appraisal_id);
1747 FETCH get_appr_objectives BULK COLLECT into l_appr_objs;
1748 CLOSE get_appr_objectives; -- close cursor variable
1749 -- Loop thru the table and create objective with copied objectiveid and scorecardid
1750 create_new_objectives(l_appr_objs);
1751 -- Loop thru the rows and update all existing objectives with null scorecardid
1752 update_appr_objectives(l_appr_objs);
1753
1754 END IF;
1755
1756
1757 EXCEPTION WHEN OTHERS THEN
1758 raise;
1759 END;
1760
1761 PROCEDURE COMPLETE_APPR
1762 ( item_type IN varchar2,
1763 item_key IN varchar2,
1764 p_result_out in out nocopy varchar2)
1765 IS
1766 appraisal_id per_appraisals.appraisal_id%type;
1767 l_ins_ovn number;
1768 l_ins_comp_id number;
1769 next_comp_ele_id per_competence_elements.competence_element_id%TYPE;
1770 l_person_id number;
1771 l_log varchar2(4000);
1772 chg_appr_status varchar2(2);
1773 apply_pers_profile_status varchar2(2);
1774 upd_train_comps_status varchar2(2);
1775 upd_create_event_status varchar2(2);
1776 lv_chg_appr_status_log wf_item_attributes.text_default%TYPE;
1777 lv_apply_pers_comps_log wf_item_attributes.text_default%TYPE;
1778 lv_upd_train_comps_status_log wf_item_attributes.text_default%TYPE;
1779 lv_upd_create_event_status_log wf_item_attributes.text_default%TYPE;
1780 p_new_appraisal boolean;
1781 l_talent_mang_src_typ per_appraisal_templates.COMP_PROFILE_SOURCE_TYPE%TYPE;
1782 l_available_flag per_appraisal_templates.available_flag%TYPE;
1783 update_personal_profile varchar2(100) default null;
1784 l_proc varchar2(100);
1785 cursor c_appr_template_details(c_appraisal_id in number) is
1786 select available_flag ,update_personal_comp_profile,comp_profile_source_type
1787 from per_appraisal_templates pat ,per_appraisals pa where
1788 pa.appraisal_template_id = pat.appraisal_template_id
1789 and pa.appraisal_id = c_appraisal_id;
1790 BEGIN
1791 l_proc := 'HR_COMPLETE_APPRAISAL_SS.COMPLETE_APPR';
1792 -- hr_utility.trace_on(null,'APPR');
1793 hr_utility.set_location(' Entering:' || l_proc,5);
1794 appraisal_id := wf_engine.GetItemAttrNumber (itemtype => item_type ,
1795 itemkey => item_key ,
1796 aname => 'APPRAISAL_ID',
1797 ignore_notfound => true);
1798 hr_utility.set_location(' Entering:' || l_proc,10);
1799 if appraisal_id is null then
1800 hr_utility.set_location(' Entering:' || l_proc,20);
1801 l_log := l_log || 'No Appraisal Id for this WorkFlow Transaction';
1802 raise lv_no_appraisal;
1803 end if;
1804 open c_appr_template_details(appraisal_id);
1805 fetch c_appr_template_details into l_available_flag,update_personal_profile,l_talent_mang_src_typ;
1806 if (c_appr_template_details%NOTFOUND) then
1807 hr_utility.set_message(800,'HR_52256_APR_TEMP_MANDATORY');
1808 hr_utility.raise_error;
1809 end if;
1810
1811 hr_utility.set_location(' Entering:' || l_proc,30);
1812 check_item_attribute(item_type, item_key, gv_appr_compl_status);
1813 hr_utility.set_location(' Entering:' || l_proc,35);
1814 check_item_attribute(item_type, item_key, gv_upd_appr_status_log);
1815 hr_utility.set_location(' Entering:' || l_proc,40);
1816 check_item_attribute(item_type, item_key, gv_apply_asses_comps_log);
1817 hr_utility.set_location(' Entering:' || l_proc,45);
1818 check_item_attribute(item_type, item_key, gv_create_event_log);
1819 hr_utility.set_location(' Entering:' || l_proc,50);
1820 check_item_attribute(item_type, item_key, gv_upd_trn_act_status_log);
1821 hr_utility.set_location(' Entering:' || l_proc,55);
1822
1823 savepoint complete_appraisal_status;
1824
1825 -- change the appraisal system status to completed
1826 -- if this errors out then exit from the procudure
1827 -- with out processing furhter
1828 hr_utility.set_location(' Entering:' || l_proc,60);
1829 change_appr_status(appraisal_id, item_type, item_key, lv_chg_appr_status_log, chg_appr_status);
1830 hr_utility.set_location(' Entering:' || l_proc,70);
1831 set_appr_status_log(item_type, item_key, chg_appr_status, gv_upd_appr_status_log, lv_chg_appr_status_log);
1832 hr_utility.set_location(' Entering:' || l_proc,75);
1833
1834 -- create and event and add a performance record
1835 if l_available_flag is null then
1836 hr_utility.set_location(' Entering:' || l_proc,77);
1837 update_personal_profile := fnd_profile.value('HR_APPLY_COMPETENCIES_TO_PERSON');
1838 l_talent_mang_src_typ := fnd_profile.value('HR_TALENT_MGMT_SRC_TYPE');
1839 end if;
1840 hr_utility.set_location(' Entering:' || l_proc,80);
1841 p_new_appraisal := is_new_appraisal(item_type, item_key);
1842 if(update_personal_profile is not null and update_personal_profile = 'Y') then
1843 hr_utility.set_location(' Entering:' || l_proc,85);
1844 apply_to_personal_profile(appraisal_id, lv_apply_pers_comps_log, apply_pers_profile_status, p_new_appraisal,l_talent_mang_src_typ);
1845 hr_utility.set_location(' Entering:' || l_proc,90);
1846 set_appr_status_log(item_type, item_key, apply_pers_profile_status, gv_apply_asses_comps_log, lv_apply_pers_comps_log);
1847 hr_utility.set_location(' Entering:' || l_proc,95);
1848 end if;
1849
1850 -- update the OTA status
1851 --update_train_component_status(appraisal_id, item_type, item_key, l_log, p_result_out);
1852 hr_utility.set_location(' Entering:' || l_proc,100);
1853 update_train_component_status(appraisal_id, lv_upd_train_comps_status_log, upd_train_comps_status, p_new_appraisal);
1854 hr_utility.set_location(' Entering:' || l_proc,105);
1855 set_appr_status_log(item_type, item_key, upd_train_comps_status, gv_upd_trn_act_status_log, lv_upd_train_comps_status_log);
1856 hr_utility.set_location(' Entering:' || l_proc,110);
1857
1858 create_event(appraisal_id, lv_upd_create_event_status_log, upd_create_event_status, p_new_appraisal);
1859 hr_utility.set_location(' Entering:' || l_proc,115);
1860 set_appr_status_log(item_type, item_key, upd_create_event_status, gv_create_event_log, lv_upd_create_event_status_log);
1861 hr_utility.set_location(' Entering:' || l_proc,120);
1862 -- add the following line to disable multi messaging for fixing bug#5947176
1863 hr_multi_message.disable_message_list;
1864 -- END changes for bug#5947176
1865
1866 post_appraisal_completion(appraisal_id);
1867
1868 if( chg_appr_status = 'W' or apply_pers_profile_status = 'W' or upd_train_comps_status = 'W' or upd_create_event_status = 'W') then
1869 hr_utility.set_location(' Entering:' || l_proc,125);
1870 p_result_out := 'W';
1871 else
1872 hr_utility.set_location(' Entering:' || l_proc,130);
1873 p_result_out := 'S';
1874 end if;
1875
1876
1877
1878
1879 EXCEPTION
1880 -- when there is an exception it is due to change appraisal status
1881 -- for all other tasks it there wont be any exception raise,
1882 -- instead the errors are written to WorkFlow so that appraisal
1883 -- can read and display corresponding message / warning.
1884 WHEN lv_no_appraisal THEN
1885 p_result_out := 'E';
1886 hr_utility.set_location(' Entering:' || l_proc,200);
1887 set_appr_status_log(item_type, item_key, 'E', gv_upd_appr_status_log, lv_chg_appr_status_log);
1888 WHEN others THEN
1889 hr_utility.set_location(' Entering:' || l_proc,300);
1890 rollback to complete_appraisal_status;
1891 p_result_out := 'E';
1892 set_appr_status_log(item_type, item_key, 'E', gv_upd_appr_status_log, lv_chg_appr_status_log);
1893 END; -- Procedure
1894
1895
1896
1897 PROCEDURE SEND_NOTIFICATION
1898 ( p_item_type IN varchar2,
1899 p_item_key IN varchar2,
1900 p_result_out in out nocopy varchar2)
1901 IS
1902 l_appraisal_id per_appraisals.appraisal_id%type;
1903 update_personal_profile varchar2(100) default null;
1904
1905 CURSOR get_appraisee_access(appr_id per_appraisals.appraisal_id%TYPE) IS
1906 select appraisal_id, appraisee_access, system_type from per_appraisals where appraisal_id = appr_id;
1907
1908 TYPE appraisal_access_info is RECORD (
1909 appraisal_id per_appraisals.appraisal_id%TYPE,
1910 appraisee_access per_appraisals.appraisee_access%TYPE,
1911 system_type per_appraisals.system_type%TYPE
1912 );
1913
1914 appr_access_info appraisal_access_info;
1915
1916
1917 BEGIN
1918
1919 p_result_out := 'N';
1920 l_appraisal_id := wf_engine.GetItemAttrNumber (itemtype => p_item_type ,
1921 itemkey => p_item_key ,
1922 aname => 'APPRAISAL_ID',
1923 ignore_notfound=>false);
1924
1925
1926 open get_appraisee_access(l_appraisal_id);
1927 fetch get_appraisee_access into appr_access_info;
1928 if get_appraisee_access%FOUND then
1929 if appr_access_info.system_type is not null then
1930 if( appr_access_info.system_type = 'MGR360' or appr_access_info.system_type = 'MGRSTD' ) then
1931 if not (appr_access_info.appraisee_access = 'NONE' or appr_access_info.appraisee_access is null) then
1932 p_result_out := 'Y';
1933 end if;
1934 else
1935 p_result_out := 'Y';
1936 end if;
1937 end if;
1938 end if;
1939 close get_appraisee_access;
1940 EXCEPTION
1941 WHEN OTHERS THEN
1942 raise;
1943 END;
1944
1945 END HR_COMPLETE_APPRAISAL_SS;