[Home] [Help]
PACKAGE BODY: APPS.HR_APPRAISALS_API
Source
1 Package Body hr_appraisals_api as
2 /* $Header: peaprapi.pkb 120.5.12020000.3 2013/02/21 09:38:21 schowdhu ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_appraisals_api.';
7 --
8 -- ---------------------------------------------------------------------------
9 -- |-----------------------< <create_appraisal> >--------------------------|
10 -- ---------------------------------------------------------------------------
11 --
12 procedure create_appraisal
13 (p_validate in boolean default false,
14 p_effective_date in date,
15 p_business_group_id in number,
16 p_appraisal_template_id in number,
17 p_appraisee_person_id in number,
18 p_appraiser_person_id in number,
19 p_appraisal_date in date default null,
20 p_appraisal_period_start_date in date,
21 p_appraisal_period_end_date in date ,
22 p_type in varchar2 default null,
23 p_next_appraisal_date in date default null,
24 p_status in varchar2 default null,
25 p_group_date in date default null,
26 p_group_initiator_id in number default null,
27 p_comments in varchar2 default null,
28 p_overall_performance_level_id in number default null,
29 p_open in varchar2 default 'Y',
30 p_attribute_category in varchar2 default null,
31 p_attribute1 in varchar2 default null,
32 p_attribute2 in varchar2 default null,
33 p_attribute3 in varchar2 default null,
34 p_attribute4 in varchar2 default null,
35 p_attribute5 in varchar2 default null,
36 p_attribute6 in varchar2 default null,
37 p_attribute7 in varchar2 default null,
38 p_attribute8 in varchar2 default null,
39 p_attribute9 in varchar2 default null,
40 p_attribute10 in varchar2 default null,
41 p_attribute11 in varchar2 default null,
42 p_attribute12 in varchar2 default null,
43 p_attribute13 in varchar2 default null,
44 p_attribute14 in varchar2 default null,
45 p_attribute15 in varchar2 default null,
46 p_attribute16 in varchar2 default null,
47 p_attribute17 in varchar2 default null,
48 p_attribute18 in varchar2 default null,
49 p_attribute19 in varchar2 default null,
50 p_attribute20 in varchar2 default null,
51 p_system_type in varchar2 default null,
52 p_system_params in varchar2 default null,
53 p_appraisee_access in varchar2 default null,
54 p_main_appraiser_id in number default null,
55 p_assignment_id in number default null,
56 p_assignment_start_date in date default null,
57 p_asg_business_group_id in number default null,
58 p_assignment_organization_id in number default null,
59 p_assignment_job_id in number default null,
60 p_assignment_position_id in number default null,
61 p_assignment_grade_id in number default null,
62 p_appraisal_id out nocopy number,
63 p_object_version_number out nocopy number,
64 p_appraisal_system_status in varchar2 default null,
65 p_potential_readiness_level in varchar2 default null,
66 p_potential_short_term_workopp in varchar2 default null,
67 p_potential_long_term_workopp in varchar2 default null,
68 p_potential_details in varchar2 default null,
69 p_event_id in number default null,
70 p_show_competency_ratings in varchar2 default null,
71 p_show_objective_ratings in varchar2 default null,
72 p_show_questionnaire_info in varchar2 default null,
73 p_show_participant_details in varchar2 default null,
74 p_show_participant_ratings in varchar2 default null,
75 p_show_participant_names in varchar2 default null,
76 p_show_overall_ratings in varchar2 default null,
77 p_show_overall_comments in varchar2 default null,
78 p_update_appraisal in varchar2 default null,
79 p_provide_overall_feedback in varchar2 default null,
80 p_appraisee_comments in varchar2 default null,
81 p_plan_id in number default null,
82 p_offline_status in varchar2 default null,
83 p_retention_potential in varchar2 default null,
84 p_show_participant_comments in varchar2 default null -- 8651478 bug fix
85 )
86 is
87 --
88 -- Declare cursors and local variables
89 --
90 --
91 l_proc varchar2(72) := g_package||'create_appraisal';
92 l_appraisal_id per_appraisals.appraisal_id%TYPE;
93 l_object_version_number per_appraisals.object_version_number%TYPE;
94
95 begin
96 hr_utility.set_location('Entering:'|| l_proc, 5);
97 --
98 -- Issue a savepoint.
99 --
100 savepoint create_appraisal;
101 hr_utility.set_location(l_proc, 6);
102 --
103 -- Call Before Process User Hook
104 --
105 begin
106 hr_appraisals_bk1.create_appraisal_b (
107 p_effective_date => p_effective_date,
108 p_business_group_id => p_business_group_id,
109 p_appraisal_template_id => p_appraisal_template_id,
110 p_appraisee_person_id => p_appraisee_person_id,
111 p_appraiser_person_id => p_appraiser_person_id,
112 p_appraisal_date => p_appraisal_date,
113 p_appraisal_period_start_date => p_appraisal_period_start_date,
114 p_appraisal_period_end_date => p_appraisal_period_end_date,
115 p_type => p_type,
116 p_next_appraisal_date => p_next_appraisal_date,
117 p_status => p_status,
118 p_group_date => p_group_date,
119 p_group_initiator_id => p_group_initiator_id,
120 p_comments => p_comments,
121 p_overall_performance_level_id => p_overall_performance_level_id,
122 p_open => p_open,
123 p_attribute_category => p_attribute_category,
124 p_attribute1 => p_attribute1,
125 p_attribute2 => p_attribute2,
126 p_attribute3 => p_attribute3,
127 p_attribute4 => p_attribute4,
128 p_attribute5 => p_attribute5,
129 p_attribute6 => p_attribute6,
130 p_attribute7 => p_attribute7,
131 p_attribute8 => p_attribute8,
132 p_attribute9 => p_attribute9,
133 p_attribute10 => p_attribute10,
134 p_attribute11 => p_attribute11,
135 p_attribute12 => p_attribute12,
136 p_attribute13 => p_attribute13,
137 p_attribute14 => p_attribute14,
138 p_attribute15 => p_attribute15,
139 p_attribute16 => p_attribute16,
140 p_attribute17 => p_attribute17,
141 p_attribute18 => p_attribute18,
142 p_attribute19 => p_attribute19,
143 p_attribute20 => p_attribute20,
144 p_system_type => p_system_type ,
145 p_system_params => p_system_params,
146 p_appraisee_access => p_appraisee_access ,
147 p_main_appraiser_id => p_main_appraiser_id ,
148 p_assignment_id => p_assignment_id ,
149 p_assignment_start_date => p_assignment_start_date ,
150 p_asg_business_group_id => p_asg_business_group_id ,
151 p_assignment_organization_id => p_assignment_organization_id ,
152 p_assignment_job_id => p_assignment_job_id ,
153 p_assignment_position_id => p_assignment_position_id ,
154 p_assignment_grade_id => p_assignment_grade_id,
155 p_appraisal_system_status => p_appraisal_system_status,
156 p_potential_readiness_level => p_potential_readiness_level,
157 p_potential_short_term_workopp => p_potential_short_term_workopp,
158 p_potential_long_term_workopp => p_potential_long_term_workopp,
159 p_potential_details => p_potential_details,
160 p_event_id => p_event_id,
161 p_show_competency_ratings => p_show_competency_ratings,
162 p_show_objective_ratings => p_show_objective_ratings,
163 p_show_questionnaire_info => p_show_questionnaire_info,
164 p_show_participant_details => p_show_participant_details,
165 p_show_participant_ratings => p_show_participant_ratings,
166 p_show_participant_names => p_show_participant_names,
167 p_show_overall_ratings => p_show_overall_ratings,
168 p_show_overall_comments => p_show_overall_comments,
169 p_update_appraisal => p_update_appraisal,
170 p_provide_overall_feedback => p_provide_overall_feedback,
171 p_appraisee_comments => p_appraisee_comments,
172 p_plan_id => p_plan_id,
173 p_offline_status => p_offline_status,
174 p_retention_potential => p_retention_potential,
175 p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
176 );
177 exception
178 when hr_api.cannot_find_prog_unit then
179 hr_api.cannot_find_prog_unit_error
180 (p_module_name => 'create_appraisal',
181 p_hook_type => 'BP'
182 );
183 end;
184 --
185 -- End of Before Process User Hook call
186 --
187 -- Validation in addition to Table Handlers
188 --
189 hr_utility.set_location(l_proc, 7);
190 --
191 -- Process Logic
192 --
193 per_apr_ins.ins
194 (p_validate => p_validate,
195 p_effective_date => p_effective_date,
196 p_business_group_id => p_business_group_id,
197 p_appraisal_template_id => p_appraisal_template_id,
198 p_appraisee_person_id => p_appraisee_person_id,
199 p_appraiser_person_id => p_appraiser_person_id,
200 p_appraisal_date => p_appraisal_date,
201 p_appraisal_period_start_date => p_appraisal_period_start_date,
202 p_appraisal_period_end_date => p_appraisal_period_end_date,
203 p_type => p_type,
204 p_next_appraisal_date => p_next_appraisal_date,
205 p_status => p_status,
206 p_group_date => p_group_date,
207 p_group_initiator_id => p_group_initiator_id,
208 p_comments => p_comments,
209 p_overall_performance_level_id => p_overall_performance_level_id,
210 p_open => p_open,
211 p_attribute_category => p_attribute_category,
212 p_attribute1 => p_attribute1,
213 p_attribute2 => p_attribute2,
214 p_attribute3 => p_attribute3,
215 p_attribute4 => p_attribute4,
216 p_attribute5 => p_attribute5,
217 p_attribute6 => p_attribute6,
218 p_attribute7 => p_attribute7,
219 p_attribute8 => p_attribute8,
220 p_attribute9 => p_attribute9,
221 p_attribute10 => p_attribute10,
222 p_attribute11 => p_attribute11,
223 p_attribute12 => p_attribute12,
224 p_attribute13 => p_attribute13,
225 p_attribute14 => p_attribute14,
226 p_attribute15 => p_attribute15,
227 p_attribute16 => p_attribute16,
228 p_attribute17 => p_attribute17,
229 p_attribute18 => p_attribute18,
230 p_attribute19 => p_attribute19,
231 p_attribute20 => p_attribute20,
232 p_appraisal_id => l_appraisal_id,
233 p_object_version_number => l_object_version_number,
234 p_system_type => p_system_type ,
235 p_system_params => p_system_params,
236 p_appraisee_access => p_appraisee_access ,
237 p_main_appraiser_id => p_main_appraiser_id ,
238 p_assignment_id => p_assignment_id ,
239 p_assignment_start_date => p_assignment_start_date ,
240 p_asg_business_group_id => p_asg_business_group_id ,
241 p_assignment_organization_id => p_assignment_organization_id ,
242 p_assignment_job_id => p_assignment_job_id ,
243 p_assignment_position_id => p_assignment_position_id ,
244 p_assignment_grade_id => p_assignment_grade_id,
245 p_appraisal_system_status => p_appraisal_system_status,
246 p_potential_readiness_level => p_potential_readiness_level,
247 p_potential_short_term_workopp => p_potential_short_term_workopp,
248 p_potential_long_term_workopp => p_potential_long_term_workopp,
249 p_potential_details => p_potential_details,
250 p_event_id => p_event_id,
251 p_show_competency_ratings => p_show_competency_ratings,
252 p_show_objective_ratings => p_show_objective_ratings,
253 p_show_questionnaire_info => p_show_questionnaire_info,
254 p_show_participant_details => p_show_participant_details,
255 p_show_participant_ratings => p_show_participant_ratings,
256 p_show_participant_names => p_show_participant_names,
257 p_show_overall_ratings => p_show_overall_ratings,
258 p_show_overall_comments => p_show_overall_comments,
259 p_update_appraisal => p_update_appraisal,
260 p_provide_overall_feedback => p_provide_overall_feedback,
261 p_appraisee_comments => p_appraisee_comments,
262 p_plan_id => p_plan_id,
263 p_offline_status => p_offline_status,
264 p_retention_potential => p_retention_potential,
265 p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
266 );
267 --
268 hr_utility.set_location(l_proc, 8);
269 --
270 -- Call After Process User Hook
271 --
272 begin
273 hr_appraisals_bk1.create_appraisal_a (
274 p_effective_date => p_effective_date,
275 p_business_group_id => p_business_group_id,
276 p_appraisal_template_id => p_appraisal_template_id,
277 p_appraisee_person_id => p_appraisee_person_id,
278 p_appraiser_person_id => p_appraiser_person_id,
279 p_appraisal_date => p_appraisal_date,
280 p_appraisal_period_start_date => p_appraisal_period_start_date,
281 p_appraisal_period_end_date => p_appraisal_period_end_date,
282 p_type => p_type,
283 p_next_appraisal_date => p_next_appraisal_date,
284 p_status => p_status,
285 p_group_date => p_group_date,
286 p_group_initiator_id => p_group_initiator_id,
287 p_comments => p_comments,
288 p_overall_performance_level_id => p_overall_performance_level_id,
289 p_open => p_open,
290 p_attribute_category => p_attribute_category,
291 p_attribute1 => p_attribute1,
292 p_attribute2 => p_attribute2,
293 p_attribute3 => p_attribute3,
294 p_attribute4 => p_attribute4,
295 p_attribute5 => p_attribute5,
296 p_attribute6 => p_attribute6,
297 p_attribute7 => p_attribute7,
298 p_attribute8 => p_attribute8,
299 p_attribute9 => p_attribute9,
300 p_attribute10 => p_attribute10,
301 p_attribute11 => p_attribute11,
302 p_attribute12 => p_attribute12,
303 p_attribute13 => p_attribute13,
304 p_attribute14 => p_attribute14,
305 p_attribute15 => p_attribute15,
306 p_attribute16 => p_attribute16,
307 p_attribute17 => p_attribute17,
308 p_attribute18 => p_attribute18,
309 p_attribute19 => p_attribute19,
310 p_attribute20 => p_attribute20,
311 p_appraisal_id => l_appraisal_id,
312 p_system_type => p_system_type ,
313 p_system_params => p_system_params,
314 p_appraisee_access => p_appraisee_access ,
315 p_main_appraiser_id => p_main_appraiser_id ,
316 p_assignment_id => p_assignment_id ,
317 p_assignment_start_date => p_assignment_start_date ,
318 p_asg_business_group_id => p_asg_business_group_id ,
319 p_assignment_organization_id => p_assignment_organization_id ,
320 p_assignment_job_id => p_assignment_job_id ,
321 p_assignment_position_id => p_assignment_position_id ,
322 p_assignment_grade_id => p_assignment_grade_id ,
323 p_object_version_number => l_object_version_number,
324 p_appraisal_system_status => p_appraisal_system_status,
325 p_potential_readiness_level => p_potential_readiness_level,
326 p_potential_short_term_workopp => p_potential_short_term_workopp,
327 p_potential_long_term_workopp => p_potential_long_term_workopp,
328 p_potential_details => p_potential_details,
329 p_event_id => p_event_id,
330 p_show_competency_ratings => p_show_competency_ratings,
331 p_show_objective_ratings => p_show_objective_ratings,
332 p_show_questionnaire_info => p_show_questionnaire_info,
333 p_show_participant_details => p_show_participant_details,
334 p_show_participant_ratings => p_show_participant_ratings,
335 p_show_participant_names => p_show_participant_names,
336 p_show_overall_ratings => p_show_overall_ratings,
337 p_show_overall_comments => p_show_overall_comments,
338 p_update_appraisal => p_update_appraisal,
339 p_provide_overall_feedback => p_provide_overall_feedback,
340 p_appraisee_comments => p_appraisee_comments,
341 p_plan_id => p_plan_id,
342 p_offline_status => p_offline_status,
343 p_retention_potential => p_retention_potential,
344 p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
345 );
346 exception
347 when hr_api.cannot_find_prog_unit then
348 hr_api.cannot_find_prog_unit_error
349 (p_module_name => 'create_appraisal',
350 p_hook_type => 'AP'
351 );
352 end;
353 --
354 --
355 -- When in validation only mode raise the Validate_Enabled exception
356 --
357 if p_validate then
358 raise hr_api.validate_enabled;
359 end if;
360 --
361 -- Set all output arguments
362 --
363 p_appraisal_id := l_appraisal_id;
364 p_object_version_number := l_object_version_number;
365 --
366 hr_utility.set_location(' Leaving:'||l_proc, 11);
367 exception
368 when hr_api.validate_enabled then
369 --
370 -- As the Validate_Enabled exception has been raised
371 -- we must rollback to the savepoint
372 --
373 ROLLBACK TO create_appraisal;
374 --
375 -- Only set output warning arguments
376 -- (Any key or derived arguments must be set to null
377 -- when validation only mode is being used.)
378 --
379 p_appraisal_id := null;
380 p_object_version_number := null;
381 --
382 when others then
383 --
384 -- A validation or unexpected error has occurred
385 --
386 -- Added as part of fix to bug 632482
387 --
388 ROLLBACK TO create_appraisal;
389 raise;
390 --
391 -- End of fix.
392 --
393 hr_utility.set_location(' Leaving:'||l_proc, 12);
394 end create_appraisal;
395 --
396 --
397 -- ---------------------------------------------------------------------------
398 -- |-----------------------< <update_learning_path> >--------------------------|
399 -- ---------------------------------------------------------------------------
400 procedure update_learning_path
401 (p_appraisal_id in number,
402 p_appraisal_system_status in varchar2)
403 is
404 l_ota_error_num NUMBER;
405 l_lpmid NUMBER;
406 l_lpmeid NUMBER;
407 l_act_ver_id NUMBER;
408 l_ver_name VARCHAR2(80);
409 l_lpeid NUMBER;
410 l_lpid NUMBER;
411 l_lpname VARCHAR2(80);
412 l_lpme_ovn NUMBER;
413 l_lpe_ovn NUMBER;
414 l_found_components boolean;
415
416 TYPE dynamic_ota_cursor_type IS REF CURSOR;
417 dynamic_ota_cursor dynamic_ota_cursor_type;
418
419
420 dynamic_ota_lpme_query varchar2(3600) := ' select lpm.learning_path_member_id, lpme.lp_member_enrollment_id, ' ||
421 ' lpm.activity_version_id, tav.version_name , lpe.lp_enrollment_id, lptl.name, ' ||
422 ' lpme.object_version_number, lpe.object_version_number ' ||
423 ' from ota_learning_path_members lpm, ota_learning_paths lp,ota_lp_sections lps, ' ||
424 ' ota_lp_enrollments lpe, ota_lp_member_enrollments lpme, ota_activity_versions tav, ' ||
425 ' ota_learning_paths_tl lptl ' ||
426 ' where lp.source_id = :1 ' ||
427 ' and source_function_code = ''APPRAISAL''' ||
428 ' and lptl.learning_path_id = lp.learning_path_id ' ||
429 ' and lptl.language = userenv(''lang'') ' ||
430 ' and lpe.learning_path_id = lp.learning_path_id ' ||
431 ' and lps.learning_path_id = lp.learning_path_id ' ||
432 ' and lpm.learning_path_section_id = lps.learning_path_section_id ' ||
433 ' and lpme.learning_path_member_id = lpm.learning_path_member_id ' ||
434 ' and tav.activity_version_id = lpm.activity_version_id ';
435
436 dynamic_ota_lpe_query varchar2(3600) := ' select lp.learning_path_id, lpe.lp_enrollment_id, ' ||
437 ' lpe.object_version_number ' ||
438 ' from ota_learning_paths lp, ota_lp_enrollments lpe ' ||
439 ' where lp.source_id = :1 ' ||
440 ' and lp.source_function_code = ''APPRAISAL''' ||
441 ' and lpe.learning_path_id = lp.learning_path_id ';
442
443 dynamic_ota_lpme_upd varchar2(3600) := 'begin ota_lp_member_enrollment_api.update_lp_member_enrollment( ' ||
444 'p_effective_date => trunc(sysdate) , ' ||
445 'p_lp_member_enrollment_id => :1 ,' ||
446 'p_object_version_number => :2 ,' ||
447 'p_member_status_code => :3); end;';
448 dynamic_ota_lpe_upd varchar2(3600) := ' BEGIN ota_lp_enrollment_api.update_lp_enrollment( ' ||
449 'p_effective_date => trunc(sysdate) ' ||
450 ',p_lp_enrollment_id => :1 ' ||
451 ',p_path_status_code => :2 ' ||
452 ',p_object_version_number => :3); END;';
453
454 begin
455 if (p_appraisal_system_status = 'DELETED') then
456 begin
457 open dynamic_ota_cursor for dynamic_ota_lpme_query USING p_appraisal_id;
458 loop
459 fetch dynamic_ota_cursor into l_lpmid, l_lpmeid, l_act_ver_id, l_ver_name, l_lpeid, l_lpname, l_lpme_ovn , l_lpe_ovn;
460 exit when dynamic_ota_cursor%NOTFOUND;
461
462 EXECUTE IMMEDIATE dynamic_ota_lpme_upd USING in l_lpmeid, in out l_lpme_ovn, in 'CANCELLED';
463 l_found_components := true;
464 end loop;
465 close dynamic_ota_cursor;
466 exception
467 when others then
468 if (dynamic_ota_cursor%ISOPEN) then
469 close dynamic_ota_cursor;
470 end if;
471 if (hr_utility.debug_enabled) then
472 hr_utility.set_location('Exception raised in update_learning_path while executing dynamic sql dynamic_ota_lpme_query' || sqlerrm, 1);
473 end if;
474 end;
475 end if;
476
477 if l_found_components = true then
478 EXECUTE IMMEDIATE dynamic_ota_lpe_upd USING in l_lpeid, in 'CANCELLED', in out l_lpe_ovn ;
479 else
480 if (p_appraisal_system_status = 'DELETED') then
481 begin
482 open dynamic_ota_cursor for dynamic_ota_lpe_query USING p_appraisal_id;
483 loop
484 fetch dynamic_ota_cursor into l_lpid, l_lpeid, l_lpe_ovn;
485 exit when dynamic_ota_cursor%NOTFOUND;
486 EXECUTE IMMEDIATE dynamic_ota_lpe_upd USING in l_lpeid, in 'CANCELLED', in out l_lpe_ovn ;
487 end loop;
488 close dynamic_ota_cursor;
489 exception
490 when others then
491 if (dynamic_ota_cursor%ISOPEN) then
492 close dynamic_ota_cursor;
493 end if;
494 if (hr_utility.debug_enabled) then
495 hr_utility.set_location('Exception raised in update_learning_path while executing dynamic sql dynamic_ota_lpe_query' || sqlerrm, 1);
496 end if;
497 end;
498 end if;
499 end if;
500 exception
501 when OTHERS then
502 l_ota_error_num := sqlcode;
503 if (dynamic_ota_cursor%ISOPEN) then
504 close dynamic_ota_cursor;
505 end if;
506 if ((l_ota_error_num= -904) or (l_ota_error_num = -6550) or (l_ota_error_num = -942)) then
507 if (hr_utility.debug_enabled) then
508 hr_utility.set_location('Oracle iLearning (OTA) is not installed. Contact your System Administrator', 1);
509 end if;
510 else
511 raise;
512 end if;
513 end;
514 -- ---------------------------------------------------------------------------
515 -- |-----------------------< <update_appraisal> >--------------------------|
516 -- ---------------------------------------------------------------------------
517 --
518 procedure update_appraisal
519 (p_validate in boolean default false,
520 p_effective_date in date,
521 p_appraisal_id in number,
522 p_object_version_number in out nocopy number,
523 p_appraiser_person_id in number,
524 p_appraisal_date in date default hr_api.g_date,
525 p_appraisal_period_end_date in date default hr_api.g_date,
526 p_appraisal_period_start_date in date default hr_api.g_date,
527 p_type in varchar2 default hr_api.g_varchar2,
528 p_next_appraisal_date in date default hr_api.g_date,
529 p_status in varchar2 default hr_api.g_varchar2,
530 p_comments in varchar2 default hr_api.g_varchar2,
531 p_overall_performance_level_id in number default hr_api.g_number,
532 p_open in varchar2 default hr_api.g_varchar2,
533 p_attribute_category in varchar2 default hr_api.g_varchar2,
534 p_attribute1 in varchar2 default hr_api.g_varchar2,
535 p_attribute2 in varchar2 default hr_api.g_varchar2,
536 p_attribute3 in varchar2 default hr_api.g_varchar2,
537 p_attribute4 in varchar2 default hr_api.g_varchar2,
538 p_attribute5 in varchar2 default hr_api.g_varchar2,
539 p_attribute6 in varchar2 default hr_api.g_varchar2,
540 p_attribute7 in varchar2 default hr_api.g_varchar2,
541 p_attribute8 in varchar2 default hr_api.g_varchar2,
542 p_attribute9 in varchar2 default hr_api.g_varchar2,
543 p_attribute10 in varchar2 default hr_api.g_varchar2,
544 p_attribute11 in varchar2 default hr_api.g_varchar2,
545 p_attribute12 in varchar2 default hr_api.g_varchar2,
546 p_attribute13 in varchar2 default hr_api.g_varchar2,
547 p_attribute14 in varchar2 default hr_api.g_varchar2,
548 p_attribute15 in varchar2 default hr_api.g_varchar2,
549 p_attribute16 in varchar2 default hr_api.g_varchar2,
550 p_attribute17 in varchar2 default hr_api.g_varchar2,
551 p_attribute18 in varchar2 default hr_api.g_varchar2,
552 p_attribute19 in varchar2 default hr_api.g_varchar2,
553 p_attribute20 in varchar2 default hr_api.g_varchar2,
554 p_system_type in varchar2 default hr_api.g_varchar2,
555 p_system_params in varchar2 default hr_api.g_varchar2,
556 p_appraisee_access in varchar2 default hr_api.g_varchar2,
557 p_main_appraiser_id in number default hr_api.g_number,
558 p_assignment_id in number default hr_api.g_number,
559 p_assignment_start_date in date default hr_api.g_date,
560 p_asg_business_group_id in number default hr_api.g_number,
561 p_assignment_organization_id in number default hr_api.g_number,
562 p_assignment_job_id in number default hr_api.g_number,
563 p_assignment_position_id in number default hr_api.g_number,
564 p_assignment_grade_id in number default hr_api.g_number,
565 p_appraisal_system_status in varchar2 default hr_api.g_varchar2,
566 p_potential_readiness_level in varchar2 default hr_api.g_varchar2,
567 p_potential_short_term_workopp in varchar2 default hr_api.g_varchar2,
568 p_potential_long_term_workopp in varchar2 default hr_api.g_varchar2,
569 p_potential_details in varchar2 default hr_api.g_varchar2,
570 p_event_id in number default hr_api.g_number,
571 p_show_competency_ratings in varchar2 default hr_api.g_varchar2,
572 p_show_objective_ratings in varchar2 default hr_api.g_varchar2,
573 p_show_questionnaire_info in varchar2 default hr_api.g_varchar2,
574 p_show_participant_details in varchar2 default hr_api.g_varchar2,
575 p_show_participant_ratings in varchar2 default hr_api.g_varchar2,
576 p_show_participant_names in varchar2 default hr_api.g_varchar2,
577 p_show_overall_ratings in varchar2 default hr_api.g_varchar2,
578 p_show_overall_comments in varchar2 default hr_api.g_varchar2,
579 p_update_appraisal in varchar2 default hr_api.g_varchar2,
580 p_provide_overall_feedback in varchar2 default hr_api.g_varchar2,
581 p_appraisee_comments in varchar2 default hr_api.g_varchar2,
582 p_plan_id in number default hr_api.g_number,
583 p_offline_status in varchar2 default hr_api.g_varchar2,
584 p_retention_potential in varchar2 default hr_api.g_varchar2,
585 p_show_participant_comments in varchar2 default hr_api.g_varchar2 -- 8651478 bug fix
586 ) is
587 --
588 -- Declare cursors and local variables
589 --
590
591 l_proc varchar2(72) := g_package||'update_appraisal';
592 l_object_version_number per_appraisals.object_version_number%TYPE;
593 l_asn_object_version_number per_assessments.object_version_number%TYPE;
594 l_assessment_id per_assessments.assessment_id%TYPE;
595 l_old_mainap_id per_appraisals.main_appraiser_id%TYPE;
596 l_participant_id_1 per_participants.participant_id%TYPE;
597 l_participant_id_2 per_participants.participant_id%TYPE;
598 l_part_object_version_number_1 per_participants.object_version_number%TYPE;
599 l_part_object_version_number_2 per_participants.object_version_number%TYPE;
600 l_appraisee_person_id per_appraisals.appraisee_person_id%TYPE;
601 l_appraiser_person_id per_appraisals.appraiser_person_id%TYPE;
602 l_appraisal_sys_status per_appraisals.appraisal_system_status%TYPE;
603 --
604 lv_object_version_number number := p_object_version_number ;
605 --
606 l_person_id NUMBER(9,0);
607 l_ota_installed varchar2(10);
608 CURSOR get_appraisee_person_id (p_appraisal_id IN number) IS
609 SELECT appraisee_person_id
610 FROM per_appraisals
611 WHERE appraisal_id = p_appraisal_id;
612 -- ----------------------------------------------------------------------
613 -- Declare Local Procedure and Functions
614 -- ----------------------------------------------------------------------
615 --
616 -- ----------------------------------------------------------------------
617 -- ---------------------< Get_Assessment_Details >-----------------------
618 -- ----------------------------------------------------------------------
619 PROCEDURE Get_Assessment_Details (p_appraisal_id IN NUMBER
620 ,p_assessment_id OUT NOCOPY NUMBER
621 ,p_object_version_number OUT NOCOPY NUMBER)
622 IS
623 --
624 CURSOR csr_get_asn_details IS
625 SELECT assessment_id , object_version_number
626 FROM per_assessments
627 WHERE appraisal_id = p_appraisal_id;
628 --
629 BEGIN
630 --
631 OPEN csr_get_asn_details;
632 FETCH csr_get_asn_details into p_assessment_id , p_object_version_number;
633 CLOSE csr_get_asn_details;
634 --
635 exception
636 when others then
637 p_assessment_id := null;
638 p_object_version_number := null;
639 raise;
640
641 END Get_Assessment_Details;
642 --
643 -- ----------------------------------------------------------------------
644 -- ------------------< Appraisal_Period_Date_Changed >-------------------
645 -- ----------------------------------------------------------------------
646 FUNCTION Appraisal_Period_Date_Changed(p_appraisal_id IN NUMBER
647 ,p_appraisal_period_start_date IN DATE
648 ,p_appraisal_period_end_date IN DATE
649 ,p_appraisal_date IN DATE
650 )
651 RETURN BOOLEAN
652 IS
653 --
654 -- The assessment start and end dates will be found if an assessment
655 -- is part of this appraisal
656 --
657 CURSOR csr_get_period IS
658 SELECT asn.assessment_period_start_date, asn.assessment_period_end_date,
659 asn.assessment_date
660 FROM per_assessments asn
661 WHERE asn.appraisal_id = p_appraisal_id;
662 --
663 l_asn_start_date per_assessments.assessment_period_start_date%TYPE;
664 l_asn_end_date per_assessments.assessment_period_end_date%TYPE;
665 l_asn_date per_assessments.assessment_date%TYPE;
666 --
667 BEGIN
668 --
669 OPEN csr_get_period;
670 FETCH csr_get_period INTO l_asn_start_date, l_asn_end_date, l_asn_date;
671 --
672 IF csr_get_period%FOUND -- i.e. if there is an assessment
673 AND (l_asn_start_date <> p_appraisal_period_start_date -- and the dates differ ..
674 OR l_asn_end_date <> p_appraisal_period_end_date
675 OR l_asn_date <> p_appraisal_date) THEN
676 --
677 CLOSE csr_get_period;
678 --
679 -- The assessment will need updating as the dates are different from the appraisals.
680 --
681 RETURN TRUE;
682 --
683 ELSE
684 --
685 CLOSE csr_get_period;
686 --
687 -- Either there is no assessment for this appraisal, or the dates are the same.
688 --
689 RETURN FALSE;
690 --
691 END IF;
692 END Appraisal_Period_Date_Changed;
693 -- ----------------------------------------------------------------------
694 -- ------------------< Participant_Exists >-------------------
695 -- ----------------------------------------------------------------------
696 PROCEDURE Get_Participant_Id(p_appraisal_id IN NUMBER
697 ,p_person_id IN NUMBER
698 ,p_participant_id OUT NOCOPY NUMBER
699 ,p_ovn OUT NOCOPY NUMBER
700 )
701 IS
702 --
703 -- check if Participant exists for the Appraisal and the PersonId
704 --
705 CURSOR csr_get_participant IS
706 SELECT par.participant_id, par.object_version_number
707 FROM per_participants par
708 WHERE par.participation_in_id = p_appraisal_id
709 and par.participation_in_table = 'PER_APPRAISALS'
710 and par.participation_in_column = 'APPRAISAL_ID'
711 and par.person_id = p_person_id;
712 --
713 BEGIN
714 --
715 OPEN csr_get_participant;
716 FETCH csr_get_participant INTO p_participant_id, p_ovn;
717 if csr_get_participant%NOTFOUND then
718 p_participant_id := NULL;
719 p_ovn := NULL;
720 end if;
721 CLOSE csr_get_participant;
722 END Get_Participant_Id;
723 --
724 -- ----------------------------------------------------------------------
725 -- ------------------< Participant_Exists >-------------------
726 -- ----------------------------------------------------------------------
727 FUNCTION Get_Old_Main_Appraiser_Id(p_appraisal_id IN NUMBER
728 )
729 RETURN NUMBER
730 IS
731 --
732 -- check if Participant exists for the Appraisal and the PersonId
733 --
734 CURSOR csr_get_map_id IS
735 SELECT apr.main_appraiser_id
736 FROM per_appraisals apr
737 WHERE apr.appraisal_id = p_appraisal_id;
738 --
739 l_main_appraiser_id per_appraisals.main_appraiser_id%TYPE;
740 BEGIN
741 --
742 OPEN csr_get_map_id;
743 FETCH csr_get_map_id INTO l_main_appraiser_id;
744 --
745 IF csr_get_map_id%FOUND THEN
746 --
747 CLOSE csr_get_map_id;
748 --
749 -- There is a Participant
750 --
751 RETURN l_main_appraiser_id;
752 --
753 ELSE
754 --
755 CLOSE csr_get_map_id;
756 --
757 -- There is no Participant for MA
758 --
759 RETURN NULL;
760 --
761 END IF;
762 END Get_Old_Main_Appraiser_Id;
763
764 --
765 begin
766 hr_utility.set_location('Entering:'|| l_proc, 5);
767 --
768 -- Issue a savepoint.
769 --
770 savepoint update_appraisal;
771 hr_utility.set_location(l_proc, 6);
772 --
773 --
774 -- Call Before Process User Hook
775 --
776 begin
777 hr_appraisals_bk2.update_appraisal_b (
778 p_effective_date => p_effective_date,
779 p_appraisal_id => p_appraisal_id,
780 p_object_version_number => p_object_version_number,
781 p_appraiser_person_id => p_appraiser_person_id,
782 p_appraisal_date => p_appraisal_date,
783 p_appraisal_period_start_date => p_appraisal_period_start_date,
784 p_appraisal_period_end_date => p_appraisal_period_end_date,
785 p_type => p_type,
786 p_next_appraisal_date => p_next_appraisal_date,
787 p_status => p_status,
788 p_comments => p_comments,
789 p_overall_performance_level_id => p_overall_performance_level_id,
790 p_open => p_open,
791 p_attribute_category => p_attribute_category,
792 p_attribute1 => p_attribute1,
793 p_attribute2 => p_attribute2,
794 p_attribute3 => p_attribute3,
795 p_attribute4 => p_attribute4,
796 p_attribute5 => p_attribute5,
797 p_attribute6 => p_attribute6,
798 p_attribute7 => p_attribute7,
799 p_attribute8 => p_attribute8,
800 p_attribute9 => p_attribute9,
801 p_attribute10 => p_attribute10,
802 p_attribute11 => p_attribute11,
803 p_attribute12 => p_attribute12,
804 p_attribute13 => p_attribute13,
805 p_attribute14 => p_attribute14,
806 p_attribute15 => p_attribute15,
807 p_attribute16 => p_attribute16,
808 p_attribute17 => p_attribute17,
809 p_attribute18 => p_attribute18,
810 p_attribute19 => p_attribute19,
811 p_attribute20 => p_attribute20,
812 p_system_type => p_system_type ,
813 p_system_params => p_system_params,
814 p_appraisee_access => p_appraisee_access ,
815 p_main_appraiser_id => p_main_appraiser_id ,
816 p_assignment_id => p_assignment_id ,
817 p_assignment_start_date => p_assignment_start_date ,
818 p_asg_business_group_id => p_asg_business_group_id ,
819 p_assignment_organization_id => p_assignment_organization_id ,
820 p_assignment_job_id => p_assignment_job_id ,
821 p_assignment_position_id => p_assignment_position_id ,
822 p_assignment_grade_id => p_assignment_grade_id,
823 p_appraisal_system_status => p_appraisal_system_status,
824 p_potential_readiness_level => p_potential_readiness_level,
825 p_potential_short_term_workopp => p_potential_short_term_workopp,
826 p_potential_long_term_workopp => p_potential_long_term_workopp,
827 p_potential_details => p_potential_details,
828 p_event_id => p_event_id,
829 p_show_competency_ratings => p_show_competency_ratings,
830 p_show_objective_ratings => p_show_objective_ratings,
831 p_show_questionnaire_info => p_show_questionnaire_info,
832 p_show_participant_details => p_show_participant_details,
833 p_show_participant_ratings => p_show_participant_ratings,
834 p_show_participant_names => p_show_participant_names,
835 p_show_overall_ratings => p_show_overall_ratings,
836 p_show_overall_comments => p_show_overall_comments,
837 p_update_appraisal => p_update_appraisal,
838 p_provide_overall_feedback => p_provide_overall_feedback,
839 p_appraisee_comments => p_appraisee_comments,
840 p_plan_id => p_plan_id,
841 p_offline_status => p_offline_status,
842 p_retention_potential => p_retention_potential,
843 p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
844 );
845 exception
846 when hr_api.cannot_find_prog_unit then
847 hr_api.cannot_find_prog_unit_error
848 (p_module_name => 'update_appraisal',
849 p_hook_type => 'BP'
850 );
851 end;
852 --
853 -- End of Before Process User Hook call
854 --
855 -- Validation in addition to Table Handlers
856 --
857 hr_utility.set_location(l_proc, 7);
858 --
859 -- Process Logic
860 --
861 open get_appraisee_person_id(p_appraisal_id);
862 fetch get_appraisee_person_id into l_appraisee_person_id;
863 close get_appraisee_person_id;
864 l_object_version_number := p_object_version_number;
865
866
867 -- Get Old MA Id and check if MainAppraiser has been switched and based on
868 -- that we check if there is a Participant for MA and we change the type. For
869 -- the new MA check if he is a Participant and if Yes change his Type to MA
870
871
872 l_old_mainap_id := get_old_main_appraiser_id (p_appraisal_id);
873 l_appraiser_person_id := p_appraiser_person_id;
874
875
876 hr_utility.set_location('l_appraisee_person_id:'|| l_appraisee_person_id, 5);
877 hr_utility.set_location('l_old_mainap_id:'|| l_old_mainap_id, 5);
878 hr_utility.set_location('l_appraiser_person_id:'|| l_appraiser_person_id, 5);
879 hr_utility.set_location('l_appraisal_sys_status:'|| l_appraisal_sys_status, 5);
880 hr_utility.set_location('p_main_appraiser_id:'|| p_main_appraiser_id, 5);
881
882
883 IF l_old_mainap_id IS NOT NULL
884 AND p_main_appraiser_id <> hr_api.g_number THEN
885 IF l_old_mainap_id <> p_main_appraiser_id THEN
886 IF (l_appraisal_sys_status IN ('PLANNED','SAVED')
887 AND p_appraiser_person_id <> l_appraisee_person_id) THEN
888 l_appraiser_person_id := p_main_appraiser_id;
889 hr_utility.set_location('l_appraiser_person_id:'|| l_appraiser_person_id, 50);
890 END IF;
891
892 Get_Participant_id(p_appraisal_id, l_old_mainap_id, l_participant_id_1, l_part_object_version_number_1);
893 -- if old MA is a Participant change his Type
894 if l_participant_id_1 IS NOT NULL THEN
895 hr_participants_api.update_participant(p_validate => p_validate,
896 p_effective_date => p_effective_date,
897 p_participant_id => l_participant_id_1,
898 p_object_version_number => l_part_object_version_number_1,
899 p_participation_type => 'GROUPAPPRAISER'
900 );
901 end if;
902 Get_Participant_id(p_appraisal_id, p_main_appraiser_id, l_participant_id_2, l_part_object_version_number_2);
903 if l_participant_id_2 IS NOT NULL THEN
904 hr_participants_api.update_participant(p_validate => p_validate,
905 p_effective_date => p_effective_date,
906 p_participant_id => l_participant_id_2,
907 p_object_version_number => l_part_object_version_number_2,
908 p_participation_type => 'MAINAP'
909 );
910 end if;
911 end if;
912 end if;
913
914 -- in case of appraisal delete or reject we are going to change the
915 -- Learning Path Status to Cancelled.
916 hr_util_misc_ss.check_ota_installed(810, l_ota_installed);
917 if(l_ota_installed = 'Y') then
918 update_learning_path(p_appraisal_id, p_appraisal_system_status);
919 end if;
920 --
921 per_apr_upd.upd
922 (p_validate => p_validate,
923 p_effective_date => p_effective_date,
924 p_appraisal_id => p_appraisal_id,
925 p_object_version_number => l_object_version_number,
926 p_appraiser_person_id => l_appraiser_person_id,
927 p_appraisal_date => p_appraisal_date,
928 p_appraisal_period_start_date => p_appraisal_period_start_date,
929 p_appraisal_period_end_date => p_appraisal_period_end_date,
930 p_type => p_type,
931 p_next_appraisal_date => p_next_appraisal_date,
932 p_status => p_status,
933 p_comments => p_comments,
934 p_overall_performance_level_id => p_overall_performance_level_id,
935 p_open => p_open,
936 p_attribute_category => p_attribute_category,
937 p_attribute1 => p_attribute1,
938 p_attribute2 => p_attribute2,
939 p_attribute3 => p_attribute3,
940 p_attribute4 => p_attribute4,
941 p_attribute5 => p_attribute5,
942 p_attribute6 => p_attribute6,
943 p_attribute7 => p_attribute7,
944 p_attribute8 => p_attribute8,
945 p_attribute9 => p_attribute9,
946 p_attribute10 => p_attribute10,
947 p_attribute11 => p_attribute11,
948 p_attribute12 => p_attribute12,
949 p_attribute13 => p_attribute13,
950 p_attribute14 => p_attribute14,
951 p_attribute15 => p_attribute15,
952 p_attribute16 => p_attribute16,
953 p_attribute17 => p_attribute17,
954 p_attribute18 => p_attribute18,
955 p_attribute19 => p_attribute19,
956 p_attribute20 => p_attribute20,
957 p_system_type => p_system_type ,
958 p_system_params => p_system_params,
959 p_appraisee_access => p_appraisee_access ,
960 p_main_appraiser_id => p_main_appraiser_id,
961 p_assignment_id => p_assignment_id ,
962 p_assignment_start_date => p_assignment_start_date ,
963 p_asg_business_group_id => p_asg_business_group_id ,
964 p_assignment_organization_id => p_assignment_organization_id ,
965 p_assignment_job_id => p_assignment_job_id ,
966 p_assignment_position_id => p_assignment_position_id ,
967 p_assignment_grade_id => p_assignment_grade_id,
968 p_appraisal_system_status => p_appraisal_system_status,
969 p_potential_readiness_level => p_potential_readiness_level,
970 p_potential_short_term_workopp => p_potential_short_term_workopp,
971 p_potential_long_term_workopp => p_potential_long_term_workopp,
972 p_potential_details => p_potential_details,
973 p_event_id => p_event_id,
974 p_show_competency_ratings => p_show_competency_ratings,
975 p_show_objective_ratings => p_show_objective_ratings,
976 p_show_questionnaire_info => p_show_questionnaire_info,
977 p_show_participant_details => p_show_participant_details,
978 p_show_participant_ratings => p_show_participant_ratings,
979 p_show_participant_names => p_show_participant_names,
980 p_show_overall_ratings => p_show_overall_ratings,
981 p_show_overall_comments => p_show_overall_comments,
982 p_update_appraisal => p_update_appraisal,
983 p_provide_overall_feedback => p_provide_overall_feedback,
984 p_appraisee_comments => p_appraisee_comments,
985 p_plan_id => p_plan_id,
986 p_offline_status => p_offline_status,
987 p_retention_potential => p_retention_potential,
988 p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
989 );
990 --
991 --
992 hr_utility.set_location(l_proc, 8);
993 --
994 -- Update the assessment if necessary (as the appraisal_period_start_date and
995 -- appraisal_period_end_date may have changed which would affect an assessment
996 -- if there is one attached to this appraisal)
997 --
998 IF Appraisal_Period_Date_Changed (p_appraisal_id => p_appraisal_id
999 ,p_appraisal_period_start_date => p_appraisal_period_start_date
1000 ,p_appraisal_period_end_date => p_appraisal_period_end_date
1001 ,p_appraisal_date => p_appraisal_date) THEN
1002 --
1003 -- [the above IF can be carried out here as the db dates that are checked are from the assessment]
1004 --
1005 --
1006 Get_Assessment_Details(p_appraisal_id, l_assessment_id, l_asn_object_version_number);
1007 --
1008 hr_assessments_api.update_assessment ( p_assessment_id => l_assessment_id
1009 , p_object_version_number => l_asn_object_version_number
1010 , p_assessment_period_start_date => p_appraisal_period_start_date
1011 , p_assessment_period_end_date => p_appraisal_period_end_date
1012 , p_assessment_date => p_appraisal_date
1013 , p_validate => p_validate
1014 , p_effective_date => p_effective_date);
1015 END IF;
1016
1017 --
1018 -- Call After Process User Hook
1019 --
1020 begin
1021 hr_appraisals_bk2.update_appraisal_a (
1022 p_effective_date => p_effective_date,
1023 p_appraisal_id => p_appraisal_id,
1024 p_object_version_number => l_object_version_number,
1025 p_appraiser_person_id => p_appraiser_person_id,
1026 p_appraisal_date => p_appraisal_date,
1027 p_appraisal_period_start_date => p_appraisal_period_start_date,
1028 p_appraisal_period_end_date => p_appraisal_period_end_date,
1029 p_type => p_type,
1030 p_next_appraisal_date => p_next_appraisal_date,
1031 p_status => p_status,
1032 p_comments => p_comments,
1033 p_overall_performance_level_id => p_overall_performance_level_id,
1034 p_open => p_open,
1035 p_attribute_category => p_attribute_category,
1036 p_attribute1 => p_attribute1,
1037 p_attribute2 => p_attribute2,
1038 p_attribute3 => p_attribute3,
1039 p_attribute4 => p_attribute4,
1040 p_attribute5 => p_attribute5,
1041 p_attribute6 => p_attribute6,
1042 p_attribute7 => p_attribute7,
1043 p_attribute8 => p_attribute8,
1044 p_attribute9 => p_attribute9,
1045 p_attribute10 => p_attribute10,
1046 p_attribute11 => p_attribute11,
1047 p_attribute12 => p_attribute12,
1048 p_attribute13 => p_attribute13,
1049 p_attribute14 => p_attribute14,
1050 p_attribute15 => p_attribute15,
1051 p_attribute16 => p_attribute16,
1052 p_attribute17 => p_attribute17,
1053 p_attribute18 => p_attribute18,
1054 p_attribute19 => p_attribute19,
1055 p_attribute20 => p_attribute20,
1056 p_system_type => p_system_type ,
1057 p_system_params => p_system_params,
1058 p_appraisee_access => p_appraisee_access ,
1059 p_main_appraiser_id => p_main_appraiser_id ,
1060 p_assignment_id => p_assignment_id ,
1061 p_assignment_start_date => p_assignment_start_date ,
1062 p_asg_business_group_id => p_asg_business_group_id ,
1063 p_assignment_organization_id => p_assignment_organization_id ,
1064 p_assignment_job_id => p_assignment_job_id ,
1065 p_assignment_position_id => p_assignment_position_id ,
1066 p_assignment_grade_id => p_assignment_grade_id,
1067 p_appraisal_system_status => p_appraisal_system_status,
1068 p_potential_readiness_level => p_potential_readiness_level,
1069 p_potential_short_term_workopp => p_potential_short_term_workopp,
1070 p_potential_long_term_workopp => p_potential_long_term_workopp,
1071 p_potential_details => p_potential_details,
1072 p_event_id => p_event_id,
1073 p_show_competency_ratings => p_show_competency_ratings,
1074 p_show_objective_ratings => p_show_objective_ratings,
1075 p_show_questionnaire_info => p_show_questionnaire_info,
1076 p_show_participant_details => p_show_participant_details,
1077 p_show_participant_ratings => p_show_participant_ratings,
1078 p_show_participant_names => p_show_participant_names,
1079 p_show_overall_ratings => p_show_overall_ratings,
1080 p_show_overall_comments => p_show_overall_comments,
1081 p_update_appraisal => p_update_appraisal,
1082 p_provide_overall_feedback => p_provide_overall_feedback,
1083 p_appraisee_comments => p_appraisee_comments,
1084 p_plan_id => p_plan_id,
1085 p_offline_status => p_offline_status,
1086 p_retention_potential => p_retention_potential,
1087 p_show_participant_comments => p_show_participant_comments -- 8651478 bug fix
1088 );
1089 exception
1090 when hr_api.cannot_find_prog_unit then
1091 hr_api.cannot_find_prog_unit_error
1092 (p_module_name => 'update_appraisal',
1093 p_hook_type => 'AP'
1094 );
1095 end;
1096 --
1097 -- End After Process User Hook
1098 --
1099 --
1100 -- When in validation only mode raise the Validate_Enabled exception
1101 --
1102 if p_validate then
1103 raise hr_api.validate_enabled;
1104 end if;
1105 --
1106 -- Set all output arguments. l_object_version_number now has the new
1107 -- object version number as the update was successful
1108 --
1109 p_object_version_number := l_object_version_number;
1110 --
1111 hr_utility.set_location(' Leaving:'||l_proc, 11);
1112 exception
1113 when hr_api.validate_enabled then
1114 --
1115 -- As the Validate_Enabled exception has been raised
1116 -- we must rollback to the savepoint
1117 --
1118 ROLLBACK TO update_appraisal;
1119 --
1120 -- Only set output warning arguments and in out arguments back
1121 -- to their IN value
1122 -- (Any key or derived arguments must be set to null
1123 -- when validation only mode is being used.)
1124 --
1125 p_object_version_number := l_object_version_number;
1126 --
1127 when others then
1128 --
1129 p_object_version_number := lv_object_version_number ;
1130
1131 -- A validation or unexpected error has occurred
1132 --
1133 -- Added as part of fix to bug 632482
1134 --
1135 ROLLBACK TO update_appraisal;
1136 raise;
1137 --
1138 -- End of fix.
1139 --
1140 hr_utility.set_location(' Leaving:'||l_proc, 12);
1141 --
1142 end update_appraisal;
1143 --
1144 --
1145 -- ---------------------------------------------------------------------------
1146 -- |-----------------------< <delete_appraisal> >--------------------------|
1147 -- ---------------------------------------------------------------------------
1148 --
1149 procedure delete_appraisal
1150 (p_validate in boolean default false,
1151 p_appraisal_id in number,
1152 p_object_version_number in number
1153 ) is
1154 --
1155 -- Declare cursors and local variables
1156 --
1157 --
1158 cursor c_quest_ans_id
1159 is
1160 select questionnaire_answer_id
1161 from hr_quest_answers
1162 where hr_quest_answers.type = 'APPRAISAL' and
1163 hr_quest_answers.type_object_id = p_appraisal_id; -- Fix for Bug No.1386826
1164
1165 --
1166 --
1167 cursor cs_get_participants is
1168 select participant_id,object_version_number from per_participants
1169 where participation_in_id = p_appraisal_id
1170 and participation_in_table = 'PER_APPRAISALS'
1171 and participation_in_column = 'APPRAISAL_ID';
1172 --
1173
1174 cursor cs_get_objectives is
1175 select objective_id ,object_version_number from per_objectives
1176 where appraisal_id = p_appraisal_id;
1177 --
1178 cursor cs_get_assessments is
1179 select assessment_id ,object_version_number from per_assessments
1180 where appraisal_id = p_appraisal_id;
1181 --
1182 cursor cs_get_perf_ratings is
1183 select performance_rating_id ,object_version_number from per_performance_ratings
1184 where appraisal_id = p_appraisal_id;
1185
1186 --
1187 cursor cs_get_comp_elmnt_rec is
1188 select competence_element_id ,object_version_number
1189 from per_competence_elements
1190 where assessment_id in
1191 (select assessment_id from per_assessments where appraisal_id = p_appraisal_id);
1192
1193 cursor cs_get_perf_review_rec is
1194 select performance_review_id, object_version_number
1195 from per_performance_reviews
1196 where event_id in (select event_id from per_appraisals where appraisal_id = p_appraisal_id);
1197
1198 --
1199 cursor cs_get_per_events_rec is
1200 select event_id, object_version_number
1201 from per_events
1202 where event_id in (select event_id from per_appraisals where appraisal_id = p_appraisal_id);
1203
1204 --
1205 /*
1206 --
1207 cursor cs_get_apr_quest_answers_rec is
1208 select quest_answer_val_id from
1209 hr_quest_answer_values where questionnaire_answer_id in
1210 (select questionnaire_answer_id from hr_quest_answers
1211 where type = 'APPRAISAL' and type_object_id=p_appraisal_id );
1212 --
1213 cursor cs_get_part_quest_answers_rec is
1214 select quest_answer_val_id from
1215 hr_quest_answer_values where questionnaire_answer_id in
1216 (select questionnaire_answer_id from hr_quest_answers
1217 where type = 'PARTICIPANT' and type_object_id in
1218 (select participant_id from per_participants where
1219 participation_in_table='PER_APPRAISALS' and participation_in_column='APPRAISAL_ID' and
1220 participation_in_id=p_appraisal_id
1221 )
1222 );
1223
1224 */
1225
1226 --
1227
1228 l_quest_ans_id hr_quest_answers.questionnaire_answer_id%type;
1229 --
1230 l_proc varchar2(72) := g_package||'delete_appraisal';
1231 l_person_id NUMBER(9,0);
1232 l_training_plan_member_id NUMBER(9,0);
1233 l_ota_error_num NUMBER;
1234 l_ota_installed VARCHAR2(10);
1235
1236 begin
1237 hr_utility.set_location('Entering:'|| l_proc, 5);
1238 --
1239 -- Issue a savepoint.
1240 --
1241 savepoint delete_appraisal;
1242 hr_utility.set_location(l_proc, 6);
1243 --
1244 --
1245 -- Call Before Process User Hook
1246 --
1247 begin
1248 hr_appraisals_bk3.delete_appraisal_b
1249 (
1250 p_appraisal_id => p_appraisal_id,
1251 p_object_version_number => p_object_version_number
1252 );
1253 exception
1254 when hr_api.cannot_find_prog_unit then
1255 hr_api.cannot_find_prog_unit_error
1256 (p_module_name => 'delete_appraisal',
1257 p_hook_type => 'BP'
1258 );
1259 end;
1260 --
1261 -- End of before process hook
1262 --
1263 -- Validation in addition to Table Handlers
1264 --
1265 hr_utility.set_location(l_proc, 7);
1266 --
1267 -- Process Logic
1268 --
1269
1270 -- To delete Appraisal , Participants, Assessments, Comp Elements, Objectives, Perf Ratings
1271
1272 -- Fix for bug no. 1386826 begins.
1273 --
1274 -- Deleting the related child records in hr_quest_answers and hr_quest_answer_values
1275 -- tables if the entry in "VALUE" column of hr_quest_answer_values has null entry.
1276 --
1277 open c_quest_ans_id;
1278 fetch c_quest_ans_id into l_quest_ans_id;
1279 if c_quest_ans_id%found then
1280
1281 -- deleting answers and answer values.
1282 hr_quest_perform_web.delete_quest_answer(p_questionnaire_answer_id => l_quest_ans_id);
1283 end if;
1284 close c_quest_ans_id;
1285 --
1286
1287 -- Fix for bug no. 1386826 ends.
1288 --
1289
1290
1291 /*
1292 Order of delete
1293
1294 a) delete_competence_elements based on assessment_id
1295 b) delete_assessment
1296 c) delete_performance_ratings
1297 d) delete_objectives
1298 e) delete_appraisal_quest_answer_values -
1299 f) delete_appraisal_quest_answer -
1300 g) delete_participant_quest_answer_values
1301 - Done internally, after calling hr_quest_perform_web.delete_quest_answer
1302 h) delete_participant_quest_answers
1303 - Done in Participants API
1304 i) delete_participants
1305 j) delete_ota_training_plan_members
1306 - this is under review as to how to be deleted . so nothing for this now
1307 k) hr_perf_review_api.delete_perf_review
1308 (we create an entry in per_performance_reviews table and this has the column
1309 Event_Id which will be same as Event_Id in Per_Appraisals table)
1310 l) per_events_api.delete_event
1311 (we added an Event_id in Per_Appraisals, using that event_id we go here and delete it)
1312 m) delete_appraisal finally .
1313
1314 */
1315
1316
1317 FOR comp_elmnt_rec in cs_get_comp_elmnt_rec LOOP
1318 hr_competence_element_api.delete_competence_element
1319 (
1320 p_validate => p_validate
1321 ,p_competence_element_id => comp_elmnt_rec.competence_element_id
1322 ,p_object_version_number => comp_elmnt_rec.object_version_number
1323 );
1324
1325 END LOOP;
1326
1327 --
1328 FOR assessments_rec in cs_get_assessments LOOP
1329 hr_assessments_api.delete_assessment
1330 (p_validate => p_validate
1331 ,p_assessment_id => assessments_rec.assessment_id
1332 ,p_object_version_number => assessments_rec.object_version_number
1333 );
1334
1335 END LOOP;
1336 --
1337 FOR perf_rating_rec in cs_get_perf_ratings LOOP
1338 hr_performance_ratings_api.delete_performance_rating
1339 (p_validate => p_validate
1340 ,p_performance_rating_id => perf_rating_rec.performance_rating_id
1341 ,p_object_version_number => perf_rating_rec.object_version_number
1342 );
1343
1344 END LOOP;
1345 --
1346 FOR objectives_rec in cs_get_objectives LOOP
1347 hr_objectives_api.delete_objective
1348 (p_validate => p_validate
1349 ,p_objective_id => objectives_rec.objective_id
1350 ,p_object_version_number => objectives_rec.object_version_number
1351 );
1352
1353 END LOOP;
1354 --
1355 FOR participants_rec in cs_get_participants LOOP
1356 hr_participants_api.delete_participant
1357 (p_validate => p_validate
1358 ,p_participant_id => participants_rec.participant_id
1359 ,p_object_version_number => participants_rec.object_version_number
1360 );
1361
1362 END LOOP;
1363
1364 --
1365 -- in case of appraisal delete or reject we are going to change the
1366 -- Learning Path Status to Cancelled.
1367 hr_util_misc_ss.check_ota_installed(810, l_ota_installed);
1368 if(l_ota_installed = 'Y') then
1369 update_learning_path(p_appraisal_id, 'DELETED');
1370 end if;
1371
1372
1373 FOR perf_review_rec in cs_get_perf_review_rec LOOP
1374 hr_perf_review_api.delete_perf_review
1375 (
1376 p_validate => p_validate
1377 ,p_performance_review_id => perf_review_rec.performance_review_id
1378 ,p_object_version_number => perf_review_rec.object_version_number
1379 );
1380 END LOOP;
1381
1382 --
1383 FOR per_events_rec in cs_get_per_events_rec LOOP
1384 per_events_api.delete_event
1385 (
1386 p_validate => p_validate
1387 ,p_event_id => per_events_rec.event_id
1388 ,p_object_version_number => per_events_rec.object_version_number
1389 );
1390 END LOOP;
1391 --
1392
1393
1394 -- now delete the appraisal itself
1395 --
1396 per_apr_del.del
1397 (p_validate => FALSE
1398 ,p_appraisal_id => p_appraisal_id
1399 ,p_object_version_number => p_object_version_number
1400 );
1401 --
1402 hr_utility.set_location(l_proc, 8);
1403 --
1404 -- Call After Process User Hook
1405 --
1406 begin
1407 hr_appraisals_bk3.delete_appraisal_a (
1408 p_appraisal_id => p_appraisal_id,
1409 p_object_version_number => p_object_version_number
1410 );
1411 exception
1412 when hr_api.cannot_find_prog_unit then
1413 hr_api.cannot_find_prog_unit_error
1414 (p_module_name => 'delete_appraisal',
1415 p_hook_type => 'AP'
1416 );
1417 end;
1418 --
1419 -- End of After Process User hook
1420 --
1421 -- When in validation only mode raise the Validate_Enabled exception
1422 --
1423 if p_validate then
1424 raise hr_api.validate_enabled;
1425 end if;
1426 --
1427 hr_utility.set_location(' Leaving:'||l_proc, 11);
1428 exception
1429 when hr_api.validate_enabled then
1430 --
1431 -- As the Validate_Enabled exception has been raised
1432 -- we must rollback to the savepoint
1433 --
1434 ROLLBACK TO delete_appraisal;
1435 --
1436 --
1437 when others then
1438 --
1439 -- A validation or unexpected error has occurred
1440 --
1441 -- Added as part of fix to bug 632482
1442 --
1443 ROLLBACK TO delete_appraisal;
1444 raise;
1445 --
1446 -- End of fix.
1447 --
1448 hr_utility.set_location(' Leaving:'||l_proc, 12);
1449 end delete_appraisal;
1450 --
1451 end hr_appraisals_api;