[Home] [Help]
PACKAGE BODY: APPS.HR_COMPETENCES_API
Source
1 Package Body hr_competences_api as
2 /* $Header: pecpnapi.pkb 120.0 2005/05/31 07:13:25 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_competences_api.';
7 --
8 -- ---------------------------------------------------------------------------
9 -- |-----------------------< <create_competence> >--------------------------|
10 -- ---------------------------------------------------------------------------
11 --
12 -- ngundura business group id made an optional parameter.
13 procedure create_competence
14 (p_validate in boolean default false,
15 p_effective_date in date,
16 p_language_code in varchar2 default hr_api.userenv_lang,
17 p_business_group_id in number default null,
18 p_description in varchar2 default null,
19 p_competence_alias in varchar2 default null,
20 p_date_from in date,
21 p_date_to in date default null,
22 p_behavioural_indicator in varchar2 default null,
23 p_certification_required in varchar2 default 'N',
24 p_evaluation_method in varchar2 default null,
25 p_renewal_period_frequency in number default null,
26 p_renewal_period_units in varchar2 default null,
27 p_rating_scale_id in number default null,
28 p_attribute_category in varchar2 default null,
29 p_attribute1 in varchar2 default null,
30 p_attribute2 in varchar2 default null,
31 p_attribute3 in varchar2 default null,
32 p_attribute4 in varchar2 default null,
33 p_attribute5 in varchar2 default null,
34 p_attribute6 in varchar2 default null,
35 p_attribute7 in varchar2 default null,
36 p_attribute8 in varchar2 default null,
37 p_attribute9 in varchar2 default null,
38 p_attribute10 in varchar2 default null,
39 p_attribute11 in varchar2 default null,
40 p_attribute12 in varchar2 default null,
41 p_attribute13 in varchar2 default null,
42 p_attribute14 in varchar2 default null,
43 p_attribute15 in varchar2 default null,
44 p_attribute16 in varchar2 default null,
45 p_attribute17 in varchar2 default null,
46 p_attribute18 in varchar2 default null,
47 p_attribute19 in varchar2 default null,
48 p_attribute20 in varchar2 default null
49 ,p_segment1 in varchar2 default null
50 ,p_segment2 in varchar2 default null
51 ,p_segment3 in varchar2 default null
52 ,p_segment4 in varchar2 default null
53 ,p_segment5 in varchar2 default null
54 ,p_segment6 in varchar2 default null
55 ,p_segment7 in varchar2 default null
56 ,p_segment8 in varchar2 default null
57 ,p_segment9 in varchar2 default null
58 ,p_segment10 in varchar2 default null
59 ,p_segment11 in varchar2 default null
60 ,p_segment12 in varchar2 default null
61 ,p_segment13 in varchar2 default null
62 ,p_segment14 in varchar2 default null
63 ,p_segment15 in varchar2 default null
64 ,p_segment16 in varchar2 default null
65 ,p_segment17 in varchar2 default null
66 ,p_segment18 in varchar2 default null
67 ,p_segment19 in varchar2 default null
68 ,p_segment20 in varchar2 default null
69 ,p_segment21 in varchar2 default null
70 ,p_segment22 in varchar2 default null
71 ,p_segment23 in varchar2 default null
72 ,p_segment24 in varchar2 default null
73 ,p_segment25 in varchar2 default null
74 ,p_segment26 in varchar2 default null
75 ,p_segment27 in varchar2 default null
76 ,p_segment28 in varchar2 default null
77 ,p_segment29 in varchar2 default null
78 ,p_segment30 in varchar2 default null
79 ,p_concat_segments in varchar2 default null
80 ,p_competence_id out nocopy number
81 --
82 -- bug 2267635 change p_competence_definition_id from an out to an in/out
83 -- parameter to enable value to be passed into program when known and reqd
84 --
85 ,p_competence_definition_id in out nocopy number
86 ,p_object_version_number out nocopy number
87 ,p_name out nocopy varchar2
88 ,p_competence_cluster in varchar2 default null
89 ,p_unit_standard_id in varchar2 default null
90 ,p_credit_type in varchar2 default null
91 ,p_credits in number default null
92 ,p_level_type in varchar2 default null
93 ,p_level_number in number default null
94 ,p_field in varchar2 default null
95 ,p_sub_field in varchar2 default null
96 ,p_provider in varchar2 default null
97 ,p_qa_organization in varchar2 default null
98 ,p_information_category in varchar2 default null
99 ,p_information1 in varchar2 default null
100 ,p_information2 in varchar2 default null
101 ,p_information3 in varchar2 default null
102 ,p_information4 in varchar2 default null
103 ,p_information5 in varchar2 default null
104 ,p_information6 in varchar2 default null
105 ,p_information7 in varchar2 default null
106 ,p_information8 in varchar2 default null
107 ,p_information9 in varchar2 default null
108 ,p_information10 in varchar2 default null
109 ,p_information11 in varchar2 default null
110 ,p_information12 in varchar2 default null
111 ,p_information13 in varchar2 default null
112 ,p_information14 in varchar2 default null
113 ,p_information15 in varchar2 default null
114 ,p_information16 in varchar2 default null
115 ,p_information17 in varchar2 default null
116 ,p_information18 in varchar2 default null
117 ,p_information19 in varchar2 default null
118 ,p_information20 in varchar2 default null
119 ) is
120 --
121 -- Declare cursors and local variables
122 --
123 l_proc varchar2(72) := g_package||'create_competence';
124 l_competence_id per_competences.competence_id%TYPE;
125 l_name varchar2(700) := p_concat_segments; -- bug#2925181
126 l_flex_num number;
127 l_competence_definition_id per_competences.competence_definition_id%TYPE
128 := p_competence_definition_id;
129 l_object_version_number per_competences.object_version_number%TYPE;
130 l_segment1 varchar2(150) := p_segment1;
131 l_segment2 varchar2(150) := p_segment2;
132 l_segment3 varchar2(150) := p_segment3;
133 l_segment4 varchar2(150) := p_segment4;
134 l_segment5 varchar2(150) := p_segment5;
135 l_segment6 varchar2(150) := p_segment6;
136 l_segment7 varchar2(150) := p_segment7;
137 l_segment8 varchar2(150) := p_segment8;
138 l_segment9 varchar2(150) := p_segment9;
139 l_segment10 varchar2(150) := p_segment10;
140 l_segment11 varchar2(150) := p_segment11;
141 l_segment12 varchar2(150) := p_segment12;
142 l_segment13 varchar2(150) := p_segment13;
143 l_segment14 varchar2(150) := p_segment14;
144 l_segment15 varchar2(150) := p_segment15;
145 l_segment16 varchar2(150) := p_segment16;
146 l_segment17 varchar2(150) := p_segment17;
147 l_segment18 varchar2(150) := p_segment18;
148 l_segment19 varchar2(150) := p_segment19;
149 l_segment20 varchar2(150) := p_segment20;
150 l_segment21 varchar2(150) := p_segment21;
151 l_segment22 varchar2(150) := p_segment22;
152 l_segment23 varchar2(150) := p_segment23;
153 l_segment24 varchar2(150) := p_segment24;
154 l_segment25 varchar2(150) := p_segment25;
155 l_segment26 varchar2(150) := p_segment26;
156 l_segment27 varchar2(150) := p_segment27;
157 l_segment28 varchar2(150) := p_segment28;
158 l_segment29 varchar2(150) := p_segment29;
159 l_segment30 varchar2(150) := p_segment30;
160 l_language_code varchar2(30);
161 --
162 -- bug 2267635 new variable to indicate whether key flex id parameter
163 -- enters the program with a value.
164 --
165 l_null_ind number(1) := 0;
166 --
167 cursor csr_comp_struct
168 is
169 select competence_structure
170 from per_business_groups
171 where business_group_id = p_business_group_id;
172 --
173 -- bug 2267635 get per_competence_definition segment values where
174 -- competence_definition_id is known
175 --
176 cursor c_segments is
177 select segment1,
178 segment2,
179 segment3,
180 segment4,
181 segment5,
182 segment6,
183 segment7,
184 segment8,
185 segment9,
186 segment10,
187 segment11,
188 segment12,
189 segment13,
190 segment14,
191 segment15,
192 segment16,
193 segment17,
194 segment18,
195 segment19,
196 segment20,
197 segment21,
198 segment22,
199 segment23,
200 segment24,
201 segment25,
202 segment26,
203 segment27,
204 segment28,
205 segment29,
206 segment30
207 from per_competence_definitions
208 where competence_definition_id = l_competence_definition_id;
209 --
210 -- bug 2267635 get per_competence name value for cases when the user is using
211 -- the api to insert the record directly and the comp def id is known
212 -- and the name should be saved in per_competences but
213 -- p_concat_segments has not been entered as a parameter
214 --
215 cursor c_name is
216 select name
217 from per_competences_vl
218 where competence_definition_id = l_competence_definition_id;
219 --
220 begin
221 --
222 hr_utility.set_location('Entering:'|| l_proc, 5);
223 hr_utility.set_location(l_competence_definition_id,67);
224 --
225 -- Issue a savepoint.
226 --
227 if p_business_group_id is not null then
228 hr_api.validate_bus_grp_id(p_business_group_id);
229 end if;
230 --
231 savepoint create_competence;
232
233 -- Validate the language parameter. l_language_code should be passed
234 -- instead of p_language_code from now on, to allow an IN OUT parameter to
235 -- be passed through.
236 --
237 l_language_code := p_language_code;
238 hr_api.validate_language_code(p_language_code => l_language_code);
239
240 hr_utility.set_location(l_proc, 6);
241 -- ngundura global competence allowed only if cross business group
242 -- profile is set
243 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') <> 'Y'
244 and p_business_group_id is null
245 then
246 fnd_message.set_name('PER','HR_52691_NO_GLOB_COMP');
247 fnd_message.raise_error;
248 end if;
249 --
250 if p_business_group_id is not null
251 then
252 open csr_comp_struct;
253 fetch csr_comp_struct into l_flex_num;
254 close csr_comp_struct;
255 hr_utility.set_location(l_proc, 7);
256 else
257 l_flex_num := fnd_profile.value('HR_GLOBAL_COMPETENCE');
258 end if;
259 --
260 if nvl(l_flex_num,hr_api.g_number) = hr_api.g_number
261 then
262 hr_utility.set_message(801,'HR_6035_ALL_INVALID_KEYFLEX');
263 hr_utility.raise_error;
264 end if;
265 --
266 if l_name is null and l_competence_definition_id is not null
267 then
268 hr_utility.set_location(l_proc, 13);
269 open c_name;
270 fetch c_name into
271 l_name;
272 if c_name%NOTFOUND OR c_name%NOTFOUND IS NULL
273 then
274 l_name := NULL;
275 hr_utility.set_location(l_proc, 14);
276 end if;
277 close c_name;
278 end if;
279 --
280 if l_competence_definition_id is not null
281 and l_name is not null
282 --
283 then
284 --
285 hr_utility.set_location(l_proc, 15);
286 --
287 -- set indicator to show p_competence_definition_id did not enter pgm null
288 --
289 l_null_ind := 1;
290 --
291 open c_segments;
292 fetch c_segments into
293 l_segment1,
294 l_segment2,
295 l_segment3,
296 l_segment4,
297 l_segment5,
298 l_segment6,
299 l_segment7,
300 l_segment8,
301 l_segment9,
302 l_segment10,
303 l_segment11,
304 l_segment12,
305 l_segment13,
306 l_segment14,
307 l_segment15,
308 l_segment16,
309 l_segment17,
310 l_segment18,
311 l_segment19,
312 l_segment20,
313 l_segment21,
314 l_segment22,
315 l_segment23,
316 l_segment24,
317 l_segment25,
318 l_segment26,
319 l_segment27,
320 l_segment28,
321 l_segment29,
322 l_segment30;
323 if c_segments%NOTFOUND OR c_segments%NOTFOUND IS NULL
324 then
325 l_competence_definition_id := NULL;
326 l_null_ind := 0;
327 hr_utility.set_location(l_proc, 17);
328 end if;
329 close c_segments;
330 end if;
331 --
332 -- Call Before Process User Hook
333 --
334 begin
335 hr_competences_bk1.create_competence_b (
336 p_effective_date => p_effective_date,
337 p_business_group_id => p_business_group_id,
338 p_competence_alias => p_competence_alias,
339 p_description => p_description,
340 p_date_from => trunc(p_date_from),
341 p_date_to => trunc(p_date_to),
342 p_behavioural_indicator => p_behavioural_indicator,
343 p_certification_required => p_certification_required,
344 p_evaluation_method => p_evaluation_method,
345 p_renewal_period_frequency => p_renewal_period_frequency,
346 p_renewal_period_units => p_renewal_period_units,
347 p_rating_scale_id => p_rating_scale_id,
348 p_attribute_category => p_attribute_category,
349 p_attribute1 => p_attribute1,
350 p_attribute2 => p_attribute2,
351 p_attribute3 => p_attribute3,
352 p_attribute4 => p_attribute4,
353 p_attribute5 => p_attribute5,
354 p_attribute6 => p_attribute6,
355 p_attribute7 => p_attribute7,
356 p_attribute8 => p_attribute8,
357 p_attribute9 => p_attribute9,
358 p_attribute10 => p_attribute10,
359 p_attribute11 => p_attribute11,
360 p_attribute12 => p_attribute12,
361 p_attribute13 => p_attribute13,
362 p_attribute14 => p_attribute14,
363 p_attribute15 => p_attribute15,
364 p_attribute16 => p_attribute16,
365 p_attribute17 => p_attribute17,
366 p_attribute18 => p_attribute18,
367 p_attribute19 => p_attribute19,
368 p_attribute20 => p_attribute20,
369 p_segment1 => l_segment1,
370 p_segment2 => l_segment2,
371 p_segment3 => l_segment3,
372 p_segment4 => l_segment4,
373 p_segment5 => l_segment5,
374 p_segment6 => l_segment6,
375 p_segment7 => l_segment7,
376 p_segment8 => l_segment8,
377 p_segment9 => l_segment9,
378 p_segment10 => l_segment10,
379 p_segment11 => l_segment11,
380 p_segment12 => l_segment12,
381 p_segment13 => l_segment13,
382 p_segment14 => l_segment14,
383 p_segment15 => l_segment15,
384 p_segment16 => l_segment16,
385 p_segment17 => l_segment17,
386 p_segment18 => l_segment18,
387 p_segment19 => l_segment19,
388 p_segment20 => l_segment20,
389 p_segment21 => l_segment21,
390 p_segment22 => l_segment22,
391 p_segment23 => l_segment23,
392 p_segment24 => l_segment24,
393 p_segment25 => l_segment25,
394 p_segment26 => l_segment26,
395 p_segment27 => l_segment27,
396 p_segment28 => l_segment28,
397 p_segment29 => l_segment29,
398 p_segment30 => l_segment30,
399 p_concat_segments => p_concat_segments,
400 p_language_code => l_language_code,
401 p_competence_cluster => p_competence_cluster,
402 p_unit_standard_id => p_unit_standard_id ,
403 p_credit_type => p_credit_type,
404 p_credits => p_credits,
405 p_level_type => p_level_type,
406 p_level_number => p_level_number,
407 p_field => p_field,
408 p_sub_field => p_sub_field,
409 p_provider => p_provider,
410 p_qa_organization => p_qa_organization,
411 p_information_category => p_information_category,
412 p_information1 => p_information1,
413 p_information2 => p_information2,
414 p_information3 => p_information3,
415 p_information4 => p_information4,
416 p_information5 => p_information5,
417 p_information6 => p_information6,
418 p_information7 => p_information7,
419 p_information8 => p_information8,
420 p_information9 => p_information9,
421 p_information10 => p_information10,
422 p_information11 => p_information11,
423 p_information12 => p_information12,
424 p_information13 => p_information13,
425 p_information14 => p_information14,
426 p_information15 => p_information15,
427 p_information16 => p_information16,
428 p_information17 => p_information17,
429 p_information18 => p_information18,
430 p_information19 => p_information19,
431 p_information20 => p_information20
432 );
433 hr_utility.set_location('hr_competences_bk1.create_competence_b',999);
434 exception
435 when hr_api.cannot_find_prog_unit then
436 hr_api.cannot_find_prog_unit_error
437 (p_module_name => 'create_competence',
438 p_hook_type => 'BP'
439 );
440 end;
441 --
442 -- End of Before Process User Hook call create_competence
443 --
444 -- Validation in addition to Table Handlers
445 --
446 if l_competence_definition_id is null
447 or l_name is null
448 then
449 l_null_ind := 0;
450 --
451 hr_utility.set_location(l_proc, 7);
452 --
453 -- Determine the competence defintion by calling ins_or_sel
454 --
455 hr_kflex_utility.ins_or_sel_keyflex_comb
456 (p_appl_short_name => 'PER'
457 ,p_flex_code => 'CMP'
458 ,p_flex_num => l_flex_num
459 ,p_segment1 => l_segment1
460 ,p_segment2 => l_segment2
461 ,p_segment3 => l_segment3
462 ,p_segment4 => l_segment4
463 ,p_segment5 => l_segment5
464 ,p_segment6 => l_segment6
465 ,p_segment7 => l_segment7
466 ,p_segment8 => l_segment8
467 ,p_segment9 => l_segment9
468 ,p_segment10 => l_segment10
469 ,p_segment11 => l_segment11
470 ,p_segment12 => l_segment12
471 ,p_segment13 => l_segment13
472 ,p_segment14 => l_segment14
473 ,p_segment15 => l_segment15
474 ,p_segment16 => l_segment16
475 ,p_segment17 => l_segment17
476 ,p_segment18 => l_segment18
477 ,p_segment19 => l_segment19
478 ,p_segment20 => l_segment20
479 ,p_segment21 => l_segment21
480 ,p_segment22 => l_segment22
481 ,p_segment23 => l_segment23
482 ,p_segment24 => l_segment24
483 ,p_segment25 => l_segment25
484 ,p_segment26 => l_segment26
485 ,p_segment27 => l_segment27
486 ,p_segment28 => l_segment28
487 ,p_segment29 => l_segment29
488 ,p_segment30 => l_segment30
489 ,p_concat_segments_in => p_concat_segments
490 ,p_ccid => l_competence_definition_id
491 ,p_concat_segments_out => l_name
492 );
493 end if;
494 --
495 --
496 -- Process Logic
497 --
498 per_cpn_ins.ins
499 (p_validate => p_validate,
500 p_effective_date => p_effective_date,
501 p_name => l_name,
502 p_business_group_id => p_business_group_id,
503 p_description => p_description,
504 p_date_from => trunc(p_date_from),
505 p_date_to => trunc(p_date_to),
506 p_behavioural_indicator => p_behavioural_indicator,
507 p_certification_required => p_certification_required,
508 p_evaluation_method => p_evaluation_method,
509 p_renewal_period_frequency => p_renewal_period_frequency,
510 p_renewal_period_units => p_renewal_period_units,
511 p_rating_scale_id => p_rating_scale_id,
512 p_attribute_category => p_attribute_category,
513 p_attribute1 => p_attribute1,
514 p_attribute2 => p_attribute2,
515 p_attribute3 => p_attribute3,
516 p_attribute4 => p_attribute4,
517 p_attribute5 => p_attribute5,
518 p_attribute6 => p_attribute6,
519 p_attribute7 => p_attribute7,
520 p_attribute8 => p_attribute8,
521 p_attribute9 => p_attribute9,
522 p_attribute10 => p_attribute10,
523 p_attribute11 => p_attribute11,
524 p_attribute12 => p_attribute12,
525 p_attribute13 => p_attribute13,
526 p_attribute14 => p_attribute14,
527 p_attribute15 => p_attribute15,
528 p_attribute16 => p_attribute16,
529 p_attribute17 => p_attribute17,
530 p_attribute18 => p_attribute18,
531 p_attribute19 => p_attribute19,
532 p_attribute20 => p_attribute20,
533 p_competence_definition_id => l_competence_definition_id,
534 p_competence_id => l_competence_id,
535 p_object_version_number => l_object_version_number,
536 p_competence_alias => p_competence_alias,
537 p_competence_cluster => p_competence_cluster,
538 p_unit_standard_id => p_unit_standard_id ,
539 p_credit_type => p_credit_type,
540 p_credits => p_credits,
541 p_level_type => p_level_type,
542 p_level_number => p_level_number,
543 p_field => p_field,
544 p_sub_field => p_sub_field,
545 p_provider => p_provider,
546 p_qa_organization => p_qa_organization,
547 p_information_category => p_information_category,
548 p_information1 => p_information1,
549 p_information2 => p_information2,
550 p_information3 => p_information3,
551 p_information4 => p_information4,
552 p_information5 => p_information5,
553 p_information6 => p_information6,
554 p_information7 => p_information7,
555 p_information8 => p_information8,
556 p_information9 => p_information9,
557 p_information10 => p_information10,
558 p_information11 => p_information11,
559 p_information12 => p_information12,
560 p_information13 => p_information13,
561 p_information14 => p_information14,
562 p_information15 => p_information15,
563 p_information16 => p_information16,
564 p_information17 => p_information17,
565 p_information18 => p_information18,
566 p_information19 => p_information19,
567 p_information20 => p_information20
568 );
569 --
570 hr_utility.set_location(l_proc, 8);
571
572 --
573 -- MLS Processing
574 --
575 --
576 per_cpl_ins.ins_tl
577 (p_effective_date => p_effective_date
578 ,p_language_code => l_language_code
579 ,p_competence_id => l_competence_id
580 ,p_name => l_name
581 ,p_competence_alias => p_competence_alias
582 ,p_behavioural_indicator => p_behavioural_indicator
583 ,p_description => p_description
584 );
585 -- Call After Process User Hook
586 --
587 begin
588 hr_competences_bk1.create_competence_a (
589 p_competence_id => l_competence_id,
590 p_object_version_number => l_object_version_number,
591 p_effective_date => p_effective_date,
592 p_name => l_name,
593 p_business_group_id => p_business_group_id,
594 p_competence_alias => p_competence_alias,
595 p_description => p_description,
596 p_date_from => trunc(p_date_from),
597 p_date_to => trunc(p_date_to),
598 p_behavioural_indicator => p_behavioural_indicator,
599 p_certification_required => p_certification_required,
600 p_evaluation_method => p_evaluation_method,
601 p_renewal_period_frequency => p_renewal_period_frequency,
602 p_renewal_period_units => p_renewal_period_units,
603 p_rating_scale_id => p_rating_scale_id,
604 p_attribute_category => p_attribute_category,
605 p_attribute1 => p_attribute1,
606 p_attribute2 => p_attribute2,
607 p_attribute3 => p_attribute3,
608 p_attribute4 => p_attribute4,
609 p_attribute5 => p_attribute5,
610 p_attribute6 => p_attribute6,
611 p_attribute7 => p_attribute7,
612 p_attribute8 => p_attribute8,
613 p_attribute9 => p_attribute9,
614 p_attribute10 => p_attribute10,
615 p_attribute11 => p_attribute11,
616 p_attribute12 => p_attribute12,
617 p_attribute13 => p_attribute13,
618 p_attribute14 => p_attribute14,
619 p_attribute15 => p_attribute15,
620 p_attribute16 => p_attribute16,
621 p_attribute17 => p_attribute17,
622 p_attribute18 => p_attribute18,
623 p_attribute19 => p_attribute19,
624 p_attribute20 => p_attribute20
625 ,p_segment1 => l_segment1
626 ,p_segment2 => l_segment2
627 ,p_segment3 => l_segment3
628 ,p_segment4 => l_segment4
629 ,p_segment5 => l_segment5
630 ,p_segment6 => l_segment6
631 ,p_segment7 => l_segment7
632 ,p_segment8 => l_segment8
633 ,p_segment9 => l_segment9
634 ,p_segment10 => l_segment10
635 ,p_segment11 => l_segment11
636 ,p_segment12 => l_segment12
637 ,p_segment13 => l_segment13
638 ,p_segment14 => l_segment14
639 ,p_segment15 => l_segment15
640 ,p_segment16 => l_segment16
641 ,p_segment17 => l_segment17
642 ,p_segment18 => l_segment18
643 ,p_segment19 => l_segment19
644 ,p_segment20 => l_segment20
645 ,p_segment21 => l_segment21
646 ,p_segment22 => l_segment22
647 ,p_segment23 => l_segment23
648 ,p_segment24 => l_segment24
649 ,p_segment25 => l_segment25
650 ,p_segment26 => l_segment26
651 ,p_segment27 => l_segment27
652 ,p_segment28 => l_segment28
653 ,p_segment29 => l_segment29
654 ,p_segment30 => l_segment30
655 ,p_concat_segments => p_concat_segments
656 ,p_competence_definition_id => l_competence_definition_id
657 ,p_language_code => l_language_code
658 ,p_competence_cluster => p_competence_cluster
659 ,p_unit_standard_id => p_unit_standard_id
660 ,p_credit_type => p_credit_type
661 ,p_credits => p_credits
662 ,p_level_type => p_level_type
663 ,p_level_number => p_level_number
664 ,p_field => p_field
665 ,p_sub_field => p_sub_field
666 ,p_provider => p_provider
667 ,p_qa_organization => p_qa_organization
668 ,p_information_category => p_information_category
669 ,p_information1 => p_information1
670 ,p_information2 => p_information2
671 ,p_information3 => p_information3
672 ,p_information4 => p_information4
673 ,p_information5 => p_information5
674 ,p_information6 => p_information6
675 ,p_information7 => p_information7
676 ,p_information8 => p_information8
677 ,p_information9 => p_information9
678 ,p_information10 => p_information10
679 ,p_information11 => p_information11
680 ,p_information12 => p_information12
681 ,p_information13 => p_information13
682 ,p_information14 => p_information14
683 ,p_information15 => p_information15
684 ,p_information16 => p_information16
685 ,p_information17 => p_information17
686 ,p_information18 => p_information18
687 ,p_information19 => p_information19
688 ,p_information20 => p_information20
689 );
690 exception
691 when hr_api.cannot_find_prog_unit then
692 hr_api.cannot_find_prog_unit_error
693 (p_module_name => 'create_competence',
694 p_hook_type => 'AP'
695 );
696 end;
697 --
698 -- End of After process user hook create_competence
699 --
700 -- When in validation only mode raise the Validate_Enabled exception
701 --
702 if p_validate then
703 raise hr_api.validate_enabled;
704 end if;
705 --
706 -- Set all output arguments
707 --
708 p_competence_id := l_competence_id;
709 p_object_version_number := l_object_version_number;
710 p_competence_definition_id := l_competence_definition_id;
711 p_name := l_name;
712 --
713 hr_utility.set_location(' Leaving:'||l_proc, 11);
714 exception
715 when hr_api.validate_enabled then
716 --
717 -- As the Validate_Enabled exception has been raised
718 -- we must rollback to the savepoint
719 --
720 ROLLBACK TO create_competence;
721 --
722 -- Only set output warning arguments
723 -- (Any key or derived arguments must be set to null
724 -- when validation only mode is being used.)
725 --
726 p_competence_id := null;
727 p_object_version_number := null;
728 p_name := null;
729 if l_null_ind = 0
730 then
731 p_competence_definition_id := null;
732 end if;
733 --
734 when others then
735 --
736 -- A validation or unexpected error has occurred
737 --
738 -- Added as part of fix to bug 632482
739 --
740 ROLLBACK TO create_competence;
741 --
742 -- set in out parameters and set out parameters
743 --
744 p_competence_definition_id := l_competence_definition_id;
745 p_competence_id := null;
746 p_object_version_number := null;
747 p_name := null;
748 --
749 raise;
750 --
751 -- End of fix.
752 --
753 hr_utility.set_location(' Leaving:'||l_proc, 12);
754 end create_competence;
755 --
756 --
757 -- ---------------------------------------------------------------------------
758 -- |-----------------------< <update_competence> >--------------------------|
759 -- ---------------------------------------------------------------------------
760 --
761 procedure update_competence
762 (p_validate in boolean default false,
763 p_effective_date in date,
764 p_competence_id in number,
765 p_object_version_number in out nocopy number,
766 p_language_code in varchar2 default hr_api.userenv_lang,
767 p_name out nocopy varchar2 ,
768 p_description in varchar2 default hr_api.g_varchar2,
769 p_date_from in date default hr_api.g_date,
770 p_date_to in date default hr_api.g_date,
771 p_behavioural_indicator in varchar2 default hr_api.g_varchar2,
772 p_certification_required in varchar2 default hr_api.g_varchar2,
773 p_evaluation_method in varchar2 default hr_api.g_varchar2,
774 p_renewal_period_frequency in number default hr_api.g_number,
775 p_renewal_period_units in varchar2 default hr_api.g_varchar2,
776 p_rating_scale_id in number default hr_api.g_number,
777 p_attribute_category in varchar2 default hr_api.g_varchar2,
778 p_attribute1 in varchar2 default hr_api.g_varchar2,
779 p_attribute2 in varchar2 default hr_api.g_varchar2,
780 p_attribute3 in varchar2 default hr_api.g_varchar2,
781 p_attribute4 in varchar2 default hr_api.g_varchar2,
782 p_attribute5 in varchar2 default hr_api.g_varchar2,
783 p_attribute6 in varchar2 default hr_api.g_varchar2,
784 p_attribute7 in varchar2 default hr_api.g_varchar2,
785 p_attribute8 in varchar2 default hr_api.g_varchar2,
786 p_attribute9 in varchar2 default hr_api.g_varchar2,
787 p_attribute10 in varchar2 default hr_api.g_varchar2,
788 p_attribute11 in varchar2 default hr_api.g_varchar2,
789 p_attribute12 in varchar2 default hr_api.g_varchar2,
790 p_attribute13 in varchar2 default hr_api.g_varchar2,
791 p_attribute14 in varchar2 default hr_api.g_varchar2,
792 p_attribute15 in varchar2 default hr_api.g_varchar2,
793 p_attribute16 in varchar2 default hr_api.g_varchar2,
794 p_attribute17 in varchar2 default hr_api.g_varchar2,
795 p_attribute18 in varchar2 default hr_api.g_varchar2,
796 p_attribute19 in varchar2 default hr_api.g_varchar2,
797 p_attribute20 in varchar2 default hr_api.g_varchar2,
798 p_competence_alias in varchar2 default hr_api.g_varchar2,
799 p_segment1 in varchar2 default hr_api.g_varchar2,
800 p_segment2 in varchar2 default hr_api.g_varchar2,
801 p_segment3 in varchar2 default hr_api.g_varchar2,
802 p_segment4 in varchar2 default hr_api.g_varchar2,
803 p_segment5 in varchar2 default hr_api.g_varchar2,
804 p_segment6 in varchar2 default hr_api.g_varchar2,
805 p_segment7 in varchar2 default hr_api.g_varchar2,
806 p_segment8 in varchar2 default hr_api.g_varchar2,
807 p_segment9 in varchar2 default hr_api.g_varchar2,
808 p_segment10 in varchar2 default hr_api.g_varchar2,
809 p_segment11 in varchar2 default hr_api.g_varchar2,
810 p_segment12 in varchar2 default hr_api.g_varchar2,
811 p_segment13 in varchar2 default hr_api.g_varchar2,
812 p_segment14 in varchar2 default hr_api.g_varchar2,
813 p_segment15 in varchar2 default hr_api.g_varchar2,
814 p_segment16 in varchar2 default hr_api.g_varchar2,
815 p_segment17 in varchar2 default hr_api.g_varchar2,
816 p_segment18 in varchar2 default hr_api.g_varchar2,
817 p_segment19 in varchar2 default hr_api.g_varchar2,
818 p_segment20 in varchar2 default hr_api.g_varchar2,
819 p_segment21 in varchar2 default hr_api.g_varchar2,
820 p_segment22 in varchar2 default hr_api.g_varchar2,
821 p_segment23 in varchar2 default hr_api.g_varchar2,
822 p_segment24 in varchar2 default hr_api.g_varchar2,
823 p_segment25 in varchar2 default hr_api.g_varchar2,
824 p_segment26 in varchar2 default hr_api.g_varchar2,
825 p_segment27 in varchar2 default hr_api.g_varchar2,
826 p_segment28 in varchar2 default hr_api.g_varchar2,
827 p_segment29 in varchar2 default hr_api.g_varchar2,
828 p_segment30 in varchar2 default hr_api.g_varchar2,
829 p_concat_segments in varchar2 default hr_api.g_varchar2,
830 --
831 -- bug 2267635 change p_competence_definition_id from an out to an in/out
832 -- parameter to enable value to be passed into program when known and required
833 --
834 p_competence_definition_id in out nocopy number
835 --
836 -- BUG3356369 Added unit standard qualification framework
837 --
838 ,p_competence_cluster in varchar2 default hr_api.g_varchar2
839 ,p_unit_standard_id in varchar2 default hr_api.g_varchar2
840 ,p_credit_type in varchar2 default hr_api.g_varchar2
841 ,p_credits in number default hr_api.g_number
842 ,p_level_type in varchar2 default hr_api.g_varchar2
843 ,p_level_number in number default hr_api.g_number
844 ,p_field in varchar2 default hr_api.g_varchar2
845 ,p_sub_field in varchar2 default hr_api.g_varchar2
846 ,p_provider in varchar2 default hr_api.g_varchar2
847 ,p_qa_organization in varchar2 default hr_api.g_varchar2
848 ,p_information_category in varchar2 default hr_api.g_varchar2
849 ,p_information1 in varchar2 default hr_api.g_varchar2
850 ,p_information2 in varchar2 default hr_api.g_varchar2
851 ,p_information3 in varchar2 default hr_api.g_varchar2
852 ,p_information4 in varchar2 default hr_api.g_varchar2
853 ,p_information5 in varchar2 default hr_api.g_varchar2
854 ,p_information6 in varchar2 default hr_api.g_varchar2
855 ,p_information7 in varchar2 default hr_api.g_varchar2
856 ,p_information8 in varchar2 default hr_api.g_varchar2
857 ,p_information9 in varchar2 default hr_api.g_varchar2
858 ,p_information10 in varchar2 default hr_api.g_varchar2
859 ,p_information11 in varchar2 default hr_api.g_varchar2
860 ,p_information12 in varchar2 default hr_api.g_varchar2
861 ,p_information13 in varchar2 default hr_api.g_varchar2
862 ,p_information14 in varchar2 default hr_api.g_varchar2
863 ,p_information15 in varchar2 default hr_api.g_varchar2
864 ,p_information16 in varchar2 default hr_api.g_varchar2
865 ,p_information17 in varchar2 default hr_api.g_varchar2
866 ,p_information18 in varchar2 default hr_api.g_varchar2
867 ,p_information19 in varchar2 default hr_api.g_varchar2
868 ,p_information20 in varchar2 default hr_api.g_varchar2
869 ) is
870 --
871 -- Declare cursors and local variables
872 --
873 -- bug 2267635 initialize l_competence_definition_id and segmnt variables with
874 -- values where these are passed into program.
875 --
876 l_proc varchar2(72) := g_package||'update_competence';
877 l_object_version_number per_competences.object_version_number%TYPE;
878 l_ovn per_competences.object_version_number%TYPE := p_object_version_number;
879 l_competence_definition_id per_competence_definitions.competence_definition_id%TYPE := p_competence_definition_id;
880 l_comp_def_id per_competence_definitions.competence_definition_id%TYPE := p_competence_definition_id;
881 l_flex_num per_competence_definitions.id_flex_num%TYPE;
882 l_name varchar2(700); -- bug#2925181
883 l_api_updating boolean;
884 l_segment1 varchar2(150) := p_segment1;
885 l_segment2 varchar2(150) := p_segment2;
886 l_segment3 varchar2(150) := p_segment3;
887 l_segment4 varchar2(150) := p_segment4;
888 l_segment5 varchar2(150) := p_segment5;
889 l_segment6 varchar2(150) := p_segment6;
890 l_segment7 varchar2(150) := p_segment7;
891 l_segment8 varchar2(150) := p_segment8;
892 l_segment9 varchar2(150) := p_segment9;
893 l_segment10 varchar2(150) := p_segment10;
894 l_segment11 varchar2(150) := p_segment11;
895 l_segment12 varchar2(150) := p_segment12;
896 l_segment13 varchar2(150) := p_segment13;
897 l_segment14 varchar2(150) := p_segment14;
898 l_segment15 varchar2(150) := p_segment15;
899 l_segment16 varchar2(150) := p_segment16;
900 l_segment17 varchar2(150) := p_segment17;
901 l_segment18 varchar2(150) := p_segment18;
902 l_segment19 varchar2(150) := p_segment19;
903 l_segment20 varchar2(150) := p_segment20;
904 l_segment21 varchar2(150) := p_segment21;
905 l_segment22 varchar2(150) := p_segment22;
906 l_segment23 varchar2(150) := p_segment23;
907 l_segment24 varchar2(150) := p_segment24;
908 l_segment25 varchar2(150) := p_segment25;
909 l_segment26 varchar2(150) := p_segment26;
910 l_segment27 varchar2(150) := p_segment27;
911 l_segment28 varchar2(150) := p_segment28;
912 l_segment29 varchar2(150) := p_segment29;
913 l_segment30 varchar2(150) := p_segment30;
914 l_null_ind number(1) := 0;
915 l_language_code varchar2(30);
916 --
917 cursor csr_idsel is
918 select cmp.id_flex_num
919 from per_competence_definitions cmp
920 where cmp.competence_definition_id = l_competence_definition_id;
921 --
922 --
923 -- bug 2267635 get per_competence_definition segment values where
924 -- competence_definition_id is known
925 --
926 cursor c_segments is
927 select segment1,
928 segment2,
929 segment3,
930 segment4,
931 segment5,
932 segment6,
933 segment7,
934 segment8,
935 segment9,
936 segment10,
937 segment11,
938 segment12,
939 segment13,
940 segment14,
941 segment15,
942 segment16,
943 segment17,
944 segment18,
945 segment19,
946 segment20,
947 segment21,
948 segment22,
949 segment23,
950 segment24,
951 segment25,
952 segment26,
953 segment27,
954 segment28,
955 segment29,
956 segment30
957 from per_competence_definitions
958 where competence_definition_id = l_competence_definition_id;
959 --
960 -- bug 2267635 get per_competence name value for when updating by api
961 -- and comp def id is known, name should be in per_competences and
962 -- p_concat_segments has not entered as a param
963 --
964 cursor c_name is
965 select name
966 from per_competences_vl
967 where competence_definition_id = l_competence_definition_id;
968 --
969 begin
970 --
971 hr_utility.set_location('Entering:'|| l_proc, 5);
972 --
973 -- Issue a savepoint.
974 --
975 savepoint update_competence;
976 hr_utility.set_location(l_proc, 6);
977 hr_utility.set_location(l_competence_definition_id,69);
978 hr_utility.set_location(substr(l_name,1,128),691); --bug#2925181
979 --
980 if l_name is null and l_competence_definition_id is not null
981 then
982 hr_utility.set_location(l_proc, 13);
983 open c_name;
984 fetch c_name into
985 l_name;
986 if c_name%NOTFOUND OR c_name%NOTFOUND IS NULL
987 then
988 l_name := NULL;
989 hr_utility.set_location(l_proc, 14);
990 end if;
991 close c_name;
992 end if;
993 --
994 if l_competence_definition_id is not null
995 and l_name is not null
996 then
997 -- 2267635
998 -- get segment values if p_competence_definition_id entered with a value
999 -- set indicator to show p_competence_definition_id did not enter pgm null
1000 --
1001 l_null_ind := 1;
1002 --
1003 open c_segments;
1004 fetch c_segments into
1005 l_segment1,
1006 l_segment2,
1007 l_segment3,
1008 l_segment4,
1009 l_segment5,
1010 l_segment6,
1011 l_segment7,
1012 l_segment8,
1013 l_segment9,
1014 l_segment10,
1015 l_segment11,
1016 l_segment12,
1017 l_segment13,
1018 l_segment14,
1019 l_segment15,
1020 l_segment16,
1021 l_segment17,
1022 l_segment18,
1023 l_segment19,
1024 l_segment20,
1025 l_segment21,
1026 l_segment22,
1027 l_segment23,
1028 l_segment24,
1029 l_segment25,
1030 l_segment26,
1031 l_segment27,
1032 l_segment28,
1033 l_segment29,
1034 l_segment30;
1035 if c_segments%NOTFOUND OR c_segments%NOTFOUND IS NULL
1036 then
1037 l_competence_definition_id := NULL;
1038 l_null_ind := 0;
1039 hr_utility.set_location(l_proc, 24);
1040 end if;
1041 close c_segments;
1042 hr_utility.set_location(l_proc, 27);
1043 --
1044 end if;
1045 --
1046
1047 l_language_code := p_language_code;
1048 hr_api.validate_language_code(p_language_code => l_language_code);
1049
1050 -- Call Before Process User Hook
1051 --
1052 begin
1053 hr_competences_bk2.update_competence_b (
1054 p_competence_id => p_competence_id,
1055 p_object_version_number => p_object_version_number,
1056 p_effective_date => p_effective_date,
1057 p_description => p_description,
1058 p_date_from => trunc(p_date_from),
1059 p_date_to => trunc(p_date_to),
1060 p_behavioural_indicator => p_behavioural_indicator,
1061 p_certification_required => p_certification_required,
1062 p_evaluation_method => p_evaluation_method,
1063 p_renewal_period_frequency => p_renewal_period_frequency,
1064 p_renewal_period_units => p_renewal_period_units,
1065 p_rating_scale_id => p_rating_scale_id,
1066 p_attribute_category => p_attribute_category,
1067 p_attribute1 => p_attribute1,
1068 p_attribute2 => p_attribute2,
1069 p_attribute3 => p_attribute3,
1070 p_attribute4 => p_attribute4,
1071 p_attribute5 => p_attribute5,
1072 p_attribute6 => p_attribute6,
1073 p_attribute7 => p_attribute7,
1074 p_attribute8 => p_attribute8,
1075 p_attribute9 => p_attribute9,
1076 p_attribute10 => p_attribute10,
1077 p_attribute11 => p_attribute11,
1078 p_attribute12 => p_attribute12,
1079 p_attribute13 => p_attribute13,
1080 p_attribute14 => p_attribute14,
1081 p_attribute15 => p_attribute15,
1082 p_attribute16 => p_attribute16,
1083 p_attribute17 => p_attribute17,
1084 p_attribute18 => p_attribute18,
1085 p_attribute19 => p_attribute19,
1086 p_attribute20 => p_attribute20,
1087 p_competence_alias => p_competence_alias,
1088 p_segment1 => l_segment1
1089 ,p_segment2 => l_segment2
1090 ,p_segment3 => l_segment3
1091 ,p_segment4 => l_segment4
1092 ,p_segment5 => l_segment5
1093 ,p_segment6 => l_segment6
1094 ,p_segment7 => l_segment7
1095 ,p_segment8 => l_segment8
1096 ,p_segment9 => l_segment9
1097 ,p_segment10 => l_segment10
1098 ,p_segment11 => l_segment11
1099 ,p_segment12 => l_segment12
1100 ,p_segment13 => l_segment13
1101 ,p_segment14 => l_segment14
1102 ,p_segment15 => l_segment15
1103 ,p_segment16 => l_segment16
1104 ,p_segment17 => l_segment17
1105 ,p_segment18 => l_segment18
1106 ,p_segment19 => l_segment19
1107 ,p_segment20 => l_segment20
1108 ,p_segment21 => l_segment21
1109 ,p_segment22 => l_segment22
1110 ,p_segment23 => l_segment23
1111 ,p_segment24 => l_segment24
1112 ,p_segment25 => l_segment25
1113 ,p_segment26 => l_segment26
1114 ,p_segment27 => l_segment27
1115 ,p_segment28 => l_segment28
1116 ,p_segment29 => l_segment29
1117 ,p_segment30 => l_segment30
1118 ,p_concat_segments => p_concat_segments
1119 ,p_language_code => l_language_code
1120 ,p_competence_cluster => p_competence_cluster
1121 ,p_unit_standard_id => p_unit_standard_id
1122 ,p_credit_type => p_credit_type
1123 ,p_credits => p_credits
1124 ,p_level_type => p_level_type
1125 ,p_level_number => p_level_number
1126 ,p_field => p_field
1127 ,p_sub_field => p_sub_field
1128 ,p_provider => p_provider
1129 ,p_qa_organization => p_qa_organization
1130 ,p_information_category => p_information_category
1131 ,p_information1 => p_information1
1132 ,p_information2 => p_information2
1133 ,p_information3 => p_information3
1134 ,p_information4 => p_information4
1135 ,p_information5 => p_information5
1136 ,p_information6 => p_information6
1137 ,p_information7 => p_information7
1138 ,p_information8 => p_information8
1139 ,p_information9 => p_information9
1140 ,p_information10 => p_information10
1141 ,p_information11 => p_information11
1142 ,p_information12 => p_information12
1143 ,p_information13 => p_information13
1144 ,p_information14 => p_information14
1145 ,p_information15 => p_information15
1146 ,p_information16 => p_information16
1147 ,p_information17 => p_information17
1148 ,p_information18 => p_information18
1149 ,p_information19 => p_information19
1150 ,p_information20 => p_information20
1151 );
1152 exception
1153 when hr_api.cannot_find_prog_unit then
1154 hr_api.cannot_find_prog_unit_error
1155 (p_module_name => 'update_competence',
1156 p_hook_type => 'BP'
1157 );
1158 end;
1159 --
1160 -- End of Before Process User Hook call
1161 --
1162 -- Validation in addition to Table Handlers
1163 --
1164 --
1165 -- Retrieve current competence details
1166 --
1167 l_api_updating := per_cpn_shd.api_updating
1168 (p_competence_id => p_competence_id
1169 ,p_object_version_number => p_object_version_number);
1170 --
1171 hr_utility.set_location(p_concat_segments, 692);
1172 hr_utility.set_location(substr(l_name,1,128), 692); --bug#2925181
1173 hr_utility.set_location(l_proc, 7);
1174 --
1175 if not l_api_updating
1176 then
1177 hr_utility.set_location(l_proc, 8);
1178 --
1179 -- As this an updating API, the competence should already exist.
1180 --
1181 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1182 hr_utility.raise_error;
1183 else
1184 --
1185 hr_utility.set_location(l_proc, 9);
1186 if l_competence_definition_id is null
1187 then
1188 l_null_ind := 0;
1189 l_competence_definition_id
1190 := per_cpn_shd.g_old_rec.competence_definition_id;
1191 end if;
1192 end if;
1193 --
1194 open csr_idsel;
1195 fetch csr_idsel
1196 into l_flex_num;
1197 if csr_idsel%NOTFOUND then
1198 close csr_idsel;
1199 fnd_message.set_name('PER','HR_52733_INVALID_CODE_COMB');
1200 fnd_message.raise_error;
1201 end if;
1202 close csr_idsel;
1203 --
1204 -- 2267635
1205 -- update competence definitions in per_competence_definitions if
1206 -- p_competence_definition_id had no value when passed into program
1207 -- no value when passed into program or if l_name has no value.
1208 --
1209 --
1210 hr_utility.set_location(l_null_ind, 693);
1211 if l_null_ind = 0
1212 or l_name is null
1213 then
1214 --
1215 hr_kflex_utility.upd_or_sel_keyflex_comb
1216 (p_appl_short_name => 'PER'
1217 ,p_flex_code => 'CMP'
1218 ,p_flex_num => l_flex_num
1219 ,p_segment1 => l_segment1
1220 ,p_segment2 => l_segment2
1221 ,p_segment3 => l_segment3
1222 ,p_segment4 => l_segment4
1223 ,p_segment5 => l_segment5
1224 ,p_segment6 => l_segment6
1225 ,p_segment7 => l_segment7
1226 ,p_segment8 => l_segment8
1227 ,p_segment9 => l_segment9
1228 ,p_segment10 => l_segment10
1229 ,p_segment11 => l_segment11
1230 ,p_segment12 => l_segment12
1231 ,p_segment13 => l_segment13
1232 ,p_segment14 => l_segment14
1233 ,p_segment15 => l_segment15
1234 ,p_segment16 => l_segment16
1235 ,p_segment17 => l_segment17
1236 ,p_segment18 => l_segment18
1237 ,p_segment19 => l_segment19
1238 ,p_segment20 => l_segment20
1239 ,p_segment21 => l_segment21
1240 ,p_segment22 => l_segment22
1241 ,p_segment23 => l_segment23
1242 ,p_segment24 => l_segment24
1243 ,p_segment25 => l_segment25
1244 ,p_segment26 => l_segment26
1245 ,p_segment27 => l_segment27
1246 ,p_segment28 => l_segment28
1247 ,p_segment29 => l_segment29
1248 ,p_segment30 => l_segment30
1249 ,p_concat_segments_in => p_concat_segments
1250 ,p_ccid => l_competence_definition_id
1251 ,p_concat_segments_out => l_name
1252 );
1253 --
1254 hr_utility.set_location(l_proc, 10);
1255 end if;
1256 hr_utility.set_location(p_concat_segments, 6931);
1257 hr_utility.set_location(substr(l_name,1,128), 6932); --bug#2925181
1258 --
1259 -- Process Logic
1260 --
1261 l_object_version_number := p_object_version_number;
1262 --
1263 per_cpn_upd.upd
1264 (p_validate => p_validate,
1265 p_effective_date => p_effective_date,
1266 p_competence_id => p_competence_id,
1267 p_object_version_number => l_object_version_number,
1268 p_name => l_name,
1269 p_description => p_description,
1270 p_date_from => trunc(p_date_from),
1271 p_date_to => trunc(p_date_to),
1272 p_behavioural_indicator => p_behavioural_indicator,
1273 p_certification_required => p_certification_required,
1274 p_evaluation_method => p_evaluation_method,
1275 p_renewal_period_frequency => p_renewal_period_frequency,
1276 p_renewal_period_units => p_renewal_period_units,
1277 p_rating_scale_id => p_rating_scale_id,
1278 p_attribute_category => p_attribute_category,
1279 p_attribute1 => p_attribute1,
1280 p_attribute2 => p_attribute2,
1281 p_attribute3 => p_attribute3,
1282 p_attribute4 => p_attribute4,
1283 p_attribute5 => p_attribute5,
1284 p_attribute6 => p_attribute6,
1285 p_attribute7 => p_attribute7,
1286 p_attribute8 => p_attribute8,
1287 p_attribute9 => p_attribute9,
1288 p_attribute10 => p_attribute10,
1289 p_attribute11 => p_attribute11,
1290 p_attribute12 => p_attribute12,
1291 p_attribute13 => p_attribute13,
1292 p_attribute14 => p_attribute14,
1293 p_attribute15 => p_attribute15,
1294 p_attribute16 => p_attribute16,
1295 p_attribute17 => p_attribute17,
1296 p_attribute18 => p_attribute18,
1297 p_attribute19 => p_attribute19,
1298 p_attribute20 => p_attribute20,
1299 p_competence_alias => p_competence_alias,
1300 p_competence_definition_id => l_competence_definition_id,
1301 p_competence_cluster => p_competence_cluster,
1302 p_unit_standard_id => p_unit_standard_id ,
1303 p_credit_type => p_credit_type,
1304 p_credits => p_credits,
1305 p_level_type => p_level_type,
1306 p_level_number => p_level_number,
1307 p_field => p_field,
1308 p_sub_field => p_sub_field,
1309 p_provider => p_provider,
1310 p_qa_organization => p_qa_organization,
1311 p_information_category => p_information_category,
1312 p_information1 => p_information1,
1313 p_information2 => p_information2,
1314 p_information3 => p_information3,
1315 p_information4 => p_information4,
1316 p_information5 => p_information5,
1317 p_information6 => p_information6,
1318 p_information7 => p_information7,
1319 p_information8 => p_information8,
1320 p_information9 => p_information9,
1321 p_information10 => p_information10,
1322 p_information11 => p_information11,
1323 p_information12 => p_information12,
1324 p_information13 => p_information13,
1325 p_information14 => p_information14,
1326 p_information15 => p_information15,
1327 p_information16 => p_information16,
1328 p_information17 => p_information17,
1329 p_information18 => p_information18,
1330 p_information19 => p_information19,
1331 p_information20 => p_information20
1332 );
1333 --
1334 --
1335 hr_utility.set_location(l_proc, 11);
1336 --
1337 -- MLS Processing
1338 --
1339 --
1340 per_cpl_upd.upd_tl
1341 (p_effective_date => p_effective_date
1342 ,p_language_code => l_language_code
1343 ,p_competence_id => p_competence_id
1344 ,p_name => l_name
1345 ,p_competence_alias => p_competence_alias
1346 ,p_behavioural_indicator => p_behavioural_indicator
1347 ,p_description => p_description
1348 );
1349 --
1350 -- Call After Process User Hook
1351 --
1352 begin
1353 hr_competences_bk2.update_competence_a (
1354 p_competence_id => p_competence_id,
1355 p_object_version_number => l_object_version_number,
1356 p_effective_date => p_effective_date,
1357 p_name => l_name,
1358 p_description => p_description,
1359 p_date_from => trunc(p_date_from),
1360 p_date_to => trunc(p_date_to),
1361 p_behavioural_indicator => p_behavioural_indicator,
1362 p_certification_required => p_certification_required,
1363 p_evaluation_method => p_evaluation_method,
1364 p_renewal_period_frequency => p_renewal_period_frequency,
1365 p_renewal_period_units => p_renewal_period_units,
1366 p_rating_scale_id => p_rating_scale_id,
1367 p_attribute_category => p_attribute_category,
1368 p_attribute1 => p_attribute1,
1369 p_attribute2 => p_attribute2,
1370 p_attribute3 => p_attribute3,
1371 p_attribute4 => p_attribute4,
1372 p_attribute5 => p_attribute5,
1373 p_attribute6 => p_attribute6,
1374 p_attribute7 => p_attribute7,
1375 p_attribute8 => p_attribute8,
1376 p_attribute9 => p_attribute9,
1377 p_attribute10 => p_attribute10,
1378 p_attribute11 => p_attribute11,
1379 p_attribute12 => p_attribute12,
1380 p_attribute13 => p_attribute13,
1381 p_attribute14 => p_attribute14,
1382 p_attribute15 => p_attribute15,
1383 p_attribute16 => p_attribute16,
1384 p_attribute17 => p_attribute17,
1385 p_attribute18 => p_attribute18,
1386 p_attribute19 => p_attribute19,
1387 p_attribute20 => p_attribute20,
1388 p_competence_alias => p_competence_alias,
1389 p_segment1 => l_segment1
1390 ,p_segment2 => l_segment2
1391 ,p_segment3 => l_segment3
1392 ,p_segment4 => l_segment4
1393 ,p_segment5 => l_segment5
1394 ,p_segment6 => l_segment6
1395 ,p_segment7 => l_segment7
1396 ,p_segment8 => l_segment8
1397 ,p_segment9 => l_segment9
1398 ,p_segment10 => l_segment10
1399 ,p_segment11 => l_segment11
1400 ,p_segment12 => l_segment12
1401 ,p_segment13 => l_segment13
1402 ,p_segment14 => l_segment14
1403 ,p_segment15 => l_segment15
1404 ,p_segment16 => l_segment16
1405 ,p_segment17 => l_segment17
1406 ,p_segment18 => l_segment18
1407 ,p_segment19 => l_segment19
1408 ,p_segment20 => l_segment20
1409 ,p_segment21 => l_segment21
1410 ,p_segment22 => l_segment22
1411 ,p_segment23 => l_segment23
1412 ,p_segment24 => l_segment24
1413 ,p_segment25 => l_segment25
1414 ,p_segment26 => l_segment26
1415 ,p_segment27 => l_segment27
1416 ,p_segment28 => l_segment28
1417 ,p_segment29 => l_segment29
1418 ,p_segment30 => l_segment30
1419 ,p_concat_segments => p_concat_segments
1420 ,p_competence_definition_id => l_competence_definition_id
1421 ,p_language_code => l_language_code
1422 ,p_competence_cluster => p_competence_cluster
1423 ,p_unit_standard_id => p_unit_standard_id
1424 ,p_credit_type => p_credit_type
1425 ,p_credits => p_credits
1426 ,p_level_type => p_level_type
1427 ,p_level_number => p_level_number
1428 ,p_field => p_field
1429 ,p_sub_field => p_sub_field
1430 ,p_provider => p_provider
1431 ,p_qa_organization => p_qa_organization
1432 ,p_information_category => p_information_category
1433 ,p_information1 => p_information1
1434 ,p_information2 => p_information2
1435 ,p_information3 => p_information3
1436 ,p_information4 => p_information4
1437 ,p_information5 => p_information5
1438 ,p_information6 => p_information6
1439 ,p_information7 => p_information7
1440 ,p_information8 => p_information8
1441 ,p_information9 => p_information9
1442 ,p_information10 => p_information10
1443 ,p_information11 => p_information11
1444 ,p_information12 => p_information12
1445 ,p_information13 => p_information13
1446 ,p_information14 => p_information14
1447 ,p_information15 => p_information15
1448 ,p_information16 => p_information16
1449 ,p_information17 => p_information17
1450 ,p_information18 => p_information18
1451 ,p_information19 => p_information19
1452 ,p_information20 => p_information20
1453 );
1454 exception
1455 when hr_api.cannot_find_prog_unit then
1456 hr_api.cannot_find_prog_unit_error
1457 (p_module_name => 'update_competence',
1458 p_hook_type => 'AP'
1459 );
1460 end;
1461 --
1462 -- End After Process User Hook
1463 --
1464 -- When in validation only mode raise the Validate_Enabled exception
1465 --
1466 if p_validate then
1467 raise hr_api.validate_enabled;
1468 end if;
1469 --
1470 -- Set all output arguments. l_object_version_number now has the new
1471 -- object version number as the update was successful
1472 --
1473 p_object_version_number := l_object_version_number;
1474 p_name := l_name;
1475 p_competence_definition_id := l_competence_definition_id;
1476 --
1477 hr_utility.set_location(p_concat_segments, 694);
1478 hr_utility.set_location(' Leaving:'||l_proc, 12);
1479 exception
1480 when hr_api.validate_enabled then
1481 --
1482 -- As the Validate_Enabled exception has been raised
1483 -- we must rollback to the savepoint
1484 --
1485 ROLLBACK TO update_competence;
1486 --
1487 -- Only set output warning arguments and in out arguments back
1488 -- to their IN value
1489 -- (Any key or derived arguments must be set to null
1490 -- when validation only mode is being used.)
1491 --
1492 p_object_version_number := l_object_version_number;
1493 p_name := null;
1494 if l_null_ind = 0
1495 then
1496 p_competence_definition_id := null;
1497 end if;
1498 --
1499 when others then
1500 --
1501 -- A validation or unexpected error has occurred
1502 --
1503 -- Added as part of fix to bug 632482
1504 --
1505 ROLLBACK TO update_competence;
1506 p_object_version_number := l_ovn;
1507 p_name := null;
1508 p_competence_definition_id := l_comp_def_id;
1509 raise;
1510 --
1511 -- End of fix.
1512 --
1513 hr_utility.set_location(' Leaving:'||l_proc, 12);
1514 --
1515 end update_competence;
1516 --
1517 --
1518 -- ---------------------------------------------------------------------------
1519 -- |-----------------------< <delete_competence> >--------------------------|
1520 -- ---------------------------------------------------------------------------
1521 --
1522 procedure delete_competence
1523 (p_validate in boolean default false,
1524 p_competence_id in number,
1525 p_object_version_number in number
1526 ) is
1527 --
1528 -- Declare cursors and local variables
1529 --
1530 cursor csr_get_prof_levels(c_competence_id per_competences.competence_id%TYPE)
1531 is
1532 select rating_level_id,
1533 object_version_number
1534 from per_rating_levels
1535 where competence_id = c_competence_id;
1536 --
1537 --
1538 l_proc varchar2(72) := g_package||'delete_competence';
1539 begin
1540 hr_utility.set_location('Entering:'|| l_proc, 5);
1541 --
1542 -- Issue a savepoint.
1543 --
1544 savepoint delete_competence;
1545 hr_utility.set_location(l_proc, 6);
1546 --
1547 -- Call Before Process User Hook
1548 --
1549 begin
1550 hr_competences_bk3.delete_competence_b
1551 (
1552 p_competence_id => p_competence_id,
1553 p_object_version_number => p_object_version_number
1554 );
1555 exception
1556 when hr_api.cannot_find_prog_unit then
1557 hr_api.cannot_find_prog_unit_error
1558 (p_module_name => 'delete_competence',
1559 p_hook_type => 'BP'
1560 );
1561 end;
1562 --
1563 -- End of Before Process User hook
1564 --
1565 -- Validation in addition to Table Handlers
1566 --
1567 hr_utility.set_location(l_proc, 7);
1568 --
1569 -- Process Logic
1570 --
1571 --
1572 -- check if competence has any rating levels (proficiency)
1573 -- if yes, delete them.
1574 --
1575 for c_prof_rec in csr_get_prof_levels(p_competence_id) loop
1576 per_rtl_del.del
1577 (p_validate => FALSE
1578 ,p_rating_level_id => c_prof_rec.rating_level_id
1579 ,p_object_version_number => c_prof_rec.object_version_number
1580 );
1581 end loop;
1582 --
1583 --
1584 -- MLS Processing
1585 --
1586 per_cpl_del.del_tl
1587 (p_competence_id => p_competence_id
1588 );
1589
1590 -- now delete the competence itself
1591 --
1592 per_cpn_del.del
1593 (p_validate => FALSE
1594 ,p_competence_id => p_competence_id
1595 ,p_object_version_number => p_object_version_number
1596 );
1597 --
1598 hr_utility.set_location(l_proc, 8);
1599 --
1600 -- Call After Process User Hook
1601 --
1602 begin
1603 hr_competences_bk3.delete_competence_a (
1604 p_competence_id => p_competence_id,
1605 p_object_version_number => p_object_version_number
1606 );
1607 exception
1608 when hr_api.cannot_find_prog_unit then
1609 hr_api.cannot_find_prog_unit_error
1610 (p_module_name => 'delete_competence',
1611 p_hook_type => 'AP'
1612 );
1613 end;
1614 --
1615 -- End of After Process User hook
1616 --
1617 -- When in validation only mode raise the Validate_Enabled exception
1618 --
1619 if p_validate then
1620 raise hr_api.validate_enabled;
1621 end if;
1622 --
1623 hr_utility.set_location(' Leaving:'||l_proc, 11);
1624 exception
1625 when hr_api.validate_enabled then
1626 --
1627 -- As the Validate_Enabled exception has been raised
1628 -- we must rollback to the savepoint
1629 --
1630 ROLLBACK TO delete_competence;
1631 --
1632 --
1633 when others then
1634 --
1635 -- A validation or unexpected error has occurred
1636 --
1637 -- Added as part of fix to bug 632482
1638 --
1639 ROLLBACK TO delete_competence;
1640 raise;
1641 --
1642 -- End of fix.
1643 --
1644 hr_utility.set_location(' Leaving:'||l_proc, 12);
1645 end delete_competence;
1646 --
1647 -- ---------------------------------------------------------------------------
1648 -- |----------------< create_or_update_competence >--------------------------|
1649 -- ---------------------------------------------------------------------------
1650 --
1651 procedure create_or_update_competence
1652 (p_validate in boolean default false
1653 ,p_effective_date in date default trunc(sysdate)
1654 ,p_language_code in varchar2 default hr_api.userenv_lang
1655 ,p_category in varchar2 default null
1656 ,p_name in varchar2 default null
1657 ,p_date_from in date default trunc(sysdate)
1658 ,p_date_to in date default null
1659 ,p_description in varchar2 default null
1660 ,p_competence_alias in varchar2 default null
1661 ,p_behavioural_indicator in varchar2 default null
1662 ,p_certification_required in varchar2 default null
1663 ,p_evaluation_method in varchar2 default null
1664 ,p_renewal_period_frequency in number default null
1665 ,p_renewal_period_units in varchar2 default null
1666 ,p_rating_scale_name in varchar2 default null
1667 ,p_translated_language in varchar2 default null
1668 ,p_source_category_name in varchar2 default null
1669 ,p_source_competence_name in varchar2 default null
1670
1671 ) is
1672
1673 l_proc varchar2(72) := g_package||'create_or_update_competence';
1674 l_competence_id per_competences.competence_id%TYPE;
1675 l_source_competence_id per_competences.competence_id%TYPE;
1676 l_conc_name varchar2(700);
1677 l_flex_num number;
1678 l_competence_definition_id per_competences.competence_definition_id%TYPE;
1679 l_rating_scale_id per_competences.rating_scale_id%TYPE;
1680 l_ovn per_competences.object_version_number%TYPE;
1681 l_source_ovn per_competences.object_version_number%TYPE;
1682 l_effective_date date;
1683 l_date_from date;
1684 l_evaluation_method per_competences.evaluation_method%TYPE;
1685 l_certification_required per_competences.certification_required%TYPE;
1686 l_renewal_period_units per_competences.renewal_period_units%TYPE;
1687 l_language_code varchar2(20);
1688 l_translated_language varchar2(20);
1689 l_segment1 varchar2(150);
1690 l_segment2 varchar2(150);
1691 l_concatenate_name varchar2(150);
1692
1693 cursor csr_cpn(p_competence_name in varchar2)
1694 is
1695 select competence_id , object_version_number
1696 from per_competences_v
1697 where business_group_id is null
1698 and name = p_competence_name;
1699
1700 cursor csr_rsc
1701 is
1702 select rating_scale_id
1703 from per_rating_scales
1704 where business_group_id is null
1705 and name = p_rating_scale_name;
1706
1707 --
1708 -- Declare local modules
1709 --
1710 function return_lookup_code
1711 (p_meaning in fnd_lookup_values.meaning%TYPE default null
1712 ,p_lookup_type in fnd_lookup_values.lookup_code%TYPE
1713 ,p_language_code in fnd_lookup_values.language%TYPE
1714 )
1715 Return fnd_lookup_values.lookup_code%TYPE Is
1716 --
1717 l_lookup_code fnd_lookup_values.lookup_code%TYPE := null;
1718 --
1719 Cursor Sel_Id Is
1720 select flv.lookup_code
1721 from fnd_lookup_values flv
1722 where flv.lookup_type = p_lookup_type
1723 and UPPER(flv.meaning) = UPPER(p_meaning)
1724 and flv.language = p_language_code
1725 and flv.enabled_flag = 'Y';
1726 --
1727 begin
1728 open Sel_Id;
1729 fetch Sel_Id Into l_lookup_code;
1730 if Sel_Id%notfound then
1731 close Sel_Id;
1732 hr_utility.set_message(800, 'HR_449156_LOOK_MEANING_INVALID');
1733 fnd_message.set_token('MEANING',p_meaning);
1734 hr_utility.raise_error;
1735 end if;
1736 close Sel_Id;
1737 --
1738 return (l_lookup_code);
1739 end;
1740 --
1741 Begin
1742 --
1743 hr_utility.set_location('Entering...' || l_proc,10);
1744 --
1745 -- Issue a savepoint.
1746 --
1747 savepoint create_or_update_competence;
1748
1749 hr_competences_api.g_ignore_df := 'Y';
1750
1751 if (p_language_code is NULL) then
1752 l_language_code := hr_api.userenv_lang;
1753 else
1754 l_language_code := p_language_code;
1755 -- BUG3668368
1756 hr_api.validate_language_code(p_language_code => l_language_code);
1757 end if;
1758
1759 if (p_effective_date is NULL) then
1760 l_effective_date := trunc(sysdate);
1761 else
1762 l_effective_date := trunc(p_effective_date);
1763 end if;
1764 hr_utility.trace('p_effective_date : ' || p_effective_date);
1765
1766 if (p_date_from is NULL) then
1767 l_date_from := trunc(sysdate);
1768 else
1769 l_date_from := trunc(p_date_from);
1770 end if;
1771 hr_utility.trace('p_date_from : ' || p_date_from);
1772
1773 IF ( p_translated_language IS NULL AND p_source_competence_name IS NULL
1774 AND p_source_category_name IS NULL)
1775 THEN
1776 hr_utility.set_location(l_proc,20);
1777
1778 if (p_category IS NOT NULL) then
1779 l_concatenate_name := p_category || '.' || p_name;
1780 l_segment1 := p_category;
1781 l_segment2 := p_name;
1782 else
1783 l_concatenate_name := p_name;
1784 l_segment1 := p_name;
1785 l_segment2 := NULL;
1786 end if;
1787
1788 open csr_cpn(l_concatenate_name);
1789 fetch csr_cpn into l_competence_id, l_ovn;
1790 if csr_cpn%NOTFOUND then
1791 close csr_cpn;
1792 l_competence_id := NULL;
1793 l_ovn := NULL;
1794 else
1795 close csr_cpn;
1796 end if;
1797 hr_utility.trace('l_competence_id : ' || l_competence_id);
1798 hr_utility.trace('l_ovn : ' || l_ovn);
1799
1800 if (p_rating_scale_name is not NULL) then
1801 open csr_rsc;
1802 fetch csr_rsc into l_rating_scale_id;
1803 if csr_rsc%NOTFOUND then
1804 close csr_rsc;
1805 fnd_message.set_name('PER','HR_51928_APT_RSC_NOT_EXIST');
1806 fnd_message.raise_error;
1807 end if;
1808 close csr_rsc;
1809 hr_utility.trace('l_rating_scale_id : ' || l_rating_scale_id);
1810 end if;
1811
1812 hr_utility.set_location(l_proc,30);
1813
1814 if (p_evaluation_method is not null) then
1815 l_evaluation_method := return_lookup_code
1816 (p_meaning => p_evaluation_method
1817 ,p_lookup_type => 'COMPETENCE_EVAL_TYPE'
1818 ,p_language_code => l_language_code
1819 );
1820 hr_utility.trace('l_evaluation_method : ' || l_evaluation_method);
1821 else
1822 l_evaluation_method := null;
1823 end if;
1824
1825 if (p_certification_required is not null) then
1826 l_certification_required := return_lookup_code
1827 (p_meaning => p_certification_required
1828 ,p_lookup_type => 'YES_NO'
1829 ,p_language_code => l_language_code
1830 );
1831 hr_utility.trace('l_certification_required : ' || l_certification_required);
1832 else
1833 l_certification_required := 'N';
1834 end if;
1835
1836 if (p_renewal_period_units is not null) then
1837 l_renewal_period_units := return_lookup_code
1838 (p_meaning => p_renewal_period_units
1839 ,p_lookup_type => 'FREQUENCY'
1840 ,p_language_code => l_language_code
1841 );
1842 hr_utility.trace('l_renewal_period_units : ' || l_renewal_period_units);
1843 else
1844 l_renewal_period_units := null;
1845 end if;
1846
1847 --
1848 if ( l_competence_id is null and l_ovn is null) then
1849
1850 hr_utility.set_location(l_proc,40);
1851
1852 create_competence
1853 (p_effective_date => l_effective_date
1854 ,p_language_code => l_language_code
1855 ,p_description => p_description
1856 ,p_competence_alias => p_competence_alias
1857 ,p_date_from => l_date_from
1858 ,p_date_to => p_date_to
1859 ,p_behavioural_indicator => p_behavioural_indicator
1860 ,p_certification_required => l_certification_required
1861 ,p_evaluation_method => l_evaluation_method
1862 ,p_renewal_period_frequency => p_renewal_period_frequency
1863 ,p_renewal_period_units => l_renewal_period_units
1864 ,p_rating_scale_id => l_rating_scale_id
1865 ,p_segment1 => l_segment1
1866 ,p_segment2 => l_segment2
1867 ,p_competence_id => l_competence_id
1868 ,p_competence_definition_id => l_competence_definition_id
1869 ,p_object_version_number => l_ovn
1870 ,p_name => l_conc_name
1871 );
1872
1873 else
1874
1875 hr_utility.set_location(l_proc,50);
1876
1877 update_competence
1878 (p_effective_date => l_effective_date
1879 ,p_language_code => l_language_code
1880 ,p_description => p_description
1881 ,p_competence_alias => p_competence_alias
1882 ,p_date_from => l_date_from
1883 ,p_date_to => p_date_to
1884 ,p_behavioural_indicator => p_behavioural_indicator
1885 ,p_certification_required => l_certification_required
1886 ,p_evaluation_method => l_evaluation_method
1887 ,p_renewal_period_frequency => p_renewal_period_frequency
1888 ,p_renewal_period_units => l_renewal_period_units
1889 ,p_rating_scale_id => l_rating_scale_id
1890 ,p_segment1 => l_segment1
1891 ,p_segment2 => l_segment2
1892 ,p_competence_id => l_competence_id
1893 ,p_competence_definition_id => l_competence_definition_id
1894 ,p_object_version_number => l_ovn
1895 ,p_name => l_conc_name
1896 );
1897
1898 end if;
1899
1900 ELSE
1901
1902 --
1903 -- MLS update
1904 --
1905 hr_utility.set_location(l_proc,50);
1906
1907 if (p_source_category_name IS NOT NULL) then
1908 l_concatenate_name := p_source_category_name || '.' || p_source_competence_name;
1909 else
1910 l_concatenate_name := p_source_competence_name;
1911 end if;
1912 hr_utility.trace('l_concatenate_name => ' || l_concatenate_name);
1913
1914 open csr_cpn(l_concatenate_name);
1915 fetch csr_cpn into l_source_competence_id, l_source_ovn;
1916 if csr_cpn%NOTFOUND then
1917 close csr_cpn;
1918 hr_utility.set_message(800, 'HR_449189_SOURCE_CPN_INVALID');
1919 hr_utility.raise_error;
1920 else
1921 close csr_cpn;
1922 end if;
1923 hr_utility.trace('l_source_competence_id : ' || l_source_competence_id);
1924 hr_utility.trace('l_source_ovn : ' || l_source_ovn);
1925
1926 l_translated_language := p_translated_language;
1927 hr_api.validate_language_code(p_language_code => l_translated_language);
1928
1929 --
1930 -- MLS Processing
1931 --
1932 --
1933 per_cpl_upd.upd_tl
1934 (p_effective_date => l_effective_date
1935 ,p_language_code => l_translated_language
1936 ,p_competence_id => l_source_competence_id
1937 ,p_name => l_concatenate_name
1938 ,p_competence_alias => p_competence_alias
1939 ,p_behavioural_indicator => p_behavioural_indicator
1940 ,p_description => p_description
1941 );
1942
1943 hr_utility.set_location(l_proc,60);
1944
1945 --
1946 -- Call After Process User Hook
1947 --
1948 begin
1949 hr_competences_bk2.update_competence_a (
1950 p_competence_id => l_source_competence_id,
1951 p_object_version_number => l_source_ovn,
1952 p_effective_date => l_effective_date,
1953 p_name => p_source_competence_name,
1954 p_description => p_description,
1955 p_date_from => trunc(l_date_from),
1956 p_date_to => trunc(p_date_to),
1957 p_behavioural_indicator => p_behavioural_indicator,
1958 p_certification_required => p_certification_required,
1959 p_evaluation_method => p_evaluation_method,
1960 p_renewal_period_frequency => p_renewal_period_frequency,
1961 p_renewal_period_units => p_renewal_period_units,
1962 p_rating_scale_id => NULL,
1963 p_attribute_category => NULL,
1964 p_attribute1 => NULL,
1965 p_attribute2 => NULL,
1966 p_attribute3 => NULL,
1967 p_attribute4 => NULL,
1968 p_attribute5 => NULL,
1969 p_attribute6 => NULL,
1970 p_attribute7 => NULL,
1971 p_attribute8 => NULL,
1972 p_attribute9 => NULL,
1973 p_attribute10 => NULL,
1974 p_attribute11 => NULL,
1975 p_attribute12 => NULL,
1976 p_attribute13 => NULL,
1977 p_attribute14 => NULL,
1978 p_attribute15 => NULL,
1979 p_attribute16 => NULL,
1980 p_attribute17 => NULL,
1981 p_attribute18 => NULL,
1982 p_attribute19 => NULL,
1983 p_attribute20 => NULL,
1984 p_competence_alias => p_competence_alias,
1985 p_segment1 => NULL
1986 ,p_segment2 => NULL
1987 ,p_segment3 => NULL
1988 ,p_segment4 => NULL
1989 ,p_segment5 => NULL
1990 ,p_segment6 => NULL
1991 ,p_segment7 => NULL
1992 ,p_segment8 => NULL
1993 ,p_segment9 => NULL
1994 ,p_segment10 => NULL
1995 ,p_segment11 => NULL
1996 ,p_segment12 => NULL
1997 ,p_segment13 => NULL
1998 ,p_segment14 => NULL
1999 ,p_segment15 => NULL
2000 ,p_segment16 => NULL
2001 ,p_segment17 => NULL
2002 ,p_segment18 => NULL
2003 ,p_segment19 => NULL
2004 ,p_segment20 => NULL
2005 ,p_segment21 => NULL
2006 ,p_segment22 => NULL
2007 ,p_segment23 => NULL
2008 ,p_segment24 => NULL
2009 ,p_segment25 => NULL
2010 ,p_segment26 => NULL
2011 ,p_segment27 => NULL
2012 ,p_segment28 => NULL
2013 ,p_segment29 => NULL
2014 ,p_segment30 => NULL
2015 ,p_concat_segments => NULL
2016 ,p_competence_definition_id => NULL
2017 ,p_language_code => l_translated_language
2018 ,p_competence_cluster => NULL
2019 ,p_unit_standard_id => NULL
2020 ,p_credit_type => NULL
2021 ,p_credits => NULL
2022 ,p_level_type => NULL
2023 ,p_level_number => NULL
2024 ,p_field => NULL
2025 ,p_sub_field => NULL
2026 ,p_provider => NULL
2027 ,p_qa_organization => NULL
2028 ,p_information_category => NULL
2029 ,p_information1 => NULL
2030 ,p_information2 => NULL
2031 ,p_information3 => NULL
2032 ,p_information4 => NULL
2033 ,p_information5 => NULL
2034 ,p_information6 => NULL
2035 ,p_information7 => NULL
2036 ,p_information8 => NULL
2037 ,p_information9 => NULL
2038 ,p_information10 => NULL
2039 ,p_information11 => NULL
2040 ,p_information12 => NULL
2041 ,p_information13 => NULL
2042 ,p_information14 => NULL
2043 ,p_information15 => NULL
2044 ,p_information16 => NULL
2045 ,p_information17 => NULL
2046 ,p_information18 => NULL
2047 ,p_information19 => NULL
2048 ,p_information20 => NULL
2049 );
2050 exception
2051 when hr_api.cannot_find_prog_unit then
2052 hr_competences_api.g_ignore_df := 'N';
2053 hr_api.cannot_find_prog_unit_error
2054 (p_module_name => 'update_competence',
2055 p_hook_type => 'AP'
2056 );
2057 end;
2058
2059 END IF;
2060 hr_competences_api.g_ignore_df := 'N';
2061 hr_utility.set_location('Leaving... ' || l_proc,70);
2062 exception
2063 when hr_api.validate_enabled then
2064 --
2065 -- As the Validate_Enabled exception has been raised
2066 -- we must rollback to the savepoint
2067 --
2068 ROLLBACK TO creaet_or_update_competence;
2069 --
2070 hr_competences_api.g_ignore_df := 'N';
2071 --
2072 --
2073 when others then
2074 --
2075 -- A validation or unexpected error has occurred
2076 --
2077 --
2078 ROLLBACK TO create_or_update_competence;
2079 --
2080 hr_competences_api.g_ignore_df := 'N';
2081 raise;
2082 hr_utility.set_location(' Leaving:'||l_proc, 80);
2083 end create_or_update_competence;
2084 --
2085 --
2086 end hr_competences_api;