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