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