[Home] [Help]
PACKAGE BODY: APPS.HR_APPRAISALS_SWI
Source
1 Package Body hr_appraisals_swi As
2 /* $Header: peaprswi.pkb 120.1 2007/06/20 07:45:24 rapandi ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'hr_appraisals_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< create_appraisal >---------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_appraisal
12 (p_validate in number default hr_api.g_false_num
13 ,p_effective_date in date
14 ,p_business_group_id in number
15 ,p_appraisal_template_id in number
16 ,p_appraisee_person_id in number
17 ,p_appraiser_person_id in number
18 ,p_appraisal_date in date default null
19 ,p_appraisal_period_start_date in date
20 ,p_appraisal_period_end_date in date
21 ,p_type in varchar2 default null
22 ,p_next_appraisal_date in date default null
23 ,p_status in varchar2 default null
24 ,p_group_date in date default null
25 ,p_group_initiator_id in number default null
26 ,p_comments in varchar2 default null
27 ,p_overall_performance_level_id in number default null
28 ,p_open in varchar2 default null
29 ,p_attribute_category in varchar2 default null
30 ,p_attribute1 in varchar2 default null
31 ,p_attribute2 in varchar2 default null
32 ,p_attribute3 in varchar2 default null
33 ,p_attribute4 in varchar2 default null
34 ,p_attribute5 in varchar2 default null
35 ,p_attribute6 in varchar2 default null
36 ,p_attribute7 in varchar2 default null
37 ,p_attribute8 in varchar2 default null
38 ,p_attribute9 in varchar2 default null
39 ,p_attribute10 in varchar2 default null
40 ,p_attribute11 in varchar2 default null
41 ,p_attribute12 in varchar2 default null
42 ,p_attribute13 in varchar2 default null
43 ,p_attribute14 in varchar2 default null
44 ,p_attribute15 in varchar2 default null
45 ,p_attribute16 in varchar2 default null
46 ,p_attribute17 in varchar2 default null
47 ,p_attribute18 in varchar2 default null
48 ,p_attribute19 in varchar2 default null
49 ,p_attribute20 in varchar2 default null
50 ,p_system_type in varchar2 default null
51 ,p_system_params in varchar2 default null
52 ,p_appraisee_access in varchar2 default null
53 ,p_main_appraiser_id in number default null
54 ,p_assignment_id in number default null
55 ,p_assignment_start_date in date default null
56 ,p_asg_business_group_id in number default null
57 ,p_assignment_organization_id in number default null
58 ,p_assignment_job_id in number default null
59 ,p_assignment_position_id in number default null
60 ,p_assignment_grade_id in number default null
61 ,p_appraisal_id in number
62 ,p_object_version_number out nocopy number
63 ,p_return_status out nocopy varchar2
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_offline_status in varchar2 default null
82 ) is
83 --
84 -- Variables for API Boolean parameters
85 l_validate boolean;
86 l_appraisal_id number;
87 --
88 -- Variables for IN/OUT parameters
89 --
90 -- Other variables
91 l_proc varchar2(72) := g_package ||'create_appraisal';
92 Begin
93 hr_utility.set_location(' Entering:' || l_proc,10);
94 --
95 -- Issue a savepoint
96 --
97 savepoint create_appraisal_swi;
98 --
99 -- Initialise Multiple Message Detection
100 --
101 hr_multi_message.enable_message_list;
102 --
103 -- Remember IN OUT parameter IN values
104 --
105 --
106 -- Convert constant values to their corresponding boolean value
107 --
108 l_validate :=
109 hr_api.constant_to_boolean
110 (p_constant_value => p_validate);
111 --
112 -- Register Surrogate ID or user key values
113 --
114 per_apr_ins.set_base_key_value
115 (p_appraisal_id => p_appraisal_id
116 );
117
118 --
119 -- Call API
120 --
121 hr_appraisals_api.create_appraisal
122 (p_validate => l_validate
123 ,p_effective_date => p_effective_date
124 ,p_business_group_id => p_business_group_id
125 ,p_appraisal_template_id => p_appraisal_template_id
126 ,p_appraisee_person_id => p_appraisee_person_id
127 ,p_appraiser_person_id => p_appraiser_person_id
128 ,p_appraisal_date => p_appraisal_date
129 ,p_appraisal_period_start_date => p_appraisal_period_start_date
130 ,p_appraisal_period_end_date => p_appraisal_period_end_date
131 ,p_type => p_type
132 ,p_next_appraisal_date => p_next_appraisal_date
133 ,p_status => p_status
134 ,p_group_date => p_group_date
135 ,p_group_initiator_id => p_group_initiator_id
136 ,p_comments => p_comments
137 ,p_overall_performance_level_id => p_overall_performance_level_id
138 ,p_open => p_open
139 ,p_attribute_category => p_attribute_category
140 ,p_attribute1 => p_attribute1
141 ,p_attribute2 => p_attribute2
142 ,p_attribute3 => p_attribute3
143 ,p_attribute4 => p_attribute4
144 ,p_attribute5 => p_attribute5
145 ,p_attribute6 => p_attribute6
146 ,p_attribute7 => p_attribute7
147 ,p_attribute8 => p_attribute8
148 ,p_attribute9 => p_attribute9
149 ,p_attribute10 => p_attribute10
150 ,p_attribute11 => p_attribute11
151 ,p_attribute12 => p_attribute12
152 ,p_attribute13 => p_attribute13
153 ,p_attribute14 => p_attribute14
154 ,p_attribute15 => p_attribute15
155 ,p_attribute16 => p_attribute16
156 ,p_attribute17 => p_attribute17
157 ,p_attribute18 => p_attribute18
158 ,p_attribute19 => p_attribute19
159 ,p_attribute20 => p_attribute20
160 ,p_system_type => p_system_type
161 ,p_system_params => p_system_params
162 ,p_appraisee_access => p_appraisee_access
163 ,p_main_appraiser_id => p_main_appraiser_id
164 ,p_assignment_id => p_assignment_id
165 ,p_assignment_start_date => p_assignment_start_date
166 ,p_asg_business_group_id => p_asg_business_group_id
167 ,p_assignment_organization_id => p_assignment_organization_id
168 ,p_assignment_job_id => p_assignment_job_id
169 ,p_assignment_position_id => p_assignment_position_id
170 ,p_assignment_grade_id => p_assignment_grade_id
171 ,p_appraisal_id => l_appraisal_id
172 ,p_object_version_number => p_object_version_number
173 ,p_appraisal_system_status => p_appraisal_system_status
174 ,p_potential_readiness_level => p_potential_readiness_level
175 ,p_potential_short_term_workopp => p_potential_short_term_workopp
176 ,p_potential_long_term_workopp => p_potential_long_term_workopp
177 ,p_potential_details => p_potential_details
178 ,p_event_id => p_event_id
179 ,p_show_competency_ratings => p_show_competency_ratings
180 ,p_show_objective_ratings => p_show_objective_ratings
181 ,p_show_questionnaire_info => p_show_questionnaire_info
182 ,p_show_participant_details => p_show_participant_details
183 ,p_show_participant_ratings => p_show_participant_ratings
184 ,p_show_participant_names => p_show_participant_names
185 ,p_show_overall_ratings => p_show_overall_ratings
186 ,p_show_overall_comments => p_show_overall_comments
187 ,p_update_appraisal => p_update_appraisal
188 ,p_provide_overall_feedback => p_provide_overall_feedback
189 ,p_appraisee_comments => p_appraisee_comments
190 ,p_offline_status => p_offline_status
191 );
192 --
193 -- Convert API warning boolean parameter values to specific
194 -- messages and add them to Multiple Message List
195 --
196 --
197 -- Convert API non-warning boolean parameter values
198 --
199 --
200 -- Derive the API return status value based on whether
201 -- messages of any type exist in the Multiple Message List.
202 -- Also disable Multiple Message Detection.
203 --
204 p_return_status := hr_multi_message.get_return_status_disable;
205 hr_utility.set_location(' Leaving:' || l_proc,20);
206 --
207 exception
208 when hr_multi_message.error_message_exist then
209 --
210 -- Catch the Multiple Message List exception which
211 -- indicates API processing has been aborted because
212 -- at least one message exists in the list.
213 --
214 rollback to create_appraisal_swi;
215 --
216 -- Reset IN OUT parameters and set OUT parameters
217 --
218 p_object_version_number := null;
219 p_return_status := hr_multi_message.get_return_status_disable;
220 hr_utility.set_location(' Leaving:' || l_proc, 30);
221 when others then
222 --
223 -- When Multiple Message Detection is enabled catch
224 -- any Application specific or other unexpected
225 -- exceptions. Adding appropriate details to the
226 -- Multiple Message List. Otherwise re-raise the
227 -- error.
228 --
229 rollback to create_appraisal_swi;
230 if hr_multi_message.unexpected_error_add(l_proc) then
231 hr_utility.set_location(' Leaving:' || l_proc,40);
232 raise;
233 end if;
234 --
235 -- Reset IN OUT and set OUT parameters
236 --
237 p_object_version_number := null;
238 p_return_status := hr_multi_message.get_return_status_disable;
239 hr_utility.set_location(' Leaving:' || l_proc,50);
240 end create_appraisal;
241 -- ----------------------------------------------------------------------------
242 -- |---------------------------< delete_appraisal >---------------------------|
243 -- ----------------------------------------------------------------------------
244 PROCEDURE delete_appraisal
245 (p_validate in number default hr_api.g_false_num
246 ,p_appraisal_id in number
247 ,p_object_version_number in number
248 ,p_return_status out nocopy varchar2
249 ) is
250 --
251 -- Variables for API Boolean parameters
252 l_validate boolean;
253 --
254 -- Variables for IN/OUT parameters
255 --
256 -- Other variables
257 l_proc varchar2(72) := g_package ||'delete_appraisal';
258 l_apr_status per_appraisals.appraisal_system_status%TYPE;
259 l_del_mode boolean;
260 l_del_status varchar2(10);
261 l_ovn per_appraisals.appraisal_id%TYPE;
262
263 -- Cursor to get the appraisal_system_status
264
265 cursor cs_apr_status is
266 select appraisal_system_status
267 from per_appraisals
268 where appraisal_id= p_appraisal_id ;
269 --
270
271 Begin
272 hr_utility.set_location(' Entering:' || l_proc,10);
273 --
274 -- Issue a savepoint
275 --
276 savepoint delete_appraisal_swi;
277 --
278 -- Initialise Multiple Message Detection
279 --
280 hr_multi_message.enable_message_list;
281 --
282 -- Remember IN OUT parameter IN values
283 --
284 --
285 -- Convert constant values to their corresponding boolean value
286 --
287 l_validate :=
288 hr_api.constant_to_boolean
289 (p_constant_value => p_validate);
290
291
292 open cs_apr_status;
293
294 fetch cs_apr_status into l_apr_status;
295
296 IF cs_apr_status%NOTFOUND then
297 null; -- REVISIT Throw exception after closing the cursor.
298 END IF;
299
300 close cs_apr_status;
301
302 --
303
304 IF l_apr_status = 'PLANNED' OR l_apr_status = 'SAVED' THEN
305 l_del_mode := true;
306 ELSE
307 l_del_mode := false;
308 END IF;
309
310 -- If appraisal_system_status = PLANNED or SAVED , we actually delete the appraisal
311 -- For appraisals of other status, we have to update the appraisal record with
312 -- appraisal_system_status=DELETED
313
314 IF l_del_mode = true THEN
315
316 --
317 -- Register Surrogate ID or user key values
318 --
319
320 --
321 -- Call API
322 --
323 hr_appraisals_api.delete_appraisal
324 (p_validate => l_validate
325 ,p_appraisal_id => p_appraisal_id
326 ,p_object_version_number => p_object_version_number
327 );
328 --
329 -- Convert API warning boolean parameter values to specific
330 -- messages and add them to Multiple Message List
331 --
332 --
333 -- Convert API non-warning boolean parameter values
334 --
335 --
336 -- Derive the API return status value based on whether
337 -- messages of any type exist in the Multiple Message List.
338 -- Also disable Multiple Message Detection.
339 --
340 p_return_status := hr_multi_message.get_return_status_disable;
341
342 ELSE
343
344 -- To update appraisal record with appraisal_system_status=DELETED
345
346
347 l_ovn := p_object_version_number;
348
349 update_appraisal
350 (
351 p_validate => p_validate
352 ,p_effective_date => sysdate
353 ,p_appraisal_id => p_appraisal_id
354 ,p_object_version_number => l_ovn
355 ,p_appraisal_system_status => 'DELETED'
356 ,p_return_status => p_return_status
357 );
358
359 END IF; -- l_del_mode
360
361
362 hr_utility.set_location(' Leaving:' || l_proc,20);
363 --
364 exception
365 when hr_multi_message.error_message_exist then
366 --
367 -- Catch the Multiple Message List exception which
368 -- indicates API processing has been aborted because
369 -- at least one message exists in the list.
370 --
371 rollback to delete_appraisal_swi;
372 --
373 -- Reset IN OUT parameters and set OUT parameters
374 --
375 p_return_status := hr_multi_message.get_return_status_disable;
376 hr_utility.set_location(' Leaving:' || l_proc, 30);
377 when others then
378 --
379 -- When Multiple Message Detection is enabled catch
380 -- any Application specific or other unexpected
381 -- exceptions. Adding appropriate details to the
385 rollback to delete_appraisal_swi;
382 -- Multiple Message List. Otherwise re-raise the
383 -- error.
384 --
386 if hr_multi_message.unexpected_error_add(l_proc) then
387 hr_utility.set_location(' Leaving:' || l_proc,40);
388 raise;
389 end if;
390 --
391 -- Reset IN OUT and set OUT parameters
392 --
393 p_return_status := hr_multi_message.get_return_status_disable;
394 hr_utility.set_location(' Leaving:' || l_proc,50);
395 end delete_appraisal;
396 -- ----------------------------------------------------------------------------
397 -- |---------------------------< update_appraisal >---------------------------|
398 -- ----------------------------------------------------------------------------
399 PROCEDURE update_appraisal
400 (p_validate in number default hr_api.g_false_num
401 ,p_effective_date in date
402 ,p_appraisal_id in number
403 ,p_object_version_number in out nocopy number
404 ,p_appraiser_person_id in number default hr_api.g_number
405 ,p_appraisal_date in date default hr_api.g_date
406 ,p_appraisal_period_end_date in date default hr_api.g_date
407 ,p_appraisal_period_start_date in date default hr_api.g_date
408 ,p_type in varchar2 default hr_api.g_varchar2
409 ,p_next_appraisal_date in date default hr_api.g_date
410 ,p_status in varchar2 default hr_api.g_varchar2
411 ,p_comments in varchar2 default hr_api.g_varchar2
412 ,p_overall_performance_level_id in number default hr_api.g_number
413 ,p_open in varchar2 default hr_api.g_varchar2
414 ,p_attribute_category in varchar2 default hr_api.g_varchar2
415 ,p_attribute1 in varchar2 default hr_api.g_varchar2
416 ,p_attribute2 in varchar2 default hr_api.g_varchar2
417 ,p_attribute3 in varchar2 default hr_api.g_varchar2
418 ,p_attribute4 in varchar2 default hr_api.g_varchar2
419 ,p_attribute5 in varchar2 default hr_api.g_varchar2
420 ,p_attribute6 in varchar2 default hr_api.g_varchar2
421 ,p_attribute7 in varchar2 default hr_api.g_varchar2
422 ,p_attribute8 in varchar2 default hr_api.g_varchar2
423 ,p_attribute9 in varchar2 default hr_api.g_varchar2
424 ,p_attribute10 in varchar2 default hr_api.g_varchar2
425 ,p_attribute11 in varchar2 default hr_api.g_varchar2
426 ,p_attribute12 in varchar2 default hr_api.g_varchar2
427 ,p_attribute13 in varchar2 default hr_api.g_varchar2
428 ,p_attribute14 in varchar2 default hr_api.g_varchar2
429 ,p_attribute15 in varchar2 default hr_api.g_varchar2
430 ,p_attribute16 in varchar2 default hr_api.g_varchar2
431 ,p_attribute17 in varchar2 default hr_api.g_varchar2
432 ,p_attribute18 in varchar2 default hr_api.g_varchar2
433 ,p_attribute19 in varchar2 default hr_api.g_varchar2
434 ,p_attribute20 in varchar2 default hr_api.g_varchar2
435 ,p_system_type in varchar2 default hr_api.g_varchar2
436 ,p_system_params in varchar2 default hr_api.g_varchar2
437 ,p_appraisee_access in varchar2 default hr_api.g_varchar2
438 ,p_main_appraiser_id in number default hr_api.g_number
439 ,p_assignment_id in number default hr_api.g_number
440 ,p_assignment_start_date in date default hr_api.g_date
441 ,p_asg_business_group_id in number default hr_api.g_number
442 ,p_assignment_organization_id in number default hr_api.g_number
443 ,p_assignment_job_id in number default hr_api.g_number
444 ,p_assignment_position_id in number default hr_api.g_number
445 ,p_assignment_grade_id in number default hr_api.g_number
446 ,p_return_status out nocopy varchar2
447 ,p_appraisal_system_status in varchar2 default hr_api.g_varchar2
448 ,p_potential_readiness_level in varchar2 default hr_api.g_varchar2
449 ,p_potential_short_term_workopp in varchar2 default hr_api.g_varchar2
450 ,p_potential_long_term_workopp in varchar2 default hr_api.g_varchar2
451 ,p_potential_details in varchar2 default hr_api.g_varchar2
452 ,p_event_id in number default hr_api.g_number
453 ,p_show_competency_ratings in varchar2 default hr_api.g_varchar2
454 ,p_show_objective_ratings in varchar2 default hr_api.g_varchar2
455 ,p_show_questionnaire_info in varchar2 default hr_api.g_varchar2
456 ,p_show_participant_details in varchar2 default hr_api.g_varchar2
457 ,p_show_participant_ratings in varchar2 default hr_api.g_varchar2
458 ,p_show_participant_names in varchar2 default hr_api.g_varchar2
459 ,p_show_overall_ratings in varchar2 default hr_api.g_varchar2
460 ,p_show_overall_comments in varchar2 default hr_api.g_varchar2
461 ,p_update_appraisal in varchar2 default hr_api.g_varchar2
462 ,p_provide_overall_feedback in varchar2 default hr_api.g_varchar2
463 ,p_appraisee_comments in varchar2 default hr_api.g_varchar2
467 -- Variables for API Boolean parameters
464 ,p_offline_status in varchar2 default hr_api.g_varchar2
465 ) is
466 --
468 l_validate boolean;
469 --
470 -- Variables for IN/OUT parameters
471 l_object_version_number number;
472 --
473 -- Other variables
474 l_proc varchar2(72) := g_package ||'update_appraisal';
475 Begin
476 hr_utility.set_location(' Entering:' || l_proc,10);
477 --
478 -- Issue a savepoint
479 --
480 savepoint update_appraisal_swi;
481 --
482 -- Initialise Multiple Message Detection
483 --
484 hr_multi_message.enable_message_list;
485 --
486 -- Remember IN OUT parameter IN values
487 --
488 l_object_version_number := p_object_version_number;
489 --
490 -- Convert constant values to their corresponding boolean value
491 --
492 l_validate :=
493 hr_api.constant_to_boolean
494 (p_constant_value => p_validate);
495 --
496 -- Register Surrogate ID or user key values
497 --
498 --
499 -- Call API
500 --
501 hr_appraisals_api.update_appraisal
502 (p_validate => l_validate
503 ,p_effective_date => p_effective_date
504 ,p_appraisal_id => p_appraisal_id
505 ,p_object_version_number => p_object_version_number
506 ,p_appraiser_person_id => p_appraiser_person_id
507 ,p_appraisal_date => p_appraisal_date
508 ,p_appraisal_period_end_date => p_appraisal_period_end_date
509 ,p_appraisal_period_start_date => p_appraisal_period_start_date
510 ,p_type => p_type
511 ,p_next_appraisal_date => p_next_appraisal_date
512 ,p_status => p_status
513 ,p_comments => p_comments
514 ,p_overall_performance_level_id => p_overall_performance_level_id
515 ,p_open => p_open
516 ,p_attribute_category => p_attribute_category
517 ,p_attribute1 => p_attribute1
518 ,p_attribute2 => p_attribute2
519 ,p_attribute3 => p_attribute3
520 ,p_attribute4 => p_attribute4
521 ,p_attribute5 => p_attribute5
522 ,p_attribute6 => p_attribute6
523 ,p_attribute7 => p_attribute7
524 ,p_attribute8 => p_attribute8
525 ,p_attribute9 => p_attribute9
526 ,p_attribute10 => p_attribute10
527 ,p_attribute11 => p_attribute11
528 ,p_attribute12 => p_attribute12
529 ,p_attribute13 => p_attribute13
530 ,p_attribute14 => p_attribute14
531 ,p_attribute15 => p_attribute15
532 ,p_attribute16 => p_attribute16
533 ,p_attribute17 => p_attribute17
534 ,p_attribute18 => p_attribute18
535 ,p_attribute19 => p_attribute19
536 ,p_attribute20 => p_attribute20
537 ,p_system_type => p_system_type
538 ,p_system_params => p_system_params
539 ,p_appraisee_access => p_appraisee_access
540 ,p_main_appraiser_id => p_main_appraiser_id
541 ,p_assignment_id => p_assignment_id
542 ,p_assignment_start_date => p_assignment_start_date
543 ,p_asg_business_group_id => p_asg_business_group_id
544 ,p_assignment_organization_id => p_assignment_organization_id
545 ,p_assignment_job_id => p_assignment_job_id
546 ,p_assignment_position_id => p_assignment_position_id
547 ,p_assignment_grade_id => p_assignment_grade_id
548 ,p_appraisal_system_status => p_appraisal_system_status
549 ,p_potential_readiness_level => p_potential_readiness_level
550 ,p_potential_short_term_workopp => p_potential_short_term_workopp
551 ,p_potential_long_term_workopp => p_potential_long_term_workopp
552 ,p_potential_details => p_potential_details
553 ,p_event_id => p_event_id
554 ,p_show_competency_ratings => p_show_competency_ratings
555 ,p_show_objective_ratings => p_show_objective_ratings
556 ,p_show_questionnaire_info => p_show_questionnaire_info
557 ,p_show_participant_details => p_show_participant_details
558 ,p_show_participant_ratings => p_show_participant_ratings
559 ,p_show_participant_names => p_show_participant_names
560 ,p_show_overall_ratings => p_show_overall_ratings
561 ,p_show_overall_comments => p_show_overall_comments
562 ,p_update_appraisal => p_update_appraisal
563 ,p_provide_overall_feedback => p_provide_overall_feedback
564 ,p_appraisee_comments => p_appraisee_comments
565 ,p_offline_status => p_offline_status
566
567 );
568 --
569 -- Convert API warning boolean parameter values to specific
570 -- messages and add them to Multiple Message List
571 --
572 --
573 -- Convert API non-warning boolean parameter values
574 --
575 --
576 -- Derive the API return status value based on whether
577 -- messages of any type exist in the Multiple Message List.
578 -- Also disable Multiple Message Detection.
579 --
583 exception
580 p_return_status := hr_multi_message.get_return_status_disable;
581 hr_utility.set_location(' Leaving:' || l_proc,20);
582 --
584 when hr_multi_message.error_message_exist then
585 --
586 -- Catch the Multiple Message List exception which
587 -- indicates API processing has been aborted because
588 -- at least one message exists in the list.
589 --
590 rollback to update_appraisal_swi;
591 --
592 -- Reset IN OUT parameters and set OUT parameters
593 --
594 p_object_version_number := l_object_version_number;
595 p_return_status := hr_multi_message.get_return_status_disable;
596 hr_utility.set_location(' Leaving:' || l_proc, 30);
597 when others then
598 --
599 -- When Multiple Message Detection is enabled catch
600 -- any Application specific or other unexpected
601 -- exceptions. Adding appropriate details to the
602 -- Multiple Message List. Otherwise re-raise the
603 -- error.
604 --
605 rollback to update_appraisal_swi;
606 if hr_multi_message.unexpected_error_add(l_proc) then
607 hr_utility.set_location(' Leaving:' || l_proc,40);
608 raise;
609 end if;
610 --
611 -- Reset IN OUT and set OUT parameters
612 --
613 p_object_version_number := l_object_version_number;
614 p_return_status := hr_multi_message.get_return_status_disable;
615 hr_utility.set_location(' Leaving:' || l_proc,50);
616 end update_appraisal;
617
618 -- ----------------------------------------------------------------------------
619 -- |---------------------------< process_api >--------------------------------|
620 -- ----------------------------------------------------------------------------
621
622 Procedure process_api
623 (p_document in CLOB
624 ,p_return_status out nocopy VARCHAR2
625 ,p_validate in number default hr_api.g_false_num
626 ,p_effective_date in date default null
627 )
628 IS
629 l_postState VARCHAR2(2);
630 l_return_status VARCHAR2(1);
631 l_commitElement xmldom.DOMElement;
632 l_object_version_number number;
633 l_parser xmlparser.Parser;
634 l_CommitNode xmldom.DOMNode;
635 l_proc varchar2(72) := g_package || 'process_api';
636
637 BEGIN
638
639 hr_utility.set_location(' Entering:' || l_proc,10);
640 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
641
642 l_parser := xmlparser.newParser;
643 xmlparser.ParseCLOB(l_parser,p_document);
644 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
645
646 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
647
648 l_commitElement := xmldom.makeElement(l_CommitNode);
649 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
650 l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
651
652 if l_postState = '0' then
653
654 create_appraisal
655 ( p_validate => p_validate
656 ,p_effective_date => p_effective_date
657 ,p_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'BusinessGroupId',null)
658 ,p_appraisal_template_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AppraisalTemplateId',null)
659 ,p_appraisee_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AppraiseePersonId',null)
660 ,p_appraiser_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AppraiserPersonId',null)
661 ,p_appraisal_date => hr_transaction_swi.getDateValue(l_CommitNode,'AppraisalDate',null)
662 ,p_appraisal_period_start_date => hr_transaction_swi.getDateValue(l_CommitNode,'AppraisalPeriodStartDate',null)
663 ,p_appraisal_period_end_date => hr_transaction_swi.getDateValue(l_CommitNode,'AppraisalPeriodEndDate',null)
664 ,p_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Type',null)
665 ,p_next_appraisal_date => hr_transaction_swi.getDateValue(l_CommitNode,'NextAppraisalDate',null)
666 ,p_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Status',null)
667 ,p_group_date => hr_transaction_swi.getDateValue(l_CommitNode,'GroupDate',null)
668 ,p_group_initiator_id => hr_transaction_swi.getNumberValue(l_CommitNode,'GroupInitiatorId',null)
669 ,p_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Comments',null)
670 ,p_overall_performance_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OverallPerformanceLevelId',null)
671 ,p_open => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Open',null)
672 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory',null)
673 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1',null)
674 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2',null)
675 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3',null)
676 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4',null)
677 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5',null)
681 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9',null)
678 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6',null)
679 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7',null)
680 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8',null)
682 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10',null)
683 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11',null)
684 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12',null)
685 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13',null)
686 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14',null)
687 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15',null)
688 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16',null)
689 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17',null)
690 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18',null)
691 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19',null)
692 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20',null)
693 ,p_system_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SystemType',null)
694 ,p_system_params => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SystemParams',null)
695 ,p_appraisee_access => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AppraiseeAccess',null)
696 ,p_main_appraiser_id => hr_transaction_swi.getNumberValue(l_CommitNode,'MainAppraiserId',null)
697 ,p_assignment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentId',null)
698 ,p_assignment_start_date => hr_transaction_swi.getDateValue(l_CommitNode,'AssignmentStartDate',null)
699 ,p_asg_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AsgBusinessGroupId',null)
700 ,p_assignment_organization_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentOrganizationId',null)
701 ,p_assignment_job_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentJobId',null)
702 ,p_assignment_position_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentPositionId',null)
703 ,p_assignment_grade_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentGradeId',null)
704 ,p_appraisal_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AppraisalId',null)
705 ,p_object_version_number => l_object_version_number
706 ,p_return_status => l_return_status
707 ,p_appraisal_system_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AppraisalSystemStatus',null)
708 ,p_potential_readiness_level => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PotentialReadinessLevel',null)
709 ,p_potential_short_term_workopp => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PotentialShortTermWorkopp',null)
710 ,p_potential_long_term_workopp => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PotentialLongTermWorkopp',null)
711 ,p_potential_details => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PotentialDetails',null)
712 ,p_event_id => hr_transaction_swi.getNumberValue(l_CommitNode,'EventId',null)
713 ,p_show_competency_ratings => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowCompetencyRatings',null)
714 ,p_show_objective_ratings => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowObjectiveRatings',null)
715 ,p_show_questionnaire_info => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowQuestionnaireInfo',null)
716 ,p_show_participant_details => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowParticipantDetails',null)
717 ,p_show_participant_ratings => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowParticipantRatings',null)
718 ,p_show_participant_names => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowParticipantNames',null)
719 ,p_show_overall_ratings => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowOverallRatings',null)
720 ,p_show_overall_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowOverallComments',null)
721 ,p_update_appraisal => hr_transaction_swi.getVarchar2Value(l_CommitNode,'UpdateAppraisal',null)
722 ,p_provide_overall_feedback => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ProvideOverallFeedback',null)
723 ,p_appraisee_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AppraiseeComments',null)
724 );
725
726 elsif l_postState = '2' then
727
728 update_appraisal
729 ( p_validate => p_validate
730 ,p_effective_date => p_effective_date
731 ,p_appraisal_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AppraisalId')
732 ,p_object_version_number => l_object_version_number
733 ,p_appraiser_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AppraiserPersonId')
734 ,p_appraisal_date => hr_transaction_swi.getDateValue(l_CommitNode,'AppraisalDate')
735 ,p_appraisal_period_end_date => hr_transaction_swi.getDateValue(l_CommitNode,'AppraisalPeriodEndDate')
739 ,p_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Status')
736 ,p_appraisal_period_start_date => hr_transaction_swi.getDateValue(l_CommitNode,'AppraisalPeriodStartDate')
737 ,p_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Type')
738 ,p_next_appraisal_date => hr_transaction_swi.getDateValue(l_CommitNode,'NextAppraisalDate')
740 ,p_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Comments')
741 ,p_overall_performance_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OverallPerformanceLevelId')
742 ,p_open => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Open')
743 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory')
744 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1')
745 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2')
746 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3')
747 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4')
748 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5')
749 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6')
750 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7')
751 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8')
752 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9')
753 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10')
754 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11')
755 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12')
756 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13')
757 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14')
758 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15')
759 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16')
760 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17')
761 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18')
762 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19')
763 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20')
764 ,p_system_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SystemType')
765 ,p_system_params => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SystemParams')
766 ,p_appraisee_access => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AppraiseeAccess')
767 ,p_main_appraiser_id => hr_transaction_swi.getNumberValue(l_CommitNode,'MainAppraiserId')
768 ,p_assignment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentId')
769 ,p_assignment_start_date => hr_transaction_swi.getDateValue(l_CommitNode,'AssignmentStartDate')
770 ,p_asg_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AsgBusinessGroupId')
771 ,p_assignment_organization_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentOrganizationId')
772 ,p_assignment_job_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentJobId')
773 ,p_assignment_position_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentPositionId')
774 ,p_assignment_grade_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssignmentGradeId')
775 ,p_return_status => l_return_status
776 ,p_appraisal_system_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AppraisalSystemStatus')
777 ,p_potential_readiness_level => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PotentialReadinessLevel')
778 ,p_potential_short_term_workopp => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PotentialShortTermWorkopp')
779 ,p_potential_long_term_workopp => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PotentialLongTermWorkopp')
780 ,p_potential_details => hr_transaction_swi.getVarchar2Value(l_CommitNode,'PotentialDetails')
781 ,p_event_id => hr_transaction_swi.getNumberValue(l_CommitNode,'EventId')
782 ,p_show_competency_ratings => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowCompetencyRatings')
783 ,p_show_objective_ratings => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowObjectiveRatings')
784 ,p_show_questionnaire_info => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowQuestionnaireInfo')
785 ,p_show_participant_details => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowParticipantDetails')
786 ,p_show_participant_ratings => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowParticipantRatings')
787 ,p_show_participant_names => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowParticipantNames')
788 ,p_show_overall_ratings => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowOverallRatings')
789 ,p_show_overall_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ShowOverallComments')
790 ,p_update_appraisal => hr_transaction_swi.getVarchar2Value(l_CommitNode,'UpdateAppraisal')
791 ,p_provide_overall_feedback => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ProvideOverallFeedback')
792 ,p_appraisee_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AppraiseeComments')
793 );
794
795 elsif l_postState = '3' then
796
797 delete_appraisal
798 ( p_validate => p_validate
799 ,p_appraisal_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AppraisalId')
800 ,p_object_version_number => l_object_version_number
801 ,p_return_status => l_return_status
802 );
803
804 p_return_status := l_return_status;
805
806 end if;
807
808 hr_utility.set_location('Exiting:' || l_proc,40);
809
810 END process_api;
811 end hr_appraisals_swi;