1 Package Body per_cel_bus as
2 /* $Header: pecelrhi.pkb 120.3.12020000.3 2012/07/05 01:49:58 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cel_bus.'; -- Global package name
9 --
10 -- Followwing two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_competence_element_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |----------------------< check_non_updateable_args >-----------------------|
18 -- ----------------------------------------------------------------------------
19 --
20 Procedure check_non_updateable_args(p_rec in per_cel_shd.g_rec_type) is
21 --
22 l_proc varchar2(72) := g_package||'check_non_updateable_args';
23 --
24 Begin
25 hr_utility.set_location('Entering:'||l_proc, 5);
26 --
27 -- Only proceed with validation if a row exists for
28 -- the current record in the HR Schema
29
30 --
31 if not per_cel_shd.api_updating
32 (p_competence_element_id => p_rec.competence_element_id
33 ,p_object_version_number => p_rec.object_version_number
34 ) then
35 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
36 hr_utility.set_message_token('PROCEDURE', l_proc);
37 hr_utility.set_message_token('STEP', '5');
38 end if;
39 --
40 hr_utility.set_location(l_proc, 6);
41 --
42 if p_rec.business_group_id <> per_cel_shd.g_old_rec.business_group_id then
43 hr_api.argument_changed_error
44 (p_api_name => l_proc
45 ,p_argument => 'BUSINESS_GROUP_ID'
46 ,p_base_table => per_cel_shd.g_tab_nam
47 );
48 end if;
49 hr_utility.set_location(l_proc, 7);
50 --
51 if p_rec.competence_id <> per_cel_shd.g_old_rec.competence_id then
52 hr_api.argument_changed_error
53 (p_api_name => l_proc
54 ,p_argument => 'COMPETENCE_ID'
55 ,p_base_table => per_cel_shd.g_tab_nam
56 );
57 end if;
58 hr_utility.set_location(l_proc, 8);
59 --
60 if p_rec.competence_element_id <>
61 per_cel_shd.g_old_rec.competence_element_id then
62 hr_api.argument_changed_error
63 (p_api_name => l_proc
64 ,p_argument => 'COMPETENCE_ELEMENT_ID'
65 ,p_base_table => per_cel_shd.g_tab_nam
66 );
67
68 end if;
69 hr_utility.set_location(l_proc, 9);
70 --
71 if p_rec.parent_competence_element_id <>
72 per_cel_shd.g_old_rec.parent_competence_element_id then
73 hr_api.argument_changed_error
74 (p_api_name => l_proc
75 ,p_argument => 'PARENT_COMPETENCE_ELEMENT_ID'
76 ,p_base_table => per_cel_shd.g_tab_nam
77 );
78 end if;
79 hr_utility.set_location(l_proc, 10);
80 --
81 if p_rec.activity_version_id <> per_cel_shd.g_old_rec.activity_version_id then
82 hr_api.argument_changed_error
83 (p_api_name => l_proc
84 ,p_argument => 'ACTIVITY_VERSION_ID'
85 ,p_base_table => per_cel_shd.g_tab_nam
86 );
87 end if;
88 hr_utility.set_location(l_proc, 13);
89 --
90 hr_utility.set_location(l_proc, 14);
91 --
92 if p_rec.person_id <> per_cel_shd.g_old_rec.person_id then
93 hr_api.argument_changed_error
94 (p_api_name => l_proc
95 ,p_argument => 'PERSON_ID'
96 ,p_base_table => per_cel_shd.g_tab_nam
97 );
98 end if;
99 -- HR/TCA merge support party_id
100 -- But allow change to NULL or if currently NULL
101 if nvl(p_rec.party_id,per_cel_shd.g_old_rec.party_id) <>
102 nvl(per_cel_shd.g_old_rec.party_id,p_rec.party_id) then
103 hr_api.argument_changed_error
104 (p_api_name => l_proc
105 ,p_argument => 'PARTY_ID'
106 ,p_base_table => per_cel_shd.g_tab_nam
107 );
108 end if;
109
110 hr_utility.set_location(l_proc, 15);
111 --
112 if p_rec.job_id <> per_cel_shd.g_old_rec.job_id then
113 hr_api.argument_changed_error
114 (p_api_name => l_proc
115 ,p_argument => 'JOB_ID'
116 ,p_base_table => per_cel_shd.g_tab_nam
117 );
118
119 end if;
120 hr_utility.set_location(l_proc, 16);
121 --
122 if p_rec.valid_grade_id <> per_cel_shd.g_old_rec.valid_grade_id then
123 hr_api.argument_changed_error
124 (p_api_name => l_proc
125 ,p_argument => 'VALID_GRADE_ID'
126 ,p_base_table => per_cel_shd.g_tab_nam
127 );
128 end if;
129 hr_utility.set_location(l_proc, 16);
130 --
131 if p_rec.organization_id <> per_cel_shd.g_old_rec.organization_id then
132 hr_api.argument_changed_error
133 (p_api_name => l_proc
134 ,p_argument => 'ORGANIZATION_ID'
135 ,p_base_table => per_cel_shd.g_tab_nam
136 );
137 end if;
138 hr_utility.set_location(l_proc, 17);
139
140 --
141 if p_rec.assessment_id <> per_cel_shd.g_old_rec.assessment_id then
142 hr_api.argument_changed_error
143 (p_api_name => l_proc
144 ,p_argument => 'ASSESSMENT_ID'
145 ,p_base_table => per_cel_shd.g_tab_nam
146 );
147 end if;
148 hr_utility.set_location(l_proc, 18);
149 --
150 --
151 if p_rec.enterprise_id <> per_cel_shd.g_old_rec.enterprise_id then
152 hr_api.argument_changed_error
153 (p_api_name => l_proc
154 ,p_argument => 'ENTERPRISE_ID'
155 ,p_base_table => per_cel_shd.g_tab_nam
156 );
157 end if;
158 hr_utility.set_location(l_proc, 20);
159
160 --
161 --
162 if p_rec.position_id <> per_cel_shd.g_old_rec.position_id then
163 hr_api.argument_changed_error
164 (p_api_name => l_proc
165 ,p_argument => 'POSITION_ID'
166 ,p_base_table => per_cel_shd.g_tab_nam
167 );
168 end if;
169 hr_utility.set_location(l_proc, 22);
170 --
171 if p_rec.type <> per_cel_shd.g_old_rec.type then
172 hr_api.argument_changed_error
173 (p_api_name => l_proc
174 ,p_argument => 'TYPE'
175 ,p_base_table => per_cel_shd.g_tab_nam
176 );
177 end if;
178 hr_utility.set_location(l_proc, 25);
179 --
180 if p_rec.object_id <> per_cel_shd.g_old_rec.object_id then
181 hr_api.argument_changed_error
182 (p_api_name => l_proc
183 ,p_argument => 'OBJECT_ID'
184 ,p_base_table => per_cel_shd.g_tab_nam
185 );
186 end if;
187 --
188 if p_rec.object_name <> per_cel_shd.g_old_rec.object_name then
189 hr_api.argument_changed_error
190 (p_api_name => l_proc
191 ,p_argument => 'OBJECT_NAME'
192 ,p_base_table => per_cel_shd.g_tab_nam
193 );
194 end if;
195 end check_non_updateable_args;
196 --
197 ------------------------------------------------------------------------------
198 -- |--------------------------<CHK_mandatory >-------------------------------|
199 -----------------------------------------------------------------------------
200
201 --
202 -- Description;
203 -- Validates that the value entered for mandatory exists
204 -- in HR_LOOKUPS
205 --
206 -- Pre-Conditions:
207 -- None
208 --
209 -- In Arguments:
210 -- p_competence_element_id
211 -- p_effective_date
212 -- p_mandatory
213 -- p_object_version_number
214
215 --
216 -- Post Success:
217 -- Processing continues if:
218 -- - The mandatory value is valid
219 --
220 -- Post Failure:
221 -- An application error is raised and processing is terminated if any
222 -- - The mandatory value is invalid
223 --
224 -- Access Status:
225 -- Internal Table Handler Use Only.
226 --
227 --
228
229 --
230 procedure chk_mandatory
231 (p_competence_element_id
232 in per_competence_elements.competence_element_id%TYPE
233 ,p_effective_date in Date
234 ,p_mandatory
235 in per_competence_elements.mandatory%TYPE
236 ,p_object_version_number
237 in per_competence_elements.object_version_number%TYPE
238 ) is
239 --
240 l_proc varchar2(72):= g_package||'chk_mandatory';
241 l_api_updating boolean;
242
243 --
244 begin
245 hr_utility.set_location('Entering:'|| l_proc, 1);
246 --
247 -- Check mandatory parameters have being set.
248 --
249 hr_api.mandatory_arg_error
250 (p_api_name => l_proc
251 ,p_argument => 'effective_date'
252 ,p_argument_value => p_effective_date
253 );
254 --
255 -- Only proceed with validation if :
256
257 -- a) The current g_old_rec is current and
258 -- b) The value for mandatory has changed
259 --
260 l_api_updating := per_cel_shd.api_updating
261 (p_competence_element_id => p_competence_element_id
262 ,p_object_version_number => p_object_version_number);
263 --
264 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.mandatory,
265 hr_api.g_varchar2) <>
266 nvl(p_mandatory,hr_api.g_varchar2))
267 OR not l_api_updating ) then
268 hr_utility.set_location(l_proc, 6);
269 --
270 -- check that the p_mandatory exists in hr_lookups.
271 --
272 if (p_mandatory IS NOT NULL ) then
273 if hr_api.not_exists_in_hr_lookups
274 (p_effective_date => p_effective_date
275 ,p_lookup_type => 'YES_NO'
276 ,p_lookup_code => p_mandatory
277 ) then
278 -- Error: Invalid certification_method
279 hr_utility.set_location(l_proc, 10);
280 hr_utility.set_message(801,'HR_51635_CEL_MANDATORY_INVL');
281 hr_multi_message.add
282 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.MANDATORY');
283
284 hr_utility.raise_error;
285 end if;
286
287 --
288 end if;
289 end if;
290 --
291 hr_utility.set_location(' Leaving:'|| l_proc, 15);
292 exception
293 when app_exception.application_exception then
294 if hr_multi_message.exception_add
295 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.MANDATORY'
296 ) then
297 hr_utility.set_location(' Leaving:'||l_proc,20);
298 raise;
299 end if;
300 hr_utility.set_location(' Leaving:'||l_proc,25);
301 end chk_mandatory;
302 --
303 ------------------------------------------------------------------------------
304 -- |--------------------------<CHK_CERTIFICATION_METHOD >--------------------|
305 -----------------------------------------------------------------------------
306 --
307 -- Description;
308
309 -- Validates that the value entered for certification method exists
310 -- in HR_LOOKUPS
311 --
312 -- Pre-Conditions:
313 -- None
314 --
315 -- In Arguments:
316 -- p_competence_element_id
317 -- p_effective_date
318 -- p_certification_method
319 -- p_object_version_number
320 --
321 -- Post Success:
322
323 -- Processing continues if:
324 -- - The certification_method value is valid
325 --
326 -- Post Failure:
327 -- An application error is raised and processing is terminated if any
328 -- - The certification_method value is invalid
329 --
330 -- Access Status:
331 -- Internal Table Handler Use Only.
332 --
333 --
334 --
335 procedure chk_certification_method
336 (p_competence_element_id
337 in per_competence_elements.competence_element_id%TYPE
338 ,p_effective_date in Date
339 ,p_certification_method
340 in per_competence_elements.certification_method%TYPE
341 ,p_object_version_number
342 in per_competence_elements.object_version_number%TYPE
343 ) is
344 --
345 l_proc varchar2(72):= g_package||'chk_certification_method';
346 l_api_updating boolean;
347 --
348 begin
349
350 hr_utility.set_location('Entering:'|| l_proc, 1);
351 --
352 -- Check mandatory parameters have being set.
353 --
354 hr_api.mandatory_arg_error
355 (p_api_name => l_proc
356 ,p_argument => 'effective_date'
357 ,p_argument_value => p_effective_date
358 );
359 --
360 -- Only proceed with validation if :
361 -- a) The current g_old_rec is current and
362 -- b) The value for certification_method has changed
363 --
364 l_api_updating := per_cel_shd.api_updating
365 (p_competence_element_id => p_competence_element_id
366 ,p_object_version_number => p_object_version_number);
367 --
368 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.certification_method,
369 hr_api.g_varchar2) <>
370 nvl(p_certification_method,hr_api.g_varchar2))
371 OR not l_api_updating ) then
372 hr_utility.set_location(l_proc, 6);
373 --
374 -- check that the p_certification_method exists in hr_lookups.
375 --
376
377 if (p_certification_method IS NOT NULL ) then
378 if hr_api.not_exists_in_hr_lookups
379 (p_effective_date => p_effective_date
380 ,p_lookup_type => 'CERTIFICATION_METHOD'
381 ,p_lookup_code => p_certification_method
382 ) then
383 -- Error: Invalid certification_method
384 hr_utility.set_location(l_proc, 10);
385 hr_utility.set_message(801,'HR_51636_CEL_CERTIF_INVL');
386 hr_multi_message.add
387 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD');
388 hr_utility.raise_error;
389 end if;
390 --
391 end if;
392 --
393 end if;
394 --
395 hr_utility.set_location(' Leaving:'|| l_proc, 15);
396 exception
397 when app_exception.application_exception then
398 if hr_multi_message.exception_add
399 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
400 ) then
401 hr_utility.set_location(' Leaving:'||l_proc,20);
402 raise;
403 end if;
404 hr_utility.set_location(' Leaving:'||l_proc,25);
405 end chk_certification_method;
406 --
407 --
408 ------------------------------------------------------------------------------
409 -- |--------------------< CHK_CERTIFICATION_METHOD_DATE >--------------------|
410 -----------------------------------------------------------------------------
411 --
412 -- Description;
413 -- Validates that if the certification method is entered then the certificatio
414 -- Date is also entered and vice versa.
415 --
416 -- Pre-Conditions:
417 -- None
418 --
419 -- In Arguments:
420 -- p_competence_element_id
421 -- p_certification_method
422 -- p_certification_date
423 -- p_object_version_number
424 --
425 -- Post Success:
426
427 -- Processing continues if:
428 -- - The certification_method value and certification date are both valid
429 --
430 -- Post Failure:
431 -- application error is raised and processing is terminated if any
432 -- - The certification_method and certification date are invalid
433 --
434 -- Access Status:
435 -- Internal Table Handler Use Only.
436 --
437 --
438 --
439 procedure chk_certification_method_date
440 (p_competence_element_id
441 in per_competence_elements.competence_element_id%TYPE
442 ,p_certification_date
443 in per_competence_elements.certification_date%TYPE
444 ,p_certification_method
445 in per_competence_elements.certification_method%TYPE
446 ,p_object_version_number
447 in per_competence_elements.object_version_number%TYPE
448 ) is
449 --
450 l_proc varchar2(72):= g_package||'chk_certification_method';
451 l_api_updating boolean;
452 --
453
454 begin
455 hr_utility.set_location('Entering:'|| l_proc, 1);
456 -- Only proceed with certification method/certification_date validation
457 -- when the multi message list does not already contain an error
458 -- associated with the certification_method.
459 if hr_multi_message.no_exclusive_error
460 ( p_check_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
461 ) then
462 --
463 -- Only proceed with validation if :
464 -- a) The current g_old_rec is current and
465 -- b) The value for certification_method has changed
466 --
467 l_api_updating := per_cel_shd.api_updating
468 (p_competence_element_id => p_competence_element_id
469 ,p_object_version_number => p_object_version_number);
470 --
471 if (l_api_updating AND ((nvl(per_cel_shd.g_old_rec.certification_method,
472 hr_api.g_varchar2) <>
473 nvl(p_certification_method,hr_api.g_varchar2)) OR
474 (nvl(per_cel_shd.g_old_rec.certification_date , hr_api.g_date ) <>
475 nvl(p_certification_date,hr_api.g_date)))
476 OR not l_api_updating ) then
477 hr_utility.set_location(l_proc, 6);
478 --
479 -- if the certification_date is null and certification_method is
480 -- not null then raise an error
481 --
482 if ( (p_certification_method is NOT NULL)
483 and (p_certification_date is NULL) )
484 then
485 hr_utility.set_location(l_proc,10);
486 hr_utility.set_message(801,'HR_51637_CEL_CERF_DATE_METHOD');
487
488 -- Issue with Error not coming up
489 hr_multi_message.add
490 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
491 -- ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
492 );
493
494 hr_utility.raise_error;
495
496 end if;
497 --
498 if ( (p_certification_method is NULL)
499 and (p_certification_date is NOT NULL) )
500 then
501 hr_utility.set_location(l_proc, 20);
502 hr_utility.set_message(801,'HR_51629_CEL_CERF_METHOD_DATE');
503 -- Issue with Error not coming up
504 hr_multi_message.add
505 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
506 -- ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
507 );
508
509 hr_utility.raise_error;
510 end if;
511 --
512 hr_utility.set_location('Leaving: '||l_proc,15);
513 end if;
514 end if;
515 exception
516 when app_exception.application_exception then
517 if hr_multi_message.exception_add
518 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_METHOD'
519 ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
520 ) then
521 hr_utility.set_location(' Leaving:'||l_proc,20);
522 raise;
523 end if;
524 hr_utility.set_location(' Leaving:'||l_proc,25);
525 end chk_certification_method_date;
526 --
527 --
528 ----------------------------------------------------------------------------
529 ---|-----------------------<CHK_NEXT_CERTIFICATION_DATE>-----------|--
530 --
531 -- Description:
532 -- Validates that the date entered for the next certication date is ahead
533 -- of the date entered for the initial certification date
534 --
535 -- Pre-Conditions:
536 -- There must be an existing certification date
537 --
538 -- In Arguments:
539 -- p_competence_element_id
540 -- p_certification_date
541 -- p_next_certification_date
542 -- p_object_version_number
543 --
544 -- Post Success:
545 -- Processing continues if
546 -- - The next_certification_date is valid
547 --
548 -- Post Failure:
549 -- An application error is raised and processing is terminated if
550 -- - The next_certification_date is invalid
551 --
552 -- Access Status:
553 -- Internal Table Handler Use Only.
554 --
555 --
556 --
557 procedure chk_next_certification_date
558
559 (p_competence_element_id
560 in per_competence_elements.competence_element_id%TYPE
561 ,p_certification_date
562 in per_competence_elements.certification_date%TYPE
563 ,p_next_certification_date
564 in per_competence_elements.next_certification_date%TYPE
565 ,p_object_version_number
566 in per_competence_elements.object_version_number%TYPE
567 ,p_effective_date_from
568 in per_competence_elements.effective_date_from%TYPE --added for fix of #731089
569 ) is
570 --
571 l_proc varchar2(72):=g_package||'chk_next_certification_date';
572 l_api_updating boolean;
573 --
574
575
576 begin
577 hr_utility.set_location('Entering:'||l_proc, 1);
578 --
579 if hr_multi_message.no_all_inclusive_error
580 ( p_check_column1 => 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE' ) then
581 --
582 -- Only Proceed if the value for g_old_rec is current
583 -- and the value for the certification method has changed
584 --
585 l_api_updating := per_cel_shd.api_updating
586 (p_competence_element_id =>p_competence_element_id
587 ,p_object_version_number =>p_object_version_number);
588
589 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.certification_date,
590 hr_api.g_date)<> nvl(p_certification_date,hr_api.g_date))
591 OR
592 (l_api_updating AND (nvl(per_cel_shd.g_old_rec.next_certification_date,
593 hr_api.g_date)<> nvl(p_next_certification_date,hr_api.g_date))
594 OR not l_api_updating))
595 then hr_utility.set_location(l_proc, 6);
596 --
597 -- Raise error if certification date is NULL
598 -- or next certification date occurs before certification date
599 --
600
601 if((p_certification_date is NULL
602 AND p_next_certification_date is NOT NULL)
603 OR
604 (p_certification_date is NOT NULL AND
605 p_next_certification_date <= p_certification_date)) THEN
606 --
607 hr_utility.set_location(l_proc,10);
608 hr_utility.set_message(800,'PER_52861_CHK_NEXT_CERT_DATE');
609 hr_multi_message.add
610 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NEXT_CERTIFICATION_DATE');
611 hr_utility.raise_error;
612 end if;
613 -- Added the code for fix of #731089
614 if p_next_certification_date is not null then
615 if p_next_certification_date < p_effective_date_from THEN
616 hr_utility.set_location(l_proc,12);
617 hr_utility.set_message(800,'PER_289487_CEL_CERT_REV_DATE');
618 hr_multi_message.add
619 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NEXT_CERTIFICATION_DATE');
620 hr_utility.raise_error;
621 end if;
622 end if;
623 -- Added the code for fix of #731089
624
625 end if;
626 --
627 end if; -- end for if checking for no_exclusive_error for CERTIFICATION_DATE
628 --
629 hr_utility.set_location('Leaving:'||l_proc,15);
630 exception
631 when app_exception.application_exception then
632 if hr_multi_message.exception_add
633 (p_associated_column1 =>
634 'PER_COMPETENCE_ELEMENTS.NEXT_CERTIFICATION_DATE'
635 ,p_associated_column2 =>
636 'PER_COMPETENCE_ELEMENTS.CERTIFICATION_DATE'
637 ) then
638 hr_utility.set_location(' Leaving:'||l_proc,20);
639 raise;
640 end if;
641 hr_utility.set_location(' Leaving:'||l_proc,25);
642 end chk_next_certification_date;
643 --
644 --
645 ------------------------------------------------------------------------------
646 -- |--------------------------<CHK_COMPETENCE_TYPE >-----------------------|
647 -----------------------------------------------------------------------------
648 --
649 -- Description;
650 -- Validates that the value entered for competence_type exists
651 -- in HR_LOOKUPS
652 --
653 -- Pre-Conditions:
654 -- None
655 --
656 -- In Arguments:
657 -- p_competence_element_id
658 -- p_effective_date
659 -- p_competence_type
660 -- p_object_version_number
661 --
662 -- Post Success:
663 -- Processing continues if:
664 -- - The competence_type value is valid
665 --
666 -- Post Failure:
667 -- An application error is raised and processing is terminated if any
668 -- - The competence_type value is invalid
669 --
670 -- Access Status:
671 -- Internal Table Handler Use Only.
672 --
673 --
674 --
675 procedure chk_competence_type
676 (p_competence_element_id
677 in per_competence_elements.competence_element_id%TYPE
678 ,p_effective_date in Date
679 ,p_competence_type
680 in per_competence_elements.competence_type%TYPE
681 ,p_object_version_number
682 in per_competence_elements.object_version_number%TYPE
683 ) is
684 --
685 l_proc varchar2(72):= g_package||'chk_competence_type';
686
687 l_api_updating boolean;
688 --
689 begin
690 hr_utility.set_location('Entering:'|| l_proc, 1);
691 --
692 -- Check mandatory parameters have being set.
693 --
694 hr_api.mandatory_arg_error
695 (p_api_name => l_proc
696 ,p_argument => 'effective_date'
697 ,p_argument_value => p_effective_date
698 );
699 --
700 --
701 -- Only proceed with validation if :
702 -- a) The current g_old_rec is current and
703 -- b) The value for competence_type has changed
704 --
705 l_api_updating := per_cel_shd.api_updating
706 (p_competence_element_id => p_competence_element_id
707 ,p_object_version_number => p_object_version_number);
708 --
709 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.competence_type,
710 hr_api.g_varchar2) <>
711 nvl(p_competence_type,hr_api.g_varchar2))
712 OR not l_api_updating ) then
713
714 hr_utility.set_location(l_proc, 6);
715 --
716 -- check that the p_competence_type exists in hr_lookups.
717 --
718 if (p_competence_type IS NOT NULL ) then
719 if hr_api.not_exists_in_hr_lookups
720 (p_effective_date => p_effective_date
721 ,p_lookup_type => 'COMPETENCE_TYPE'
722 ,p_lookup_code => p_competence_type
723 ) then
724 -- Error: Invalid competence_type
725 hr_utility.set_location(l_proc, 10);
726 hr_utility.set_message(801,'HR_51638_CEL_COMP_TYPE_INVL');
727 hr_multi_message.add
728 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_TYPE');
729
730 hr_utility.raise_error;
731 end if;
732 --
733 end if;
734 --
735 end if;
736 --
737 hr_utility.set_location(' Leaving:'|| l_proc, 15);
738 exception
739 when app_exception.application_exception then
740 if hr_multi_message.exception_add
741 (p_associated_column1 =>
742 'PER_COMPETENCE_ELEMENTS.COMPETENCE_TYPE'
743 ) then
744 hr_utility.set_location(' Leaving:'||l_proc,20);
745 raise;
746 end if;
747 hr_utility.set_location(' Leaving:'||l_proc,25);
748 end chk_competence_type;
749 --
750 --
751 ------------------------------------------------------------------------------
752 -- |---------------------<CHK_source_of_proficiency >------------------------|
753 -----------------------------------------------------------------------------
754 --
755 -- Description;
756 -- Validates that the value entered for source_of_proficiency exists
757 -- in HR_LOOKUPS
758 --
759 -- Pre-Conditions:
760 -- None
761 --
762 -- In Arguments:
763 -- p_competence_element_id
764 -- p_effective_date
765 -- p_source_of_proficiency
766 -- p_object_version_number
767 --
768 -- Post Success:
769 -- Processing continues if:
770 -- - The source_of_proficiency value is valid
771 --
772 -- Post Failure:
773 -- An application error is raised and processing is terminated if any
774 -- - The source_of_proficiency value is invalid
775 --
776 -- Access Status:
777 -- Internal Table Handler Use Only.
778 --
779 --
780 --
781 procedure chk_source_of_proficiency
782 (p_competence_element_id
783 in per_competence_elements.competence_element_id%TYPE
784 ,p_effective_date in Date
785 ,p_source_of_proficiency_level
786 in per_competence_elements.source_of_proficiency_level%TYPE
787 ,p_object_version_number
788 in per_competence_elements.object_version_number%TYPE
789 ) is
790 --
791 l_proc varchar2(72):=
792
793 g_package||'chk_source_of_proficiency';
794 l_api_updating boolean;
795 --
796 begin
797 hr_utility.set_location('Entering:'|| l_proc, 1);
798 --
799 -- Check mandatory parameters have being set.
800 --
801 hr_api.mandatory_arg_error
802 (p_api_name => l_proc
803 ,p_argument => 'effective_date'
804 ,p_argument_value => p_effective_date
805 );
806
807 --
808 --
809 -- Only proceed with validation if :
810 -- a) The current g_old_rec is current and
811 -- b) The value for source_of_proficiency has changed
812 --
813 l_api_updating := per_cel_shd.api_updating
814 (p_competence_element_id => p_competence_element_id
815 ,p_object_version_number => p_object_version_number);
816 --
817 if (l_api_updating AND
818 (nvl(per_cel_shd.g_old_rec.source_of_proficiency_level,
819 hr_api.g_varchar2) <>
820
821 nvl(p_source_of_proficiency_level,hr_api.g_varchar2))
822 OR not l_api_updating ) then
823 hr_utility.set_location(l_proc, 6);
824 --
825 -- check that the p_source_of_proficiency exists in hr_lookups.
826 --
827 if (p_source_of_proficiency_level IS NOT NULL ) then
828 if hr_api.not_exists_in_hr_lookups
829 (p_effective_date => p_effective_date
830 ,p_lookup_type => 'PROFICIENCY_SOURCE'
831 ,p_lookup_code => p_source_of_proficiency_level
832 ) then
833 -- Error: Invalid source_of_proficiency
834
835 hr_utility.set_location(l_proc, 10);
836 hr_utility.set_message(801,'HR_51639_CEL_SOURCE_PROF_INVL');
837 hr_multi_message.add
838 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.SOURCE_OF_PROFICIENCY_LEVEL');
839
840 hr_utility.raise_error;
841 end if;
842 --
843 end if;
844 --
845 end if;
846 --
847 hr_utility.set_location(' Leaving:'|| l_proc, 15);
848 exception
849 when app_exception.application_exception then
850 if hr_multi_message.exception_add
851 (p_associated_column1 =>
852 'PER_COMPETENCE_ELEMENTS.SOURCE_OF_PROFICIENCY_LEVEL'
853 ) then
854 hr_utility.set_location(' Leaving:'||l_proc,20);
855 raise;
856 end if;
857 hr_utility.set_location(' Leaving:'||l_proc,25);
858 end chk_source_of_proficiency;
859 --
860 --
861 --
862 ------------------------------------------------------------------------------
863 -- |--------------------------<CHK_TYPE >------------------------------------|
864 -----------------------------------------------------------------------------
865 --
866 -- Description;
867 -- Validates that the value entered for type exists
868 -- in HR_LOOKUPS and type is not updateable.
869 --
870 --
871 -- Pre-Conditions:
872 -- None
873 --
874
875 -- In Arguments:
876 -- p_competence_element_id
877 -- p_effective_date
878 -- p_type
879 -- p_object_version_number
880 --
881 -- Post Success:
882 -- Processing continues if:
883 -- - The type value is valid
884 --
885 -- Post Failure:
886 -- An application error is raised and processing is terminated if any
887 -- - The type value is invalid
888
889 --
890 -- Access Status:
891 -- Internal Table Handler Use Only.
892 --
893 --
894 --
895 procedure chk_type
896 (p_competence_element_id
897 in per_competence_elements.competence_element_id%TYPE
898 ,p_effective_date in Date
899 ,p_type in per_competence_elements.type%TYPE
900 ,p_object_version_number
901 in per_competence_elements.object_version_number%TYPE
902 ) is
903 --
904 l_proc varchar2(72):= g_package||'chk_type';
905 l_api_updating boolean;
906 --
907 begin
908 hr_utility.set_location('Entering:'|| l_proc, 1);
909 --
910 -- Check mandatory parameters have being set.
911 --
912 --
913 hr_api.mandatory_arg_error
914 (p_api_name => l_proc
915 ,p_argument => 'effective_date'
916 ,p_argument_value => p_effective_date
917 );
918 --
919 hr_api.mandatory_arg_error
920 (p_api_name => l_proc
921 ,p_argument => 'type'
922 ,p_argument_value => p_type
923 );
924 --
925 --
926 l_api_updating := per_cel_shd.api_updating
927 (p_competence_element_id => p_competence_element_id
928
929 ,p_object_version_number => p_object_version_number);
930 --
931 hr_utility.set_location(l_proc, 6);
932 --
933 -- check that the p_type exists in hr_lookups.
934 --
935 if (NOT l_api_updating) then
936 if (p_type IS NOT NULL ) then
937 if hr_api.not_exists_in_hr_lookups
938 (p_effective_date => p_effective_date
939 ,p_lookup_type => 'COMPETENCE_ELEMENT_TYPE'
940 ,p_lookup_code => p_type
941 ) then
942
943 -- Error: Invalid competence_type
944 hr_utility.set_location(l_proc, 10);
945 hr_utility.set_message(801,'HR_51641_CEL_COMP_ELTP_INVL');
946 hr_multi_message.add
947 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_TYPE');
948 hr_utility.raise_error;
949
950 end if;
951 --
952 end if;
953 --
954 end if;
955 --
956 hr_utility.set_location(' Leaving:'|| l_proc, 15);
957 exception
958 when app_exception.application_exception then
959 if hr_multi_message.exception_add
960 (p_associated_column1 =>
961 'PER_COMPETENCE_ELEMENTS.TYPE'
962 ) then
963 hr_utility.set_location(' Leaving:'||l_proc,20);
964 raise;
965 end if;
966 hr_utility.set_location(' Leaving:'||l_proc,25);
967 end chk_type;
968
969 -------------------------------------------------------------------------------
970 -----------------------------< chk_foreign_keys >------------------------------
971 -------------------------------------------------------------------------------
972 --
973 --
974 -- Description:
975 -- - Validates that the person_id,activity_version_id
976 -- position_id,organization_id,job_id, valid_grade_id,assessment_id,
977 -- high_proficiency_id,
978 -- competence_id,proficiency_level_id,
979 -- rating_level_id, weighting_level_id, p_parent_competence_element_id,
980 -- corresponding tables.
981 --
982 --
983 -- Pre_conditions:
984 -- A valid business_group_id
985 --
986 -- In Arguments:
987 -- p_competence_element_id
988 -- p_activity_version_id
989 -- p_business_group_id
990 -- p_enterprise_id
991 -- p_person_id
992 -- p_position_id
993 -- p_organization_id
994 -- p_job_id
995 -- p_valid_grade_id
996 -- p_assessment_id
997 -- p_assessment_type_id
998 -- p_competence_id
999 -- p_proficiency_level_id
1000 -- p_high_proficiency_level_id
1001 -- p_rating_level_id
1002 -- p_weighting_level_id
1003 -- p_parent_competence_element_id
1004 -- p_business_group_id
1005 -- p_object_version_number
1006 -- p_party_id -- HR/TCA merge
1007 -- p_qualification_type_id BUG3356369
1008 --
1009 -- Post Success:
1010 -- Process continues if :
1011 -- - The job_id
1012 -- competence_element_id
1013 -- activity_version_id
1014 -- person_id
1015 -- position_id
1016 -- valid_grade_id
1017 -- organization_id
1018 -- assessment_id
1019 -- assessment_type_id
1020 -- competence_id
1021 -- proficiency_level_id
1022 -- high_proficiency_level_id
1023 -- rating_level_id
1024 -- weighting_level_id
1025 -- parent_competence_element_id
1026 -- exits inthe corresponding table and in correct business_group.
1027 --
1028 -- Post Failure:
1029 -- An application error is raised and processing is terminated if any of
1030
1031 -- the following cases are found :
1032 -- - The job_id
1033 -- competence_element_id
1034 -- activity_version_id
1035 -- person_id
1036 -- party_id
1037 -- position_id
1038 -- organization_id
1039 -- assessment_id
1040 -- assessment_type_id
1041 -- competence_id
1042 -- proficiency_level_id
1043 -- high_proficiency_level_id
1044 -- valid_grade_id
1045 -- rating_level_id
1046 -- weighting_level_id
1047 -- parent_competence_element_id
1048 -- not found.
1049 --
1050 -- Access Status:
1051 -- Internal Table Handler Use Only.
1052 --
1053 --
1054 procedure chk_foreign_keys
1055 (p_competence_element_id
1056 in per_competence_elements.competence_element_id%TYPE
1057 ,p_job_id
1058 in per_competence_elements.job_id%TYPE
1059 ,p_valid_grade_id
1060 in per_competence_elements.valid_grade_id%TYPE
1061 ,p_activity_version_id
1062 in per_competence_elements.activity_version_id%TYPE
1063 ,p_person_id
1064 in per_competence_elements.person_id%TYPE
1065 ,p_position_id
1066 in per_competence_elements.position_id%TYPE
1067 ,p_organization_id
1068 in per_competence_elements.organization_id%TYPE
1069 ,p_assessment_id
1070 in per_competence_elements.assessment_id%TYPE
1071 ,p_assessment_type_id
1072 in per_competence_elements.assessment_type_id%TYPE
1073 ,p_competence_id
1074 in per_competence_elements.competence_id%TYPE
1075 ,p_proficiency_level_id
1076 in per_competence_elements.proficiency_level_id%TYPE
1077 ,p_high_proficiency_level_id
1078 in per_competence_elements.high_proficiency_level_id%TYPE
1079 ,p_rating_level_id
1080 in per_competence_elements.rating_level_id%TYPE
1081 ,p_weighting_level_id
1082 in per_competence_elements.weighting_level_id%TYPE
1083 ,p_parent_competence_element_id
1084 in per_competence_elements.parent_competence_element_id%TYPE
1085 ,p_business_group_id
1086 in per_competence_elements.business_group_id%TYPE
1087 ,p_enterprise_id
1088 in per_competence_elements.enterprise_id%TYPE
1089 ,p_object_version_number
1090 in per_competence_elements.object_version_number%TYPE
1091 ,p_effective_date_from
1092 per_competence_elements.effective_date_from%TYPE
1093 ,p_effective_date_to
1094 per_competence_elements.effective_date_to%TYPE
1095 ,p_effective_date date
1096 ,p_type per_competence_elements.type%TYPE
1097 ,p_party_id per_competence_elements.party_id%TYPE -- HR/TCA merge
1098 ,p_qualification_type_id per_competence_elements.qualification_type_id%TYPE
1099 )is
1100 --
1101 l_proc varchar2(72) := g_package||'chk_foreign_keys';
1102 l_business_group_id per_jobs.business_group_id%TYPE;
1103 l_start_date per_jobs.date_from%TYPE;
1104 l_end_date per_jobs.date_to%TYPE;
1105 l_start_date_2 per_jobs.date_from%TYPE;
1106 l_end_date_2 per_jobs.date_to%TYPE;
1107 l_date_of_birth per_people_f.date_of_birth%TYPE;
1108 l_exist varchar2(1);
1109 l_api_updating boolean;
1110 l_party_id per_competence_elements.party_id%TYPE; -- HR/TCA merge
1111 l_qualification_type_id per_competence_elements.qualification_type_id%TYPE;
1112 --
1113 --
1114 -- cursor to check that the job_id exists.
1115 --
1116 cursor csr_valid_job_id is
1117
1118 select business_group_id,date_from,date_to
1119 from per_jobs_v
1120 where p_job_id = job_id;
1121 --and p_effective_date_from >= date_from
1122 --and nvl(p_effective_date_to,hr_api.g_eot)<=
1123 -- nvl(date_to,hr_api.g_eot);
1124 --
1125 -- cursor to check that the valid_grade_id exists.
1126 --
1127 cursor csr_valid_grade_id is
1128
1129 select business_group_id, date_from, date_to
1130 from per_valid_grades
1131 where p_valid_grade_id = valid_grade_id;
1132 --and p_effective_date_from >= date_from
1133 --and nvl(p_effective_date_to,hr_api.g_eot)<=
1134 -- nvl(date_to,hr_api.g_eot);
1135 --
1136 --
1137 --
1138 -- cursor to check that the activity_version_id exists.
1139 --
1140 cursor csr_valid_activity_id is
1141 select actd.business_group_id,
1142 actv.start_date,
1143 nvl(actv.end_date, hr_api.g_eot)
1144 from ota_activity_versions actv,
1145 ota_activity_definitions actd
1146 where p_activity_version_id = actv.activity_version_id
1147 and actv.activity_id = actd.activity_id;
1148 --
1149 --
1150 -- cursor to check that the person_id exists.
1151 -- We are only intereted to see the person exists. We are not restricting
1152 -- it by a date because the person may have had the skill before
1153 -- he joined.
1154 --
1155
1156 cursor csr_valid_person_id is
1157 select business_group_id,effective_start_date, effective_end_date
1158 from per_people_f
1159 where p_person_id = person_id;
1160 --and p_effective_date between
1161 -- effective_start_date and effective_end_date;
1162 --
1163 -- Cursor to check that the start_date is valid
1164 -- (Part of fix for bug 572277)
1165 -- Updated to check against date_of_birth not effective_start_date
1166 -- for bug #794075
1167 --
1168 cursor csr_valid_date_of_birth is
1169 select min(date_of_birth)
1170 from per_people_f
1171 where p_person_id = person_id;
1172 --
1173 -- Cursor to check that the end_date is valid
1174 -- Removed (bug #794075)
1175 --
1176 -- cursor csr_valid_person_end is
1177 -- select max(effective_end_date)
1178 -- from per_people_f
1179 -- where p_person_id = person_id;
1180 --
1181 -- Cursor to check that the position_id exists
1182 --
1183 -- Changed 12-Oct-99 SCNair (per_positions to hr_positions_f) date track position req.
1184 --
1185 cursor csr_valid_position_id is
1186 select business_group_id, date_effective, hr_general.get_position_date_end(p_position_id)
1187 from hr_positions_f
1188 where p_position_id = position_id;
1189 -- and p_effective_date
1190 -- between effective_start_date
1191 -- and effective_end_date;
1192 --and p_effective_date_from >= date_effective
1193 --and nvl(p_effective_date_to,hr_api.g_eot)<=
1194 -- nvl(date_end,hr_api.g_eot);
1195 --
1196 -- Cursor to check that competence_id exists
1197 --
1198 cursor csr_valid_competence_id is
1199 select business_group_id, date_from, date_to
1200 from per_competences
1201 where p_competence_id = competence_id;
1202 -- and nvl(p_effective_date_from,hr_api.g_eot) >= date_from
1203 -- and nvl(p_effective_date_to,hr_api.g_eot)<=
1204 -- nvl(date_to,hr_api.g_eot);
1205 --
1206
1207 --
1208 -- Cursor to check that competence_id when the type is 'COMPETENCE_USAGE'
1209 -- Note; there should not be a date checking for the competence if it
1210 -- is going to be used for COMPETENCE_USAGE, ASSESSMENT,ASSESSMENT_GROUP
1211 -- AND ASSESSMENT_COMPETENCE type.
1212 --
1213 cursor csr_usage_competence_id is
1214 select business_group_id
1215 from per_competences
1216 where p_competence_id = competence_id;
1217
1218 --
1219 -- cursor to check that the organization_id is valid
1220 --
1221 cursor csr_valid_organization_id is
1222 select business_group_id,date_from,date_to
1223 from hr_organization_units
1224 where organization_id = p_organization_id;
1225 -- and p_effective_date_from >= date_from
1226 -- and nvl(p_effective_date_to,hr_api.g_eot)<=
1227 -- nvl(date_to,hr_api.g_eot);
1228 --
1229 -- cursor to check that the assessment_id is valid
1230 --
1231 cursor csr_valid_assessment_id is
1232 select business_group_id
1233 from per_assessments
1234 where p_assessment_id = assessment_id;
1235 --
1236 --
1237 -- cursor to check that the assessment_type_id is valid
1238 --
1239 cursor csr_valid_assessment_type_id is
1240 select business_group_id
1241 from per_assessment_types
1242 where p_assessment_type_id = assessment_type_id;
1243 --
1244 -- cursor to check that the rating_level_id is valid
1245 --
1246 cursor csr_val_prof_id
1247 (c_level_id per_rating_levels.rating_level_id%TYPE) is
1248 select business_group_id
1249 from per_rating_levels
1250 where c_level_id = rating_level_id;
1251
1252 --
1253 -- cursor to check that the parent_comp_element_id is valid
1254 --
1255 cursor csr_valid_comp_id is
1256 select business_group_id
1257 from per_competence_elements
1258 where p_parent_competence_element_id =
1259 competence_element_id;
1260
1261 --
1262 -- cursor to check that the qualification_type_id
1263 --
1264 cursor csr_valid_qualification_type is
1265 select qualification_type_id
1266 from per_qualification_types
1267 where p_qualification_type_id =
1268 qualification_type_id;
1269 --
1270 --
1271 -- This function was included as part of a fix for bug 572277, but
1272 -- checked the comptence dates against the person's effective dates.
1273 -- This doesn't make sense, since a skill could be learnt before
1274 -- the person was an employee. The check has now been limited to
1275 -- the person's date of birth.
1276 --
1277 function invalid_person_dates(p_date_from in date, p_date_to in date) return boolean is
1278 --
1279 l_proc varchar2(72) := g_package||'invalid_person_dates';
1280 --
1281 Begin
1282 --
1283 -- A person cannot have gained a competence before they are born
1284 -- so check that here... (Changes to fix bug #794075)
1285 --
1286 l_date_of_birth := NULL;
1287 open csr_valid_date_of_birth;
1288 fetch csr_valid_date_of_birth into l_date_of_birth;
1289 if p_date_from < nvl(l_date_of_birth, hr_api.g_sot) THEN
1290 close csr_valid_date_of_birth;
1291 return true;
1292 else
1293 close csr_valid_date_of_birth;
1294 return false;
1295 end if;
1296 end invalid_person_dates;
1297
1298
1299 --
1300 begin
1301 hr_utility.set_location('Entering:'|| l_proc, 1);
1302 --
1303 -- Check mandatory parameters have being set.
1304 -- ngundura should check only if object_type is not
1305 -- PROJECT_ROLE AND OPEN_ASSIGNMENT
1306 if per_cel_shd.g_bus_grp then
1307 hr_api.mandatory_arg_error
1308 (p_api_name => l_proc
1309 ,p_argument => 'business_group_id'
1310 ,p_argument_value => p_business_group_id
1311 );
1312 end if;
1313 --
1314 --
1315 -- We only proceed with the checking if it is not updating and
1316 -- the p_person_id is not null.
1317 --
1318 l_api_updating := per_cel_shd.api_updating
1319 (p_competence_element_id => p_competence_element_id
1320 ,p_object_version_number => p_object_version_number);
1321 --
1322 IF ( NOT l_api_updating ) then
1323
1324 hr_utility.set_location(l_proc, 6);
1325 --
1326 -- Check that the person_id exists in the correct BG.
1327 --
1328 if(p_person_id IS NOT NULL) then
1329 --hr_utility.set_location(l_proc, 7);
1330 open csr_valid_person_id;
1331 fetch csr_valid_person_id into l_business_group_id,l_start_date,l_end_date;
1332 if csr_valid_person_id%notfound then
1333 --hr_utility.set_location(l_proc, 10);
1334 close csr_valid_person_id;
1335 --per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK7');
1336 else
1337
1338 close csr_valid_person_id;
1339 if
1340 p_business_group_id <> l_business_group_id then
1341 --
1342 -- The person_id exists in a different_business_group_id.
1343 --
1344 hr_utility.set_location(l_proc, 15);
1345 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1346 elsif invalid_person_dates(p_effective_date_from,p_effective_date_to) then
1347 --
1348 -- The person_id is not date valid
1349 -- Part of fix for bug 572277
1350 --
1351 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK19');
1352 end if;
1353 end if;
1354 --
1355 end if;
1356
1357 -- check that the activity_version_id is valid
1358 --
1359 if(p_activity_version_id IS NOT NULL) then
1360 hr_utility.set_location(l_proc, 40);
1361 open csr_valid_activity_id;
1362 fetch csr_valid_activity_id into l_business_group_id,
1363 l_start_date,
1364 l_end_date;
1365
1366 if csr_valid_activity_id%notfound then
1367 hr_utility.set_location(l_proc, 45);
1368 hr_utility.set_message(801,'HR_51701_CEL_ACTIVE_ID_INVL');
1369 hr_multi_message.add
1370 (p_associated_column1 =>
1371 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1372 );
1373 close csr_valid_activity_id;
1374 else
1375 close csr_valid_activity_id;
1376 if p_business_group_id <> l_business_group_id then
1377 --
1378 -- The activity_version exists in a different_business_group_id.
1379 --
1380 hr_utility.set_location(l_proc, 50);
1381 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1382 --
1383 elsif l_start_date > p_effective_date_from or
1384 l_end_date < nvl(p_effective_date_to, l_end_date) then
1385 --
1386 -- The activity_version is not date valid
1387 --
1388 hr_utility.set_message(810,'OTA_13673_TAV_COMPETENCY_DATES');
1389 hr_multi_message.add
1390 (p_associated_column1 =>
1391 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1392 ,p_associated_column2 =>
1393 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
1394 ,p_associated_column3 =>
1395 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
1396 );
1397 end if;
1398 end if;
1399 --
1400 end if;
1401 --
1402 -- check that the job_id is valid
1403 --
1404 if(p_job_id IS NOT NULL) then
1405 hr_utility.set_location(l_proc, 80);
1406 open csr_valid_job_id;
1407 fetch csr_valid_job_id into l_business_group_id, l_start_date,l_end_date;
1408 if csr_valid_job_id%notfound then
1409 hr_utility.set_location(l_proc, 85);
1410 close csr_valid_job_id;
1411 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK8');
1412 else
1413 close csr_valid_job_id;
1414 if
1415 p_business_group_id <> l_business_group_id then
1416
1417 --
1418 -- The job_id exists in a different_business_group_id.
1419 --
1420 hr_utility.set_location(l_proc, 90);
1421 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1422 elsif (p_effective_date_from < l_start_date) OR
1423 (nvl(p_effective_date_to,hr_api.g_eot) >
1424 nvl(l_end_date,hr_api.g_eot)) THEN
1425 --
1426 -- The job_id is not date valid
1427 -- Part of fix for bug 572277
1428 --
1429 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1430
1431
1432 end if;
1433 end if;
1434 --
1435 end if;
1436 --
1437 -- check that the valid_grade_id is valid
1438 --
1439 if(p_valid_grade_id IS NOT NULL) then
1440 hr_utility.set_location(l_proc, 81);
1441 open csr_valid_grade_id;
1442 fetch csr_valid_grade_id into l_business_group_id,l_start_date,l_end_date;
1443 if csr_valid_grade_id%notfound then
1444 hr_utility.set_location(l_proc, 82);
1445 close csr_valid_grade_id;
1446 hr_utility.set_message(800, 'HR_52372_CEL_INVL_GRD_ID');
1447 hr_utility.set_location(l_proc,83);
1448 hr_multi_message.add
1449 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID'
1450 );
1451 else
1452 close csr_valid_grade_id;
1453 if
1454 p_business_group_id <> l_business_group_id then
1455
1456 --
1457 -- The valid_grade_id exists in a different_business_group_id.
1458 --
1459 hr_utility.set_location(l_proc, 84);
1460 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1461 elsif (p_effective_date_from < l_start_date) OR
1462 (nvl(p_effective_date_to,hr_api.g_eot) >
1463 nvl(l_end_date,hr_api.g_eot)) THEN
1464 --
1465 -- The valid_grade_id is not date valid
1466 -- Part of fix for bug 572277
1467 --
1468 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK22');
1469
1470 end if;
1471 end if;
1472 --
1473 end if;
1474
1475 --
1476 -- check that the position_id is valid
1477 --
1478 if(p_position_id IS NOT NULL) then
1479
1480 hr_utility.set_location(l_proc, 95);
1481 open csr_valid_position_id;
1482 fetch csr_valid_position_id into l_business_group_id,l_start_date,l_end_date;
1483 if csr_valid_position_id%notfound then
1484 hr_utility.set_location(l_proc, 100);
1485 close csr_valid_position_id;
1486 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK9');
1487 else
1488 close csr_valid_position_id;
1489 if
1490 p_business_group_id <> l_business_group_id then
1491 --
1492 -- The position_id exists in a different_business_group_id.
1493 --
1494 hr_utility.set_location(l_proc, 105);
1495 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1496 elsif (p_effective_date_from < l_start_date) OR
1497 (nvl(p_effective_date_to,hr_api.g_eot) >
1498 nvl(l_end_date,hr_api.g_eot)) THEN
1499 --
1500 -- The position_id is not date valid
1501 -- Part of fix for bug 572277
1502 --
1503 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1504
1505 end if;
1506 end if;
1507 --
1508 end if;
1509 --
1510 -- check that the organization_id is valid
1511 --
1512 if(p_organization_id IS NOT NULL) then
1513 hr_utility.set_location(l_proc, 110);
1514 open csr_valid_organization_id;
1515 fetch csr_valid_organization_id into l_business_group_id,l_start_date,l_end_date;
1516 if csr_valid_organization_id%notfound then
1517 hr_utility.set_location(l_proc, 115);
1518 close csr_valid_organization_id;
1519 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK10');
1520 else
1521 close csr_valid_organization_id;
1522 if
1523 p_business_group_id <> l_business_group_id then
1524 --
1525 -- The organization_id exists in a different_business_group_id.
1526 --
1527 hr_utility.set_location(l_proc, 120);
1528
1529 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1530 elsif (p_effective_date_from < l_start_date) OR
1531 (nvl(p_effective_date_to,hr_api.g_eot) >
1532 nvl(l_end_date,hr_api.g_eot)) THEN
1533 --
1534 -- The organization_id is not date valid
1535 -- Part of fix for bug 572277
1536 --
1537 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1538 end if;
1539 --
1540 end if;
1541 --
1542 end if;
1543 --
1544 -- check that the assessment_id is valid
1545 --
1546 if(p_assessment_id IS NOT NULL) then
1547 hr_utility.set_location(l_proc, 125);
1548 open csr_valid_assessment_id;
1549 fetch csr_valid_assessment_id into l_business_group_id;
1550
1551 if csr_valid_assessment_id%notfound then
1552 hr_utility.set_location(l_proc, 130);
1553 close csr_valid_assessment_id;
1554 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK14');
1555 else
1556 close csr_valid_assessment_id;
1557 if
1558 p_business_group_id <> l_business_group_id then
1559 --
1560 -- The assessment_id exists in a different_business_group_id.
1561 --
1562 hr_utility.set_location(l_proc, 135);
1563 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1564
1565 end if;
1566 --
1567 end if;
1568 --
1569 end if;
1570 --
1571 -- check that the assessment_type_id is valid
1572 --
1573 --
1574 if (p_assessment_type_id IS NOT NULL) then
1575 hr_utility.set_location(l_proc, 151);
1576 open csr_valid_assessment_type_id;
1577 fetch csr_valid_assessment_type_id into l_business_group_id;
1578 if csr_valid_assessment_type_id%notfound then
1579
1580 hr_utility.set_location(l_proc, 152);
1581 close csr_valid_assessment_type_id;
1582 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK15');
1583 else
1584 close csr_valid_assessment_type_id;
1585 if
1586 p_business_group_id <> l_business_group_id then
1587 --
1588 -- The assessment_type_id exists in a different_business_group_id.
1589 --
1590 hr_utility.set_location(l_proc, 153);
1591 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1592 end if;
1593
1594 --
1595 end if;
1596 end if;
1597 --
1598 -- check that the parent_comp_elements_id is valid
1599 --
1600 if(p_parent_competence_element_id IS NOT NULL) then
1601 hr_utility.set_location(l_proc, 155);
1602 open csr_valid_comp_id;
1603 fetch csr_valid_comp_id into l_business_group_id;
1604 if csr_valid_comp_id%notfound then
1605 hr_utility.set_location(l_proc, 160);
1606 close csr_valid_comp_id;
1607
1608 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK18');
1609 else
1610 close csr_valid_comp_id;
1611 if
1612 p_business_group_id <> l_business_group_id then
1613 --
1614 -- The parent_comp_element_id exists in
1615 -- a different_business_group_id.
1616 --
1617 hr_utility.set_location(l_proc, 165);
1618 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1619 end if;
1620 --
1621
1622 end if;
1623 --
1624 end if;
1625 --
1626 --
1627 -- check that the high_proficiency_level_id is valid
1628 --
1629 if(p_high_proficiency_level_id IS NOT NULL) then
1630 hr_utility.set_location(l_proc, 205);
1631 open csr_val_prof_id(p_high_proficiency_level_id);
1632 fetch csr_val_prof_id into l_business_group_id;
1633 if csr_val_prof_id%notfound then
1634 hr_utility.set_location(l_proc, 210);
1635 close csr_val_prof_id;
1636 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK6');
1637 else
1638
1639 close csr_val_prof_id;
1640 if
1641 p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1642 --
1643 -- The high_proficiency_level_id exists in a
1644 -- different_business_group_id.
1645 --
1646 hr_utility.set_location(l_proc, 215);
1647 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1648 end if;
1649 end if;
1650 --
1651 end if;
1652
1653 --
1654 -- check that the proficiency_level_id is valid
1655 --
1656 if(p_proficiency_level_id IS NOT NULL) then
1657 hr_utility.set_location(l_proc, 220);
1658 open csr_val_prof_id(p_proficiency_level_id);
1659 hr_utility.set_location(l_proc, 221);
1660 fetch csr_val_prof_id into l_business_group_id;
1661 if csr_val_prof_id%notfound then
1662 hr_utility.set_location(l_proc, 225);
1663 close csr_val_prof_id;
1664 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK5');
1665 else
1666
1667 hr_utility.set_location(l_proc, 226);
1668 close csr_val_prof_id;
1669 hr_utility.set_location(l_proc, 227);
1670 -- ngundura changes done for pa requirements
1671 if
1672 p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1673 --
1674 -- The proficiency_level_id exists
1675 -- in a different_business_group_id.
1676 --
1677 hr_utility.set_location(l_proc, 230);
1678 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1679 end if;
1680 end if;
1681
1682 --
1683 end if;
1684 --
1685 -- check that the weighting_level_id is valid
1686 --
1687 if(p_weighting_level_id IS NOT NULL) then
1688 hr_utility.set_location(l_proc, 235);
1689 open csr_val_prof_id(p_weighting_level_id);
1690 fetch csr_val_prof_id into l_business_group_id;
1691 if csr_val_prof_id%notfound then
1692 hr_utility.set_location(l_proc, 240);
1693 close csr_val_prof_id;
1694 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK17');
1695
1696 else
1697 close csr_val_prof_id;
1698 if
1699 p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1700 --
1701 -- The weighting_level_id exists in
1702 -- a different_business_group_id.
1703 --
1704 hr_utility.set_location(l_proc, 245);
1705 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1706 end if;
1707 end if;
1708 --
1709
1710 end if;
1711 --
1712 -- check that the rating_level_id is valid
1713 --
1714 if(p_rating_level_id IS NOT NULL) then
1715 hr_utility.set_location(l_proc, 250);
1716 open csr_val_prof_id(p_rating_level_id);
1717 fetch csr_val_prof_id into l_business_group_id;
1718 if csr_val_prof_id%notfound then
1719 hr_utility.set_location(l_proc, 255);
1720 close csr_val_prof_id;
1721 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK16');
1722 else
1723
1724 close csr_val_prof_id;
1725 -- ngundura changes done for pa requirements
1726 if
1727 p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1728 --
1729 -- The rating_level_id exists in a different_business_group_id.
1730 --
1731 hr_utility.set_location(l_proc, 260);
1732 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1733 end if;
1734 end if;
1735 --
1736 end if;
1737 --
1738
1739 -- check that the competence_id is valid
1740 --
1741 if(p_competence_id IS NOT NULL) then
1742 hr_utility.set_location(l_proc, 265);
1743 if p_type = 'COMPETENCE_USAGE' OR p_type = 'ASSESSMENT_GROUP' OR
1744 p_type = 'ASSESSMENT' OR p_type= 'ASSESSMENT_COMPETENCE' then
1745 open csr_usage_competence_id;
1746 fetch csr_usage_competence_id into l_business_group_id;
1747 if csr_usage_competence_id%notfound then
1748 close csr_usage_competence_id;
1749 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK1');
1750 else
1751 close csr_usage_competence_id;
1752 -- ngundura changes done for pa requirements
1753 -- added the AND condition in the following if
1754 if
1755 p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1756 --
1757 -- The competence_id exists in a different_business_group_id.
1758 --
1759 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1760 end if;
1761 end if;
1762 else
1763 open csr_valid_competence_id;
1764 hr_utility.set_location(l_proc, 266);
1765 fetch csr_valid_competence_id into l_business_group_id,l_start_date,l_end_date;
1766 hr_utility.set_location(l_proc, 267);
1767 if csr_valid_competence_id%notfound then
1768 hr_utility.set_location(l_proc, 270);
1769 close csr_valid_competence_id;
1770 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK1');
1771 else
1772 hr_utility.set_location(l_proc, 271);
1773 close csr_valid_competence_id;
1774 -- ngundura changes done for pa requirement
1775 -- added the AND condition in the following if
1776 if
1777 p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1778 --
1779 -- The competence_id exists in a different_business_group_id.
1780 --
1781 hr_utility.set_location(l_proc, 275);
1782 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1783 elsif (nvl(p_effective_date_from,hr_api.g_eot) <
1784 nvl(l_start_date,hr_api.g_eot)) OR
1785 (nvl(p_effective_date_to,nvl(l_end_date,hr_api.g_eot)) > nvl(l_end_date,hr_api.g_eot)) THEN
1786 --
1787 -- The competence_id is not date valid
1788 -- Part of fix for bug 572277
1789 --
1790 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK21');
1791
1792 end if;
1793 end if;
1794 --
1795 end if;
1796 end if;
1797 --
1798 elsif (l_api_updating and (nvl(per_cel_shd.g_old_rec.high_proficiency_level_id,
1799 hr_api.g_number)
1800 <> nvl(p_high_proficiency_level_id,hr_api.g_number) OR
1801 nvl(per_cel_shd.g_old_rec.proficiency_level_id, hr_api.g_number)
1802 <> nvl(p_proficiency_level_id,hr_api.g_number))) then
1803 --
1804 -- check that the high_proficiency_level_id is valid
1805 --
1806 if(p_high_proficiency_level_id IS NOT NULL) then
1807 hr_utility.set_location(l_proc, 280);
1808
1809 open csr_val_prof_id(p_high_proficiency_level_id);
1810 fetch csr_val_prof_id into l_business_group_id;
1811 if csr_val_prof_id%notfound then
1812 hr_utility.set_location(l_proc, 285);
1813 close csr_val_prof_id;
1814 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK6');
1815 else
1816 close csr_val_prof_id;
1817 -- ngundura changes for pa requirements
1818 if
1819 p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1820 --
1821 -- The high_proficiency_level_id exists in a
1822 -- different_business_group_id.
1823
1824 --
1825 hr_utility.set_location(l_proc, 290);
1826 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1827 end if;
1828 end if;
1829 --
1830 end if;
1831 --
1832 -- check that the proficiency_level_id is valid
1833 --
1834 if(p_proficiency_level_id IS NOT NULL) then
1835 hr_utility.set_location(l_proc, 295);
1836 open csr_val_prof_id(p_proficiency_level_id);
1837
1838 fetch csr_val_prof_id into l_business_group_id;
1839 if csr_val_prof_id%notfound then
1840 hr_utility.set_location(l_proc, 300);
1841 close csr_val_prof_id;
1842 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK5');
1843 else
1844 close csr_val_prof_id;
1845 -- ngundura changes for pa requirements
1846 -- added the AND condition in the following if
1847 if
1848 p_business_group_id <> l_business_group_id and l_business_group_id is not null then
1849 --
1850 -- The proficiency_level_id exists in a
1851 -- different_business_group_id.
1852 --
1853
1854 hr_utility.set_location(l_proc, 305);
1855 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK2');
1856 end if;
1857 end if;
1858 --
1859 end if;
1860 --
1861 -- Check the enterprise_id is valid
1862 --
1863 --
1864 if(p_enterprise_id is not null ) then
1865 hr_utility.set_location(l_proc, 310);
1866 if (p_enterprise_id <> p_business_group_id) then
1867 hr_utility.set_message(800,'HR_52252_CEL_ENTPISE_ID_INVL');
1868 hr_utility.set_location(l_proc,315);
1869
1870 end if;
1871 end if;
1872 elsif l_api_updating then
1873 --
1874
1875 if(p_person_id IS NOT NULL) then
1876 --hr_utility.set_location(l_proc, 7);
1877 open csr_valid_person_id;
1878 fetch csr_valid_person_id into l_business_group_id,l_start_date,l_end_date;
1879 close csr_valid_person_id;
1880 if invalid_person_dates(p_effective_date_from,p_effective_date_to) then
1881 --
1882 -- The person_id is not date valid
1883 -- Part of fix for bug 572277
1884 --
1885 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK19');
1886 end if;
1887 --
1888 end if;
1889 --
1890 -- check that the activity_version_id is valid
1891 --
1892 if(p_activity_version_id IS NOT NULL) then
1893 hr_utility.set_location(l_proc, 330);
1894 open csr_valid_activity_id;
1895 fetch csr_valid_activity_id into l_business_group_id,
1896 l_start_date,
1897 l_end_date;
1898 close csr_valid_activity_id;
1899 if l_start_date > p_effective_date_from or
1900 l_end_date < nvl(p_effective_date_to, l_end_date) THEN
1901 --
1902 -- The activity_version is not date valid
1903 --
1904 hr_utility.set_message(810,'OTA_13673_TAV_COMPETENCY_DATES');
1905 hr_multi_message.add
1906 (p_associated_column1 =>
1907 'PER_COMPETENCE_ELEMENTS.ACTIVITY_VERSION_ID'
1908 ,p_associated_column2 =>
1909 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
1910 ,p_associated_column3 =>
1911 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
1912 );
1913 end if;
1914 --
1915 end if;
1916 --
1917 -- check that the job_id is valid
1918 --
1919 if(p_job_id IS NOT NULL) then
1920 hr_utility.set_location(l_proc, 340);
1921 open csr_valid_job_id;
1922 fetch csr_valid_job_id into l_business_group_id, l_start_date,l_end_date;
1923 close csr_valid_job_id;
1924 if (p_effective_date_from < l_start_date) OR
1925 (nvl(p_effective_date_to,hr_api.g_eot) >
1926 nvl(l_end_date,hr_api.g_eot)) THEN
1927 --
1928 -- The job_id is not date valid
1929 -- Part of fix for bug 572277
1930 --
1931 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1932 end if;
1933 --
1934 end if;
1935 --
1936 -- check that the valid_grade_id is valid
1937 --
1938 if(p_valid_grade_id IS NOT NULL) then
1939 hr_utility.set_location(l_proc, 350);
1940 open csr_valid_grade_id;
1941 fetch csr_valid_grade_id into l_business_group_id,l_start_date,l_end_date;
1942 close csr_valid_grade_id;
1943 if (p_effective_date_from < l_start_date) OR
1944 (nvl(p_effective_date_to,hr_api.g_eot) >
1945 nvl(l_end_date,hr_api.g_eot)) THEN
1946 --
1947 -- The valid_grade_id is not date valid
1948 -- Part of fix for bug 572277
1949 --
1950 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK22');
1951
1952 end if;
1953 --
1954 end if;
1955
1956 --
1957 -- check that the position_id is valid
1958 --
1959 if(p_position_id IS NOT NULL) then
1960
1961 hr_utility.set_location(l_proc, 360);
1962 open csr_valid_position_id;
1963 fetch csr_valid_position_id into l_business_group_id,l_start_date,l_end_date;
1964 close csr_valid_position_id;
1965 if (p_effective_date_from < l_start_date) OR
1966 (nvl(p_effective_date_to,hr_api.g_eot) >
1967 nvl(l_end_date,hr_api.g_eot)) THEN
1968 --
1969 -- The position_id is not date valid
1970 -- Part of fix for bug 572277
1971 --
1972 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1973
1974 end if;
1975 --
1976 end if;
1977 --
1978 -- check that the organization_id is valid
1979 --
1980 if(p_organization_id IS NOT NULL) then
1981 hr_utility.set_location(l_proc, 370);
1982 open csr_valid_organization_id;
1983 fetch csr_valid_organization_id into l_business_group_id,l_start_date,l_end_date;
1984 close csr_valid_organization_id;
1985 if (p_effective_date_from < l_start_date) OR
1986 (nvl(p_effective_date_to,hr_api.g_eot) >
1987 nvl(l_end_date,hr_api.g_eot)) THEN
1988 --
1989 -- The organization_id is not date valid
1990 -- Part of fix for bug 572277
1991 --
1992 per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK20');
1993 end if;
1994 --
1995 end if;
1996 --
1997 -- check that the competence_id is valid
1998 --
1999 if(p_competence_id IS NOT NULL) then
2000 hr_utility.set_location(l_proc, 380);
2001 open csr_valid_competence_id;
2002 fetch csr_valid_competence_id into l_business_group_id,l_start_date,l_end_date;
2003 close csr_valid_competence_id;
2004 if (nvl(p_effective_date_from,hr_api.g_eot) <
2005 nvl(l_start_date,hr_api.g_eot)) OR
2006 (nvl(p_effective_date_to,nvl(l_end_date,hr_api.g_eot)) >
2007 nvl(l_end_date,hr_api.g_eot)) THEN
2008 --
2009 -- The competence_id is not date valid
2010 -- Part of fix for bug 572277
2011 --
2012 Per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK21');
2013
2014 end if;
2015 --
2016 end if;
2017 --
2018 -- check that the qualification_type_id is valid
2019 --
2020 if(p_qualification_type_id IS NOT NULL) then
2021 hr_utility.set_location(l_proc, 390);
2022
2023 open csr_valid_qualification_type;
2024 fetch csr_valid_qualification_type into l_qualification_type_id;
2025
2026 if csr_valid_qualification_type%NOTFOUND then
2027 close csr_valid_qualification_type;
2028 Per_cel_shd.constraint_error('PER_COMPETENCE_ELEMENTS_FK23');
2029 else
2030 close csr_valid_qualification_type;
2031 end if;
2032 --
2033 end if;
2034 end if;
2035 --
2036 hr_utility.set_location('Leaving: '||l_proc, 400);
2037 --
2038
2039 end chk_foreign_keys;
2040 --
2041 --
2042 -- ----------------------------------------------------------------------------
2043 -- |--------------------------< chk_party_id >--------------------------------|
2044 -- ----------------------------------------------------------------------------
2045 --
2046 -- Description:
2047 -- - Validates that the person_id and the party_id are matched in
2048 -- per_all_people_f
2049 -- and if person_id is not null and party_id is null, derive party_id
2050 -- from per_all_people_f from person_id
2051 --
2052 -- Pre_conditions:
2053 -- A valid business_group_id
2054 --
2055 -- In Arguments:
2056 -- A Pl/Sql record structre.
2057 -- effective_date
2058
2059 --
2060 -- Post Success:
2061 -- Process continues if :
2062 --
2063 -- Post Failure:
2064 -- An application error is raised and processing is terminated if any of
2065
2066 -- Access Status:
2067 -- Internal Table Handler Use Only.
2068 --
2069 --
2070 Procedure chk_party_id(
2071 p_rec in out nocopy per_cel_shd.g_rec_type
2072 ,p_effective_date in date
2073 )is
2074 --
2075 l_proc varchar2(72) := g_package||'chk_party_id';
2076 l_party_id per_competence_elements.party_id%TYPE;
2077 l_party_id2 per_competence_elements.party_id%TYPE;
2078 --
2079 --
2080 -- cursor to check that the party_id maches person_id
2081 --
2082 cursor csr_get_party_id is
2083 select party_id
2084 from per_all_people_f per
2085 where per.person_id = p_rec.person_id
2086 and p_effective_date
2087 between per.effective_start_date
2088 and nvl(per.effective_end_date,hr_api.g_eot);
2089 --
2090 cursor csr_valid_party_id is
2091 select party_id
2092 from hz_parties hzp
2093 where hzp.party_id = p_rec.party_id;
2094 --
2095 begin
2096 hr_utility.set_location('Entering:'|| l_proc, 1);
2097 --
2098 --
2099 if p_rec.person_id is not null then
2100 --
2101 open csr_get_party_id;
2102 fetch csr_get_party_id into l_party_id;
2103 close csr_get_party_id;
2104 hr_utility.set_location(l_proc,20);
2105 if p_rec.party_id is not null then
2106 if p_rec.party_id <> nvl(l_party_id,-1) then
2107 hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
2108 hr_utility.set_location(l_proc,30);
2109 hr_multi_message.add
2110 (p_associated_column1 =>
2111 'PER_COMPETENCE_ELEMENTS.PARTY_ID'
2112 ,p_associated_column2 =>
2113 'PER_COMPETENCE_ELEMENTS.PERSON_ID'
2114 );
2115 end if;
2116 --
2117 else -- if party_id is null
2118 --
2119 -- derive party_id from per_all_people_f using person_id
2120 --
2121 hr_utility.set_location(l_proc,50);
2122 p_rec.party_id := l_party_id;
2123 end if;
2124 --
2125 else -- if person_id is null
2126 --
2127 if p_rec.party_id is not null then
2128 open csr_valid_party_id;
2129 fetch csr_valid_party_id into l_party_id2;
2130 if csr_valid_party_id%notfound then
2131 close csr_valid_party_id;
2132 hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
2133 hr_utility.set_location(l_proc,70);
2134 hr_multi_message.add
2135 (p_associated_column1 =>
2136 'PER_COMPETENCE_ELEMENTS.PARTY_ID'
2137 );
2138 else
2139 --
2140 close csr_valid_party_id;
2141 --
2142 end if;
2143 --
2144 end if; -- end if for if party is not null.
2145 --
2146 end if; -- end if for if person_id is null.
2147 --
2148 hr_utility.set_location(' Leaving:'||l_proc,100);
2149 End chk_party_id;
2150 --
2151 --
2152 ------------------------------------------------------------------------------
2153 --|--------------------------< Chk_proficiency_level_id >--------------------|
2154 ------------------------------------------------------------------------------
2155 --
2156 -- Description:
2157 -- It checks that if the parent of the proficiency_level is the rating
2158 -- scale,then the rating_sacle should be for the same competence which is
2159 -- referenced in the competence_element.
2160 -- It checks that if the parent of the proficiency_level is the competence,
2161 -- then the competence should be the same as the one which is referenced
2162 -- in competence_element.
2163 -- If the high and low proficiency levels are not null then the high proficiency
2164 -- level should be greater or equal to the low_proficiency level.
2165 --
2166 -- Pre-Condition
2167 -- None
2168 --
2169 -- In Arguments:
2170 -- p_competence_element_id
2171 -- p_object_version_number
2172 -- p_proficiency_level_id
2173 -- p_high_proficiency_level_id
2174 -- p_competence_id
2175 --
2176 -- Post Success:
2177 -- Processing continues if:
2178 -- - The proficiency or high proficiency value is valid
2179 --
2180 -- Post Failure:
2181 -- An application error is raised and processing is terminated if any
2182 -- - The proficiency or high proficiency value is invalid
2183 --
2184 -- Access Status:
2185 -- Internal Table Handler Use Only.
2186 --
2187 --
2188 procedure chk_proficiency_level_id
2189 (p_competence_element_id
2190 in per_competence_elements.competence_element_id%TYPE
2191 ,p_business_group_id
2192 in per_competence_elements.business_group_id%TYPE
2193 ,p_object_version_number
2194 in per_competence_elements.object_version_number%TYPE
2195 ,p_proficiency_level_id
2196 in per_competence_elements.proficiency_level_id%TYPE
2197 ,p_high_proficiency_level_id
2198 in per_competence_elements.high_proficiency_level_id%TYPE
2199 ,p_competence_id
2200 in per_competence_elements.competence_id%TYPE
2201 ,p_party_id
2202 in per_competence_elements.party_id%TYPE
2203 ) is
2204 --
2205 l_proc varchar2(72):=
2206 g_package||'chk_proficiency_level_id';
2207 l_api_updating boolean;
2208 l_competence_id1 number(9);
2209 l_rating_scale_id1 number(9);
2210 l_competence_id2 number(9);
2211 l_rating_scale_id2 number(9);
2212 l_step_value1 number(15);
2213 l_step_value2 number(15);
2214 l_rating_scale_id number(9);
2215
2216 --
2217 --
2218 procedure validate_comp_levels (
2219 p_prof_id in number,
2220 p_error in varchar2,
2221 p_step_val out nocopy number) is
2222 l_proc varchar2(80):= 'per_cel_bus.validate_comp_levels';
2223 --
2224 -- cursor to check that the rating_scale referenced by the proficiency
2225 -- is the same as the one that the competence in competence_element is
2226 -- referencing.
2227 --
2228 -- Cursor to check that the rating_scale_id is for the same competence
2229 -- which is referenced in the proficiecy_level.
2230 --
2231 cursor csr_get_rating_scale (p_prof_id in NUMBER) is
2232 select step_value
2233 --
2234 -- This is when the parent of the proficiency is the competence.
2235 --
2236 -- ngundura changes for pa requirements
2237
2238 from per_rating_levels
2239 where p_competence_id = competence_id
2240 and p_prof_id = rating_level_id
2241 -- where p_business_group_id = business_group_id + 0
2242 -- and p_competence_id = competence_id
2243 -- and p_prof_id = rating_level_id
2244
2245 union
2246 --
2247 -- This is when the parent of the proficiency level is the rating scale.
2248 --
2249
2250 select ral.step_value
2251 from per_rating_levels ral,
2252 per_rating_scales ras,
2253 per_competences comp
2254 where p_competence_id = comp.competence_id
2255 and ras.rating_scale_id = comp.rating_scale_id
2256 and ral.rating_scale_id = ras.rating_scale_id
2257 and p_prof_id = ral.rating_level_id;
2258
2259 -- where p_business_group_id + 0 = ral.business_group_id
2260 -- and p_business_group_id + 0 = ras.business_group_id
2261 -- and p_business_group_id + 0 = comp.business_group_id
2262 -- and p_competence_id = comp.competence_id
2263 -- and ras.rating_scale_id = comp.rating_scale_id
2264 -- and ral.rating_scale_id = ras.rating_scale_id;
2265 -- ngundura end of changes.
2266 --
2267 begin
2268 open csr_get_rating_scale(p_prof_id);
2269 fetch csr_get_rating_scale into p_step_val;
2270 hr_utility.set_location(l_proc,5);
2271
2272 if csr_get_rating_scale%notfound then
2273 --
2274 -- raise an error. The proficiency_level_id must be invalid
2275 --
2276 close csr_get_rating_scale;
2277 if p_error = 'HIGH_PROF' then
2278 hr_utility.set_message(801,'HR_51616_CEL_HG_PROF_ID_INVL');
2279 hr_utility.set_location(l_proc,15);
2280 hr_multi_message.add
2281 (p_associated_column1 =>
2282 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2283 ,p_associated_column2 =>
2284 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2285 );
2286 else
2287 hr_utility.set_message(801,'HR_51615_CEL_PROF_ID_INVL');
2288 hr_utility.set_location(l_proc,20);
2289 hr_multi_message.add
2290 (p_associated_column1 =>
2291 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2292 ,p_associated_column2 =>
2293 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2294 );
2295 end if;
2296 --
2297 hr_utility.set_location(l_proc,25);
2298 else
2299 close csr_get_rating_scale;
2300 end if;
2301 hr_utility.set_location('LEAVING: ' ||l_proc,30);
2302 end validate_comp_levels;
2303 --
2304 begin
2305 hr_utility.set_location('Entering:'|| l_proc, 1);
2306 --
2307 --
2308 -- Check mandatory parameters have being set.
2309 -- ngundura check for business group_id only if its business_group specific
2310 if per_cel_shd.g_bus_grp then
2311 hr_api.mandatory_arg_error
2312 (p_api_name => l_proc
2313 ,p_argument => 'business_group_id'
2314 ,p_argument_value => p_business_group_id
2315 );
2316 end if;
2317 --
2318 --
2319 -- Only proceed with validation if :
2320 -- a) The current g_old_rec is current and
2321 -- b) The value for proficiency has changed
2322 --
2323 --
2324 l_api_updating := per_cel_shd.api_updating
2325 (p_competence_element_id => p_competence_element_id
2326 ,p_object_version_number => p_object_version_number);
2327 --
2328 --
2329 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.proficiency_level_id,
2330 hr_api.g_number)<> nvl(p_proficiency_level_id,hr_api.g_number) OR
2331 nvl(per_cel_shd.g_old_rec.high_proficiency_level_id,hr_api.g_number)
2332 <> nvl(p_high_proficiency_level_id,hr_api.g_number))
2333 OR not l_api_updating) then
2334 --
2335 -- do nothing if both high and low proficiencies are null
2336 --
2337 if (p_high_proficiency_level_id is null AND
2338 p_proficiency_level_id is null) then
2339 hr_utility.set_location(l_proc,5);
2340 --
2341 -- raise error if the high_prof is set but not the proficiency_level.
2342 --
2343 elsif (p_proficiency_level_id is null AND
2344 p_high_proficiency_level_id is not null) then
2345 hr_utility.set_location(l_proc,6);
2346 hr_utility.set_message(801,'HR_51726_CEL_PROF_HG_PROF');
2347 hr_multi_message.add
2348 (p_associated_column1 =>
2349 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2350 ,p_associated_column2 =>
2351 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2352 );
2353 else
2354 --
2355 -- validate competence_id against the proficiency_levels
2356 -- the competence_id MUST not be null
2357 --
2358 if p_competence_id is null then
2359 --
2360 -- issue an error message if the competence_id is null
2361 --
2362 hr_utility.set_location(l_proc,10);
2363 hr_utility.set_message(801,'HR_51642_COMP_ID_MANDATORY');
2364 hr_multi_message.add
2365 (p_associated_column1 =>
2366 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2367 );
2368 --
2369 else -- if p_competence_id is not null
2370 --
2371 if p_proficiency_level_id is not null then
2372 validate_comp_levels (p_prof_id => p_proficiency_level_id,
2373 p_error => 'PROF',
2374 p_step_val => l_step_value1);
2375 hr_utility.set_location(l_proc,15);
2376 --
2377 end if;
2378 if p_high_proficiency_level_id is not null then
2379 validate_comp_levels (p_prof_id => p_high_proficiency_level_id,
2380 p_error => 'HIGH_PROF',
2381 p_step_val => l_step_value2);
2382 hr_utility.set_location(l_proc,20);
2383 --
2384 end if;
2385 --
2386 --
2387 -- Validate that if both high and low proficiency level_id are
2388 -- not null. then the step value of the high proficiec is greater
2389 -- or equal to the step value of the proficiency level id.
2390 --
2391 if (p_high_proficiency_level_id is not null and p_proficiency_level_id
2392 is not null) then
2393 if(l_step_value1 > l_step_value2) then
2394 hr_utility.set_location(l_proc,25);
2395 hr_utility.set_message(801,'HR_51644_CEL_PROF_VAL_ERROR');
2396 hr_multi_message.add
2397 (p_associated_column1 =>
2398 'PER_COMPETENCE_ELEMENTS.HIGH_PROFICIENCY_LEVEL_ID'
2399 ,p_associated_column2 =>
2400 'PER_COMPETENCE_ELEMENTS.PROFICIENCY_LEVEL_ID'
2401 );
2402 end if;
2403 end if;
2404 --
2405 end if; -- end if p_competence_id is not null
2406 --
2407 end if;
2408 --
2409 end if; -- end if for api_updating
2410 hr_utility.set_location('Leaving: ' || l_proc, 30);
2411 end chk_proficiency_level_id;
2412 --
2413 --
2414 ------------------------------------------------------------------------------
2415 --|--------------------------< Chk_rating_weighting_id >--------------------|
2416 ------------------------------------------------------------------------------
2417 --
2418 -- Description:
2419 -- It checks that if the rating_level_id on the competence_element is not
2420 -- null then it refers to the same rating_scale as the corresponding
2421 -- assessment_type.
2422 --
2423 -- It checks that if the weighting_level_id on the competence_element is not
2424 -- null then it refers to the same weighting_scale as the corresponding
2425 -- assessment_type.
2426 --
2427 -- It checks if either rateing_level_id or weighting level_id are entered then
2428 -- the assemment_id or assessment_type_id is not null.
2429 --
2430 -- Pre-Condition
2431 -- None.
2432 --
2433 -- In Arguments:
2434 -- p_competence_element_id
2435 -- p_object_version_number
2436 -- p_rating_level_id
2437
2438 -- p_weighting_level_id
2439 -- p_assessment_id
2440 -- p_assessment_type_id
2441 --
2442 -- Post Success:
2443 -- Processing continues if:
2444 -- - The rating_level and weighting_level values are valid
2445 --
2446 -- Post Failure:
2447 -- An application error is raised and processing is terminated if any
2448 -- - The rating_level and weighting_level values are invalid
2449 --
2450 -- Access Status:
2451 -- Internal Table Handler Use Only.
2452 --
2453 --
2454 procedure chk_rating_weighting_id
2455 (p_competence_element_id
2456 in per_competence_elements.competence_element_id%TYPE
2457 ,p_business_group_id
2458 in per_competence_elements.business_group_id%TYPE
2459 ,p_object_version_number
2460 in per_competence_elements.object_version_number%TYPE
2461 ,p_rating_level_id
2462 in per_competence_elements.rating_level_id%TYPE
2463 ,p_weighting_level_id
2464 in per_competence_elements.weighting_level_id%TYPE
2465 ,p_assessment_id
2466 in per_competence_elements.assessment_id%TYPE
2467 ,p_type
2468 in per_competence_elements.type%TYPE
2469 ,p_party_id
2470 in per_competence_elements.party_id%TYPE
2471 ) is
2472 --
2473 l_proc varchar2(72):= g_package||'chk_rating_weighting_id';
2474 l_api_updating boolean;
2475 l_rate_weight number(9);
2476 l_perf_weight varchar2(30);
2477 l_assessment_type_id number(9);
2478 l_rating_scale_id number(9);
2479
2480 l_weighting_scale_id number(9);
2481 --
2482 -- cursor to check that the rating_sacle referenced by the rating_level
2483 -- is the same one that the assessment_type in competence_element is
2484 -- referencing.
2485 --
2486 cursor csr_valid_rate_weight_id (c_rate_level_id in number,
2487 c_scale_type in varchar2)is
2488 select ast.rating_scale_id, ast.weighting_scale_id
2489 from per_assessment_types ast,
2490 per_assessments ass,
2491 per_rating_levels ral
2492 where ass.assessment_id = p_assessment_id
2493 and ass.assessment_type_id = ast.assessment_type_id
2494 and decode(c_scale_type, 'PERFORMANCE', ast.rating_scale_id,
2495 ast.weighting_scale_id) = ral.rating_scale_id
2496 and ral.rating_level_id = c_rate_level_id
2497 and nvl(ast.business_group_id +0,p_business_group_id) = p_business_group_id
2498 and nvl(ast.business_group_id,ass.business_group_id+0) = ass.business_group_id+0
2499 and ass.business_group_id+0 = NVL(ral.business_group_id , ass.business_group_id);
2500 --
2501 begin
2502 hr_utility.set_location('Entering:'|| l_proc, 1);
2503 --
2504 --
2505 -- Check mandatory parameters have being set.
2506 -- ngundura check should only be for business group specific
2507 if per_cel_shd.g_bus_grp then
2508 hr_api.mandatory_arg_error
2509 (p_api_name => l_proc
2510 ,p_argument => 'business_group_id'
2511 ,p_argument_value => p_business_group_id
2512 );
2513 end if;
2514 --
2515 if hr_multi_message.no_exclusive_error
2516 ( p_check_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE'
2517 ) then
2518 --
2519 -- Only proceed with validation if :
2520 -- a) The current g_old_rec is current and
2521 -- b) The value for proficiency has changed
2522 --
2523 --
2524 l_api_updating := per_cel_shd.api_updating
2525 (p_competence_element_id => p_competence_element_id
2526 ,p_object_version_number => p_object_version_number);
2527 --
2528 -- only do this procedure if the type is assessment.
2529 --
2530 if p_type = 'ASSESSMENT' then
2531 --
2532 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.rating_level_id,
2533 hr_api.g_number)<> nvl(p_rating_level_id,hr_api.g_number) OR
2534 nvl(per_cel_shd.g_old_rec.weighting_level_id,hr_api.g_number)
2535 <> nvl(p_weighting_level_id,hr_api.g_number))
2536 OR not l_api_updating) then
2537 --
2538 -- do nothing if both rating and weighting are null
2539 --
2540 if (p_rating_level_id is null AND p_weighting_level_id is null) then
2541 hr_utility.set_location(l_proc,5);
2542 else
2543 if (p_assessment_id is null) then
2544 hr_utility.set_location(l_proc,10);
2545 hr_utility.set_message(801,'HR_51645_CEL_ASS_ASS_TP_NULL');
2546 hr_multi_message.add
2547 (p_associated_column1 =>
2548 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2549 ,p_associated_column2 =>
2550 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2551 ,p_associated_column3 =>
2552 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2553 ,p_associated_column4 =>
2554 'PER_COMPETENCE_ELEMENTS.TYPE'
2555 );
2556
2557 end if;
2558 --
2559 -- now the cursor with appropriate assessment_type parameter.
2560 --
2561 if p_rating_level_id is not null then
2562 l_perf_weight := 'PERFORMANCE';
2563 l_rate_weight := p_rating_level_id;
2564 else
2565 l_perf_weight := 'WEIGHTING';
2566 l_rate_weight := p_weighting_level_id;
2567 end if;
2568 --
2569 open csr_valid_rate_weight_id (l_rate_weight,
2570 l_perf_weight);
2571 fetch csr_valid_rate_weight_id into l_rating_scale_id,
2572 l_weighting_scale_id;
2573 if csr_valid_rate_weight_id%notfound then
2574 close csr_valid_rate_weight_id;
2575 hr_utility.set_location(l_proc,15);
2576 hr_utility.set_message(801,'HR_51727_CEL_RATE_ASS_ID_INVL');
2577 --
2578 if p_rating_level_id is not null then
2579 --
2580 hr_multi_message.add
2581 (p_associated_column1 =>
2582 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2583 ,p_associated_column2 =>
2584 'PER_COMPETENCE_ELEMENTS.RATING_LEVEL_ID'
2585 ,p_associated_column3 =>
2586 'PER_COMPETENCE_ELEMENTS.TYPE'
2587 );
2588 else
2589 --
2590 hr_multi_message.add
2591 (p_associated_column1 =>
2592 'PER_COMPETENCE_ELEMENTS.ASSESSMENT_ID'
2593 ,p_associated_column2 =>
2594 'PER_COMPETENCE_ELEMENTS.WEIGHTING_LEVEL_ID'
2595 ,p_associated_column3 =>
2596 'PER_COMPETENCE_ELEMENTS.TYPE'
2597 );
2598 end if;
2599 --
2600 else
2601 close csr_valid_rate_weight_id;
2602 hr_utility.set_location(l_proc,20);
2603 end if; -- end if for csr_valid_rate_weight_id%notfound
2604 end if; -- end if for any of rating_level_id or weighting_level_id is not null.
2605 end if; -- end if for api_updating
2606 end if; -- end if for checking whether TYPE is assessment.
2607 end if; -- end if for no_exclusive_error check for TYPE
2608 hr_utility.set_location('Leaving: ' || l_proc, 30);
2609 end chk_rating_weighting_id;
2610 --
2611 --
2612 ------------------------------------------------------------------------------
2613 --|--------------------------< Chk_competence_element_dates >----------------|
2614 ------------------------------------------------------------------------------
2615 --
2616 -- Description:
2617 -- It checks that the dates for a specifice competence_id are not overlapped
2618 -- for a person_id,Job_id,valid_grade_id,Position_id,organisation_id and
2619 -- then it refers to the same rating_scale as the corresponding
2620 -- assessment_type.
2621 --
2622 -- Pre-Condition
2623 -- None.
2624 --
2625 -- In Arguments:
2626 -- p_competence_element_id
2627 -- p_competence_id
2628 -- p_business_group_id
2629 -- p_object_version_number
2630 -- p_effective_date_from
2631 -- p_effective_date_to
2632 -- p_person_id
2633 -- p_job_id
2634 -- p_valid_grade_id
2635 -- p_position_id
2636 -- p_organization_id
2637 -- p_enterprise_id
2638 --
2639 -- Post Success:
2640 -- Processing continues if:
2641 -- - The p_person_id, p_job_id, p_valid_grade_id,
2642 -- p_position_id,p_organization_id,
2643 -- are valid
2644 --
2645 -- Post Failure:
2646 -- An application error is raised and processing is terminated if any
2647
2648 -- - The p_person_id or p_job_id or p_position_id or p_organization_id or
2649 -- are invalid.
2650 --
2651 -- Access Status:
2652 -- Internal Table Handler Use Only.
2653 --
2654 --
2655 procedure chk_competence_element_dates
2656 (p_competence_element_id
2657 in per_competence_elements.competence_element_id%TYPE
2658 ,p_business_group_id
2659 in per_competence_elements.business_group_id%TYPE
2660 ,p_competence_id
2661 in per_competence_elements.competence_id%TYPE
2662 ,p_object_version_number
2663 in per_competence_elements.object_version_number%TYPE
2664 ,p_person_id
2665 in per_competence_elements.person_id%TYPE
2666 ,p_position_id
2667 in per_competence_elements.position_id%TYPE
2668 ,p_organization_id
2669 in per_competence_elements.organization_id%TYPE
2670 ,p_job_id
2671 in per_competence_elements.job_id%TYPE
2672 ,p_valid_grade_id
2673 in per_competence_elements.valid_grade_id%TYPE
2674 ,p_effective_date_from
2675 in per_competence_elements.effective_date_from%type
2676 ,p_effective_date_to
2677 in per_competence_elements.effective_date_to%TYPE
2678 ,p_enterprise_id
2679 in per_competence_elements.enterprise_id%TYPE
2680 ) is
2681 --
2682 l_proc varchar2(72)
2683 := g_package||'chk_competence_element_dates';
2684 l_api_updating boolean;
2685 l_exists varchar2(1);
2686 --
2687 l_associate_attribute varchar2(50);
2688 --
2689 --
2690 procedure check_all_dates (p_check_type in varchar2,
2691 p_key_id in number,
2692 p_valid_grade_null in varchar2,
2693 p_error_app in number,
2694 p_error in varchar2) is
2695 --
2696 l_proc varchar2(80):= 'check_all_dates';
2697 l_exists varchar2(1);
2698 TYPE csr_check_dates_type is ref cursor;
2699 csr_check_dates csr_check_dates_type;
2700 v_check_type varchar2(20);
2701 begin
2702 --
2703 hr_utility.set_location('Entering:'|| l_proc, 1);
2704 --
2705 if p_check_type = 'GRADE' then
2706 v_check_type := 'VALID_GRADE_ID';
2707 else
2708 v_check_type := p_check_type || '_ID';
2709 end if;
2710
2711 open csr_check_dates for 'select null from per_competence_elements where '
2712 || v_check_type || ' = :p_key_id'
2713 || ' and competence_id = :p_competence_id'
2714 || ' and nvl(business_group_id,-999) = nvl(:p_business_group_id,-999)'
2715 || ' and :p_effective_date_from <= nvl(effective_date_to,:eot1)'
2716 || ' and nvl(:p_effective_date_to,:eot2) >= effective_date_from'
2717 || ' and (competence_element_id <> :p_competence_element_id1 or :p_competence_element_id2 is null)'
2718 || ' and decode(:p_valid_grade_null,''Y'',valid_grade_id,null) is null'
2719 using p_key_id,p_competence_id,p_business_group_id,p_effective_date_from,
2720 hr_api.g_eot,p_effective_date_to, hr_api.g_eot,
2721 p_competence_element_id,p_competence_element_id,p_valid_grade_null;
2722
2723 fetch csr_check_dates into l_exists;
2724 if csr_check_dates%found then
2725 hr_utility.set_location(l_proc,2);
2726 close csr_check_dates;
2727 hr_utility.set_message(p_error_app,p_error);
2728 if p_check_type = 'ENTERPRISE' then
2729 l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.ENTERPRISE_ID';
2730 elsif p_check_type = 'PERSON' then
2731 l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.PERSON_ID';
2732 elsif p_check_type = 'JOB' then
2733 l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.JOB_ID';
2734 elsif p_check_type = 'POSITION' then
2735 l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.POSITION_ID';
2736 elsif p_check_type = 'GRADE' then
2737 l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID';
2738 elsif p_check_type = 'ORGANIZATION' then
2739 l_associate_attribute := 'PER_COMPETENCE_ELEMENTS.ORGANIZATION_ID';
2740 end if;
2741 if p_valid_grade_null is null then
2742 hr_multi_message.add
2743 (
2744 p_associated_column1 => l_associate_attribute
2745 -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2746 ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2747 ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2748 ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2749 );
2750 else
2751 hr_multi_message.add
2752 (
2753 p_associated_column1 => l_associate_attribute
2754 -- p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2755 ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ID'
2756 ,p_associated_column3 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM'
2757 ,p_associated_column4 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_TO'
2758 ,p_associated_column5 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID'
2759 );
2760 end if;
2761 else -- if no records are found in the cursor, close the cursor.
2762 hr_utility.set_location(l_proc,3);
2763 close csr_check_dates;
2764 end if;
2765 hr_utility.set_location('LEAVING: ' || l_proc,4);
2766 --
2767
2768 end check_all_dates;
2769 --
2770 begin
2771 hr_utility.set_location('Entering:'|| l_proc, 1);
2772 --
2773 --
2774 -- Check mandatory parameters have being set.
2775 -- ngundura this check should not be there for global competences
2776 if per_cel_shd.g_bus_grp then
2777 hr_api.mandatory_arg_error
2778 (p_api_name => l_proc
2779 ,p_argument => 'business_group_id'
2780 ,p_argument_value => p_business_group_id
2781 );
2782 end if;
2783 --
2784 -- Only proceed with validation if :
2785 -- a) The current g_old_rec is current and
2786 -- b) The value for proficiency has changed
2787 --
2788 --
2789 l_api_updating := per_cel_shd.api_updating
2790 (p_competence_element_id => p_competence_element_id
2791 ,p_object_version_number => p_object_version_number);
2792 --
2793 --
2794 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.effective_date_from,
2795 hr_api.g_date)<> nvl(p_effective_date_from,hr_api.g_date) OR
2796 nvl(per_cel_shd.g_old_rec.effective_date_to,hr_api.g_date)
2797 <> nvl(p_effective_date_to,hr_api.g_date))
2798 OR not l_api_updating) then
2799 --
2800 -- check that the effective_date_from is before the effective_date_to
2801 --
2802 if (p_effective_date_from > nvl(p_effective_date_to,hr_api.g_eot))
2803 then
2804 hr_utility.set_location(l_proc,10);
2805 hr_utility.set_message(801,'HR_51647_CEL_DATES_INVL');
2806 --
2807 hr_multi_message.add
2808 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM');
2809
2810 elsif (p_enterprise_id is not null) then
2811 --
2812 -- Check that the dates for enterprise_id does not overlap.
2813 --
2814 check_all_dates('ENTERPRISE',p_enterprise_id,null,
2815 801,'HR_52288_CEL_ENT_DATES_OVLAP');
2816 hr_utility.set_location(l_proc,13);
2817 elsif (p_person_id is not null) then
2818 --
2819 -- Check that the dates for person_id does not overlap.
2820 --
2821 check_all_dates('PERSON',p_person_id,null,
2822 801,'HR_51648_CEL_PER_DATES_OVLAP');
2823 hr_utility.set_location(l_proc,15);
2824 elsif (p_valid_grade_id is not null) then
2825 if p_job_id is not null then
2826 --
2827 -- Check that the dates from valid_grade_id do not overlap
2828 --
2829 check_all_dates('GRADE',p_valid_grade_id, null,
2830 800,'HR_52353_CEL_JGD_DATES_OVLAP');
2831 --
2832 -- Also ensure that there isn't a record with just the job or position
2833 -- ID with overlapping dates.
2834 --
2835 check_all_dates('JOB',p_job_id , 'Y',
2836 800,'HR_52355_CEL_JEX_DATES_OVLAP');
2837 --
2838 elsif p_position_id is not null then
2839 --
2840 -- Check that the dates from valid_grade_id do not overlap
2841 --
2842 check_all_dates('GRADE',p_valid_grade_id, null,
2843 800,'HR_52354_CEL_PGD_DATES_OVLAP');
2844 --
2845 -- Also ensure that there isn't a record with just the job or position
2846 -- ID with overlapping dates.
2847 --
2848 check_all_dates('POSITION',p_position_id , 'Y',
2849 800,'HR_52356_CEL_PEX_DATES_OVLAP');
2850 end if;
2851 elsif (p_job_id is not null) then
2852 --
2853 -- Check that the dates for job_id does not overlap.
2854 --
2855 check_all_dates('JOB',p_job_id,null,
2856 801,'HR_51649_CEL_JOB_DATES_OVLAP');
2857 hr_utility.set_location(l_proc,20);
2858 --
2859 elsif (p_position_id is not null) then
2860 --
2861 -- Check that the dates for position_id does not overlap.
2862 --
2863 check_all_dates('POSITION',p_position_id,null,
2864 801,'HR_51650_CEL_POS_DATES_OVLAP');
2865 hr_utility.set_location(l_proc,25);
2866 --
2867 elsif (p_organization_id is not null) then
2868 --
2869 -- Check that the dates for organization_id does not overlap.
2870 --
2871 check_all_dates('ORGANIZATION',p_organization_id,null,
2872 801,'HR_51651_CEL_ORG_DATES_OVLAP');
2873 hr_utility.set_location(l_proc,30);
2874 --
2875 end if;
2876 end if; -- end if for api_updating
2877 hr_utility.set_location('Leaving: ' || l_proc, 40);
2878 end chk_competence_element_dates;
2879 --
2880 ------------------------------------------------------------------------------
2881 --|--------------------------< Chk_normal_elapse_duration >----------------|
2882 ------------------------------------------------------------------------------
2883 --
2884 -- Description:
2885 -- It checks that either both the normal_elapse_duration and normal_duration
2886 -- units are entered or niether of them are.
2887 -- It checks that the normal_elapse_duration_unints exits in HR_LOOKUPS
2888 --
2889 -- Pre-Condition
2890 -- None.
2891 --
2892 -- In Arguments:
2893
2894 -- p_competence_element_id
2895 -- p_object_version_number
2896 -- p_effective_date
2897 -- p_normal_elapse_duration
2898 -- p_normal_elapse_duration_unit
2899 --
2900 --
2901 -- Post Success:
2902 -- Processing continues if:
2903 -- The normal_elapse_duration and normal_duration units are both valid.
2904 --
2905 -- Post Failure:
2906 -- An application error is raised and processing is terminated if any
2907
2908 -- The normal_elapse_duration and normal_duration units are invalid
2909 --
2910 -- Access Status:
2911 -- Internal Table Handler Use Only.
2912 --
2913 --
2914 procedure chk_normal_elapse_duration
2915 (p_competence_element_id
2916 in per_competence_elements.competence_element_id%TYPE
2917 ,p_object_version_number
2918 in per_competence_elements.object_version_number%TYPE
2919 ,p_effective_date
2920 in Date
2921 ,p_normal_elapse_duration
2922 in per_competence_elements.normal_elapse_duration%TYPE
2923 ,p_normal_elapse_duration_unit
2924 in per_competence_elements.normal_elapse_duration_unit%TYPE
2925 ) is
2926 --
2927 l_proc varchar2(72)
2928 := g_package||'chk_normal_elapse_duration';
2929 l_api_updating boolean;
2930 --
2931 begin
2932 hr_utility.set_location('Entering:'|| l_proc, 1);
2933 --
2934
2935 --
2936 -- Check mandatory parameters have being set.
2937 --
2938 hr_api.mandatory_arg_error
2939 (p_api_name => l_proc
2940 ,p_argument => 'effective_date'
2941 ,p_argument_value => p_effective_date
2942 );
2943 --
2944 -- Only proceed with validation if :
2945 -- a) The current g_old_rec is current and
2946 -- b) The value for elapse_duration or unit has changed
2947 --
2948
2949 --
2950 l_api_updating := per_cel_shd.api_updating
2951 (p_competence_element_id => p_competence_element_id
2952 ,p_object_version_number => p_object_version_number);
2953 --
2954 --
2955 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.normal_elapse_duration,
2956 hr_api.g_number)
2957 <> nvl(p_normal_elapse_duration,hr_api.g_number) OR
2958 nvl(per_cel_shd.g_old_rec.normal_elapse_duration_unit,hr_api.g_varchar2)
2959 <> nvl(p_normal_elapse_duration_unit,hr_api.g_varchar2))
2960 OR not l_api_updating) then
2961 if (p_normal_elapse_duration IS NULL AND
2962
2963 p_normal_elapse_duration_unit IS NULL) then
2964 --
2965 -- Do nothing if both are null.
2966 --
2967 hr_utility.set_location(l_proc, 5);
2968 elsif ((p_normal_elapse_duration IS NULL AND
2969 p_normal_elapse_duration_unit IS NOT NULL) OR
2970 (p_normal_elapse_duration IS NOT NULL AND
2971 p_normal_elapse_duration_unit IS NULL)) then
2972 --
2973 -- Raise an error if one is set but not the other
2974 --
2975 hr_utility.set_location(l_proc,10);
2976
2977 hr_utility.set_message(801,'HR_51653_CEL_NOR_ELPS_COMB');
2978 hr_multi_message.add
2979 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NORMAL_ELAPSE_DURATION'
2980 ,p_associated_column2 => 'PER_COMPETENCE_ELEMENTS.NORMAL_ELAPSE_DURATION_UNIT'
2981 );
2982 elsif (p_normal_elapse_duration IS NOT NULL AND
2983 p_normal_elapse_duration_unit IS NOT NULL) then
2984 --
2985 -- Check that the p_normal_elapse_duration_unit exists in
2986 -- hr_lookups.
2987 --
2988 if hr_api.not_exists_in_hr_lookups
2989 (p_effective_date => p_effective_date
2990 ,p_lookup_type => 'ELAPSE_DURATION'
2991 ,p_lookup_code => p_normal_elapse_duration_unit
2992 )then
2993
2994 -- Error: Invalid normal_elapse_duration_unit
2995 hr_utility.set_location(l_proc, 15);
2996 hr_utility.set_message(801,'HR_51654_CEL_NOR_ELPS_INVL');
2997 hr_multi_message.add
2998 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.NORMAL_ELAPSE_DURATION_UNIT'
2999 );
3000 end if;
3001 end if;
3002 end if;
3003 hr_utility.set_location('Leaving: ' || l_proc, 20);
3004 end chk_normal_elapse_duration;
3005 --
3006 --
3007 --
3008 /**********
3009
3010 -----------------------------------------------------------------------------
3011 -- |---------------------<Chk_rating_weighting_ass_type >--------------------|
3012 -----------------------------------------------------------------------------
3013 --
3014 -- Description;
3015 -- Validates that if an assessment type has a performance rating Scale
3016 -- specified then the rating_level_id is not null on the assessment
3017 -- competence element.
3018 -- It checks that if an assessment type has no performance rating Scale
3019 -- then the rating_level_id is null on the assessment competence_element.
3020 --
3021 -- Validates that if an assessment type has a performance weighting Scale
3022 -- specified then the weigthing_level_id is not null on the assessment
3023
3024 -- competence element.
3025 -- It checks that if an assessment type has no performance weighting Scale
3026 -- then the weighting_level_id is null on the assessment competence_element.
3027 --
3028 -- Pre-Conditions:
3029 -- None
3030 --
3031 -- In Arguments:
3032 -- p_competence_element_id
3033 -- p_object_version_number
3034 -- p_business_group_id
3035 -- p_assessment_id
3036 -- p_assessment_type_id
3037
3038 -- p_rating_level_id
3039 --
3040 -- Post Success:
3041 -- Processing continues if:
3042 -- - The rating_level_id is not null when the assessment type
3043 -- has performance specified. The process also succeeds when the
3044 -- rating_level_id is null and assessment_type has no performance
3045 -- specified.
3046 --
3047 -- Post Failure:
3048 -- An application error is raised and processing is terminated if any
3049 -- - The assessment type has a performance rating specified and the
3050 -- competence elem,ent has no rating_level_id specefied and vice versa.
3051
3052 --
3053 -- Access Status:
3054 -- Internal Table Handler Use Only.
3055 --
3056 --
3057 --
3058 --
3059 procedure chk_rating_weighting_ass_type
3060 (p_competence_element_id
3061 in per_competence_elements.competence_element_id%TYPE
3062 ,p_business_group_id
3063 in per_competence_elements.business_group_id%TYPE
3064 ,p_object_version_number
3065
3066 in per_competence_elements.object_version_number%TYPE
3067 ,p_rating_level_id
3068 in per_competence_elements.rating_level_id%TYPE
3069 ,p_weighting_level_id
3070 in per_competence_elements.weighting_level_id%TYPE
3071 ,p_assessment_id
3072 in per_competence_elements.assessment_id%TYPE
3073 ,p_assessment_type_id
3074 in per_competence_elements.assessment_type_id%TYPE
3075 ) is
3076 --
3077 l_proc varchar2(72)
3078 := g_package||'chk_rating_weighting_ass_type';
3079
3080 l_api_updating boolean;
3081 l_assessment_type_id number(9);
3082 l_rating_scale_id number(9);
3083 l_weighting_scale_id number(9);
3084 --
3085 -- cursor to check that the rating_sacle referenced by the rating_level
3086 -- is the same one that the assessment_type in competence_element is
3087 -- referencing.
3088 --
3089 -- ngundura changes done for pa requirements
3090 -- commented the business_group_id check
3091 cursor csr_get_rating_scale_id(c_ass_type_id in number) is
3092 select ast.rating_scale_id, ast.weighting_scale_id
3093 from per_rating_levels ral,
3094 per_assessment_types ast,
3095
3096 per_rating_scales ras
3097 where ast.assessment_type_id = c_ass_type_id
3098 -- and p_business_group_id +0 = ast.business_group_id
3099 -- and p_business_group_id +0 = ral.business_group_id
3100 -- and p_business_group_id +0 = ras.business_group_id
3101 and ral.rating_scale_id = ras.rating_scale_id
3102 and ras.rating_scale_id = ast.rating_scale_id
3103 and ras.type in ( 'PERFORMANCE', 'WEIGHTING');
3104 --
3105 --
3106 -- Cursor to get the assessment_type_id from assessment
3107 -- which is referenced in the proficiecy_level.
3108 --
3109
3110 cursor csr_get_ass_type_id is
3111 select assessment_type_id
3112 from per_assessments
3113 where p_business_group_id + 0 = business_group_id
3114 and p_assessment_id = assessment_id;
3115 --
3116 begin
3117 hr_utility.set_location('Entering:'|| l_proc, 1);
3118 --
3119 --
3120 -- Check mandatory parameters have being set.
3121 -- ngundura this check should not be there for global competence elements
3122 if per_cel_shd.g_bus_grp then
3123 hr_api.mandatory_arg_error
3124 (p_api_name => l_proc
3125 ,p_argument => 'business_group_id'
3126 ,p_argument_value => p_business_group_id
3127 );
3128 end if;
3129 --
3130 --
3131 --
3132 -- Only proceed with validation if :
3133 -- a) The current g_old_rec is current and
3134 -- b) The value for rating_level_id or weighting level_id has changed.
3135 --
3136 --
3137 l_api_updating := per_cel_shd.api_updating
3138
3139 (p_competence_element_id => p_competence_element_id
3140 ,p_object_version_number => p_object_version_number);
3141 --
3142 --
3143 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.rating_level_id,
3144 hr_api.g_number)<> nvl(p_rating_level_id,hr_api.g_number) OR
3145 nvl(per_cel_shd.g_old_rec.weighting_level_id,hr_api.g_number)
3146 <> nvl(p_weighting_level_id,hr_api.g_number))
3147 OR not l_api_updating) then
3148 --
3149 -- Do nothing if the assessment_type and assessment_id are null
3150 --
3151 if (p_assessment_type_id is null and p_assessment_id is null) then
3152
3153 hr_utility.set_location (l_proc,5);
3154 else
3155 if(p_assessment_type_id is null) then
3156 open csr_get_ass_type_id;
3157 fetch csr_get_ass_type_id into l_assessment_type_id;
3158 if csr_get_ass_type_id%notfound then
3159 hr_utility.set_location (l_proc,10);
3160 close csr_get_ass_type_id;
3161 hr_utility.set_message(801,'HR_51748_CEL_ASS_TYPE_ID_INVL');
3162 hr_utility.raise_error;
3163 end if;
3164 close csr_get_ass_type_id;
3165 --
3166
3167 end if;
3168 --
3169 -- get the rating and weighting from the assessment_type
3170 --
3171 open csr_get_rating_scale_id(l_assessment_type_id);
3172 fetch csr_get_rating_scale_id into l_rating_scale_id,
3173 l_weighting_scale_id;
3174 if csr_get_rating_scale_id%notfound then
3175 close csr_get_rating_scale_id;
3176 hr_utility.set_location(l_proc,15);
3177 hr_utility.set_message(801,'HR_51748_CEL_ASS_TYPE_ID_INVL');
3178 hr_utility.raise_error;
3179 else
3180
3181 close csr_get_rating_scale_id;
3182 --
3183 -- Now check if the rating_scale_id or weighting_scale
3184 -- on assessment_type is null then the rating_level_id
3185 -- or weighting_level_id must be null on the competence_element.
3186 -- And vice versa.
3187 --
3188 if((l_rating_scale_id is NULL AND
3189 p_rating_level_id is NOT NULL) OR
3190 (l_rating_scale_id is not NULL AND
3191 p_rating_level_id is NULL) OR
3192 (l_weighting_scale_id is NULL AND
3193 p_weighting_level_id is NOT NULL) OR
3194
3195 (l_weighting_scale_id is NOT NULL AND
3196 p_weighting_level_id is NULL)) then
3197 --
3198 hr_utility.set_location(l_proc,20);
3199 hr_utility.set_message(801,'HR_51646_CEL_RATE_WEG_INVL');
3200 hr_utility.raise_error;
3201 end if;
3202 --
3203 end if;
3204 end if;
3205 end if;
3206 hr_utility.set_location('Leaving: ' || l_proc, 30);
3207 end chk_rating_weighting_ass_type;
3208
3209 --
3210 ********/
3211 -- --------------------------------------------------------------------------
3212 -- |---------------------------< Chk_type_and_validation >-------------------|
3213 -----------------------------------------------------------------------------
3214 -- Description;
3215 -- It validates that the value entered for TYPE exists in HR_LOOKUPS.
3216 -- Depending on which type is entered, it validates which attributes has
3217 -- to be null or not null.
3218 --
3219 -- Pre-Conditions:
3220 -- None
3221 --
3222
3223 -- In Arguments:
3224 -- p_competence_element_id
3225 -- p_object_version_number
3226 -- p_business_group_id
3227 -- p_enterprise_id
3228 -- p_type
3229 -- p_competence_id
3230 -- p_assessment_id
3231 -- p_assessment_type_id
3232 -- p_activity_version_id
3233 -- p_organization_id
3234 -- p_job_id
3235 -- p_valid_grade_id
3236 -- p_position_id
3237 -- p_person_id
3238 -- p_parent_competence_element_id
3239 -- p_group_competence_type
3240 -- p_effective_date_to
3241 -- p_effective_date_from
3242 -- p_proficiency_level_id
3243 -- p_certification_date
3244 -- p_certification_method
3245 -- p_next_certification_date
3246 -- p_mandatory
3247 -- p_normal_elapse_duration
3248 -- p_normal_elapse_duration_unit
3249
3250 -- p_high_proficiency_level_id
3251 -- p_competence_type
3252 -- p_sequence_number
3253 -- p_source_of_proficiency_level
3254 -- p_weighting_level_id
3255 -- p_rating_level_id
3256 -- p_comments
3257 -- p_party_id -- HR/TCA merge
3258 --
3259 -- Post Success:
3260 -- Processing continues if:
3261 -- - The value of the in arguments are null or not null depending
3262 -- on the type. For more details refer to percel.bru document.
3263
3264 --
3265 --
3266 -- Post Failure:
3267 -- An application error is raised and processing is terminated if any
3268 -- - The value of some of the in parameters are not valid.
3269 --
3270 --
3271 -- Access Status:
3272 -- Internal Table Handler Use Only.
3273 --
3274 --
3275 --
3276 procedure chk_type_and_validation
3277 (p_competence_element_id
3278 in per_competence_elements.competence_element_ID%TYPE
3279 ,p_object_version_number
3280 in per_competence_elements.object_version_number%TYPE
3281 ,p_business_group_id
3282 in per_competence_elements.business_group_id%TYPE
3283 ,p_enterprise_id
3284 in per_competence_elements.enterprise_id%TYPE
3285 ,p_type
3286 in per_competence_elements.type%TYPE
3287 ,p_competence_id
3288 in per_competence_elements.competence_id%TYPE
3289 ,p_assessment_id
3290 in per_competence_elements.assessment_id%TYPE
3291 ,p_assessment_type_id
3292 in per_competence_elements.assessment_type_id%TYPE
3293 ,p_activity_version_id
3294 in per_competence_elements.activity_version_id%TYPE
3295 ,p_organization_id
3296 in per_competence_elements.organization_id%TYPE
3297 ,p_job_id
3298 in per_competence_elements.job_id%TYPE
3299 ,p_valid_grade_id
3300 in per_competence_elements.valid_grade_id%TYPE
3301 ,p_position_id
3302 in per_competence_elements.position_id%TYPE
3303 ,p_person_id
3304 in per_competence_elements.person_id%TYPE
3305 ,p_parent_competence_element_id
3306 in per_competence_elements.parent_competence_element_id%TYPE
3307 ,p_group_competence_type
3308 in per_competence_elements.group_competence_type%TYPE
3309 ,p_effective_date_to
3310 in per_competence_elements.effective_date_to%TYPE
3311 ,p_effective_date_from
3312 in per_competence_elements.effective_date_from%TYPE
3313 ,p_proficiency_level_id
3314 in per_competence_elements.proficiency_level_id%TYPE
3315 ,p_certification_date
3316 in per_competence_elements.certification_date%TYPE
3317 ,p_certification_method
3318 in per_competence_elements.certification_method%TYPE
3319 ,p_next_certification_date
3320 in per_competence_elements.next_certification_date%TYPE
3321 ,p_mandatory
3322 in per_competence_elements.mandatory%TYPE
3323 ,p_normal_elapse_duration
3324 in per_competence_elements.normal_elapse_duration%TYPE
3325 ,p_normal_elapse_duration_unit
3326 in per_competence_elements.normal_elapse_duration_unit%TYPE
3327 ,p_high_proficiency_level_id
3328 in per_competence_elements.high_proficiency_level_id%TYPE
3329 ,p_competence_type
3330 in per_competence_elements.competence_type%TYPE
3331 ,p_sequence_number
3332 in per_competence_elements.sequence_number%TYPE
3333 ,p_source_of_proficiency_level
3334 in per_competence_elements.source_of_proficiency_level%TYPE
3335 ,p_weighting_level_id
3336 in per_competence_elements.weighting_level_id%TYPE
3337 ,p_rating_level_id
3338 in per_competence_elements.rating_level_id%TYPE
3339 ,p_line_score
3340 in per_competence_elements.line_score%TYPE
3341 ,p_object_id
3342 in per_competence_elements.object_id%TYPE
3343 ,p_object_name
3344 in per_competence_elements.object_name%TYPE
3345 ,p_party_id -- HR/TCA merge
3346 in per_competence_elements.party_id%TYPE
3347 ,p_qualification_type_id -- BUG3356369
3348 in per_competence_elements.qualification_type_id%TYPE
3349 ) is
3350 --
3351 -- Cursor to check that the parent_competence_element has
3352 -- type 'ASSESSMENT_GROUP'
3353 --
3354 cursor csr_parent_comp_element is
3355 select null
3356 from per_competence_elements
3357 where competence_element_id = p_parent_competence_element_id
3358 and nvl(business_group_id,-1) = nvl(p_business_group_id,-1)
3359 and type = 'ASSESSMENT_GROUP';
3360
3361 --
3362 -- Cursor to check that the combination of competence and
3363 -- qualification_type is unique when thpe is 'QUALIFICATION'
3364 --
3365 cursor csr_comp_qual_link is
3366 select null from per_competence_elements cel
3367 where cel.competence_id = p_competence_id
3368 and cel.qualification_type_id = p_qualification_type_id
3369 and cel.type = 'QUALIFICATION'
3370 and cel.qualification_type_id = p_qualification_type_id
3371 and (p_effective_date_from <= nvl(cel.effective_date_to, hr_api.g_eot)
3372 and NVL(p_effective_date_to, hr_api.g_eot) >= cel.effective_date_from);
3373
3374 cursor csr_upd_comp_qual_link is
3375 select null from per_competence_elements cel
3376 where cel.competence_element_id <> p_competence_element_id
3377 and cel.competence_id = p_competence_id
3378 and cel.qualification_type_id = p_qualification_type_id
3379 and cel.type = 'QUALIFICATION'
3380 and cel.qualification_type_id = p_qualification_type_id
3381 and (p_effective_date_from <= nvl(cel.effective_date_to, hr_api.g_eot)
3382 and NVL(p_effective_date_to, hr_api.g_eot) >= cel.effective_date_from);
3383
3384 --
3385 l_proc varchar2(72)
3386 := g_package||'chk_type_and_validation';
3387 l_api_updating boolean;
3388 l_exists varchar2(1);
3389 --
3390 begin
3391 hr_utility.set_location('Entering:'|| l_proc, 1);
3392 --
3393 if hr_multi_message.no_exclusive_error
3394 (p_check_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE') then
3395 --
3396 -- Check mandatory parameters have being set.
3397 --
3398 hr_api.mandatory_arg_error
3399 (p_api_name => l_proc
3400 ,p_argument => 'type'
3401 ,p_argument_value => p_type
3402 );
3403 -- mandatory parameter
3404 --
3405 -- ngundura should check only when p_type is not
3406 -- 'PROJECT_ROLE' and 'OPEN_ASSIGNMENT'
3407 if per_cel_shd.g_bus_grp then
3408 hr_api.mandatory_arg_error
3409 (p_api_name => l_proc
3410 ,p_argument => 'business_group_id'
3411 ,p_argument_value => p_business_group_id
3412 );
3413 -- if p_object_id is not null or p_object_name is not null then
3414 if p_object_name is not null and p_object_id is null then
3415 hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
3416 hr_utility.set_message_token('API_NAME', l_proc);
3417 hr_utility.set_message_token('ARGUMENT', 'object_id, object_name');
3418 end if;
3419 end if;
3420 --
3421 --
3422 -- Only proceed with validation if :
3423 -- a) The current g_old_rec is current and
3424
3425 -- b) The value for group_competence_type,competence_type,effective_date_from
3426 -- effective_date_to,high_proficiency_level_id,mandatory,normal_elapse
3427 -- ration ,normal_elapse_duration_unit,sequence_number,source_of_prof_level
3428
3429
3430 -- certification_date,certification_method,next_certification_date,
3431 -- proficiency_level_id,line_score
3432 --
3433 -- NOTE: Only updateable parameters are checked
3434 --
3435 --
3436 l_api_updating := per_cel_shd.api_updating
3437 (p_competence_element_id => p_competence_element_id
3438
3439 ,p_object_version_number => p_object_version_number);
3440
3441 --
3442 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.group_competence_type,
3443 hr_api.g_varchar2) <> nvl(p_group_competence_type,hr_api.g_varchar2)
3444 OR nvl(per_cel_shd.g_old_rec.competence_type,hr_api.g_varchar2) <>
3445 nvl(p_competence_type,hr_api.g_varchar2) OR
3446 nvl(per_cel_shd.g_old_rec.effective_date_from,hr_api.g_date) <>
3447 nvl(p_effective_date_from,hr_api.g_date) OR
3448 nvl(per_cel_shd.g_old_rec.effective_date_to,hr_api.g_date) <>
3449 nvl(p_effective_date_to,hr_api.g_date) OR
3450 nvl(per_cel_shd.g_old_rec.high_proficiency_level_id,hr_api.g_number) <>
3451 nvl(p_high_proficiency_level_id,hr_api.g_number) OR
3452 nvl(per_cel_shd.g_old_rec.mandatory,hr_api.g_varchar2) <>
3453 nvl(p_mandatory,hr_api.g_varchar2) OR
3454 nvl(per_cel_shd.g_old_rec.normal_elapse_duration,hr_api.g_number) <>
3455 nvl(p_normal_elapse_duration,hr_api.g_number) OR
3456 nvl(per_cel_shd.g_old_rec.normal_elapse_duration_unit,hr_api.g_varchar2)
3457 <> nvl(p_normal_elapse_duration_unit,hr_api.g_varchar2) OR
3458 nvl(per_cel_shd.g_old_rec.sequence_number,hr_api.g_number) <>
3459 nvl(p_sequence_number,hr_api.g_number) OR
3460 nvl(per_cel_shd.g_old_rec.certification_date,hr_api.g_date) <>
3461 nvl(p_certification_date,hr_api.g_date) OR
3462 nvl(per_cel_shd.g_old_rec.source_of_proficiency_level,
3463 hr_api.g_varchar2) <>
3464 nvl(p_source_of_proficiency_level,hr_api.g_varchar2) OR
3465 nvl(per_cel_shd.g_old_rec.certification_method,hr_api.g_varchar2) <>
3466 nvl(p_certification_method,hr_api.g_varchar2) OR
3467 nvl(per_cel_shd.g_old_rec.next_certification_date,hr_api.g_date) <>
3468 nvl(p_next_certification_date,hr_api.g_date)OR
3469 nvl(per_cel_shd.g_old_rec.proficiency_level_id,hr_api.g_number) <>
3470 nvl(p_proficiency_level_id,hr_api.g_number) OR
3471 nvl(per_cel_shd.g_old_rec.line_score,hr_api.g_number) <>
3472 nvl(p_line_score,hr_api.g_number) OR
3473 nvl(per_cel_shd.g_old_rec.qualification_type_id,hr_api.g_number) <>
3474 nvl(p_qualification_type_id,hr_api.g_number))
3475 OR NOT l_api_updating) then
3476 hr_utility.set_location(l_proc, 6);
3477 --
3478 -- Check the parameters status when the type is 'REQUIREMENT'
3479 --
3480 if p_type = 'REQUIREMENT' then
3481 -- type 'REQUIREMENT and object_name 'VACANCY' won't have org, job etc.
3482 if (p_object_name is not NULL AND
3483 p_object_name <> 'VACANCY') then
3484 if (p_organization_id is NULL AND
3485 p_job_id is null AND p_position_id IS NULL AND
3486 p_enterprise_id is null) then
3487 --
3488 hr_utility.set_location(l_proc, 7);
3489 hr_utility.set_message(801,'HR_51655_CEL_ORG_JOB_POS');
3490
3491 hr_utility.raise_error;
3492 --
3493 elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
3494 AND (p_valid_grade_id is not null) ) then
3495 hr_utility.set_location(l_proc, 8);
3496 hr_utility.set_message(800,'HR_52373_CEL_GRD_ID_MST_NULL');
3497 hr_multi_message.add
3498 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID');
3499
3500 hr_utility.raise_error;
3501 --
3502 elsif ((p_enterprise_id is not null AND(p_organization_id
3503 is not null OR p_job_id is not null OR p_position_id is not null))
3504 OR
3505 (p_organization_id is not null AND(p_enterprise_id is not null
3506 OR p_job_id is not null OR p_position_id is not null))
3507 OR
3508 (p_job_id is not null AND(p_enterprise_id is not null OR
3509 p_organization_id is not null OR p_position_id is not null))
3510 OR
3511 (p_position_id is not null AND(p_enterprise_id is not null OR
3512 p_job_id is not null OR p_organization_id is not null))) then
3513 --
3514 hr_utility.set_location(l_proc, 10);
3515 hr_utility.set_message(801,'HR_51656_CEL_ORG_JOB_POS_ENT');
3516 hr_utility.raise_error;
3517 --
3518 elsif(p_assessment_id is not null
3519 OR p_assessment_type_id is not null
3520 OR p_activity_version_id is not null
3521 OR p_person_id is not null
3522 OR p_parent_competence_element_id is not null
3523 OR p_group_competence_type is not null
3524 OR p_competence_type is not null
3525 OR p_line_score is not null
3526 OR p_sequence_number is not null
3527 OR p_normal_elapse_duration is not null
3528 OR p_normal_elapse_duration_unit is not null
3529 OR p_source_of_proficiency_level is not null
3530 OR p_certification_date is not null
3531 OR p_certification_method is not null
3532 OR p_next_certification_date is not null
3533 OR p_weighting_level_id is not null
3534 OR p_rating_level_id is not null
3535 OR p_effective_date_from is NULL
3536 OR p_competence_id is NULL
3537 OR p_mandatory is null)
3538 then
3539 --
3540 hr_utility.set_location(l_proc, 15);
3541 hr_utility.set_message(801,'HR_51657_CEL_REQ_TYPE_ERROR');
3542 hr_utility.raise_error;
3543 --
3544 end if;
3545 end if;
3546 --
3547 elsif (p_type = 'ASSESSMENT') then
3548 --
3549 if (p_assessment_id is NULL
3550 OR p_competence_id is NULL
3551 OR p_effective_date_from is NULL
3552 OR p_assessment_type_id is not null
3553 OR p_organization_id is not null
3554 OR p_job_id is not null
3555 OR p_valid_grade_id is not null
3556 OR p_enterprise_id is not null
3557 OR p_position_id is not null
3558 OR p_activity_version_id is not null
3559 OR p_person_id is not null
3560 OR p_group_competence_type is not null
3561 OR p_competence_type is not null
3562 OR p_high_proficiency_level_id is not null
3563 OR p_mandatory is not null
3564 OR p_normal_elapse_duration is not null
3565 OR p_normal_elapse_duration_unit is not null
3566 OR p_sequence_number is not null
3567 OR p_source_of_proficiency_level is not null
3568 OR p_certification_date is not null
3569 OR p_certification_method is not null
3570 OR p_next_certification_date is not null
3571 )
3572 then
3573 --
3574 hr_utility.set_location(l_proc, 20);
3575 hr_utility.set_message(801,'HR_51658_CEL_ASS_TYPE_ERROR');
3576 hr_utility.raise_error;
3577 --
3578 end if;
3579 --
3580 elsif (p_type = 'ASSESSMENT_GROUP') then
3581 if (p_assessment_type_id is NULL
3582 OR p_group_competence_type is NULL
3583 OR p_competence_id is not NULL
3584 OR p_parent_competence_element_id is not null
3585 OR p_organization_id is not null
3586 OR p_job_id is not null
3587 OR p_valid_grade_id is not null
3588 OR p_enterprise_id is not null
3589 OR p_position_id is not null
3590 OR p_activity_version_id is not null
3591 OR p_person_id is not null
3592 OR p_effective_date_from is not null
3593 OR p_effective_date_to is not null
3594 OR p_high_proficiency_level_id is not null
3595 OR p_mandatory is not null
3596 OR p_normal_elapse_duration is not null
3597 OR p_normal_elapse_duration_unit is not null
3598 OR p_source_of_proficiency_level is not null
3599 OR p_certification_date is not null
3600 OR p_certification_method is not null
3601 OR p_next_certification_date is not null
3602 OR p_proficiency_level_id is not null
3603 OR p_competence_type is not null
3604 OR p_assessment_id is not null
3605 OR p_weighting_level_id is not null
3606 OR p_rating_level_id is not null
3607 )
3608 then
3609 --
3610 hr_utility.set_location(l_proc, 25);
3611 hr_utility.set_message(801,'HR_51659_CEL_ASS_GRP_ERROR');
3612 hr_utility.raise_error;
3613 --
3614 end if;
3615 elsif (p_type = 'ASSESSMENT_COMPETENCE') then
3616 if(p_parent_competence_element_id IS NOT NULL) then
3617 open csr_parent_comp_element;
3618 fetch csr_parent_comp_element into l_exists;
3619 if csr_parent_comp_element%notfound then
3620 close csr_parent_comp_element;
3621 --
3622 -- raise an error message because the
3623 -- parent_competence_element type is not
3624 -- 'ASSESSMENT_GROUP'
3625 --
3626 hr_utility.set_location(l_proc, 30);
3627 hr_utility.set_message(801,'HR_51660_CEL_ASS_COMP_ERROR');
3628 hr_utility.raise_error;
3629 end if;
3630 close csr_parent_comp_element;
3631 end if;
3632 if ((p_assessment_type_id is null AND p_parent_competence_element_id
3633 is NULL )
3634 OR
3635
3636 (p_assessment_type_id is not null AND
3637 p_parent_competence_element_id is not null )) then
3638 --
3639 hr_utility.set_location(l_proc, 35);
3640 hr_utility.set_message(801,'HR_51662_CEL_ASS_COMP_MUTA');
3641 hr_utility.raise_error;
3642 --
3643 end if;
3644 if(p_assessment_id is not null
3645 OR p_competence_id is NULL
3646 OR p_effective_date_from is not NULL
3647 OR p_effective_date_to is not NULL
3648 OR p_enterprise_id is not null
3649 OR p_organization_id is not null
3650 OR p_job_id is not null
3651 OR p_valid_grade_id is not null
3652 OR p_position_id is not null
3653 OR p_activity_version_id is not null
3654 OR p_person_id is not null
3655 OR p_group_competence_type is not null
3656 OR p_high_proficiency_level_id is not null
3657 OR p_mandatory is not null
3658 OR p_normal_elapse_duration is not null
3659 OR p_normal_elapse_duration_unit is not null
3660 OR p_source_of_proficiency_level is not null
3661 OR p_certification_date is not null
3662 OR p_certification_method is not null
3663 OR p_next_certification_date is not null
3664 OR p_proficiency_level_id is not null
3665 OR p_competence_type is not null
3666 OR p_weighting_level_id is not null
3667 OR p_rating_level_id is not null
3668 )
3669 then
3670 --
3671 hr_utility.set_location(l_proc, 40);
3672 hr_utility.set_message(801,'HR_51663_CEL_ASS_COMP_ERR');
3673 hr_utility.raise_error;
3674
3675 --
3676 end if;
3677 --
3678 elsif (p_type = 'COMPETENCE_USAGE') then
3679 if ( p_competence_type is NULL
3680 OR p_competence_id is NULL
3681 OR p_effective_date_from is not NULL
3682 OR p_effective_date_to is not null
3683 OR p_organization_id is not null
3684 OR p_job_id is not null
3685 OR p_valid_grade_id is not null
3686 OR p_position_id is not null
3687 OR p_enterprise_id is not null
3688 OR p_activity_version_id is not null
3689 OR p_person_id is not null
3690 OR p_parent_competence_element_id is not null
3691 OR p_group_competence_type is not null
3692 OR p_high_proficiency_level_id is not null
3693 OR p_mandatory is not null
3694 OR p_normal_elapse_duration is not null
3695 OR p_normal_elapse_duration_unit is not null
3696 OR p_sequence_number is not null
3697 OR p_source_of_proficiency_level is not null
3698 OR p_certification_date is not null
3699 OR p_certification_method is not null
3700 OR p_next_certification_date is not null
3701 OR p_proficiency_level_id is not null
3702 OR p_assessment_id is not null
3703 OR p_assessment_type_id is not null
3704 OR p_weighting_level_id is not null
3705 OR p_rating_level_id is not null
3706 )
3707 then
3708 --
3709 hr_utility.set_location(l_proc, 45);
3710 hr_utility.set_message(801,'HR_51664_CEL_COMP_USG_ERR');
3711 hr_utility.raise_error;
3712 --
3713 end if;
3714
3715 --
3716 elsif (p_type = 'DELIVERY') then
3717 if (p_activity_version_id is NULL)
3718 then
3719 --
3720 hr_utility.set_location(l_proc, 50);
3721 hr_utility.set_message(801,'HR_51665_CEL_DELVR_ERROR');
3722 hr_utility.raise_error;
3723 --
3724 elsif(p_assessment_id is not null
3725 OR p_assessment_type_id is not null
3726 OR p_organization_id is not null
3727 OR p_job_id is not null
3728 OR p_valid_grade_id is not null
3729 OR p_mandatory is not null
3730 OR p_position_id is not null
3731 OR p_enterprise_id is not null
3732 OR p_person_id is not null
3733 OR p_high_proficiency_level_id is not null
3734 OR p_competence_type is not null
3735 OR p_normal_elapse_duration is not null
3736 OR p_normal_elapse_duration_unit is not null
3737 OR p_sequence_number is not null
3738 OR p_source_of_proficiency_level is not null
3739 OR p_certification_date is not null
3740 OR p_certification_method is not null
3741 OR p_next_certification_date is not null
3742 OR p_weighting_level_id is not null
3743 OR p_rating_level_id is not null
3744 OR p_parent_competence_element_id is not null
3745 OR p_group_competence_type is not null
3746 OR p_competence_id is NULL
3747 OR p_effective_date_from is NULL
3748 )
3749 then
3750 --
3751 hr_utility.set_location(l_proc, 55);
3752 hr_utility.set_message(801,'HR_51666_CEL_DELVR_ERROR');
3753
3754 hr_utility.raise_error;
3755 --
3756 end if;
3757 --
3758 -- Note: The PREREQUISITE type is the same as the DELIVERY
3759 -- But we have included this so that we are able to modify it
3760 -- for the future versions.
3761 --
3762 elsif (p_type = 'PREREQUISITE') then
3763 if ( p_activity_version_id is NULL)
3764 then
3765 --
3766 hr_utility.set_location(l_proc, 60);
3767
3768 hr_utility.set_message(801,'HR_51667_CEL_PRE_REQ_MUTA');
3769 hr_utility.raise_error;
3770 --
3771 elsif(p_assessment_id is not null
3772 OR p_assessment_type_id is not null
3773 OR p_organization_id is not null
3774 OR p_job_id is not null
3775 OR p_valid_grade_id is not null
3776 OR p_position_id is not null
3777 OR p_enterprise_id is not null
3778 OR p_person_id is not null
3779 OR p_high_proficiency_level_id is not null
3780 OR p_competence_type is not null
3781 OR p_normal_elapse_duration is not null
3782 OR p_normal_elapse_duration_unit is not null
3783 OR p_sequence_number is not null
3784 OR p_source_of_proficiency_level is not null
3785 OR p_certification_date is not null
3786 OR p_certification_method is not null
3787 OR p_next_certification_date is not null
3788 OR p_weighting_level_id is not null
3789 OR p_rating_level_id is not null
3790 OR p_parent_competence_element_id is not null
3791 OR p_group_competence_type is not null
3792 OR p_competence_id is NULL
3793 OR p_effective_date_from is NULL
3794 OR p_mandatory is NULL
3795 )
3796 then
3797 --
3798 hr_utility.set_location(l_proc, 65);
3799 hr_utility.set_message(801,'HR_51668_CEL_PRE_REQ_ERR');
3800 hr_utility.raise_error;
3801 --
3802 end if;
3803 --
3804 elsif (p_type = 'PATH') then
3805 if (p_sequence_number is NULL
3806 OR p_competence_id is not null
3807 OR p_competence_type is not null
3808 OR p_assessment_id is not null
3809 OR p_assessment_type_id is not null
3810 OR p_organization_id is not null
3811 OR p_job_id is not null
3812 OR p_valid_grade_id is not null
3813 OR p_position_id is not null
3814 OR p_activity_version_id is not null
3815 OR p_person_id is not null
3816 OR p_high_proficiency_level_id is not null
3817 OR p_proficiency_level_id is not null
3818 OR p_effective_date_from is not null
3819 OR p_effective_date_to is not null
3820 OR p_mandatory is not null
3821 OR p_source_of_proficiency_level is not null
3822 OR p_certification_date is not null
3823 OR p_certification_method is not null
3824 OR p_next_certification_date is not null
3825 OR p_weighting_level_id is not null
3826 OR p_rating_level_id is not null
3827 OR p_parent_competence_element_id is not null
3828 OR p_group_competence_type is not null
3829 )
3830
3831 then
3832 --
3833 hr_utility.set_location(l_proc, 70);
3834 hr_utility.set_message(801,'HR_51669_CEL_PATH_ERROR');
3835 hr_utility.raise_error;
3836 --
3837 end if;
3838 --
3839 elsif (p_type = 'PERSONAL') then
3840 if ((p_person_id is NULL and p_party_id is NULL) -- HR/TCA merge
3841 OR p_competence_id is NULL
3842 OR p_effective_date_from is NULL
3843 OR p_competence_type is not null
3844 OR p_assessment_id is not null
3845 OR p_assessment_type_id is not null
3846 OR p_activity_version_id is not null
3847 OR p_enterprise_id is not null
3848 OR p_organization_id is not null
3849 OR p_job_id is not null
3850 OR p_valid_grade_id is not null
3851 OR p_position_id is not null
3852 OR p_parent_competence_element_id is not null
3853 OR p_group_competence_type is not null
3854 OR p_high_proficiency_level_id is not null
3855 OR p_mandatory is not null
3856 OR p_normal_elapse_duration is not null
3857 OR p_normal_elapse_duration_unit is not null
3858 OR p_sequence_number is not null
3859 OR p_weighting_level_id is not null
3860 OR p_rating_level_id is not null
3861 OR p_competence_type is not null
3862 )
3863 then
3864 --
3865 hr_utility.set_location(l_proc, 75);
3866 hr_utility.set_message(801,'HR_51670_CEL_PER_TYPE_ERROR');
3867 hr_multi_message.add
3868 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM');
3869 hr_utility.raise_error;
3870 --
3871 end if;
3872 --
3873 elsif (p_type = 'PROPOSAL') then
3874 if(p_competence_id is NULL
3875 OR p_effective_date_from is NULL
3876 OR p_mandatory is NULL
3877 OR p_assessment_id is not null
3878 OR p_assessment_type_id is not null
3879 OR p_organization_id is not null
3880 OR p_job_id is not null
3881 OR p_valid_grade_id is not null
3882 OR p_enterprise_id is not null
3883 OR p_activity_version_id is not null
3884 OR p_position_id is not null
3885 OR p_person_id is not null
3886 OR p_parent_competence_element_id is not null
3887 OR p_group_competence_type is not null
3888 OR p_high_proficiency_level_id is not null
3889 OR p_competence_type is not null
3890 OR p_normal_elapse_duration is not null
3891 OR p_normal_elapse_duration_unit is not null
3892 OR p_sequence_number is not null
3893 OR p_source_of_proficiency_level is not null
3894 OR p_weighting_level_id is not null
3895 OR p_rating_level_id is not null
3896
3897 )
3898 then
3899 --
3900 hr_utility.set_location(l_proc, 85);
3901 hr_utility.set_message(801,'HR_51672_CEL_PRO_TYPE_ERROR');
3902 hr_utility.raise_error;
3903 --
3904 end if;
3905 --
3906 elsif (p_type = 'SET') then
3907 if (p_competence_id is NULL
3908 OR p_effective_date_from is not NULL
3909 OR p_assessment_id is not null
3910 OR p_assessment_type_id is not null
3911 OR p_organization_id is not null
3912 OR p_job_id is not null
3913 OR p_valid_grade_id is not null
3914 OR p_position_id is not null
3915 OR p_enterprise_id is not null
3916 OR p_activity_version_id is not null
3917 OR p_person_id is not null
3918 OR p_parent_competence_element_id is not null
3919 OR p_group_competence_type is not null
3920 OR p_high_proficiency_level_id is not null
3921 OR p_proficiency_level_id is not null
3922 OR p_source_of_proficiency_level is not null
3923 OR p_certification_date is not null
3924 OR p_certification_method is not null
3925 OR p_next_certification_date is not null
3926 OR p_mandatory is not null
3927 OR p_competence_type is not null
3928 OR p_normal_elapse_duration is not null
3929 OR p_normal_elapse_duration_unit is not null
3930 OR p_sequence_number is not null
3931 OR p_weighting_level_id is not null
3932 OR p_rating_level_id is not null
3933 )
3934 then
3935
3936 --
3937 hr_utility.set_location(l_proc, 90);
3938 hr_utility.set_message(801,'HR_51673_CEL_SET_TYPE_ERROR');
3939 hr_utility.raise_error;
3940 --
3941 end if;
3942 elsif (p_type = 'QUALIFICATION') then
3943 hr_utility.trace('date_from : ' || p_effective_date_from);
3944 hr_utility.trace('date_to : ' || p_effective_date_to);
3945 hr_utility.set_location(l_proc, 100);
3946
3947 --
3948 -- Mandatory parameter check
3949 --
3950 hr_api.mandatory_arg_error
3951 (p_api_name => l_proc,
3952 p_argument => 'qualification_type_id',
3953 p_argument_value => p_qualification_type_id);
3954
3955 hr_api.mandatory_arg_error
3956 (p_api_name => l_proc,
3957 p_argument => 'effective_date_from',
3958 p_argument_value => p_effective_date_from);
3959 --
3960 if (NOT l_api_updating) then
3961 hr_utility.set_location(l_proc, 103);
3962 --
3963 -- Mandatory parameter check
3964 --
3965 hr_api.mandatory_arg_error
3966 (p_api_name => l_proc,
3967 p_argument => 'competence_id',
3968 p_argument_value => p_competence_id);
3969
3970 open csr_comp_qual_link;
3971 fetch csr_comp_qual_link into l_exists;
3972 if csr_comp_qual_link%FOUND then
3973 close csr_comp_qual_link;
3974 hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3975 hr_utility.raise_error;
3976 end if;
3977 close csr_comp_qual_link;
3978 else
3979 hr_utility.set_location(l_proc, 105);
3980
3981 open csr_upd_comp_qual_link;
3982 fetch csr_upd_comp_qual_link into l_exists;
3983 if csr_upd_comp_qual_link%FOUND then
3984 close csr_upd_comp_qual_link;
3985 hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3986 hr_utility.raise_error;
3987 end if;
3988 close csr_upd_comp_qual_link;
3989 end if;
3990 end if;
3991 hr_utility.set_location('Leaving: ' || l_proc, 110);
3992 end if;
3993 end if; -- check for no_exclusive_error for TYPE.
3994 exception
3995 when app_exception.application_exception then
3996 if hr_multi_message.exception_add
3997 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE'
3998 ) then
3999 hr_utility.set_location(' Leaving:'||l_proc,110);
4000 raise;
4001 end if;
4002 hr_utility.set_location(' Leaving:'||l_proc,120);
4003 end chk_type_and_validation;
4004 --
4005 --
4006 --
4007 -- --------------------------------------------------------------------------
4008 -- |----------------< Chk_unique_competence_element >-----------------------|
4009 -----------------------------------------------------------------------------
4010 -- Description;
4011 -- It validates that the competence element is unique for any of the foreign
4012 -- key relationships.
4013 --
4014 -- Pre-Conditions:
4015 -- None
4016 --
4017 -- In Arguments:
4018 -- p_competence_element_id
4019 -- p_object_version_number
4020 -- p_business_group_id
4021 -- p_enterprise_id
4022 -- p_type
4023 -- p_competence_id
4024 -- p_assessment_id
4025 -- p_assessment_type_id
4026 -- p_activity_version_id
4027 -- p_organization_id
4028 -- p_job_id
4029 -- p_valid_grade_id
4030 -- p_position_id
4031 -- p_person_id
4032 -- p_parent_competence_element_id
4033 -- p_group_competence_type
4034 -- p_effective_date_from
4035 -- p_competence_type
4036 -- p_party_id -- HR/TCA merge
4037 --
4038 -- Post Success:
4039 -- Processing continues if:
4040 -- - The value of the in arguments don't violate the uniqueness
4041 -- test.
4042 --
4043 --
4044 -- Post Failure:
4045 -- An application error is raised and processing is terminated if any
4046
4047 -- - The value entered are not unique.
4048 --
4049 --
4050 -- Access Status:
4051 -- Internal Table Handler Use Only.
4052 --
4053 --
4054 --
4055 --
4056 procedure chk_unique_competence_element
4057 (p_competence_element_id
4058 in per_competence_elements.competence_element_ID%TYPE
4059 ,p_object_version_number
4060 in per_competence_elements.object_version_number%TYPE
4061 ,p_business_group_id
4062 in per_competence_elements.business_group_id%TYPE
4063 ,p_enterprise_id
4064 in per_competence_elements.enterprise_id%TYPE
4065 ,p_type in per_competence_elements.type%TYPE
4066 ,p_competence_id in per_competence_elements.competence_id%TYPE
4067 ,p_assessment_id in per_competence_elements.assessment_id%TYPE
4068 ,p_assessment_type_id
4069 in per_competence_elements.assessment_type_id%TYPE
4070 ,p_activity_version_id
4071 in per_competence_elements.activity_version_id%TYPE
4072 ,p_organization_id
4073 in per_competence_elements.organization_id%TYPE
4074 ,p_job_id in per_competence_elements.job_id%TYPE
4075 ,p_valid_grade_id in per_competence_elements.valid_grade_id%TYPE
4076 ,p_position_id
4077 in per_competence_elements.position_id%TYPE
4078 ,p_person_id in per_competence_elements.person_id%TYPE
4079 ,p_parent_competence_element_id
4080 in per_competence_elements.parent_competence_element_id%TYPE
4081 ,p_group_competence_type
4082 in per_competence_elements.group_competence_type%TYPE
4083 ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4084 ,p_competence_type in per_competence_elements.competence_type%TYPE
4085 ,p_object_name in per_competence_elements.object_name%type
4086 ,p_object_id in per_competence_elements.object_id%type
4087 ,p_party_id in per_competence_elements.party_id%type -- HR/TCA merge
4088 ,p_qualification_type_id in per_competence_elements.qualification_type_id%type
4089 ) is
4090 --
4091 l_proc varchar2(72):=
4092 g_package||'chk_unique_competence_element';
4093 l_sql_stmt VARCHAR2(1500);
4094 l_api_updating boolean;
4095 l_exists varchar2(1);
4096 --
4097 begin
4098 hr_utility.set_location('Entering:'|| l_proc, 1);
4099 if hr_multi_message.no_exclusive_error
4100 (p_check_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE') then
4101 --
4102 -- Check mandatory parameters have being set.
4103 --
4104 hr_api.mandatory_arg_error
4105 (p_api_name => l_proc
4106 ,p_argument => 'type'
4107 ,p_argument_value => p_type
4108 );
4109 -- mandatory parameter
4110 --
4111 -- ngundura this check should not be there for global competence elements
4112 if per_cel_shd.g_bus_grp then
4113 hr_api.mandatory_arg_error
4114 (p_api_name => l_proc
4115 ,p_argument => 'business_group_id'
4116 ,p_argument_value => p_business_group_id
4117 );
4118 end if;
4119 --
4120 --
4121 -- Only proceed with validation if :
4122 -- a) The current g_old_rec is current and
4123 -- b) The value for group_competence_type,competence_type,effective_date_from
4124 -- or any of the above in arguments has changed.
4125 --
4126 --
4127 l_api_updating := per_cel_shd.api_updating
4128 (p_competence_element_id => p_competence_element_id
4129 ,p_object_version_number => p_object_version_number);
4130 --
4131 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.group_competence_type,
4132 hr_api.g_varchar2) <> nvl(p_group_competence_type,hr_api.g_varchar2)
4133 OR nvl(per_cel_shd.g_old_rec.competence_type,hr_api.g_varchar2) <>
4134 nvl(p_competence_type,hr_api.g_varchar2) OR
4135 nvl(per_cel_shd.g_old_rec.effective_date_from,hr_api.g_date) <>
4136 nvl(p_effective_date_from,hr_api.g_date))
4137 OR NOT l_api_updating) then
4138 hr_utility.set_location(l_proc, 6);
4139 --
4140 -- build the NATIVE dynamic SQL
4141 -- note: native dynamic SQL has been used for performance
4142 l_sql_stmt := 'SELECT NULL '||
4143 'FROM per_competence_elements '||
4144 'WHERE business_group_id = :p_business_group_id '||
4145 'AND type = :p_type ';
4146 -- evaluate each bind determining the predicate
4147 -- note: if the bind is null we still add a predicate
4148 -- because the USING clause is not dynamic but will be faster than
4149 -- using DBMS_SQL.BIND calls.
4150 IF p_parent_competence_element_id IS NOT NULL THEN
4151 l_sql_stmt := l_sql_stmt||
4152 'AND parent_competence_element_id = :p_parent_competence_element_id ';
4153 ELSE
4154 l_sql_stmt := l_sql_stmt||
4155 'AND :p_parent_competence_element_id IS NULL /* p_parent_competence_element_id IS NULL*/ ';
4156 END IF;
4157 --
4158 IF p_competence_id IS NOT NULL THEN
4159 l_sql_stmt := l_sql_stmt|| 'AND competence_id = :p_competence_id ';
4160 ELSE
4161 l_sql_stmt := l_sql_stmt||
4162 'AND :p_competence_id IS NULL /* p_competence_id IS NULL */';
4163 END IF;
4164 --
4165 IF p_person_id IS NOT NULL THEN
4166 l_sql_stmt := l_sql_stmt|| 'AND person_id = :p_person_id ';
4167 ELSE
4168 l_sql_stmt := l_sql_stmt||
4169 'AND :p_person_id IS NULL /* p_person_id IS NULL */ ';
4170 END IF;
4171 --
4172 IF p_job_id IS NOT NULL THEN
4173 l_sql_stmt := l_sql_stmt|| 'AND job_id = :p_job_id ';
4174 ELSE
4175 l_sql_stmt := l_sql_stmt||
4176 'AND :p_job_id IS NULL /* p_job_id IS NULL */ ';
4177 END IF;
4178 --
4179 IF p_valid_grade_id IS NOT NULL THEN
4180 l_sql_stmt := l_sql_stmt|| 'AND valid_grade_id = :p_valid_grade_id ';
4181 ELSE
4182 l_sql_stmt := l_sql_stmt||
4183 'AND :p_valid_grade_id IS NULL /* p_valid_grade_id IS NULL*/';
4184 END IF;
4185 --
4186 IF p_position_id IS NOT NULL THEN
4187 l_sql_stmt := l_sql_stmt|| 'AND position_id = :p_position_id ';
4188 ELSE
4189 l_sql_stmt := l_sql_stmt||
4190 'AND :p_position_id IS NULL /* p_position_id IS NULL */ ';
4191 END IF;
4192 --
4193 IF p_enterprise_id IS NOT NULL THEN
4194 l_sql_stmt := l_sql_stmt|| 'AND enterprise_id = :p_enterprise_id ';
4195 ELSE
4196 l_sql_stmt := l_sql_stmt||
4197 'AND :p_enterprise_id IS NULL /* p_enterprise_id IS NULL */';
4198 END IF;
4199 --
4200 IF p_organization_id IS NOT NULL THEN
4201 l_sql_stmt := l_sql_stmt|| 'AND organization_id = :p_organization_id ';
4202 ELSE
4203 l_sql_stmt := l_sql_stmt||
4204 'AND :p_organization_id IS NULL /* p_organization_id IS NULL*/';
4205 END IF;
4206 --
4207 IF p_activity_version_id IS NOT NULL THEN
4208 l_sql_stmt := l_sql_stmt||
4209 'AND activity_version_id = :p_activity_version_id ';
4210 ELSE
4211 l_sql_stmt := l_sql_stmt||
4212 'AND :p_activity_version_id IS NULL /* p_activity_version_id IS NULL */ ';
4213 END IF;
4214 --
4215 IF p_assessment_id IS NOT NULL THEN
4216 l_sql_stmt := l_sql_stmt|| 'AND assessment_id = :p_assessment_id ';
4217 ELSE
4218 l_sql_stmt := l_sql_stmt||
4219 'AND :p_assessment_id IS NULL /* p_assessment_id IS NULL */ ';
4220 END IF;
4221 --
4222 IF p_assessment_type_id IS NOT NULL THEN
4223 l_sql_stmt := l_sql_stmt|| 'AND assessment_type_id = :p_assessment_type_id ';
4224 ELSE
4225 l_sql_stmt := l_sql_stmt||
4226 'AND :p_assessment_type_id IS NULL /* p_assessment_type_id IS NULL */ ';
4227 END IF;
4228 --
4229 IF p_effective_date_from IS NOT NULL THEN
4230 l_sql_stmt := l_sql_stmt|| 'AND effective_date_from = :p_effective_date_from ';
4231 ELSE
4232 l_sql_stmt := l_sql_stmt||
4233 'AND :p_effective_date_from IS NULL /* p_effective_date_from IS NULL */ ';
4234 END IF;
4235 --
4236 IF p_group_competence_type IS NOT NULL THEN
4237 l_sql_stmt := l_sql_stmt||
4238 'AND group_competence_type = :p_group_competence_type ';
4239 ELSE
4240 l_sql_stmt := l_sql_stmt||
4241 'AND :p_group_competence_type IS NULL /* p_group_competence_type IS NULL */ ';
4242 END IF;
4243 --
4244 IF p_competence_type IS NOT NULL THEN
4245 l_sql_stmt := l_sql_stmt||
4246 'AND competence_type = :p_competence_type ';
4247 ELSE
4248 l_sql_stmt := l_sql_stmt||
4249 'AND :p_competence_type IS NULL /* p_competence_type IS NULL */ ';
4250 END IF;
4251 --
4252 IF p_object_id IS NOT NULL THEN
4253 l_sql_stmt := l_sql_stmt|| 'AND object_id = :p_object_id ';
4254 ELSE
4255 l_sql_stmt := l_sql_stmt||
4256 'AND :p_object_id IS NULL /* p_object_id IS NULL */ ';
4257 END IF;
4258 --
4259 IF p_object_name IS NOT NULL THEN
4260 l_sql_stmt := l_sql_stmt|| 'AND object_name = :p_object_name ';
4261 ELSE
4262 l_sql_stmt := l_sql_stmt||
4263 'AND :p_object_name IS NULL /* p_object_name IS NULL */ ';
4264 END IF;
4265 --
4266 IF p_party_id IS NOT NULL THEN -- HR/TCA merge
4267 l_sql_stmt := l_sql_stmt|| 'AND party_id = :p_party_id ';
4268 ELSE
4269 l_sql_stmt := l_sql_stmt||
4270 'AND :p_party_id IS NULL /* p_party_id IS NULL */ ';
4271 END IF;
4272 --
4273 IF p_qualification_type_id IS NOT NULL THEN -- BUG3356369
4274 l_sql_stmt := l_sql_stmt|| 'AND qualification_type_id = :p_qualification_type_id ';
4275 ELSE
4276 l_sql_stmt := l_sql_stmt||
4277 'AND :p_qualification_type_id IS NULL /* qualification_type_id IS NULL */ ';
4278 END IF;
4279 hr_utility.set_location(l_proc,10);
4280 --hr_utility.trace('l_sql_stmt : ' || l_sql_stmt);
4281 -- dynamically execute the SQL
4282 BEGIN
4283 EXECUTE IMMEDIATE l_sql_stmt
4284 INTO l_exists
4285 USING p_business_group_id,
4286 p_type,
4287 p_parent_competence_element_id,
4288 p_competence_id,
4289 p_person_id,
4290 p_job_id,
4291 p_valid_grade_id,
4292 p_position_id,
4293 p_enterprise_id,
4294 p_organization_id,
4295 p_activity_version_id,
4296 p_assessment_id,
4297 P_assessment_type_id,
4298 p_effective_date_from,
4299 p_group_competence_type,
4300 p_competence_type,
4301 p_object_id,
4302 p_object_name,
4303 p_party_id, -- HR/TCA merge
4304 p_qualification_type_id;
4305 -- executed successful therefore a row has been found
4306 if p_type = 'COMPETENCE_USAGE' then
4307 hr_utility.set_message(800,'HR_52262_CEL_UNIQUE_COMP_USAGE');
4308 elsif p_type = 'ASSESSMENT' then
4309 hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4310 elsif p_type = 'ASSESSMNET_COMPETENCE' then
4311 hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4312 elsif p_type = 'ASSESSMENT_GROUP' then
4313 hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4314 elsif p_type = 'REQUIREMENT' then
4315 hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4316 elsif p_type = 'DELIVERY' then
4317 hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4318 elsif p_type = 'PERSONAL' then
4319 hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4320 else
4321 hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4322 end if;
4323 --
4324 hr_utility.raise_error;
4325 EXCEPTION
4326 WHEN NO_DATA_FOUND THEN
4327 null;
4328 END;
4329 end if;
4330 hr_utility.set_location(l_proc,7);
4331 --
4332 end if; -- no_exclusive_check for TYPE
4333 hr_utility.set_location('Leaving: ' || l_proc, 10);
4334 exception
4335 when app_exception.application_exception then
4336 hr_multi_message.add;
4337 hr_utility.set_location(' Leaving:'||l_proc,105);
4338 end chk_unique_competence_element;
4339 /*
4340 --
4341 -- ----------------------------------------------------------------------------
4342 -- |-----------------------< CHK_unique_comp_qual >------------------------------|
4343 -- ----------------------------------------------------------------------------
4344 --
4345 -- Description
4346 -- This procedure checks that a combination of competence_id and
4347 -- qualification_type_id is unique.
4348 --
4349 -- Pre-Conditions
4350 -- None.
4351 --
4352 -- In Parameters
4353 -- p_competence_element_id
4354 -- p_competence_id
4355 -- p_qualification_type_id
4356 -- p_object_version_number
4357 -- p_effective_date
4358 --
4359 -- Post Success
4360 -- Processing continues
4361 --
4362 -- Post Failure
4363 -- Error raised.
4364 --
4365 -- Access Status
4366 -- Internal table handler use only.
4367 --
4368 Procedure chk_unique_comp_qual(p_competence_element_id in number
4369 ,p_competence_id in number
4370 ,p_qualification_type_id in number
4371 ,p_object_version_number in number
4372 ,p_effective_date in date) is
4373 --
4374 l_proc varchar2(72) := g_package||'chk_unique_comp_qual';
4375 l_api_updating boolean;
4376 l_exists varchar2(1);
4377 --
4378 cursor csr_unique_comp_qual is
4379 select 'x'
4380 from per_competence_elements
4381 where type = 'QUALIFICATION'
4382 and competence_id = p_competence_id
4383 and qualification_type_id = p_qualification_type_id
4384 and p_effective_date between effective_date_from
4385 and nvl(effective_date_to,hr_api.g_eot);
4386 Begin
4387 --
4388 hr_utility.set_location('Entering:'||l_proc,10);
4389 if p_qualification_type_id is not NULL and p_competence_id is not NULL
4390 then
4391 --
4392 -- Only proceed with validation if :
4393 -- a) The current g_old_rec is current and
4394 -- b) The value for competence_id or qualification_type_id have changed
4395 --
4396 l_api_updating := per_cel_shd.api_updating
4397 (p_competence_element_id => p_competence_element_id
4398 ,p_object_version_number => p_object_version_number);
4399 --
4400 if (l_api_updating
4401 and nvl(per_cel_shd.g_old_rec.competence_id,
4402 hr_api.g_number) = nvl(p_competence_id, hr_api.g_number)
4403 and nvl(per_cel_shd.g_old_rec.qualification_type_id,hr_api.g_number)
4404 = nvl(p_qualification_type_id, hr_api.g_number)
4405 ) then
4406 hr_utility.set_location('Leaving.... ' || l_proc,20);
4407 return;
4408 end if;
4409
4410 hr_utility.set_location(l_proc,20);
4411
4412 open csr_unique_comp_qual;
4413 fetch csr_unique_comp_qual into l_exists;
4414 if csr_unique_comp_qual%found then
4415 close csr_unique_comp_qual;
4416 --
4417 hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4418 hr_utility.raise_error;
4419 --
4420 end if;
4421 close csr_unique_comp_qual;
4422 end if;
4423
4424 hr_utility.set_location('Leaving:'||l_proc,30);
4425 --
4426 End chk_unique_comp_qual;
4427 */
4428 --
4429 -- ---------------------------------------------------------------------------
4430 -- |----------------< CHK_COMP_ELEMENT_DELETE >------------------------------|
4431 -----------------------------------------------------------------------------
4432 --
4433 -- Description:
4434 -- It checks that a competence_element of type personal cannot be
4435 -- deleted. It checks that the competence_element of a compeleted
4436 -- assessment cannot be deleted.
4437 -- It also check that the competence_element of type 'COMPETENCE_USAGE'
4438 -- cannot be deleted if there is a competence element of type 'ASSESSMENT_
4439 -- COMPETENCE' which references those competences and also has a parent_
4440 -- competence_element_id which correspond to a competence_element
4441 -- containing the competences in the group_competence_type_column.
4442 --
4443 -- In Arguments:
4444
4445 -- type
4446 -- competence_type
4447 -- competence_element_id
4448 -- p_parent_competence_element_id
4449 -- group_competence_type
4450 -- assessment_id
4451 -- assessment_type_id
4452 -- competence_id
4453 -- business_group_id
4454 --
4455 -- Post Success:
4456 -- The process succeeds if:
4457 -- the competence_element which need to be deleted is not referenced
4458
4459 -- by another competence element
4460 --
4461 -- Post Failure:
4462 -- An application error is raised and processing is terminated if any:
4463 -- the competence_element which need to be deleted is referenced
4464 -- by another competence element.
4465 --
4466 -- Access Status:
4467 -- Internal Table Handler Use Only.
4468 --
4469 --
4470 procedure chk_comp_element_delete
4471 ( p_competence_element_id
4472 in per_competence_elements.competence_element_id%TYPE
4473 ,p_business_group_id
4474 in per_competence_elements.business_group_id%TYPE
4475 ,p_parent_competence_element_id
4476 in per_competence_elements.parent_competence_element_id%TYPE
4477 ,p_type
4478 in per_competence_elements.type%TYPE
4479 ,p_competence_type
4480 in per_competence_elements.competence_type%TYPE
4481 ,p_group_competence_type
4482 in per_competence_elements.group_competence_type%TYPE
4483 ,p_assessment_id
4484 in per_competence_elements.assessment_id%TYPE
4485 ,p_assessment_type_id
4486 in per_competence_elements.assessment_type_id%TYPE
4487 ,p_competence_id
4488 in per_competence_elements.competence_id%TYPE
4489 ) is
4490 --
4491 l_proc varchar2(72):= g_package||'chk_comp_element_delete';
4492 l_exists varchar2(1);
4493 l_assessment_type_id per_assessment_types.assessment_type_id%TYPE;
4494 --
4495 -- Cursor which is used to check whether the competence being removed from the assessment template
4496 -- is being used by any assessments (ie.TYPE='ASSESSMENT'). This cursor only makes sure that the
4497 -- competence isnt' being used, not whether the assessment in which the competence is being used.
4498 -- Maybe the business rules need tighting up around here as maybe the form should change.
4499 --
4500 cursor csr_get_used_comp_element is
4501 select null
4502 from per_competence_elements
4503 where type = 'ASSESSMENT'
4504 and competence_id = p_competence_id
4505 and assessment_id in
4506 (Select asn.assessment_id
4507 From per_assessments asn
4508 Where asn.assessment_type_id =
4509 (Select assessment_type_id
4510 From per_competence_elements
4511 Where competence_element_id =
4512 (Select parent_competence_element_id
4513 From per_competence_elements
4514 Where competence_element_id=p_competence_element_id
4515 )
4516 )
4517 )
4518 ;
4519 -- Cursor to check that whether a competence element is
4520 -- the parent of other competence element.
4521 --
4522 cursor csr_is_parent_comp is
4523 select null
4524 from per_competence_elements
4525 where parent_competence_element_id = p_competence_element_id
4526 and business_group_id = p_business_group_id;
4527 --
4528 -- Cursor to check the COMPETENCE_USAGE' type referenced by
4529 -- competence element of type "ASSESSMENT_COMPETENCE'
4530 --
4531 cursor csr_get_comp_group is
4532 select null
4533 from per_competence_elements comp1
4534 where comp1.type = 'ASSESSMENT_COMPETENCE'
4535 and comp1.parent_competence_element_id is not null
4536 and comp1.business_group_id = p_business_group_id
4537 and comp1.competence_id = p_competence_id
4538 and exists (select null
4539 from per_competence_elements comp2
4540 where comp2.competence_element_id =
4541 comp1.parent_competence_element_id
4542 and comp1.business_group_id =
4543
4544 comp2.business_group_id
4545 and comp2.group_competence_type =
4546 p_competence_type);
4547 --
4548 -- Cursor to check the COMPETENCE_ELMENT_ID' referenced by
4549 -- per_comp_element_outcmes table BUG3356369
4550 --
4551 cursor csr_comp_element_outcome is
4552 select 'x' from per_comp_element_outcomes
4553 where competence_element_id = p_competence_element_id;
4554
4555 --
4556 begin
4557 --
4558 hr_utility.set_location('Entering:'|| l_proc, 1);
4559 --
4560 -- Only do the delete validation if the type is
4561 -- ASSESSESSMENT_GROUP, ASSESSMENT_COMPETENCE, COMPETENCE_USAGE or
4562 -- or PERSONAL,ASSESSMENT.
4563 --
4564 if (p_type= 'ASSESSESSMENT_GROUP' OR p_type = 'ASSESSMENT_COMPETENCE' OR
4565 p_type= 'COMPETENCE_USAGE' or p_type = 'ASSESSMENT' OR p_type = 'PERSONAL'
4566
4567 ) then
4568
4569 --
4570 -- raise an error message if the type = 'PERSONAL'
4571 --
4572 -- commented out following section due to bug raised (no. 525537) 1-09-97
4573 -- if p_type = 'PERSONAL' then
4574 -- hr_utility.set_location(l_proc,5);
4575 -- hr_utility.set_message(801,'HR_51675_CEL_PER_CANT_DEL');
4576 -- hr_utility.raise_error;
4577 -- end if;
4578 --
4579 --
4580 if(p_type = 'ASSESSMENT_GROUP') then
4581 --
4582 -- check that a parent competence_element cannot be deleted
4583 -- if it is referenced by another competence_element.
4584 --
4585 open csr_is_parent_comp;
4586 fetch csr_is_parent_comp into l_exists;
4587 if csr_is_parent_comp%found then
4588 close csr_is_parent_comp;
4589 hr_utility.set_location(l_proc,15);
4590 hr_utility.set_message(801,'HR_51677_CEL_PARNT_CANT_DEL');
4591 hr_utility.raise_error;
4592
4593 end if;
4594 close csr_is_parent_comp;
4595 end if;
4596 --
4597 -- Now check that if the type is COMPETENCE_USAGE and there is
4598 -- a competence_element of type 'ASSESSMENT_COMPETENCE' with
4599 -- the same competence_type(i.e. Via the parent)and refernces
4600 -- the same competence.
4601 --
4602 if(p_type= 'COMPETENCE_USAGE') then
4603 open csr_get_comp_group;
4604 fetch csr_get_comp_group into l_exists;
4605 if csr_get_comp_group%found then
4606
4607 close csr_get_comp_group;
4608 hr_utility.set_location(l_proc,20);
4609 hr_utility.set_message(801,'HR_51678_CEL_COM_USG_CANT_DEL');
4610 hr_utility.raise_error;
4611 end if;
4612 close csr_get_comp_group;
4613 end if;
4614 --
4615 -- Now check that if an element of ASSESSMENT_COMPETENCE type is
4616 -- going to be deleted, then the competence is not referenced by any
4617 -- other element of type 'ASSESSMENT'
4618 --
4619 if (p_type = 'ASSESSMENT_COMPETENCE') then
4620
4621 open csr_get_used_comp_element;
4622 fetch csr_get_used_comp_element into l_exists;
4623 if csr_get_used_comp_element%found then
4624 close csr_get_used_comp_element;
4625 hr_utility.set_location(l_proc,25);
4626 hr_utility.set_message(801,'HR_51679_CEL_ASS_COMP_CANT_DEL');
4627 hr_utility.raise_error;
4628 end if;
4629 --
4630 close csr_get_used_comp_element;
4631 end if;
4632 --
4633 end if;
4634 --
4635 hr_utility.set_location(l_proc,30);
4636
4637 open csr_comp_element_outcome;
4638 fetch csr_comp_element_outcome into l_exists;
4639 if csr_comp_element_outcome%FOUND then
4640 close csr_comp_element_outcome;
4641 hr_utility.set_message(800,'HR_449135_QUA_FWK_CEL_TAB_REF');
4642 hr_utility.raise_error;
4643 end if;
4644 close csr_comp_element_outcome;
4645
4646 hr_utility.set_location('Leaving: ' || l_proc,40);
4647 end chk_comp_element_delete;
4648 -- -----------------------------------------------------------------------
4649 -- |------------------------------< chk_df >-----------------------------|
4650 -- -----------------------------------------------------------------------
4651 --
4652 -- Description:
4653 -- Validates the all Descriptive Flexfield values.
4654 --
4655 -- Pre-conditions:
4656 -- All other columns have been validated. Must be called as the
4657 -- last step from insert_validate and update_validate.
4658 --
4659 -- In Arguments:
4660 -- p_rec
4661 --
4662 -- Post Success:
4663 -- If the Descriptive Flexfield structure column and data values are
4664 -- all valid this procedure will end normally and processing will
4665 -- continue.
4666 --
4667 -- Post Failure:
4668 -- If the Descriptive Flexfield structure column value or any of
4669 -- the data values are invalid then an application error is raised as
4670 -- a PL/SQL exception.
4671 --
4672 -- Access Status:
4673 -- Internal Row Handler Use Only.
4674 --
4675 procedure chk_df
4676 (p_rec in per_cel_shd.g_rec_type) is
4677 --
4678 l_proc varchar2(72) := g_package||'chk_df';
4679 --
4680 begin
4681 hr_utility.set_location('Entering:'||l_proc, 10);
4682 --
4683 if ((p_rec.valid_grade_id is not null) and (
4684 nvl(per_cel_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
4685 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
4686 nvl(per_cel_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
4687 nvl(p_rec.attribute1, hr_api.g_varchar2) or
4688 nvl(per_cel_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
4689 nvl(p_rec.attribute2, hr_api.g_varchar2) or
4690 nvl(per_cel_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
4691 nvl(p_rec.attribute3, hr_api.g_varchar2) or
4692 nvl(per_cel_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
4693 nvl(p_rec.attribute4, hr_api.g_varchar2) or
4694 nvl(per_cel_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
4695 nvl(p_rec.attribute5, hr_api.g_varchar2) or
4696 nvl(per_cel_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
4697 nvl(p_rec.attribute6, hr_api.g_varchar2) or
4698 nvl(per_cel_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
4699 nvl(p_rec.attribute7, hr_api.g_varchar2) or
4700 nvl(per_cel_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
4701 nvl(p_rec.attribute8, hr_api.g_varchar2) or
4702 nvl(per_cel_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
4703 nvl(p_rec.attribute9, hr_api.g_varchar2) or
4704 nvl(per_cel_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
4705 nvl(p_rec.attribute10, hr_api.g_varchar2) or
4706 nvl(per_cel_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
4707 nvl(p_rec.attribute11, hr_api.g_varchar2) or
4708 nvl(per_cel_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
4709 nvl(p_rec.attribute12, hr_api.g_varchar2) or
4710 nvl(per_cel_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
4711 nvl(p_rec.attribute13, hr_api.g_varchar2) or
4712 nvl(per_cel_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
4713 nvl(p_rec.attribute14, hr_api.g_varchar2) or
4714 nvl(per_cel_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
4715 nvl(p_rec.attribute15, hr_api.g_varchar2) or
4716 nvl(per_cel_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
4717 nvl(p_rec.attribute16, hr_api.g_varchar2) or
4718 nvl(per_cel_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
4719 nvl(p_rec.attribute17, hr_api.g_varchar2) or
4720 nvl(per_cel_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
4721 nvl(p_rec.attribute18, hr_api.g_varchar2) or
4722 nvl(per_cel_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
4723 nvl(p_rec.attribute19, hr_api.g_varchar2) or
4724 nvl(per_cel_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
4725 nvl(p_rec.attribute20, hr_api.g_varchar2)))
4726 or
4727 (p_rec.valid_grade_id is null) then
4728 --
4729 -- Only execute the validation if absolutely necessary:
4730 -- a) During update, the structure column value or any
4731 -- of the attribute values have actually changed.
4732 -- b) During insert.
4733 --
4734 hr_dflex_utility.ins_or_upd_descflex_attribs
4735 (p_appl_short_name => 'PER'
4736 ,p_descflex_name => 'PER_COMPETENCE_ELEMENTS'
4737 ,p_attribute_category => p_rec.attribute_category
4738 ,p_attribute1_name => 'ATTRIBUTE1'
4739 ,p_attribute1_value => p_rec.attribute1
4740 ,p_attribute2_name => 'ATTRIBUTE2'
4741 ,p_attribute2_value => p_rec.attribute2
4742 ,p_attribute3_name => 'ATTRIBUTE3'
4743 ,p_attribute3_value => p_rec.attribute3
4744 ,p_attribute4_name => 'ATTRIBUTE4'
4745 ,p_attribute4_value => p_rec.attribute4
4746 ,p_attribute5_name => 'ATTRIBUTE5'
4747 ,p_attribute5_value => p_rec.attribute5
4748 ,p_attribute6_name => 'ATTRIBUTE6'
4749 ,p_attribute6_value => p_rec.attribute6
4750 ,p_attribute7_name => 'ATTRIBUTE7'
4751 ,p_attribute7_value => p_rec.attribute7
4752 ,p_attribute8_name => 'ATTRIBUTE8'
4753 ,p_attribute8_value => p_rec.attribute8
4754 ,p_attribute9_name => 'ATTRIBUTE9'
4755 ,p_attribute9_value => p_rec.attribute9
4756 ,p_attribute10_name => 'ATTRIBUTE10'
4757 ,p_attribute10_value => p_rec.attribute10
4758 ,p_attribute11_name => 'ATTRIBUTE11'
4759 ,p_attribute11_value => p_rec.attribute11
4760 ,p_attribute12_name => 'ATTRIBUTE12'
4761 ,p_attribute12_value => p_rec.attribute12
4762 ,p_attribute13_name => 'ATTRIBUTE13'
4763 ,p_attribute13_value => p_rec.attribute13
4764 ,p_attribute14_name => 'ATTRIBUTE14'
4765 ,p_attribute14_value => p_rec.attribute14
4766 ,p_attribute15_name => 'ATTRIBUTE15'
4767 ,p_attribute15_value => p_rec.attribute15
4768 ,p_attribute16_name => 'ATTRIBUTE16'
4769 ,p_attribute16_value => p_rec.attribute16
4770 ,p_attribute17_name => 'ATTRIBUTE17'
4771 ,p_attribute17_value => p_rec.attribute17
4772 ,p_attribute18_name => 'ATTRIBUTE18'
4773 ,p_attribute18_value => p_rec.attribute18
4774 ,p_attribute19_name => 'ATTRIBUTE19'
4775 ,p_attribute19_value => p_rec.attribute19
4776 ,p_attribute20_name => 'ATTRIBUTE20'
4777 ,p_attribute20_value => p_rec.attribute20
4778 );
4779 end if;
4780 --
4781 hr_utility.set_location(' Leaving:'||l_proc, 20);
4782
4783 end chk_df;
4784 --
4785 -- ----------------------------------------------------------------------------
4786 -- |-----------------------------< chk_ddf >----------------------------------|
4787 -- ----------------------------------------------------------------------------
4788 --
4789 -- Description:
4790 -- Validates all the Developer Descriptive Flexfield values.
4791 --
4792 -- Prerequisites:
4793 -- All other columns have been validated. Must be called as the
4794 -- last step from insert_validate and update_validate.
4795 --
4796 -- In Arguments:
4797 -- p_rec
4798 --
4799 -- Post Success:
4800 -- If the Developer Descriptive Flexfield structure column and data values
4801 -- are all valid this procedure will end normally and processing will
4802 -- continue.
4803 --
4804 -- Post Failure:
4805 -- If the Developer Descriptive Flexfield structure column value or any of
4806 -- the data values are invalid then an application error is raised as
4807 -- a PL/SQL exception.
4808 --
4809 -- Access Status:
4810 -- Internal Row Handler Use Only.
4811 --
4812 -- ----------------------------------------------------------------------------
4813 procedure chk_ddf
4814 (p_rec in per_cel_shd.g_rec_type
4815 ) is
4816 --
4817 l_proc varchar2(72) := g_package || 'chk_ddf';
4818 --
4819 begin
4820 hr_utility.set_location('Entering:'||l_proc,10);
4821 --
4822 if ((p_rec.competence_element_id is not null) and (
4823 nvl(per_cel_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
4824 nvl(p_rec.information_category, hr_api.g_varchar2) or
4825 nvl(per_cel_shd.g_old_rec.information1, hr_api.g_varchar2) <>
4826 nvl(p_rec.information1, hr_api.g_varchar2) or
4827 nvl(per_cel_shd.g_old_rec.information2, hr_api.g_varchar2) <>
4828 nvl(p_rec.information2, hr_api.g_varchar2) or
4829 nvl(per_cel_shd.g_old_rec.information3, hr_api.g_varchar2) <>
4830 nvl(p_rec.information3, hr_api.g_varchar2) or
4831 nvl(per_cel_shd.g_old_rec.information4, hr_api.g_varchar2) <>
4832 nvl(p_rec.information4, hr_api.g_varchar2) or
4833 nvl(per_cel_shd.g_old_rec.information5, hr_api.g_varchar2) <>
4834 nvl(p_rec.information5, hr_api.g_varchar2) or
4835 nvl(per_cel_shd.g_old_rec.information6, hr_api.g_varchar2) <>
4836 nvl(p_rec.information6, hr_api.g_varchar2) or
4837 nvl(per_cel_shd.g_old_rec.information7, hr_api.g_varchar2) <>
4838 nvl(p_rec.information7, hr_api.g_varchar2) or
4839 nvl(per_cel_shd.g_old_rec.information8, hr_api.g_varchar2) <>
4840 nvl(p_rec.information8, hr_api.g_varchar2) or
4841 nvl(per_cel_shd.g_old_rec.information9, hr_api.g_varchar2) <>
4842 nvl(p_rec.information9, hr_api.g_varchar2) or
4843 nvl(per_cel_shd.g_old_rec.information10, hr_api.g_varchar2) <>
4844 nvl(p_rec.information10, hr_api.g_varchar2) or
4845 nvl(per_cel_shd.g_old_rec.information11, hr_api.g_varchar2) <>
4846 nvl(p_rec.information11, hr_api.g_varchar2) or
4847 nvl(per_cel_shd.g_old_rec.information13, hr_api.g_varchar2) <>
4848 nvl(p_rec.information13, hr_api.g_varchar2) or
4849 nvl(per_cel_shd.g_old_rec.information14, hr_api.g_varchar2) <>
4850 nvl(p_rec.information14, hr_api.g_varchar2) or
4851 nvl(per_cel_shd.g_old_rec.information15, hr_api.g_varchar2) <>
4852 nvl(p_rec.information15, hr_api.g_varchar2) or
4853 nvl(per_cel_shd.g_old_rec.information16, hr_api.g_varchar2) <>
4854 nvl(p_rec.information16, hr_api.g_varchar2) or
4855 nvl(per_cel_shd.g_old_rec.information17, hr_api.g_varchar2) <>
4856 nvl(p_rec.information17, hr_api.g_varchar2) or
4857 nvl(per_cel_shd.g_old_rec.information18, hr_api.g_varchar2) <>
4858 nvl(p_rec.information18, hr_api.g_varchar2) or
4859 nvl(per_cel_shd.g_old_rec.information19, hr_api.g_varchar2) <>
4860 nvl(p_rec.information19, hr_api.g_varchar2) or
4861 nvl(per_cel_shd.g_old_rec.information20, hr_api.g_varchar2) <>
4862 nvl(p_rec.information20, hr_api.g_varchar2)))
4863 or (p_rec.competence_element_id is not null) then
4864 --
4865 -- Only execute the validation if absolutely necessary:
4866 -- a) During update, the structure column value or any
4867 -- of the attribute values have actually changed.
4868 -- b) During insert.
4869 --
4870 hr_dflex_utility.ins_or_upd_descflex_attribs
4871 (p_appl_short_name => 'PER'
4872 ,p_descflex_name => 'Competence Element Developer'
4873 ,p_attribute_category => p_rec.INFORMATION_CATEGORY
4874 ,p_attribute1_name => 'INFORMATION1'
4875 ,p_attribute1_value => p_rec.information1
4876 ,p_attribute2_name => 'INFORMATION2'
4877 ,p_attribute2_value => p_rec.information2
4878 ,p_attribute3_name => 'INFORMATION3'
4879 ,p_attribute3_value => p_rec.information3
4880 ,p_attribute4_name => 'INFORMATION4'
4881 ,p_attribute4_value => p_rec.information4
4882 ,p_attribute5_name => 'INFORMATION5'
4883 ,p_attribute5_value => p_rec.information5
4884 ,p_attribute6_name => 'INFORMATION6'
4885 ,p_attribute6_value => p_rec.information6
4886 ,p_attribute7_name => 'INFORMATION7'
4887 ,p_attribute7_value => p_rec.information7
4888 ,p_attribute8_name => 'INFORMATION8'
4889 ,p_attribute8_value => p_rec.information8
4890 ,p_attribute9_name => 'INFORMATION9'
4891 ,p_attribute9_value => p_rec.information9
4892 ,p_attribute10_name => 'INFORMATION10'
4893 ,p_attribute10_value => p_rec.information10
4894 ,p_attribute11_name => 'INFORMATION11'
4895 ,p_attribute11_value => p_rec.information11
4896 ,p_attribute12_name => 'INFORMATION12'
4897 ,p_attribute12_value => p_rec.information12
4898 ,p_attribute13_name => 'INFORMATION13'
4899 ,p_attribute13_value => p_rec.information13
4900 ,p_attribute14_name => 'INFORMATION14'
4901 ,p_attribute14_value => p_rec.information14
4902 ,p_attribute15_name => 'INFORMATION15'
4903 ,p_attribute15_value => p_rec.information15
4904 ,p_attribute16_name => 'INFORMATION16'
4905 ,p_attribute16_value => p_rec.information16
4906 ,p_attribute17_name => 'INFORMATION17'
4907 ,p_attribute17_value => p_rec.information17
4908 ,p_attribute18_name => 'INFORMATION18'
4909 ,p_attribute18_value => p_rec.information18
4910 ,p_attribute19_name => 'INFORMATION19'
4911 ,p_attribute19_value => p_rec.information19
4912 ,p_attribute20_name => 'INFORMATION20'
4913 ,p_attribute20_value => p_rec.information20
4914 );
4915 end if;
4916 --
4917 hr_utility.set_location(' Leaving:'||l_proc,20);
4918 end chk_ddf;
4919 --
4920 -- ----------------------------------------------------------------------------
4921 -- |---------------------------< insert_validate >----------------------------|
4922 -- ----------------------------------------------------------------------------
4923 Procedure insert_validate(p_rec in out nocopy per_cel_shd.g_rec_type,
4924 p_effective_date in Date) is
4925 --
4926 l_proc varchar2(72) := g_package||'insert_validate';
4927 --
4928 Begin
4929 hr_utility.set_location('Entering:'||l_proc, 5);
4930 --
4931 -- Validate important attributes
4932 --
4933 -- Call all supporting business operations
4934 --
4935 -- Validate business_group_id
4936 --
4937 -- HR/TCA merge
4938 -- if party_id is null or business_group_id is not null
4939 -- no need to check business_grroup_id
4940 if p_rec.party_id is null or p_rec.business_group_id is not null then
4941 -- ngundura added this if condition
4942
4943 if ( p_rec.type not in ('PROJECT_ROLE','OPEN_ASSIGNMENT','QUALIFICATION','ASSESSMENT_GROUP','ASSESSMENT_COMPETENCE')) then
4944 -- Validate Bus Grp
4945 hr_api.validate_bus_grp_id(
4946 p_business_group_id => p_rec.business_group_id
4947 ,p_associated_column1 => per_cel_shd.g_tab_nam ||
4948 '.BUSINESS_GROUP_ID'
4949 );
4950 --
4951 -- After validating the set of important attributes,
4952 -- if Mulitple message detection is enabled and at least
4953 -- one error has been found then abort further validation.
4954 --
4955 hr_multi_message.end_validation_set;
4956 --
4957 per_cel_shd.g_bus_grp := true;
4958 else
4959 per_cel_shd.g_bus_grp := false;
4960 end if;
4961 else
4962 per_cel_shd.g_bus_grp := false;
4963 end if;
4964 --
4965 -- Business Rule Mapping
4966 -- =====================
4967 -- CHK_TYPE
4968 --
4969 chk_type
4970 (p_competence_element_id => p_rec.competence_element_id
4971 ,p_effective_date => p_effective_date
4972 ,p_type => p_rec.type
4973 ,p_object_version_number => p_rec.object_version_number
4974 );
4975 --
4976 hr_utility.set_location(l_proc, 10);
4977 --
4978 --
4979 -- Business Rule Mapping
4980 -- =====================
4981 -- CHK_TYPE_AND_VALIDATION
4982 --
4983 chk_type_and_validation
4984 (p_competence_element_id => p_rec.competence_element_id
4985 ,p_object_version_number => p_rec.object_version_number
4986 ,p_business_group_id => p_rec.business_group_id
4987 ,p_enterprise_id => p_rec.enterprise_id
4988 ,p_type => p_rec.type
4989 ,p_competence_id => p_rec.competence_id
4990 ,p_assessment_id => p_rec.assessment_id
4991 ,p_assessment_type_id => p_rec.assessment_type_id
4992 ,p_activity_version_id => p_rec.activity_version_id
4993 ,p_organization_id => p_rec.organization_id
4994 ,p_job_id => p_rec.job_id
4995 ,p_valid_grade_id => p_rec.valid_grade_id
4996 ,p_position_id => p_rec.position_id
4997 ,p_person_id => p_rec.person_id
4998
4999 ,p_parent_competence_element_id
5000 => p_rec.parent_competence_element_id
5001 ,p_group_competence_type => p_rec.group_competence_type
5002 ,p_effective_date_to => p_rec.effective_date_to
5003 ,p_effective_date_from => p_rec.effective_date_from
5004 ,p_proficiency_level_id => p_rec.proficiency_level_id
5005 ,p_certification_date => p_rec.certification_date
5006 ,p_certification_method => p_rec.certification_method
5007 ,p_next_certification_date => p_rec.next_certification_date
5008 ,p_mandatory => p_rec.mandatory
5009 ,p_normal_elapse_duration => p_rec.normal_elapse_duration
5010 ,p_normal_elapse_duration_unit
5011 => p_rec.normal_elapse_duration_unit
5012
5013 ,p_high_proficiency_level_id
5014 => p_rec.high_proficiency_level_id
5015 ,p_competence_type => p_rec.competence_type
5016 ,p_sequence_number => p_rec.sequence_number
5017 ,p_source_of_proficiency_level
5018 => p_rec.source_of_proficiency_level
5019 ,p_weighting_level_id => p_rec.weighting_level_id
5020 ,p_rating_level_id => p_rec.rating_level_id
5021 ,p_line_score => p_rec.line_score
5022 ,p_object_id => p_rec.object_id
5023 ,p_object_name => p_rec.object_name
5024 ,p_party_id => p_rec.party_id -- HR/TCA merge
5025 ,p_qualification_type_id => p_rec.qualification_type_id
5026 );
5027 --
5028
5029 hr_utility.set_location(l_proc, 15);
5030 --
5031 -- Business Rule Mapping
5032 -- =====================
5033 -- CHK_UNIQUE_COMPETENCE_ELEMENT
5034 --
5035 chk_unique_competence_element
5036 (p_competence_element_id => p_rec.competence_element_id
5037 ,p_business_group_id => p_rec.business_group_id
5038 ,p_enterprise_id => p_rec.enterprise_id
5039 ,p_type => p_rec.type
5040 ,p_competence_id => p_rec.competence_id
5041 ,p_assessment_id => p_rec.assessment_id
5042
5043 ,p_assessment_type_id => p_rec.assessment_type_id
5044 ,p_activity_version_id => p_rec.activity_version_id
5045 ,p_organization_id => p_rec.organization_id
5046 ,p_job_id => p_rec.job_id
5047 ,p_valid_grade_id => p_rec.valid_grade_id
5048 ,p_position_id => p_rec.position_id
5049 ,p_person_id => p_rec.person_id
5050 ,p_parent_competence_element_id
5051 => p_rec.parent_competence_element_id
5052 ,p_group_competence_type => p_rec.group_competence_type
5053 ,p_effective_date_from => p_rec.effective_date_from
5054
5055 ,p_competence_type => p_rec.competence_type
5056 ,p_object_version_number => p_rec.object_version_number
5057
5058 ,p_object_name => p_rec.object_name
5059 ,p_object_id => p_rec.object_id
5060 ,p_party_id => p_rec.party_id -- HR/TCA merge
5061 ,p_qualification_type_id => p_rec.qualification_type_id
5062 );
5063 --
5064 hr_utility.set_location(l_proc, 16);
5065 --
5066 -- Business Rule Mapping
5067 -- =====================
5068 -- CHK_CERTIFICATION_METHOD
5069 --
5070 chk_certification_method
5071 (p_competence_element_id => p_rec.competence_element_id
5072 ,p_effective_date => p_effective_date
5073 ,p_certification_method => p_rec.certification_method
5074 ,p_object_version_number => p_rec.object_version_number
5075 );
5076 --
5077 hr_utility.set_location(l_proc, 20);
5078 --
5079 -- Business Rule Mapping
5080 -- =====================
5081 -- CHK_COMPETENCE_TYPE
5082 --
5083 chk_competence_type
5084 (p_competence_element_id => p_rec.competence_element_id
5085 ,p_effective_date => p_effective_date
5086 ,p_competence_type => p_rec.competence_type
5087 ,p_object_version_number => p_rec.object_version_number
5088 );
5089 --
5090 hr_utility.set_location(l_proc, 25);
5091 --
5092 -- Business Rule Mapping
5093 -- =====================
5094 -- CHK_GROUP_COMPETENCE_TYPE
5095 --
5096 chk_competence_type
5097 (p_competence_element_id => p_rec.competence_element_id
5098 ,p_effective_date => p_effective_date
5099 ,p_competence_type => p_rec.group_competence_type
5100 ,p_object_version_number => p_rec.object_version_number
5101 );
5102 --
5103 hr_utility.set_location(l_proc, 30);
5104 --
5105 -- Business Rule Mapping
5106 -- =====================
5107 -- CHK_MANDATORY
5108 --
5109 chk_mandatory
5110 (p_competence_element_id => p_rec.competence_element_id
5111 ,p_effective_date => p_effective_date
5112
5113 ,p_mandatory => p_rec.mandatory
5114 ,p_object_version_number => p_rec.object_version_number
5115 );
5116 --
5117 hr_utility.set_location(l_proc, 35);
5118 --
5119 -- Business Rule Mapping
5120 -- =====================
5121 -- CHK_SOURC_OF_PROFICIENCY_LEVEL
5122 --
5123 chk_source_of_proficiency
5124 (p_competence_element_id => p_rec.competence_element_id
5125 ,p_effective_date => p_effective_date
5126
5127 ,p_source_of_proficiency_level
5128 => p_rec.source_of_proficiency_level
5129 ,p_object_version_number => p_rec.object_version_number
5130 );
5131 --
5132 hr_utility.set_location(l_proc, 40);
5133 --
5134 -- Business Rule Mapping
5135 -- =====================
5136 -- CHK_CERTIFICATION_METHOD_DATE
5137 --
5138 chk_certification_method_date
5139 (p_competence_element_id => p_rec.competence_element_id
5140
5141 ,p_certification_date => p_rec.certification_date
5142 ,p_certification_method => p_rec.certification_method
5143 ,p_object_version_number => p_rec.object_version_number
5144 );
5145 --
5146 hr_utility.set_location(l_proc, 45);
5147 --
5148 -- Business Rule Mapping
5149 -- =====================
5150 -- CHK_NEXT_CERTIFICATION_DATE
5151 --
5152 chk_next_certification_date
5153 (p_competence_element_id => p_rec.competence_element_id
5154 ,p_certification_date => p_rec.certification_date
5155 ,p_next_certification_date => p_rec.next_certification_date
5156 ,p_object_version_number => p_rec.object_version_number
5157 ,p_effective_date_from => p_rec.effective_date_from -- added for fix of #731089
5158 );
5159 --
5160 hr_utility.set_location(l_proc, 48);
5161 --
5162 -- Business Rule Mapping
5163 -- =====================
5164 -- CHK_FOREIGN_KEYS
5165 --
5166 chk_foreign_keys
5167 (p_competence_element_id => p_rec.competence_element_id
5168
5169 ,p_object_version_number => p_rec.object_version_number
5170 ,p_business_group_id => p_rec.business_group_id
5171 ,p_enterprise_id => p_rec.enterprise_id
5172 ,p_competence_id => p_rec.competence_id
5173 ,p_assessment_id => p_rec.assessment_id
5174 ,p_assessment_type_id => p_rec.assessment_type_id
5175 ,p_activity_version_id => p_rec.activity_version_id
5176 ,p_organization_id => p_rec.organization_id
5177 ,p_job_id => p_rec.job_id
5178 ,p_valid_grade_id => p_rec.valid_grade_id
5179 ,p_position_id => p_rec.position_id
5180 ,p_person_id => p_rec.person_id
5181 ,p_parent_competence_element_id
5182
5183 => p_rec.parent_competence_element_id
5184 ,p_effective_date_to => p_rec.effective_date_to
5185 ,p_effective_date_from => p_rec.effective_date_from
5186 ,p_proficiency_level_id => p_rec.proficiency_level_id
5187 ,p_high_proficiency_level_id
5188 => p_rec.high_proficiency_level_id
5189 ,p_weighting_level_id => p_rec.weighting_level_id
5190 ,p_rating_level_id => p_rec.rating_level_id
5191 ,p_effective_date => p_effective_date
5192 ,p_type => p_rec.type
5193 ,p_party_id => p_rec.party_id -- HR/TCA merge
5194 ,p_qualification_type_id => p_rec.qualification_type_id
5195 );
5196 --
5197 hr_utility.set_location('Entering:'||l_proc, 50);
5198
5199 --
5200 -- Business Rule Mapping
5201 -- =====================
5202 -- CHK_PARTY_ID
5203 --
5204 chk_party_id
5205 (p_rec
5206 ,p_effective_date
5207 );
5208
5209 --
5210 hr_utility.set_location('Entering:'||l_proc, 52);
5211
5212 --
5213 -- Business Rule Mapping
5214 -- =====================
5215 -- CHK_PROFICIENCY_LEVEL_ID
5216 --
5217 chk_proficiency_level_id
5218 (p_competence_element_id => p_rec.competence_element_id
5219 ,p_business_group_id => p_rec.business_group_id
5220 ,p_proficiency_level_id => p_rec.proficiency_level_id
5221 ,p_high_proficiency_level_id
5222 => p_rec.high_proficiency_level_id
5223 ,p_competence_id => p_rec.competence_id
5224 ,p_object_version_number => p_rec.object_version_number
5225 ,p_party_id => p_rec.party_id
5226 );
5227 --
5228 hr_utility.set_location(l_proc, 55);
5229 --
5230 -- Business Rule Mapping
5231 -- =====================
5232 -- CHK_RATING_WEIGHTING_ID
5233 --
5234 chk_rating_weighting_id
5235 (p_competence_element_id => p_rec.competence_element_id
5236 ,p_business_group_id => p_rec.business_group_id
5237 ,p_rating_level_id => p_rec.rating_level_id
5238 ,p_weighting_level_id => p_rec.weighting_level_id
5239
5240 ,p_assessment_id => p_rec.assessment_id
5241 ,p_object_version_number => p_rec.object_version_number
5242 ,p_type => p_rec.type
5243 ,p_party_id => p_rec.party_id
5244 );
5245 --
5246 hr_utility.set_location(l_proc, 60);
5247 --
5248 -- Business Rule Mapping
5249 -- =====================
5250 -- CHK_COMPETENCE_ELEMENT_DATES
5251 --
5252 chk_competence_element_dates
5253 (p_competence_element_id => p_rec.competence_element_id
5254
5255 ,p_business_group_id => p_rec.business_group_id
5256 ,p_competence_id => p_rec.competence_id
5257 ,p_person_id => p_rec.person_id
5258 ,p_position_id => p_rec.position_id
5259 ,p_organization_id => p_rec.organization_id
5260 ,p_job_id => p_rec.job_id
5261 ,p_valid_grade_id => p_rec.valid_grade_id
5262 ,p_effective_date_from => p_rec.effective_date_from
5263 ,p_effective_date_to => p_rec.effective_date_to
5264 ,p_object_version_number => p_rec.object_version_number
5265 ,p_enterprise_id => p_rec.enterprise_id
5266 );
5267 --
5268 hr_utility.set_location(l_proc, 65);
5269
5270 --
5271 -- Business Rule Mapping
5272 -- =====================
5273 -- CHK_NORMAL_ELAPSE_DURATION
5274 --
5275 chk_normal_elapse_duration
5276 (p_competence_element_id => p_rec.competence_element_id
5277 ,p_effective_date => p_effective_date
5278 ,p_normal_elapse_duration => p_rec.normal_elapse_duration
5279 ,p_normal_elapse_duration_unit
5280 => p_rec.normal_elapse_duration_unit
5281 ,p_object_version_number => p_rec.object_version_number
5282 );
5283
5284 --
5285 hr_utility.set_location(l_proc, 70);
5286
5287 /*
5288 -- Business Rule Mapping
5289 -- =====================
5290 -- CHK_UNIQUE_COMP_QUAL
5291 --
5292 chk_unique_comp_qual
5293 (p_competence_element_id => p_rec.competence_element_id
5294 ,p_competence_id => p_rec.competence_id
5295 ,p_qualification_type_id => p_rec.qualification_type_id
5296 ,p_object_version_number => p_rec.object_version_number
5297 ,p_effective_date => p_effective_date
5298 );
5299 */
5300 --
5301 hr_utility.set_location(l_proc, 80);
5302 --
5303 -- do the descriptive flex validation.
5304 --
5305 per_cel_bus.chk_df(p_rec => p_rec);
5306
5307 hr_utility.set_location(l_proc, 90);
5308
5309 --
5310 -- do the developer descriptive flex validation.
5311 --
5312 per_cel_bus.chk_ddf(p_rec => p_rec);
5313 --
5314 hr_utility.set_location('Leaving:'||l_proc, 100);
5315 --
5316 End insert_validate;
5317 --
5318 -- ----------------------------------------------------------------------------
5319 -- |---------------------------< update_validate >----------------------------|
5320 -- ----------------------------------------------------------------------------
5321 Procedure update_validate(p_rec in per_cel_shd.g_rec_type,
5322 p_effective_date in Date) is
5323 --
5324 l_proc varchar2(72) := g_package||'update_validate';
5325 --
5326 Begin
5327 hr_utility.set_location('Entering:'||l_proc, 5);
5328 -- Validate Important Attributes
5329 --
5330 -- Business Rule Mapping
5331 -- =====================
5332 -- Check non_updateable arguments
5333 --
5334 -- if party_id is pecified, business_group is not mandatory
5335 -- HR/TCA merge
5336 if p_rec.party_id is null or p_rec.business_group_id is not null then
5337 --
5338 -- Validate business_group_id
5339 --
5340 if ( p_rec.type not in ('PROJECT_ROLE','OPEN_ASSIGNMENT','QUALIFICATION','ASSESSMENT_GROUP','ASSESSMENT_COMPETENCE')) then
5341 hr_api.validate_bus_grp_id(
5342 p_business_group_id => p_rec.business_group_id
5343 ,p_associated_column1 => per_cel_shd.g_tab_nam ||
5344 '.BUSINESS_GROUP_ID'
5345 );
5346 --
5347 -- After validating the set of important attributes,
5348 -- if Mulitple message detection is enabled and at least
5349 -- one error has been found then abort further validation.
5350 --
5351 hr_multi_message.end_validation_set;
5352 --
5353 per_cel_shd.g_bus_grp := true;
5354 else
5355 per_cel_shd.g_bus_grp := false;
5356 end if;
5357 else
5358 per_cel_shd.g_bus_grp := false;
5359 end if;
5360 --
5361 --
5362 per_cel_bus.check_non_updateable_args
5363 (p_rec =>p_rec);
5364 --
5365 hr_utility.set_location (l_proc,6);
5366 --
5367 -- Business Rule Mapping
5368 -- =====================
5369 -- CHK_TYPE_AND_VALIDATION
5370 --
5371 chk_type_and_validation
5372 (p_competence_element_id => p_rec.competence_element_id
5373 ,p_object_version_number => p_rec.object_version_number
5374
5375 ,p_business_group_id => p_rec.business_group_id
5376 ,p_enterprise_id => p_rec.enterprise_id
5377 ,p_type => p_rec.type
5378 ,p_competence_id => p_rec.competence_id
5379 ,p_assessment_id => p_rec.assessment_id
5380 ,p_assessment_type_id => p_rec.assessment_type_id
5381 ,p_activity_version_id => p_rec.activity_version_id
5382 ,p_organization_id => p_rec.organization_id
5383 ,p_job_id => p_rec.job_id
5384 ,p_valid_grade_id => p_rec.valid_grade_id
5385 ,p_position_id => p_rec.position_id
5386 ,p_person_id => p_rec.person_id
5387 ,p_parent_competence_element_id
5388
5389 => p_rec.parent_competence_element_id
5390 ,p_group_competence_type => p_rec.group_competence_type
5391 ,p_effective_date_to => p_rec.effective_date_to
5392 ,p_effective_date_from => p_rec.effective_date_from
5393 ,p_proficiency_level_id => p_rec.proficiency_level_id
5394 ,p_certification_date => p_rec.certification_date
5395 ,p_certification_method => p_rec.certification_method
5396 ,p_next_certification_date => p_rec.next_certification_date
5397 ,p_mandatory => p_rec.mandatory
5398 ,p_normal_elapse_duration => p_rec.normal_elapse_duration
5399 ,p_normal_elapse_duration_unit
5400 => p_rec.normal_elapse_duration_unit
5401
5402 ,p_high_proficiency_level_id
5403 => p_rec.high_proficiency_level_id
5404 ,p_competence_type => p_rec.competence_type
5405 ,p_sequence_number => p_rec.sequence_number
5406 ,p_source_of_proficiency_level
5407 => p_rec.source_of_proficiency_level
5408 ,p_weighting_level_id => p_rec.weighting_level_id
5409 ,p_rating_level_id => p_rec.rating_level_id
5410 ,p_line_score => p_rec.line_score
5411 ,p_object_id => p_rec.object_id
5412 ,p_object_name => p_rec.object_name
5413 ,p_party_id => p_rec.party_id -- HR/TCA merge
5414 ,p_qualification_type_id => p_rec.qualification_type_id -- BUG3356369
5415 );
5416 --
5417 hr_utility.set_location(l_proc, 15);
5418
5419 --
5420 -- Business Rule Mapping
5421 -- =====================
5422 -- CHK_UNIQUE_COMPETENCE_ELEMENT
5423 --
5424 chk_unique_competence_element
5425 (p_competence_element_id => p_rec.competence_element_id
5426 ,p_business_group_id => p_rec.business_group_id
5427 ,p_enterprise_id => p_rec.enterprise_id
5428 ,p_type => p_rec.type
5429 ,p_competence_id => p_rec.competence_id
5430 ,p_assessment_id => p_rec.assessment_id
5431 ,p_assessment_type_id => p_rec.assessment_type_id
5432
5433 ,p_activity_version_id => p_rec.activity_version_id
5434 ,p_organization_id => p_rec.organization_id
5435 ,p_job_id => p_rec.job_id
5436 ,p_valid_grade_id => p_rec.valid_grade_id
5437 ,p_position_id => p_rec.position_id
5438 ,p_person_id => p_rec.person_id
5439 ,p_parent_competence_element_id
5440 => p_rec.parent_competence_element_id
5441 ,p_group_competence_type => p_rec.group_competence_type
5442 ,p_effective_date_from => p_rec.effective_date_from
5443 ,p_competence_type => p_rec.competence_type
5444
5445 ,p_object_version_number => p_rec.object_version_number
5446
5447 ,p_object_name => p_rec.object_name
5448 ,p_object_id => p_rec.object_id
5449 ,p_party_id => p_rec.party_id -- HR/TCA merge
5450 ,p_qualification_type_id => p_rec.qualification_type_id
5451 );
5452 --
5453 hr_utility.set_location(l_proc, 16);
5454 --
5455 -- Business Rule Mapping
5456 -- =====================
5457 -- CHK_CERTIFICATION_METHOD
5458 --
5459 chk_certification_method
5460 (p_competence_element_id => p_rec.competence_element_id
5461 ,p_effective_date => p_effective_date
5462 ,p_certification_method => p_rec.certification_method
5463
5464 ,p_object_version_number => p_rec.object_version_number
5465 );
5466 --
5467 hr_utility.set_location(l_proc, 20);
5468 --
5469 -- Business Rule Mapping
5470 -- =====================
5471 -- CHK_COMPETENCE_TYPE
5472 --
5473 chk_competence_type
5474 (p_competence_element_id => p_rec.competence_element_id
5475 ,p_effective_date => p_effective_date
5476 ,p_competence_type => p_rec.competence_type
5477
5478 ,p_object_version_number => p_rec.object_version_number
5479 );
5480 --
5481 hr_utility.set_location(l_proc, 25);
5482 --
5483 -- Business Rule Mapping
5484 -- =====================
5485 -- CHK_GROUP_COMPETENCE_TYPE
5486 --
5487 chk_competence_type
5488 (p_competence_element_id => p_rec.competence_element_id
5489 ,p_effective_date => p_effective_date
5490 ,p_competence_type => p_rec.group_competence_type
5491
5492 ,p_object_version_number => p_rec.object_version_number
5493 );
5494 --
5495 hr_utility.set_location(l_proc, 30);
5496 --
5497 -- Business Rule Mapping
5498 -- =====================
5499 -- CHK_MANDATORY
5500 --
5501 chk_mandatory
5502 (p_competence_element_id => p_rec.competence_element_id
5503 ,p_effective_date => p_effective_date
5504 ,p_mandatory => p_rec.mandatory
5505
5506 ,p_object_version_number => p_rec.object_version_number
5507 );
5508 --
5509 hr_utility.set_location(l_proc, 35);
5510 --
5511 -- Business Rule Mapping
5512 -- =====================
5513 -- CHK_SOURC_OF_PROFICIENCY_LEVEL
5514 --
5515 chk_source_of_proficiency
5516 (p_competence_element_id => p_rec.competence_element_id
5517 ,p_effective_date => p_effective_date
5518 ,p_source_of_proficiency_level
5519
5520 => p_rec.source_of_proficiency_level
5521 ,p_object_version_number => p_rec.object_version_number
5522 );
5523 --
5524 hr_utility.set_location(l_proc, 40);
5525 --
5526 -- Business Rule Mapping
5527 -- =====================
5528 -- CHK_CERTIFICATION_METHOD_DATE
5529 --
5530 chk_certification_method_date
5531 (p_competence_element_id => p_rec.competence_element_id
5532 ,p_certification_date => p_rec.certification_date
5533
5534 ,p_certification_method => p_rec.certification_method
5535 ,p_object_version_number => p_rec.object_version_number
5536 );
5537 --
5538 hr_utility.set_location(l_proc, 45);
5539 --
5540 -- Business Rule Mapping
5541 -- =====================
5542 -- CHK_NEXT_CERTIFICATION_DATE
5543 --
5544 chk_next_certification_date
5545 (p_competence_element_id => p_rec.competence_element_id
5546 ,p_certification_date => p_rec.certification_date
5547
5548 ,p_next_certification_date => p_rec.next_certification_date
5549 ,p_object_version_number => p_rec.object_version_number
5550 ,p_effective_date_from => p_rec.effective_date_from --added for bug fix of #731089
5551 );
5552 --
5553 hr_utility.set_location(l_proc, 48);
5554
5555 -- Business Rule Mapping
5556 -- =====================
5557 -- CHK_PROFICIENCY_LEVEL_ID
5558 --
5559 chk_proficiency_level_id
5560 (p_competence_element_id => p_rec.competence_element_id
5561 ,p_business_group_id => p_rec.business_group_id
5562
5563 ,p_proficiency_level_id => p_rec.proficiency_level_id
5564 ,p_high_proficiency_level_id
5565 => p_rec.high_proficiency_level_id
5566 ,p_competence_id => p_rec.competence_id
5567 ,p_object_version_number => p_rec.object_version_number
5568 ,p_party_id => p_rec.party_id
5569 );
5570 --
5571 hr_utility.set_location(l_proc, 55);
5572 --
5573 -- Business Rule Mapping
5574 -- =====================
5575 -- CHK_RATING_WEIGHTING_ID
5576 --
5577
5578 chk_rating_weighting_id
5579 (p_competence_element_id => p_rec.competence_element_id
5580 ,p_business_group_id => p_rec.business_group_id
5581 ,p_rating_level_id => p_rec.rating_level_id
5582 ,p_weighting_level_id => p_rec.weighting_level_id
5583 ,p_assessment_id => p_rec.assessment_id
5584 ,p_object_version_number => p_rec.object_version_number
5585 ,p_type => p_rec.type
5586 ,p_party_id => p_rec.party_id
5587 );
5588 --
5589 hr_utility.set_location(l_proc, 60);
5590 --
5591 -- Business Rule Mapping
5592
5593 -- =====================
5594 -- CHK_COMPETENCE_ELEMENT_DATES
5595 --
5596 chk_competence_element_dates
5597 (p_competence_element_id => p_rec.competence_element_id
5598 ,p_business_group_id => p_rec.business_group_id
5599 ,p_competence_id => p_rec.competence_id
5600 ,p_person_id => p_rec.person_id
5601 ,p_position_id => p_rec.position_id
5602 ,p_organization_id => p_rec.organization_id
5603 ,p_job_id => p_rec.job_id
5604 ,p_valid_grade_id => p_rec.valid_grade_id
5605 ,p_effective_date_from => p_rec.effective_date_from
5606
5607 ,p_effective_date_to => p_rec.effective_date_to
5608 ,p_object_version_number => p_rec.object_version_number
5609 ,p_enterprise_id => p_rec.enterprise_id
5610 );
5611 --
5612 hr_utility.set_location(l_proc, 65);
5613 --
5614 -- Business Rule Mapping
5615 -- =====================
5616 -- CHK_NORMAL_ELAPSE_DURATION
5617 --
5618 chk_normal_elapse_duration
5619 (p_competence_element_id => p_rec.competence_element_id
5620 ,p_effective_date => p_effective_date
5621
5622 ,p_normal_elapse_duration => p_rec.normal_elapse_duration
5623 ,p_normal_elapse_duration_unit
5624 => p_rec.normal_elapse_duration_unit
5625 ,p_object_version_number => p_rec.object_version_number
5626 );
5627 --
5628 hr_utility.set_location(l_proc, 70);
5629 --
5630 --
5631 -- Business Rule Mapping
5632 -- =====================
5633 -- CHK_FOREIGN_KEYS
5634 --
5635
5636 chk_foreign_keys
5637 (p_competence_element_id => p_rec.competence_element_id
5638 ,p_object_version_number => p_rec.object_version_number
5639 ,p_business_group_id => p_rec.business_group_id
5640 ,p_enterprise_id => p_rec.enterprise_id
5641 ,p_competence_id => p_rec.competence_id
5642 ,p_assessment_id => p_rec.assessment_id
5643 ,p_assessment_type_id => p_rec.assessment_type_id
5644 ,p_activity_version_id => p_rec.activity_version_id
5645 ,p_organization_id => p_rec.organization_id
5646 ,p_job_id => p_rec.job_id
5647 ,p_valid_grade_id => p_rec.valid_grade_id
5648 ,p_position_id => p_rec.position_id
5649
5650 ,p_person_id => p_rec.person_id
5651 ,p_parent_competence_element_id
5652 => p_rec.parent_competence_element_id
5653 ,p_effective_date_to => p_rec.effective_date_to
5654 ,p_effective_date_from => p_rec.effective_date_from
5655 ,p_proficiency_level_id => p_rec.proficiency_level_id
5656 ,p_high_proficiency_level_id
5657 => p_rec.high_proficiency_level_id
5658 ,p_weighting_level_id => p_rec.weighting_level_id
5659 ,p_rating_level_id => p_rec.rating_level_id
5660 ,p_effective_date => p_effective_date
5661 ,p_type => p_rec.type
5662 ,p_party_id => p_rec.party_id -- HR/TCA merge
5663 ,p_qualification_type_id => p_rec.qualification_type_id
5664 );
5665
5666 --
5667 hr_utility.set_location(l_proc, 70);
5668 /*
5669 -- Business Rule Mapping
5670 -- =====================
5671 -- CHK_UNIQ_COMP_QUAL
5672 --
5673 chk_unique_comp_qual
5674 (p_competence_element_id => p_rec.competence_element_id
5675 ,p_competence_id => p_rec.competence_id
5676 ,p_qualification_type_id => p_rec.qualification_type_id
5677 ,p_object_version_number => p_rec.object_version_number
5678 ,p_effective_date => p_effective_date
5679 );
5680
5681 */
5682 hr_utility.set_location(l_proc, 80);
5683
5684 --
5685 -- do the descriptive flex validation.
5686 --
5687 per_cel_bus.chk_df(p_rec => p_rec);
5688
5689 hr_utility.set_location(l_proc, 80);
5690
5691 --
5692 -- do the developer descriptive flex validation.
5693 --
5694 per_cel_bus.chk_ddf(p_rec => p_rec);
5695
5696 hr_utility.set_location('Leaving:'||l_proc, 80);
5697 End update_validate;
5698 --
5699 -- ----------------------------------------------------------------------------
5700 -- |---------------------------< delete_validate >----------------------------|
5701 -- ----------------------------------------------------------------------------
5702 Procedure delete_validate(p_rec in per_cel_shd.g_rec_type) is
5703 --
5704 l_proc varchar2(72) := g_package||'delete_validate';
5705 --
5706 Begin
5707 hr_utility.set_location('Entering:'||l_proc, 5);
5708 --
5709 -- Call all supporting business operations
5710 -- Business Rule Mapping
5711 -- =====================
5712 -- CHK_COMP_ELEMENT_DELETE
5713 chk_comp_element_delete
5714
5715 (p_competence_element_id => per_cel_shd.g_old_rec.competence_element_id
5716 ,p_business_group_id => per_cel_shd.g_old_rec.business_group_id
5717 ,p_parent_competence_element_id
5718 => per_cel_shd.g_old_rec.parent_competence_element_id
5719 ,p_type => per_cel_shd.g_old_rec.type
5720 ,p_competence_type => per_cel_shd.g_old_rec.competence_type
5721 ,p_assessment_id => per_cel_shd.g_old_rec.assessment_id
5722 ,p_assessment_type_id => per_cel_shd.g_old_rec.assessment_type_id
5723 ,p_competence_id => per_cel_shd.g_old_rec.competence_id
5724 ,p_group_competence_type => per_cel_shd.g_old_rec.group_competence_type
5725 );
5726 --
5727
5728 hr_utility.set_location(' Leaving:'||l_proc, 10);
5729 End delete_validate;
5730 --
5731 -- ----------------------------------------------------------------------------
5732 -- |-----------------------< return_legislation_code >-------------------------|
5733 -- ----------------------------------------------------------------------------
5734 Function return_legislation_code
5735 ( p_competence_element_id in number
5736 ) return varchar2 is
5737 --
5738 -- Declare cursor
5739 --
5740 cursor csr_leg_code is
5741 select legislation_code
5742 from per_business_groups pbg,
5743 per_competence_elements pce
5744 where pce.competence_element_id = p_competence_element_id
5745 and pbg.business_group_id = pce.business_group_id;
5746
5747 l_proc varchar2(72) := g_package||'return_legislation_code';
5748 l_legislation_code varchar2(150);
5749 l_business_group_flag varchar2(1);
5750 --
5751 Begin
5752 hr_utility.set_location('Entering:'||l_proc, 5);
5753 --
5754 -- Ensure that all the mandatory parameters are not null
5755 --
5756 hr_api.mandatory_arg_error (p_api_name => l_proc,
5757 p_argument => 'competence_element_id',
5758 p_argument_value => p_competence_element_id );
5759 --
5760 Select 'Y' into l_business_group_flag
5761 from per_competence_elements
5762 where competence_element_id = p_competence_element_id
5763 and business_group_id is null;
5764
5765
5766 if l_business_group_flag = 'Y' then
5767 return null;
5768 end if;
5769
5770 if nvl(g_competence_element_id, hr_api.g_number) = p_competence_element_id then
5771 --
5772 -- The legislation code has already been found with a previous
5773 -- call to this function. Just return the value in the global
5774 -- variable.
5775 --
5776 l_legislation_code := g_legislation_code;
5777 hr_utility.set_location(l_proc, 20);
5778 else
5779 --
5780 -- The ID is different to the last call to this function
5781 -- or this is the first call to this function.
5782 --
5783 open csr_leg_code;
5784 fetch csr_leg_code into l_legislation_code;
5785 if csr_leg_code%notfound then
5786 close csr_leg_code;
5787 --
5788 -- The primary key is invalid therefore we must error out
5789 --
5790 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5791 hr_multi_message.add
5792 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ELEMENT_ID');
5793 hr_utility.raise_error;
5794 end if;
5795 --
5796 close csr_leg_code;
5797 g_competence_element_id:= p_competence_element_id;
5798 g_legislation_code := l_legislation_code;
5799 end if;
5800 return l_legislation_code;
5801 --
5802 hr_utility.set_location(' Leaving:'||l_proc, 10);
5803 --
5804 End return_legislation_code;
5805 --
5806 --
5807
5808 end per_cel_bus;