1 Package Body per_cel_bus as
2 /* $Header: pecelrhi.pkb 120.3 2006/03/28 05:27:21 arumukhe noship $ */
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 hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
3415 hr_utility.set_message_token('API_NAME', l_proc);
3416 hr_utility.set_message_token('ARGUMENT', 'object_id, object_name');
3417 end if;
3418 end if;
3419 --
3420 --
3421 -- Only proceed with validation if :
3422 -- a) The current g_old_rec is current and
3423
3424 -- b) The value for group_competence_type,competence_type,effective_date_from
3425 -- effective_date_to,high_proficiency_level_id,mandatory,normal_elapse
3426 -- ration ,normal_elapse_duration_unit,sequence_number,source_of_prof_level
3427
3428
3429 -- certification_date,certification_method,next_certification_date,
3430 -- proficiency_level_id,line_score
3431 --
3432 -- NOTE: Only updateable parameters are checked
3433 --
3434 --
3435 l_api_updating := per_cel_shd.api_updating
3436 (p_competence_element_id => p_competence_element_id
3437
3438 ,p_object_version_number => p_object_version_number);
3439
3440 --
3441 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.group_competence_type,
3442 hr_api.g_varchar2) <> nvl(p_group_competence_type,hr_api.g_varchar2)
3443 OR nvl(per_cel_shd.g_old_rec.competence_type,hr_api.g_varchar2) <>
3444 nvl(p_competence_type,hr_api.g_varchar2) OR
3445 nvl(per_cel_shd.g_old_rec.effective_date_from,hr_api.g_date) <>
3446 nvl(p_effective_date_from,hr_api.g_date) OR
3447 nvl(per_cel_shd.g_old_rec.effective_date_to,hr_api.g_date) <>
3448 nvl(p_effective_date_to,hr_api.g_date) OR
3449 nvl(per_cel_shd.g_old_rec.high_proficiency_level_id,hr_api.g_number) <>
3450 nvl(p_high_proficiency_level_id,hr_api.g_number) OR
3451 nvl(per_cel_shd.g_old_rec.mandatory,hr_api.g_varchar2) <>
3452 nvl(p_mandatory,hr_api.g_varchar2) OR
3453 nvl(per_cel_shd.g_old_rec.normal_elapse_duration,hr_api.g_number) <>
3454 nvl(p_normal_elapse_duration,hr_api.g_number) OR
3455 nvl(per_cel_shd.g_old_rec.normal_elapse_duration_unit,hr_api.g_varchar2)
3456 <> nvl(p_normal_elapse_duration_unit,hr_api.g_varchar2) OR
3457 nvl(per_cel_shd.g_old_rec.sequence_number,hr_api.g_number) <>
3458 nvl(p_sequence_number,hr_api.g_number) OR
3459 nvl(per_cel_shd.g_old_rec.certification_date,hr_api.g_date) <>
3460 nvl(p_certification_date,hr_api.g_date) OR
3461 nvl(per_cel_shd.g_old_rec.source_of_proficiency_level,
3462 hr_api.g_varchar2) <>
3463 nvl(p_source_of_proficiency_level,hr_api.g_varchar2) OR
3464 nvl(per_cel_shd.g_old_rec.certification_method,hr_api.g_varchar2) <>
3465 nvl(p_certification_method,hr_api.g_varchar2) OR
3466 nvl(per_cel_shd.g_old_rec.next_certification_date,hr_api.g_date) <>
3467 nvl(p_next_certification_date,hr_api.g_date)OR
3468 nvl(per_cel_shd.g_old_rec.proficiency_level_id,hr_api.g_number) <>
3469 nvl(p_proficiency_level_id,hr_api.g_number) OR
3470 nvl(per_cel_shd.g_old_rec.line_score,hr_api.g_number) <>
3471 nvl(p_line_score,hr_api.g_number) OR
3472 nvl(per_cel_shd.g_old_rec.qualification_type_id,hr_api.g_number) <>
3473 nvl(p_qualification_type_id,hr_api.g_number))
3474 OR NOT l_api_updating) then
3475 hr_utility.set_location(l_proc, 6);
3476 --
3477 -- Check the parameters status when the type is 'REQUIREMENT'
3478 --
3479 if p_type = 'REQUIREMENT' then
3480 -- type 'REQUIREMENT and object_name 'VACANCY' won't have org, job etc.
3481 if (p_object_name is not NULL AND
3482 p_object_name <> 'VACANCY') then
3483 if (p_organization_id is NULL AND
3484 p_job_id is null AND p_position_id IS NULL AND
3485 p_enterprise_id is null) then
3486 --
3487 hr_utility.set_location(l_proc, 7);
3488 hr_utility.set_message(801,'HR_51655_CEL_ORG_JOB_POS');
3489
3490 hr_utility.raise_error;
3491 --
3492 elsif ((p_organization_id is not null OR p_enterprise_id is not NULL)
3493 AND (p_valid_grade_id is not null) ) then
3494 hr_utility.set_location(l_proc, 8);
3495 hr_utility.set_message(800,'HR_52373_CEL_GRD_ID_MST_NULL');
3496 hr_multi_message.add
3497 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.VALID_GRADE_ID');
3498
3499 hr_utility.raise_error;
3500 --
3501 elsif ((p_enterprise_id is not null AND(p_organization_id
3502 is not null OR p_job_id is not null OR p_position_id is not null))
3503 OR
3504 (p_organization_id is not null AND(p_enterprise_id is not null
3505 OR p_job_id is not null OR p_position_id is not null))
3506 OR
3507 (p_job_id is not null AND(p_enterprise_id is not null OR
3508 p_organization_id is not null OR p_position_id is not null))
3509 OR
3510 (p_position_id is not null AND(p_enterprise_id is not null OR
3511 p_job_id is not null OR p_organization_id is not null))) then
3512 --
3513 hr_utility.set_location(l_proc, 10);
3514 hr_utility.set_message(801,'HR_51656_CEL_ORG_JOB_POS_ENT');
3515 hr_utility.raise_error;
3516 --
3517 elsif(p_assessment_id is not null
3518 OR p_assessment_type_id is not null
3519 OR p_activity_version_id is not null
3520 OR p_person_id is not null
3521 OR p_parent_competence_element_id is not null
3522 OR p_group_competence_type is not null
3523 OR p_competence_type is not null
3524 OR p_line_score is not null
3525 OR p_sequence_number is not null
3526 OR p_normal_elapse_duration is not null
3527 OR p_normal_elapse_duration_unit is not null
3528 OR p_source_of_proficiency_level is not null
3529 OR p_certification_date is not null
3530 OR p_certification_method is not null
3531 OR p_next_certification_date is not null
3532 OR p_weighting_level_id is not null
3533 OR p_rating_level_id is not null
3534 OR p_effective_date_from is NULL
3535 OR p_competence_id is NULL
3536 OR p_mandatory is null)
3537 then
3538 --
3539 hr_utility.set_location(l_proc, 15);
3540 hr_utility.set_message(801,'HR_51657_CEL_REQ_TYPE_ERROR');
3541 hr_utility.raise_error;
3542 --
3543 end if;
3544 end if;
3545 --
3546 elsif (p_type = 'ASSESSMENT') then
3547 --
3548 if (p_assessment_id is NULL
3549 OR p_competence_id is NULL
3550 OR p_effective_date_from is NULL
3551 OR p_assessment_type_id is not null
3552 OR p_organization_id is not null
3553 OR p_job_id is not null
3554 OR p_valid_grade_id is not null
3555 OR p_enterprise_id is not null
3556 OR p_position_id is not null
3557 OR p_activity_version_id is not null
3558 OR p_person_id is not null
3559 OR p_group_competence_type is not null
3560 OR p_competence_type is not null
3561 OR p_high_proficiency_level_id is not null
3562 OR p_mandatory is not null
3563 OR p_normal_elapse_duration is not null
3564 OR p_normal_elapse_duration_unit is not null
3565 OR p_sequence_number is not null
3566 OR p_source_of_proficiency_level is not null
3567 OR p_certification_date is not null
3568 OR p_certification_method is not null
3569 OR p_next_certification_date is not null
3570 )
3571 then
3572 --
3573 hr_utility.set_location(l_proc, 20);
3574 hr_utility.set_message(801,'HR_51658_CEL_ASS_TYPE_ERROR');
3575 hr_utility.raise_error;
3576 --
3577 end if;
3578 --
3579 elsif (p_type = 'ASSESSMENT_GROUP') then
3580 if (p_assessment_type_id is NULL
3581 OR p_group_competence_type is NULL
3582 OR p_competence_id is not NULL
3583 OR p_parent_competence_element_id is not null
3584 OR p_organization_id is not null
3585 OR p_job_id is not null
3586 OR p_valid_grade_id is not null
3587 OR p_enterprise_id is not null
3588 OR p_position_id is not null
3589 OR p_activity_version_id is not null
3590 OR p_person_id is not null
3591 OR p_effective_date_from is not null
3592 OR p_effective_date_to is not null
3593 OR p_high_proficiency_level_id is not null
3594 OR p_mandatory is not null
3595 OR p_normal_elapse_duration is not null
3596 OR p_normal_elapse_duration_unit is not null
3597 OR p_source_of_proficiency_level is not null
3598 OR p_certification_date is not null
3599 OR p_certification_method is not null
3600 OR p_next_certification_date is not null
3601 OR p_proficiency_level_id is not null
3602 OR p_competence_type is not null
3603 OR p_assessment_id is not null
3604 OR p_weighting_level_id is not null
3605 OR p_rating_level_id is not null
3606 )
3607 then
3608 --
3609 hr_utility.set_location(l_proc, 25);
3610 hr_utility.set_message(801,'HR_51659_CEL_ASS_GRP_ERROR');
3611 hr_utility.raise_error;
3612 --
3613 end if;
3614 elsif (p_type = 'ASSESSMENT_COMPETENCE') then
3615 if(p_parent_competence_element_id IS NOT NULL) then
3616 open csr_parent_comp_element;
3617 fetch csr_parent_comp_element into l_exists;
3618 if csr_parent_comp_element%notfound then
3619 close csr_parent_comp_element;
3620 --
3621 -- raise an error message because the
3622 -- parent_competence_element type is not
3623 -- 'ASSESSMENT_GROUP'
3624 --
3625 hr_utility.set_location(l_proc, 30);
3626 hr_utility.set_message(801,'HR_51660_CEL_ASS_COMP_ERROR');
3627 hr_utility.raise_error;
3628 end if;
3629 close csr_parent_comp_element;
3630 end if;
3631 if ((p_assessment_type_id is null AND p_parent_competence_element_id
3632 is NULL )
3633 OR
3634
3635 (p_assessment_type_id is not null AND
3636 p_parent_competence_element_id is not null )) then
3637 --
3638 hr_utility.set_location(l_proc, 35);
3639 hr_utility.set_message(801,'HR_51662_CEL_ASS_COMP_MUTA');
3640 hr_utility.raise_error;
3641 --
3642 end if;
3643 if(p_assessment_id is not null
3644 OR p_competence_id is NULL
3645 OR p_effective_date_from is not NULL
3646 OR p_effective_date_to is not NULL
3647 OR p_enterprise_id is not null
3648 OR p_organization_id is not null
3649 OR p_job_id is not null
3650 OR p_valid_grade_id is not null
3651 OR p_position_id is not null
3652 OR p_activity_version_id is not null
3653 OR p_person_id is not null
3654 OR p_group_competence_type is not null
3655 OR p_high_proficiency_level_id is not null
3656 OR p_mandatory is not null
3657 OR p_normal_elapse_duration is not null
3658 OR p_normal_elapse_duration_unit is not null
3659 OR p_source_of_proficiency_level is not null
3660 OR p_certification_date is not null
3661 OR p_certification_method is not null
3662 OR p_next_certification_date is not null
3663 OR p_proficiency_level_id is not null
3664 OR p_competence_type is not null
3665 OR p_weighting_level_id is not null
3666 OR p_rating_level_id is not null
3667 )
3668 then
3669 --
3670 hr_utility.set_location(l_proc, 40);
3671 hr_utility.set_message(801,'HR_51663_CEL_ASS_COMP_ERR');
3672 hr_utility.raise_error;
3673
3674 --
3675 end if;
3676 --
3677 elsif (p_type = 'COMPETENCE_USAGE') then
3678 if ( p_competence_type is NULL
3679 OR p_competence_id is NULL
3680 OR p_effective_date_from is not NULL
3681 OR p_effective_date_to is not null
3682 OR p_organization_id is not null
3683 OR p_job_id is not null
3684 OR p_valid_grade_id is not null
3685 OR p_position_id is not null
3686 OR p_enterprise_id is not null
3687 OR p_activity_version_id is not null
3688 OR p_person_id is not null
3689 OR p_parent_competence_element_id is not null
3690 OR p_group_competence_type is not null
3691 OR p_high_proficiency_level_id is not null
3692 OR p_mandatory is not null
3693 OR p_normal_elapse_duration is not null
3694 OR p_normal_elapse_duration_unit is not null
3695 OR p_sequence_number is not null
3696 OR p_source_of_proficiency_level is not null
3697 OR p_certification_date is not null
3698 OR p_certification_method is not null
3699 OR p_next_certification_date is not null
3700 OR p_proficiency_level_id is not null
3701 OR p_assessment_id is not null
3702 OR p_assessment_type_id is not null
3703 OR p_weighting_level_id is not null
3704 OR p_rating_level_id is not null
3705 )
3706 then
3707 --
3708 hr_utility.set_location(l_proc, 45);
3709 hr_utility.set_message(801,'HR_51664_CEL_COMP_USG_ERR');
3710 hr_utility.raise_error;
3711 --
3712 end if;
3713
3714 --
3715 elsif (p_type = 'DELIVERY') then
3716 if (p_activity_version_id is NULL)
3717 then
3718 --
3719 hr_utility.set_location(l_proc, 50);
3720 hr_utility.set_message(801,'HR_51665_CEL_DELVR_ERROR');
3721 hr_utility.raise_error;
3722 --
3723 elsif(p_assessment_id is not null
3724 OR p_assessment_type_id is not null
3725 OR p_organization_id is not null
3726 OR p_job_id is not null
3727 OR p_valid_grade_id is not null
3728 OR p_mandatory is not null
3729 OR p_position_id is not null
3730 OR p_enterprise_id is not null
3731 OR p_person_id is not null
3732 OR p_high_proficiency_level_id is not null
3733 OR p_competence_type is not null
3734 OR p_normal_elapse_duration is not null
3735 OR p_normal_elapse_duration_unit is not null
3736 OR p_sequence_number is not null
3737 OR p_source_of_proficiency_level is not null
3738 OR p_certification_date is not null
3739 OR p_certification_method is not null
3740 OR p_next_certification_date is not null
3741 OR p_weighting_level_id is not null
3742 OR p_rating_level_id is not null
3743 OR p_parent_competence_element_id is not null
3744 OR p_group_competence_type is not null
3745 OR p_competence_id is NULL
3746 OR p_effective_date_from is NULL
3747 )
3748 then
3749 --
3750 hr_utility.set_location(l_proc, 55);
3751 hr_utility.set_message(801,'HR_51666_CEL_DELVR_ERROR');
3752
3753 hr_utility.raise_error;
3754 --
3755 end if;
3756 --
3757 -- Note: The PREREQUISITE type is the same as the DELIVERY
3758 -- But we have included this so that we are able to modify it
3759 -- for the future versions.
3760 --
3761 elsif (p_type = 'PREREQUISITE') then
3762 if ( p_activity_version_id is NULL)
3763 then
3764 --
3765 hr_utility.set_location(l_proc, 60);
3766
3767 hr_utility.set_message(801,'HR_51667_CEL_PRE_REQ_MUTA');
3768 hr_utility.raise_error;
3769 --
3770 elsif(p_assessment_id is not null
3771 OR p_assessment_type_id is not null
3772 OR p_organization_id is not null
3773 OR p_job_id is not null
3774 OR p_valid_grade_id is not null
3775 OR p_position_id is not null
3776 OR p_enterprise_id is not null
3777 OR p_person_id is not null
3778 OR p_high_proficiency_level_id is not null
3779 OR p_competence_type is not null
3780 OR p_normal_elapse_duration is not null
3781 OR p_normal_elapse_duration_unit is not null
3782 OR p_sequence_number is not null
3783 OR p_source_of_proficiency_level is not null
3784 OR p_certification_date is not null
3785 OR p_certification_method is not null
3786 OR p_next_certification_date is not null
3787 OR p_weighting_level_id is not null
3788 OR p_rating_level_id is not null
3789 OR p_parent_competence_element_id is not null
3790 OR p_group_competence_type is not null
3791 OR p_competence_id is NULL
3792 OR p_effective_date_from is NULL
3793 OR p_mandatory is NULL
3794 )
3795 then
3796 --
3797 hr_utility.set_location(l_proc, 65);
3798 hr_utility.set_message(801,'HR_51668_CEL_PRE_REQ_ERR');
3799 hr_utility.raise_error;
3800 --
3801 end if;
3802 --
3803 elsif (p_type = 'PATH') then
3804 if (p_sequence_number is NULL
3805 OR p_competence_id is not null
3806 OR p_competence_type is not null
3807 OR p_assessment_id is not null
3808 OR p_assessment_type_id is not null
3809 OR p_organization_id is not null
3810 OR p_job_id is not null
3811 OR p_valid_grade_id is not null
3812 OR p_position_id is not null
3813 OR p_activity_version_id is not null
3814 OR p_person_id is not null
3815 OR p_high_proficiency_level_id is not null
3816 OR p_proficiency_level_id is not null
3817 OR p_effective_date_from is not null
3818 OR p_effective_date_to is not null
3819 OR p_mandatory is not null
3820 OR p_source_of_proficiency_level is not null
3821 OR p_certification_date is not null
3822 OR p_certification_method is not null
3823 OR p_next_certification_date is not null
3824 OR p_weighting_level_id is not null
3825 OR p_rating_level_id is not null
3826 OR p_parent_competence_element_id is not null
3827 OR p_group_competence_type is not null
3828 )
3829
3830 then
3831 --
3832 hr_utility.set_location(l_proc, 70);
3833 hr_utility.set_message(801,'HR_51669_CEL_PATH_ERROR');
3834 hr_utility.raise_error;
3835 --
3836 end if;
3837 --
3838 elsif (p_type = 'PERSONAL') then
3839 if ((p_person_id is NULL and p_party_id is NULL) -- HR/TCA merge
3840 OR p_competence_id is NULL
3841 OR p_effective_date_from is NULL
3842 OR p_competence_type is not null
3843 OR p_assessment_id is not null
3844 OR p_assessment_type_id is not null
3845 OR p_activity_version_id is not null
3846 OR p_enterprise_id is not null
3847 OR p_organization_id is not null
3848 OR p_job_id is not null
3849 OR p_valid_grade_id is not null
3850 OR p_position_id is not null
3851 OR p_parent_competence_element_id is not null
3852 OR p_group_competence_type is not null
3853 OR p_high_proficiency_level_id is not null
3854 OR p_mandatory is not null
3855 OR p_normal_elapse_duration is not null
3856 OR p_normal_elapse_duration_unit is not null
3857 OR p_sequence_number is not null
3858 OR p_weighting_level_id is not null
3859 OR p_rating_level_id is not null
3860 OR p_competence_type is not null
3861 )
3862 then
3863 --
3864 hr_utility.set_location(l_proc, 75);
3865 hr_utility.set_message(801,'HR_51670_CEL_PER_TYPE_ERROR');
3866 hr_multi_message.add
3867 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.EFFECTIVE_DATE_FROM');
3868 hr_utility.raise_error;
3869 --
3870 end if;
3871 --
3872 elsif (p_type = 'PROPOSAL') then
3873 if(p_competence_id is NULL
3874 OR p_effective_date_from is NULL
3875 OR p_mandatory is NULL
3876 OR p_assessment_id is not null
3877 OR p_assessment_type_id is not null
3878 OR p_organization_id is not null
3879 OR p_job_id is not null
3880 OR p_valid_grade_id is not null
3881 OR p_enterprise_id is not null
3882 OR p_activity_version_id is not null
3883 OR p_position_id is not null
3884 OR p_person_id is not null
3885 OR p_parent_competence_element_id is not null
3886 OR p_group_competence_type is not null
3887 OR p_high_proficiency_level_id is not null
3888 OR p_competence_type is not null
3889 OR p_normal_elapse_duration is not null
3890 OR p_normal_elapse_duration_unit is not null
3891 OR p_sequence_number is not null
3892 OR p_source_of_proficiency_level is not null
3893 OR p_weighting_level_id is not null
3894 OR p_rating_level_id is not null
3895
3896 )
3897 then
3898 --
3899 hr_utility.set_location(l_proc, 85);
3900 hr_utility.set_message(801,'HR_51672_CEL_PRO_TYPE_ERROR');
3901 hr_utility.raise_error;
3902 --
3903 end if;
3904 --
3905 elsif (p_type = 'SET') then
3906 if (p_competence_id is NULL
3907 OR p_effective_date_from is not NULL
3908 OR p_assessment_id is not null
3909 OR p_assessment_type_id is not null
3910 OR p_organization_id is not null
3911 OR p_job_id is not null
3912 OR p_valid_grade_id is not null
3913 OR p_position_id is not null
3914 OR p_enterprise_id is not null
3915 OR p_activity_version_id is not null
3916 OR p_person_id is not null
3917 OR p_parent_competence_element_id is not null
3918 OR p_group_competence_type is not null
3919 OR p_high_proficiency_level_id is not null
3920 OR p_proficiency_level_id is not null
3921 OR p_source_of_proficiency_level is not null
3922 OR p_certification_date is not null
3923 OR p_certification_method is not null
3924 OR p_next_certification_date is not null
3925 OR p_mandatory is not null
3926 OR p_competence_type is not null
3927 OR p_normal_elapse_duration is not null
3928 OR p_normal_elapse_duration_unit is not null
3929 OR p_sequence_number is not null
3930 OR p_weighting_level_id is not null
3931 OR p_rating_level_id is not null
3932 )
3933 then
3934
3935 --
3936 hr_utility.set_location(l_proc, 90);
3937 hr_utility.set_message(801,'HR_51673_CEL_SET_TYPE_ERROR');
3938 hr_utility.raise_error;
3939 --
3940 end if;
3941 elsif (p_type = 'QUALIFICATION') then
3942 hr_utility.trace('date_from : ' || p_effective_date_from);
3943 hr_utility.trace('date_to : ' || p_effective_date_to);
3944 hr_utility.set_location(l_proc, 100);
3945
3946 --
3947 -- Mandatory parameter check
3948 --
3949 hr_api.mandatory_arg_error
3950 (p_api_name => l_proc,
3951 p_argument => 'qualification_type_id',
3952 p_argument_value => p_qualification_type_id);
3953
3954 hr_api.mandatory_arg_error
3955 (p_api_name => l_proc,
3956 p_argument => 'effective_date_from',
3957 p_argument_value => p_effective_date_from);
3958 --
3959 if (NOT l_api_updating) then
3960 hr_utility.set_location(l_proc, 103);
3961 --
3962 -- Mandatory parameter check
3963 --
3964 hr_api.mandatory_arg_error
3965 (p_api_name => l_proc,
3966 p_argument => 'competence_id',
3967 p_argument_value => p_competence_id);
3968
3969 open csr_comp_qual_link;
3970 fetch csr_comp_qual_link into l_exists;
3971 if csr_comp_qual_link%FOUND then
3972 close csr_comp_qual_link;
3973 hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3974 hr_utility.raise_error;
3975 end if;
3976 close csr_comp_qual_link;
3977 else
3978 hr_utility.set_location(l_proc, 105);
3979
3980 open csr_upd_comp_qual_link;
3981 fetch csr_upd_comp_qual_link into l_exists;
3982 if csr_upd_comp_qual_link%FOUND then
3983 close csr_upd_comp_qual_link;
3984 hr_utility.set_message(800,'HR_449136_QUA_FWK_LINK_EXISTS');
3985 hr_utility.raise_error;
3986 end if;
3987 close csr_upd_comp_qual_link;
3988 end if;
3989 end if;
3990 hr_utility.set_location('Leaving: ' || l_proc, 110);
3991 end if;
3992 end if; -- check for no_exclusive_error for TYPE.
3993 exception
3994 when app_exception.application_exception then
3995 if hr_multi_message.exception_add
3996 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE'
3997 ) then
3998 hr_utility.set_location(' Leaving:'||l_proc,110);
3999 raise;
4000 end if;
4001 hr_utility.set_location(' Leaving:'||l_proc,120);
4002 end chk_type_and_validation;
4003 --
4004 --
4005 --
4006 -- --------------------------------------------------------------------------
4007 -- |----------------< Chk_unique_competence_element >-----------------------|
4008 -----------------------------------------------------------------------------
4009 -- Description;
4010 -- It validates that the competence element is unique for any of the foreign
4011 -- key relationships.
4012 --
4013 -- Pre-Conditions:
4014 -- None
4015 --
4016 -- In Arguments:
4017 -- p_competence_element_id
4018 -- p_object_version_number
4019 -- p_business_group_id
4020 -- p_enterprise_id
4021 -- p_type
4022 -- p_competence_id
4023 -- p_assessment_id
4024 -- p_assessment_type_id
4025 -- p_activity_version_id
4026 -- p_organization_id
4027 -- p_job_id
4028 -- p_valid_grade_id
4029 -- p_position_id
4030 -- p_person_id
4031 -- p_parent_competence_element_id
4032 -- p_group_competence_type
4033 -- p_effective_date_from
4034 -- p_competence_type
4035 -- p_party_id -- HR/TCA merge
4036 --
4037 -- Post Success:
4038 -- Processing continues if:
4039 -- - The value of the in arguments don't violate the uniqueness
4040 -- test.
4041 --
4042 --
4043 -- Post Failure:
4044 -- An application error is raised and processing is terminated if any
4045
4046 -- - The value entered are not unique.
4047 --
4048 --
4049 -- Access Status:
4050 -- Internal Table Handler Use Only.
4051 --
4052 --
4053 --
4054 --
4055 procedure chk_unique_competence_element
4056 (p_competence_element_id
4057 in per_competence_elements.competence_element_ID%TYPE
4058 ,p_object_version_number
4059 in per_competence_elements.object_version_number%TYPE
4060 ,p_business_group_id
4061 in per_competence_elements.business_group_id%TYPE
4062 ,p_enterprise_id
4063 in per_competence_elements.enterprise_id%TYPE
4064 ,p_type in per_competence_elements.type%TYPE
4065 ,p_competence_id in per_competence_elements.competence_id%TYPE
4066 ,p_assessment_id in per_competence_elements.assessment_id%TYPE
4067 ,p_assessment_type_id
4068 in per_competence_elements.assessment_type_id%TYPE
4069 ,p_activity_version_id
4070 in per_competence_elements.activity_version_id%TYPE
4071 ,p_organization_id
4072 in per_competence_elements.organization_id%TYPE
4073 ,p_job_id in per_competence_elements.job_id%TYPE
4074 ,p_valid_grade_id in per_competence_elements.valid_grade_id%TYPE
4075 ,p_position_id
4076 in per_competence_elements.position_id%TYPE
4077 ,p_person_id in per_competence_elements.person_id%TYPE
4078 ,p_parent_competence_element_id
4079 in per_competence_elements.parent_competence_element_id%TYPE
4080 ,p_group_competence_type
4081 in per_competence_elements.group_competence_type%TYPE
4082 ,p_effective_date_from in per_competence_elements.effective_date_from%TYPE
4083 ,p_competence_type in per_competence_elements.competence_type%TYPE
4084 ,p_object_name in per_competence_elements.object_name%type
4085 ,p_object_id in per_competence_elements.object_id%type
4086 ,p_party_id in per_competence_elements.party_id%type -- HR/TCA merge
4087 ,p_qualification_type_id in per_competence_elements.qualification_type_id%type
4088 ) is
4089 --
4090 l_proc varchar2(72):=
4091 g_package||'chk_unique_competence_element';
4092 l_sql_stmt VARCHAR2(1500);
4093 l_api_updating boolean;
4094 l_exists varchar2(1);
4095 --
4096 begin
4097 hr_utility.set_location('Entering:'|| l_proc, 1);
4098 if hr_multi_message.no_exclusive_error
4099 (p_check_column1 => 'PER_COMPETENCE_ELEMENTS.TYPE') then
4100 --
4101 -- Check mandatory parameters have being set.
4102 --
4103 hr_api.mandatory_arg_error
4104 (p_api_name => l_proc
4105 ,p_argument => 'type'
4106 ,p_argument_value => p_type
4107 );
4108 -- mandatory parameter
4109 --
4110 -- ngundura this check should not be there for global competence elements
4111 if per_cel_shd.g_bus_grp then
4112 hr_api.mandatory_arg_error
4113 (p_api_name => l_proc
4114 ,p_argument => 'business_group_id'
4115 ,p_argument_value => p_business_group_id
4116 );
4117 end if;
4118 --
4119 --
4120 -- Only proceed with validation if :
4121 -- a) The current g_old_rec is current and
4122 -- b) The value for group_competence_type,competence_type,effective_date_from
4123 -- or any of the above in arguments has changed.
4124 --
4125 --
4126 l_api_updating := per_cel_shd.api_updating
4127 (p_competence_element_id => p_competence_element_id
4128 ,p_object_version_number => p_object_version_number);
4129 --
4130 if (l_api_updating AND (nvl(per_cel_shd.g_old_rec.group_competence_type,
4131 hr_api.g_varchar2) <> nvl(p_group_competence_type,hr_api.g_varchar2)
4132 OR nvl(per_cel_shd.g_old_rec.competence_type,hr_api.g_varchar2) <>
4133 nvl(p_competence_type,hr_api.g_varchar2) OR
4134 nvl(per_cel_shd.g_old_rec.effective_date_from,hr_api.g_date) <>
4135 nvl(p_effective_date_from,hr_api.g_date))
4136 OR NOT l_api_updating) then
4137 hr_utility.set_location(l_proc, 6);
4138 --
4139 -- build the NATIVE dynamic SQL
4140 -- note: native dynamic SQL has been used for performance
4141 l_sql_stmt := 'SELECT NULL '||
4142 'FROM per_competence_elements '||
4143 'WHERE business_group_id = :p_business_group_id '||
4144 'AND type = :p_type ';
4145 -- evaluate each bind determining the predicate
4146 -- note: if the bind is null we still add a predicate
4147 -- because the USING clause is not dynamic but will be faster than
4148 -- using DBMS_SQL.BIND calls.
4149 IF p_parent_competence_element_id IS NOT NULL THEN
4150 l_sql_stmt := l_sql_stmt||
4151 'AND parent_competence_element_id = :p_parent_competence_element_id ';
4152 ELSE
4153 l_sql_stmt := l_sql_stmt||
4154 'AND :p_parent_competence_element_id IS NULL /* p_parent_competence_element_id IS NULL*/ ';
4155 END IF;
4156 --
4157 IF p_competence_id IS NOT NULL THEN
4158 l_sql_stmt := l_sql_stmt|| 'AND competence_id = :p_competence_id ';
4159 ELSE
4160 l_sql_stmt := l_sql_stmt||
4161 'AND :p_competence_id IS NULL /* p_competence_id IS NULL */';
4162 END IF;
4163 --
4164 IF p_person_id IS NOT NULL THEN
4165 l_sql_stmt := l_sql_stmt|| 'AND person_id = :p_person_id ';
4166 ELSE
4167 l_sql_stmt := l_sql_stmt||
4168 'AND :p_person_id IS NULL /* p_person_id IS NULL */ ';
4169 END IF;
4170 --
4171 IF p_job_id IS NOT NULL THEN
4172 l_sql_stmt := l_sql_stmt|| 'AND job_id = :p_job_id ';
4173 ELSE
4174 l_sql_stmt := l_sql_stmt||
4175 'AND :p_job_id IS NULL /* p_job_id IS NULL */ ';
4176 END IF;
4177 --
4178 IF p_valid_grade_id IS NOT NULL THEN
4179 l_sql_stmt := l_sql_stmt|| 'AND valid_grade_id = :p_valid_grade_id ';
4180 ELSE
4181 l_sql_stmt := l_sql_stmt||
4182 'AND :p_valid_grade_id IS NULL /* p_valid_grade_id IS NULL*/';
4183 END IF;
4184 --
4185 IF p_position_id IS NOT NULL THEN
4186 l_sql_stmt := l_sql_stmt|| 'AND position_id = :p_position_id ';
4187 ELSE
4188 l_sql_stmt := l_sql_stmt||
4189 'AND :p_position_id IS NULL /* p_position_id IS NULL */ ';
4190 END IF;
4191 --
4192 IF p_enterprise_id IS NOT NULL THEN
4193 l_sql_stmt := l_sql_stmt|| 'AND enterprise_id = :p_enterprise_id ';
4194 ELSE
4195 l_sql_stmt := l_sql_stmt||
4196 'AND :p_enterprise_id IS NULL /* p_enterprise_id IS NULL */';
4197 END IF;
4198 --
4199 IF p_organization_id IS NOT NULL THEN
4200 l_sql_stmt := l_sql_stmt|| 'AND organization_id = :p_organization_id ';
4201 ELSE
4202 l_sql_stmt := l_sql_stmt||
4203 'AND :p_organization_id IS NULL /* p_organization_id IS NULL*/';
4204 END IF;
4205 --
4206 IF p_activity_version_id IS NOT NULL THEN
4207 l_sql_stmt := l_sql_stmt||
4208 'AND activity_version_id = :p_activity_version_id ';
4209 ELSE
4210 l_sql_stmt := l_sql_stmt||
4211 'AND :p_activity_version_id IS NULL /* p_activity_version_id IS NULL */ ';
4212 END IF;
4213 --
4214 IF p_assessment_id IS NOT NULL THEN
4215 l_sql_stmt := l_sql_stmt|| 'AND assessment_id = :p_assessment_id ';
4216 ELSE
4217 l_sql_stmt := l_sql_stmt||
4218 'AND :p_assessment_id IS NULL /* p_assessment_id IS NULL */ ';
4219 END IF;
4220 --
4221 IF p_assessment_type_id IS NOT NULL THEN
4222 l_sql_stmt := l_sql_stmt|| 'AND assessment_type_id = :p_assessment_type_id ';
4223 ELSE
4224 l_sql_stmt := l_sql_stmt||
4225 'AND :p_assessment_type_id IS NULL /* p_assessment_type_id IS NULL */ ';
4226 END IF;
4227 --
4228 IF p_effective_date_from IS NOT NULL THEN
4229 l_sql_stmt := l_sql_stmt|| 'AND effective_date_from = :p_effective_date_from ';
4230 ELSE
4231 l_sql_stmt := l_sql_stmt||
4232 'AND :p_effective_date_from IS NULL /* p_effective_date_from IS NULL */ ';
4233 END IF;
4234 --
4235 IF p_group_competence_type IS NOT NULL THEN
4236 l_sql_stmt := l_sql_stmt||
4237 'AND group_competence_type = :p_group_competence_type ';
4238 ELSE
4239 l_sql_stmt := l_sql_stmt||
4240 'AND :p_group_competence_type IS NULL /* p_group_competence_type IS NULL */ ';
4241 END IF;
4242 --
4243 IF p_competence_type IS NOT NULL THEN
4244 l_sql_stmt := l_sql_stmt||
4245 'AND competence_type = :p_competence_type ';
4246 ELSE
4247 l_sql_stmt := l_sql_stmt||
4248 'AND :p_competence_type IS NULL /* p_competence_type IS NULL */ ';
4249 END IF;
4250 --
4251 IF p_object_id IS NOT NULL THEN
4252 l_sql_stmt := l_sql_stmt|| 'AND object_id = :p_object_id ';
4253 ELSE
4254 l_sql_stmt := l_sql_stmt||
4255 'AND :p_object_id IS NULL /* p_object_id IS NULL */ ';
4256 END IF;
4257 --
4258 IF p_object_name IS NOT NULL THEN
4259 l_sql_stmt := l_sql_stmt|| 'AND object_name = :p_object_name ';
4260 ELSE
4261 l_sql_stmt := l_sql_stmt||
4262 'AND :p_object_name IS NULL /* p_object_name IS NULL */ ';
4263 END IF;
4264 --
4265 IF p_party_id IS NOT NULL THEN -- HR/TCA merge
4266 l_sql_stmt := l_sql_stmt|| 'AND party_id = :p_party_id ';
4267 ELSE
4268 l_sql_stmt := l_sql_stmt||
4269 'AND :p_party_id IS NULL /* p_party_id IS NULL */ ';
4270 END IF;
4271 --
4272 IF p_qualification_type_id IS NOT NULL THEN -- BUG3356369
4273 l_sql_stmt := l_sql_stmt|| 'AND qualification_type_id = :p_qualification_type_id ';
4274 ELSE
4275 l_sql_stmt := l_sql_stmt||
4276 'AND :p_qualification_type_id IS NULL /* qualification_type_id IS NULL */ ';
4277 END IF;
4278 hr_utility.set_location(l_proc,10);
4279 --hr_utility.trace('l_sql_stmt : ' || l_sql_stmt);
4280 -- dynamically execute the SQL
4281 BEGIN
4282 EXECUTE IMMEDIATE l_sql_stmt
4283 INTO l_exists
4284 USING p_business_group_id,
4285 p_type,
4286 p_parent_competence_element_id,
4287 p_competence_id,
4288 p_person_id,
4289 p_job_id,
4290 p_valid_grade_id,
4291 p_position_id,
4292 p_enterprise_id,
4293 p_organization_id,
4294 p_activity_version_id,
4295 p_assessment_id,
4296 P_assessment_type_id,
4297 p_effective_date_from,
4298 p_group_competence_type,
4299 p_competence_type,
4300 p_object_id,
4301 p_object_name,
4302 p_party_id, -- HR/TCA merge
4303 p_qualification_type_id;
4304 -- executed successful therefore a row has been found
4305 if p_type = 'COMPETENCE_USAGE' then
4306 hr_utility.set_message(800,'HR_52262_CEL_UNIQUE_COMP_USAGE');
4307 elsif p_type = 'ASSESSMENT' then
4308 hr_utility.set_message(800,'HR_52263_CEL_UNIQUE_ASSESSMENT');
4309 elsif p_type = 'ASSESSMNET_COMPETENCE' then
4310 hr_utility.set_message(800,'HR_52264_CEL_UNIQUE_ASM_COMP');
4311 elsif p_type = 'ASSESSMENT_GROUP' then
4312 hr_utility.set_message(800,'HR_52265_CEL_UNIQUE_ASM_GROUP');
4313 elsif p_type = 'REQUIREMENT' then
4314 hr_utility.set_message(800,'HR_52266_CEL_UNIQUE_REQUIREMEN');
4315 elsif p_type = 'DELIVERY' then
4316 hr_utility.set_message(800,'HR_52267_CEL_UNIQUE_DELIVERY');
4317 elsif p_type = 'PERSONAL' then
4318 hr_utility.set_message(800,'HR_52268_CEL_UNIQUE_PERSONAL');
4319 else
4320 hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4321 end if;
4322 --
4323 hr_utility.raise_error;
4324 EXCEPTION
4325 WHEN NO_DATA_FOUND THEN
4326 null;
4327 END;
4328 end if;
4329 hr_utility.set_location(l_proc,7);
4330 --
4331 end if; -- no_exclusive_check for TYPE
4332 hr_utility.set_location('Leaving: ' || l_proc, 10);
4333 exception
4334 when app_exception.application_exception then
4335 hr_multi_message.add;
4336 hr_utility.set_location(' Leaving:'||l_proc,105);
4337 end chk_unique_competence_element;
4338 /*
4339 --
4340 -- ----------------------------------------------------------------------------
4341 -- |-----------------------< CHK_unique_comp_qual >------------------------------|
4342 -- ----------------------------------------------------------------------------
4343 --
4344 -- Description
4345 -- This procedure checks that a combination of competence_id and
4346 -- qualification_type_id is unique.
4347 --
4348 -- Pre-Conditions
4349 -- None.
4350 --
4351 -- In Parameters
4352 -- p_competence_element_id
4353 -- p_competence_id
4354 -- p_qualification_type_id
4355 -- p_object_version_number
4356 -- p_effective_date
4357 --
4358 -- Post Success
4359 -- Processing continues
4360 --
4361 -- Post Failure
4362 -- Error raised.
4363 --
4364 -- Access Status
4365 -- Internal table handler use only.
4366 --
4367 Procedure chk_unique_comp_qual(p_competence_element_id in number
4368 ,p_competence_id in number
4369 ,p_qualification_type_id in number
4370 ,p_object_version_number in number
4371 ,p_effective_date in date) is
4372 --
4373 l_proc varchar2(72) := g_package||'chk_unique_comp_qual';
4374 l_api_updating boolean;
4375 l_exists varchar2(1);
4376 --
4377 cursor csr_unique_comp_qual is
4378 select 'x'
4379 from per_competence_elements
4380 where type = 'QUALIFICATION'
4381 and competence_id = p_competence_id
4382 and qualification_type_id = p_qualification_type_id
4383 and p_effective_date between effective_date_from
4384 and nvl(effective_date_to,hr_api.g_eot);
4385 Begin
4386 --
4387 hr_utility.set_location('Entering:'||l_proc,10);
4388 if p_qualification_type_id is not NULL and p_competence_id is not NULL
4389 then
4390 --
4391 -- Only proceed with validation if :
4392 -- a) The current g_old_rec is current and
4393 -- b) The value for competence_id or qualification_type_id have changed
4394 --
4395 l_api_updating := per_cel_shd.api_updating
4396 (p_competence_element_id => p_competence_element_id
4397 ,p_object_version_number => p_object_version_number);
4398 --
4399 if (l_api_updating
4400 and nvl(per_cel_shd.g_old_rec.competence_id,
4401 hr_api.g_number) = nvl(p_competence_id, hr_api.g_number)
4402 and nvl(per_cel_shd.g_old_rec.qualification_type_id,hr_api.g_number)
4403 = nvl(p_qualification_type_id, hr_api.g_number)
4404 ) then
4405 hr_utility.set_location('Leaving.... ' || l_proc,20);
4406 return;
4407 end if;
4408
4409 hr_utility.set_location(l_proc,20);
4410
4411 open csr_unique_comp_qual;
4412 fetch csr_unique_comp_qual into l_exists;
4413 if csr_unique_comp_qual%found then
4414 close csr_unique_comp_qual;
4415 --
4416 hr_utility.set_message(801,'HR_51674_CEL_COMP_UNIQ_ERROR');
4417 hr_utility.raise_error;
4418 --
4419 end if;
4420 close csr_unique_comp_qual;
4421 end if;
4422
4423 hr_utility.set_location('Leaving:'||l_proc,30);
4424 --
4425 End chk_unique_comp_qual;
4426 */
4427 --
4428 -- ---------------------------------------------------------------------------
4429 -- |----------------< CHK_COMP_ELEMENT_DELETE >------------------------------|
4430 -----------------------------------------------------------------------------
4431 --
4432 -- Description:
4433 -- It checks that a competence_element of type personal cannot be
4434 -- deleted. It checks that the competence_element of a compeleted
4435 -- assessment cannot be deleted.
4436 -- It also check that the competence_element of type 'COMPETENCE_USAGE'
4437 -- cannot be deleted if there is a competence element of type 'ASSESSMENT_
4438 -- COMPETENCE' which references those competences and also has a parent_
4439 -- competence_element_id which correspond to a competence_element
4440 -- containing the competences in the group_competence_type_column.
4441 --
4442 -- In Arguments:
4443
4444 -- type
4445 -- competence_type
4446 -- competence_element_id
4447 -- p_parent_competence_element_id
4448 -- group_competence_type
4449 -- assessment_id
4450 -- assessment_type_id
4451 -- competence_id
4452 -- business_group_id
4453 --
4454 -- Post Success:
4455 -- The process succeeds if:
4456 -- the competence_element which need to be deleted is not referenced
4457
4458 -- by another competence element
4459 --
4460 -- Post Failure:
4461 -- An application error is raised and processing is terminated if any:
4462 -- the competence_element which need to be deleted is referenced
4463 -- by another competence element.
4464 --
4465 -- Access Status:
4466 -- Internal Table Handler Use Only.
4467 --
4468 --
4469 procedure chk_comp_element_delete
4470 ( p_competence_element_id
4471 in per_competence_elements.competence_element_id%TYPE
4472 ,p_business_group_id
4473 in per_competence_elements.business_group_id%TYPE
4474 ,p_parent_competence_element_id
4475 in per_competence_elements.parent_competence_element_id%TYPE
4476 ,p_type
4477 in per_competence_elements.type%TYPE
4478 ,p_competence_type
4479 in per_competence_elements.competence_type%TYPE
4480 ,p_group_competence_type
4481 in per_competence_elements.group_competence_type%TYPE
4482 ,p_assessment_id
4483 in per_competence_elements.assessment_id%TYPE
4484 ,p_assessment_type_id
4485 in per_competence_elements.assessment_type_id%TYPE
4486 ,p_competence_id
4487 in per_competence_elements.competence_id%TYPE
4488 ) is
4489 --
4490 l_proc varchar2(72):= g_package||'chk_comp_element_delete';
4491 l_exists varchar2(1);
4492 l_assessment_type_id per_assessment_types.assessment_type_id%TYPE;
4493 --
4494 -- Cursor which is used to check whether the competence being removed from the assessment template
4495 -- is being used by any assessments (ie.TYPE='ASSESSMENT'). This cursor only makes sure that the
4496 -- competence isnt' being used, not whether the assessment in which the competence is being used.
4497 -- Maybe the business rules need tighting up around here as maybe the form should change.
4498 --
4499 cursor csr_get_used_comp_element is
4500 select null
4501 from per_competence_elements
4502 where type = 'ASSESSMENT'
4503 and competence_id = p_competence_id
4504 and assessment_id in
4505 (Select asn.assessment_id
4506 From per_assessments asn
4507 Where asn.assessment_type_id =
4508 (Select assessment_type_id
4509 From per_competence_elements
4510 Where competence_element_id =
4511 (Select parent_competence_element_id
4512 From per_competence_elements
4513 Where competence_element_id=p_competence_element_id
4514 )
4515 )
4516 )
4517 ;
4518 -- Cursor to check that whether a competence element is
4519 -- the parent of other competence element.
4520 --
4521 cursor csr_is_parent_comp is
4522 select null
4523 from per_competence_elements
4524 where parent_competence_element_id = p_competence_element_id
4525 and business_group_id = p_business_group_id;
4526 --
4527 -- Cursor to check the COMPETENCE_USAGE' type referenced by
4528 -- competence element of type "ASSESSMENT_COMPETENCE'
4529 --
4530 cursor csr_get_comp_group is
4531 select null
4532 from per_competence_elements comp1
4533 where comp1.type = 'ASSESSMENT_COMPETENCE'
4534 and comp1.parent_competence_element_id is not null
4535 and comp1.business_group_id = p_business_group_id
4536 and comp1.competence_id = p_competence_id
4537 and exists (select null
4538 from per_competence_elements comp2
4539 where comp2.competence_element_id =
4540 comp1.parent_competence_element_id
4541 and comp1.business_group_id =
4542
4543 comp2.business_group_id
4544 and comp2.group_competence_type =
4545 p_competence_type);
4546 --
4547 -- Cursor to check the COMPETENCE_ELMENT_ID' referenced by
4548 -- per_comp_element_outcmes table BUG3356369
4549 --
4550 cursor csr_comp_element_outcome is
4551 select 'x' from per_comp_element_outcomes
4552 where competence_element_id = p_competence_element_id;
4553
4554 --
4555 begin
4556 --
4557 hr_utility.set_location('Entering:'|| l_proc, 1);
4558 --
4559 -- Only do the delete validation if the type is
4560 -- ASSESSESSMENT_GROUP, ASSESSMENT_COMPETENCE, COMPETENCE_USAGE or
4561 -- or PERSONAL,ASSESSMENT.
4562 --
4563 if (p_type= 'ASSESSESSMENT_GROUP' OR p_type = 'ASSESSMENT_COMPETENCE' OR
4564 p_type= 'COMPETENCE_USAGE' or p_type = 'ASSESSMENT' OR p_type = 'PERSONAL'
4565
4566 ) then
4567
4568 --
4569 -- raise an error message if the type = 'PERSONAL'
4570 --
4571 -- commented out following section due to bug raised (no. 525537) 1-09-97
4572 -- if p_type = 'PERSONAL' then
4573 -- hr_utility.set_location(l_proc,5);
4574 -- hr_utility.set_message(801,'HR_51675_CEL_PER_CANT_DEL');
4575 -- hr_utility.raise_error;
4576 -- end if;
4577 --
4578 --
4579 if(p_type = 'ASSESSMENT_GROUP') then
4580 --
4581 -- check that a parent competence_element cannot be deleted
4582 -- if it is referenced by another competence_element.
4583 --
4584 open csr_is_parent_comp;
4585 fetch csr_is_parent_comp into l_exists;
4586 if csr_is_parent_comp%found then
4587 close csr_is_parent_comp;
4588 hr_utility.set_location(l_proc,15);
4589 hr_utility.set_message(801,'HR_51677_CEL_PARNT_CANT_DEL');
4590 hr_utility.raise_error;
4591
4592 end if;
4593 close csr_is_parent_comp;
4594 end if;
4595 --
4596 -- Now check that if the type is COMPETENCE_USAGE and there is
4597 -- a competence_element of type 'ASSESSMENT_COMPETENCE' with
4598 -- the same competence_type(i.e. Via the parent)and refernces
4599 -- the same competence.
4600 --
4601 if(p_type= 'COMPETENCE_USAGE') then
4602 open csr_get_comp_group;
4603 fetch csr_get_comp_group into l_exists;
4604 if csr_get_comp_group%found then
4605
4606 close csr_get_comp_group;
4607 hr_utility.set_location(l_proc,20);
4608 hr_utility.set_message(801,'HR_51678_CEL_COM_USG_CANT_DEL');
4609 hr_utility.raise_error;
4610 end if;
4611 close csr_get_comp_group;
4612 end if;
4613 --
4614 -- Now check that if an element of ASSESSMENT_COMPETENCE type is
4615 -- going to be deleted, then the competence is not referenced by any
4616 -- other element of type 'ASSESSMENT'
4617 --
4618 if (p_type = 'ASSESSMENT_COMPETENCE') then
4619
4620 open csr_get_used_comp_element;
4621 fetch csr_get_used_comp_element into l_exists;
4622 if csr_get_used_comp_element%found then
4623 close csr_get_used_comp_element;
4624 hr_utility.set_location(l_proc,25);
4625 hr_utility.set_message(801,'HR_51679_CEL_ASS_COMP_CANT_DEL');
4626 hr_utility.raise_error;
4627 end if;
4628 --
4629 close csr_get_used_comp_element;
4630 end if;
4631 --
4632 end if;
4633 --
4634 hr_utility.set_location(l_proc,30);
4635
4636 open csr_comp_element_outcome;
4637 fetch csr_comp_element_outcome into l_exists;
4638 if csr_comp_element_outcome%FOUND then
4639 close csr_comp_element_outcome;
4640 hr_utility.set_message(800,'HR_449135_QUA_FWK_CEL_TAB_REF');
4641 hr_utility.raise_error;
4642 end if;
4643 close csr_comp_element_outcome;
4644
4645 hr_utility.set_location('Leaving: ' || l_proc,40);
4646 end chk_comp_element_delete;
4647 -- -----------------------------------------------------------------------
4648 -- |------------------------------< chk_df >-----------------------------|
4649 -- -----------------------------------------------------------------------
4650 --
4651 -- Description:
4652 -- Validates the all Descriptive Flexfield values.
4653 --
4654 -- Pre-conditions:
4655 -- All other columns have been validated. Must be called as the
4656 -- last step from insert_validate and update_validate.
4657 --
4658 -- In Arguments:
4659 -- p_rec
4660 --
4661 -- Post Success:
4662 -- If the Descriptive Flexfield structure column and data values are
4663 -- all valid this procedure will end normally and processing will
4664 -- continue.
4665 --
4666 -- Post Failure:
4667 -- If the Descriptive Flexfield structure column value or any of
4668 -- the data values are invalid then an application error is raised as
4669 -- a PL/SQL exception.
4670 --
4671 -- Access Status:
4672 -- Internal Row Handler Use Only.
4673 --
4674 procedure chk_df
4675 (p_rec in per_cel_shd.g_rec_type) is
4676 --
4677 l_proc varchar2(72) := g_package||'chk_df';
4678 --
4679 begin
4680 hr_utility.set_location('Entering:'||l_proc, 10);
4681 --
4682 if ((p_rec.valid_grade_id is not null) and (
4683 nvl(per_cel_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
4684 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
4685 nvl(per_cel_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
4686 nvl(p_rec.attribute1, hr_api.g_varchar2) or
4687 nvl(per_cel_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
4688 nvl(p_rec.attribute2, hr_api.g_varchar2) or
4689 nvl(per_cel_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
4690 nvl(p_rec.attribute3, hr_api.g_varchar2) or
4691 nvl(per_cel_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
4692 nvl(p_rec.attribute4, hr_api.g_varchar2) or
4693 nvl(per_cel_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
4694 nvl(p_rec.attribute5, hr_api.g_varchar2) or
4695 nvl(per_cel_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
4696 nvl(p_rec.attribute6, hr_api.g_varchar2) or
4697 nvl(per_cel_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
4698 nvl(p_rec.attribute7, hr_api.g_varchar2) or
4699 nvl(per_cel_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
4700 nvl(p_rec.attribute8, hr_api.g_varchar2) or
4701 nvl(per_cel_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
4702 nvl(p_rec.attribute9, hr_api.g_varchar2) or
4703 nvl(per_cel_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
4704 nvl(p_rec.attribute10, hr_api.g_varchar2) or
4705 nvl(per_cel_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
4706 nvl(p_rec.attribute11, hr_api.g_varchar2) or
4707 nvl(per_cel_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
4708 nvl(p_rec.attribute12, hr_api.g_varchar2) or
4709 nvl(per_cel_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
4710 nvl(p_rec.attribute13, hr_api.g_varchar2) or
4711 nvl(per_cel_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
4712 nvl(p_rec.attribute14, hr_api.g_varchar2) or
4713 nvl(per_cel_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
4714 nvl(p_rec.attribute15, hr_api.g_varchar2) or
4715 nvl(per_cel_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
4716 nvl(p_rec.attribute16, hr_api.g_varchar2) or
4717 nvl(per_cel_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
4718 nvl(p_rec.attribute17, hr_api.g_varchar2) or
4719 nvl(per_cel_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
4720 nvl(p_rec.attribute18, hr_api.g_varchar2) or
4721 nvl(per_cel_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
4722 nvl(p_rec.attribute19, hr_api.g_varchar2) or
4723 nvl(per_cel_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
4724 nvl(p_rec.attribute20, hr_api.g_varchar2)))
4725 or
4726 (p_rec.valid_grade_id is null) then
4727 --
4728 -- Only execute the validation if absolutely necessary:
4729 -- a) During update, the structure column value or any
4730 -- of the attribute values have actually changed.
4731 -- b) During insert.
4732 --
4733 hr_dflex_utility.ins_or_upd_descflex_attribs
4734 (p_appl_short_name => 'PER'
4735 ,p_descflex_name => 'PER_COMPETENCE_ELEMENTS'
4736 ,p_attribute_category => p_rec.attribute_category
4737 ,p_attribute1_name => 'ATTRIBUTE1'
4738 ,p_attribute1_value => p_rec.attribute1
4739 ,p_attribute2_name => 'ATTRIBUTE2'
4740 ,p_attribute2_value => p_rec.attribute2
4741 ,p_attribute3_name => 'ATTRIBUTE3'
4742 ,p_attribute3_value => p_rec.attribute3
4743 ,p_attribute4_name => 'ATTRIBUTE4'
4744 ,p_attribute4_value => p_rec.attribute4
4745 ,p_attribute5_name => 'ATTRIBUTE5'
4746 ,p_attribute5_value => p_rec.attribute5
4747 ,p_attribute6_name => 'ATTRIBUTE6'
4748 ,p_attribute6_value => p_rec.attribute6
4749 ,p_attribute7_name => 'ATTRIBUTE7'
4750 ,p_attribute7_value => p_rec.attribute7
4751 ,p_attribute8_name => 'ATTRIBUTE8'
4752 ,p_attribute8_value => p_rec.attribute8
4753 ,p_attribute9_name => 'ATTRIBUTE9'
4754 ,p_attribute9_value => p_rec.attribute9
4755 ,p_attribute10_name => 'ATTRIBUTE10'
4756 ,p_attribute10_value => p_rec.attribute10
4757 ,p_attribute11_name => 'ATTRIBUTE11'
4758 ,p_attribute11_value => p_rec.attribute11
4759 ,p_attribute12_name => 'ATTRIBUTE12'
4760 ,p_attribute12_value => p_rec.attribute12
4761 ,p_attribute13_name => 'ATTRIBUTE13'
4762 ,p_attribute13_value => p_rec.attribute13
4763 ,p_attribute14_name => 'ATTRIBUTE14'
4764 ,p_attribute14_value => p_rec.attribute14
4765 ,p_attribute15_name => 'ATTRIBUTE15'
4766 ,p_attribute15_value => p_rec.attribute15
4767 ,p_attribute16_name => 'ATTRIBUTE16'
4768 ,p_attribute16_value => p_rec.attribute16
4769 ,p_attribute17_name => 'ATTRIBUTE17'
4770 ,p_attribute17_value => p_rec.attribute17
4771 ,p_attribute18_name => 'ATTRIBUTE18'
4772 ,p_attribute18_value => p_rec.attribute18
4773 ,p_attribute19_name => 'ATTRIBUTE19'
4774 ,p_attribute19_value => p_rec.attribute19
4775 ,p_attribute20_name => 'ATTRIBUTE20'
4776 ,p_attribute20_value => p_rec.attribute20
4777 );
4778 end if;
4779 --
4780 hr_utility.set_location(' Leaving:'||l_proc, 20);
4781
4782 end chk_df;
4783 --
4784 -- ----------------------------------------------------------------------------
4785 -- |-----------------------------< chk_ddf >----------------------------------|
4786 -- ----------------------------------------------------------------------------
4787 --
4788 -- Description:
4789 -- Validates all the Developer Descriptive Flexfield values.
4790 --
4791 -- Prerequisites:
4792 -- All other columns have been validated. Must be called as the
4793 -- last step from insert_validate and update_validate.
4794 --
4795 -- In Arguments:
4796 -- p_rec
4797 --
4798 -- Post Success:
4799 -- If the Developer Descriptive Flexfield structure column and data values
4800 -- are all valid this procedure will end normally and processing will
4801 -- continue.
4802 --
4803 -- Post Failure:
4804 -- If the Developer Descriptive Flexfield structure column value or any of
4805 -- the data values are invalid then an application error is raised as
4806 -- a PL/SQL exception.
4807 --
4808 -- Access Status:
4809 -- Internal Row Handler Use Only.
4810 --
4811 -- ----------------------------------------------------------------------------
4812 procedure chk_ddf
4813 (p_rec in per_cel_shd.g_rec_type
4814 ) is
4815 --
4816 l_proc varchar2(72) := g_package || 'chk_ddf';
4817 --
4818 begin
4819 hr_utility.set_location('Entering:'||l_proc,10);
4820 --
4821 if ((p_rec.competence_element_id is not null) and (
4822 nvl(per_cel_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
4823 nvl(p_rec.information_category, hr_api.g_varchar2) or
4824 nvl(per_cel_shd.g_old_rec.information1, hr_api.g_varchar2) <>
4825 nvl(p_rec.information1, hr_api.g_varchar2) or
4826 nvl(per_cel_shd.g_old_rec.information2, hr_api.g_varchar2) <>
4827 nvl(p_rec.information2, hr_api.g_varchar2) or
4828 nvl(per_cel_shd.g_old_rec.information3, hr_api.g_varchar2) <>
4829 nvl(p_rec.information3, hr_api.g_varchar2) or
4830 nvl(per_cel_shd.g_old_rec.information4, hr_api.g_varchar2) <>
4831 nvl(p_rec.information4, hr_api.g_varchar2) or
4832 nvl(per_cel_shd.g_old_rec.information5, hr_api.g_varchar2) <>
4833 nvl(p_rec.information5, hr_api.g_varchar2) or
4834 nvl(per_cel_shd.g_old_rec.information6, hr_api.g_varchar2) <>
4835 nvl(p_rec.information6, hr_api.g_varchar2) or
4836 nvl(per_cel_shd.g_old_rec.information7, hr_api.g_varchar2) <>
4837 nvl(p_rec.information7, hr_api.g_varchar2) or
4838 nvl(per_cel_shd.g_old_rec.information8, hr_api.g_varchar2) <>
4839 nvl(p_rec.information8, hr_api.g_varchar2) or
4840 nvl(per_cel_shd.g_old_rec.information9, hr_api.g_varchar2) <>
4841 nvl(p_rec.information9, hr_api.g_varchar2) or
4842 nvl(per_cel_shd.g_old_rec.information10, hr_api.g_varchar2) <>
4843 nvl(p_rec.information10, hr_api.g_varchar2) or
4844 nvl(per_cel_shd.g_old_rec.information11, hr_api.g_varchar2) <>
4845 nvl(p_rec.information11, hr_api.g_varchar2) or
4846 nvl(per_cel_shd.g_old_rec.information13, hr_api.g_varchar2) <>
4847 nvl(p_rec.information13, hr_api.g_varchar2) or
4848 nvl(per_cel_shd.g_old_rec.information14, hr_api.g_varchar2) <>
4849 nvl(p_rec.information14, hr_api.g_varchar2) or
4850 nvl(per_cel_shd.g_old_rec.information15, hr_api.g_varchar2) <>
4851 nvl(p_rec.information15, hr_api.g_varchar2) or
4852 nvl(per_cel_shd.g_old_rec.information16, hr_api.g_varchar2) <>
4853 nvl(p_rec.information16, hr_api.g_varchar2) or
4854 nvl(per_cel_shd.g_old_rec.information17, hr_api.g_varchar2) <>
4855 nvl(p_rec.information17, hr_api.g_varchar2) or
4856 nvl(per_cel_shd.g_old_rec.information18, hr_api.g_varchar2) <>
4857 nvl(p_rec.information18, hr_api.g_varchar2) or
4858 nvl(per_cel_shd.g_old_rec.information19, hr_api.g_varchar2) <>
4859 nvl(p_rec.information19, hr_api.g_varchar2) or
4860 nvl(per_cel_shd.g_old_rec.information20, hr_api.g_varchar2) <>
4861 nvl(p_rec.information20, hr_api.g_varchar2)))
4862 or (p_rec.competence_element_id is not null) then
4863 --
4864 -- Only execute the validation if absolutely necessary:
4865 -- a) During update, the structure column value or any
4866 -- of the attribute values have actually changed.
4867 -- b) During insert.
4868 --
4869 hr_dflex_utility.ins_or_upd_descflex_attribs
4870 (p_appl_short_name => 'PER'
4871 ,p_descflex_name => 'Competence Element Developer'
4872 ,p_attribute_category => p_rec.INFORMATION_CATEGORY
4873 ,p_attribute1_name => 'INFORMATION1'
4874 ,p_attribute1_value => p_rec.information1
4875 ,p_attribute2_name => 'INFORMATION2'
4876 ,p_attribute2_value => p_rec.information2
4877 ,p_attribute3_name => 'INFORMATION3'
4878 ,p_attribute3_value => p_rec.information3
4879 ,p_attribute4_name => 'INFORMATION4'
4880 ,p_attribute4_value => p_rec.information4
4881 ,p_attribute5_name => 'INFORMATION5'
4882 ,p_attribute5_value => p_rec.information5
4883 ,p_attribute6_name => 'INFORMATION6'
4884 ,p_attribute6_value => p_rec.information6
4885 ,p_attribute7_name => 'INFORMATION7'
4886 ,p_attribute7_value => p_rec.information7
4887 ,p_attribute8_name => 'INFORMATION8'
4888 ,p_attribute8_value => p_rec.information8
4889 ,p_attribute9_name => 'INFORMATION9'
4890 ,p_attribute9_value => p_rec.information9
4891 ,p_attribute10_name => 'INFORMATION10'
4892 ,p_attribute10_value => p_rec.information10
4893 ,p_attribute11_name => 'INFORMATION11'
4894 ,p_attribute11_value => p_rec.information11
4895 ,p_attribute12_name => 'INFORMATION12'
4896 ,p_attribute12_value => p_rec.information12
4897 ,p_attribute13_name => 'INFORMATION13'
4898 ,p_attribute13_value => p_rec.information13
4899 ,p_attribute14_name => 'INFORMATION14'
4900 ,p_attribute14_value => p_rec.information14
4901 ,p_attribute15_name => 'INFORMATION15'
4902 ,p_attribute15_value => p_rec.information15
4903 ,p_attribute16_name => 'INFORMATION16'
4904 ,p_attribute16_value => p_rec.information16
4905 ,p_attribute17_name => 'INFORMATION17'
4906 ,p_attribute17_value => p_rec.information17
4907 ,p_attribute18_name => 'INFORMATION18'
4908 ,p_attribute18_value => p_rec.information18
4909 ,p_attribute19_name => 'INFORMATION19'
4910 ,p_attribute19_value => p_rec.information19
4911 ,p_attribute20_name => 'INFORMATION20'
4912 ,p_attribute20_value => p_rec.information20
4913 );
4914 end if;
4915 --
4916 hr_utility.set_location(' Leaving:'||l_proc,20);
4917 end chk_ddf;
4918 --
4919 -- ----------------------------------------------------------------------------
4920 -- |---------------------------< insert_validate >----------------------------|
4921 -- ----------------------------------------------------------------------------
4922 Procedure insert_validate(p_rec in out nocopy per_cel_shd.g_rec_type,
4923 p_effective_date in Date) is
4924 --
4925 l_proc varchar2(72) := g_package||'insert_validate';
4926 --
4927 Begin
4928 hr_utility.set_location('Entering:'||l_proc, 5);
4929 --
4930 -- Validate important attributes
4931 --
4932 -- Call all supporting business operations
4933 --
4934 -- Validate business_group_id
4935 --
4936 -- HR/TCA merge
4937 -- if party_id is null or business_group_id is not null
4938 -- no need to check business_grroup_id
4939 if p_rec.party_id is null or p_rec.business_group_id is not null then
4940 -- ngundura added this if condition
4941
4942 if ( p_rec.type not in ('PROJECT_ROLE','OPEN_ASSIGNMENT','QUALIFICATION','ASSESSMENT_GROUP','ASSESSMENT_COMPETENCE')) then
4943 -- Validate Bus Grp
4944 hr_api.validate_bus_grp_id(
4945 p_business_group_id => p_rec.business_group_id
4946 ,p_associated_column1 => per_cel_shd.g_tab_nam ||
4947 '.BUSINESS_GROUP_ID'
4948 );
4949 --
4950 -- After validating the set of important attributes,
4951 -- if Mulitple message detection is enabled and at least
4952 -- one error has been found then abort further validation.
4953 --
4954 hr_multi_message.end_validation_set;
4955 --
4956 per_cel_shd.g_bus_grp := true;
4957 else
4958 per_cel_shd.g_bus_grp := false;
4959 end if;
4960 else
4961 per_cel_shd.g_bus_grp := false;
4962 end if;
4963 --
4964 -- Business Rule Mapping
4965 -- =====================
4966 -- CHK_TYPE
4967 --
4968 chk_type
4969 (p_competence_element_id => p_rec.competence_element_id
4970 ,p_effective_date => p_effective_date
4971 ,p_type => p_rec.type
4972 ,p_object_version_number => p_rec.object_version_number
4973 );
4974 --
4975 hr_utility.set_location(l_proc, 10);
4976 --
4977 --
4978 -- Business Rule Mapping
4979 -- =====================
4980 -- CHK_TYPE_AND_VALIDATION
4981 --
4982 chk_type_and_validation
4983 (p_competence_element_id => p_rec.competence_element_id
4984 ,p_object_version_number => p_rec.object_version_number
4985 ,p_business_group_id => p_rec.business_group_id
4986 ,p_enterprise_id => p_rec.enterprise_id
4987 ,p_type => p_rec.type
4988 ,p_competence_id => p_rec.competence_id
4989 ,p_assessment_id => p_rec.assessment_id
4990 ,p_assessment_type_id => p_rec.assessment_type_id
4991 ,p_activity_version_id => p_rec.activity_version_id
4992 ,p_organization_id => p_rec.organization_id
4993 ,p_job_id => p_rec.job_id
4994 ,p_valid_grade_id => p_rec.valid_grade_id
4995 ,p_position_id => p_rec.position_id
4996 ,p_person_id => p_rec.person_id
4997
4998 ,p_parent_competence_element_id
4999 => p_rec.parent_competence_element_id
5000 ,p_group_competence_type => p_rec.group_competence_type
5001 ,p_effective_date_to => p_rec.effective_date_to
5002 ,p_effective_date_from => p_rec.effective_date_from
5003 ,p_proficiency_level_id => p_rec.proficiency_level_id
5004 ,p_certification_date => p_rec.certification_date
5005 ,p_certification_method => p_rec.certification_method
5006 ,p_next_certification_date => p_rec.next_certification_date
5007 ,p_mandatory => p_rec.mandatory
5008 ,p_normal_elapse_duration => p_rec.normal_elapse_duration
5009 ,p_normal_elapse_duration_unit
5010 => p_rec.normal_elapse_duration_unit
5011
5012 ,p_high_proficiency_level_id
5013 => p_rec.high_proficiency_level_id
5014 ,p_competence_type => p_rec.competence_type
5015 ,p_sequence_number => p_rec.sequence_number
5016 ,p_source_of_proficiency_level
5017 => p_rec.source_of_proficiency_level
5018 ,p_weighting_level_id => p_rec.weighting_level_id
5019 ,p_rating_level_id => p_rec.rating_level_id
5020 ,p_line_score => p_rec.line_score
5021 ,p_object_id => p_rec.object_id
5022 ,p_object_name => p_rec.object_name
5023 ,p_party_id => p_rec.party_id -- HR/TCA merge
5024 ,p_qualification_type_id => p_rec.qualification_type_id
5025 );
5026 --
5027
5028 hr_utility.set_location(l_proc, 15);
5029 --
5030 -- Business Rule Mapping
5031 -- =====================
5032 -- CHK_UNIQUE_COMPETENCE_ELEMENT
5033 --
5034 chk_unique_competence_element
5035 (p_competence_element_id => p_rec.competence_element_id
5036 ,p_business_group_id => p_rec.business_group_id
5037 ,p_enterprise_id => p_rec.enterprise_id
5038 ,p_type => p_rec.type
5039 ,p_competence_id => p_rec.competence_id
5040 ,p_assessment_id => p_rec.assessment_id
5041
5042 ,p_assessment_type_id => p_rec.assessment_type_id
5043 ,p_activity_version_id => p_rec.activity_version_id
5044 ,p_organization_id => p_rec.organization_id
5045 ,p_job_id => p_rec.job_id
5046 ,p_valid_grade_id => p_rec.valid_grade_id
5047 ,p_position_id => p_rec.position_id
5048 ,p_person_id => p_rec.person_id
5049 ,p_parent_competence_element_id
5050 => p_rec.parent_competence_element_id
5051 ,p_group_competence_type => p_rec.group_competence_type
5052 ,p_effective_date_from => p_rec.effective_date_from
5053
5054 ,p_competence_type => p_rec.competence_type
5055 ,p_object_version_number => p_rec.object_version_number
5056
5057 ,p_object_name => p_rec.object_name
5058 ,p_object_id => p_rec.object_id
5059 ,p_party_id => p_rec.party_id -- HR/TCA merge
5060 ,p_qualification_type_id => p_rec.qualification_type_id
5061 );
5062 --
5063 hr_utility.set_location(l_proc, 16);
5064 --
5065 -- Business Rule Mapping
5066 -- =====================
5067 -- CHK_CERTIFICATION_METHOD
5068 --
5069 chk_certification_method
5070 (p_competence_element_id => p_rec.competence_element_id
5071 ,p_effective_date => p_effective_date
5072 ,p_certification_method => p_rec.certification_method
5073 ,p_object_version_number => p_rec.object_version_number
5074 );
5075 --
5076 hr_utility.set_location(l_proc, 20);
5077 --
5078 -- Business Rule Mapping
5079 -- =====================
5080 -- CHK_COMPETENCE_TYPE
5081 --
5082 chk_competence_type
5083 (p_competence_element_id => p_rec.competence_element_id
5084 ,p_effective_date => p_effective_date
5085 ,p_competence_type => p_rec.competence_type
5086 ,p_object_version_number => p_rec.object_version_number
5087 );
5088 --
5089 hr_utility.set_location(l_proc, 25);
5090 --
5091 -- Business Rule Mapping
5092 -- =====================
5093 -- CHK_GROUP_COMPETENCE_TYPE
5094 --
5095 chk_competence_type
5096 (p_competence_element_id => p_rec.competence_element_id
5097 ,p_effective_date => p_effective_date
5098 ,p_competence_type => p_rec.group_competence_type
5099 ,p_object_version_number => p_rec.object_version_number
5100 );
5101 --
5102 hr_utility.set_location(l_proc, 30);
5103 --
5104 -- Business Rule Mapping
5105 -- =====================
5106 -- CHK_MANDATORY
5107 --
5108 chk_mandatory
5109 (p_competence_element_id => p_rec.competence_element_id
5110 ,p_effective_date => p_effective_date
5111
5112 ,p_mandatory => p_rec.mandatory
5113 ,p_object_version_number => p_rec.object_version_number
5114 );
5115 --
5116 hr_utility.set_location(l_proc, 35);
5117 --
5118 -- Business Rule Mapping
5119 -- =====================
5120 -- CHK_SOURC_OF_PROFICIENCY_LEVEL
5121 --
5122 chk_source_of_proficiency
5123 (p_competence_element_id => p_rec.competence_element_id
5124 ,p_effective_date => p_effective_date
5125
5126 ,p_source_of_proficiency_level
5127 => p_rec.source_of_proficiency_level
5128 ,p_object_version_number => p_rec.object_version_number
5129 );
5130 --
5131 hr_utility.set_location(l_proc, 40);
5132 --
5133 -- Business Rule Mapping
5134 -- =====================
5135 -- CHK_CERTIFICATION_METHOD_DATE
5136 --
5137 chk_certification_method_date
5138 (p_competence_element_id => p_rec.competence_element_id
5139
5140 ,p_certification_date => p_rec.certification_date
5141 ,p_certification_method => p_rec.certification_method
5142 ,p_object_version_number => p_rec.object_version_number
5143 );
5144 --
5145 hr_utility.set_location(l_proc, 45);
5146 --
5147 -- Business Rule Mapping
5148 -- =====================
5149 -- CHK_NEXT_CERTIFICATION_DATE
5150 --
5151 chk_next_certification_date
5152 (p_competence_element_id => p_rec.competence_element_id
5153 ,p_certification_date => p_rec.certification_date
5154 ,p_next_certification_date => p_rec.next_certification_date
5155 ,p_object_version_number => p_rec.object_version_number
5156 ,p_effective_date_from => p_rec.effective_date_from -- added for fix of #731089
5157 );
5158 --
5159 hr_utility.set_location(l_proc, 48);
5160 --
5161 -- Business Rule Mapping
5162 -- =====================
5163 -- CHK_FOREIGN_KEYS
5164 --
5165 chk_foreign_keys
5166 (p_competence_element_id => p_rec.competence_element_id
5167
5168 ,p_object_version_number => p_rec.object_version_number
5169 ,p_business_group_id => p_rec.business_group_id
5170 ,p_enterprise_id => p_rec.enterprise_id
5171 ,p_competence_id => p_rec.competence_id
5172 ,p_assessment_id => p_rec.assessment_id
5173 ,p_assessment_type_id => p_rec.assessment_type_id
5174 ,p_activity_version_id => p_rec.activity_version_id
5175 ,p_organization_id => p_rec.organization_id
5176 ,p_job_id => p_rec.job_id
5177 ,p_valid_grade_id => p_rec.valid_grade_id
5178 ,p_position_id => p_rec.position_id
5179 ,p_person_id => p_rec.person_id
5180 ,p_parent_competence_element_id
5181
5182 => p_rec.parent_competence_element_id
5183 ,p_effective_date_to => p_rec.effective_date_to
5184 ,p_effective_date_from => p_rec.effective_date_from
5185 ,p_proficiency_level_id => p_rec.proficiency_level_id
5186 ,p_high_proficiency_level_id
5187 => p_rec.high_proficiency_level_id
5188 ,p_weighting_level_id => p_rec.weighting_level_id
5189 ,p_rating_level_id => p_rec.rating_level_id
5190 ,p_effective_date => p_effective_date
5191 ,p_type => p_rec.type
5192 ,p_party_id => p_rec.party_id -- HR/TCA merge
5193 ,p_qualification_type_id => p_rec.qualification_type_id
5194 );
5195 --
5196 hr_utility.set_location('Entering:'||l_proc, 50);
5197
5198 --
5199 -- Business Rule Mapping
5200 -- =====================
5201 -- CHK_PARTY_ID
5202 --
5203 chk_party_id
5204 (p_rec
5205 ,p_effective_date
5206 );
5207
5208 --
5209 hr_utility.set_location('Entering:'||l_proc, 52);
5210
5211 --
5212 -- Business Rule Mapping
5213 -- =====================
5214 -- CHK_PROFICIENCY_LEVEL_ID
5215 --
5216 chk_proficiency_level_id
5217 (p_competence_element_id => p_rec.competence_element_id
5218 ,p_business_group_id => p_rec.business_group_id
5219 ,p_proficiency_level_id => p_rec.proficiency_level_id
5220 ,p_high_proficiency_level_id
5221 => p_rec.high_proficiency_level_id
5222 ,p_competence_id => p_rec.competence_id
5223 ,p_object_version_number => p_rec.object_version_number
5224 ,p_party_id => p_rec.party_id
5225 );
5226 --
5227 hr_utility.set_location(l_proc, 55);
5228 --
5229 -- Business Rule Mapping
5230 -- =====================
5231 -- CHK_RATING_WEIGHTING_ID
5232 --
5233 chk_rating_weighting_id
5234 (p_competence_element_id => p_rec.competence_element_id
5235 ,p_business_group_id => p_rec.business_group_id
5236 ,p_rating_level_id => p_rec.rating_level_id
5237 ,p_weighting_level_id => p_rec.weighting_level_id
5238
5239 ,p_assessment_id => p_rec.assessment_id
5240 ,p_object_version_number => p_rec.object_version_number
5241 ,p_type => p_rec.type
5242 ,p_party_id => p_rec.party_id
5243 );
5244 --
5245 hr_utility.set_location(l_proc, 60);
5246 --
5247 -- Business Rule Mapping
5248 -- =====================
5249 -- CHK_COMPETENCE_ELEMENT_DATES
5250 --
5251 chk_competence_element_dates
5252 (p_competence_element_id => p_rec.competence_element_id
5253
5254 ,p_business_group_id => p_rec.business_group_id
5255 ,p_competence_id => p_rec.competence_id
5256 ,p_person_id => p_rec.person_id
5257 ,p_position_id => p_rec.position_id
5258 ,p_organization_id => p_rec.organization_id
5259 ,p_job_id => p_rec.job_id
5260 ,p_valid_grade_id => p_rec.valid_grade_id
5261 ,p_effective_date_from => p_rec.effective_date_from
5262 ,p_effective_date_to => p_rec.effective_date_to
5263 ,p_object_version_number => p_rec.object_version_number
5264 ,p_enterprise_id => p_rec.enterprise_id
5265 );
5266 --
5267 hr_utility.set_location(l_proc, 65);
5268
5269 --
5270 -- Business Rule Mapping
5271 -- =====================
5272 -- CHK_NORMAL_ELAPSE_DURATION
5273 --
5274 chk_normal_elapse_duration
5275 (p_competence_element_id => p_rec.competence_element_id
5276 ,p_effective_date => p_effective_date
5277 ,p_normal_elapse_duration => p_rec.normal_elapse_duration
5278 ,p_normal_elapse_duration_unit
5279 => p_rec.normal_elapse_duration_unit
5280 ,p_object_version_number => p_rec.object_version_number
5281 );
5282
5283 --
5284 hr_utility.set_location(l_proc, 70);
5285
5286 /*
5287 -- Business Rule Mapping
5288 -- =====================
5289 -- CHK_UNIQUE_COMP_QUAL
5290 --
5291 chk_unique_comp_qual
5292 (p_competence_element_id => p_rec.competence_element_id
5293 ,p_competence_id => p_rec.competence_id
5294 ,p_qualification_type_id => p_rec.qualification_type_id
5295 ,p_object_version_number => p_rec.object_version_number
5296 ,p_effective_date => p_effective_date
5297 );
5298 */
5299 --
5300 hr_utility.set_location(l_proc, 80);
5301 --
5302 -- do the descriptive flex validation.
5303 --
5304 per_cel_bus.chk_df(p_rec => p_rec);
5305
5306 hr_utility.set_location(l_proc, 90);
5307
5308 --
5309 -- do the developer descriptive flex validation.
5310 --
5311 per_cel_bus.chk_ddf(p_rec => p_rec);
5312 --
5313 hr_utility.set_location('Leaving:'||l_proc, 100);
5314 --
5315 End insert_validate;
5316 --
5317 -- ----------------------------------------------------------------------------
5318 -- |---------------------------< update_validate >----------------------------|
5319 -- ----------------------------------------------------------------------------
5320 Procedure update_validate(p_rec in per_cel_shd.g_rec_type,
5321 p_effective_date in Date) is
5322 --
5323 l_proc varchar2(72) := g_package||'update_validate';
5324 --
5325 Begin
5326 hr_utility.set_location('Entering:'||l_proc, 5);
5327 -- Validate Important Attributes
5328 --
5329 -- Business Rule Mapping
5330 -- =====================
5331 -- Check non_updateable arguments
5332 --
5333 -- if party_id is pecified, business_group is not mandatory
5334 -- HR/TCA merge
5335 if p_rec.party_id is null or p_rec.business_group_id is not null then
5336 --
5337 -- Validate business_group_id
5338 --
5339 if ( p_rec.type not in ('PROJECT_ROLE','OPEN_ASSIGNMENT','QUALIFICATION','ASSESSMENT_GROUP','ASSESSMENT_COMPETENCE')) then
5340 hr_api.validate_bus_grp_id(
5341 p_business_group_id => p_rec.business_group_id
5342 ,p_associated_column1 => per_cel_shd.g_tab_nam ||
5343 '.BUSINESS_GROUP_ID'
5344 );
5345 --
5346 -- After validating the set of important attributes,
5347 -- if Mulitple message detection is enabled and at least
5348 -- one error has been found then abort further validation.
5349 --
5350 hr_multi_message.end_validation_set;
5351 --
5352 per_cel_shd.g_bus_grp := true;
5353 else
5354 per_cel_shd.g_bus_grp := false;
5355 end if;
5356 else
5357 per_cel_shd.g_bus_grp := false;
5358 end if;
5359 --
5360 --
5361 per_cel_bus.check_non_updateable_args
5362 (p_rec =>p_rec);
5363 --
5364 hr_utility.set_location (l_proc,6);
5365 --
5366 -- Business Rule Mapping
5367 -- =====================
5368 -- CHK_TYPE_AND_VALIDATION
5369 --
5370 chk_type_and_validation
5371 (p_competence_element_id => p_rec.competence_element_id
5372 ,p_object_version_number => p_rec.object_version_number
5373
5374 ,p_business_group_id => p_rec.business_group_id
5375 ,p_enterprise_id => p_rec.enterprise_id
5376 ,p_type => p_rec.type
5377 ,p_competence_id => p_rec.competence_id
5378 ,p_assessment_id => p_rec.assessment_id
5379 ,p_assessment_type_id => p_rec.assessment_type_id
5380 ,p_activity_version_id => p_rec.activity_version_id
5381 ,p_organization_id => p_rec.organization_id
5382 ,p_job_id => p_rec.job_id
5383 ,p_valid_grade_id => p_rec.valid_grade_id
5384 ,p_position_id => p_rec.position_id
5385 ,p_person_id => p_rec.person_id
5386 ,p_parent_competence_element_id
5387
5388 => p_rec.parent_competence_element_id
5389 ,p_group_competence_type => p_rec.group_competence_type
5390 ,p_effective_date_to => p_rec.effective_date_to
5391 ,p_effective_date_from => p_rec.effective_date_from
5392 ,p_proficiency_level_id => p_rec.proficiency_level_id
5393 ,p_certification_date => p_rec.certification_date
5394 ,p_certification_method => p_rec.certification_method
5395 ,p_next_certification_date => p_rec.next_certification_date
5396 ,p_mandatory => p_rec.mandatory
5397 ,p_normal_elapse_duration => p_rec.normal_elapse_duration
5398 ,p_normal_elapse_duration_unit
5399 => p_rec.normal_elapse_duration_unit
5400
5401 ,p_high_proficiency_level_id
5402 => p_rec.high_proficiency_level_id
5403 ,p_competence_type => p_rec.competence_type
5404 ,p_sequence_number => p_rec.sequence_number
5405 ,p_source_of_proficiency_level
5406 => p_rec.source_of_proficiency_level
5407 ,p_weighting_level_id => p_rec.weighting_level_id
5408 ,p_rating_level_id => p_rec.rating_level_id
5409 ,p_line_score => p_rec.line_score
5410 ,p_object_id => p_rec.object_id
5411 ,p_object_name => p_rec.object_name
5412 ,p_party_id => p_rec.party_id -- HR/TCA merge
5413 ,p_qualification_type_id => p_rec.qualification_type_id -- BUG3356369
5414 );
5415 --
5416 hr_utility.set_location(l_proc, 15);
5417
5418 --
5419 -- Business Rule Mapping
5420 -- =====================
5421 -- CHK_UNIQUE_COMPETENCE_ELEMENT
5422 --
5423 chk_unique_competence_element
5424 (p_competence_element_id => p_rec.competence_element_id
5425 ,p_business_group_id => p_rec.business_group_id
5426 ,p_enterprise_id => p_rec.enterprise_id
5427 ,p_type => p_rec.type
5428 ,p_competence_id => p_rec.competence_id
5429 ,p_assessment_id => p_rec.assessment_id
5430 ,p_assessment_type_id => p_rec.assessment_type_id
5431
5432 ,p_activity_version_id => p_rec.activity_version_id
5433 ,p_organization_id => p_rec.organization_id
5434 ,p_job_id => p_rec.job_id
5435 ,p_valid_grade_id => p_rec.valid_grade_id
5436 ,p_position_id => p_rec.position_id
5437 ,p_person_id => p_rec.person_id
5438 ,p_parent_competence_element_id
5439 => p_rec.parent_competence_element_id
5440 ,p_group_competence_type => p_rec.group_competence_type
5441 ,p_effective_date_from => p_rec.effective_date_from
5442 ,p_competence_type => p_rec.competence_type
5443
5444 ,p_object_version_number => p_rec.object_version_number
5445
5446 ,p_object_name => p_rec.object_name
5447 ,p_object_id => p_rec.object_id
5448 ,p_party_id => p_rec.party_id -- HR/TCA merge
5449 ,p_qualification_type_id => p_rec.qualification_type_id
5450 );
5451 --
5452 hr_utility.set_location(l_proc, 16);
5453 --
5454 -- Business Rule Mapping
5455 -- =====================
5456 -- CHK_CERTIFICATION_METHOD
5457 --
5458 chk_certification_method
5459 (p_competence_element_id => p_rec.competence_element_id
5460 ,p_effective_date => p_effective_date
5461 ,p_certification_method => p_rec.certification_method
5462
5463 ,p_object_version_number => p_rec.object_version_number
5464 );
5465 --
5466 hr_utility.set_location(l_proc, 20);
5467 --
5468 -- Business Rule Mapping
5469 -- =====================
5470 -- CHK_COMPETENCE_TYPE
5471 --
5472 chk_competence_type
5473 (p_competence_element_id => p_rec.competence_element_id
5474 ,p_effective_date => p_effective_date
5475 ,p_competence_type => p_rec.competence_type
5476
5477 ,p_object_version_number => p_rec.object_version_number
5478 );
5479 --
5480 hr_utility.set_location(l_proc, 25);
5481 --
5482 -- Business Rule Mapping
5483 -- =====================
5484 -- CHK_GROUP_COMPETENCE_TYPE
5485 --
5486 chk_competence_type
5487 (p_competence_element_id => p_rec.competence_element_id
5488 ,p_effective_date => p_effective_date
5489 ,p_competence_type => p_rec.group_competence_type
5490
5491 ,p_object_version_number => p_rec.object_version_number
5492 );
5493 --
5494 hr_utility.set_location(l_proc, 30);
5495 --
5496 -- Business Rule Mapping
5497 -- =====================
5498 -- CHK_MANDATORY
5499 --
5500 chk_mandatory
5501 (p_competence_element_id => p_rec.competence_element_id
5502 ,p_effective_date => p_effective_date
5503 ,p_mandatory => p_rec.mandatory
5504
5505 ,p_object_version_number => p_rec.object_version_number
5506 );
5507 --
5508 hr_utility.set_location(l_proc, 35);
5509 --
5510 -- Business Rule Mapping
5511 -- =====================
5512 -- CHK_SOURC_OF_PROFICIENCY_LEVEL
5513 --
5514 chk_source_of_proficiency
5515 (p_competence_element_id => p_rec.competence_element_id
5516 ,p_effective_date => p_effective_date
5517 ,p_source_of_proficiency_level
5518
5519 => p_rec.source_of_proficiency_level
5520 ,p_object_version_number => p_rec.object_version_number
5521 );
5522 --
5523 hr_utility.set_location(l_proc, 40);
5524 --
5525 -- Business Rule Mapping
5526 -- =====================
5527 -- CHK_CERTIFICATION_METHOD_DATE
5528 --
5529 chk_certification_method_date
5530 (p_competence_element_id => p_rec.competence_element_id
5531 ,p_certification_date => p_rec.certification_date
5532
5533 ,p_certification_method => p_rec.certification_method
5534 ,p_object_version_number => p_rec.object_version_number
5535 );
5536 --
5537 hr_utility.set_location(l_proc, 45);
5538 --
5539 -- Business Rule Mapping
5540 -- =====================
5541 -- CHK_NEXT_CERTIFICATION_DATE
5542 --
5543 chk_next_certification_date
5544 (p_competence_element_id => p_rec.competence_element_id
5545 ,p_certification_date => p_rec.certification_date
5546
5547 ,p_next_certification_date => p_rec.next_certification_date
5548 ,p_object_version_number => p_rec.object_version_number
5549 ,p_effective_date_from => p_rec.effective_date_from --added for bug fix of #731089
5550 );
5551 --
5552 hr_utility.set_location(l_proc, 48);
5553
5554 -- Business Rule Mapping
5555 -- =====================
5556 -- CHK_PROFICIENCY_LEVEL_ID
5557 --
5558 chk_proficiency_level_id
5559 (p_competence_element_id => p_rec.competence_element_id
5560 ,p_business_group_id => p_rec.business_group_id
5561
5562 ,p_proficiency_level_id => p_rec.proficiency_level_id
5563 ,p_high_proficiency_level_id
5564 => p_rec.high_proficiency_level_id
5565 ,p_competence_id => p_rec.competence_id
5566 ,p_object_version_number => p_rec.object_version_number
5567 ,p_party_id => p_rec.party_id
5568 );
5569 --
5570 hr_utility.set_location(l_proc, 55);
5571 --
5572 -- Business Rule Mapping
5573 -- =====================
5574 -- CHK_RATING_WEIGHTING_ID
5575 --
5576
5577 chk_rating_weighting_id
5578 (p_competence_element_id => p_rec.competence_element_id
5579 ,p_business_group_id => p_rec.business_group_id
5580 ,p_rating_level_id => p_rec.rating_level_id
5581 ,p_weighting_level_id => p_rec.weighting_level_id
5582 ,p_assessment_id => p_rec.assessment_id
5583 ,p_object_version_number => p_rec.object_version_number
5584 ,p_type => p_rec.type
5585 ,p_party_id => p_rec.party_id
5586 );
5587 --
5588 hr_utility.set_location(l_proc, 60);
5589 --
5590 -- Business Rule Mapping
5591
5592 -- =====================
5593 -- CHK_COMPETENCE_ELEMENT_DATES
5594 --
5595 chk_competence_element_dates
5596 (p_competence_element_id => p_rec.competence_element_id
5597 ,p_business_group_id => p_rec.business_group_id
5598 ,p_competence_id => p_rec.competence_id
5599 ,p_person_id => p_rec.person_id
5600 ,p_position_id => p_rec.position_id
5601 ,p_organization_id => p_rec.organization_id
5602 ,p_job_id => p_rec.job_id
5603 ,p_valid_grade_id => p_rec.valid_grade_id
5604 ,p_effective_date_from => p_rec.effective_date_from
5605
5606 ,p_effective_date_to => p_rec.effective_date_to
5607 ,p_object_version_number => p_rec.object_version_number
5608 ,p_enterprise_id => p_rec.enterprise_id
5609 );
5610 --
5611 hr_utility.set_location(l_proc, 65);
5612 --
5613 -- Business Rule Mapping
5614 -- =====================
5615 -- CHK_NORMAL_ELAPSE_DURATION
5616 --
5617 chk_normal_elapse_duration
5618 (p_competence_element_id => p_rec.competence_element_id
5619 ,p_effective_date => p_effective_date
5620
5621 ,p_normal_elapse_duration => p_rec.normal_elapse_duration
5622 ,p_normal_elapse_duration_unit
5623 => p_rec.normal_elapse_duration_unit
5624 ,p_object_version_number => p_rec.object_version_number
5625 );
5626 --
5627 hr_utility.set_location(l_proc, 70);
5628 --
5629 --
5630 -- Business Rule Mapping
5631 -- =====================
5632 -- CHK_FOREIGN_KEYS
5633 --
5634
5635 chk_foreign_keys
5636 (p_competence_element_id => p_rec.competence_element_id
5637 ,p_object_version_number => p_rec.object_version_number
5638 ,p_business_group_id => p_rec.business_group_id
5639 ,p_enterprise_id => p_rec.enterprise_id
5640 ,p_competence_id => p_rec.competence_id
5641 ,p_assessment_id => p_rec.assessment_id
5642 ,p_assessment_type_id => p_rec.assessment_type_id
5643 ,p_activity_version_id => p_rec.activity_version_id
5644 ,p_organization_id => p_rec.organization_id
5645 ,p_job_id => p_rec.job_id
5646 ,p_valid_grade_id => p_rec.valid_grade_id
5647 ,p_position_id => p_rec.position_id
5648
5649 ,p_person_id => p_rec.person_id
5650 ,p_parent_competence_element_id
5651 => p_rec.parent_competence_element_id
5652 ,p_effective_date_to => p_rec.effective_date_to
5653 ,p_effective_date_from => p_rec.effective_date_from
5654 ,p_proficiency_level_id => p_rec.proficiency_level_id
5655 ,p_high_proficiency_level_id
5656 => p_rec.high_proficiency_level_id
5657 ,p_weighting_level_id => p_rec.weighting_level_id
5658 ,p_rating_level_id => p_rec.rating_level_id
5659 ,p_effective_date => p_effective_date
5660 ,p_type => p_rec.type
5661 ,p_party_id => p_rec.party_id -- HR/TCA merge
5662 ,p_qualification_type_id => p_rec.qualification_type_id
5663 );
5664
5665 --
5666 hr_utility.set_location(l_proc, 70);
5667 /*
5668 -- Business Rule Mapping
5669 -- =====================
5670 -- CHK_UNIQ_COMP_QUAL
5671 --
5672 chk_unique_comp_qual
5673 (p_competence_element_id => p_rec.competence_element_id
5674 ,p_competence_id => p_rec.competence_id
5675 ,p_qualification_type_id => p_rec.qualification_type_id
5676 ,p_object_version_number => p_rec.object_version_number
5677 ,p_effective_date => p_effective_date
5678 );
5679
5680 */
5681 hr_utility.set_location(l_proc, 80);
5682
5683 --
5684 -- do the descriptive flex validation.
5685 --
5686 per_cel_bus.chk_df(p_rec => p_rec);
5687
5688 hr_utility.set_location(l_proc, 80);
5689
5690 --
5691 -- do the developer descriptive flex validation.
5692 --
5693 per_cel_bus.chk_ddf(p_rec => p_rec);
5694
5695 hr_utility.set_location('Leaving:'||l_proc, 80);
5696 End update_validate;
5697 --
5698 -- ----------------------------------------------------------------------------
5699 -- |---------------------------< delete_validate >----------------------------|
5700 -- ----------------------------------------------------------------------------
5701 Procedure delete_validate(p_rec in per_cel_shd.g_rec_type) is
5702 --
5703 l_proc varchar2(72) := g_package||'delete_validate';
5704 --
5705 Begin
5706 hr_utility.set_location('Entering:'||l_proc, 5);
5707 --
5708 -- Call all supporting business operations
5709 -- Business Rule Mapping
5710 -- =====================
5711 -- CHK_COMP_ELEMENT_DELETE
5712 chk_comp_element_delete
5713
5714 (p_competence_element_id => per_cel_shd.g_old_rec.competence_element_id
5715 ,p_business_group_id => per_cel_shd.g_old_rec.business_group_id
5716 ,p_parent_competence_element_id
5717 => per_cel_shd.g_old_rec.parent_competence_element_id
5718 ,p_type => per_cel_shd.g_old_rec.type
5719 ,p_competence_type => per_cel_shd.g_old_rec.competence_type
5720 ,p_assessment_id => per_cel_shd.g_old_rec.assessment_id
5721 ,p_assessment_type_id => per_cel_shd.g_old_rec.assessment_type_id
5722 ,p_competence_id => per_cel_shd.g_old_rec.competence_id
5723 ,p_group_competence_type => per_cel_shd.g_old_rec.group_competence_type
5724 );
5725 --
5726
5727 hr_utility.set_location(' Leaving:'||l_proc, 10);
5728 End delete_validate;
5729 --
5730 -- ----------------------------------------------------------------------------
5731 -- |-----------------------< return_legislation_code >-------------------------|
5732 -- ----------------------------------------------------------------------------
5733 Function return_legislation_code
5734 ( p_competence_element_id in number
5735 ) return varchar2 is
5736 --
5737 -- Declare cursor
5738 --
5739 cursor csr_leg_code is
5740 select legislation_code
5741 from per_business_groups pbg,
5742 per_competence_elements pce
5743 where pce.competence_element_id = p_competence_element_id
5744 and pbg.business_group_id = pce.business_group_id;
5745
5746 l_proc varchar2(72) := g_package||'return_legislation_code';
5747 l_legislation_code varchar2(150);
5748 l_business_group_flag varchar2(1);
5749 --
5750 Begin
5751 hr_utility.set_location('Entering:'||l_proc, 5);
5752 --
5753 -- Ensure that all the mandatory parameters are not null
5754 --
5755 hr_api.mandatory_arg_error (p_api_name => l_proc,
5756 p_argument => 'competence_element_id',
5757 p_argument_value => p_competence_element_id );
5758 --
5759 Select 'Y' into l_business_group_flag
5760 from per_competence_elements
5761 where competence_element_id = p_competence_element_id
5762 and business_group_id is null;
5763
5764
5765 if l_business_group_flag = 'Y' then
5766 return null;
5767 end if;
5768
5769 if nvl(g_competence_element_id, hr_api.g_number) = p_competence_element_id then
5770 --
5771 -- The legislation code has already been found with a previous
5772 -- call to this function. Just return the value in the global
5773 -- variable.
5774 --
5775 l_legislation_code := g_legislation_code;
5776 hr_utility.set_location(l_proc, 20);
5777 else
5778 --
5779 -- The ID is different to the last call to this function
5780 -- or this is the first call to this function.
5781 --
5782 open csr_leg_code;
5783 fetch csr_leg_code into l_legislation_code;
5784 if csr_leg_code%notfound then
5785 close csr_leg_code;
5786 --
5787 -- The primary key is invalid therefore we must error out
5788 --
5789 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5790 hr_multi_message.add
5791 (p_associated_column1 => 'PER_COMPETENCE_ELEMENTS.COMPETENCE_ELEMENT_ID');
5792 hr_utility.raise_error;
5793 end if;
5794 --
5795 close csr_leg_code;
5796 g_competence_element_id:= p_competence_element_id;
5797 g_legislation_code := l_legislation_code;
5798 end if;
5799 return l_legislation_code;
5800 --
5801 hr_utility.set_location(' Leaving:'||l_proc, 10);
5802 --
5803 End return_legislation_code;
5804 --
5805 --
5806
5807 end per_cel_bus;
[Home] [Help]