[Home] [Help]
PACKAGE BODY: APPS.HR_COMPETENCE_OUTCOME_API
Source
1 Package Body hr_competence_outcome_api as
2 /* $Header: pecpoapi.pkb 115.3 2004/03/31 13:54 ynegoro noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_competence_outcome_api.';
7 --
8 --
9 -- ----------------------------------------------------------------------------
10 -- |------------------------< create_outcome >---------------------------------|
11 -- ----------------------------------------------------------------------------
12 procedure create_outcome
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_language_code in varchar2 default hr_api.userenv_lang
16 ,p_competence_id in number
17 ,p_outcome_number in number
18 ,p_name in varchar2
19 ,p_date_from in date
20 ,p_date_to in date default null
21 ,p_assessment_criteria in varchar2 default null
22 ,p_attribute_category in varchar2 default null
23 ,p_attribute1 in varchar2 default null
24 ,p_attribute2 in varchar2 default null
25 ,p_attribute3 in varchar2 default null
26 ,p_attribute4 in varchar2 default null
27 ,p_attribute5 in varchar2 default null
28 ,p_attribute6 in varchar2 default null
29 ,p_attribute7 in varchar2 default null
30 ,p_attribute8 in varchar2 default null
31 ,p_attribute9 in varchar2 default null
32 ,p_attribute10 in varchar2 default null
33 ,p_attribute11 in varchar2 default null
34 ,p_attribute12 in varchar2 default null
35 ,p_attribute13 in varchar2 default null
36 ,p_attribute14 in varchar2 default null
37 ,p_attribute15 in varchar2 default null
38 ,p_attribute16 in varchar2 default null
39 ,p_attribute17 in varchar2 default null
40 ,p_attribute18 in varchar2 default null
41 ,p_attribute19 in varchar2 default null
42 ,p_attribute20 in varchar2 default null
43 ,p_information_category in varchar2 default null
44 ,p_information1 in varchar2 default null
45 ,p_information2 in varchar2 default null
46 ,p_information3 in varchar2 default null
47 ,p_information4 in varchar2 default null
48 ,p_information5 in varchar2 default null
49 ,p_information6 in varchar2 default null
50 ,p_information7 in varchar2 default null
51 ,p_information8 in varchar2 default null
52 ,p_information9 in varchar2 default null
53 ,p_information10 in varchar2 default null
54 ,p_information11 in varchar2 default null
55 ,p_information12 in varchar2 default null
56 ,p_information13 in varchar2 default null
57 ,p_information14 in varchar2 default null
58 ,p_information15 in varchar2 default null
59 ,p_information16 in varchar2 default null
60 ,p_information17 in varchar2 default null
61 ,p_information18 in varchar2 default null
62 ,p_information19 in varchar2 default null
63 ,p_information20 in varchar2 default null
64 ,p_outcome_id out nocopy number
65 ,p_object_version_number out nocopy number
66 ) is
67
68 --
69 -- Declare cursors and local variables
70 --
71
72 l_proc varchar2(72) := g_package||'create_outcome';
73 l_effective_date date;
74 l_language_code per_competence_outcomes_tl.language%TYPE;
75
76 --
77 -- Declare out parameters
78 --
79 l_outcome_id per_competence_outcomes.outcome_id%TYPE;
80 l_object_version_number per_competence_outcomes.object_version_number%TYPE;
81 --
82 begin
83 --
84 hr_utility.set_location('Entering:'|| l_proc, 10);
85
86 l_outcome_id := p_outcome_id;
87
88 --
89 -- Issue a savepoint
90 --
91 savepoint create_outcome;
92
93 hr_utility.set_location(l_proc, 20);
94
95 --
96 -- Truncate the time portion from all IN date parameters
97 --
98 l_effective_date := trunc(p_effective_date);
99
100 --
101 -- Call Before Process User hook for create_competence_outcome
102 --
103 begin
104 hr_competence_outcome_bk1.create_outcome_b
105 (p_effective_date => l_effective_date
106 ,p_language_code => p_language_code
107 ,p_competence_id => p_competence_id
108 ,p_outcome_number => p_outcome_number
109 ,p_name => p_name
110 ,p_date_from => p_date_from
111 ,p_date_to => p_date_to
112 ,p_assessment_criteria => p_assessment_criteria
113 ,p_attribute_category => p_attribute_category
114 ,p_attribute1 => p_attribute1
115 ,p_attribute2 => p_attribute2
116 ,p_attribute3 => p_attribute3
117 ,p_attribute4 => p_attribute4
118 ,p_attribute5 => p_attribute5
119 ,p_attribute6 => p_attribute6
120 ,p_attribute7 => p_attribute7
121 ,p_attribute8 => p_attribute8
122 ,p_attribute9 => p_attribute9
123 ,p_attribute10 => p_attribute10
124 ,p_attribute11 => p_attribute11
125 ,p_attribute12 => p_attribute12
126 ,p_attribute13 => p_attribute13
127 ,p_attribute14 => p_attribute14
128 ,p_attribute15 => p_attribute15
129 ,p_attribute16 => p_attribute16
130 ,p_attribute17 => p_attribute17
131 ,p_attribute18 => p_attribute18
132 ,p_attribute19 => p_attribute19
133 ,p_attribute20 => p_attribute20
134 ,p_information_category => p_information_category
135 ,p_information1 => p_information1
136 ,p_information2 => p_information2
137 ,p_information3 => p_information3
138 ,p_information4 => p_information4
139 ,p_information5 => p_information5
140 ,p_information6 => p_information6
141 ,p_information7 => p_information7
142 ,p_information8 => p_information8
143 ,p_information9 => p_information9
144 ,p_information10 => p_information10
145 ,p_information11 => p_information11
146 ,p_information12 => p_information12
147 ,p_information13 => p_information13
148 ,p_information14 => p_information14
149 ,p_information15 => p_information15
150 ,p_information16 => p_information16
151 ,p_information17 => p_information17
152 ,p_information18 => p_information18
153 ,p_information19 => p_information19
154 ,p_information20 => p_information20
155 );
156 exception
157 when hr_api.cannot_find_prog_unit then
158 hr_api.cannot_find_prog_unit_error
159 (p_module_name => 'CREATE_OUTCOME'
160 ,p_hook_type => 'BP'
161 );
162 --
163 -- End of before hook process (create_outcome)
164 --
165 end;
166
167 hr_utility.set_location(l_proc, 30);
168 --
169 -- Process Logic
170 --
171
172 --
173 -- Validate the language parameter. l_language_code should be passed to
174 -- functions instead of p_language_code from now on, to allow an IN OUT
175 -- parameter to be passed through.
176 --
177 l_language_code := p_language_code;
178 hr_api.validate_language_code(p_language_code => l_language_code);
179
180 hr_utility.set_location(l_proc, 40);
181
182 --
183 -- Insert competence outcome
184 --
185 --
186 per_cpo_ins.ins
187 (p_effective_date => l_effective_date
188 ,p_competence_id => p_competence_id
189 ,p_outcome_number => p_outcome_number
190 ,p_name => p_name
191 ,p_date_from => p_date_from
192 ,p_date_to => p_date_to
193 ,p_assessment_criteria => p_assessment_criteria
194 ,p_attribute_category => p_attribute_category
195 ,p_attribute1 => p_attribute1
196 ,p_attribute2 => p_attribute2
197 ,p_attribute3 => p_attribute3
198 ,p_attribute4 => p_attribute4
199 ,p_attribute5 => p_attribute5
200 ,p_attribute6 => p_attribute6
201 ,p_attribute7 => p_attribute7
202 ,p_attribute8 => p_attribute8
203 ,p_attribute9 => p_attribute9
204 ,p_attribute10 => p_attribute10
205 ,p_attribute11 => p_attribute11
206 ,p_attribute12 => p_attribute12
207 ,p_attribute13 => p_attribute13
208 ,p_attribute14 => p_attribute14
209 ,p_attribute15 => p_attribute15
210 ,p_attribute16 => p_attribute16
211 ,p_attribute17 => p_attribute17
212 ,p_attribute18 => p_attribute18
213 ,p_attribute19 => p_attribute19
214 ,p_attribute20 => p_attribute20
215 ,p_information_category => p_information_category
216 ,p_information1 => p_information1
217 ,p_information2 => p_information2
218 ,p_information3 => p_information3
219 ,p_information4 => p_information4
220 ,p_information5 => p_information5
221 ,p_information6 => p_information6
222 ,p_information7 => p_information7
223 ,p_information8 => p_information8
224 ,p_information9 => p_information9
225 ,p_information10 => p_information10
226 ,p_information11 => p_information11
227 ,p_information12 => p_information12
228 ,p_information13 => p_information13
229 ,p_information14 => p_information14
230 ,p_information15 => p_information15
231 ,p_information16 => p_information16
232 ,p_information17 => p_information17
233 ,p_information18 => p_information18
234 ,p_information19 => p_information19
235 ,p_information20 => p_information20
236 ,p_outcome_id => l_outcome_id
237 ,p_object_version_number => l_object_version_number
238 );
239
240 hr_utility.set_location(l_proc, 50);
241
242 --
243 -- Insert translatable rows in per_competence_outcomes_tl table
244 --
245 per_cot_ins.ins_tl
246 (p_outcome_id => l_outcome_id
247 ,p_language_code => l_language_code
248 ,p_name => p_name
249 ,p_assessment_criteria => p_assessment_criteria
250 );
251 --
252 hr_utility.set_location(l_proc, 60);
253
254 --
255 --
256 -- Call After Process hook for create_outcome
257 --
258
259 begin
260 hr_competence_outcome_bk1.create_outcome_a
261 (p_effective_date => l_effective_date
262 ,p_language_code => p_language_code
263 ,p_outcome_id => p_outcome_id
264 ,p_competence_id => p_competence_id
265 ,p_outcome_number => p_outcome_number
266 ,p_name => p_name
267 ,p_date_from => p_date_from
268 ,p_date_to => p_date_to
269 ,p_assessment_criteria => p_assessment_criteria
270 ,p_attribute_category => p_attribute_category
271 ,p_attribute1 => p_attribute1
272 ,p_attribute2 => p_attribute2
273 ,p_attribute3 => p_attribute3
274 ,p_attribute4 => p_attribute4
275 ,p_attribute5 => p_attribute5
276 ,p_attribute6 => p_attribute6
277 ,p_attribute7 => p_attribute7
278 ,p_attribute8 => p_attribute8
279 ,p_attribute9 => p_attribute9
280 ,p_attribute10 => p_attribute10
281 ,p_attribute11 => p_attribute11
282 ,p_attribute12 => p_attribute12
283 ,p_attribute13 => p_attribute13
284 ,p_attribute14 => p_attribute14
285 ,p_attribute15 => p_attribute15
286 ,p_attribute16 => p_attribute16
287 ,p_attribute17 => p_attribute17
288 ,p_attribute18 => p_attribute18
289 ,p_attribute19 => p_attribute19
290 ,p_attribute20 => p_attribute20
291 ,p_information_category => p_information_category
292 ,p_information1 => p_information1
293 ,p_information2 => p_information2
294 ,p_information3 => p_information3
295 ,p_information4 => p_information4
296 ,p_information5 => p_information5
297 ,p_information6 => p_information6
298 ,p_information7 => p_information7
299 ,p_information8 => p_information8
300 ,p_information9 => p_information9
301 ,p_information10 => p_information10
302 ,p_information11 => p_information11
303 ,p_information12 => p_information12
304 ,p_information13 => p_information13
305 ,p_information14 => p_information14
306 ,p_information15 => p_information15
307 ,p_information16 => p_information16
308 ,p_information17 => p_information17
309 ,p_information18 => p_information18
310 ,p_information19 => p_information19
311 ,p_information20 => p_information20
312 ,p_object_version_number => l_object_version_number
313 );
314 exception
315 when hr_api.cannot_find_prog_unit then
316 hr_api.cannot_find_prog_unit_error
317 (p_module_name => 'CREATE_OUTCOME'
318 ,p_hook_type => 'AP'
319 );
320 --
321 -- End of after hook process (create_outcome)
322 --
323 end;
324
325 hr_utility.set_location(l_proc, 70);
326
327 --
328 -- When in validation only mode raise the Validate_Enabled exception
329 --
330 if p_validate
331 then
332 raise hr_api.validate_enabled;
333 end if;
334 --
335 hr_utility.set_location(l_proc, 80);
336 --
337 -- Set OUT parameters
338 --
339 p_outcome_id := l_outcome_id;
340 p_object_version_number := l_object_version_number;
341 --
342 hr_utility.set_location(' Leaving:' ||l_proc, 90);
343 --
344 exception
345 --
346 when hr_api.validate_enabled then
347 --
348 -- As the Validate_Enabled exception has been raised
349 -- we must rollback to the savepoint
350 --
351 ROLLBACK TO create_outcome;
352 --
353 -- Set OUT parameters to null
354 -- Only set output warning arguments
355 -- (Any key or derived arguments must be set to null
356 -- when validation only mode is being used.)
357 --
358 p_outcome_id := null;
359 p_object_version_number := null;
360 --
361 hr_utility.set_location(' Leaving:'||l_proc, 100);
362 --
363 when others then
364 --
365 -- A validation or unexpected error has occurred
366 --
367 ROLLBACK TO create_outcome;
368 --
369 hr_utility.set_location(' Leaving:'||l_proc, 110);
370 --
371 p_outcome_id := null;
372 p_object_version_number := null;
373 --
374 raise;
375 --
376 end create_outcome;
377 --
378 -- ----------------------------------------------------------------------------
379 -- |-----------------------< update_outcome >----------------------------------|
380 -- ----------------------------------------------------------------------------
381 --
382 procedure update_outcome
383 (p_validate in boolean default false
384 ,p_effective_date in date
385 ,p_language_code in varchar2 default hr_api.userenv_lang
386 ,p_outcome_id in number
387 ,p_object_version_number in out nocopy number
388 ,p_competence_id in number default hr_api.g_number
389 ,p_outcome_number in number default hr_api.g_number
390 ,p_name in varchar2 default hr_api.g_varchar2
391 ,p_date_from in date default hr_api.g_date
392 ,p_date_to in date default hr_api.g_date
393 ,p_assessment_criteria in varchar2 default hr_api.g_varchar2
394 ,p_attribute_category in varchar2 default hr_api.g_varchar2
395 ,p_attribute1 in varchar2 default hr_api.g_varchar2
396 ,p_attribute2 in varchar2 default hr_api.g_varchar2
397 ,p_attribute3 in varchar2 default hr_api.g_varchar2
398 ,p_attribute4 in varchar2 default hr_api.g_varchar2
399 ,p_attribute5 in varchar2 default hr_api.g_varchar2
400 ,p_attribute6 in varchar2 default hr_api.g_varchar2
401 ,p_attribute7 in varchar2 default hr_api.g_varchar2
402 ,p_attribute8 in varchar2 default hr_api.g_varchar2
403 ,p_attribute9 in varchar2 default hr_api.g_varchar2
404 ,p_attribute10 in varchar2 default hr_api.g_varchar2
405 ,p_attribute11 in varchar2 default hr_api.g_varchar2
406 ,p_attribute12 in varchar2 default hr_api.g_varchar2
407 ,p_attribute13 in varchar2 default hr_api.g_varchar2
408 ,p_attribute14 in varchar2 default hr_api.g_varchar2
409 ,p_attribute15 in varchar2 default hr_api.g_varchar2
410 ,p_attribute16 in varchar2 default hr_api.g_varchar2
411 ,p_attribute17 in varchar2 default hr_api.g_varchar2
412 ,p_attribute18 in varchar2 default hr_api.g_varchar2
413 ,p_attribute19 in varchar2 default hr_api.g_varchar2
414 ,p_attribute20 in varchar2 default hr_api.g_varchar2
415 ,p_information_category in varchar2 default hr_api.g_varchar2
416 ,p_information1 in varchar2 default hr_api.g_varchar2
417 ,p_information2 in varchar2 default hr_api.g_varchar2
418 ,p_information3 in varchar2 default hr_api.g_varchar2
419 ,p_information4 in varchar2 default hr_api.g_varchar2
420 ,p_information5 in varchar2 default hr_api.g_varchar2
421 ,p_information6 in varchar2 default hr_api.g_varchar2
422 ,p_information7 in varchar2 default hr_api.g_varchar2
423 ,p_information8 in varchar2 default hr_api.g_varchar2
424 ,p_information9 in varchar2 default hr_api.g_varchar2
425 ,p_information10 in varchar2 default hr_api.g_varchar2
426 ,p_information11 in varchar2 default hr_api.g_varchar2
427 ,p_information12 in varchar2 default hr_api.g_varchar2
428 ,p_information13 in varchar2 default hr_api.g_varchar2
429 ,p_information14 in varchar2 default hr_api.g_varchar2
430 ,p_information15 in varchar2 default hr_api.g_varchar2
431 ,p_information16 in varchar2 default hr_api.g_varchar2
432 ,p_information17 in varchar2 default hr_api.g_varchar2
433 ,p_information18 in varchar2 default hr_api.g_varchar2
434 ,p_information19 in varchar2 default hr_api.g_varchar2
435 ,p_information20 in varchar2 default hr_api.g_varchar2
436 ) is
437
438 --
439 -- Declare cursors and local variables
440 --
441 l_proc varchar2(72) := g_package||'update_outcome';
442 l_effective_date date;
443 lv_object_version_number per_competence_outcomes.object_version_number%TYPE;
444 l_language_code per_competence_outcomes_tl.language%TYPE;
445 l_date_from per_comp_element_outcomes.date_from%TYPE;
446 l_date_to per_comp_element_outcomes.date_to%TYPE;
447 l_ceo_ovn per_comp_element_outcomes.object_version_number%TYPE;
448 l_cel_ovn per_competence_elements.object_version_number%TYPE;
449 l_boolean boolean;
450 l_competence_element_id per_competence_elements.competence_element_id%TYPE;
451 l_party_id per_competence_elements.party_id%TYPE;
452 lo_object_version_number per_competence_elements.object_version_number%TYPE;
453 l_competence_id per_competence_outcomes.competence_id%TYPE;
454 l_exists varchar2(1);
455 l_max_date_from date;
456
457 --
458 cursor csr_get_competence_element_id is
459 select competence_element_id ,party_id,object_version_number
460 from per_competence_elements
461 where competence_id = l_competence_id
462 and type = 'PERSONAL';
463
464 --
465 -- Declare out parameters
466 --
467 l_object_version_number per_competence_outcomes.object_version_number%TYPE;
468 --
469 --
470 begin
471 --
472 hr_utility.set_location('Entering:'|| l_proc, 10);
473 --
474 lv_object_version_number := p_object_version_number;
475
476 --
477 -- Issue a savepoint
478 --
479 savepoint update_outcome;
480
481 hr_utility.set_location(l_proc, 20);
482
483 --
484 -- Truncate the time portion from all IN date parameters
485 --
486 l_effective_date := trunc(p_effective_date);
487
488 --
489 -- store object version number passed in
490 --
491 l_object_version_number := p_object_version_number;
492
493 --
494 -- Call Before Process User hook for update_outcome
495 --
496 begin
497 hr_competence_outcome_bk2.update_outcome_b
498 (p_effective_date => l_effective_date
499 ,p_language_code => p_language_code
500 ,p_outcome_id => p_outcome_id
501 ,p_competence_id => p_competence_id
502 ,p_outcome_number => p_outcome_number
503 ,p_name => p_name
504 ,p_date_from => p_date_from
505 ,p_date_to => p_date_to
506 ,p_assessment_criteria => p_assessment_criteria
507 ,p_attribute_category => p_attribute_category
508 ,p_attribute1 => p_attribute1
509 ,p_attribute2 => p_attribute2
510 ,p_attribute3 => p_attribute3
511 ,p_attribute4 => p_attribute4
512 ,p_attribute5 => p_attribute5
513 ,p_attribute6 => p_attribute6
514 ,p_attribute7 => p_attribute7
515 ,p_attribute8 => p_attribute8
516 ,p_attribute9 => p_attribute9
517 ,p_attribute10 => p_attribute10
518 ,p_attribute11 => p_attribute11
519 ,p_attribute12 => p_attribute12
520 ,p_attribute13 => p_attribute13
521 ,p_attribute14 => p_attribute14
522 ,p_attribute15 => p_attribute15
523 ,p_attribute16 => p_attribute16
524 ,p_attribute17 => p_attribute17
525 ,p_attribute18 => p_attribute18
526 ,p_attribute19 => p_attribute19
527 ,p_attribute20 => p_attribute20
528 ,p_information_category => p_information_category
529 ,p_information1 => p_information1
530 ,p_information2 => p_information2
531 ,p_information3 => p_information3
532 ,p_information4 => p_information4
533 ,p_information5 => p_information5
534 ,p_information6 => p_information6
535 ,p_information7 => p_information7
536 ,p_information8 => p_information8
537 ,p_information9 => p_information9
538 ,p_information10 => p_information10
539 ,p_information11 => p_information11
540 ,p_information12 => p_information12
541 ,p_information13 => p_information13
542 ,p_information14 => p_information14
543 ,p_information15 => p_information15
544 ,p_information16 => p_information16
545 ,p_information17 => p_information17
546 ,p_information18 => p_information18
547 ,p_information19 => p_information19
548 ,p_information20 => p_information20
549 ,p_object_version_number => l_object_version_number
550 );
551 exception
552 when hr_api.cannot_find_prog_unit then
553 hr_api.cannot_find_prog_unit_error
554 (p_module_name => 'UPDATE_OUTCOME'
555 ,p_hook_type => 'BP'
556 );
557 --
558 -- End of before hook process (update_outcome)
559 --
560 end;
561
562 hr_utility.set_location(l_proc, 30);
563 --
564 -- Process Logic
565 --
566
567 --
568 -- Validate the language parameter. l_language_code should be passed to
569 -- functions instead of p_language_code from now on, to allow an IN OUT
570 -- parameter to be passed through.
571 --
572 l_language_code := p_language_code;
573 hr_api.validate_language_code(p_language_code => l_language_code);
574
575 hr_utility.set_location(l_proc, 40);
576
577 l_object_version_number := p_object_version_number;
578
579 --
580 -- Update Progression Point
581 --
582 --
583 per_cpo_upd.upd
584 (p_effective_date => l_effective_date
585 ,p_outcome_id => p_outcome_id
586 ,p_object_version_number => l_object_version_number
587 ,p_competence_id => p_competence_id
588 ,p_outcome_number => p_outcome_number
589 ,p_name => p_name
590 ,p_date_from => p_date_from
591 ,p_date_to => p_date_to
592 ,p_assessment_criteria => p_assessment_criteria
593 ,p_attribute_category => p_attribute_category
594 ,p_attribute1 => p_attribute1
595 ,p_attribute2 => p_attribute2
596 ,p_attribute3 => p_attribute3
597 ,p_attribute4 => p_attribute4
598 ,p_attribute5 => p_attribute5
599 ,p_attribute6 => p_attribute6
600 ,p_attribute7 => p_attribute7
601 ,p_attribute8 => p_attribute8
602 ,p_attribute9 => p_attribute9
603 ,p_attribute10 => p_attribute10
604 ,p_attribute11 => p_attribute11
605 ,p_attribute12 => p_attribute12
606 ,p_attribute13 => p_attribute13
607 ,p_attribute14 => p_attribute14
608 ,p_attribute15 => p_attribute15
609 ,p_attribute16 => p_attribute16
610 ,p_attribute17 => p_attribute17
611 ,p_attribute18 => p_attribute18
612 ,p_attribute19 => p_attribute19
613 ,p_attribute20 => p_attribute20
614 ,p_information_category => p_information_category
615 ,p_information1 => p_information1
616 ,p_information2 => p_information2
617 ,p_information3 => p_information3
618 ,p_information4 => p_information4
619 ,p_information5 => p_information5
620 ,p_information6 => p_information6
621 ,p_information7 => p_information7
622 ,p_information8 => p_information8
623 ,p_information9 => p_information9
624 ,p_information10 => p_information10
625 ,p_information11 => p_information11
626 ,p_information12 => p_information12
627 ,p_information13 => p_information13
628 ,p_information14 => p_information14
629 ,p_information15 => p_information15
630 ,p_information16 => p_information16
631 ,p_information17 => p_information17
632 ,p_information18 => p_information18
633 ,p_information19 => p_information19
634 ,p_information20 => p_information20
635 );
636
637 --
638 -- Assign the out parameters.
639 --
640 p_object_version_number := l_object_version_number;
641
642 hr_utility.set_location(l_proc, 50);
643
644 --
645 -- Insert translatable rows in per_competence_outcomes_tl table
646 --
647 per_cot_upd.upd_tl
648 (p_outcome_id => p_outcome_id
649 ,p_language_code => p_language_code
650 ,p_name => p_name
651 ,p_assessment_criteria => p_assessment_criteria
652 );
653 --
654 hr_utility.set_location(l_proc, 60);
655
656 --
657 --
658 -- Call After Process hook for update_competence_outcome
659 --
660
661 begin
662 hr_competence_outcome_bk2.update_outcome_a
663 (p_effective_date => l_effective_date
664 ,p_language_code => p_language_code
665 ,p_outcome_id => p_outcome_id
666 ,p_object_version_number => l_object_version_number
667 ,p_competence_id => p_competence_id
668 ,p_outcome_number => p_outcome_number
669 ,p_name => p_name
670 ,p_date_from => p_date_from
671 ,p_date_to => p_date_to
672 ,p_assessment_criteria => p_assessment_criteria
673 ,p_attribute_category => p_attribute_category
674 ,p_attribute1 => p_attribute1
675 ,p_attribute2 => p_attribute2
676 ,p_attribute3 => p_attribute3
677 ,p_attribute4 => p_attribute4
678 ,p_attribute5 => p_attribute5
679 ,p_attribute6 => p_attribute6
680 ,p_attribute7 => p_attribute7
681 ,p_attribute8 => p_attribute8
682 ,p_attribute9 => p_attribute9
683 ,p_attribute10 => p_attribute10
684 ,p_attribute11 => p_attribute11
685 ,p_attribute12 => p_attribute12
686 ,p_attribute13 => p_attribute13
687 ,p_attribute14 => p_attribute14
688 ,p_attribute15 => p_attribute15
689 ,p_attribute16 => p_attribute16
690 ,p_attribute17 => p_attribute17
691 ,p_attribute18 => p_attribute18
692 ,p_attribute19 => p_attribute19
693 ,p_attribute20 => p_attribute20
694 ,p_information_category => p_information_category
695 ,p_information1 => p_information1
696 ,p_information2 => p_information2
697 ,p_information3 => p_information3
698 ,p_information4 => p_information4
699 ,p_information5 => p_information5
700 ,p_information6 => p_information6
701 ,p_information7 => p_information7
702 ,p_information8 => p_information8
703 ,p_information9 => p_information9
704 ,p_information10 => p_information10
705 ,p_information11 => p_information11
706 ,p_information12 => p_information12
707 ,p_information13 => p_information13
708 ,p_information14 => p_information14
709 ,p_information15 => p_information15
710 ,p_information16 => p_information16
711 ,p_information17 => p_information17
712 ,p_information18 => p_information18
713 ,p_information19 => p_information19
714 ,p_information20 => p_information20
715 );
716 exception
717 when hr_api.cannot_find_prog_unit then
718 hr_api.cannot_find_prog_unit_error
719 (p_module_name => 'UPDATE_OUTCOME'
720 ,p_hook_type => 'AP'
721 );
722 --
723 -- End of after hook process (update_outcome)
724 --
725 end;
726
727 hr_utility.set_location(l_proc, 70);
728 --
729 -- If the outcome is end dated, associated outcome achieved should
730 -- change to 'IN_PROGRESS'
731 --
732 if p_date_from <> hr_api.g_date or p_date_to <> hr_api.g_date then
733 hr_utility.set_location(l_proc, 71);
734 hr_utility.trace('p_competence_id : ' || p_competence_id);
735 if (p_competence_id = hr_api.g_number) then
736 hr_utility.set_location(l_proc, 72);
737 select competence_id into l_competence_id
738 from per_competence_outcomes
739 where outcome_id = p_outcome_id;
740 else
741 hr_utility.set_location(l_proc, 73);
742 l_competence_id := p_competence_id;
743 end if;
744 open csr_get_competence_element_id;
745 loop
746 hr_utility.set_location(l_proc, 74);
747 fetch csr_get_competence_element_id into l_competence_element_id
748 ,l_party_id , lo_object_version_number;
749 exit when csr_get_competence_element_id%NOTFOUND;
750 hr_utility.trace('p_outcome_id : '|| p_outcome_id);
751 hr_utility.trace('l_comptence_element_id : '|| l_competence_element_id);
752 hr_utility.trace('l_party_id : '|| l_party_id);
753 hr_utility.trace('lo_object_version_number: '|| lo_object_version_number);
754
755 l_max_date_from := per_ceo_bus.check_outcome_achieved(
756 p_effective_date => l_effective_date
757 ,p_competence_element_id => l_competence_element_id
758 ,p_competence_id => l_competence_id
759 );
760
761 hr_utility.trace('l_max_date_from : '|| l_max_date_from);
762
763 if l_max_date_from is not NULL then
764 hr_utility.set_location(l_proc, 75);
765 hr_competence_element_api.update_competence_element
766 (p_effective_date => l_effective_date
767 ,p_competence_element_id => l_competence_element_id
768 ,p_status => 'ACHIEVED'
769 ,p_achieved_date => l_max_date_from
770 ,p_party_id => l_party_id
771 ,p_object_version_number => lo_object_version_number
772 );
773 else
774 --
775 hr_utility.set_location(l_proc, 76);
776 hr_competence_element_api.update_competence_element
777 (p_effective_date => l_effective_date
778 ,p_competence_element_id => l_competence_element_id
779 ,p_status => 'IN_PROGRESS'
780 ,p_achieved_date => NULL
781 ,p_party_id => l_party_id
782 ,p_object_version_number => lo_object_version_number
783 );
784 end if;
785 end loop;
786 close csr_get_competence_element_id;
787 end if;
788 hr_utility.set_location(l_proc, 77);
789 --
790 -- When in validation only mode raise the Validate_Enabled exception
791 --
792 if p_validate
793 then
794 raise hr_api.validate_enabled;
795 end if;
796 --
797 hr_utility.set_location(l_proc, 80);
798 --
799 -- Set OUT parameters
800 --
801 p_object_version_number := l_object_version_number;
802 --
803 hr_utility.set_location(' Leaving:' ||l_proc, 90);
804 --
805 exception
806 --
807 when hr_api.validate_enabled then
808 --
809 -- As the Validate_Enabled exception has been raised
810 -- we must rollback to the savepoint
811 --
812 rollback to update_outcome;
813 --
814 -- Set OUT parameters to null
815 -- Only set output warning arguments
816 -- (Any key or derived arguments must be set to null
817 -- when validation only mode is being used.)
818 --
819 p_object_version_number := p_object_version_number;
820 --
821 hr_utility.set_location(' Leaving:'||l_proc, 100);
822 --
823 when others then
824 --
825 -- A validation or unexpected error has occurred
826 --
827 rollback to update_outcome;
828 --
829 p_object_version_number := lv_object_version_number;
830 --
831 hr_utility.set_location(' Leaving:'||l_proc, 110);
832 --
833 raise;
834 --
835 end update_outcome;
836 --
837 --
838 -- ----------------------------------------------------------------------------
839 -- |--------------------------< delete_outcome >-------------------------------|
840 -- ----------------------------------------------------------------------------
841 --
842
843 procedure delete_outcome
844 (p_validate in boolean
845 ,p_outcome_id in number
846 ,p_object_version_number in out nocopy number
847 ) IS
848
849 --
850 -- Declare cursors and local variables
851 --
852 l_proc varchar2(72) := g_package||'delete_outcome';
853 lv_object_version_number per_competence_outcomes.object_version_number%TYPE;
854 l_competence_element_id per_competence_elements.competence_element_id%TYPE;
855 l_party_id per_competence_elements.party_id%TYPE;
856 lo_object_version_number per_competence_elements.object_version_number%TYPE;
857 l_competence_id per_competence_outcomes.competence_id%TYPE;
858 l_max_date_from date;
859
860
861 cursor csr_get_competence_element_id is
862 select competence_element_id ,party_id,object_version_number
863 from per_competence_elements
864 where competence_id = l_competence_id
865 and type = 'PERSONAL' and status = 'IN_PROGRESS';
866
867 --
868 -- Declare out variables
869 --
870 l_object_version_number per_competence_outcomes.object_version_number%TYPE;
871 --
872 begin
873 hr_utility.set_location('Entering:'|| l_proc, 10);
874
875 lv_object_version_number := p_object_version_number;
876
877 --
878 -- Issue a savepoint
879 --
880 savepoint delete_outcome;
881
882 l_object_version_number := p_object_version_number;
883
884 --
885 -- Call Before Process User Hook
886 --
887 begin
888 hr_competence_outcome_bk3.delete_outcome_b
889 (p_outcome_id => p_outcome_id
890 ,p_object_version_number => l_object_version_number
891 );
892 exception
893 when hr_api.cannot_find_prog_unit then
894 hr_api.cannot_find_prog_unit_error
895 (p_module_name => 'DELETE_OUTCOME'
896 ,p_hook_type => 'BP'
897 );
898 end;
899
900 hr_utility.set_location(l_proc, 20);
901
902 --
903 -- Saved competence_id for calling hr_competence_element api
904 --
905 hr_utility.trace('p_outcome_id : '|| p_outcome_id);
906 select competence_id into l_competence_id
907 from per_competence_outcomes
908 where outcome_id = p_outcome_id;
909
910 hr_utility.trace('l_competence_id : '|| l_competence_id);
911
912 --
913 -- Remove all matching translation rows in per_competence_outcomes_tl
914 --
915 per_cot_del.del_tl
916 (p_outcome_id => p_outcome_id
917 );
918
919 hr_utility.set_location(l_proc, 30);
920
921 --
922 -- Process Logic
923 --
924
925 per_cpo_del.del
926 (p_outcome_id => p_outcome_id
927 ,p_object_version_number => l_object_version_number
928 );
929
930 hr_utility.set_location(l_proc, 40);
931
932 --
933 -- Call After Process User Hook
934 --
935 begin
936 hr_competence_outcome_bk3.delete_outcome_a
937 (p_outcome_id => p_outcome_id
938 ,p_object_version_number => l_object_version_number
939 );
940 exception
941 when hr_api.cannot_find_prog_unit then
942 hr_api.cannot_find_prog_unit_error
943 (p_module_name => 'DELETE_OUTCOME'
944 ,p_hook_type => 'AP'
945 );
946 end;
947
948 hr_utility.set_location(l_proc, 50);
949
950 --
951 -- If an outcome is deleted, associated outcome achieved may be
952 -- change to 'ACHIEVED'
953 --
954 open csr_get_competence_element_id;
955 loop
956 fetch csr_get_competence_element_id into l_competence_element_id
957 ,l_party_id , lo_object_version_number;
958 exit when csr_get_competence_element_id%NOTFOUND;
959 hr_utility.trace('l_comptence_element_id : '|| l_competence_element_id);
960 hr_utility.trace('l_party_id : '|| l_party_id);
961
962 l_max_date_from := per_ceo_bus.check_outcome_achieved(
963 p_effective_date => sysdate
964 ,p_competence_element_id => l_competence_element_id
965 ,p_competence_id => l_competence_id
966 );
967
968 hr_utility.trace('l_max_date_from : '|| l_max_date_from);
969 if (l_max_date_from is not NULL) then
970 hr_utility.set_location(l_proc, 60);
971 hr_competence_element_api.update_competence_element
972 (p_effective_date => sysdate
973 ,p_competence_element_id => l_competence_element_id
974 ,p_status => 'ACHIEVED'
975 ,p_achieved_date => l_max_date_from
976 ,p_party_id => l_party_id
977 ,p_object_version_number => lo_object_version_number
978 );
979 end if;
980 end loop;
981 close csr_get_competence_element_id;
982
983 hr_utility.set_location(l_proc, 70);
984
985 p_object_version_number := l_object_version_number;
986 --
987 -- When in validation only mode raise the Validate_Enabled exception
988 --
989 if p_validate then
990 raise hr_api.validate_enabled;
991 end if;
992
993 --
994 -- Set all output arguments (returned by some dt modes only)
995 --
996 p_object_version_number := l_object_version_number;
997
998 hr_utility.set_location(' Leaving:'||l_proc, 80);
999 exception
1000 when hr_api.validate_enabled then
1001 hr_utility.set_location(' Leaving...:'||l_proc, 90);
1002 --
1003 -- As the Validate_Enabled exception has been raised
1004 -- we must rollback to the savepoint
1005 --
1006 rollback to delete_outcome;
1007 --
1008 -- Only set output warning arguments
1009 -- (Any key or derived arguments must be set to null
1010 -- when validation only mode is being used.)
1011 --
1012 p_object_version_number := null;
1013 --
1014 when others then
1015 hr_utility.set_location(' Leaving...:'||l_proc, 100);
1016 --
1017 -- A validation or unexpected error has occured
1018 --
1019 rollback to delete_outcome;
1020 --
1021 -- set in out parameters and set out parameters
1022 --
1023 p_object_version_number := lv_object_version_number;
1024 --
1025 raise;
1026 --
1027 end delete_outcome;
1028 --
1029 end hr_competence_outcome_api;