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