[Home] [Help]
PACKAGE BODY: APPS.HR_COMPETENCE_ELEMENT_API
Source
1 Package Body hr_competence_element_api as
2 /* $Header: pecelapi.pkb 120.4 2009/04/21 10:15:15 rsykam ship $ */
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 l_new_effective_date_to DATE DEFAULT NULL;
1584 --
1585 cursor c_get_comp_element is
1586 select competence_element_id
1587 , object_version_number
1588 , proficiency_level_id
1589 , effective_date_from
1590 , effective_date_to -- added for bug#1623036
1591 from per_competence_elements
1592 where person_id = p_person_id
1593 and type = 'PERSONAL'
1594 and competence_id = p_competence_id
1595 order by effective_date_from desc;
1596
1597 Cursor csr_chk_date_overlap is
1598 select competence_element_id
1599 , object_version_number
1600 , proficiency_level_id
1601 , effective_date_from
1602 , effective_date_to
1603 from per_competence_elements
1604 where person_id = p_person_id
1605 and type = 'PERSONAL'
1606 and competence_id = p_competence_id
1607 and
1608 p_effective_date_from between effective_date_from and Nvl(effective_Date_to,p_effective_date_from) ;
1609
1610 -- get from_date nearest to new record from_date
1611 cursor c_get_nearest_from_date is
1612 select competence_element_id
1613 , object_version_number
1614 , proficiency_level_id
1615 , effective_date_from
1616 , effective_date_to -- added for bug#1623036
1617 from per_competence_elements
1618 where person_id = p_person_id
1619 and type = 'PERSONAL'
1620 and competence_id = p_competence_id
1621 and
1622 p_effective_date_from < effective_date_from
1623 order by effective_date_from asc;
1624 --
1625 begin
1626 --
1627 -- Determine if there is an existing competence element
1628 --
1629 --
1630 -- Issue a savepoint.
1631 --
1632 savepoint maintain_student_comp_element;
1633 --
1634 -- Call Before Process User Hook
1635 --
1636 begin
1637 hr_competence_element_bk5.maintain_student_comp_elemen_b (
1638 p_person_id => p_person_id
1639 ,p_business_group_id => p_business_group_id
1640 ,p_competence_id => p_competence_id
1641 ,p_proficiency_level_id => p_proficiency_level_id
1642 ,p_effective_date_from => p_effective_date_from
1643 ,p_effective_date_to => p_effective_date_to
1644 ,p_certification_date => p_certification_date
1645 ,p_certification_method => p_certification_method
1646 ,p_next_certification_date => p_next_certification_date
1647 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1648 ,p_comments => p_comments
1649 ,p_effective_date => p_effective_date
1650 );
1651 exception
1652 when hr_api.cannot_find_prog_unit then
1653 hr_api.cannot_find_prog_unit_error
1654 (p_module_name => 'maintain_student_comp_element',
1655 p_hook_type => 'BP'
1656 );
1657 end;
1658 --
1659 -- End of Before Process User hook
1660 --
1661 open c_get_comp_element;
1662 fetch c_get_comp_element into l_competence_element_id,
1663 l_object_version_number,
1664 l_proficiency_level_id,
1665 l_effective_date_from,
1666 l_effective_date_to; -- added for bug#1623036
1667
1668 close c_get_comp_element;
1669 --
1670 p_competence_created := 0;
1671 --
1672 if l_competence_element_id is null then
1673 create_competence_element
1674 (p_competence_element_id => l_new_competence_element_id
1675 ,p_object_version_number => l_new_object_version_number
1676 ,p_type => 'PERSONAL'
1677 ,p_person_id => p_person_id
1678 ,p_business_group_id => p_business_group_id
1679 ,p_competence_id => p_competence_id
1680 ,p_proficiency_level_id => p_proficiency_level_id
1681 ,p_effective_date_from => p_effective_date_from
1682 ,p_effective_date_to => p_effective_date_to
1683 ,p_certification_date => p_certification_date
1684 ,p_certification_method => p_certification_method
1685 ,p_next_certification_date => p_next_certification_date
1686 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1687 ,p_comments => p_comments
1688 ,p_effective_date => p_effective_date);
1689 --
1690 p_competence_created := 1;
1691 --
1692 else
1693 /********* NEW Logic***********8888*/
1694 if p_effective_date_from < l_effective_date_from then
1695 -- implies new record from date is less than the max(from date) of existing record.
1696 -- First check whether p_eff_date_from lies between some already existing record dates
1697 open csr_chk_date_overlap;
1698 fetch csr_chk_date_overlap into l_competence_element_id,
1699 l_object_version_number,
1700 l_proficiency_level_id,
1701 l_effective_date_from,
1702 l_effective_date_to;
1703
1704
1705 --Dbms_output.put_line('UPD : Date already exists');
1706 -- If the New E.F.Date is between the existing From and To date then simply overwrite the level.
1707 /* l_object_version_number := l_new_object_version_number ;
1708 l_competence_element_id := l_new_competence_element_id ;
1709
1710 l_effective_Date_to := l_new_effective_date_to ;*/
1711 If csr_chk_date_overlap%NotFound then
1712 -- since p_eff_date_from doesn't lie between already existing record dates
1713 -- get the record whose l_effective_from_date is just greater than p_effective_from_date
1714
1715 open c_get_nearest_from_date;
1716 fetch c_get_nearest_from_date into l_competence_element_id,
1717 l_object_version_number,
1718 l_proficiency_level_id,
1719 l_effective_date_from,
1720 l_effective_date_to;
1721
1722 Close c_get_nearest_from_date;
1723
1724 end if; --- csr_chk_date_overlap%Found
1725
1726 close csr_chk_date_overlap;
1727 end if; ---p_effective_date_from < l_effective_date_from
1728
1729
1730 if nvl(l_proficiency_level_id, -1) = nvl(p_proficiency_level_id, -1) then
1731
1732 if l_effective_date_from > p_effective_date_from then
1733 l_new_effective_date_from := p_effective_date_from;
1734 else
1735 l_new_effective_date_from := l_effective_date_from;
1736 end if;
1737 elsif nvl(l_proficiency_level_id, -1) < nvl(p_proficiency_level_id, -1) then
1738
1739 l_new_effective_date_from := p_effective_date_from;
1740 Else
1741 --
1742 -- start modifications for bg #1623036
1743 --
1744 /* if l_effective_date_from > p_effective_date_from then
1745 l_new_effective_date_from := l_effective_date_from;
1746 else*/
1747 l_new_effective_date_from := p_effective_date_from;
1748 -- end if;
1749
1750 end if;
1751 if nvl(l_proficiency_level_id, -1) > nvl(p_proficiency_level_id, -1) then
1752 -- new record has level lesser then existing record
1753
1754 --
1755 --Bug 2366782
1756 if l_effective_date_to is null OR l_effective_date_to >= p_effective_date_from then
1757
1758 /********* NEW Logic***********8888*/
1759
1760 if (l_effective_date_from < p_effective_date_from) then
1761 if ((l_effective_date_to is null or l_effective_date_to>= nvl(p_effective_date_to,hr_api.g_eot))) then
1762 -- new record lies within span of existing record
1763 null;
1764
1765 elsif (l_effective_date_to is not null and l_effective_date_to < nvl(p_effective_date_to,hr_api.g_eot) ) then
1766 /*update_personal_comp_element
1767 (p_competence_element_id => l_competence_element_id
1768 ,p_object_version_number => l_object_version_number
1769 ,p_proficiency_level_id => l_proficiency_level_id
1770 ,p_effective_date_from => l_effective_date_from
1771 ,p_effective_date_to => p_effective_date_from -1
1772 ,p_certification_date => p_certification_date
1773 ,p_certification_method => p_certification_method
1774 ,p_next_certification_date => p_next_certification_date
1775 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1776 ,p_comments => p_comments
1777 ,p_effective_date => p_effective_date
1778 ,p_ins_ovn => l_new_object_version_number
1779 ,p_ins_comp_id => l_new_competence_element_id);*/
1780
1781
1782 create_competence_element
1783 (p_competence_element_id => l_new_competence_element_id
1784 ,p_object_version_number => l_new_object_version_number
1785 ,p_type => 'PERSONAL'
1786 ,p_person_id => p_person_id
1787 ,p_business_group_id => p_business_group_id
1788 ,p_competence_id => p_competence_id
1789 ,p_proficiency_level_id => p_proficiency_level_id
1790 ,p_effective_date_from => l_effective_date_to+1
1791 ,p_effective_date_to => p_effective_date_to
1792 ,p_certification_date => p_certification_date
1793 ,p_certification_method => p_certification_method
1794 ,p_next_certification_date => p_next_certification_date
1795 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1796 ,p_comments => p_comments
1797 ,p_effective_date => p_effective_date);
1798
1799 --
1800 p_competence_created := 1;
1801 end if;
1802 else -- for (l_effective_date_from < p_effective_date_from)
1803 --if nvl(l_proficiency_level_id, -1) < nvl(p_proficiency_level_id, -1) then
1804 -- implies new record has level lesser then existing level but it's start date
1805 -- is also lesser than exisitng record date
1806
1807 create_competence_element
1808 (p_competence_element_id => l_new_competence_element_id
1809 ,p_object_version_number => l_new_object_version_number
1810 ,p_type => 'PERSONAL'
1811 ,p_person_id => p_person_id
1812 ,p_business_group_id => p_business_group_id
1813 ,p_competence_id => p_competence_id
1814 ,p_proficiency_level_id => p_proficiency_level_id
1815 ,p_effective_date_from => p_effective_date_from
1816 ,p_effective_date_to => l_effective_date_from -1
1817 ,p_certification_date => p_certification_date
1818 ,p_certification_method => p_certification_method
1819 ,p_next_certification_date => p_next_certification_date
1820 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1821 ,p_comments => p_comments
1822 ,p_effective_date => p_effective_date);
1823
1824 if (nvl(l_effective_date_to,hr_api.g_eot) < nvl(p_effective_date_to,hr_api.g_eot)) then
1825 create_competence_element
1826 (p_competence_element_id => l_new_competence_element_id
1827 ,p_object_version_number => l_new_object_version_number
1828 ,p_type => 'PERSONAL'
1829 ,p_person_id => p_person_id
1830 ,p_business_group_id => p_business_group_id
1831 ,p_competence_id => p_competence_id
1832 ,p_proficiency_level_id => p_proficiency_level_id
1833 ,p_effective_date_from => l_effective_date_to+1
1834 ,p_effective_date_to => p_effective_date_to
1835 ,p_certification_date => p_certification_date
1836 ,p_certification_method => p_certification_method
1837 ,p_next_certification_date => p_next_certification_date
1838 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1839 ,p_comments => p_comments
1840 ,p_effective_date => p_effective_date);
1841
1842
1843 end if;
1844 p_competence_created := 1;
1845 end if;
1846 elsif l_effective_date_to is not null and p_effective_date_from >= l_effective_date_to then
1847 create_competence_element
1848 (p_competence_element_id => l_new_competence_element_id
1849 ,p_object_version_number => l_new_object_version_number
1850 ,p_type => 'PERSONAL'
1851 ,p_person_id => p_person_id
1852 ,p_business_group_id => p_business_group_id
1853 ,p_competence_id => p_competence_id
1854 ,p_proficiency_level_id => p_proficiency_level_id
1855 ,p_effective_date_from => l_new_effective_date_from
1856 ,p_effective_date_to => p_effective_date_to
1857 ,p_certification_date => p_certification_date
1858 ,p_certification_method => p_certification_method
1859 ,p_next_certification_date => p_next_certification_date
1860 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1861 ,p_comments => p_comments
1862 ,p_effective_date => p_effective_date);
1863
1864 --
1865 p_competence_created := 1;
1866
1867 else
1868 create_competence_element
1869 (p_competence_element_id => l_new_competence_element_id
1870 ,p_object_version_number => l_new_object_version_number
1871 ,p_type => 'PERSONAL'
1872 ,p_person_id => p_person_id
1873 ,p_business_group_id => p_business_group_id
1874 ,p_competence_id => p_competence_id
1875 ,p_proficiency_level_id => p_proficiency_level_id
1876 ,p_effective_date_from => l_new_effective_date_from
1877 ,p_effective_date_to => p_effective_date_to
1878 ,p_certification_date => p_certification_date
1879 ,p_certification_method => p_certification_method
1880 ,p_next_certification_date => p_next_certification_date
1881 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1882 ,p_comments => p_comments
1883 ,p_effective_date => p_effective_date);
1884 p_competence_created := 1;
1885
1886 end if;
1887
1888 --
1889 elsif nvl(l_proficiency_level_id, -1) < nvl(p_proficiency_level_id, -1) then
1890 --
1891 --Bug 2366782
1892 if l_effective_date_to is null OR l_effective_date_to >= p_effective_date_from then
1893
1894 /********* NEW Logic***********8888*/
1895 if l_effective_date_from < p_effective_date_from then
1896 update_personal_comp_element
1897 (p_competence_element_id => l_competence_element_id
1898 ,p_object_version_number => l_object_version_number
1899 ,p_proficiency_level_id => l_proficiency_level_id
1900 ,p_effective_date_from => l_effective_date_from
1901 ,p_effective_date_to => p_effective_date_from -1
1902 ,p_certification_date => p_certification_date
1903 ,p_certification_method => p_certification_method
1904 ,p_next_certification_date => p_next_certification_date
1905 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1906 ,p_comments => p_comments
1907 ,p_effective_date => p_effective_date
1908 ,p_ins_ovn => l_new_object_version_number
1909 ,p_ins_comp_id => l_new_competence_element_id);
1910
1911
1912 create_competence_element
1913 (p_competence_element_id => l_new_competence_element_id
1914 ,p_object_version_number => l_new_object_version_number
1915 ,p_type => 'PERSONAL'
1916 ,p_person_id => p_person_id
1917 ,p_business_group_id => p_business_group_id
1918 ,p_competence_id => p_competence_id
1919 ,p_proficiency_level_id => p_proficiency_level_id
1920 ,p_effective_date_from => l_new_effective_date_from
1921 ,p_effective_date_to => p_effective_date_to
1922 ,p_certification_date => p_certification_date
1923 ,p_certification_method => p_certification_method
1924 ,p_next_certification_date => p_next_certification_date
1925 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1926 ,p_comments => p_comments
1927 ,p_effective_date => p_effective_date);
1928
1929 if (p_effective_date_to is not null and nvl(l_effective_date_to,hr_api.g_eot)> p_effective_date_to) then
1930
1931 create_competence_element
1932 (p_competence_element_id => l_new_competence_element_id
1933 ,p_object_version_number => l_new_object_version_number
1934 ,p_type => 'PERSONAL'
1935 ,p_person_id => p_person_id
1936 ,p_business_group_id => p_business_group_id
1937 ,p_competence_id => p_competence_id
1938 ,p_proficiency_level_id => l_proficiency_level_id
1939 ,p_effective_date_from => p_effective_date_to+1
1940 ,p_effective_date_to => l_effective_date_to
1941 ,p_certification_date => p_certification_date
1942 ,p_certification_method => p_certification_method
1943 ,p_next_certification_date => p_next_certification_date
1944 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1945 ,p_comments => p_comments
1946 ,p_effective_date => p_effective_date);
1947
1948 end if;
1949 --
1950 p_competence_created := 2;
1951 else
1952 --if nvl(l_proficiency_level_id, -1) < nvl(p_proficiency_level_id, -1) then
1953 -- implies new record has level greater then existing level but it's start date
1954 -- is less than exisitng record date
1955 -- update the exisitng record to new level and new start date
1956 if (p_effective_date_to is null) then
1957 --implies new record covers entire span of existing record
1958 delete_competence_element
1959 (p_validate => p_validate,
1960 p_competence_element_id => l_competence_element_id
1961 ,p_object_version_number => l_object_version_number
1962 );
1963 create_competence_element
1964 (p_competence_element_id => l_new_competence_element_id
1965 ,p_object_version_number => l_new_object_version_number
1966 ,p_type => 'PERSONAL'
1967 ,p_person_id => p_person_id
1968 ,p_business_group_id => p_business_group_id
1969 ,p_competence_id => p_competence_id
1970 ,p_proficiency_level_id => p_proficiency_level_id
1971 ,p_effective_date_from => p_effective_date_from
1972 ,p_effective_date_to => p_effective_date_to
1973 ,p_certification_date => p_certification_date
1974 ,p_certification_method => p_certification_method
1975 ,p_next_certification_date => p_next_certification_date
1976 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1977 ,p_comments => p_comments
1978 ,p_effective_date => p_effective_date);
1979
1980 elsif (p_effective_date_to is not null and p_effective_date_to < nvl(l_effective_date_to,hr_api.g_eot)) then
1981 -- new record has an end date less than existing record
1982 update_personal_comp_element
1983 (p_competence_element_id => l_competence_element_id
1984 ,p_object_version_number => l_object_version_number
1985 ,p_proficiency_level_id => l_proficiency_level_id
1986 ,p_effective_date_from => p_effective_date_to+1
1987 ,p_effective_date_to => l_effective_date_to
1988 ,p_certification_date => p_certification_date
1989 ,p_certification_method => p_certification_method
1990 ,p_next_certification_date => p_next_certification_date
1991 ,p_source_of_proficiency_level => p_source_of_proficiency_level
1992 ,p_comments => p_comments
1993 ,p_effective_date => p_effective_date
1994 ,p_ins_ovn => l_new_object_version_number
1995 ,p_ins_comp_id => l_new_competence_element_id);
1996
1997 create_competence_element
1998 (p_competence_element_id => l_new_competence_element_id
1999 ,p_object_version_number => l_new_object_version_number
2000 ,p_type => 'PERSONAL'
2001 ,p_person_id => p_person_id
2002 ,p_business_group_id => p_business_group_id
2003 ,p_competence_id => p_competence_id
2004 ,p_proficiency_level_id => p_proficiency_level_id
2005 ,p_effective_date_from => p_effective_date_from
2006 ,p_effective_date_to => p_effective_date_to
2007 ,p_certification_date => p_certification_date
2008 ,p_certification_method => p_certification_method
2009 ,p_next_certification_date => p_next_certification_date
2010 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2011 ,p_comments => p_comments
2012 ,p_effective_date => p_effective_date);
2013
2014 else
2015 update_personal_comp_element
2016 (p_competence_element_id => l_competence_element_id
2017 ,p_object_version_number => l_object_version_number
2018 ,p_proficiency_level_id => p_proficiency_level_id
2019 ,p_effective_date_from => l_new_effective_date_from
2020 ,p_effective_date_to => p_effective_date_to
2021 ,p_certification_date => p_certification_date
2022 ,p_certification_method => p_certification_method
2023 ,p_next_certification_date => p_next_certification_date
2024 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2025 ,p_comments => p_comments
2026 ,p_effective_date => p_effective_date
2027 ,p_ins_ovn => l_new_object_version_number
2028 ,p_ins_comp_id => l_new_competence_element_id);
2029 end if;
2030 /* else
2031 -- implies new record has level less then existing level and it's start date
2032 -- is less than exisitng record date
2033 -- create a new record with new level and new start date and end it
2034 -- one day before the exisitng record start date
2035 create_competence_element
2036 (p_competence_element_id => l_new_competence_element_id
2037 ,p_object_version_number => l_new_object_version_number
2038 ,p_type => 'PERSONAL'
2039 ,p_person_id => p_person_id
2040 ,p_business_group_id => p_business_group_id
2041 ,p_competence_id => p_competence_id
2042 ,p_proficiency_level_id => p_proficiency_level_id
2043 ,p_effective_date_from => l_new_effective_date_from
2044 ,p_effective_date_to => l_effective_date_from-1
2045 ,p_certification_date => p_certification_date
2046 ,p_certification_method => p_certification_method
2047 ,p_next_certification_date => p_next_certification_date
2048 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2049 ,p_comments => p_comments
2050 ,p_effective_date => p_effective_date);
2051
2052 end if;*/
2053 p_competence_created := 2;
2054
2055 end if;
2056 elsif l_effective_date_to is not null and p_effective_date_from >= l_effective_date_to then
2057 create_competence_element
2058 (p_competence_element_id => l_new_competence_element_id
2059 ,p_object_version_number => l_new_object_version_number
2060 ,p_type => 'PERSONAL'
2061 ,p_person_id => p_person_id
2062 ,p_business_group_id => p_business_group_id
2063 ,p_competence_id => p_competence_id
2064 ,p_proficiency_level_id => p_proficiency_level_id
2065 ,p_effective_date_from => l_new_effective_date_from
2066 ,p_effective_date_to => p_effective_date_to
2067 ,p_certification_date => p_certification_date
2068 ,p_certification_method => p_certification_method
2069 ,p_next_certification_date => p_next_certification_date
2070 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2071 ,p_comments => p_comments
2072 ,p_effective_date => p_effective_date);
2073
2074 --
2075 p_competence_created := 1;
2076
2077 else
2078 create_competence_element
2079 (p_competence_element_id => l_new_competence_element_id
2080 ,p_object_version_number => l_new_object_version_number
2081 ,p_type => 'PERSONAL'
2082 ,p_person_id => p_person_id
2083 ,p_business_group_id => p_business_group_id
2084 ,p_competence_id => p_competence_id
2085 ,p_proficiency_level_id => p_proficiency_level_id
2086 ,p_effective_date_from => l_new_effective_date_from
2087 ,p_effective_date_to => p_effective_date_to
2088 ,p_certification_date => p_certification_date
2089 ,p_certification_method => p_certification_method
2090 ,p_next_certification_date => p_next_certification_date
2091 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2092 ,p_comments => p_comments
2093 ,p_effective_date => p_effective_date);
2094 p_competence_created := 1;
2095
2096 end if;
2097 --Bug 2366782
2098 --
2099 elsif nvl(l_proficiency_level_id, -1) = nvl(p_proficiency_level_id, -1) then
2100 --
2101 if l_effective_date_to is null and p_effective_date_to is null and l_effective_date_from > p_effective_date_from then
2102 --
2103 update_personal_comp_element
2104 (p_competence_element_id => l_competence_element_id
2105 ,p_object_version_number => l_object_version_number
2106 ,p_proficiency_level_id => p_proficiency_level_id
2107 ,p_effective_date_from => l_new_effective_date_from
2108 ,p_effective_date_to => p_effective_date_to
2109 ,p_certification_date => p_certification_date
2110 ,p_certification_method => p_certification_method
2111 ,p_next_certification_date => p_next_certification_date
2112 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2113 ,p_comments => p_comments
2114 ,p_effective_date => p_effective_date
2115 ,p_ins_ovn => l_new_object_version_number
2116 ,p_ins_comp_id => l_new_competence_element_id);
2117 --
2118 p_competence_created := 1;
2119 --
2120 elsif l_effective_date_to is null and p_effective_date_to is not null then
2121 --
2122 if p_effective_date_to >= l_effective_date_from then
2123 update_personal_comp_element
2124 (p_competence_element_id => l_competence_element_id
2125 ,p_object_version_number => l_object_version_number
2126 ,p_proficiency_level_id => p_proficiency_level_id
2127 ,p_effective_date_from => l_new_effective_date_from
2128 ,p_effective_date_to => l_effective_date_to
2129 ,p_certification_date => p_certification_date
2130 ,p_certification_method => p_certification_method
2131 ,p_next_certification_date => p_next_certification_date
2132 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2133 ,p_comments => p_comments
2134 ,p_effective_date => p_effective_date
2135 ,p_ins_ovn => l_new_object_version_number
2136 ,p_ins_comp_id => l_new_competence_element_id);
2137
2138 else
2139 -- new record ends before exisitng record start date.
2140 create_competence_element
2141 (p_competence_element_id => l_new_competence_element_id
2142 ,p_object_version_number => l_new_object_version_number
2143 ,p_type => 'PERSONAL'
2144 ,p_person_id => p_person_id
2145 ,p_business_group_id => p_business_group_id
2146 ,p_competence_id => p_competence_id
2147 ,p_proficiency_level_id => p_proficiency_level_id
2148 ,p_effective_date_from => p_effective_date_from
2149 ,p_effective_date_to => p_effective_date_to
2150 ,p_certification_date => p_certification_date
2151 ,p_certification_method => p_certification_method
2152 ,p_next_certification_date => p_next_certification_date
2153 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2154 ,p_comments => p_comments
2155 ,p_effective_date => p_effective_date);
2156
2157 end if;
2158 --
2159 p_competence_created := 1;
2160 --
2161 elsif l_effective_date_to is not null
2162 and (p_effective_date_to is null and p_effective_date_from > l_effective_date_to) then
2163 --
2164 create_competence_element
2165 (p_competence_element_id => l_new_competence_element_id
2166 ,p_object_version_number => l_new_object_version_number
2167 ,p_type => 'PERSONAL'
2168 ,p_person_id => p_person_id
2169 ,p_business_group_id => p_business_group_id
2170 ,p_competence_id => p_competence_id
2171 ,p_proficiency_level_id => p_proficiency_level_id
2172 ,p_effective_date_from => p_effective_date_from
2173 ,p_effective_date_to => p_effective_date_to
2174 ,p_certification_date => p_certification_date
2175 ,p_certification_method => p_certification_method
2176 ,p_next_certification_date => p_next_certification_date
2177 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2178 ,p_comments => p_comments
2179 ,p_effective_date => p_effective_date);
2180 --
2181 p_competence_created := 1;
2182 --
2183 elsif l_effective_date_to is not null and p_effective_date_to is not null then
2184 --
2185 if l_effective_date_from <= p_effective_date_from
2186 and l_effective_date_to >= p_effective_date_to then
2187 --
2188 null;
2189 --
2190 elsif l_effective_date_to < p_effective_date_from then
2191 --
2192 create_competence_element
2193 (p_competence_element_id => l_new_competence_element_id
2194 ,p_object_version_number => l_new_object_version_number
2195 ,p_type => 'PERSONAL'
2196 ,p_person_id => p_person_id
2197 ,p_business_group_id => p_business_group_id
2198 ,p_competence_id => p_competence_id
2199 ,p_proficiency_level_id => p_proficiency_level_id
2200 ,p_effective_date_from => p_effective_date_from
2201 ,p_effective_date_to => p_effective_date_to
2202 ,p_certification_date => p_certification_date
2203 ,p_certification_method => p_certification_method
2204 ,p_next_certification_date => p_next_certification_date
2205 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2206 ,p_comments => p_comments
2207 ,p_effective_date => p_effective_date);
2208 --
2209 p_competence_created := 1;
2210 --
2211 else
2212 --
2213 if p_effective_date_to >= l_effective_date_from then
2214 IF l_effective_date_to >= p_effective_date_to THEN
2215 l_new_effective_date_to := l_effective_date_to;
2216 ELSE
2217 l_new_effective_date_to := p_effective_date_to;
2218 END IF;
2219
2220 update_personal_comp_element
2221 (p_competence_element_id => l_competence_element_id
2222 ,p_object_version_number => l_object_version_number
2223 ,p_proficiency_level_id => p_proficiency_level_id
2224 ,p_effective_date_from => l_new_effective_date_from
2225 ,p_effective_date_to => l_new_effective_date_to
2226 ,p_certification_date => p_certification_date
2227 ,p_certification_method => p_certification_method
2228 ,p_next_certification_date => p_next_certification_date
2229 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2230 ,p_comments => p_comments
2231 ,p_effective_date => p_effective_date
2232 ,p_ins_ovn => l_new_object_version_number
2233 ,p_ins_comp_id => l_new_competence_element_id);
2234
2235 else
2236 -- new record ends before exisitng record start date.
2237 create_competence_element
2238 (p_competence_element_id => l_new_competence_element_id
2239 ,p_object_version_number => l_new_object_version_number
2240 ,p_type => 'PERSONAL'
2241 ,p_person_id => p_person_id
2242 ,p_business_group_id => p_business_group_id
2243 ,p_competence_id => p_competence_id
2244 ,p_proficiency_level_id => p_proficiency_level_id
2245 ,p_effective_date_from => p_effective_date_from
2246 ,p_effective_date_to => p_effective_date_to
2247 ,p_certification_date => p_certification_date
2248 ,p_certification_method => p_certification_method
2249 ,p_next_certification_date => p_next_certification_date
2250 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2251 ,p_comments => p_comments
2252 ,p_effective_date => p_effective_date);
2253
2254 end if;
2255 --
2256 p_competence_created := 1;
2257 --
2258 end if;
2259 elsif l_effective_date_to is not null
2260 and (p_effective_date_to is null and p_effective_date_from < l_effective_date_to) then
2261
2262 --Bug 2366782
2263 update_personal_comp_element
2264 (p_competence_element_id => l_competence_element_id
2265 ,p_object_version_number => l_object_version_number
2266 ,p_proficiency_level_id => p_proficiency_level_id
2267 ,p_effective_date_from => l_new_effective_date_from
2268 ,p_effective_date_to => p_effective_date_to
2269 ,p_certification_date => p_certification_date
2270 ,p_certification_method => p_certification_method
2271 ,p_next_certification_date => p_next_certification_date
2272 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2273 ,p_comments => p_comments
2274 ,p_effective_date => p_effective_date
2275 ,p_ins_ovn => l_new_object_version_number
2276 ,p_ins_comp_id => l_new_competence_element_id);
2277 --
2278 --
2279 p_competence_created := 1;
2280
2281 --Bug 2366782
2282 end if;
2283 --
2284 end if;
2285 --
2286 end if;
2287 --
2288 -- end modifications for bug #1623036
2289 --
2290 -- Call After Process User Hook
2291 --
2292
2293 begin
2294 hr_competence_element_bk5.maintain_student_comp_elemen_a (
2295 p_person_id => p_person_id
2296 ,p_business_group_id => p_business_group_id
2297 ,p_competence_id => p_competence_id
2298 ,p_proficiency_level_id => p_proficiency_level_id
2299 ,p_effective_date_from => l_new_effective_date_from
2300 ,p_effective_date_to => p_effective_date_to
2301 ,p_certification_date => p_certification_date
2302 ,p_certification_method => p_certification_method
2303 ,p_next_certification_date => p_next_certification_date
2304 ,p_source_of_proficiency_level => p_source_of_proficiency_level
2305 ,p_comments => p_comments
2306 ,p_effective_date => p_effective_date
2307 ,p_competence_created => p_competence_created
2308 );
2309 exception
2310 when hr_api.cannot_find_prog_unit then
2311 hr_api.cannot_find_prog_unit_error
2312 (p_module_name => 'maintain_student_comp_element',
2313 p_hook_type => 'AP'
2314 );
2315 end;
2316 --
2317 -- End of After Process User hook
2318 --
2319 if p_validate then
2320 raise hr_api.validate_enabled;
2321 end if;
2322 --
2323
2324 exception
2325 when hr_api.validate_enabled then
2326 --
2327 -- As the Validate_Enabled exception has been raised
2328 -- we must rollback to the savepoint
2329 --
2330 ROLLBACK TO maintain_student_comp_element;
2331 --
2332 -- Only set output warning arguments
2333 -- (Any key or derived arguments must be set to null
2334 -- when validation only mode is being used.)
2335 --
2336 p_competence_created := null;
2337 --
2338 when others then
2339 --
2340 -- A validation or unexpected error has occurred
2341 --
2342 -- Added as part of fix to bug 632482
2343 --
2344 p_competence_created := null;
2345 --
2346 ROLLBACK TO maintain_student_comp_element;
2347 raise;
2348 --
2349 -- End of fix.
2350 --
2351 end maintain_student_comp_element;
2352 --
2353 --
2354 --
2355 -- ---------------------------------------------------------------------------
2356 -- |--------------------------< <copy_competencies> >------------------------|
2357 -- ---------------------------------------------------------------------------
2358 --
2359 procedure copy_competencies(p_activity_version_from number
2360 ,p_activity_version_to number
2361 ,p_competence_type VARCHAR2 default null -- Added for bug 1868713
2362 ,p_validate boolean default FALSE) is
2363 --
2364 l_competence_element_id number;
2365 l_object_version_number number;
2366 --
2367 /*Added for Globalization*/
2368 cursor c_get_competencies_trainer is
2369 select e.competence_id
2370 , e.business_group_id
2371 , e.proficiency_level_id
2372 , e.effective_date_from
2373 , e.effective_date_to
2374 , e.comments
2375 , e.attribute_category
2376 , e.attribute1
2377 , e.attribute2
2378 , e.attribute3
2379 , e.attribute4
2380 , e.attribute5
2381 , e.attribute6
2382 , e.attribute7
2383 , e.attribute8
2384 , e.attribute9
2385 , e.attribute10
2386 , e.attribute11
2387 , e.attribute12
2388 , e.attribute13
2389 , e.attribute14
2390 , e.attribute15
2391 , e.attribute16
2392 , e.attribute17
2393 , e.attribute18
2394 , e.attribute19
2395 , e.attribute20
2396 from per_competence_elements e
2397 where e.object_id = p_activity_version_from
2398 and e.type='TRAINER';
2399 --
2400 cursor c_get_competencies_other is
2401 select e.competence_id
2402 , e.business_group_id
2403 , e.proficiency_level_id
2404 , e.effective_date_from
2405 , e.effective_date_to
2406 , e.comments
2407 , e.attribute_category
2408 , e.attribute1
2409 , e.attribute2
2410 , e.attribute3
2411 , e.attribute4
2412 , e.attribute5
2413 , e.attribute6
2414 , e.attribute7
2415 , e.attribute8
2416 , e.attribute9
2417 , e.attribute10
2418 , e.attribute11
2419 , e.attribute12
2420 , e.attribute13
2421 , e.attribute14
2422 , e.attribute15
2423 , e.attribute16
2424 , e.attribute17
2425 , e.attribute18
2426 , e.attribute19
2427 , e.attribute20
2428 from per_competence_elements e
2429 where e.activity_version_id = p_activity_version_from
2430 and e.type = nvl(p_competence_type,'DELIVERY'); -- For Bug 1868713
2431 --
2432 begin
2433 --
2434 -- Issue a savepoint.
2435 --
2436 savepoint copy_competencies;
2437 --
2438 -- Call Before Process User Hook
2439 --
2440 begin
2441 --
2442 -- p_activity_version_from will be passed to p_activity_version_id since the
2443 -- function ota_tav_bus.return_legislation_code expects the parameter
2444 -- activity_version_id and it is the same as the activity_version_from.
2445 --
2446 hr_competence_element_bk6.copy_competencies_b (
2447 p_activity_version_from => p_activity_version_from,
2448 p_activity_version_id => p_activity_version_from,
2449 p_activity_version_to => p_activity_version_to ,
2450 p_competence_type => p_competence_type
2451 );
2452 exception
2453 when hr_api.cannot_find_prog_unit then
2454 hr_api.cannot_find_prog_unit_error
2455 (p_module_name => 'copy_competencies',
2456 p_hook_type => 'BP'
2457 );
2458 end;
2459 --
2460 -- End of Before Process User hook
2461 --
2462 IF p_competence_type= 'TRAINER' then
2463
2464 for comp in c_get_competencies_trainer loop
2465 per_cel_ins.ins
2466 (p_competence_element_id => l_competence_element_id
2467 ,p_object_version_number => l_object_version_number
2468 ,p_type => 'TRAINER'
2469 ,p_business_group_id => comp.business_group_id
2470 ,p_competence_id => comp.competence_id
2471 ,p_proficiency_level_id => comp.proficiency_level_id
2472 ,p_activity_version_id =>NULL-- p_activity_version_to Modified for Globalization
2473 ,p_effective_date_from => comp.effective_date_from
2474 ,p_effective_date_to => comp.effective_date_to
2475 ,p_comments => comp.comments
2476 ,p_attribute_category => comp.attribute_category
2477 ,p_attribute1 => comp.attribute1
2478 ,p_attribute2 => comp.attribute2
2479 ,p_attribute3 => comp.attribute3
2480 ,p_attribute4 => comp.attribute4
2481 ,p_attribute5 => comp.attribute5
2482 ,p_attribute6 => comp.attribute6
2483 ,p_attribute7 => comp.attribute7
2484 ,p_attribute8 => comp.attribute8
2485 ,p_attribute9 => comp.attribute9
2486 ,p_attribute10 => comp.attribute10
2487 ,p_attribute11 => comp.attribute11
2488 ,p_attribute12 => comp.attribute12
2489 ,p_attribute13 => comp.attribute13
2490 ,p_attribute14 => comp.attribute14
2491 ,p_attribute15 => comp.attribute15
2492 ,p_attribute16 => comp.attribute16
2493 ,p_attribute17 => comp.attribute17
2494 ,p_attribute18 => comp.attribute18
2495 ,p_attribute19 => comp.attribute19
2496 ,p_attribute20 => comp.attribute20
2497 ,p_effective_date => sysdate
2498 ,p_validate => FALSE
2499 ,p_object_id => p_activity_version_to /*Added for Globalization*/
2500 );
2501 end loop;
2502 ELSE
2503 for comp in c_get_competencies_other loop
2504 per_cel_ins.ins
2505 (p_competence_element_id => l_competence_element_id
2506 ,p_object_version_number => l_object_version_number
2507 ,p_type => 'DELIVERY'
2508 ,p_business_group_id => comp.business_group_id
2509 ,p_competence_id => comp.competence_id
2510 ,p_proficiency_level_id => comp.proficiency_level_id
2511 ,p_activity_version_id => p_activity_version_to -- Modified for Globalization
2512 ,p_effective_date_from => comp.effective_date_from
2513 ,p_effective_date_to => comp.effective_date_to
2514 ,p_comments => comp.comments
2515 ,p_attribute_category => comp.attribute_category
2516 ,p_attribute1 => comp.attribute1
2517 ,p_attribute2 => comp.attribute2
2518 ,p_attribute3 => comp.attribute3
2519 ,p_attribute4 => comp.attribute4
2520 ,p_attribute5 => comp.attribute5
2521 ,p_attribute6 => comp.attribute6
2522 ,p_attribute7 => comp.attribute7
2523 ,p_attribute8 => comp.attribute8
2524 ,p_attribute9 => comp.attribute9
2525 ,p_attribute10 => comp.attribute10
2526 ,p_attribute11 => comp.attribute11
2527 ,p_attribute12 => comp.attribute12
2528 ,p_attribute13 => comp.attribute13
2529 ,p_attribute14 => comp.attribute14
2530 ,p_attribute15 => comp.attribute15
2531 ,p_attribute16 => comp.attribute16
2532 ,p_attribute17 => comp.attribute17
2533 ,p_attribute18 => comp.attribute18
2534 ,p_attribute19 => comp.attribute19
2535 ,p_attribute20 => comp.attribute20
2536 ,p_effective_date => sysdate
2537 ,p_validate => FALSE
2538 );
2539 end loop;
2540 END IF;
2541
2542 -- Call after Process User Hook
2543 --
2544 begin
2545 --
2546 -- p_activity_version_from will be passed to p_activity_version_id since the
2547 -- function ota_tav_bus.return_legislation_code expects the parameter
2548 -- activity_version_id and it is the same as the activity_version_from.
2549 --
2550 hr_competence_element_bk6.copy_competencies_a (
2551 p_activity_version_from => p_activity_version_from,
2552 p_activity_version_id => p_activity_version_from,
2553 p_activity_version_to => p_activity_version_to ,
2554 p_competence_type => p_competence_type );
2555 exception
2556 when hr_api.cannot_find_prog_unit then
2557 hr_api.cannot_find_prog_unit_error
2558 (p_module_name => 'copy_competencies',
2559 p_hook_type => 'AP'
2560 );
2561 end;
2562 --
2563 -- End of After Process User hook
2564 --
2565 if p_validate then
2566 raise hr_api.validate_enabled;
2567 end if;
2568 --
2569 exception
2570 when hr_api.validate_enabled then
2571 --
2572 -- As the Validate_Enabled exception has been raised
2573 -- we must rollback to the savepoint
2574 --
2575 ROLLBACK TO copy_competencies;
2576 --
2577 --
2578 when others then
2579 --
2580 -- A validation or unexpected error has occurred
2581 --
2582 -- Added as part of fix to bug 632482
2583 --
2584 ROLLBACK TO copy_competencies;
2585 raise;
2586 --
2587 -- End of fix.
2588 --
2589 end copy_competencies;
2590 --
2591 --
2592 -- ---------------------------------------------------------------------------
2593 -- |------------------------< <update_delivered_dates> >---------------------|
2594 -- ---------------------------------------------------------------------------
2595 --
2596 procedure update_delivered_dates
2597 (p_activity_version_id in number,
2598 p_old_start_date in date,
2599 p_start_date in date,
2600 p_old_end_date in date,
2601 p_end_date in date,
2602 p_validate in boolean default FALSE) is
2603 --
2604 v_proc varchar2 (72) := g_package || 'update_delivered_dates';
2605 l_start_date date;
2606 l_end_date date;
2607 l_sd_changed varchar2 (20) := 'N';
2608 l_ed_changed varchar2 (20) := 'N';
2609
2610 l_start_date_changed boolean :=
2611 ota_general.value_changed(p_old_start_date,p_start_date);
2612 l_end_date_changed boolean :=
2613 ota_general.value_changed(p_old_end_date,p_end_date);
2614 --
2615 v_competence_element_id number;
2616 v_object_version_number number;
2617 v_start_date date;
2618 v_end_date date;
2619 v_competence_date_from date;
2620 v_competence_date_to date;
2621
2622 cursor get_competence_element is
2623 select e.competence_element_id
2624 ,e.object_version_number
2625 ,e.effective_date_from
2626 ,e.effective_date_to
2627 ,c.date_from
2628 ,c.date_to
2629 from per_competence_elements e
2630 , per_competences c
2631 where e.activity_version_id = p_activity_version_id
2632 and e.type = 'DELIVERY'
2633 and e.competence_id = c.competence_id
2634 and (((l_sd_changed = 'Y'
2635 and nvl(e.effective_date_from,hr_api.g_sot)
2636 = nvl(p_old_start_date,hr_api.g_sot))
2637 or ( l_sd_changed = 'N'
2638 and l_ed_changed = 'Y'
2639 and nvl(e.effective_date_to,hr_api.g_eot)
2640 = nvl(p_old_end_date,hr_api.g_eot)))
2641 OR ((l_ed_changed = 'Y'
2642 and nvl(e.effective_date_to,hr_api.g_eot)
2643 = nvl(p_old_end_date,hr_api.g_eot))
2644 OR (l_ed_changed = 'N'
2645 and l_sd_changed = 'Y'
2646 and nvl(e.effective_date_from,hr_api.g_sot)
2647 = nvl(p_old_start_date,hr_api.g_sot))));
2648
2649 Begin
2650 --
2651 hr_utility.set_location ('Entering:' || v_proc, 5);
2652 --
2653 -- Issue a savepoint.
2654 --
2655 savepoint update_delivered_dates;
2656 --
2657 -- Call Before Process User Hook
2658 --
2659 begin
2660 hr_competence_element_bk7.update_delivered_dates_b (
2661 p_activity_version_id => p_activity_version_id,
2662 p_old_start_date => p_old_start_date ,
2663 p_start_date => p_start_date ,
2664 p_old_end_date => p_old_end_date ,
2665 p_end_date => p_end_date
2666 );
2667 exception
2668 when hr_api.cannot_find_prog_unit then
2669 hr_api.cannot_find_prog_unit_error
2670 (p_module_name => 'update_delivered_dates',
2671 p_hook_type => 'BP'
2672 );
2673 end;
2674 --
2675 -- End of Before Process User hook
2676 --
2677 -- If niether the start/end date has changed then return
2678 --
2679 If not (l_start_date_changed) and not (l_end_date_changed) Then
2680 return;
2681 End if;
2682 --
2683 -- Set variable to indicate whether start/end date has changed
2684 --
2685 If l_start_date_changed Then
2686 l_sd_changed := 'Y';
2687 End if;
2688
2689 If l_end_date_changed Then
2690 l_ed_changed := 'Y';
2691 End if;
2692 --
2693 -- Fetch the elements that need to be updated
2694 --
2695
2696 Open get_competence_element;
2697 Fetch get_competence_element into v_competence_element_id
2698 ,v_object_version_number
2699 ,v_start_date
2700 ,v_end_date
2701 ,v_competence_date_from
2702 ,v_competence_date_to;
2703
2704 Loop
2705 --
2706 Exit When get_competence_element%notfound
2707 OR get_competence_element%notfound is null;
2708 --
2709 -- If both start and end date have changed then need to determine whether the
2710 -- cel start date matches the old activity start date and also if the the old
2711 -- end date matches the cel end date.
2712 --
2713 If l_start_date_changed and
2714 l_end_date_changed Then
2715 --
2716 -- If the old start date is the same as the cel start date then need to
2717 -- update to the new date
2718 --
2719 If not ota_general.value_changed(p_old_start_date,v_start_date) Then
2720 l_start_date := greatest(p_start_date
2721 ,nvl(v_competence_date_from,hr_api.g_sot));
2722 Else
2723 l_start_date := hr_api.g_date;
2724 End if;
2725 --
2726 -- If the old end date is the same as the cel end date then need to
2727 -- update to the new date
2728 --
2729 If not ota_general.value_changed(p_old_end_date,v_end_date) Then
2730 l_end_date := least(p_end_date
2731 ,nvl(v_competence_date_to,hr_api.g_eot));
2732 Else
2733 l_end_date := hr_api.g_date;
2734 End if;
2735 --
2736 Else
2737 --
2738 -- If the start has changed then update the resource usage with the new
2739 -- activity start date, otherwise use the default value so that the
2740 -- existing resource usage start date is used
2741 --
2742 If l_start_date_changed Then
2743 l_start_date := greatest(p_start_date
2744 ,nvl(v_competence_date_from,hr_api.g_sot));
2745 Else
2746 l_start_date := hr_api.g_date;
2747 End if;
2748 --
2749 --
2750 -- If the end date has changed then update the resource usage with the new
2751 -- activity end date otherwise, use the default value so that the existing
2752
2753 -- resource usage end date is used
2754 --
2755 If l_end_date_changed Then
2756 l_end_date := least(p_end_date
2757 ,nvl(v_competence_date_to,hr_api.g_eot));
2758 Else
2759 l_end_date := hr_api.g_date;
2760 End if;
2761 --
2762 End if;
2763 --
2764 -- Now, perform the update
2765 --
2766 per_cel_upd.upd(p_competence_element_id => v_competence_element_id
2767 ,p_object_version_number => v_object_version_number
2768 ,p_effective_date_from => l_start_date
2769 ,p_effective_date_to => l_end_date
2770 ,p_effective_date => sysdate
2771 );
2772 --
2773 Fetch get_competence_element into v_competence_element_id
2774 ,v_object_version_number
2775 ,v_start_date
2776 ,v_end_date
2777 ,v_competence_date_from
2778 ,v_competence_date_to;
2779 End loop;
2780 --
2781 Close get_competence_element;
2782 --
2783 -- Call After Process User Hook
2784 --
2785 begin
2786 hr_competence_element_bk7.update_delivered_dates_a (
2787 p_activity_version_id => p_activity_version_id,
2788 p_old_start_date => p_old_start_date ,
2789 p_start_date => p_start_date ,
2790 p_old_end_date => p_old_end_date ,
2791 p_end_date => p_end_date
2792 );
2793 exception
2794 when hr_api.cannot_find_prog_unit then
2795 hr_api.cannot_find_prog_unit_error
2796 (p_module_name => 'update_delivered_dates',
2797 p_hook_type => 'AP'
2798 );
2799 end;
2800 --
2801 -- End of after Process User hook
2802 --
2803 if p_validate then
2804 raise hr_api.validate_enabled;
2805 end if;
2806 --
2807 exception
2808 when hr_api.validate_enabled then
2809 --
2810 -- As the Validate_Enabled exception has been raised
2811 -- we must rollback to the savepoint
2812 --
2813 ROLLBACK TO update_delivered_dates;
2814 --
2815 when others then
2816 --
2817 -- A validation or unexpected error has occurred
2818 --
2819 -- Added as part of fix to bug 632482
2820 --
2821 ROLLBACK TO update_delivered_dates;
2822 raise;
2823 --
2824 -- End of fix.
2825 --
2826 --
2827 hr_utility.set_location (' Leaving:' || v_proc, 5);
2828 --
2829 End update_delivered_dates;
2830 --
2831 end hr_competence_element_api;