[Home] [Help]
PACKAGE BODY: APPS.HR_COMPETENCE_ELEMENT_API
Source
1 Package Body hr_competence_element_api as
2 /* $Header: pecelapi.pkb 120.3 2006/02/13 14:07:13 vbala noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_competence_element_api.';
7 --
8 -- ---------------------------------------------------------------------------
9 -- |-----------------------< <create_competence_element> >--------------------|
10 -- ---------------------------------------------------------------------------
11 --
12 procedure create_competence_element
13 (p_validate in boolean default false,
14 p_competence_element_id out nocopy number,
15 p_object_version_number out nocopy number,
16 p_type in varchar2,
17 p_business_group_id in number default null,
18 p_enterprise_id in number default null,
19 p_competence_id in number default null,
20 p_proficiency_level_id in number default null,
21 p_high_proficiency_level_id in number default null,
22 p_weighting_level_id in number default null,
23 p_rating_level_id in number default null,
24 p_person_id in number default null,
25 p_job_id in number default null,
26 p_valid_grade_id in number default null,
27 p_position_id in number default null,
28 p_organization_id in number default null,
29 p_parent_competence_element_id in number default null,
30 p_activity_version_id in number default null,
31 p_assessment_id in number default null,
32 p_assessment_type_id in number default null,
33 p_mandatory in varchar2 default null,
34 p_effective_date_from in date default null,
35 p_effective_date_to in date default null,
36 p_group_competence_type in varchar2 default null,
37 p_competence_type in varchar2 default null,
38 p_normal_elapse_duration in number default null,
39 p_normal_elapse_duration_unit in varchar2 default null,
40 p_sequence_number in number default null,
41 p_source_of_proficiency_level in varchar2 default null,
42 p_line_score in number default null,
43 p_certification_date in date default null,
44 p_certification_method in varchar2 default null,
45 p_next_certification_date in date default null,
46 p_comments in varchar2 default null,
47 p_attribute_category in varchar2 default null,
48 p_attribute1 in varchar2 default null,
49 p_attribute2 in varchar2 default null,
50 p_attribute3 in varchar2 default null,
51 p_attribute4 in varchar2 default null,
52 p_attribute5 in varchar2 default null,
53 p_attribute6 in varchar2 default null,
54 p_attribute7 in varchar2 default null,
55 p_attribute8 in varchar2 default null,
56 p_attribute9 in varchar2 default null,
57 p_attribute10 in varchar2 default null,
58 p_attribute11 in varchar2 default null,
59 p_attribute12 in varchar2 default null,
60 p_attribute13 in varchar2 default null,
61 p_attribute14 in varchar2 default null,
62 p_attribute15 in varchar2 default null,
63 p_attribute16 in varchar2 default null,
64 p_attribute17 in varchar2 default null,
65 p_attribute18 in varchar2 default null,
66 p_attribute19 in varchar2 default null,
67 p_attribute20 in varchar2 default null,
68 p_effective_date in Date,
69 p_object_id in number default null,
70 p_object_name in varchar2 default null,
71 p_party_id in number default null -- HR/TCA merge
72 ,p_qualification_type_id in number default null
73 ,p_unit_standard_type in varchar2 default null
74 ,p_status in varchar2 default null
75 ,p_information_category in varchar2 default null
76 ,p_information1 in varchar2 default null
77 ,p_information2 in varchar2 default null
78 ,p_information3 in varchar2 default null
79 ,p_information4 in varchar2 default null
80 ,p_information5 in varchar2 default null
81 ,p_information6 in varchar2 default null
82 ,p_information7 in varchar2 default null
83 ,p_information8 in varchar2 default null
84 ,p_information9 in varchar2 default null
85 ,p_information10 in varchar2 default null
86 ,p_information11 in varchar2 default null
87 ,p_information12 in varchar2 default null
88 ,p_information13 in varchar2 default null
89 ,p_information14 in varchar2 default null
90 ,p_information15 in varchar2 default null
91 ,p_information16 in varchar2 default null
92 ,p_information17 in varchar2 default null
93 ,p_information18 in varchar2 default null
94 ,p_information19 in varchar2 default null
95 ,p_information20 in varchar2 default null
96 ,p_achieved_date in date default null
97 ,p_appr_line_score in number default null
98 ) is
99 --
100 -- Declare cursors and local variables
101 --
102 --
103 l_proc varchar2(72) := g_package||'create_competence_elements';
104 l_competence_element_id per_competence_elements.competence_id%TYPE;
105 l_object_version_number per_competence_elements.object_version_number%TYPE;
106 begin
107 hr_utility.set_location('Entering:'|| l_proc, 5);
108 --
109 -- Issue a savepoint.
110 --
111 savepoint create_competence_element;
112 hr_utility.set_location(l_proc, 6);
113 --
114 -- Call Before Process User Hook
115 --
116 begin
117 hr_competence_element_bk1.create_competence_element_b (
118 p_type => p_type
119 ,p_business_group_id => p_business_group_id
120 ,p_enterprise_id => p_enterprise_id
121 ,p_competence_id => p_competence_id
122 ,p_proficiency_level_id => p_proficiency_level_id
123 ,p_high_proficiency_level_id => p_high_proficiency_level_id
124 ,p_weighting_level_id => p_weighting_level_id
125 ,p_rating_level_id => p_rating_level_id
126 ,p_person_id => p_person_id
127 ,p_job_id => p_job_id
128 ,p_valid_grade_id => p_valid_grade_id
129 ,p_position_id => p_position_id
130 ,p_organization_id => p_organization_id
131 ,p_parent_competence_element_id => p_parent_competence_element_id
132 ,p_activity_version_id => p_activity_version_id
133 ,p_assessment_id => p_assessment_id
134 ,p_assessment_type_id => p_assessment_type_id
135 ,p_mandatory => p_mandatory
136 ,p_effective_date_from => p_effective_date_from
137 ,p_effective_date_to => p_effective_date_to
138 ,p_group_competence_type => p_group_competence_type
139 ,p_competence_type => p_competence_type
140 ,p_normal_elapse_duration => p_normal_elapse_duration
141 ,p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
142 ,p_sequence_number => p_sequence_number
143 ,p_source_of_proficiency_level => p_source_of_proficiency_level
144 ,p_line_score => p_line_score
145 ,p_certification_date => p_certification_date
146 ,p_certification_method => p_certification_method
147 ,p_next_certification_date => p_next_certification_date
148 ,p_comments => p_comments
149 ,p_attribute_category => p_attribute_category
150 ,p_attribute1 => p_attribute1
151 ,p_attribute2 => p_attribute2
152 ,p_attribute3 => p_attribute3
153 ,p_attribute4 => p_attribute4
154 ,p_attribute5 => p_attribute5
155 ,p_attribute6 => p_attribute6
156 ,p_attribute7 => p_attribute7
157 ,p_attribute8 => p_attribute8
158 ,p_attribute9 => p_attribute9
159 ,p_attribute10 => p_attribute10
160 ,p_attribute11 => p_attribute11
161 ,p_attribute12 => p_attribute12
162 ,p_attribute13 => p_attribute13
163 ,p_attribute14 => p_attribute14
164 ,p_attribute15 => p_attribute15
165 ,p_attribute16 => p_attribute16
166 ,p_attribute17 => p_attribute17
167 ,p_attribute18 => p_attribute18
168 ,p_attribute19 => p_attribute19
169 ,p_attribute20 => p_attribute20
170 ,p_effective_date => p_effective_date
171 ,p_object_id => p_object_id
172 ,p_object_name => p_object_name
173 ,p_party_id => p_party_id
174 ,p_qualification_type_id => p_qualification_type_id
175 ,p_unit_standard_type => p_unit_standard_type
176 ,p_status => p_status
177 ,p_information_category => p_information_category
178 ,p_information1 => p_information1
179 ,p_information2 => p_information2
180 ,p_information3 => p_information3
181 ,p_information4 => p_information4
182 ,p_information5 => p_information5
183 ,p_information6 => p_information6
184 ,p_information7 => p_information7
185 ,p_information8 => p_information8
186 ,p_information9 => p_information9
187 ,p_information10 => p_information10
188 ,p_information11 => p_information11
189 ,p_information12 => p_information12
190 ,p_information13 => p_information13
191 ,p_information14 => p_information14
192 ,p_information15 => p_information15
193 ,p_information16 => p_information16
194 ,p_information17 => p_information17
195 ,p_information18 => p_information18
196 ,p_information19 => p_information19
197 ,p_information20 => p_information20
198 ,p_achieved_date => p_achieved_date
199 ,p_appr_line_score => p_appr_line_score
200 );
201 exception
202 when hr_api.cannot_find_prog_unit then
203 hr_api.cannot_find_prog_unit_error
204 (p_module_name => 'create_competence_element',
205 p_hook_type => 'BP'
206 );
207 end;
208 --
209 -- End of Before Process User Hook call
210 --
211 -- Validation in addition to Table Handlers
212 --
213 hr_utility.set_location(l_proc, 7);
214 --
215 -- Process Logic
216 --
217 per_cel_ins.ins (
218 p_competence_element_id => l_competence_element_id
219 ,p_object_version_number => l_object_version_number
220 ,p_type => p_type
221 ,p_business_group_id => p_business_group_id
222 ,p_enterprise_id => p_enterprise_id
223 ,p_competence_id => p_competence_id
224 ,p_proficiency_level_id => p_proficiency_level_id
225 ,p_high_proficiency_level_id => p_high_proficiency_level_id
226 ,p_weighting_level_id => p_weighting_level_id
227 ,p_rating_level_id => p_rating_level_id
228 ,p_person_id => p_person_id
229 ,p_job_id => p_job_id
230 ,p_valid_grade_id => p_valid_grade_id
231 ,p_position_id => p_position_id
232 ,p_organization_id => p_organization_id
233 ,p_parent_competence_element_id => p_parent_competence_element_id
234 ,p_activity_version_id => p_activity_version_id
235 ,p_assessment_id => p_assessment_id
236 ,p_assessment_type_id => p_assessment_type_id
237 ,p_mandatory => p_mandatory
238 ,p_effective_date_from => p_effective_date_from
239 ,p_effective_date_to => p_effective_date_to
240 ,p_group_competence_type => p_group_competence_type
241 ,p_competence_type => p_competence_type
242 ,p_normal_elapse_duration => p_normal_elapse_duration
243 ,p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
244 ,p_sequence_number => p_sequence_number
245 ,p_source_of_proficiency_level => p_source_of_proficiency_level
246 ,p_line_score => p_line_score
247 ,p_certification_date => p_certification_date
248 ,p_certification_method => p_certification_method
249 ,p_next_certification_date => p_next_certification_date
250 ,p_comments => p_comments
251 ,p_attribute_category => p_attribute_category
252 ,p_attribute1 => p_attribute1
253 ,p_attribute2 => p_attribute2
254 ,p_attribute3 => p_attribute3
255 ,p_attribute4 => p_attribute4
256 ,p_attribute5 => p_attribute5
257 ,p_attribute6 => p_attribute6
258 ,p_attribute7 => p_attribute7
259 ,p_attribute8 => p_attribute8
260 ,p_attribute9 => p_attribute9
261 ,p_attribute10 => p_attribute10
262 ,p_attribute11 => p_attribute11
263 ,p_attribute12 => p_attribute12
264 ,p_attribute13 => p_attribute13
265 ,p_attribute14 => p_attribute14
266 ,p_attribute15 => p_attribute15
267 ,p_attribute16 => p_attribute16
268 ,p_attribute17 => p_attribute17
269 ,p_attribute18 => p_attribute18
270 ,p_attribute19 => p_attribute19
271 ,p_attribute20 => p_attribute20
272 ,p_effective_date => p_effective_date
273 ,p_validate => p_validate
274 ,p_object_id => p_object_id
275 ,p_object_name => p_object_name
276 ,p_party_id => p_party_id -- HR/TCA merge
277 ,p_qualification_type_id => p_qualification_type_id
278 ,p_unit_standard_type => p_unit_standard_type
279 ,p_status => p_status
280 ,p_information_category => p_information_category
281 ,p_information1 => p_information1
282 ,p_information2 => p_information2
283 ,p_information3 => p_information3
284 ,p_information4 => p_information4
285 ,p_information5 => p_information5
286 ,p_information6 => p_information6
287 ,p_information7 => p_information7
288 ,p_information8 => p_information8
289 ,p_information9 => p_information9
290 ,p_information10 => p_information10
291 ,p_information11 => p_information11
292 ,p_information12 => p_information12
293 ,p_information13 => p_information13
294 ,p_information14 => p_information14
295 ,p_information15 => p_information15
296 ,p_information16 => p_information16
297 ,p_information17 => p_information17
298 ,p_information18 => p_information18
299 ,p_information19 => p_information19
300 ,p_information20 => p_information20
301 ,p_achieved_date => p_achieved_date
302 ,p_appr_line_score => p_appr_line_score
303 );
304 --
305 hr_utility.set_location(l_proc, 8);
306 --
307 --
308 -- Call After Process User Hook
309 --
310 begin
311 hr_competence_element_bk1.create_competence_element_a (
312 p_competence_element_id => l_competence_element_id
313 ,p_object_version_number => l_object_version_number
314 ,p_type => p_type
315 ,p_business_group_id => p_business_group_id
316 ,p_enterprise_id => p_enterprise_id
317 ,p_competence_id => p_competence_id
318 ,p_proficiency_level_id => p_proficiency_level_id
319 ,p_high_proficiency_level_id => p_high_proficiency_level_id
320 ,p_weighting_level_id => p_weighting_level_id
321 ,p_rating_level_id => p_rating_level_id
322 ,p_person_id => p_person_id
323 ,p_job_id => p_job_id
324 ,p_valid_grade_id => p_valid_grade_id
325 ,p_position_id => p_position_id
326 ,p_organization_id => p_organization_id
327 ,p_parent_competence_element_id => p_parent_competence_element_id
328 ,p_activity_version_id => p_activity_version_id
329 ,p_assessment_id => p_assessment_id
330 ,p_assessment_type_id => p_assessment_type_id
331 ,p_mandatory => p_mandatory
332 ,p_effective_date_from => p_effective_date_from
333 ,p_effective_date_to => p_effective_date_to
334 ,p_group_competence_type => p_group_competence_type
335 ,p_competence_type => p_competence_type
336 ,p_normal_elapse_duration => p_normal_elapse_duration
337 ,p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
338 ,p_sequence_number => p_sequence_number
339 ,p_source_of_proficiency_level => p_source_of_proficiency_level
340 ,p_line_score => p_line_score
341 ,p_certification_date => p_certification_date
342 ,p_certification_method => p_certification_method
343 ,p_next_certification_date => p_next_certification_date
344 ,p_comments => p_comments
345 ,p_attribute_category => p_attribute_category
346 ,p_attribute1 => p_attribute1
347 ,p_attribute2 => p_attribute2
348 ,p_attribute3 => p_attribute3
349 ,p_attribute4 => p_attribute4
350 ,p_attribute5 => p_attribute5
351 ,p_attribute6 => p_attribute6
352 ,p_attribute7 => p_attribute7
353 ,p_attribute8 => p_attribute8
354 ,p_attribute9 => p_attribute9
355 ,p_attribute10 => p_attribute10
356 ,p_attribute11 => p_attribute11
357 ,p_attribute12 => p_attribute12
358 ,p_attribute13 => p_attribute13
359 ,p_attribute14 => p_attribute14
360 ,p_attribute15 => p_attribute15
361 ,p_attribute16 => p_attribute16
362 ,p_attribute17 => p_attribute17
363 ,p_attribute18 => p_attribute18
364 ,p_attribute19 => p_attribute19
365 ,p_attribute20 => p_attribute20
366 ,p_effective_date => p_effective_date
367 ,p_object_id => p_object_id
368 ,p_object_name => p_object_name
369 ,p_party_id => p_party_id -- HR/TCA merge
370 ,p_qualification_type_id => p_qualification_type_id
371 ,p_unit_standard_type => p_unit_standard_type
372 ,p_status => p_status
373 ,p_information_category => p_information_category
374 ,p_information1 => p_information1
375 ,p_information2 => p_information2
376 ,p_information3 => p_information3
377 ,p_information4 => p_information4
378 ,p_information5 => p_information5
379 ,p_information6 => p_information6
380 ,p_information7 => p_information7
381 ,p_information8 => p_information8
382 ,p_information9 => p_information9
383 ,p_information10 => p_information10
384 ,p_information11 => p_information11
385 ,p_information12 => p_information12
386 ,p_information13 => p_information13
387 ,p_information14 => p_information14
388 ,p_information15 => p_information15
389 ,p_information16 => p_information16
390 ,p_information17 => p_information17
391 ,p_information18 => p_information18
392 ,p_information19 => p_information19
393 ,p_information20 => p_information20
394 ,p_achieved_date => p_achieved_date
395 ,p_appr_line_score => p_appr_line_score
396 );
397 exception
398 when hr_api.cannot_find_prog_unit then
399 hr_api.cannot_find_prog_unit_error
400 (p_module_name => 'create_competence_element',
401 p_hook_type => 'AP'
402 );
403 end;
404 --
405 -- End of After process user hook
406 --
407 -- When in validation only mode raise the Validate_Enabled exception
408 --
409 if p_validate then
410 raise hr_api.validate_enabled;
411 end if;
412 --
413 -- Set all output arguments
414 --
415 p_competence_element_id := l_competence_element_id;
416 p_object_version_number := l_object_version_number;
417 --
418 hr_utility.set_location(' Leaving:'||l_proc, 11);
419 exception
420 when hr_api.validate_enabled then
421 --
422 -- As the Validate_Enabled exception has been raised
423 -- we must rollback to the savepoint
424 --
425 ROLLBACK TO create_competence_element;
426 --
427 -- Only set output warning arguments
428 -- (Any key or derived arguments must be set to null
429 -- when validation only mode is being used.)
430 --
431 p_competence_element_id := null;
432 p_object_version_number := null;
433 --
434 when others then
435 --
436 -- A validation or unexpected error has occurred
437 --
438 -- Added as part of fix to bug 632482
439 --
440 ROLLBACK TO create_competence_element;
441 raise;
442 --
443 -- End of fix.
444 --
445 hr_utility.set_location(' Leaving:'||l_proc, 12);
446 end create_competence_element;
447 --
448 --
449 -- ---------------------------------------------------------------------------
450 -- |-----------------------< <update_competence_element> >--------------------|
451 -- ---------------------------------------------------------------------------
452 --
453 procedure update_competence_element
454 (
455 p_competence_element_id in number,
456 p_object_version_number in out nocopy number,
457 p_proficiency_level_id in number default hr_api.g_number,
458 p_high_proficiency_level_id in number default hr_api.g_number,
459 p_weighting_level_id in number default hr_api.g_number,
460 p_rating_level_id in number default hr_api.g_number,
461 p_mandatory in varchar2 default hr_api.g_varchar2,
462 p_effective_date_from in date default hr_api.g_date,
463 p_effective_date_to in date default hr_api.g_date,
464 p_group_competence_type in varchar2 default hr_api.g_varchar2,
465 p_competence_type in varchar2 default hr_api.g_varchar2,
466 p_normal_elapse_duration in number default hr_api.g_number,
467 p_normal_elapse_duration_unit in varchar2 default hr_api.g_varchar2,
468 p_sequence_number in number default hr_api.g_number,
469 p_source_of_proficiency_level in varchar2 default hr_api.g_varchar2,
470 p_line_score in number default hr_api.g_number,
471 p_certification_date in date default hr_api.g_date,
472 p_certification_method in varchar2 default hr_api.g_varchar2,
473 p_next_certification_date in date default hr_api.g_date,
474 p_comments in varchar2 default hr_api.g_varchar2,
475 p_attribute_category in varchar2 default hr_api.g_varchar2,
476 p_attribute1 in varchar2 default hr_api.g_varchar2,
477 p_attribute2 in varchar2 default hr_api.g_varchar2,
478 p_attribute3 in varchar2 default hr_api.g_varchar2,
479 p_attribute4 in varchar2 default hr_api.g_varchar2,
480 p_attribute5 in varchar2 default hr_api.g_varchar2,
481 p_attribute6 in varchar2 default hr_api.g_varchar2,
482 p_attribute7 in varchar2 default hr_api.g_varchar2,
483 p_attribute8 in varchar2 default hr_api.g_varchar2,
484 p_attribute9 in varchar2 default hr_api.g_varchar2,
485 p_attribute10 in varchar2 default hr_api.g_varchar2,
486 p_attribute11 in varchar2 default hr_api.g_varchar2,
487 p_attribute12 in varchar2 default hr_api.g_varchar2,
488 p_attribute13 in varchar2 default hr_api.g_varchar2,
489 p_attribute14 in varchar2 default hr_api.g_varchar2,
490 p_attribute15 in varchar2 default hr_api.g_varchar2,
491 p_attribute16 in varchar2 default hr_api.g_varchar2,
492 p_attribute17 in varchar2 default hr_api.g_varchar2,
493 p_attribute18 in varchar2 default hr_api.g_varchar2,
494 p_attribute19 in varchar2 default hr_api.g_varchar2,
495 p_attribute20 in varchar2 default hr_api.g_varchar2,
496 p_effective_date in Date,
497 p_validate in boolean default false,
498 p_party_id in number
499 ,p_qualification_type_id in number default hr_api.g_number
500 ,p_unit_standard_type in varchar2 default hr_api.g_varchar2
501 ,p_status in varchar2 default hr_api.g_varchar2
502 ,p_information_category in varchar2 default hr_api.g_varchar2
503 ,p_information1 in varchar2 default hr_api.g_varchar2
504 ,p_information2 in varchar2 default hr_api.g_varchar2
505 ,p_information3 in varchar2 default hr_api.g_varchar2
506 ,p_information4 in varchar2 default hr_api.g_varchar2
507 ,p_information5 in varchar2 default hr_api.g_varchar2
508 ,p_information6 in varchar2 default hr_api.g_varchar2
509 ,p_information7 in varchar2 default hr_api.g_varchar2
510 ,p_information8 in varchar2 default hr_api.g_varchar2
511 ,p_information9 in varchar2 default hr_api.g_varchar2
512 ,p_information10 in varchar2 default hr_api.g_varchar2
513 ,p_information11 in varchar2 default hr_api.g_varchar2
514 ,p_information12 in varchar2 default hr_api.g_varchar2
515 ,p_information13 in varchar2 default hr_api.g_varchar2
516 ,p_information14 in varchar2 default hr_api.g_varchar2
517 ,p_information15 in varchar2 default hr_api.g_varchar2
518 ,p_information16 in varchar2 default hr_api.g_varchar2
519 ,p_information17 in varchar2 default hr_api.g_varchar2
520 ,p_information18 in varchar2 default hr_api.g_varchar2
521 ,p_information19 in varchar2 default hr_api.g_varchar2
522 ,p_information20 in varchar2 default hr_api.g_varchar2
523 ,p_achieved_date in date default hr_api.g_date
524 ,p_appr_line_score in number default hr_api.g_number
525 ) is
526 --
527 -- Declare cursors and local variables
528 --
529 --
530 l_proc varchar2(72) := g_package||'update_competence_element';
531 l_object_version_number per_competence_elements.object_version_number%TYPE;
532 --
533 lv_object_version_number per_competence_elements.object_version_number%TYPE := p_object_version_number ;
534 --
535 begin
536 hr_utility.set_location('Entering:'|| l_proc, 5);
537 --
538 -- Issue a savepoint.
539 --
540 savepoint update_competence_element;
541 hr_utility.set_location(l_proc, 6);
542 --
543 -- Call Before Process User Hook
544 --
545 begin
546 hr_competence_element_bk2.update_competence_element_b (
547 p_competence_element_id => p_competence_element_id
548 ,p_object_version_number => p_object_version_number
549 ,p_proficiency_level_id => p_proficiency_level_id
550 ,p_high_proficiency_level_id => p_high_proficiency_level_id
551 ,p_weighting_level_id => p_weighting_level_id
552 ,p_rating_level_id => p_rating_level_id
553 ,p_mandatory => p_mandatory
554 ,p_effective_date_from => p_effective_date_from
555 ,p_effective_date_to => p_effective_date_to
556 ,p_group_competence_type => p_group_competence_type
557 ,p_competence_type => p_competence_type
558 ,p_normal_elapse_duration => p_normal_elapse_duration
559 ,p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
560 ,p_sequence_number => p_sequence_number
561 ,p_source_of_proficiency_level => p_source_of_proficiency_level
562 ,p_line_score => p_line_score
563 ,p_certification_date => p_certification_date
564 ,p_certification_method => p_certification_method
565 ,p_next_certification_date => p_next_certification_date
566 ,p_comments => p_comments
567 ,p_attribute_category => p_attribute_category
568 ,p_attribute1 => p_attribute1
569 ,p_attribute2 => p_attribute2
570 ,p_attribute3 => p_attribute3
571 ,p_attribute4 => p_attribute4
572 ,p_attribute5 => p_attribute5
573 ,p_attribute6 => p_attribute6
574 ,p_attribute7 => p_attribute7
575 ,p_attribute8 => p_attribute8
576 ,p_attribute9 => p_attribute9
577 ,p_attribute10 => p_attribute10
578 ,p_attribute11 => p_attribute11
579 ,p_attribute12 => p_attribute12
580 ,p_attribute13 => p_attribute13
581 ,p_attribute14 => p_attribute14
582 ,p_attribute15 => p_attribute15
583 ,p_attribute16 => p_attribute16
584 ,p_attribute17 => p_attribute17
585 ,p_attribute18 => p_attribute18
586 ,p_attribute19 => p_attribute19
587 ,p_attribute20 => p_attribute20
588 ,p_effective_date => p_effective_date
589 ,p_qualification_type_id => p_qualification_type_id
590 ,p_unit_standard_type => p_unit_standard_type
591 ,p_status => p_status
592 ,p_information_category => p_information_category
593 ,p_information1 => p_information1
594 ,p_information2 => p_information2
595 ,p_information3 => p_information3
596 ,p_information4 => p_information4
597 ,p_information5 => p_information5
598 ,p_information6 => p_information6
599 ,p_information7 => p_information7
600 ,p_information8 => p_information8
601 ,p_information9 => p_information9
602 ,p_information10 => p_information10
603 ,p_information11 => p_information11
604 ,p_information12 => p_information12
605 ,p_information13 => p_information13
606 ,p_information14 => p_information14
607 ,p_information15 => p_information15
608 ,p_information16 => p_information16
609 ,p_information17 => p_information17
610 ,p_information18 => p_information18
611 ,p_information19 => p_information19
612 ,p_information20 => p_information20
613 ,p_achieved_date => p_achieved_date
614 ,p_appr_line_score => p_appr_line_score
615 );
616 exception
617 when hr_api.cannot_find_prog_unit then
618 hr_api.cannot_find_prog_unit_error
619 (p_module_name => 'update_competence_element',
620 p_hook_type => 'BP'
621 );
622 end;
623 --
624 -- End of Before Process User Hook call
625 --
626 -- Validation in addition to Table Handlers
627 --
628 hr_utility.set_location(l_proc, 7);
629 --
630 -- Process Logic
631 --
632 l_object_version_number := p_object_version_number;
633 --
634 per_cel_upd.upd (
635 p_competence_element_id => p_competence_element_id
636 ,p_object_version_number => p_object_version_number
637 ,p_proficiency_level_id => p_proficiency_level_id
638 ,p_high_proficiency_level_id => p_high_proficiency_level_id
639 ,p_weighting_level_id => p_weighting_level_id
640 ,p_rating_level_id => p_rating_level_id
641 ,p_mandatory => p_mandatory
642 ,p_effective_date_from => p_effective_date_from
643 ,p_effective_date_to => p_effective_date_to
644 ,p_group_competence_type => p_group_competence_type
645 ,p_competence_type => p_competence_type
646 ,p_normal_elapse_duration => p_normal_elapse_duration
647 ,p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
648 ,p_sequence_number => p_sequence_number
649 ,p_source_of_proficiency_level => p_source_of_proficiency_level
650 ,p_line_score => p_line_score
651 ,p_certification_date => p_certification_date
652 ,p_certification_method => p_certification_method
653 ,p_next_certification_date => p_next_certification_date
654 ,p_comments => p_comments
655 ,p_attribute_category => p_attribute_category
656 ,p_attribute1 => p_attribute1
657 ,p_attribute2 => p_attribute2
658 ,p_attribute3 => p_attribute3
659 ,p_attribute4 => p_attribute4
660 ,p_attribute5 => p_attribute5
661 ,p_attribute6 => p_attribute6
662 ,p_attribute7 => p_attribute7
663 ,p_attribute8 => p_attribute8
664 ,p_attribute9 => p_attribute9
665 ,p_attribute10 => p_attribute10
666 ,p_attribute11 => p_attribute11
667 ,p_attribute12 => p_attribute12
668 ,p_attribute13 => p_attribute13
669 ,p_attribute14 => p_attribute14
670 ,p_attribute15 => p_attribute15
671 ,p_attribute16 => p_attribute16
672 ,p_attribute17 => p_attribute17
673 ,p_attribute18 => p_attribute18
674 ,p_attribute19 => p_attribute19
675 ,p_attribute20 => p_attribute20
676 ,p_effective_date => p_effective_date
677 ,p_validate => p_validate
678 ,p_party_id => p_party_id
679 ,p_qualification_type_id => p_qualification_type_id
680 ,p_unit_standard_type => p_unit_standard_type
681 ,p_status => p_status
682 ,p_information_category => p_information_category
683 ,p_information1 => p_information1
684 ,p_information2 => p_information2
685 ,p_information3 => p_information3
686 ,p_information4 => p_information4
687 ,p_information5 => p_information5
688 ,p_information6 => p_information6
689 ,p_information7 => p_information7
690 ,p_information8 => p_information8
691 ,p_information9 => p_information9
692 ,p_information10 => p_information10
693 ,p_information11 => p_information11
694 ,p_information12 => p_information12
695 ,p_information13 => p_information13
696 ,p_information14 => p_information14
697 ,p_information15 => p_information15
698 ,p_information16 => p_information16
699 ,p_information17 => p_information17
700 ,p_information18 => p_information18
701 ,p_information19 => p_information19
702 ,p_information20 => p_information20
703 ,p_achieved_date => p_achieved_date
704 ,p_appr_line_score => p_appr_line_score
705 );
706 --
707 --
708 --
709 hr_utility.set_location(l_proc, 8);
710 --
711 -- Call After Process User Hook
712 --
713 begin
714 hr_competence_element_bk2.update_competence_element_a (
715 p_competence_element_id => p_competence_element_id
716 ,p_object_version_number => l_object_version_number
717 ,p_proficiency_level_id => p_proficiency_level_id
718 ,p_high_proficiency_level_id => p_high_proficiency_level_id
719 ,p_weighting_level_id => p_weighting_level_id
720 ,p_rating_level_id => p_rating_level_id
721 ,p_mandatory => p_mandatory
722 ,p_effective_date_from => p_effective_date_from
723 ,p_effective_date_to => p_effective_date_to
724 ,p_group_competence_type => p_group_competence_type
725 ,p_competence_type => p_competence_type
726 ,p_normal_elapse_duration => p_normal_elapse_duration
727 ,p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
728 ,p_sequence_number => p_sequence_number
729 ,p_source_of_proficiency_level => p_source_of_proficiency_level
730 ,p_line_score => p_line_score
731 ,p_certification_date => p_certification_date
732 ,p_certification_method => p_certification_method
733 ,p_next_certification_date => p_next_certification_date
734 ,p_comments => p_comments
735 ,p_attribute_category => p_attribute_category
736 ,p_attribute1 => p_attribute1
737 ,p_attribute2 => p_attribute2
738 ,p_attribute3 => p_attribute3
739 ,p_attribute4 => p_attribute4
740 ,p_attribute5 => p_attribute5
741 ,p_attribute6 => p_attribute6
742 ,p_attribute7 => p_attribute7
743 ,p_attribute8 => p_attribute8
744 ,p_attribute9 => p_attribute9
745 ,p_attribute10 => p_attribute10
746 ,p_attribute11 => p_attribute11
747 ,p_attribute12 => p_attribute12
748 ,p_attribute13 => p_attribute13
749 ,p_attribute14 => p_attribute14
750 ,p_attribute15 => p_attribute15
751 ,p_attribute16 => p_attribute16
752 ,p_attribute17 => p_attribute17
753 ,p_attribute18 => p_attribute18
754 ,p_attribute19 => p_attribute19
755 ,p_attribute20 => p_attribute20
756 ,p_effective_date => p_effective_date
757 ,p_qualification_type_id => p_qualification_type_id
758 ,p_unit_standard_type => p_unit_standard_type
759 ,p_status => p_status
760 ,p_information_category => p_information_category
761 ,p_information1 => p_information1
762 ,p_information2 => p_information2
763 ,p_information3 => p_information3
764 ,p_information4 => p_information4
765 ,p_information5 => p_information5
766 ,p_information6 => p_information6
767 ,p_information7 => p_information7
768 ,p_information8 => p_information8
769 ,p_information9 => p_information9
770 ,p_information10 => p_information10
771 ,p_information11 => p_information11
772 ,p_information12 => p_information12
773 ,p_information13 => p_information13
774 ,p_information14 => p_information14
775 ,p_information15 => p_information15
776 ,p_information16 => p_information16
777 ,p_information17 => p_information17
778 ,p_information18 => p_information18
779 ,p_information19 => p_information19
780 ,p_information20 => p_information20
781 ,p_achieved_date => p_achieved_date
782 ,p_appr_line_score => p_appr_line_score
783 );
784 exception
785 when hr_api.cannot_find_prog_unit then
786 hr_api.cannot_find_prog_unit_error
787 (p_module_name => 'update_competence_element',
788 p_hook_type => 'AP'
789 );
790 end;
791 --
792 -- End After Process User Hook
793 --
794 -- When in validation only mode raise the Validate_Enabled exception
795 --
796 if p_validate then
797 raise hr_api.validate_enabled;
798 end if;
799 --
800 --
801 hr_utility.set_location(' Leaving:'||l_proc, 11);
802 exception
803 when hr_api.validate_enabled then
804 --
805 -- As the Validate_Enabled exception has been raised
806 -- we must rollback to the savepoint
807 --
808 ROLLBACK TO update_competence_element;
809 --
810 -- Only set output warning arguments
811 -- (Any key or derived arguments must be set to null
812 -- when validation only mode is being used.)
813 --
814 p_object_version_number := l_object_version_number;
815 --
816 when others then
817 --
818 -- A validation or unexpected error has occurred
819 --
820 -- Added as part of fix to bug 632482
821 --
822 p_object_version_number := lv_object_version_number;
823
824 ROLLBACK TO update_competence_element;
825 raise;
826 --
827 -- End of fix.
828 --
829 hr_utility.set_location(' Leaving:'||l_proc, 12);
830 --
831 end update_competence_element;
832 --
833 --
834 -- ---------------------------------------------------------------------------
835 -- |-----------------------< <update_competence_element> >--------------------|
836 -- ---------------------------------------------------------------------------
837 -- |-- This is overload update_competence_element without party_id --|
838 -- ---------------------------------------------------------------------------
839 --
840 procedure update_competence_element
841 (
842 p_competence_element_id in number,
843 p_object_version_number in out nocopy number,
844 p_proficiency_level_id in number default hr_api.g_number,
845 p_high_proficiency_level_id in number default hr_api.g_number,
846 p_weighting_level_id in number default hr_api.g_number,
847 p_rating_level_id in number default hr_api.g_number,
848 p_mandatory in varchar2 default hr_api.g_varchar2,
849 p_effective_date_from in date default hr_api.g_date,
850 p_effective_date_to in date default hr_api.g_date,
851 p_group_competence_type in varchar2 default hr_api.g_varchar2,
852 p_competence_type in varchar2 default hr_api.g_varchar2,
853 p_normal_elapse_duration in number default hr_api.g_number,
854 p_normal_elapse_duration_unit in varchar2 default hr_api.g_varchar2,
855 p_sequence_number in number default hr_api.g_number,
856 p_source_of_proficiency_level in varchar2 default hr_api.g_varchar2,
857 p_line_score in number default hr_api.g_number,
858 p_certification_date in date default hr_api.g_date,
859 p_certification_method in varchar2 default hr_api.g_varchar2,
860 p_next_certification_date in date default hr_api.g_date,
861 p_comments in varchar2 default hr_api.g_varchar2,
862 p_attribute_category in varchar2 default hr_api.g_varchar2,
863 p_attribute1 in varchar2 default hr_api.g_varchar2,
864 p_attribute2 in varchar2 default hr_api.g_varchar2,
865 p_attribute3 in varchar2 default hr_api.g_varchar2,
866 p_attribute4 in varchar2 default hr_api.g_varchar2,
867 p_attribute5 in varchar2 default hr_api.g_varchar2,
868 p_attribute6 in varchar2 default hr_api.g_varchar2,
869 p_attribute7 in varchar2 default hr_api.g_varchar2,
870 p_attribute8 in varchar2 default hr_api.g_varchar2,
871 p_attribute9 in varchar2 default hr_api.g_varchar2,
872 p_attribute10 in varchar2 default hr_api.g_varchar2,
873 p_attribute11 in varchar2 default hr_api.g_varchar2,
874 p_attribute12 in varchar2 default hr_api.g_varchar2,
875 p_attribute13 in varchar2 default hr_api.g_varchar2,
876 p_attribute14 in varchar2 default hr_api.g_varchar2,
877 p_attribute15 in varchar2 default hr_api.g_varchar2,
878 p_attribute16 in varchar2 default hr_api.g_varchar2,
879 p_attribute17 in varchar2 default hr_api.g_varchar2,
880 p_attribute18 in varchar2 default hr_api.g_varchar2,
881 p_attribute19 in varchar2 default hr_api.g_varchar2,
882 p_attribute20 in varchar2 default hr_api.g_varchar2,
883 p_effective_date in Date,
884 p_validate in boolean default false
885 ) is
886 --
887 -- Declare cursors and local variables
888 --
889 --
890 l_proc varchar2(72) := g_package||'update_competence_element';
891 l_party_id per_competence_elements.party_id%type;
892 --
893 cursor csr_get_party_id is
894 select party_id
895 from per_competence_elements
896 where competence_element_id = p_competence_element_id;
897 --
898
899 begin
900 hr_utility.set_location('Entering:'|| l_proc, 10);
901 --
902 -- get party_id from per_competence_elements using competence_element_id
903 --
904 open csr_get_party_id;
905 fetch csr_get_party_id into l_party_id;
906 close csr_get_party_id;
907 hr_utility.set_location(l_proc, 20);
908 --
909 --
910 --
911 update_competence_element
912 ( p_competence_element_id => p_competence_element_id
913 , p_object_version_number => p_object_version_number
914 , p_proficiency_level_id => p_proficiency_level_id
915 , p_high_proficiency_level_id => p_high_proficiency_level_id
916 , p_weighting_level_id => p_weighting_level_id
917 , p_rating_level_id => p_rating_level_id
918 , p_mandatory => p_mandatory
919 , p_effective_date_from => p_effective_date_from
920 , p_effective_date_to => p_effective_date_to
921 , p_group_competence_type => p_group_competence_type
922 , p_competence_type => p_competence_type
923 , p_normal_elapse_duration => p_normal_elapse_duration
924 , p_normal_elapse_duration_unit => p_normal_elapse_duration_unit
925 , p_sequence_number => p_sequence_number
926 , p_source_of_proficiency_level => p_source_of_proficiency_level
927 , p_line_score => p_line_score
928 , p_certification_date => p_certification_date
929 , p_certification_method => p_certification_method
930 , p_next_certification_date => p_next_certification_date
931 , p_comments => p_comments
932 , p_attribute_category => p_attribute_category
933 , p_attribute1 => p_attribute1
934 , p_attribute2 => p_attribute2
935 , p_attribute3 => p_attribute3
936 , p_attribute4 => p_attribute4
937 , p_attribute5 => p_attribute5
938 , p_attribute6 => p_attribute6
939 , p_attribute7 => p_attribute7
940 , p_attribute8 => p_attribute8
941 , p_attribute9 => p_attribute9
942 , p_attribute10 => p_attribute10
943 , p_attribute11 => p_attribute11
944 , p_attribute12 => p_attribute12
945 , p_attribute13 => p_attribute13
946 , p_attribute14 => p_attribute14
947 , p_attribute15 => p_attribute15
948 , p_attribute16 => p_attribute16
949 , p_attribute17 => p_attribute17
950 , p_attribute18 => p_attribute18
951 , p_attribute19 => p_attribute19
952 , p_attribute20 => p_attribute20
953 , p_effective_date => p_effective_date
954 , p_validate => p_validate
955 , p_party_id => l_party_id
956 );
957 --
958 --
959 --
960 hr_utility.set_location(' Leaving:'||l_proc, 100);
961 --
962 end update_competence_element;
963
964 -- ---------------------------------------------------------------------------
965 -- |-------------------< <update_personal_comp_element> >--------------------|
966 -- ---------------------------------------------------------------------------
967 --
968 procedure update_personal_comp_element
969 (
970 p_competence_element_id in number,
971 p_object_version_number in out nocopy number,
972 p_proficiency_level_id in number default hr_api.g_number,
973 p_effective_date_from in date default hr_api.g_date,
974 p_effective_date_to in date default hr_api.g_date,
975 p_source_of_proficiency_level in varchar2 default hr_api.g_varchar2,
976 p_certification_date in date default hr_api.g_date,
977 p_certification_method in varchar2 default hr_api.g_varchar2,
978 p_next_certification_date in date default hr_api.g_date,
979 p_comments in varchar2 default hr_api.g_varchar2,
980 p_attribute_category in varchar2 default hr_api.g_varchar2,
981 p_attribute1 in varchar2 default hr_api.g_varchar2,
982 p_attribute2 in varchar2 default hr_api.g_varchar2,
983 p_attribute3 in varchar2 default hr_api.g_varchar2,
984 p_attribute4 in varchar2 default hr_api.g_varchar2,
985 p_attribute5 in varchar2 default hr_api.g_varchar2,
986 p_attribute6 in varchar2 default hr_api.g_varchar2,
987 p_attribute7 in varchar2 default hr_api.g_varchar2,
988 p_attribute8 in varchar2 default hr_api.g_varchar2,
989 p_attribute9 in varchar2 default hr_api.g_varchar2,
990 p_attribute10 in varchar2 default hr_api.g_varchar2,
991 p_attribute11 in varchar2 default hr_api.g_varchar2,
992 p_attribute12 in varchar2 default hr_api.g_varchar2,
993 p_attribute13 in varchar2 default hr_api.g_varchar2,
994 p_attribute14 in varchar2 default hr_api.g_varchar2,
995 p_attribute15 in varchar2 default hr_api.g_varchar2,
996 p_attribute16 in varchar2 default hr_api.g_varchar2,
997 p_attribute17 in varchar2 default hr_api.g_varchar2,
998 p_attribute18 in varchar2 default hr_api.g_varchar2,
999 p_attribute19 in varchar2 default hr_api.g_varchar2,
1000 p_attribute20 in varchar2 default hr_api.g_varchar2,
1001 p_effective_date in Date,
1002 p_validate in boolean default false ,
1003 p_ins_ovn out nocopy number,
1004 p_ins_comp_id out nocopy number
1005 ) is
1006 --
1007 -- Declare cursors and local variables
1008 --
1009 --
1010 l_proc varchar2(72) := g_package||'update_Personal_comp_element';
1011 l_object_version_number per_competence_elements.object_version_number%TYPE;
1012 l_ins_ovn per_competence_elements.object_version_number%TYPE;
1013 l_ins_comp_id number(9);
1014 l_competence_id number(9);
1015 l_business_group_id number(9);
1016 l_person_id per_competence_elements.person_id%TYPE;
1017 l_party_id per_competence_elements.party_id%TYPE;-- HR/TCA merg
1018 l_type per_competence_elements.type%TYPE;
1019 l_effective_date_from date;
1020 l_effective_date_to date;
1021 l_upd_date_from date := p_effective_date_from;
1022 l_upd_date_to date := p_effective_date_to;
1023 l_proficiency_level_id number(9);
1024 l_certification_date date;
1025 l_source_of_proficiency_level varchar2(80);
1026 l_certification_method varchar2(80);
1027 l_next_certification_date date;
1028 l_comments varchar2(2000);
1029 l_attribute_category varchar2(80);
1030 l_attribute1 varchar2(150);
1031 l_attribute2 varchar2(150);
1032 l_attribute3 varchar2(150);
1033 l_attribute4 varchar2(150);
1034 l_attribute5 varchar2(150);
1035 l_attribute6 varchar2(150);
1036 l_attribute7 varchar2(150);
1037 l_attribute8 varchar2(150);
1038 l_attribute9 varchar2(150);
1039 l_attribute10 varchar2(150);
1040 l_attribute11 varchar2(150);
1041 l_attribute12 varchar2(150);
1042 l_attribute13 varchar2(150);
1043 l_attribute14 varchar2(150);
1044 l_attribute15 varchar2(150);
1045 l_attribute16 varchar2(150);
1046 l_attribute17 varchar2(150);
1047 l_attribute18 varchar2(150);
1048 l_attribute19 varchar2(150);
1049 l_attribute20 varchar2(150);
1050 l_insert boolean := false;
1051 --
1052 lv_object_version_number number := p_object_version_number ;
1053 --
1054 -- cusror to get the type and effective_date_from of the competence_element.
1055 --
1056 cursor csr_comp_element is
1057 select
1058 type,
1059 business_group_id,
1060 competence_id,
1061 proficiency_level_id,
1062 person_id,
1063 party_id, -- HR/TCA merge
1064 effective_date_from,
1065 effective_date_to,
1066 source_of_proficiency_level,
1067 certification_date,
1068 certification_method,
1069 next_certification_date,
1070 comments,
1071 attribute_category,
1072 attribute1,
1073 attribute2,
1074 attribute3,
1075 attribute4,
1076 attribute5,
1077 attribute6,
1078 attribute7,
1079 attribute8,
1080 attribute9,
1081 attribute10,
1082 attribute11,
1083 attribute12,
1084 attribute13,
1085 attribute14,
1086 attribute15,
1087 attribute16,
1088 attribute17,
1089 attribute18,
1090 attribute19,
1091 attribute20
1092 from per_competence_elements
1093 where competence_element_id = p_competence_element_id
1094 and object_version_number = p_object_version_number;
1095 --
1096 begin
1097 hr_utility.set_location('Entering:'|| l_proc, 5);
1098 --
1099 -- Issue a savepoint.
1100 --
1101 savepoint update_competence_element;
1102 hr_utility.set_location(l_proc, 6);
1103 --
1104 -- Call Before Process User Hook
1105 --
1106 begin
1107 hr_competence_element_bk3.update_personal_comp_element_b
1108 (
1109 p_competence_element_id => p_competence_element_id,
1110 p_object_version_number => p_object_version_number,
1111 p_proficiency_level_id => p_proficiency_level_id ,
1112 p_effective_date_from => p_effective_date_from ,
1113 p_effective_date_to => p_effective_date_to ,
1114 p_source_of_proficiency_level => p_source_of_proficiency_level,
1115 p_certification_date => p_certification_date ,
1116 p_certification_method => p_certification_method ,
1117 p_next_certification_date => p_next_certification_date,
1118 p_comments => p_comments ,
1119 p_attribute_category => p_attribute_category,
1120 p_attribute1 => p_attribute1 ,
1121 p_attribute2 => p_attribute2 ,
1122 p_attribute3 => p_attribute3 ,
1123 p_attribute4 => p_attribute4 ,
1124 p_attribute5 => p_attribute5 ,
1125 p_attribute6 => p_attribute6 ,
1126 p_attribute7 => p_attribute7 ,
1127 p_attribute8 => p_attribute8 ,
1128 p_attribute9 => p_attribute9 ,
1129 p_attribute10 => p_attribute10,
1130 p_attribute11 => p_attribute11,
1131 p_attribute12 => p_attribute12,
1132 p_attribute13 => p_attribute13,
1133 p_attribute14 => p_attribute14,
1134 p_attribute15 => p_attribute15,
1135 p_attribute16 => p_attribute16,
1136 p_attribute17 => p_attribute17,
1137 p_attribute18 => p_attribute18,
1138 p_attribute19 => p_attribute19,
1139 p_attribute20 => p_attribute20,
1140 p_effective_date => p_effective_date
1141 );
1142 exception
1143 when hr_api.cannot_find_prog_unit then
1144 hr_api.cannot_find_prog_unit_error
1145 (p_module_name => 'update_personal_comp_element',
1146 p_hook_type => 'BP'
1147 );
1148 end;
1149 --
1150 -- End of Before Process User hook
1151 --
1152 -- Validation in addition to Table Handlers
1153 --
1154 hr_utility.set_location(l_proc, 7);
1155 --
1156 -- only do the following logic if the effective_date_from is entered.
1157 --
1158 if (p_effective_date_from is not null) then
1159 --
1160 --
1161 -- This is the logic to make the changes to competence element of type personal
1162 -- to be date tracked.
1163 --
1164 open csr_comp_element;
1165 fetch csr_comp_element into
1166 l_type,
1167 l_business_group_id,
1168 l_competence_id,
1169 l_proficiency_level_id,
1170 l_person_id,
1171 l_party_id, -- HR/TCA merge
1172 l_effective_date_from,
1173 l_effective_date_to,
1174 l_source_of_proficiency_level,
1175 l_certification_date,
1176 l_certification_method,
1177 l_next_certification_date,
1178 l_comments,
1179 l_attribute_category,
1180 l_attribute1,
1181 l_attribute2,
1182 l_attribute3,
1183 l_attribute4,
1184 l_attribute5,
1185 l_attribute6,
1186 l_attribute7,
1187 l_attribute8,
1188 l_attribute9,
1189 l_attribute10,
1190 l_attribute11,
1191 l_attribute12,
1192 l_attribute13,
1193 l_attribute14,
1194 l_attribute15,
1195 l_attribute16,
1196 l_attribute17,
1197 l_attribute18,
1198 l_attribute19,
1199 l_attribute20;
1200 if csr_comp_element%notfound then
1201 hr_utility.set_location(l_proc, 8);
1202 close csr_comp_element;
1203 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1204 hr_utility.raise_error;
1205 elsif (l_type <> 'PERSONAL') then
1206 hr_utility.set_location(l_proc, 9);
1207 hr_utility.set_message(801, 'HR_51866_CEL_INV_COMP_TYPE');
1208 hr_utility.raise_error;
1209 elsif (
1210 l_effective_date_from <> p_effective_date_from and
1211 ((l_proficiency_level_id is not null
1212 and p_proficiency_level_id is null) OR (l_proficiency_level_id is null and
1213 p_proficiency_level_id is not null) OR ( l_proficiency_level_id <>
1214 p_proficiency_level_id))) then
1215 --
1216 --
1217 l_insert := true;
1218 --
1219 if (p_effective_date_from > l_effective_date_from ) then
1220 --
1221 -- this means that the proficiency_level has changed so is the date_from.
1222 -- We need to update the row with the previous proficiency_level and the
1223 -- date_to being one day less than the p_effective_date_from.
1224 --
1225 l_effective_date_to := p_effective_date_from -1;
1226 --
1227 if (p_effective_date_to is not null ) then
1228 --
1229 -- the effective_date_to is also changed.
1230 --
1231 hr_utility.set_location(l_proc, 15);
1232 l_upd_date_to := p_effective_date_to;
1233 --
1234 end if;
1235 --
1236 elsif (p_effective_date_from < l_effective_date_from) then
1237 --
1238 if (p_effective_date_to is not null and
1239 p_effective_date_to > l_effective_date_from) then
1240 --
1241 hr_utility.set_location(l_proc, 20);
1242 hr_utility.set_message(801, 'HR_51867_CEL_DATE_OVERLAP');
1243 hr_utility.raise_error;
1244 elsif(p_effective_date_to is null) then
1245 --
1246 hr_utility.set_location(l_proc, 25);
1247 l_upd_date_to := l_effective_date_from -1;
1248 --
1249 end if;
1250 --
1251 end if;
1252 --
1253 end if;
1254 close csr_comp_element;
1255 end if; -- The p_effective_date_from is not null
1256
1257 -- Process Logic
1258 --
1259 l_object_version_number := p_object_version_number;
1260 --
1261 per_cel_upd.upd (
1262 p_competence_element_id => p_competence_element_id
1263 ,p_object_version_number => p_object_version_number
1264 ,p_proficiency_level_id => p_proficiency_level_id
1265 ,p_effective_date_from => p_effective_date_from
1266 ,p_effective_date_to => l_upd_date_to
1267 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1268 ,p_certification_date => p_certification_date
1269 ,p_certification_method => p_certification_method
1270 ,p_next_certification_date => p_next_certification_date
1271 ,p_comments => p_comments
1272 ,p_attribute_category => p_attribute_category
1273 ,p_attribute1 => p_attribute1
1274 ,p_attribute2 => p_attribute2
1275 ,p_attribute3 => p_attribute3
1276 ,p_attribute4 => p_attribute4
1277 ,p_attribute5 => p_attribute5
1278 ,p_attribute6 => p_attribute6
1279 ,p_attribute7 => p_attribute7
1280 ,p_attribute8 => p_attribute8
1281 ,p_attribute9 => p_attribute9
1282 ,p_attribute10 => p_attribute10
1283 ,p_attribute11 => p_attribute11
1284 ,p_attribute12 => p_attribute12
1285 ,p_attribute13 => p_attribute13
1286 ,p_attribute14 => p_attribute14
1287 ,p_attribute15 => p_attribute15
1288 ,p_attribute16 => p_attribute16
1289 ,p_attribute17 => p_attribute17
1290 ,p_attribute18 => p_attribute18
1291 ,p_attribute19 => p_attribute19
1292 ,p_attribute20 => p_attribute20
1293 ,p_effective_date => p_effective_date
1294 ,p_validate => p_validate
1295 );
1296 --
1297 --
1298 --
1299 hr_utility.set_location(l_proc, 30);
1300 --
1301 -- Now, insert a row if necessary.
1302 --
1303 if (l_insert) then
1304 hr_utility.set_location ('bus_id*** ' || to_char(l_business_group_id), 31);
1305 hr_utility.set_location ('type ***' || l_type, 32);
1306 hr_utility.set_location ('person **' || to_char(l_person_id),33);
1307 hr_utility.set_location ('date_from**' || to_char(l_effective_date_from),34);
1308 per_cel_ins.ins (
1309 p_competence_element_id => l_ins_comp_id
1310 ,p_object_version_number => l_ins_ovn
1311 ,p_business_group_id => l_business_group_id
1312 ,p_type => l_type
1313 ,p_competence_id => l_competence_id
1314 ,p_person_id => l_person_id
1315 ,p_proficiency_level_id => l_proficiency_level_id
1316 ,p_effective_date_from => l_effective_date_from
1317 ,p_effective_date_to => l_effective_date_to
1318 ,p_source_of_proficiency_level => l_source_of_proficiency_level
1319 ,p_certification_date => l_certification_date
1320 ,p_certification_method => l_certification_method
1321 ,p_next_certification_date => l_next_certification_date
1322 ,p_comments => l_comments
1323 ,p_attribute_category => l_attribute_category
1324 ,p_attribute1 => l_attribute1
1325 ,p_attribute2 => l_attribute2
1326 ,p_attribute3 => l_attribute3
1327 ,p_attribute4 => l_attribute4
1328 ,p_attribute5 => l_attribute5
1329 ,p_attribute6 => l_attribute6
1330 ,p_attribute7 => l_attribute7
1331 ,p_attribute8 => l_attribute8
1332 ,p_attribute9 => l_attribute9
1333 ,p_attribute10 => l_attribute10
1334 ,p_attribute11 => l_attribute11
1335 ,p_attribute12 => l_attribute12
1336 ,p_attribute13 => l_attribute13
1337 ,p_attribute14 => l_attribute14
1338 ,p_attribute15 => l_attribute15
1339 ,p_attribute16 => l_attribute16
1340 ,p_attribute17 => l_attribute17
1341 ,p_attribute18 => l_attribute18
1342 ,p_attribute19 => l_attribute19
1343 ,p_attribute20 => l_attribute20
1344 ,p_effective_date => p_effective_date
1345 ,p_validate => p_validate
1346 ,p_party_id => l_party_id -- HR/TCA merge
1347 );
1348 --
1349 hr_utility.set_location(l_proc, 35);
1350 --
1351 end if;
1352 --
1353 -- Call After Process User Hook
1354 --
1355 begin
1356 hr_competence_element_bk3.update_personal_comp_element_a
1357 (
1358 p_competence_element_id => p_competence_element_id,
1359 p_object_version_number => p_object_version_number,
1360 p_proficiency_level_id => p_proficiency_level_id ,
1361 p_effective_date_from => p_effective_date_from ,
1362 p_effective_date_to => p_effective_date_to ,
1363 p_source_of_proficiency_level => p_source_of_proficiency_level,
1364 p_certification_date => p_certification_date ,
1365 p_certification_method => p_certification_method ,
1366 p_next_certification_date => p_next_certification_date,
1367 p_comments => p_comments ,
1368 p_attribute_category => p_attribute_category,
1369 p_attribute1 => p_attribute1 ,
1370 p_attribute2 => p_attribute2 ,
1371 p_attribute3 => p_attribute3 ,
1372 p_attribute4 => p_attribute4 ,
1373 p_attribute5 => p_attribute5 ,
1374 p_attribute6 => p_attribute6 ,
1375 p_attribute7 => p_attribute7 ,
1376 p_attribute8 => p_attribute8 ,
1377 p_attribute9 => p_attribute9 ,
1378 p_attribute10 => p_attribute10,
1379 p_attribute11 => p_attribute11,
1380 p_attribute12 => p_attribute12,
1381 p_attribute13 => p_attribute13,
1382 p_attribute14 => p_attribute14,
1383 p_attribute15 => p_attribute15,
1384 p_attribute16 => p_attribute16,
1385 p_attribute17 => p_attribute17,
1386 p_attribute18 => p_attribute18,
1387 p_attribute19 => p_attribute19,
1388 p_attribute20 => p_attribute20,
1389 p_effective_date => p_effective_date,
1390 p_ins_ovn => l_ins_ovn,
1391 p_ins_comp_id => l_ins_comp_id
1392 );
1393 exception
1394 when hr_api.cannot_find_prog_unit then
1395 hr_api.cannot_find_prog_unit_error
1396 (p_module_name => 'update_personal_comp_element',
1397 p_hook_type => 'AP'
1398 );
1399 end;
1400 --
1401 -- End of After Process User hook
1402 --
1403 -- When in validation only mode raise the Validate_Enabled exception
1404 --
1405 if p_validate then
1406 raise hr_api.validate_enabled;
1407 end if;
1408 --
1409 --
1410 hr_utility.set_location(' Leaving:'||l_proc, 40);
1411 exception
1412 when hr_api.validate_enabled then
1413 --
1414 -- As the Validate_Enabled exception has been raised
1415 -- we must rollback to the savepoint
1416 --
1417 ROLLBACK TO update_competence_element;
1418 --
1419 -- Only set output warning arguments
1420 -- (Any key or derived arguments must be set to null
1421 -- when validation only mode is being used.)
1422 --
1423 p_object_version_number := l_object_version_number;
1424 p_ins_ovn := null;
1425 p_ins_comp_id := null;
1426 --
1427 when others then
1428 --
1429 -- A validation or unexpected error has occurred
1430 --
1431 -- Added as part of fix to bug 632482
1432 --
1433 p_object_version_number := lv_object_version_number;
1434
1435 p_ins_ovn := null;
1436 p_ins_comp_id := null;
1437 --
1438 ROLLBACK TO update_competence_element;
1439 raise;
1440 --
1441 -- End of fix.
1442 --
1443 hr_utility.set_location(' Leaving:'||l_proc, 45);
1444 --
1445 end update_personal_comp_element;
1446 --
1447 --
1448 -- ---------------------------------------------------------------------------
1449 -- |-----------------------< <delete_competence_element> >--------------------|
1450 -- ---------------------------------------------------------------------------
1451 --
1452 procedure delete_competence_element
1453 (p_validate in boolean default FALSE,
1454 p_competence_element_id in number,
1455 p_object_version_number in number
1456 ) is
1457 --
1458 -- Declare cursors and local variables
1459 --
1460 --
1461 l_proc varchar2(72) := g_package||'delete_competence_element';
1462 begin
1463 hr_utility.set_location('Entering:'|| l_proc, 5);
1464 --
1465 -- Issue a savepoint.
1466 --
1467 savepoint delete_competence_element;
1468 hr_utility.set_location(l_proc, 6);
1469 --
1470 -- Call Before Process User Hook
1471 --
1472 begin
1473 hr_competence_element_bk4.delete_competence_element_b
1474 (
1475 p_competence_element_id => p_competence_element_id,
1476 p_object_version_number => p_object_version_number
1477 );
1478 exception
1479 when hr_api.cannot_find_prog_unit then
1480 hr_api.cannot_find_prog_unit_error
1481 (p_module_name => 'delete_competence_element',
1482 p_hook_type => 'BP'
1483 );
1484 end;
1485 --
1486 -- End of Before Process User hook
1487 --
1488 -- Validation in addition to Table Handlers
1489 --
1490 hr_utility.set_location(l_proc, 7);
1491 --
1492 -- Process Logic
1493 --
1494 --
1495 -- now delete the competence itself
1496 --
1497 per_cel_del.del
1498 (p_validate => FALSE
1499 ,p_competence_element_id => p_competence_element_id
1500 ,p_object_version_number => p_object_version_number
1501 );
1502 --
1503 hr_utility.set_location(l_proc, 8);
1504 --
1505 -- Call After Process User Hook
1506 --
1507 begin
1508 hr_competence_element_bk4.delete_competence_element_a (
1509 p_competence_element_id => p_competence_element_id,
1510 p_object_version_number => p_object_version_number
1511 );
1512 exception
1513 when hr_api.cannot_find_prog_unit then
1514 hr_api.cannot_find_prog_unit_error
1515 (p_module_name => 'delete_competence_element',
1516 p_hook_type => 'AP'
1517 );
1518 end;
1519 --
1520 -- End of After Process User hook
1521 --
1522 -- When in validation only mode raise the Validate_Enabled exception
1523 --
1524 if p_validate then
1525 raise hr_api.validate_enabled;
1526 end if;
1527 --
1528 hr_utility.set_location(' Leaving:'||l_proc, 11);
1529 exception
1530 when hr_api.validate_enabled then
1531 --
1532 -- As the Validate_Enabled exception has been raised
1533 -- we must rollback to the savepoint
1534 --
1535 ROLLBACK TO delete_competence_element;
1536 --
1537 --
1538 when others then
1539 --
1540 -- A validation or unexpected error has occurred
1541 --
1542 -- Added as part of fix to bug 632482
1543 --
1544 ROLLBACK TO delete_competence_element;
1545 raise;
1546 --
1547 -- End of fix.
1548 --
1549 hr_utility.set_location(' Leaving:'||l_proc, 12);
1550 end delete_competence_element;
1551 --
1552 -- ---------------------------------------------------------------------------
1553 -- |--------------------< <maintain_student_comp_element> >------------------|
1554 -- ---------------------------------------------------------------------------
1555 --
1556 -- maintain_student_comp_element
1557 -- Called from Student Enrollment Form - maintains a students competence profile
1558 --
1559 procedure maintain_student_comp_element
1560 (p_person_id in number
1561 ,p_competence_id in number
1562 ,p_proficiency_level_id in number
1563 ,p_business_group_id in number
1564 ,p_effective_date_from in date
1565 ,p_effective_date_to in date
1566 ,p_certification_date in date
1567 ,p_certification_method in varchar2
1568 ,p_next_certification_date in date
1569 ,p_source_of_proficiency_level in varchar2
1570 ,p_comments in varchar2
1571 ,p_effective_date in date
1572 ,p_validate in boolean default FALSE
1573 ,p_competence_created out nocopy number) is
1574 --
1575 l_new_competence_element_id number;
1576 l_new_object_version_number number;
1577 l_new_effective_date_from date default null;
1578 l_competence_element_id number;
1579 l_object_version_number number;
1580 l_proficiency_level_id number;
1581 l_effective_date_from date;
1582 l_effective_date_to date; -- added for bug#1623036
1583 --
1584 cursor c_get_comp_element is
1585 select competence_element_id
1586 , object_version_number
1587 , proficiency_level_id
1588 , effective_date_from
1589 , effective_date_to -- added for bug#1623036
1590 from per_competence_elements
1591 where person_id = p_person_id
1592 and type = 'PERSONAL'
1593 and competence_id = p_competence_id
1594 order by effective_date_from desc;
1595
1596 Cursor csr_chk_date_overlap is
1597 select competence_element_id
1598 , object_version_number
1599 , proficiency_level_id
1600 , effective_date_from
1601 , effective_date_to
1602 from per_competence_elements
1603 where person_id = p_person_id
1604 and type = 'PERSONAL'
1605 and competence_id = p_competence_id
1606 and
1607 p_effective_date_from between effective_date_from and Nvl(effective_Date_to,p_effective_date_from) ;
1608
1609 -- get from_date nearest to new record from_date
1610 cursor c_get_nearest_from_date is
1611 select competence_element_id
1612 , object_version_number
1613 , proficiency_level_id
1614 , effective_date_from
1615 , effective_date_to -- added for bug#1623036
1616 from per_competence_elements
1617 where person_id = p_person_id
1618 and type = 'PERSONAL'
1619 and competence_id = p_competence_id
1620 and
1621 p_effective_date_from < effective_date_from
1622 order by effective_date_from asc;
1623 --
1624 begin
1625 --
1626 -- Determine if there is an existing competence element
1627 --
1628 --
1629 -- Issue a savepoint.
1630 --
1631 savepoint maintain_student_comp_element;
1632 --
1633 -- Call Before Process User Hook
1634 --
1635 begin
1636 hr_competence_element_bk5.maintain_student_comp_elemen_b (
1637 p_person_id => p_person_id
1638 ,p_business_group_id => p_business_group_id
1639 ,p_competence_id => p_competence_id
1640 ,p_proficiency_level_id => p_proficiency_level_id
1641 ,p_effective_date_from => p_effective_date_from
1642 ,p_effective_date_to => p_effective_date_to
1643 ,p_certification_date => p_certification_date
1644 ,p_certification_method => p_certification_method
1645 ,p_next_certification_date => p_next_certification_date
1646 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1647 ,p_comments => p_comments
1648 ,p_effective_date => p_effective_date
1649 );
1650 exception
1651 when hr_api.cannot_find_prog_unit then
1652 hr_api.cannot_find_prog_unit_error
1653 (p_module_name => 'maintain_student_comp_element',
1654 p_hook_type => 'BP'
1655 );
1656 end;
1657 --
1658 -- End of Before Process User hook
1659 --
1660 open c_get_comp_element;
1661 fetch c_get_comp_element into l_competence_element_id,
1662 l_object_version_number,
1663 l_proficiency_level_id,
1664 l_effective_date_from,
1665 l_effective_date_to; -- added for bug#1623036
1666
1667 close c_get_comp_element;
1668 --
1669 p_competence_created := 0;
1670 --
1671 if l_competence_element_id is null then
1672 create_competence_element
1673 (p_competence_element_id => l_new_competence_element_id
1674 ,p_object_version_number => l_new_object_version_number
1675 ,p_type => 'PERSONAL'
1676 ,p_person_id => p_person_id
1677 ,p_business_group_id => p_business_group_id
1678 ,p_competence_id => p_competence_id
1679 ,p_proficiency_level_id => p_proficiency_level_id
1680 ,p_effective_date_from => p_effective_date_from
1681 ,p_effective_date_to => p_effective_date_to
1682 ,p_certification_date => p_certification_date
1683 ,p_certification_method => p_certification_method
1684 ,p_next_certification_date => p_next_certification_date
1685 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1686 ,p_comments => p_comments
1687 ,p_effective_date => p_effective_date);
1688 --
1689 p_competence_created := 1;
1690 --
1691 else
1692 /********* NEW Logic***********8888*/
1693 if p_effective_date_from < l_effective_date_from then
1694 -- implies new record from date is less than the max(from date) of existing record.
1695 -- First check whether p_eff_date_from lies between some already existing record dates
1696 open csr_chk_date_overlap;
1697 fetch csr_chk_date_overlap into l_competence_element_id,
1698 l_object_version_number,
1699 l_proficiency_level_id,
1700 l_effective_date_from,
1701 l_effective_date_to;
1702
1703
1704 --Dbms_output.put_line('UPD : Date already exists');
1705 -- If the New E.F.Date is between the existing From and To date then simply overwrite the level.
1706 /* l_object_version_number := l_new_object_version_number ;
1707 l_competence_element_id := l_new_competence_element_id ;
1708
1709 l_effective_Date_to := l_new_effective_date_to ;*/
1710 If csr_chk_date_overlap%NotFound then
1711 -- since p_eff_date_from doesn't lie between already existing record dates
1712 -- get the record whose l_effective_from_date is just greater than p_effective_from_date
1713
1714 open c_get_nearest_from_date;
1715 fetch c_get_nearest_from_date into l_competence_element_id,
1716 l_object_version_number,
1717 l_proficiency_level_id,
1718 l_effective_date_from,
1719 l_effective_date_to;
1720
1721 Close c_get_nearest_from_date;
1722
1723 end if; --- csr_chk_date_overlap%Found
1724
1725 close csr_chk_date_overlap;
1726 end if; ---p_effective_date_from < l_effective_date_from
1727
1728
1729 if nvl(l_proficiency_level_id, -1) = nvl(p_proficiency_level_id, -1) then
1730
1731 if l_effective_date_from > p_effective_date_from then
1732 l_new_effective_date_from := p_effective_date_from;
1733 else
1734 l_new_effective_date_from := l_effective_date_from;
1735 end if;
1736 elsif nvl(l_proficiency_level_id, -1) < nvl(p_proficiency_level_id, -1) then
1737
1738 l_new_effective_date_from := p_effective_date_from;
1739 Else
1740 --
1741 -- start modifications for bg #1623036
1742 --
1743 /* if l_effective_date_from > p_effective_date_from then
1744 l_new_effective_date_from := l_effective_date_from;
1745 else*/
1746 l_new_effective_date_from := p_effective_date_from;
1747 -- end if;
1748
1749 end if;
1750 if nvl(l_proficiency_level_id, -1) > nvl(p_proficiency_level_id, -1) then
1751 -- new record has level lesser then existing record
1752
1753 --
1754 --Bug 2366782
1755 if l_effective_date_to is null OR l_effective_date_to >= p_effective_date_from then
1756
1757 /********* NEW Logic***********8888*/
1758
1759 if (l_effective_date_from < p_effective_date_from) then
1760 if ((l_effective_date_to is null or l_effective_date_to>= nvl(p_effective_date_to,hr_api.g_eot))) then
1761 -- new record lies within span of existing record
1762 null;
1763
1764 elsif (l_effective_date_to is not null and l_effective_date_to < nvl(p_effective_date_to,hr_api.g_eot) ) then
1765 /*update_personal_comp_element
1766 (p_competence_element_id => l_competence_element_id
1767 ,p_object_version_number => l_object_version_number
1768 ,p_proficiency_level_id => l_proficiency_level_id
1769 ,p_effective_date_from => l_effective_date_from
1770 ,p_effective_date_to => p_effective_date_from -1
1771 ,p_certification_date => p_certification_date
1772 ,p_certification_method => p_certification_method
1773 ,p_next_certification_date => p_next_certification_date
1774 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1775 ,p_comments => p_comments
1776 ,p_effective_date => p_effective_date
1777 ,p_ins_ovn => l_new_object_version_number
1778 ,p_ins_comp_id => l_new_competence_element_id);*/
1779
1780
1781 create_competence_element
1782 (p_competence_element_id => l_new_competence_element_id
1783 ,p_object_version_number => l_new_object_version_number
1784 ,p_type => 'PERSONAL'
1785 ,p_person_id => p_person_id
1786 ,p_business_group_id => p_business_group_id
1787 ,p_competence_id => p_competence_id
1788 ,p_proficiency_level_id => p_proficiency_level_id
1789 ,p_effective_date_from => l_effective_date_to+1
1790 ,p_effective_date_to => p_effective_date_to
1791 ,p_certification_date => p_certification_date
1792 ,p_certification_method => p_certification_method
1793 ,p_next_certification_date => p_next_certification_date
1794 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1795 ,p_comments => p_comments
1796 ,p_effective_date => p_effective_date);
1797
1798 --
1799 p_competence_created := 1;
1800 end if;
1801 else -- for (l_effective_date_from < p_effective_date_from)
1802 --if nvl(l_proficiency_level_id, -1) < nvl(p_proficiency_level_id, -1) then
1803 -- implies new record has level lesser then existing level but it's start date
1804 -- is also lesser than exisitng record date
1805
1806 create_competence_element
1807 (p_competence_element_id => l_new_competence_element_id
1808 ,p_object_version_number => l_new_object_version_number
1809 ,p_type => 'PERSONAL'
1810 ,p_person_id => p_person_id
1811 ,p_business_group_id => p_business_group_id
1812 ,p_competence_id => p_competence_id
1813 ,p_proficiency_level_id => p_proficiency_level_id
1814 ,p_effective_date_from => p_effective_date_from
1815 ,p_effective_date_to => l_effective_date_from -1
1816 ,p_certification_date => p_certification_date
1817 ,p_certification_method => p_certification_method
1818 ,p_next_certification_date => p_next_certification_date
1819 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1820 ,p_comments => p_comments
1821 ,p_effective_date => p_effective_date);
1822
1823 if (nvl(l_effective_date_to,hr_api.g_eot) < nvl(p_effective_date_to,hr_api.g_eot)) then
1824 create_competence_element
1825 (p_competence_element_id => l_new_competence_element_id
1826 ,p_object_version_number => l_new_object_version_number
1827 ,p_type => 'PERSONAL'
1828 ,p_person_id => p_person_id
1829 ,p_business_group_id => p_business_group_id
1830 ,p_competence_id => p_competence_id
1831 ,p_proficiency_level_id => p_proficiency_level_id
1832 ,p_effective_date_from => l_effective_date_to+1
1833 ,p_effective_date_to => p_effective_date_to
1834 ,p_certification_date => p_certification_date
1835 ,p_certification_method => p_certification_method
1836 ,p_next_certification_date => p_next_certification_date
1837 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1838 ,p_comments => p_comments
1839 ,p_effective_date => p_effective_date);
1840
1841
1842 end if;
1843 p_competence_created := 1;
1844 end if;
1845 elsif l_effective_date_to is not null and p_effective_date_from >= l_effective_date_to then
1846 create_competence_element
1847 (p_competence_element_id => l_new_competence_element_id
1848 ,p_object_version_number => l_new_object_version_number
1849 ,p_type => 'PERSONAL'
1850 ,p_person_id => p_person_id
1851 ,p_business_group_id => p_business_group_id
1852 ,p_competence_id => p_competence_id
1853 ,p_proficiency_level_id => p_proficiency_level_id
1854 ,p_effective_date_from => l_new_effective_date_from
1855 ,p_effective_date_to => p_effective_date_to
1856 ,p_certification_date => p_certification_date
1857 ,p_certification_method => p_certification_method
1858 ,p_next_certification_date => p_next_certification_date
1859 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1860 ,p_comments => p_comments
1861 ,p_effective_date => p_effective_date);
1862
1863 --
1864 p_competence_created := 1;
1865
1866 else
1867 create_competence_element
1868 (p_competence_element_id => l_new_competence_element_id
1869 ,p_object_version_number => l_new_object_version_number
1870 ,p_type => 'PERSONAL'
1871 ,p_person_id => p_person_id
1872 ,p_business_group_id => p_business_group_id
1873 ,p_competence_id => p_competence_id
1874 ,p_proficiency_level_id => p_proficiency_level_id
1875 ,p_effective_date_from => l_new_effective_date_from
1876 ,p_effective_date_to => p_effective_date_to
1877 ,p_certification_date => p_certification_date
1878 ,p_certification_method => p_certification_method
1879 ,p_next_certification_date => p_next_certification_date
1880 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1881 ,p_comments => p_comments
1882 ,p_effective_date => p_effective_date);
1883 p_competence_created := 1;
1884
1885 end if;
1886
1887 --
1888 elsif nvl(l_proficiency_level_id, -1) < nvl(p_proficiency_level_id, -1) then
1889 --
1890 --Bug 2366782
1891 if l_effective_date_to is null OR l_effective_date_to >= p_effective_date_from then
1892
1893 /********* NEW Logic***********8888*/
1894 if l_effective_date_from < p_effective_date_from then
1895 update_personal_comp_element
1896 (p_competence_element_id => l_competence_element_id
1897 ,p_object_version_number => l_object_version_number
1898 ,p_proficiency_level_id => l_proficiency_level_id
1899 ,p_effective_date_from => l_effective_date_from
1900 ,p_effective_date_to => p_effective_date_from -1
1901 ,p_certification_date => p_certification_date
1902 ,p_certification_method => p_certification_method
1903 ,p_next_certification_date => p_next_certification_date
1904 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1905 ,p_comments => p_comments
1906 ,p_effective_date => p_effective_date
1907 ,p_ins_ovn => l_new_object_version_number
1908 ,p_ins_comp_id => l_new_competence_element_id);
1909
1910
1911 create_competence_element
1912 (p_competence_element_id => l_new_competence_element_id
1913 ,p_object_version_number => l_new_object_version_number
1914 ,p_type => 'PERSONAL'
1915 ,p_person_id => p_person_id
1916 ,p_business_group_id => p_business_group_id
1917 ,p_competence_id => p_competence_id
1918 ,p_proficiency_level_id => p_proficiency_level_id
1919 ,p_effective_date_from => l_new_effective_date_from
1920 ,p_effective_date_to => p_effective_date_to
1921 ,p_certification_date => p_certification_date
1922 ,p_certification_method => p_certification_method
1923 ,p_next_certification_date => p_next_certification_date
1924 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1925 ,p_comments => p_comments
1926 ,p_effective_date => p_effective_date);
1927
1928 if (p_effective_date_to is not null and nvl(l_effective_date_to,hr_api.g_eot)> p_effective_date_to) then
1929
1930 create_competence_element
1931 (p_competence_element_id => l_new_competence_element_id
1932 ,p_object_version_number => l_new_object_version_number
1933 ,p_type => 'PERSONAL'
1934 ,p_person_id => p_person_id
1935 ,p_business_group_id => p_business_group_id
1936 ,p_competence_id => p_competence_id
1937 ,p_proficiency_level_id => l_proficiency_level_id
1938 ,p_effective_date_from => p_effective_date_to+1
1939 ,p_effective_date_to => l_effective_date_to
1940 ,p_certification_date => p_certification_date
1941 ,p_certification_method => p_certification_method
1942 ,p_next_certification_date => p_next_certification_date
1943 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1944 ,p_comments => p_comments
1945 ,p_effective_date => p_effective_date);
1946
1947 end if;
1948 --
1949 p_competence_created := 2;
1950 else
1951 --if nvl(l_proficiency_level_id, -1) < nvl(p_proficiency_level_id, -1) then
1952 -- implies new record has level greater then existing level but it's start date
1953 -- is less than exisitng record date
1954 -- update the exisitng record to new level and new start date
1955 if (p_effective_date_to is null) then
1956 --implies new record covers entire span of existing record
1957 delete_competence_element
1958 (p_validate => p_validate,
1959 p_competence_element_id => l_competence_element_id
1960 ,p_object_version_number => l_object_version_number
1961 );
1962 create_competence_element
1963 (p_competence_element_id => l_new_competence_element_id
1964 ,p_object_version_number => l_new_object_version_number
1965 ,p_type => 'PERSONAL'
1966 ,p_person_id => p_person_id
1967 ,p_business_group_id => p_business_group_id
1968 ,p_competence_id => p_competence_id
1969 ,p_proficiency_level_id => p_proficiency_level_id
1970 ,p_effective_date_from => p_effective_date_from
1971 ,p_effective_date_to => p_effective_date_to
1972 ,p_certification_date => p_certification_date
1973 ,p_certification_method => p_certification_method
1974 ,p_next_certification_date => p_next_certification_date
1975 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1976 ,p_comments => p_comments
1977 ,p_effective_date => p_effective_date);
1978
1979 elsif (p_effective_date_to is not null and p_effective_date_to < nvl(l_effective_date_to,hr_api.g_eot)) then
1980 -- new record has an end date less than existing record
1981 update_personal_comp_element
1982 (p_competence_element_id => l_competence_element_id
1983 ,p_object_version_number => l_object_version_number
1984 ,p_proficiency_level_id => l_proficiency_level_id
1985 ,p_effective_date_from => p_effective_date_to+1
1986 ,p_effective_date_to => l_effective_date_to
1987 ,p_certification_date => p_certification_date
1988 ,p_certification_method => p_certification_method
1989 ,p_next_certification_date => p_next_certification_date
1990 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1991 ,p_comments => p_comments
1992 ,p_effective_date => p_effective_date
1993 ,p_ins_ovn => l_new_object_version_number
1994 ,p_ins_comp_id => l_new_competence_element_id);
1995
1996 create_competence_element
1997 (p_competence_element_id => l_new_competence_element_id
1998 ,p_object_version_number => l_new_object_version_number
1999 ,p_type => 'PERSONAL'
2000 ,p_person_id => p_person_id
2001 ,p_business_group_id => p_business_group_id
2002 ,p_competence_id => p_competence_id
2003 ,p_proficiency_level_id => p_proficiency_level_id
2004 ,p_effective_date_from => p_effective_date_from
2005 ,p_effective_date_to => p_effective_date_to
2006 ,p_certification_date => p_certification_date
2007 ,p_certification_method => p_certification_method
2008 ,p_next_certification_date => p_next_certification_date
2009 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2010 ,p_comments => p_comments
2011 ,p_effective_date => p_effective_date);
2012
2013 else
2014 update_personal_comp_element
2015 (p_competence_element_id => l_competence_element_id
2016 ,p_object_version_number => l_object_version_number
2017 ,p_proficiency_level_id => p_proficiency_level_id
2018 ,p_effective_date_from => l_new_effective_date_from
2019 ,p_effective_date_to => p_effective_date_to
2020 ,p_certification_date => p_certification_date
2021 ,p_certification_method => p_certification_method
2022 ,p_next_certification_date => p_next_certification_date
2023 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2024 ,p_comments => p_comments
2025 ,p_effective_date => p_effective_date
2026 ,p_ins_ovn => l_new_object_version_number
2027 ,p_ins_comp_id => l_new_competence_element_id);
2028 end if;
2029 /* else
2030 -- implies new record has level less then existing level and it's start date
2031 -- is less than exisitng record date
2032 -- create a new record with new level and new start date and end it
2033 -- one day before the exisitng record start date
2034 create_competence_element
2035 (p_competence_element_id => l_new_competence_element_id
2036 ,p_object_version_number => l_new_object_version_number
2037 ,p_type => 'PERSONAL'
2038 ,p_person_id => p_person_id
2039 ,p_business_group_id => p_business_group_id
2040 ,p_competence_id => p_competence_id
2041 ,p_proficiency_level_id => p_proficiency_level_id
2042 ,p_effective_date_from => l_new_effective_date_from
2043 ,p_effective_date_to => l_effective_date_from-1
2044 ,p_certification_date => p_certification_date
2045 ,p_certification_method => p_certification_method
2046 ,p_next_certification_date => p_next_certification_date
2047 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2048 ,p_comments => p_comments
2049 ,p_effective_date => p_effective_date);
2050
2051 end if;*/
2052 p_competence_created := 2;
2053
2054 end if;
2055 elsif l_effective_date_to is not null and p_effective_date_from >= l_effective_date_to then
2056 create_competence_element
2057 (p_competence_element_id => l_new_competence_element_id
2058 ,p_object_version_number => l_new_object_version_number
2059 ,p_type => 'PERSONAL'
2060 ,p_person_id => p_person_id
2061 ,p_business_group_id => p_business_group_id
2062 ,p_competence_id => p_competence_id
2063 ,p_proficiency_level_id => p_proficiency_level_id
2064 ,p_effective_date_from => l_new_effective_date_from
2065 ,p_effective_date_to => p_effective_date_to
2066 ,p_certification_date => p_certification_date
2067 ,p_certification_method => p_certification_method
2068 ,p_next_certification_date => p_next_certification_date
2069 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2070 ,p_comments => p_comments
2071 ,p_effective_date => p_effective_date);
2072
2073 --
2074 p_competence_created := 1;
2075
2076 else
2077 create_competence_element
2078 (p_competence_element_id => l_new_competence_element_id
2079 ,p_object_version_number => l_new_object_version_number
2080 ,p_type => 'PERSONAL'
2081 ,p_person_id => p_person_id
2082 ,p_business_group_id => p_business_group_id
2083 ,p_competence_id => p_competence_id
2084 ,p_proficiency_level_id => p_proficiency_level_id
2085 ,p_effective_date_from => l_new_effective_date_from
2086 ,p_effective_date_to => p_effective_date_to
2087 ,p_certification_date => p_certification_date
2088 ,p_certification_method => p_certification_method
2089 ,p_next_certification_date => p_next_certification_date
2090 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2091 ,p_comments => p_comments
2092 ,p_effective_date => p_effective_date);
2093 p_competence_created := 1;
2094
2095 end if;
2096 --Bug 2366782
2097 --
2098 elsif nvl(l_proficiency_level_id, -1) = nvl(p_proficiency_level_id, -1) then
2099 --
2100 if l_effective_date_to is null and p_effective_date_to is null and l_effective_date_from > p_effective_date_from then
2101 --
2102 update_personal_comp_element
2103 (p_competence_element_id => l_competence_element_id
2104 ,p_object_version_number => l_object_version_number
2105 ,p_proficiency_level_id => p_proficiency_level_id
2106 ,p_effective_date_from => l_new_effective_date_from
2107 ,p_effective_date_to => p_effective_date_to
2108 ,p_certification_date => p_certification_date
2109 ,p_certification_method => p_certification_method
2110 ,p_next_certification_date => p_next_certification_date
2111 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2112 ,p_comments => p_comments
2113 ,p_effective_date => p_effective_date
2114 ,p_ins_ovn => l_new_object_version_number
2115 ,p_ins_comp_id => l_new_competence_element_id);
2116 --
2117 p_competence_created := 1;
2118 --
2119 elsif l_effective_date_to is null and p_effective_date_to is not null then
2120 --
2121 if p_effective_date_to >= l_effective_date_from then
2122 update_personal_comp_element
2123 (p_competence_element_id => l_competence_element_id
2124 ,p_object_version_number => l_object_version_number
2125 ,p_proficiency_level_id => p_proficiency_level_id
2126 ,p_effective_date_from => l_new_effective_date_from
2127 ,p_effective_date_to => l_effective_date_to
2128 ,p_certification_date => p_certification_date
2129 ,p_certification_method => p_certification_method
2130 ,p_next_certification_date => p_next_certification_date
2131 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2132 ,p_comments => p_comments
2133 ,p_effective_date => p_effective_date
2134 ,p_ins_ovn => l_new_object_version_number
2135 ,p_ins_comp_id => l_new_competence_element_id);
2136
2137 else
2138 -- new record ends before exisitng record start date.
2139 create_competence_element
2140 (p_competence_element_id => l_new_competence_element_id
2141 ,p_object_version_number => l_new_object_version_number
2142 ,p_type => 'PERSONAL'
2143 ,p_person_id => p_person_id
2144 ,p_business_group_id => p_business_group_id
2145 ,p_competence_id => p_competence_id
2146 ,p_proficiency_level_id => p_proficiency_level_id
2147 ,p_effective_date_from => p_effective_date_from
2148 ,p_effective_date_to => p_effective_date_to
2149 ,p_certification_date => p_certification_date
2150 ,p_certification_method => p_certification_method
2151 ,p_next_certification_date => p_next_certification_date
2152 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2153 ,p_comments => p_comments
2154 ,p_effective_date => p_effective_date);
2155
2156 end if;
2157 --
2158 p_competence_created := 1;
2159 --
2160 elsif l_effective_date_to is not null
2161 and (p_effective_date_to is null and p_effective_date_from > l_effective_date_to) then
2162 --
2163 create_competence_element
2164 (p_competence_element_id => l_new_competence_element_id
2165 ,p_object_version_number => l_new_object_version_number
2166 ,p_type => 'PERSONAL'
2167 ,p_person_id => p_person_id
2168 ,p_business_group_id => p_business_group_id
2169 ,p_competence_id => p_competence_id
2170 ,p_proficiency_level_id => p_proficiency_level_id
2171 ,p_effective_date_from => p_effective_date_from
2172 ,p_effective_date_to => p_effective_date_to
2173 ,p_certification_date => p_certification_date
2174 ,p_certification_method => p_certification_method
2175 ,p_next_certification_date => p_next_certification_date
2176 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2177 ,p_comments => p_comments
2178 ,p_effective_date => p_effective_date);
2179 --
2180 p_competence_created := 1;
2181 --
2182 elsif l_effective_date_to is not null and p_effective_date_to is not null then
2183 --
2184 if l_effective_date_from <= p_effective_date_from
2185 and l_effective_date_to >= p_effective_date_to then
2186 --
2187 null;
2188 --
2189 elsif l_effective_date_to < p_effective_date_from then
2190 --
2191 create_competence_element
2192 (p_competence_element_id => l_new_competence_element_id
2193 ,p_object_version_number => l_new_object_version_number
2194 ,p_type => 'PERSONAL'
2195 ,p_person_id => p_person_id
2196 ,p_business_group_id => p_business_group_id
2197 ,p_competence_id => p_competence_id
2198 ,p_proficiency_level_id => p_proficiency_level_id
2199 ,p_effective_date_from => p_effective_date_from
2200 ,p_effective_date_to => p_effective_date_to
2201 ,p_certification_date => p_certification_date
2202 ,p_certification_method => p_certification_method
2203 ,p_next_certification_date => p_next_certification_date
2204 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2205 ,p_comments => p_comments
2206 ,p_effective_date => p_effective_date);
2207 --
2208 p_competence_created := 1;
2209 --
2210 else
2211 --
2212 if p_effective_date_to >= l_effective_date_from then
2213 update_personal_comp_element
2214 (p_competence_element_id => l_competence_element_id
2215 ,p_object_version_number => l_object_version_number
2216 ,p_proficiency_level_id => p_proficiency_level_id
2217 ,p_effective_date_from => l_new_effective_date_from
2218 ,p_effective_date_to => p_effective_date_to
2219 ,p_certification_date => p_certification_date
2220 ,p_certification_method => p_certification_method
2221 ,p_next_certification_date => p_next_certification_date
2222 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2223 ,p_comments => p_comments
2224 ,p_effective_date => p_effective_date
2225 ,p_ins_ovn => l_new_object_version_number
2226 ,p_ins_comp_id => l_new_competence_element_id);
2227
2228 else
2229 -- new record ends before exisitng record start date.
2230 create_competence_element
2231 (p_competence_element_id => l_new_competence_element_id
2232 ,p_object_version_number => l_new_object_version_number
2233 ,p_type => 'PERSONAL'
2234 ,p_person_id => p_person_id
2235 ,p_business_group_id => p_business_group_id
2236 ,p_competence_id => p_competence_id
2237 ,p_proficiency_level_id => p_proficiency_level_id
2238 ,p_effective_date_from => p_effective_date_from
2239 ,p_effective_date_to => p_effective_date_to
2240 ,p_certification_date => p_certification_date
2241 ,p_certification_method => p_certification_method
2242 ,p_next_certification_date => p_next_certification_date
2243 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2244 ,p_comments => p_comments
2245 ,p_effective_date => p_effective_date);
2246
2247 end if;
2248 --
2249 p_competence_created := 1;
2250 --
2251 end if;
2252 elsif l_effective_date_to is not null
2253 and (p_effective_date_to is null and p_effective_date_from < l_effective_date_to) then
2254
2255 --Bug 2366782
2256 update_personal_comp_element
2257 (p_competence_element_id => l_competence_element_id
2258 ,p_object_version_number => l_object_version_number
2259 ,p_proficiency_level_id => p_proficiency_level_id
2260 ,p_effective_date_from => l_new_effective_date_from
2261 ,p_effective_date_to => p_effective_date_to
2262 ,p_certification_date => p_certification_date
2263 ,p_certification_method => p_certification_method
2264 ,p_next_certification_date => p_next_certification_date
2265 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2266 ,p_comments => p_comments
2267 ,p_effective_date => p_effective_date
2268 ,p_ins_ovn => l_new_object_version_number
2269 ,p_ins_comp_id => l_new_competence_element_id);
2270 --
2271 --
2272 p_competence_created := 1;
2273
2274 --Bug 2366782
2275 end if;
2276 --
2277 end if;
2278 --
2279 end if;
2280 --
2281 -- end modifications for bug #1623036
2282 --
2283 -- Call After Process User Hook
2284 --
2285
2286 begin
2287 hr_competence_element_bk5.maintain_student_comp_elemen_a (
2288 p_person_id => p_person_id
2289 ,p_business_group_id => p_business_group_id
2290 ,p_competence_id => p_competence_id
2291 ,p_proficiency_level_id => p_proficiency_level_id
2292 ,p_effective_date_from => l_new_effective_date_from
2293 ,p_effective_date_to => p_effective_date_to
2294 ,p_certification_date => p_certification_date
2295 ,p_certification_method => p_certification_method
2296 ,p_next_certification_date => p_next_certification_date
2297 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2298 ,p_comments => p_comments
2299 ,p_effective_date => p_effective_date
2300 ,p_competence_created => p_competence_created
2301 );
2302 exception
2303 when hr_api.cannot_find_prog_unit then
2304 hr_api.cannot_find_prog_unit_error
2305 (p_module_name => 'maintain_student_comp_element',
2306 p_hook_type => 'AP'
2307 );
2308 end;
2309 --
2310 -- End of After Process User hook
2311 --
2312 if p_validate then
2313 raise hr_api.validate_enabled;
2314 end if;
2315 --
2316
2317 exception
2318 when hr_api.validate_enabled then
2319 --
2320 -- As the Validate_Enabled exception has been raised
2321 -- we must rollback to the savepoint
2322 --
2323 ROLLBACK TO maintain_student_comp_element;
2324 --
2325 -- Only set output warning arguments
2326 -- (Any key or derived arguments must be set to null
2327 -- when validation only mode is being used.)
2328 --
2329 p_competence_created := null;
2330 --
2331 when others then
2332 --
2333 -- A validation or unexpected error has occurred
2334 --
2335 -- Added as part of fix to bug 632482
2336 --
2337 p_competence_created := null;
2338 --
2339 ROLLBACK TO maintain_student_comp_element;
2340 raise;
2341 --
2342 -- End of fix.
2343 --
2344 end maintain_student_comp_element;
2345 --
2346 --
2347 --
2348 -- ---------------------------------------------------------------------------
2349 -- |--------------------------< <copy_competencies> >------------------------|
2350 -- ---------------------------------------------------------------------------
2351 --
2352 procedure copy_competencies(p_activity_version_from number
2353 ,p_activity_version_to number
2354 ,p_competence_type VARCHAR2 default null -- Added for bug 1868713
2355 ,p_validate boolean default FALSE) is
2356 --
2357 l_competence_element_id number;
2358 l_object_version_number number;
2359 --
2360 /*Added for Globalization*/
2361 cursor c_get_competencies_trainer is
2362 select e.competence_id
2363 , e.business_group_id
2364 , e.proficiency_level_id
2365 , e.effective_date_from
2366 , e.effective_date_to
2367 , e.comments
2368 , e.attribute_category
2369 , e.attribute1
2370 , e.attribute2
2371 , e.attribute3
2372 , e.attribute4
2373 , e.attribute5
2374 , e.attribute6
2375 , e.attribute7
2376 , e.attribute8
2377 , e.attribute9
2378 , e.attribute10
2379 , e.attribute11
2380 , e.attribute12
2381 , e.attribute13
2382 , e.attribute14
2383 , e.attribute15
2384 , e.attribute16
2385 , e.attribute17
2386 , e.attribute18
2387 , e.attribute19
2388 , e.attribute20
2389 from per_competence_elements e
2390 where e.object_id = p_activity_version_from
2391 and e.type='TRAINER';
2392 --
2393 cursor c_get_competencies_other is
2394 select e.competence_id
2395 , e.business_group_id
2396 , e.proficiency_level_id
2397 , e.effective_date_from
2398 , e.effective_date_to
2399 , e.comments
2400 , e.attribute_category
2401 , e.attribute1
2402 , e.attribute2
2403 , e.attribute3
2404 , e.attribute4
2405 , e.attribute5
2406 , e.attribute6
2407 , e.attribute7
2408 , e.attribute8
2409 , e.attribute9
2410 , e.attribute10
2411 , e.attribute11
2412 , e.attribute12
2413 , e.attribute13
2414 , e.attribute14
2415 , e.attribute15
2416 , e.attribute16
2417 , e.attribute17
2418 , e.attribute18
2419 , e.attribute19
2420 , e.attribute20
2421 from per_competence_elements e
2422 where e.activity_version_id = p_activity_version_from
2423 and e.type = nvl(p_competence_type,'DELIVERY'); -- For Bug 1868713
2424 --
2425 begin
2426 --
2427 -- Issue a savepoint.
2428 --
2429 savepoint copy_competencies;
2430 --
2431 -- Call Before Process User Hook
2432 --
2433 begin
2434 --
2435 -- p_activity_version_from will be passed to p_activity_version_id since the
2436 -- function ota_tav_bus.return_legislation_code expects the parameter
2437 -- activity_version_id and it is the same as the activity_version_from.
2438 --
2439 hr_competence_element_bk6.copy_competencies_b (
2440 p_activity_version_from => p_activity_version_from,
2441 p_activity_version_id => p_activity_version_from,
2442 p_activity_version_to => p_activity_version_to ,
2443 p_competence_type => p_competence_type
2444 );
2445 exception
2446 when hr_api.cannot_find_prog_unit then
2447 hr_api.cannot_find_prog_unit_error
2448 (p_module_name => 'copy_competencies',
2449 p_hook_type => 'BP'
2450 );
2451 end;
2452 --
2453 -- End of Before Process User hook
2454 --
2455 IF p_competence_type= 'TRAINER' then
2456
2457 for comp in c_get_competencies_trainer loop
2458 per_cel_ins.ins
2459 (p_competence_element_id => l_competence_element_id
2460 ,p_object_version_number => l_object_version_number
2461 ,p_type => 'TRAINER'
2462 ,p_business_group_id => comp.business_group_id
2463 ,p_competence_id => comp.competence_id
2464 ,p_proficiency_level_id => comp.proficiency_level_id
2465 ,p_activity_version_id =>NULL-- p_activity_version_to Modified for Globalization
2466 ,p_effective_date_from => comp.effective_date_from
2467 ,p_effective_date_to => comp.effective_date_to
2468 ,p_comments => comp.comments
2469 ,p_attribute_category => comp.attribute_category
2470 ,p_attribute1 => comp.attribute1
2471 ,p_attribute2 => comp.attribute2
2472 ,p_attribute3 => comp.attribute3
2473 ,p_attribute4 => comp.attribute4
2474 ,p_attribute5 => comp.attribute5
2475 ,p_attribute6 => comp.attribute6
2476 ,p_attribute7 => comp.attribute7
2477 ,p_attribute8 => comp.attribute8
2478 ,p_attribute9 => comp.attribute9
2479 ,p_attribute10 => comp.attribute10
2480 ,p_attribute11 => comp.attribute11
2481 ,p_attribute12 => comp.attribute12
2482 ,p_attribute13 => comp.attribute13
2483 ,p_attribute14 => comp.attribute14
2484 ,p_attribute15 => comp.attribute15
2485 ,p_attribute16 => comp.attribute16
2486 ,p_attribute17 => comp.attribute17
2487 ,p_attribute18 => comp.attribute18
2488 ,p_attribute19 => comp.attribute19
2489 ,p_attribute20 => comp.attribute20
2490 ,p_effective_date => sysdate
2491 ,p_validate => FALSE
2492 ,p_object_id => p_activity_version_to /*Added for Globalization*/
2493 );
2494 end loop;
2495 ELSE
2496 for comp in c_get_competencies_other loop
2497 per_cel_ins.ins
2498 (p_competence_element_id => l_competence_element_id
2499 ,p_object_version_number => l_object_version_number
2500 ,p_type => 'DELIVERY'
2501 ,p_business_group_id => comp.business_group_id
2502 ,p_competence_id => comp.competence_id
2503 ,p_proficiency_level_id => comp.proficiency_level_id
2504 ,p_activity_version_id => p_activity_version_to -- Modified for Globalization
2505 ,p_effective_date_from => comp.effective_date_from
2506 ,p_effective_date_to => comp.effective_date_to
2507 ,p_comments => comp.comments
2508 ,p_attribute_category => comp.attribute_category
2509 ,p_attribute1 => comp.attribute1
2510 ,p_attribute2 => comp.attribute2
2511 ,p_attribute3 => comp.attribute3
2512 ,p_attribute4 => comp.attribute4
2513 ,p_attribute5 => comp.attribute5
2514 ,p_attribute6 => comp.attribute6
2515 ,p_attribute7 => comp.attribute7
2516 ,p_attribute8 => comp.attribute8
2517 ,p_attribute9 => comp.attribute9
2518 ,p_attribute10 => comp.attribute10
2519 ,p_attribute11 => comp.attribute11
2520 ,p_attribute12 => comp.attribute12
2521 ,p_attribute13 => comp.attribute13
2522 ,p_attribute14 => comp.attribute14
2523 ,p_attribute15 => comp.attribute15
2524 ,p_attribute16 => comp.attribute16
2525 ,p_attribute17 => comp.attribute17
2526 ,p_attribute18 => comp.attribute18
2527 ,p_attribute19 => comp.attribute19
2528 ,p_attribute20 => comp.attribute20
2529 ,p_effective_date => sysdate
2530 ,p_validate => FALSE
2531 );
2532 end loop;
2533 END IF;
2534
2535 -- Call after Process User Hook
2536 --
2537 begin
2538 --
2539 -- p_activity_version_from will be passed to p_activity_version_id since the
2540 -- function ota_tav_bus.return_legislation_code expects the parameter
2541 -- activity_version_id and it is the same as the activity_version_from.
2542 --
2543 hr_competence_element_bk6.copy_competencies_a (
2544 p_activity_version_from => p_activity_version_from,
2545 p_activity_version_id => p_activity_version_from,
2546 p_activity_version_to => p_activity_version_to ,
2547 p_competence_type => p_competence_type );
2548 exception
2549 when hr_api.cannot_find_prog_unit then
2550 hr_api.cannot_find_prog_unit_error
2551 (p_module_name => 'copy_competencies',
2552 p_hook_type => 'AP'
2553 );
2554 end;
2555 --
2556 -- End of After Process User hook
2557 --
2558 if p_validate then
2559 raise hr_api.validate_enabled;
2560 end if;
2561 --
2562 exception
2563 when hr_api.validate_enabled then
2564 --
2565 -- As the Validate_Enabled exception has been raised
2566 -- we must rollback to the savepoint
2567 --
2568 ROLLBACK TO copy_competencies;
2569 --
2570 --
2571 when others then
2572 --
2573 -- A validation or unexpected error has occurred
2574 --
2575 -- Added as part of fix to bug 632482
2576 --
2577 ROLLBACK TO copy_competencies;
2578 raise;
2579 --
2580 -- End of fix.
2581 --
2582 end copy_competencies;
2583 --
2584 --
2585 -- ---------------------------------------------------------------------------
2586 -- |------------------------< <update_delivered_dates> >---------------------|
2587 -- ---------------------------------------------------------------------------
2588 --
2589 procedure update_delivered_dates
2590 (p_activity_version_id in number,
2591 p_old_start_date in date,
2592 p_start_date in date,
2593 p_old_end_date in date,
2594 p_end_date in date,
2595 p_validate in boolean default FALSE) is
2596 --
2597 v_proc varchar2 (72) := g_package || 'update_delivered_dates';
2598 l_start_date date;
2599 l_end_date date;
2600 l_sd_changed varchar2 (20) := 'N';
2601 l_ed_changed varchar2 (20) := 'N';
2602
2603 l_start_date_changed boolean :=
2604 ota_general.value_changed(p_old_start_date,p_start_date);
2605 l_end_date_changed boolean :=
2606 ota_general.value_changed(p_old_end_date,p_end_date);
2607 --
2608 v_competence_element_id number;
2609 v_object_version_number number;
2610 v_start_date date;
2611 v_end_date date;
2612 v_competence_date_from date;
2613 v_competence_date_to date;
2614
2615 cursor get_competence_element is
2616 select e.competence_element_id
2617 ,e.object_version_number
2618 ,e.effective_date_from
2619 ,e.effective_date_to
2620 ,c.date_from
2621 ,c.date_to
2622 from per_competence_elements e
2623 , per_competences c
2624 where e.activity_version_id = p_activity_version_id
2625 and e.type = 'DELIVERY'
2626 and e.competence_id = c.competence_id
2627 and (((l_sd_changed = 'Y'
2628 and nvl(e.effective_date_from,hr_api.g_sot)
2629 = nvl(p_old_start_date,hr_api.g_sot))
2630 or ( l_sd_changed = 'N'
2631 and l_ed_changed = 'Y'
2632 and nvl(e.effective_date_to,hr_api.g_eot)
2633 = nvl(p_old_end_date,hr_api.g_eot)))
2634 OR ((l_ed_changed = 'Y'
2635 and nvl(e.effective_date_to,hr_api.g_eot)
2636 = nvl(p_old_end_date,hr_api.g_eot))
2637 OR (l_ed_changed = 'N'
2638 and l_sd_changed = 'Y'
2639 and nvl(e.effective_date_from,hr_api.g_sot)
2640 = nvl(p_old_start_date,hr_api.g_sot))));
2641
2642 Begin
2643 --
2644 hr_utility.set_location ('Entering:' || v_proc, 5);
2645 --
2646 -- Issue a savepoint.
2647 --
2648 savepoint update_delivered_dates;
2649 --
2650 -- Call Before Process User Hook
2651 --
2652 begin
2653 hr_competence_element_bk7.update_delivered_dates_b (
2654 p_activity_version_id => p_activity_version_id,
2655 p_old_start_date => p_old_start_date ,
2656 p_start_date => p_start_date ,
2657 p_old_end_date => p_old_end_date ,
2658 p_end_date => p_end_date
2659 );
2660 exception
2661 when hr_api.cannot_find_prog_unit then
2662 hr_api.cannot_find_prog_unit_error
2663 (p_module_name => 'update_delivered_dates',
2664 p_hook_type => 'BP'
2665 );
2666 end;
2667 --
2668 -- End of Before Process User hook
2669 --
2670 -- If niether the start/end date has changed then return
2671 --
2672 If not (l_start_date_changed) and not (l_end_date_changed) Then
2673 return;
2674 End if;
2675 --
2676 -- Set variable to indicate whether start/end date has changed
2677 --
2678 If l_start_date_changed Then
2679 l_sd_changed := 'Y';
2680 End if;
2681
2682 If l_end_date_changed Then
2683 l_ed_changed := 'Y';
2684 End if;
2685 --
2686 -- Fetch the elements that need to be updated
2687 --
2688
2689 Open get_competence_element;
2690 Fetch get_competence_element into v_competence_element_id
2691 ,v_object_version_number
2692 ,v_start_date
2693 ,v_end_date
2694 ,v_competence_date_from
2695 ,v_competence_date_to;
2696
2697 Loop
2698 --
2699 Exit When get_competence_element%notfound
2700 OR get_competence_element%notfound is null;
2701 --
2702 -- If both start and end date have changed then need to determine whether the
2703 -- cel start date matches the old activity start date and also if the the old
2704 -- end date matches the cel end date.
2705 --
2706 If l_start_date_changed and
2707 l_end_date_changed Then
2708 --
2709 -- If the old start date is the same as the cel start date then need to
2710 -- update to the new date
2711 --
2712 If not ota_general.value_changed(p_old_start_date,v_start_date) Then
2713 l_start_date := greatest(p_start_date
2714 ,nvl(v_competence_date_from,hr_api.g_sot));
2715 Else
2716 l_start_date := hr_api.g_date;
2717 End if;
2718 --
2719 -- If the old end date is the same as the cel end date then need to
2720 -- update to the new date
2721 --
2722 If not ota_general.value_changed(p_old_end_date,v_end_date) Then
2723 l_end_date := least(p_end_date
2724 ,nvl(v_competence_date_to,hr_api.g_eot));
2725 Else
2726 l_end_date := hr_api.g_date;
2727 End if;
2728 --
2729 Else
2730 --
2731 -- If the start has changed then update the resource usage with the new
2732 -- activity start date, otherwise use the default value so that the
2733 -- existing resource usage start date is used
2734 --
2735 If l_start_date_changed Then
2736 l_start_date := greatest(p_start_date
2737 ,nvl(v_competence_date_from,hr_api.g_sot));
2738 Else
2739 l_start_date := hr_api.g_date;
2740 End if;
2741 --
2742 --
2743 -- If the end date has changed then update the resource usage with the new
2744 -- activity end date otherwise, use the default value so that the existing
2745
2746 -- resource usage end date is used
2747 --
2748 If l_end_date_changed Then
2749 l_end_date := least(p_end_date
2750 ,nvl(v_competence_date_to,hr_api.g_eot));
2751 Else
2752 l_end_date := hr_api.g_date;
2753 End if;
2754 --
2755 End if;
2756 --
2757 -- Now, perform the update
2758 --
2759 per_cel_upd.upd(p_competence_element_id => v_competence_element_id
2760 ,p_object_version_number => v_object_version_number
2761 ,p_effective_date_from => l_start_date
2762 ,p_effective_date_to => l_end_date
2763 ,p_effective_date => sysdate
2764 );
2765 --
2766 Fetch get_competence_element into v_competence_element_id
2767 ,v_object_version_number
2768 ,v_start_date
2769 ,v_end_date
2770 ,v_competence_date_from
2771 ,v_competence_date_to;
2772 End loop;
2773 --
2774 Close get_competence_element;
2775 --
2776 -- Call After Process User Hook
2777 --
2778 begin
2779 hr_competence_element_bk7.update_delivered_dates_a (
2780 p_activity_version_id => p_activity_version_id,
2781 p_old_start_date => p_old_start_date ,
2782 p_start_date => p_start_date ,
2783 p_old_end_date => p_old_end_date ,
2784 p_end_date => p_end_date
2785 );
2786 exception
2787 when hr_api.cannot_find_prog_unit then
2788 hr_api.cannot_find_prog_unit_error
2789 (p_module_name => 'update_delivered_dates',
2790 p_hook_type => 'AP'
2791 );
2792 end;
2793 --
2794 -- End of after Process User hook
2795 --
2796 if p_validate then
2797 raise hr_api.validate_enabled;
2798 end if;
2799 --
2800 exception
2801 when hr_api.validate_enabled then
2802 --
2803 -- As the Validate_Enabled exception has been raised
2804 -- we must rollback to the savepoint
2805 --
2806 ROLLBACK TO update_delivered_dates;
2807 --
2808 when others then
2809 --
2810 -- A validation or unexpected error has occurred
2811 --
2812 -- Added as part of fix to bug 632482
2813 --
2814 ROLLBACK TO update_delivered_dates;
2815 raise;
2816 --
2817 -- End of fix.
2818 --
2819 --
2820 hr_utility.set_location (' Leaving:' || v_proc, 5);
2821 --
2822 End update_delivered_dates;
2823 --
2824 end hr_competence_element_api;