[Home] [Help]
PACKAGE BODY: APPS.HR_COMPETENCE_ELEMENT_SWI
Source
1 Package Body hr_competence_element_swi As
2 /* $Header: hrcelswi.pkb 120.7.12020000.2 2012/07/04 22:22:56 amnaraya ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'hr_competence_element_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------< create_competence_element >----------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_competence_element
12 (p_validate in number default hr_api.g_false_num
13 ,p_competence_element_id in number
14 ,p_object_version_number out nocopy number
15 ,p_type in varchar2
16 ,p_business_group_id in number default null
17 ,p_enterprise_id in number default null
18 ,p_competence_id in number default null
19 ,p_proficiency_level_id in number default null
20 ,p_high_proficiency_level_id in number default null
21 ,p_weighting_level_id in number default null
22 ,p_rating_level_id in number default null
23 ,p_person_id in number default null
24 ,p_job_id in number default null
25 ,p_valid_grade_id in number default null
26 ,p_position_id in number default null
27 ,p_organization_id in number default null
28 ,p_parent_competence_element_id in number default null
29 ,p_activity_version_id in number default null
30 ,p_assessment_id in number default null
31 ,p_assessment_type_id in number default null
32 ,p_mandatory in varchar2 default null
33 ,p_effective_date_from in date default null
34 ,p_effective_date_to in date default null
35 ,p_group_competence_type in varchar2 default null
36 ,p_competence_type in varchar2 default null
37 ,p_normal_elapse_duration in number default null
38 ,p_normal_elapse_duration_unit in varchar2 default null
39 ,p_sequence_number in number default null
40 ,p_source_of_proficiency_level in varchar2 default null
41 ,p_line_score in number default null
42 ,p_certification_date in date default null
43 ,p_certification_method in varchar2 default null
44 ,p_next_certification_date in date default null
45 ,p_comments in varchar2 default null
46 ,p_attribute_category in varchar2 default null
47 ,p_attribute1 in varchar2 default null
48 ,p_attribute2 in varchar2 default null
49 ,p_attribute3 in varchar2 default null
50 ,p_attribute4 in varchar2 default null
51 ,p_attribute5 in varchar2 default null
52 ,p_attribute6 in varchar2 default null
53 ,p_attribute7 in varchar2 default null
54 ,p_attribute8 in varchar2 default null
55 ,p_attribute9 in varchar2 default null
56 ,p_attribute10 in varchar2 default null
57 ,p_attribute11 in varchar2 default null
58 ,p_attribute12 in varchar2 default null
59 ,p_attribute13 in varchar2 default null
60 ,p_attribute14 in varchar2 default null
61 ,p_attribute15 in varchar2 default null
62 ,p_attribute16 in varchar2 default null
63 ,p_attribute17 in varchar2 default null
64 ,p_attribute18 in varchar2 default null
65 ,p_attribute19 in varchar2 default null
66 ,p_attribute20 in varchar2 default null
67 ,p_effective_date in date
68 ,p_object_id in number default null
69 ,p_object_name in varchar2 default null
70 ,p_party_id in number default null
71 ,p_return_status out nocopy varchar2
72 ,p_appr_line_score in number default null
73 ,p_status in varchar2 default null
74 ) is
75 --
76 -- Variables for API Boolean parameters
77 l_validate boolean;
78 --
79 -- Variables for IN/OUT parameters
80 --
81 -- Other variables
82 l_competence_element_id number;
83 l_proc varchar2(72) := g_package ||'create_competence_element';
84
85 Begin
86 hr_utility.set_location(' Entering:' || l_proc,10);
87
88 g_session_id := NULL;
89 g_competence_element_id := NULL;
90 --
91 -- Issue a savepoint
92 --
93 savepoint create_competence_element_swi;
94 --
95 -- Initialise Multiple Message Detection
96 --
97 hr_multi_message.enable_message_list;
98 --
99 -- Remember IN OUT parameter IN values
100 --
101 --
102 -- Convert constant values to their corresponding boolean value
103 --
104 l_validate :=
105 hr_api.constant_to_boolean
106 (p_constant_value => p_validate);
107 --
108 -- Register Surrogate ID or user key values
109 --
110 per_cel_ins.set_base_key_value
111 (p_competence_element_id => p_competence_element_id
112 );
113 --
114 -- Call API
115 --
116 hr_competence_element_api.create_competence_element
117 (p_validate => l_validate
118 ,p_competence_element_id => l_competence_element_id
119 ,p_object_version_number => p_object_version_number
120 ,p_type => p_type
121 ,p_business_group_id => p_business_group_id
122 ,p_enterprise_id => p_enterprise_id
123 ,p_competence_id => p_competence_id
124 ,p_proficiency_level_id => p_proficiency_level_id
125 ,p_high_proficiency_level_id => p_high_proficiency_level_id
126 ,p_weighting_level_id => p_weighting_level_id
127 ,p_rating_level_id => p_rating_level_id
128 ,p_person_id => p_person_id
129 ,p_job_id => p_job_id
130 ,p_valid_grade_id => p_valid_grade_id
131 ,p_position_id => p_position_id
132 ,p_organization_id => p_organization_id
133 ,p_parent_competence_element_id => p_parent_competence_element_id
134 ,p_activity_version_id => p_activity_version_id
135 ,p_assessment_id => p_assessment_id
136 ,p_assessment_type_id => p_assessment_type_id
137 ,p_mandatory => p_mandatory
138 ,p_effective_date_from => p_effective_date_from
139 ,p_effective_date_to => p_effective_date_to
140 ,p_group_competence_type => p_group_competence_type
141 ,p_competence_type => p_competence_type
142 ,p_normal_elapse_duration => p_normal_elapse_duration
143 ,p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
144 ,p_sequence_number => p_sequence_number
145 ,p_source_of_proficiency_level => p_source_of_proficiency_level
146 ,p_line_score => p_line_score
147 ,p_certification_date => p_certification_date
148 ,p_certification_method => p_certification_method
149 ,p_next_certification_date => p_next_certification_date
150 ,p_comments => p_comments
151 ,p_attribute_category => p_attribute_category
152 ,p_attribute1 => p_attribute1
153 ,p_attribute2 => p_attribute2
154 ,p_attribute3 => p_attribute3
155 ,p_attribute4 => p_attribute4
156 ,p_attribute5 => p_attribute5
157 ,p_attribute6 => p_attribute6
158 ,p_attribute7 => p_attribute7
159 ,p_attribute8 => p_attribute8
160 ,p_attribute9 => p_attribute9
161 ,p_attribute10 => p_attribute10
162 ,p_attribute11 => p_attribute11
163 ,p_attribute12 => p_attribute12
164 ,p_attribute13 => p_attribute13
165 ,p_attribute14 => p_attribute14
166 ,p_attribute15 => p_attribute15
167 ,p_attribute16 => p_attribute16
168 ,p_attribute17 => p_attribute17
169 ,p_attribute18 => p_attribute18
170 ,p_attribute19 => p_attribute19
171 ,p_attribute20 => p_attribute20
172 ,p_effective_date => p_effective_date
173 ,p_object_id => p_object_id
174 ,p_object_name => p_object_name
175 ,p_party_id => p_party_id
176 ,p_appr_line_score => p_appr_line_score
177 ,p_status => p_status
178 );
179 --
180 -- Convert API warning boolean parameter values to specific
181 -- messages and add them to Multiple Message List
182 --
183 --
184 -- Convert API non-warning boolean parameter values
185 --
186 --
187 -- Derive the API return status value based on whether
188 -- messages of any type exist in the Multiple Message List.
189 -- Also disable Multiple Message Detection.
190 --
191 p_return_status := hr_multi_message.get_return_status_disable;
192 hr_utility.set_location(' Leaving:' || l_proc,20);
193 --
194 exception
195 when hr_multi_message.error_message_exist then
196 --
197 -- Catch the Multiple Message List exception which
198 -- indicates API processing has been aborted because
199 -- at least one message exists in the list.
200 --
201 rollback to create_competence_element_swi;
202 --
203 -- Reset IN OUT parameters and set OUT parameters
204 --
205 p_object_version_number := null;
206 p_return_status := hr_multi_message.get_return_status_disable;
207 hr_utility.set_location(' Leaving:' || l_proc, 30);
208
209 when others then
210 --
211 -- When Multiple Message Detection is enabled catch
212 -- any Application specific or other unexpected
213 -- exceptions. Adding appropriate details to the
214 -- Multiple Message List. Otherwise re-raise the
215 -- error.
216 --
217 rollback to create_competence_element_swi;
218 if hr_multi_message.unexpected_error_add(l_proc) then
219 hr_utility.set_location(' Leaving:' || l_proc,40);
220 raise;
221 end if;
222
223
224 --
225 -- Reset IN OUT and set OUT parameters
226 --
227 p_object_version_number := null;
228 p_return_status := hr_multi_message.get_return_status_disable;
229 hr_utility.set_location(' Leaving:' || l_proc,50);
230 end create_competence_element;
231 -- ----------------------------------------------------------------------------
232 -- |---------------------------< copy_competencies >--------------------------|
233 -- ----------------------------------------------------------------------------
234 PROCEDURE copy_competencies
235 (p_activity_version_from in number
236 ,p_activity_version_to in number
237 ,p_competence_type in varchar2 default hr_api.g_varchar2
238 ,p_validate in number default hr_api.g_false_num
239 ,p_return_status out nocopy varchar2
240 ) is
241 --
242 -- Variables for API Boolean parameters
243 l_validate boolean;
244 --
245 -- Variables for IN/OUT parameters
246 --
247 -- Other variables
248 l_proc varchar2(72) := g_package ||'copy_competencies';
249 Begin
250 hr_utility.set_location(' Entering:' || l_proc,10);
251 --
252 -- Issue a savepoint
253 --
254 savepoint copy_competencies_swi;
255 --
256 -- Initialise Multiple Message Detection
257 --
258 hr_multi_message.enable_message_list;
259 --
260 -- Remember IN OUT parameter IN values
261 --
262 --
263 -- Convert constant values to their corresponding boolean value
264 --
265 l_validate :=
266 hr_api.constant_to_boolean
267 (p_constant_value => p_validate);
268 --
269 -- Register Surrogate ID or user key values
270 --
271 --
272 -- Call API
273 --
274 hr_competence_element_api.copy_competencies
275 (p_activity_version_from => p_activity_version_from
276 ,p_activity_version_to => p_activity_version_to
277 ,p_competence_type => p_competence_type
278 ,p_validate => l_validate
279 );
280 --
281 -- Convert API warning boolean parameter values to specific
282 -- messages and add them to Multiple Message List
283 --
284 --
285 -- Convert API non-warning boolean parameter values
286 --
287 --
288 -- Derive the API return status value based on whether
289 -- messages of any type exist in the Multiple Message List.
290 -- Also disable Multiple Message Detection.
291 --
292 p_return_status := hr_multi_message.get_return_status_disable;
293 hr_utility.set_location(' Leaving:' || l_proc,20);
294 --
295 exception
296 when hr_multi_message.error_message_exist then
297 --
298 -- Catch the Multiple Message List exception which
299 -- indicates API processing has been aborted because
300 -- at least one message exists in the list.
301 --
302 rollback to copy_competencies_swi;
303 --
304 -- Reset IN OUT parameters and set OUT parameters
305 --
306 p_return_status := hr_multi_message.get_return_status_disable;
307 hr_utility.set_location(' Leaving:' || l_proc, 30);
308 when others then
309 --
310 -- When Multiple Message Detection is enabled catch
311 -- any Application specific or other unexpected
312 -- exceptions. Adding appropriate details to the
313 -- Multiple Message List. Otherwise re-raise the
314 -- error.
315 --
316 rollback to copy_competencies_swi;
317 if hr_multi_message.unexpected_error_add(l_proc) then
318 hr_utility.set_location(' Leaving:' || l_proc,40);
319 raise;
320 end if;
321 --
322 -- Reset IN OUT and set OUT parameters
323 --
324 p_return_status := hr_multi_message.get_return_status_disable;
325 hr_utility.set_location(' Leaving:' || l_proc,50);
326 end copy_competencies;
327 -- ----------------------------------------------------------------------------
328 -- |-----------------------< delete_competence_element >----------------------|
329 -- ----------------------------------------------------------------------------
330 PROCEDURE delete_competence_element
331 (p_validate in number default hr_api.g_false_num
332 ,p_competence_element_id in number
333 ,p_object_version_number in number
334 ,p_return_status out nocopy varchar2
335 ) is
336 --
337 -- Variables for API Boolean parameters
338 l_validate boolean;
339 --
340 -- Variables for IN/OUT parameters
341 --
342 -- Other variables
343 l_proc varchar2(72) := g_package ||'delete_competence_element';
344 l_object_version_number per_competence_elements.object_version_number%TYPE;
345
346 cursor get_object_version_number(p_competence_element_id per_competence_elements.competence_element_id%TYPE) is
347 select object_version_number
348 from per_competence_elements
349 where competence_element_id = p_competence_element_id;
350 Begin
351 hr_utility.set_location(' Entering:' || l_proc,10);
352
353 g_session_id := NULL;
354 g_competence_element_id := NULL;
355 --
356 -- Issue a savepoint
357 --
358 savepoint delete_competence_element_swi;
359 --
360 -- Initialise Multiple Message Detection
361 --
362 hr_multi_message.enable_message_list;
363 --
364 -- Remember IN OUT parameter IN values
365 --
366 --
367 -- Convert constant values to their corresponding boolean value
368 --
369 l_validate :=
370 hr_api.constant_to_boolean
371 (p_constant_value => p_validate);
372 --
373 -- Register Surrogate ID or user key values
374 --
375 --
376 -- Call API
377 --
378 l_object_version_number := p_object_version_number;
379 if(p_object_version_number is null or p_object_version_number < 0) then
380 open get_object_version_number(p_competence_element_id);
381 fetch get_object_version_number into l_object_version_number;
382 close get_object_version_number;
383 end if;
384 --
385 hr_competence_element_api.delete_competence_element
386 (p_validate => l_validate
387 ,p_competence_element_id => p_competence_element_id
388 ,p_object_version_number => l_object_version_number
389 );
390 --
391 -- Convert API warning boolean parameter values to specific
392 -- messages and add them to Multiple Message List
393 --
394 --
395 -- Convert API non-warning boolean parameter values
396 --
397 --
398 -- Derive the API return status value based on whether
399 -- messages of any type exist in the Multiple Message List.
400 -- Also disable Multiple Message Detection.
401 --
402 p_return_status := hr_multi_message.get_return_status_disable;
403 hr_utility.set_location(' Leaving:' || l_proc,20);
404 --
405 exception
406 when hr_multi_message.error_message_exist then
407 --
408 -- Catch the Multiple Message List exception which
409 -- indicates API processing has been aborted because
410 -- at least one message exists in the list.
411 --
412 rollback to delete_competence_element_swi;
413 --
414 -- Reset IN OUT parameters and set OUT parameters
415 --
416 p_return_status := hr_multi_message.get_return_status_disable;
417 hr_utility.set_location(' Leaving:' || l_proc, 30);
418 when others then
419 --
420 -- When Multiple Message Detection is enabled catch
421 -- any Application specific or other unexpected
422 -- exceptions. Adding appropriate details to the
423 -- Multiple Message List. Otherwise re-raise the
424 -- error.
425 --
426 rollback to delete_competence_element_swi;
427 if hr_multi_message.unexpected_error_add(l_proc) then
428 hr_utility.set_location(' Leaving:' || l_proc,40);
429 raise;
430 end if;
431 --
432 -- Reset IN OUT and set OUT parameters
433 --
434 p_return_status := hr_multi_message.get_return_status_disable;
435 hr_utility.set_location(' Leaving:' || l_proc,50);
436 end delete_competence_element;
437 -- ----------------------------------------------------------------------------
438 -- |---------------------< maintain_student_comp_element >--------------------|
439 -- ----------------------------------------------------------------------------
440 PROCEDURE maintain_student_comp_element
441 (p_person_id in number
442 ,p_competence_id in number
443 ,p_proficiency_level_id in number
444 ,p_business_group_id in number
445 ,p_effective_date_from in date
446 ,p_effective_date_to in date
447 ,p_certification_date in date
448 ,p_certification_method in varchar2
449 ,p_next_certification_date in date
450 ,p_source_of_proficiency_level in varchar2
451 ,p_comments in varchar2
452 ,p_effective_date in date
453 ,p_validate in number default hr_api.g_false_num
454 ,p_competence_created out nocopy number
455 ,p_return_status out nocopy varchar2
456 ) is
457 --
458 -- Variables for API Boolean parameters
459 l_validate boolean;
460 --
461 -- Variables for IN/OUT parameters
462 --
463 -- Other variables
464 l_proc varchar2(72) := g_package ||'maintain_student_comp_element';
465 Begin
466 hr_utility.set_location(' Entering:' || l_proc,10);
467 --
468 -- Issue a savepoint
469 --
470 savepoint maintain_student_comp_ele_swi;
471 --
472 -- Initialise Multiple Message Detection
473 --
474 hr_multi_message.enable_message_list;
475 --
476 -- Remember IN OUT parameter IN values
477 --
478 --
479 -- Convert constant values to their corresponding boolean value
480 --
481 l_validate :=
482 hr_api.constant_to_boolean
483 (p_constant_value => p_validate);
484 --
485 -- Register Surrogate ID or user key values
486 --
487 --
488 -- Call API
489 --
490 hr_competence_element_api.maintain_student_comp_element
491 (p_person_id => p_person_id
492 ,p_competence_id => p_competence_id
493 ,p_proficiency_level_id => p_proficiency_level_id
494 ,p_business_group_id => p_business_group_id
495 ,p_effective_date_from => p_effective_date_from
496 ,p_effective_date_to => p_effective_date_to
497 ,p_certification_date => p_certification_date
498 ,p_certification_method => p_certification_method
499 ,p_next_certification_date => p_next_certification_date
500 ,p_source_of_proficiency_level => p_source_of_proficiency_level
501 ,p_comments => p_comments
502 ,p_effective_date => p_effective_date
503 ,p_validate => l_validate
504 ,p_competence_created => p_competence_created
505 );
506 --
507 -- Convert API warning boolean parameter values to specific
508 -- messages and add them to Multiple Message List
509 --
510 --
511 -- Convert API non-warning boolean parameter values
512 --
513 --
514 -- Derive the API return status value based on whether
515 -- messages of any type exist in the Multiple Message List.
516 -- Also disable Multiple Message Detection.
517 --
518 p_return_status := hr_multi_message.get_return_status_disable;
519 hr_utility.set_location(' Leaving:' || l_proc,20);
520 --
521 exception
522 when hr_multi_message.error_message_exist then
523 --
524 -- Catch the Multiple Message List exception which
525 -- indicates API processing has been aborted because
526 -- at least one message exists in the list.
527 --
528 rollback to maintain_student_comp_ele_swi;
529 --
530 -- Reset IN OUT parameters and set OUT parameters
531 --
532 p_competence_created := null;
533 p_return_status := hr_multi_message.get_return_status_disable;
534 hr_utility.set_location(' Leaving:' || l_proc, 30);
535 when others then
536 --
537 -- When Multiple Message Detection is enabled catch
538 -- any Application specific or other unexpected
539 -- exceptions. Adding appropriate details to the
540 -- Multiple Message List. Otherwise re-raise the
541 -- error.
542 --
543 rollback to maintain_student_comp_ele_swi;
544 if hr_multi_message.unexpected_error_add(l_proc) then
545 hr_utility.set_location(' Leaving:' || l_proc,40);
546 raise;
547 end if;
548 --
549 -- Reset IN OUT and set OUT parameters
550 --
551 p_competence_created := null;
552 p_return_status := hr_multi_message.get_return_status_disable;
553 hr_utility.set_location(' Leaving:' || l_proc,50);
554 end maintain_student_comp_element;
555
556 -- ----------------------------------------------------------------------------
557 -- |-----------------------< get_non_gmisc_value >----------------------|
558 -- ----------------------------------------------------------------------------
559
560 function get_non_gmisc_value
561 (p_old_value in date
562 ,p_current_value in date
563 ) return date is
564 begin
565 if p_current_value = hr_api.g_date then
566 return p_old_value;
567 else
568 return p_current_value;
569 end if;
570 end get_non_gmisc_value;
571
572 -- ----------------------------------------------------------------------------
573 -- |-----------------------< get_non_gmisc_value >----------------------|
574 -- ----------------------------------------------------------------------------
575
576
577 function get_non_gmisc_value
578 (p_old_value in number
579 ,p_current_value in number
580 ) return number is
581 begin
582 if p_current_value = hr_api.g_number then
583 return p_old_value;
584 else
585 return p_current_value;
586 end if;
587 end get_non_gmisc_value;
588
589 -- ----------------------------------------------------------------------------
590 -- |-----------------------< get_non_gmisc_value >----------------------|
591 -- ----------------------------------------------------------------------------
592
593
594 function get_non_gmisc_value
595 (p_old_value in varchar2
596 ,p_current_value in varchar2
597 ) return varchar2 is
598 begin
599 if p_current_value = hr_api.g_varchar2 then
600 return p_old_value;
601 else
602 return p_current_value;
603 end if;
604 end get_non_gmisc_value;
605
606
607
608 -- ----------------------------------------------------------------------------
609 -- |-----------------------< update_competence_element >----------------------|
610 -- ----------------------------------------------------------------------------
611 PROCEDURE update_competence_element
612 (p_competence_element_id in number
613 ,p_object_version_number in out nocopy number
614 ,p_competence_id in number default hr_api.g_number
615 ,p_proficiency_level_id in number default hr_api.g_number
616 ,p_high_proficiency_level_id in number default hr_api.g_number
617 ,p_weighting_level_id in number default hr_api.g_number
618 ,p_rating_level_id in number default hr_api.g_number
619 ,p_mandatory in varchar2 default hr_api.g_varchar2
620 ,p_effective_date_from in date default hr_api.g_date
621 ,p_effective_date_to in date default hr_api.g_date
622 ,p_group_competence_type in varchar2 default hr_api.g_varchar2
623 ,p_competence_type in varchar2 default hr_api.g_varchar2
624 ,p_normal_elapse_duration in number default hr_api.g_number
625 ,p_normal_elapse_duration_unit in varchar2 default hr_api.g_varchar2
626 ,p_sequence_number in number default hr_api.g_number
627 ,p_source_of_proficiency_level in varchar2 default hr_api.g_varchar2
628 ,p_line_score in number default hr_api.g_number
629 ,p_certification_date in date default hr_api.g_date
630 ,p_certification_method in varchar2 default hr_api.g_varchar2
631 ,p_next_certification_date in date default hr_api.g_date
632 ,p_comments in varchar2 default hr_api.g_varchar2
633 ,p_attribute_category in varchar2 default hr_api.g_varchar2
634 ,p_attribute1 in varchar2 default hr_api.g_varchar2
635 ,p_attribute2 in varchar2 default hr_api.g_varchar2
636 ,p_attribute3 in varchar2 default hr_api.g_varchar2
637 ,p_attribute4 in varchar2 default hr_api.g_varchar2
638 ,p_attribute5 in varchar2 default hr_api.g_varchar2
639 ,p_attribute6 in varchar2 default hr_api.g_varchar2
640 ,p_attribute7 in varchar2 default hr_api.g_varchar2
641 ,p_attribute8 in varchar2 default hr_api.g_varchar2
642 ,p_attribute9 in varchar2 default hr_api.g_varchar2
643 ,p_attribute10 in varchar2 default hr_api.g_varchar2
644 ,p_attribute11 in varchar2 default hr_api.g_varchar2
645 ,p_attribute12 in varchar2 default hr_api.g_varchar2
646 ,p_attribute13 in varchar2 default hr_api.g_varchar2
647 ,p_attribute14 in varchar2 default hr_api.g_varchar2
648 ,p_attribute15 in varchar2 default hr_api.g_varchar2
649 ,p_attribute16 in varchar2 default hr_api.g_varchar2
650 ,p_attribute17 in varchar2 default hr_api.g_varchar2
651 ,p_attribute18 in varchar2 default hr_api.g_varchar2
652 ,p_attribute19 in varchar2 default hr_api.g_varchar2
653 ,p_attribute20 in varchar2 default hr_api.g_varchar2
654 ,p_effective_date in date
655 ,p_validate in number default hr_api.g_false_num
656 ,p_party_id in number
657 ,p_return_status out nocopy varchar2
658 ,p_datetrack_update_mode in varchar2 default hr_api.g_correction
659 ,p_appr_line_score in number default hr_api.g_number
660 ,p_status in varchar2 default null
661 ) is
662 --
663 -- Variables for API Boolean parameters
664 l_validate boolean;
665 --
666 -- Variables for IN/OUT parameters
667 l_object_version_number number;
668 --
669 -- Other variables
670 l_competence_element_id number;
671 l_effective_date_from date;
672 l_effective_date_to date;
673 l_proc varchar2(72) := g_package ||'update_competence_element';
674
675
676 Begin
677 hr_utility.set_location(' Entering:' || l_proc,10);
678
679 g_session_id := NULL;
680 g_competence_element_id := NULL;
681 --
682 -- Issue a savepoint
683 --
684 savepoint update_competence_element_swi;
685 --
686 -- Initialise Multiple Message Detection
687 --
688 hr_multi_message.enable_message_list;
689 --
690 -- Remember IN OUT parameter IN values
691 --
692 l_object_version_number := p_object_version_number;
693 --
694 -- Convert constant values to their corresponding boolean value
695 --
696 l_validate :=
697 hr_api.constant_to_boolean
698 (p_constant_value => p_validate);
699 --
700 -- look to see if the competence_id has been changed
701 --
702 per_cel_shd.lck
703 (p_competence_element_id => p_competence_element_id
704 ,p_object_version_number => p_object_version_number
705 );
706 --
707 if p_competence_id<>hr_api.g_number
708 and per_cel_shd.g_old_rec.competence_id<>p_competence_id then
709 --
710 -- the competence has been updated, so delete the old competence element
711 -- and insert a new one.
712 --
713 hr_competence_element_api.delete_competence_element
714 (p_validate => false
715 ,p_competence_element_id => p_competence_element_id
716 ,p_object_version_number => p_object_version_number
717 );
718 per_cel_ins.set_base_key_value
719 (p_competence_element_id => p_competence_element_id
720 );
721 --
722
723 hr_competence_element_api.create_competence_element
724 (p_validate => false
725 ,p_competence_element_id => l_competence_element_id
726 ,p_object_version_number => p_object_version_number
727 ,p_type => per_cel_shd.g_old_rec.type
728 ,p_business_group_id => per_cel_shd.g_old_rec.business_group_id
729 ,p_enterprise_id => per_cel_shd.g_old_rec.enterprise_id
730 ,p_competence_id => get_non_gmisc_value( per_cel_shd.g_old_rec.competence_id,p_competence_id)
731 ,p_proficiency_level_id => get_non_gmisc_value( per_cel_shd.g_old_rec.proficiency_level_id,p_proficiency_level_id)
732 ,p_high_proficiency_level_id => get_non_gmisc_value( per_cel_shd.g_old_rec.high_proficiency_level_id,p_high_proficiency_level_id)
733 ,p_weighting_level_id => get_non_gmisc_value( per_cel_shd.g_old_rec.weighting_level_id,p_weighting_level_id)
734 ,p_rating_level_id => get_non_gmisc_value( per_cel_shd.g_old_rec.rating_level_id,p_rating_level_id)
735 ,p_person_id => per_cel_shd.g_old_rec.person_id
736 ,p_job_id => per_cel_shd.g_old_rec.job_id
737 ,p_valid_grade_id => per_cel_shd.g_old_rec.valid_grade_id
738 ,p_position_id => per_cel_shd.g_old_rec.position_id
739 ,p_organization_id => per_cel_shd.g_old_rec.organization_id
740 ,p_parent_competence_element_id => per_cel_shd.g_old_rec.parent_competence_element_id
741 ,p_activity_version_id => per_cel_shd.g_old_rec.activity_version_id
742 ,p_assessment_id => per_cel_shd.g_old_rec.assessment_id
743 ,p_assessment_type_id => per_cel_shd.g_old_rec.assessment_type_id
744 ,p_mandatory => get_non_gmisc_value( per_cel_shd.g_old_rec.mandatory,p_mandatory)
745 ,p_effective_date_from => get_non_gmisc_value( per_cel_shd.g_old_rec.effective_date_from, p_effective_date_from)
746 ,p_effective_date_to => get_non_gmisc_value( per_cel_shd.g_old_rec.effective_date_to, p_effective_date_to)
747 ,p_group_competence_type => get_non_gmisc_value( per_cel_shd.g_old_rec.group_competence_type,p_group_competence_type)
748 ,p_competence_type => get_non_gmisc_value( per_cel_shd.g_old_rec.competence_type,p_competence_type)
749 ,p_normal_elapse_duration => get_non_gmisc_value( per_cel_shd.g_old_rec.normal_elapse_duration,p_normal_elapse_duration )
750 ,p_normal_elapse_duration_unit => get_non_gmisc_value( per_cel_shd.g_old_rec.normal_elapse_duration_unit,p_normal_elapse_duration_unit)
751 ,p_sequence_number => get_non_gmisc_value( per_cel_shd.g_old_rec.sequence_number,p_sequence_number )
752 ,p_source_of_proficiency_level => get_non_gmisc_value( per_cel_shd.g_old_rec.source_of_proficiency_level, p_source_of_proficiency_level)
753 ,p_line_score => get_non_gmisc_value( per_cel_shd.g_old_rec.line_score,p_line_score )
754 ,p_certification_date => get_non_gmisc_value( per_cel_shd.g_old_rec.certification_date, p_certification_date)
755 ,p_certification_method => get_non_gmisc_value( per_cel_shd.g_old_rec.certification_method, p_certification_method)
756 ,p_next_certification_date => get_non_gmisc_value( per_cel_shd.g_old_rec.next_certification_date, p_next_certification_date)
757 ,p_comments => get_non_gmisc_value( per_cel_shd.g_old_rec.comments, p_comments)
758 ,p_attribute_category => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute_category, p_attribute_category)
759 ,p_attribute1 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute1, p_attribute1)
760 ,p_attribute2 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute2, p_attribute2)
761 ,p_attribute3 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute3, p_attribute3)
762 ,p_attribute4 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute4, p_attribute4)
763 ,p_attribute5 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute5, p_attribute5)
764 ,p_attribute6 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute6, p_attribute6)
765 ,p_attribute7 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute7, p_attribute7)
766 ,p_attribute8 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute8, p_attribute8)
767 ,p_attribute9 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute9, p_attribute9)
768 ,p_attribute10 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute10, p_attribute10)
769 ,p_attribute11 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute11, p_attribute11)
770 ,p_attribute12 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute12, p_attribute12)
771 ,p_attribute13 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute13, p_attribute13)
772 ,p_attribute14 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute14, p_attribute14)
773 ,p_attribute15 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute15, p_attribute15)
774 ,p_attribute16 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute16, p_attribute16)
775 ,p_attribute17 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute17, p_attribute17)
776 ,p_attribute18 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute18, p_attribute18)
777 ,p_attribute19 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute19, p_attribute19)
778 ,p_attribute20 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute20, p_attribute20)
779 ,p_effective_date => p_effective_date
780 ,p_object_id => per_cel_shd.g_old_rec.object_id
781 ,p_object_name => per_cel_shd.g_old_rec.object_name
782 ,p_party_id => get_non_gmisc_value( per_cel_shd.g_old_rec.party_id, p_party_id)
783 ,p_appr_line_score => get_non_gmisc_value( per_cel_shd.g_old_rec.appr_line_score, p_appr_line_score)
784 ,p_status => get_non_gmisc_value( per_cel_shd.g_old_rec.status, p_status)
785 );
786 else
787 if (nvl(p_datetrack_update_mode, '#') <> hr_api.g_update) then
788
789 -- the competence has not been changed,
790 -- so correct the existing row since datetrackmode is correct
791
792 hr_competence_element_api.update_competence_element
793 (p_competence_element_id => p_competence_element_id
794 ,p_object_version_number => p_object_version_number
795 ,p_proficiency_level_id => p_proficiency_level_id
796 ,p_high_proficiency_level_id => p_high_proficiency_level_id
797 ,p_weighting_level_id => p_weighting_level_id
798 ,p_rating_level_id => p_rating_level_id
799 ,p_mandatory => p_mandatory
800 ,p_effective_date_from => p_effective_date_from
801 ,p_effective_date_to => p_effective_date_to
802 ,p_group_competence_type => p_group_competence_type
803 ,p_competence_type => p_competence_type
804 ,p_normal_elapse_duration => p_normal_elapse_duration
805 ,p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
806 ,p_sequence_number => p_sequence_number
807 ,p_source_of_proficiency_level => p_source_of_proficiency_level
808 ,p_line_score => p_line_score
809 ,p_certification_date => p_certification_date
810 ,p_certification_method => p_certification_method
811 ,p_next_certification_date => p_next_certification_date
812 ,p_comments => p_comments
813 ,p_attribute_category => p_attribute_category
814 ,p_attribute1 => p_attribute1
815 ,p_attribute2 => p_attribute2
816 ,p_attribute3 => p_attribute3
817 ,p_attribute4 => p_attribute4
818 ,p_attribute5 => p_attribute5
819 ,p_attribute6 => p_attribute6
820 ,p_attribute7 => p_attribute7
821 ,p_attribute8 => p_attribute8
822 ,p_attribute9 => p_attribute9
823 ,p_attribute10 => p_attribute10
824 ,p_attribute11 => p_attribute11
825 ,p_attribute12 => p_attribute12
826 ,p_attribute13 => p_attribute13
827 ,p_attribute14 => p_attribute14
828 ,p_attribute15 => p_attribute15
829 ,p_attribute16 => p_attribute16
830 ,p_attribute17 => p_attribute17
831 ,p_attribute18 => p_attribute18
832 ,p_attribute19 => p_attribute19
833 ,p_attribute20 => p_attribute20
834 ,p_effective_date => p_effective_date
835 ,p_validate => false
836 ,p_party_id => p_party_id
837 ,p_appr_line_score => p_appr_line_score
838 ,p_status => p_status
839 );
840 elsif (p_datetrack_update_mode = hr_api.g_update) then
841
842 -- the competence has not been changed,
843 -- so update the existing row since datetrackmode is update
844 -- Update implies Maintain history by end-dating the previous row and inserting a new Row.
845
846 l_effective_date_to:= p_effective_date_from - 1;
847 -- End-date the existing row
848 hr_competence_element_api.update_competence_element(
849 p_competence_element_id => p_competence_element_id
850 ,p_object_version_number => p_object_version_number
851 ,p_effective_date_to => l_effective_date_to
852 ,p_effective_date => p_effective_date
853 );
854
855
856
857 -- Add new row
858
859 hr_competence_element_api.create_competence_element
860 (p_validate => false
861 ,p_competence_element_id => l_competence_element_id
862 ,p_object_version_number => l_object_version_number
863 ,p_type => per_cel_shd.g_old_rec.type
864 ,p_business_group_id => per_cel_shd.g_old_rec.business_group_id
865 ,p_enterprise_id => per_cel_shd.g_old_rec.enterprise_id
866 ,p_competence_id => get_non_gmisc_value( per_cel_shd.g_old_rec.competence_id,p_competence_id)
867 ,p_proficiency_level_id => get_non_gmisc_value( per_cel_shd.g_old_rec.proficiency_level_id,p_proficiency_level_id)
868 ,p_high_proficiency_level_id => get_non_gmisc_value( per_cel_shd.g_old_rec.high_proficiency_level_id,p_high_proficiency_level_id)
869 ,p_weighting_level_id => get_non_gmisc_value( per_cel_shd.g_old_rec.weighting_level_id,p_weighting_level_id)
870 ,p_rating_level_id => get_non_gmisc_value( per_cel_shd.g_old_rec.rating_level_id,p_rating_level_id)
871 ,p_person_id => per_cel_shd.g_old_rec.person_id
872 ,p_job_id => per_cel_shd.g_old_rec.job_id
873 ,p_valid_grade_id => per_cel_shd.g_old_rec.valid_grade_id
874 ,p_position_id => per_cel_shd.g_old_rec.position_id
875 ,p_organization_id => per_cel_shd.g_old_rec.organization_id
876 ,p_parent_competence_element_id => per_cel_shd.g_old_rec.parent_competence_element_id
877 ,p_activity_version_id => per_cel_shd.g_old_rec.activity_version_id
878 ,p_assessment_id => per_cel_shd.g_old_rec.assessment_id
879 ,p_assessment_type_id => per_cel_shd.g_old_rec.assessment_type_id
880 ,p_mandatory => get_non_gmisc_value( per_cel_shd.g_old_rec.mandatory,p_mandatory)
881 ,p_effective_date_from => get_non_gmisc_value( per_cel_shd.g_old_rec.effective_date_from, p_effective_date_from)
882 ,p_effective_date_to => get_non_gmisc_value( per_cel_shd.g_old_rec.effective_date_to, p_effective_date_to)
883 ,p_group_competence_type => get_non_gmisc_value( per_cel_shd.g_old_rec.group_competence_type,p_group_competence_type)
884 ,p_competence_type => get_non_gmisc_value( per_cel_shd.g_old_rec.competence_type,p_competence_type)
885 ,p_normal_elapse_duration => get_non_gmisc_value( per_cel_shd.g_old_rec.normal_elapse_duration,p_normal_elapse_duration )
886 ,p_normal_elapse_duration_unit => get_non_gmisc_value( per_cel_shd.g_old_rec.normal_elapse_duration_unit,p_normal_elapse_duration_unit)
887 ,p_sequence_number => get_non_gmisc_value( per_cel_shd.g_old_rec.sequence_number,p_sequence_number )
888 ,p_source_of_proficiency_level => get_non_gmisc_value( per_cel_shd.g_old_rec.source_of_proficiency_level, p_source_of_proficiency_level)
889 ,p_line_score => get_non_gmisc_value( per_cel_shd.g_old_rec.line_score,p_line_score )
890 ,p_certification_date => get_non_gmisc_value( per_cel_shd.g_old_rec.certification_date, p_certification_date)
891 ,p_certification_method => get_non_gmisc_value( per_cel_shd.g_old_rec.certification_method, p_certification_method)
892 ,p_next_certification_date => get_non_gmisc_value( per_cel_shd.g_old_rec.next_certification_date, p_next_certification_date)
893 ,p_comments => get_non_gmisc_value( per_cel_shd.g_old_rec.comments, p_comments)
894 ,p_attribute_category => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute_category, p_attribute_category)
895 ,p_attribute1 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute1, p_attribute1)
896 ,p_attribute2 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute2, p_attribute2)
897 ,p_attribute3 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute3, p_attribute3)
898 ,p_attribute4 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute4, p_attribute4)
899 ,p_attribute5 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute5, p_attribute5)
900 ,p_attribute6 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute6, p_attribute6)
901 ,p_attribute7 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute7, p_attribute7)
902 ,p_attribute8 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute8, p_attribute8)
903 ,p_attribute9 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute9, p_attribute9)
904 ,p_attribute10 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute10, p_attribute10)
905 ,p_attribute11 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute11, p_attribute11)
906 ,p_attribute12 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute12, p_attribute12)
907 ,p_attribute13 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute13, p_attribute13)
908 ,p_attribute14 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute14, p_attribute14)
909 ,p_attribute15 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute15, p_attribute15)
910 ,p_attribute16 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute16, p_attribute16)
911 ,p_attribute17 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute17, p_attribute17)
912 ,p_attribute18 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute18, p_attribute18)
913 ,p_attribute19 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute19, p_attribute19)
914 ,p_attribute20 => get_non_gmisc_value( per_cel_shd.g_old_rec.attribute20, p_attribute20)
915 ,p_effective_date => p_effective_date
916 ,p_object_id => per_cel_shd.g_old_rec.object_id
917 ,p_object_name => per_cel_shd.g_old_rec.object_name
918 ,p_party_id => get_non_gmisc_value( per_cel_shd.g_old_rec.party_id, p_party_id)
919 ,p_appr_line_score => get_non_gmisc_value( per_cel_shd.g_old_rec.appr_line_score, p_appr_line_score)
920 ,p_status => get_non_gmisc_value( per_cel_shd.g_old_rec.status, p_status)
921 );
922 g_session_id := ICX_SEC.G_SESSION_ID;
923 g_competence_element_id := l_competence_element_id;
924 end if;
925 end if;
926 --
927 -- Convert API warning boolean parameter values to specific
928 -- messages and add them to Multiple Message List
929 --
930 --
931 -- Convert API non-warning boolean parameter values
932 --
933 --
934 -- Derive the API return status value based on whether
935 -- messages of any type exist in the Multiple Message List.
936 -- Also disable Multiple Message Detection.
937 --
938 p_return_status := hr_multi_message.get_return_status_disable;
939 --
940 -- When in validation only mode raise the Validate_Enabled exception
941 --
942 if l_validate then
943 raise hr_api.validate_enabled;
944 end if;
945 hr_utility.set_location(' Leaving:' || l_proc,20);
946 --
947 exception
948 when hr_multi_message.error_message_exist then
949 --
950 -- Catch the Multiple Message List exception which
951 -- indicates API processing has been aborted because
952 -- at least one message exists in the list.
953 --
954 rollback to update_competence_element_swi;
955 --
956 -- Reset IN OUT parameters and set OUT parameters
957 --
958 p_object_version_number := l_object_version_number;
959 p_return_status := hr_multi_message.get_return_status_disable;
960 hr_utility.set_location(' Leaving:' || l_proc, 30);
961 when hr_api.validate_enabled then
962 --
963 -- As the Validate_Enabled exception has been raised
964 -- we must rollback to the savepoint
965 --
966 rollback to update_competence_element_swi;
967 --
968 -- Reset IN OUT parameters and set OUT parameters
969 --
970 p_object_version_number := l_object_version_number;
971 p_return_status := hr_multi_message.get_return_status_disable;
972 hr_utility.set_location(' Leaving:' || l_proc, 35);
973 when others then
974 --
975 -- When Multiple Message Detection is enabled catch
976 -- any Application specific or other unexpected
977 -- exceptions. Adding appropriate details to the
978 -- Multiple Message List. Otherwise re-raise the
979 -- error.
980 --
981 rollback to update_competence_element_swi;
982 if hr_multi_message.unexpected_error_add(l_proc) then
983 hr_utility.set_location(' Leaving:' || l_proc,40);
984 raise;
985 end if;
986 --
987 -- Reset IN OUT and set OUT parameters
988 --
989 p_object_version_number := l_object_version_number;
990 p_return_status := hr_multi_message.get_return_status_disable;
991 hr_utility.set_location(' Leaving:' || l_proc,50);
992 end update_competence_element;
993 -- ----------------------------------------------------------------------------
994 -- |------------------------< update_delivered_dates >------------------------|
995 -- ----------------------------------------------------------------------------
996 PROCEDURE update_delivered_dates
997 (p_activity_version_id in number
998 ,p_old_start_date in date
999 ,p_start_date in date
1000 ,p_old_end_date in date
1001 ,p_end_date in date
1002 ,p_validate in number default hr_api.g_false_num
1003 ,p_return_status out nocopy varchar2
1004 ) is
1005 --
1006 -- Variables for API Boolean parameters
1007 l_validate boolean;
1008 --
1009 -- Variables for IN/OUT parameters
1010 --
1011 -- Other variables
1012 l_proc varchar2(72) := g_package ||'update_delivered_dates';
1013 Begin
1014 hr_utility.set_location(' Entering:' || l_proc,10);
1015 --
1016 -- Issue a savepoint
1017 --
1018 savepoint update_delivered_dates_swi;
1019 --
1020 -- Initialise Multiple Message Detection
1021 --
1022 hr_multi_message.enable_message_list;
1023 --
1024 -- Remember IN OUT parameter IN values
1025 --
1026 --
1027 -- Convert constant values to their corresponding boolean value
1028 --
1029 l_validate :=
1030 hr_api.constant_to_boolean
1031 (p_constant_value => p_validate);
1032 --
1033 -- Register Surrogate ID or user key values
1034 --
1035 --
1036 -- Call API
1037 --
1038 hr_competence_element_api.update_delivered_dates
1039 (p_activity_version_id => p_activity_version_id
1040 ,p_old_start_date => p_old_start_date
1041 ,p_start_date => p_start_date
1042 ,p_old_end_date => p_old_end_date
1043 ,p_end_date => p_end_date
1044 ,p_validate => l_validate
1045 );
1046 --
1047 -- Convert API warning boolean parameter values to specific
1048 -- messages and add them to Multiple Message List
1049 --
1050 --
1051 -- Convert API non-warning boolean parameter values
1052 --
1053 --
1054 -- Derive the API return status value based on whether
1055 -- messages of any type exist in the Multiple Message List.
1056 -- Also disable Multiple Message Detection.
1057 --
1058 p_return_status := hr_multi_message.get_return_status_disable;
1059 hr_utility.set_location(' Leaving:' || l_proc,20);
1060 --
1061 exception
1062 when hr_multi_message.error_message_exist then
1063 --
1064 -- Catch the Multiple Message List exception which
1065 -- indicates API processing has been aborted because
1066 -- at least one message exists in the list.
1067 --
1068 rollback to update_delivered_dates_swi;
1069 --
1070 -- Reset IN OUT parameters and set OUT parameters
1071 --
1072 p_return_status := hr_multi_message.get_return_status_disable;
1073 hr_utility.set_location(' Leaving:' || l_proc, 30);
1074 when others then
1075 --
1076 -- When Multiple Message Detection is enabled catch
1077 -- any Application specific or other unexpected
1078 -- exceptions. Adding appropriate details to the
1079 -- Multiple Message List. Otherwise re-raise the
1080 -- error.
1081 --
1082 rollback to update_delivered_dates_swi;
1083 if hr_multi_message.unexpected_error_add(l_proc) then
1084 hr_utility.set_location(' Leaving:' || l_proc,40);
1085 raise;
1086 end if;
1087 --
1088 -- Reset IN OUT and set OUT parameters
1089 --
1090 p_return_status := hr_multi_message.get_return_status_disable;
1091 hr_utility.set_location(' Leaving:' || l_proc,50);
1092 end update_delivered_dates;
1093 -- ----------------------------------------------------------------------------
1094 -- |---------------------< update_personal_comp_element >---------------------|
1095 -- ----------------------------------------------------------------------------
1096 PROCEDURE update_personal_comp_element
1097 (p_competence_element_id in number
1098 ,p_object_version_number in out nocopy number
1099 ,p_proficiency_level_id in number default hr_api.g_number
1100 ,p_effective_date_from in date default hr_api.g_date
1101 ,p_effective_date_to in date default hr_api.g_date
1102 ,p_source_of_proficiency_level in varchar2 default hr_api.g_varchar2
1103 ,p_certification_date in date default hr_api.g_date
1104 ,p_certification_method in varchar2 default hr_api.g_varchar2
1105 ,p_next_certification_date in date default hr_api.g_date
1106 ,p_comments in varchar2 default hr_api.g_varchar2
1107 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1108 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1109 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1110 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1111 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1112 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1113 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1114 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1115 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1116 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1117 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1118 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1119 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1120 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1121 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1122 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1123 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1124 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1125 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1126 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1127 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1128 ,p_effective_date in date
1129 ,p_validate in number default hr_api.g_false_num
1130 ,p_ins_ovn out nocopy number
1131 ,p_ins_comp_id out nocopy number
1132 ,p_return_status out nocopy varchar2
1133 ) is
1134 --
1135 -- Variables for API Boolean parameters
1136 l_validate boolean;
1137 --
1138 -- Variables for IN/OUT parameters
1139 l_object_version_number number;
1140 --
1141 -- Other variables
1142 l_proc varchar2(72) := g_package ||'update_personal_comp_element';
1143 Begin
1144 hr_utility.set_location(' Entering:' || l_proc,10);
1145 --
1146 -- Issue a savepoint
1147 --
1148 savepoint update_personal_comp_ele_swi;
1149 --
1150 -- Initialise Multiple Message Detection
1151 --
1152 hr_multi_message.enable_message_list;
1153 --
1154 -- Remember IN OUT parameter IN values
1155 --
1156 l_object_version_number := p_object_version_number;
1157 --
1158 -- Convert constant values to their corresponding boolean value
1159 --
1160 l_validate :=
1161 hr_api.constant_to_boolean
1162 (p_constant_value => p_validate);
1163 --
1164 -- Register Surrogate ID or user key values
1165 --
1166 --
1167 -- Call API
1168 --
1169 hr_competence_element_api.update_personal_comp_element
1170 (p_competence_element_id => p_competence_element_id
1171 ,p_object_version_number => l_object_version_number
1172 ,p_proficiency_level_id => p_proficiency_level_id
1173 ,p_effective_date_from => p_effective_date_from
1174 ,p_effective_date_to => p_effective_date_to
1175 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1176 ,p_certification_date => p_certification_date
1177 ,p_certification_method => p_certification_method
1178 ,p_next_certification_date => p_next_certification_date
1179 ,p_comments => p_comments
1180 ,p_attribute_category => p_attribute_category
1181 ,p_attribute1 => p_attribute1
1182 ,p_attribute2 => p_attribute2
1183 ,p_attribute3 => p_attribute3
1184 ,p_attribute4 => p_attribute4
1185 ,p_attribute5 => p_attribute5
1186 ,p_attribute6 => p_attribute6
1187 ,p_attribute7 => p_attribute7
1188 ,p_attribute8 => p_attribute8
1189 ,p_attribute9 => p_attribute9
1190 ,p_attribute10 => p_attribute10
1191 ,p_attribute11 => p_attribute11
1192 ,p_attribute12 => p_attribute12
1193 ,p_attribute13 => p_attribute13
1194 ,p_attribute14 => p_attribute14
1195 ,p_attribute15 => p_attribute15
1196 ,p_attribute16 => p_attribute16
1197 ,p_attribute17 => p_attribute17
1198 ,p_attribute18 => p_attribute18
1199 ,p_attribute19 => p_attribute19
1200 ,p_attribute20 => p_attribute20
1201 ,p_effective_date => p_effective_date
1202 ,p_validate => l_validate
1203 ,p_ins_ovn => p_ins_ovn
1204 ,p_ins_comp_id => p_ins_comp_id
1205 );
1206 --
1207 -- Convert API warning boolean parameter values to specific
1208 -- messages and add them to Multiple Message List
1209 --
1210 --
1211 -- Convert API non-warning boolean parameter values
1212 --
1213 --
1214 -- Derive the API return status value based on whether
1215 -- messages of any type exist in the Multiple Message List.
1216 -- Also disable Multiple Message Detection.
1217 --
1218 p_return_status := hr_multi_message.get_return_status_disable;
1219 hr_utility.set_location(' Leaving:' || l_proc,20);
1220 --
1221 exception
1222 when hr_multi_message.error_message_exist then
1223 --
1224 -- Catch the Multiple Message List exception which
1225 -- indicates API processing has been aborted because
1226 -- at least one message exists in the list.
1227 --
1228 rollback to update_personal_comp_ele_swi;
1229 --
1230 -- Reset IN OUT parameters and set OUT parameters
1231 --
1232 p_object_version_number := l_object_version_number;
1233 p_ins_ovn := null;
1234 p_ins_comp_id := null;
1235 p_return_status := hr_multi_message.get_return_status_disable;
1236 hr_utility.set_location(' Leaving:' || l_proc, 30);
1237 when others then
1238 --
1239 -- When Multiple Message Detection is enabled catch
1240 -- any Application specific or other unexpected
1241 -- exceptions. Adding appropriate details to the
1242 -- Multiple Message List. Otherwise re-raise the
1243 -- error.
1244 --
1245 rollback to update_personal_comp_ele_swi;
1246 if hr_multi_message.unexpected_error_add(l_proc) then
1247 hr_utility.set_location(' Leaving:' || l_proc,40);
1248 raise;
1249 end if;
1250 --
1251 -- Reset IN OUT and set OUT parameters
1252 --
1253 p_object_version_number := l_object_version_number;
1254 p_ins_ovn := null;
1255 p_ins_comp_id := null;
1256 p_return_status := hr_multi_message.get_return_status_disable;
1257 hr_utility.set_location(' Leaving:' || l_proc,50);
1258 end update_personal_comp_element;
1259
1260 -- ----------------------------------------------------------------------------
1261 -- |---------------------------< process_api >--------------------------------|
1262 -- ----------------------------------------------------------------------------
1263
1264 Procedure process_api
1265 ( p_document in CLOB
1266 ,p_return_status out nocopy VARCHAR2
1267 ,p_validate in number default hr_api.g_false_num
1268 ,p_effective_date in date default null
1269 )
1270 IS
1271 l_postState VARCHAR2(2);
1272 l_return_status VARCHAR2(1);
1273 l_commitElement xmldom.DOMElement;
1274 l_object_version_number number;
1275 l_parser xmlparser.Parser;
1276 l_CommitNode xmldom.DOMNode;
1277 l_proc varchar2(72) := g_package || 'process_api';
1278 l_effective_date date := trunc(sysdate);
1279
1280 --
1281 -- SSHR Attachment feature changes : 8691102
1282 --
1283 CURSOR get_person_for_comp_element
1284 (p_comp_elt_id IN NUMBER)
1285 IS
1286 select person_id from per_competence_elements
1287 where competence_element_id = p_comp_elt_id;
1288
1289 l_person_id NUMBER;
1290 l_attach_status varchar2(80);
1291
1292 BEGIN
1293
1294 hr_utility.set_location(' Entering:' || l_proc,10);
1295 hr_utility.set_location(' CLOB --> xmldom.DOMNode:' || l_proc,15);
1296 l_parser := xmlparser.newParser;
1297 xmlparser.ParseCLOB(l_parser,p_document);
1298 l_CommitNode := xmldom.makeNode(xmldom.getDocumentElement(xmlparser.getDocument(l_parser)));
1299
1300 hr_utility.set_location('Extracting the PostState:' || l_proc,20);
1301
1302 l_commitElement := xmldom.makeElement(l_CommitNode);
1303 l_postState := xmldom.getAttribute(l_commitElement, 'PS');
1304 l_object_version_number := hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectVersionNumber');
1305 --
1306 if p_effective_date is null
1307 then
1308 --
1309 l_effective_date := trunc(sysdate);
1310 --
1311 else
1312 --
1313 l_effective_date := p_effective_date;
1314 --
1315 end if;
1316 --
1317 if l_postState = '0' then
1318
1319 create_competence_element
1320 ( p_validate => p_validate
1321 ,p_competence_element_id => hr_transaction_swi.getNumberValue(l_CommitNode,'CompetenceElementId',null)
1322 ,p_object_version_number => l_object_version_number
1323 ,p_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Type',null)
1324 ,p_business_group_id => hr_transaction_swi.getNumberValue(l_CommitNode,'BusinessGroupId',null)
1325 ,p_enterprise_id => hr_transaction_swi.getNumberValue(l_CommitNode,'EnterpriseId',null)
1326 ,p_competence_id => hr_transaction_swi.getNumberValue(l_CommitNode,'CompetenceId',null)
1327 ,p_proficiency_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ProficiencyLevelId',null)
1328 ,p_high_proficiency_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'HighProficiencyLevelId',null)
1329 ,p_weighting_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'WeightingLevelId',null)
1330 ,p_rating_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RatingLevelId',null)
1331 ,p_person_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PersonId',null)
1332 ,p_job_id => hr_transaction_swi.getNumberValue(l_CommitNode,'JobId',null)
1333 ,p_valid_grade_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ValidGradeId',null)
1334 ,p_position_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PositionId',null)
1335 ,p_organization_id => hr_transaction_swi.getNumberValue(l_CommitNode,'OrganizationId',null)
1336 ,p_parent_competence_element_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ParentCompetenceElementId',null)
1337 ,p_activity_version_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ActivityVersionId',null)
1338 ,p_assessment_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssessmentId',null)
1339 ,p_assessment_type_id => hr_transaction_swi.getNumberValue(l_CommitNode,'AssessmentTypeId',null)
1340 ,p_mandatory => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Mandatory',null)
1341 ,p_effective_date_from => hr_transaction_swi.getDateValue(l_CommitNode,'EffectiveDateFrom',null)
1342 ,p_effective_date_to => hr_transaction_swi.getDateValue(l_CommitNode,'EffectiveDateTo',null)
1343 ,p_group_competence_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'GroupCompetenceType',null)
1344 ,p_competence_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'CompetenceType',null)
1345 ,p_normal_elapse_duration => hr_transaction_swi.getNumberValue(l_CommitNode,'NormalElapseDuration',null)
1346 ,p_normal_elapse_duration_unit => hr_transaction_swi.getVarchar2Value(l_CommitNode,'NormalElapseDurationUnit',null)
1347 ,p_sequence_number => hr_transaction_swi.getNumberValue(l_CommitNode,'SequenceNumber',null)
1348 ,p_source_of_proficiency_level => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SourceOfProficiencyLevel',null)
1349 ,p_line_score => hr_transaction_swi.getNumberValue(l_CommitNode,'LineScore',null)
1350 ,p_certification_date => hr_transaction_swi.getDateValue(l_CommitNode,'CertificationDate',null)
1351 ,p_certification_method => hr_transaction_swi.getVarchar2Value(l_CommitNode,'CertificationMethod',null)
1352 ,p_next_certification_date => hr_transaction_swi.getDateValue(l_CommitNode,'NextCertificationDate',null)
1353 ,p_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Comments',null)
1354 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory',null)
1355 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1',null)
1356 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2',null)
1357 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3',null)
1358 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4',null)
1359 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5',null)
1360 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6',null)
1361 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7',null)
1362 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8',null)
1363 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9',null)
1364 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10',null)
1365 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11',null)
1366 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12',null)
1367 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13',null)
1368 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14',null)
1369 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15',null)
1370 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16',null)
1371 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17',null)
1372 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18',null)
1373 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19',null)
1374 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20',null)
1375 ,p_effective_date => l_effective_date
1376 ,p_object_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ObjectId',null)
1377 ,p_object_name => hr_transaction_swi.getVarchar2Value(l_CommitNode,'ObjectName',null)
1378 ,p_party_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PartyId',null)
1379 ,p_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Status',null)
1380 ,p_return_status => l_return_status
1381 );
1382
1383 elsif l_postState = '2' then
1384
1385
1386 update_competence_element
1387 (p_competence_element_id => hr_transaction_swi.getNumberValue(l_CommitNode,'CompetenceElementId')
1388 ,p_object_version_number => l_object_version_number
1389 ,p_competence_id => hr_transaction_swi.getNumberValue(l_CommitNode,'CompetenceId')
1390 ,p_proficiency_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'ProficiencyLevelId')
1391 ,p_high_proficiency_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'HighProficiencyLevelId')
1392 ,p_weighting_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'WeightingLevelId')
1393 ,p_rating_level_id => hr_transaction_swi.getNumberValue(l_CommitNode,'RatingLevelId')
1394 ,p_mandatory => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Mandatory')
1395 ,p_effective_date_from => hr_transaction_swi.getDateValue(l_CommitNode,'EffectiveDateFrom')
1396 ,p_effective_date_to => hr_transaction_swi.getDateValue(l_CommitNode,'EffectiveDateTo')
1397 ,p_group_competence_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'GroupCompetenceType')
1398 ,p_competence_type => hr_transaction_swi.getVarchar2Value(l_CommitNode,'CompetenceType')
1399 ,p_normal_elapse_duration => hr_transaction_swi.getNumberValue(l_CommitNode,'NormalElapseDuration')
1400 ,p_normal_elapse_duration_unit => hr_transaction_swi.getVarchar2Value(l_CommitNode,'NormalElapseDurationUnit')
1401 ,p_sequence_number => hr_transaction_swi.getNumberValue(l_CommitNode,'SequenceNumber')
1402 ,p_source_of_proficiency_level => hr_transaction_swi.getVarchar2Value(l_CommitNode,'SourceOfProficiencyLevel')
1403 ,p_line_score => hr_transaction_swi.getNumberValue(l_CommitNode,'LineScore')
1404 ,p_certification_date => hr_transaction_swi.getDateValue(l_CommitNode,'CertificationDate')
1405 ,p_certification_method => hr_transaction_swi.getVarchar2Value(l_CommitNode,'CertificationMethod')
1406 ,p_next_certification_date => hr_transaction_swi.getDateValue(l_CommitNode,'NextCertificationDate')
1407 ,p_comments => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Comments')
1408 ,p_attribute_category => hr_transaction_swi.getVarchar2Value(l_CommitNode,'AttributeCategory')
1409 ,p_attribute1 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute1')
1410 ,p_attribute2 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute2')
1411 ,p_attribute3 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute3')
1412 ,p_attribute4 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute4')
1413 ,p_attribute5 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute5')
1414 ,p_attribute6 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute6')
1415 ,p_attribute7 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute7')
1416 ,p_attribute8 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute8')
1417 ,p_attribute9 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute9')
1418 ,p_attribute10 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute10')
1419 ,p_attribute11 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute11')
1420 ,p_attribute12 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute12')
1421 ,p_attribute13 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute13')
1422 ,p_attribute14 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute14')
1423 ,p_attribute15 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute15')
1424 ,p_attribute16 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute16')
1425 ,p_attribute17 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute17')
1426 ,p_attribute18 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute18')
1427 ,p_attribute19 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute19')
1428 ,p_attribute20 => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Attribute20')
1429 ,p_effective_date => l_effective_date
1430 ,p_validate => p_validate
1431 ,p_party_id => hr_transaction_swi.getNumberValue(l_CommitNode,'PartyId')
1432 ,p_status => hr_transaction_swi.getVarchar2Value(l_CommitNode,'Status',null)
1433 ,p_return_status => l_return_status
1434 ,p_datetrack_update_mode => hr_transaction_swi.getVarchar2Value(l_CommitNode,'DatetrackUpdate',hr_api.g_correction)
1435 );
1436
1437 elsif l_postState = '3' then
1438
1439 delete_competence_element
1440 ( p_validate => p_validate
1441 ,p_competence_element_id => hr_transaction_swi.getNumberValue(l_CommitNode,'CompetenceElementId')
1442 ,p_object_version_number => l_object_version_number
1443 ,p_return_status => l_return_status
1444 );
1445
1446
1447
1448 end if;
1449 p_return_status := l_return_status;
1450
1451 --
1452 -- SSHR Attachment feature changes : 8691102
1453 OPEN get_person_for_comp_element(hr_transaction_swi.getNumberValue(l_CommitNode,'CompetenceElementId'));
1454 FETCH get_person_for_comp_element INTO l_person_id;
1455 CLOSE get_person_for_comp_element;
1456
1457 hr_utility.set_location('merge_attachments Start : l_person_id = ' || l_person_id || ' ' ||l_proc, 30);
1458
1459 HR_UTIL_MISC_SS.merge_attachments( p_dest_entity_name => 'PER_PEOPLE_F'
1460 ,p_dest_pk1_value => l_person_id
1461 ,p_return_status => l_attach_status);
1462
1463 hr_utility.set_location('merge_attachments End: l_attach_status = ' || l_attach_status || ' ' ||l_proc, 35);
1464
1465 hr_utility.set_location('Exiting:' || l_proc,40);
1466
1467 END process_api;
1468 end hr_competence_element_swi;