1 Package Body per_cpn_bus as
2 /* $Header: pecpnrhi.pkb 120.0 2005/05/31 07:14:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cpn_bus.'; -- Global package name
9 --
10 -- The following 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_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |----------------------< chk_non_updateable_args >-----------------------|
18 -- ----------------------------------------------------------------------------
19 --
20 Procedure chk_non_updateable_args(p_rec in per_cpn_shd.g_rec_type) is
21 --
22 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
23 l_error exception;
24 l_argument varchar2(30);
25 --
26 Begin
27 hr_utility.set_location('Entering:'||l_proc, 5);
28 --
29 -- Only proceed with validation if a row exists for
30 -- the current record in the HR Schema
31 --
32 if not per_cpn_shd.api_updating
33 (p_competence_id => p_rec.competence_id
34 ,p_object_version_number => p_rec.object_version_number
35 ) then
36 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
37 hr_utility.set_message_token('PROCEDURE', l_proc);
38 hr_utility.set_message_token('STEP', '5');
39 end if;
40 --
41 hr_utility.set_location(l_proc, 6);
42 --
43 if p_rec.business_group_id <> per_cpn_shd.g_old_rec.business_group_id then
44 l_argument := 'business_group_id';
45 raise l_error;
46 end if;
47 hr_utility.set_location(l_proc, 7);
48
49 if per_cpn_shd.g_old_rec.competence_cluster = 'UNIT_STANDARD'
50 and (p_rec.competence_cluster is NULL
51 or p_rec.competence_cluster <> 'UNIT_STANDARD') then
52 --
53 fnd_message.set_name('PER','HR_449146_QUA_FWK_CHG_CLUSTER');
54 fnd_message.raise_error;
55 end if;
56 hr_utility.set_location(l_proc, 8);
57
58 --
59 exception
60 when l_error then
61 hr_api.argument_changed_error
62 (p_api_name => l_proc
63 ,p_argument => l_argument);
64 when others then
65 raise;
66 hr_utility.set_location(' Leaving:'||l_proc, 12);
67 end chk_non_updateable_args;
68 --
69 -------------------------------------------------------------------------------
70 ----------------------< chk_definition_id >------------------------------------
71 -------------------------------------------------------------------------------
72 --
73 -- Description:
74 -- - Validates that a valid competence name is entered
75 --
76 -- - Validates that it is unique within the business group
77 --
78 -- Pre_conditions:
79 -- A valid business_group_id
80 --
81 --
82 -- In Arguments:
83 -- p_business_group_id
84 -- p_name
85 --
86 -- Post Success:
87 -- Process continues if :
88 -- All the in parameters are valid.
89 --
90 -- Post Failure:
91 -- An application error is raised and processing is terminated if any of
92 -- the following cases are found :
93 -- - name is invalid
94 -- - The business group is invalid
95 -- - name is not unique within business group
96 --
97 -- Access Status
98 -- Internal Table Handler Use Only.
99 --
100 --
101 --
102 procedure chk_definition_id
103 (p_competence_id in per_competences.competence_id%TYPE
104 ,p_business_group_id in per_competences.business_group_id%TYPE
105 ,p_competence_definition_id in per_competences.competence_definition_id%TYPE
106 ,p_object_version_number in per_competences.object_version_number%TYPE
107 )
108 is
109 --
110 l_exists per_competences.business_group_id%TYPE;
111 l_api_updating boolean;
112 l_proc varchar2(72) := g_package||'chk_definition_id';
113 --
114 -- Cursor to check if competence_definition_id is unique within business group
115 --
116 cursor csr_chk_definition_id is
117 select business_group_id
118 from per_competences pc
119 where (( p_competence_id is null)
120 or (p_competence_id <> pc.competence_id)
121 )
122 and competence_definition_id = p_competence_definition_id
123 and p_business_group_id is null
124 UNION
125 select business_group_id
126 from per_competences pc
127 where ( (p_competence_id is null)
128 or(p_competence_id <> pc.competence_id)
129 )
130 and competence_definition_id = p_competence_definition_id
131 and p_business_group_id is not null
132 and ( business_group_id + 0 = p_business_group_id or
133 business_group_id is null);
134 --
135 begin
136 hr_utility.set_location('Entering:'|| l_proc, 1);
137 --
138 -- Check mandatory parameters have been set
139 --
140 -- Only proceed with validation if :
141 -- a) The current g_old_rec is current and
142 -- b) The value for name has changed
143 --
144 l_api_updating := per_cpn_shd.api_updating
145 (p_competence_id => p_competence_id
146 ,p_object_version_number => p_object_version_number);
147 --
148 if ( (l_api_updating and (per_cpn_shd.g_old_rec.competence_definition_id
149 <> nvl(p_competence_definition_id,hr_api.g_number))
150 ) or
151 (NOT l_api_updating)
152 ) then
153 --
154 -- hr_utility.set_loc --
155 -- check if the user has entered a name, as name is
156 -- is mandatory column.
157 --
158 if p_competence_definition_id is null then
159 hr_utility.set_message(801,'HR_51441_COMP_NAME_MANDATORY');
160 hr_utility.raise_error;
161 end if;
162
163 --
164 -- check if name is unique
165 --
166 open csr_chk_definition_id;
167 fetch csr_chk_definition_id into l_exists;
168 if csr_chk_definition_id%found then
169 hr_utility.set_location(l_proc, 3);
170 -- name is not unique
171 close csr_chk_definition_id;
172 if l_exists is null then
173 fnd_message.set_name('PER','HR_52698_COMP_NAME_IN_GLOB');
174 fnd_message.raise_error;
175 else
176 fnd_message.set_name('PER','HR_52699_COMP_NAME_IN_BUSGRP');
177 fnd_message.raise_error;
178 end if;
179 end if;
180 end if;
181 hr_utility.set_location('Leaving:'|| l_proc, 10);
182 end chk_definition_id;
183 --
184 -- ----------------------------------------------------------------------------
185 -- |------------------------< chk_competence_dates >-------------------------|
186 -- ----------------------------------------------------------------------------
187 --
188 -- Description :
189 -- Perform check to make sure that :
190 -- - Validates that the start date of the competence is entered
191 -- - Validates that end date is later or equal to start date
192 -- -
193 -- - if called from update mode then
194 -- make sure that the competence start date and end date do not invalidate
195 -- competence element ie. competence start date has to be less than or equal
196 -- to competence element start date and competence end date has to be greater
197 -- than or equal to competence element end date
198 --
199 -- Pre-requisites
200 -- valid competence name
201 -- valid business group id
202 --
203 -- In Prameters
204 -- p_date_from
205 -- p_date_to
206 -- p_competence_id
207 -- p_called_from
208 --
209 -- Post Success
210 -- Processing continues.
211 --
212 -- Post Failure
213 -- An application error is raised and processing is terminated if any of
214 -- the following cases are found :
215 -- - date_from is not set
216 -- - date_to is not later than date_from
217 --
218 -- Access Status
219 -- Internal Development Use Only
220 --
221 Procedure chk_competence_dates
222 (p_date_from in per_competences.date_from%TYPE
223 ,p_date_to in per_competences.date_to%TYPE
224 ,p_competence_id in per_competences.competence_id%TYPE default null
225 ,p_called_from in varchar2 default null
226 ) is
227 --
228 l_exists varchar2(1);
229 l_proc varchar2(72):=g_package||'chk_competence_dates';
230
231 -- bug fix 4132284.
232 -- Condition to check the date to of competence elements is relaxed to only
233 -- whether the from date of the competence element is later than the new
234 -- competence end date.
235
236 Cursor csr_check_dates_in_ele is
237 select 'Y'
238 from per_competence_elements cpe
239 where ( nvl(cpe.effective_date_from,hr_api.g_sot) <
240 nvl(p_date_from, nvl(cpe.effective_date_from,hr_api.g_sot))
241 or nvl(cpe.effective_date_from,hr_api.g_sot) >
242 nvl(p_date_to, nvl(cpe.effective_date_from,hr_api.g_sot))
243 )
244 and cpe.competence_id = p_competence_id
245 --adhunter reinstated the following check for 2533926
246 and cpe.type not in
247 ('COMPETENCE_USAGE');
248 -- ngundura commented the below line for extensible competence requirements
249 -- and cpe.type in
250 -- ('PREREQUISITE','PERSONAL','DELIVERY','PROPOSAL','ASSESSMENT','REQUIREMENT');
251 --
252 begin
253 hr_utility.set_location('Entering:'|| l_proc, 1);
254 --
255 if (p_date_from is NULL) then
256 --
257 hr_utility.set_message(801, 'HR_51598_CPN_DATE_FROM_NULL');
258 hr_utility.raise_error;
259 --
260 end if;
261 --
262 -- The date from has to be >= the date to, else error.
263 --
264 if (p_date_from > nvl(p_date_to,hr_api.g_eot)) then
265 --
266 hr_utility.set_message(801, 'HR_51599_CPN_DATE_TO_LATER');
267 hr_utility.raise_error;
268 --
269 end if;
270 --
271 -- only continue if called from UPDATE check
272 --
273 -- Apart from having the standard date validation check ie.date from <= date_to
274 -- and date_to >= date_from, need to make sure that the user cannot change a date
275 -- so as to invalidate the dates on the competence element.
276 -- Check dates againts competence elements (only in Update mode)
277 --
278 if p_called_from = 'UPDATE'
279
280 and ( p_date_from <> per_cpn_shd.g_old_rec.date_from
281 or nvl(p_date_to,hr_api.g_date)<>nvl(per_cpn_shd.g_old_rec.date_to,hr_api.g_date)
282 ) then
283
284 open csr_check_dates_in_ele;
285 fetch csr_check_dates_in_ele into l_exists;
286 if csr_check_dates_in_ele%found then
287 hr_utility.set_location(l_proc, 3);
288 -- dates out of range
289 close csr_check_dates_in_ele ;
290 hr_utility.set_message(801,'HR_51809_CPN_INVALIDATE_ELE');
291 hr_utility.raise_error;
292 end if;
293 close csr_check_dates_in_ele;
294 end if;
295 hr_utility.set_location('Leaving:'|| l_proc, 10);
296 --
297 end chk_competence_dates;
298 -------------------------------------------------------------------------------
299 --------------------------<chk_certification_required>-------------------------
300 -------------------------------------------------------------------------------
301 --
302 -- Description:
303 -- - Validates that a valid certification required flag is set
304 --
305 -- - Validates that it is exists as lookup code for that type
306 --
307 -- Pre_conditions:
308 -- A valid competence name
309 -- A valid business_group_id
310 --
311 --
312 -- In Arguments:
313 -- p_competence_id
314 -- p_certification_required
315 -- p_object_version_number
316 --
317 -- Post Success:
318 -- Process continues if :
319 -- All the in parameters are valid.
320 --
321 -- Post Failure:
322 -- An application error is raised and processing is terminated if any of
323 -- the following cases are found :
324 -- - certification flag is not set or is invalid
325 --
326 -- Access Status
327 -- Internal Table Handler Use Only.
328 --
329 --
330 --
331 procedure chk_certification_required
332 (p_competence_id in per_competences.competence_id%TYPE
333 ,p_object_version_number in per_competences.object_version_number%TYPE
334 ,p_certification_required in per_competences.certification_required%TYPE
335 ,p_effective_date in date
336 )
337 is
338 --
339 l_api_updating boolean;
340 l_proc varchar2(72) := g_package||'chk_certification_required';
341
342 --
343 begin
344 hr_utility.set_location('Entering:'|| l_proc, 1);
345 --
346 -- Check mandatory parameters have been set
347 --
348 hr_api.mandatory_arg_error
349 (p_api_name => l_proc
350 ,p_argument => 'effective_date'
351 ,p_argument_value => p_effective_date
352 );
353 --
354 -- Only proceed with validation if :
355 -- a) The current g_old_rec is current and
356 -- b) The value for certification required flag has changed
357 --
358 l_api_updating := per_cpn_shd.api_updating
359 (p_competence_id => p_competence_id
360 ,p_object_version_number => p_object_version_number);
361 --
362 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.certification_required,
363 hr_api.g_varchar2)
364 <> nvl(p_certification_required,hr_api.g_varchar2)
365 ) or
366 (NOT l_api_updating)
367 ) then
368 --
369 hr_utility.set_location(l_proc, 2);
370 --
371 --
372 -- If certification required is not null then
373 -- check if the value exists in hr_lookups
374 -- where the lookup_type = 'YES_NO'
375 --
376 --
377 if p_certification_required is not null then
378 if hr_api.not_exists_in_hr_lookups
379 (p_effective_date => p_effective_date
380 ,p_lookup_type => 'YES_NO'
381 ,p_lookup_code => p_certification_required
382 ) then
383 -- error invalid certification flag
384 hr_utility.set_message(801,'HR_51432_COMP_CERTIFY_FLAG');
385 hr_utility.raise_error;
386 end if;
387 hr_utility.set_location(l_proc, 3);
388 end if;
389 end if;
390 hr_utility.set_location('Leaving: '|| l_proc, 10);
391 end chk_certification_required;
392 --
393 -------------------------------------------------------------------------------
394 --------------------------<chk_evaluation_method>------------------------------
395 -------------------------------------------------------------------------------
396 --
397 -- Description:
398 -- - Validates that a valid evaluation method is set
399 --
400 -- - Validates that it is exists as lookup code for that type
401 --
402 -- Pre_conditions:
403 -- A valid competence name
404 -- A valid business_group_id
405 --
406 --
407 -- In Arguments:
408 -- p_competence_id
409 -- p_evaluation_method
410 -- p_object_version_number
411 --
412 -- Post Success:
413 -- Process continues if :
414 -- All the in parameters are valid.
415 --
416 -- Post Failure:
417 -- An application error is raised and processing is terminated if any of
421 -- Access Status
418 -- the following cases are found :
419 -- - evaluation method is invalid
420 --
422 -- Internal Table Handler Use Only.
423 --
424 --
425 procedure chk_evaluation_method
426 (p_competence_id in per_competences.competence_id%TYPE
427 ,p_object_version_number in per_competences.object_version_number%TYPE
428 ,p_evaluation_method in per_competences.evaluation_method%TYPE
429 ,p_effective_date in date
430 ,p_business_group_id in per_competences.business_group_id%TYPE default null
431 )
432 is
433 --
434 l_api_updating boolean;
435 l_proc varchar2(72) := g_package||'chk_evaluation_method';
436
437 --
438 begin
439 hr_utility.set_location('Entering:'|| l_proc, 1);
440 --
441 -- Check mandatory parameters have been set
442 --
443 hr_api.mandatory_arg_error
444 (p_api_name => l_proc
445 ,p_argument => 'effective_date'
446 ,p_argument_value => p_effective_date
447 );
448 --
449 -- Only proceed with validation if :
450 -- a) The current g_old_rec is current and
451 -- b) The value for evaluation method has changed
452 --
453 l_api_updating := per_cpn_shd.api_updating
454 (p_competence_id => p_competence_id
455 ,p_object_version_number => p_object_version_number);
456 --
457 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.evaluation_method,
458 hr_api.g_varchar2)
459 <> nvl(p_evaluation_method,hr_api.g_varchar2)
460 ) or
461 (NOT l_api_updating)
462 ) then
463 --
464 hr_utility.set_location(l_proc, 2);
465 --
466 --
467 -- If evaluation method is not null then
468 -- check if the value exists in hr_lookups
469 -- where the lookup_type = COMPETENCE_EVAL_TYPE
470 --
471 -- ngundura changes for pa requirements.
472 --
473 if p_evaluation_method is not null then
474 if p_business_group_id is null then
475 if hr_api.not_exists_in_hrstanlookups
476 (p_effective_date => p_effective_date
477 ,p_lookup_type => 'COMPETENCE_EVAL_TYPE'
478 ,p_lookup_code => p_evaluation_method
479 ) then
480 hr_utility.set_message(801,'HR_51433_COMP_EVAL_METHOD');
481 hr_utility.raise_error;
482 end if;
483 else
484 if hr_api.not_exists_in_hr_lookups
485 (p_effective_date => p_effective_date
486 ,p_lookup_type => 'COMPETENCE_EVAL_TYPE'
487 ,p_lookup_code => p_evaluation_method
488 ) then
489 -- error invalid evaluation method
490 hr_utility.set_message(801,'HR_51433_COMP_EVAL_METHOD');
491 hr_utility.raise_error;
492 end if;
493 end if;
494 -- ngundura end of the changes.
495 hr_utility.set_location(l_proc, 3);
496 end if;
497 end if;
498 hr_utility.set_location('Leaving: '|| l_proc, 10);
499 end chk_evaluation_method;
500 --
501 -------------------------------------------------------------------------------
502 --------------------------<chk_renewal_period_units>---------------------------
503 -------------------------------------------------------------------------------
504 --
505 -- Description:
506 -- - Validates that a valid renewal period unit is set
507 --
508 -- - Validates that it is exists as lookup code for that type
509 --
510 -- Pre_conditions:
511 -- A valid competence name
512 -- A valid business_group_id
513 --
514 --
515 -- In Arguments:
516 -- p_competence_id
517 -- p_renewal_period_units
518 -- p_object_version_number
519 --
520 -- Post Success:
521 -- Process continues if :
522 -- All the in parameters are valid.
523 --
524 -- Post Failure:
525 -- An application error is raised and processing is terminated if any of
526 -- the following cases are found :
527 -- - renewal period unit is invalid
528 --
529 -- Access Status
530 -- Internal Table Handler Use Only.
531 --
532 --
533 procedure chk_renewal_period_units
534 (p_competence_id in per_competences.competence_id%TYPE
535 ,p_object_version_number in per_competences.object_version_number%TYPE
536 ,p_renewal_period_units in per_competences.renewal_period_units%TYPE
537 ,p_effective_date in date
538 )
539 is
540 --
541 l_api_updating boolean;
542 l_proc varchar2(72) := g_package||'chk_renewal_period_units';
543
544 --
545 begin
546 hr_utility.set_location('Entering:'|| l_proc, 1);
547 --
548 -- Check mandatory parameters have been set
549 --
550 hr_api.mandatory_arg_error
551 (p_api_name => l_proc
552 ,p_argument => 'effective_date'
553 ,p_argument_value => p_effective_date
554 );
555 --
556 -- Only proceed with validation if :
557 -- a) The current g_old_rec is current and
558 -- b) The value for renewal period units has changed
559 --
560 l_api_updating := per_cpn_shd.api_updating
564 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.renewal_period_units,
561 (p_competence_id => p_competence_id
562 ,p_object_version_number => p_object_version_number);
563 --
565 hr_api.g_varchar2)
566 <> nvl(p_renewal_period_units,hr_api.g_varchar2)
567 ) or
568 (NOT l_api_updating)
569 ) then
570 --
571 hr_utility.set_location(l_proc, 2);
572 --
573 --
574 -- If renewal_period_units is not null then
575 -- check if the value exists in hr_lookups
576 -- where the lookup_type = 'UNITS'
577 --
578 --Modified by PASHUN on 28/08/97
579 --If renewal_period_units is not null then
580 --check if the value exists in hr_lookups
581 --where the lookup_type = 'FREQUENCY'
582 --
583 --
584 if p_renewal_period_units is not null then
585 -- if hr_api.not_exists_in_hr_lookups
586 -- (p_effective_date => p_effective_date
587 -- ,p_lookup_type => 'UNITS'
588 -- ,p_lookup_code => p_renewal_period_units
589 -- ) then
590 -- error invalid period units
591 if hr_api.not_exists_in_hr_lookups
592 (p_effective_date => p_effective_date
593 ,p_lookup_type => 'FREQUENCY'
594 ,p_lookup_code => p_renewal_period_units
595 ) then
596 -- error invalid period frequency units
597 hr_utility.set_message(801,'HR_51434_COMP_RENEW_UNIT');
598 hr_utility.raise_error;
599 end if;
600 hr_utility.set_location(l_proc, 3);
601 end if;
602 end if;
603 hr_utility.set_location('Leaving: '|| l_proc, 10);
604 end chk_renewal_period_units;
605 --
606 -------------------------------------------------------------------------------
607 --------------------------<chk_renewable_unit_frequency>-----------------------
608 -------------------------------------------------------------------------------
609 --
610 -- Description:
611 -- - Validates that either both renewal period frequency and renewable
612 -- period unit fields are set, otherwise none of them.
613 --
614 --
615 -- Pre_conditions:
616 -- A valid competence
617 --
618 --
619 -- In Arguments:
620 -- p_competence_id
621 -- p_renewal_period_units
622 -- p_renewal_period_frequency
623 -- p_object_version_number
624 --
625 -- Post Success:
626 -- Process continues if :
627 -- All the in parameters are valid.
628 --
629 -- Post Failure:
630 -- An application error is raised and processing is terminated if any of
631 -- the following cases are found :
632 -- - either of the two fields are null and the other not null
633 --
634 -- Access Status
635 -- Internal Table Handler Use Only.
636 --
637 --
638 procedure chk_renewable_unit_frequency
639 (p_competence_id in per_competences.competence_id%TYPE
640 ,p_object_version_number in per_competences.object_version_number%TYPE
641 ,p_renewal_period_units in per_competences.renewal_period_units%TYPE
642 ,p_renewal_period_frequency in per_competences.renewal_period_frequency%TYPE
643 )
644 is
645 --
646 l_api_updating boolean;
647 l_proc varchar2(72) := g_package||'chk_renewable_unit_frequency';
648
649 --
650 begin
651 hr_utility.set_location('Entering:'|| l_proc, 1);
652 --
653 -- Only proceed with validation if :
654 -- a) The current g_old_rec is current and
655 -- b) The value for renewal period units or renewal period frquency has
656 -- changed
657 --
658 l_api_updating := per_cpn_shd.api_updating
659 (p_competence_id => p_competence_id
660 ,p_object_version_number => p_object_version_number);
661 --
662 if (l_api_updating
663 and
664 ((nvl(per_cpn_shd.g_old_rec.renewal_period_units,
665 hr_api.g_varchar2)
666 <> nvl(p_renewal_period_units,hr_api.g_varchar2))
667 or
668 (nvl(per_cpn_shd.g_old_rec.renewal_period_frequency,
669 hr_api.g_number)
673 --
670 <> nvl(p_renewal_period_frequency,hr_api.g_number))))
671 or
672 NOT l_api_updating then
674 hr_utility.set_location(l_proc, 2);
675 --
676 -- Only check for a valid combination when either of the fields
677 -- are set
678 --
679 if ((p_renewal_period_units is null and
680 p_renewal_period_frequency is not null
681 ) or
682 (p_renewal_period_units is not null and
683 p_renewal_period_frequency is null)
684 ) then
685 -- raise error
686 hr_utility.set_message(801,'HR_51435_COMP_DEPENDENT_FIELDS');
687 hr_utility.raise_error;
688 end if;
689 hr_utility.set_location(l_proc, 3);
690 end if;
691 hr_utility.set_location('Leaving: '|| l_proc, 10);
692 end chk_renewable_unit_frequency;
693 --
694 -----------------------------------------------------------------------------
695 ------------------------<chk_rat_scale_bus_grp_exist>--------------------
696 -----------------------------------------------------------------------------
697 --
698 -- Description:
699 -- - Validates that the rating scale exists and is within the same business
700 -- group as that of competence
701 --
702 -- Pre_conditions:
703 --
704 --
705 -- In Arguments:
706 -- p_rating_scale_id
707 --
708 -- Post Success:
709 -- Process continues if :
710 -- All the in parameters are valid.
711 --
712 -- Post Failure:
713 -- An application error is raised and processing is terminated if any of
714 -- the following cases are found :
715 -- - rating scale does not exist
716 -- -- rating sacle exists but not with the same business group
717 --
718 -- Access Status
719 -- Internal Table Handler Use Only.
720 --
721 --
722 procedure chk_rat_scale_bus_grp_exist
723 (p_competence_id in per_competences.competence_id%TYPE
724 ,p_object_version_number in per_competences.object_version_number%TYPE
725 ,p_rating_scale_id in per_rating_scales.rating_scale_id%TYPE
726 ,p_business_group_id in per_competences.business_group_id%TYPE default null
727 )
728 is
729 --
730 l_exists varchar2(1);
731 l_api_updating boolean;
732 l_proc varchar2(72) := g_package||'chk_rat_scale_bus_grp_exist';
733 l_business_group_id per_rating_scales.business_group_id%TYPE;
734 --
735 --
736 -- Cursor to check if rating scale exists
737 --
738 Cursor csr_rat_scale_bus_grp_exist
739 is
740 select business_group_id
741 from per_rating_scales
742 where rating_scale_id = p_rating_scale_id;
743 --
744 --
745 begin
746 hr_utility.set_location('Entering:'|| l_proc, 1);
747 --
748 -- Only proceed with validation if :
749 -- a) The current g_old_rec is current and
750 -- b) The value for rating scale has changed
751 --
752 l_api_updating := per_cpn_shd.api_updating
753 (p_competence_id => p_competence_id
754 ,p_object_version_number => p_object_version_number);
755 --
756 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.rating_scale_id,
757 hr_api.g_number)
758 <> nvl(p_rating_scale_id,hr_api.g_number)
759 ) or
760 (NOT l_api_updating)
761 ) then
762 --
763 hr_utility.set_location(l_proc, 2);
764 --
765 if p_rating_scale_id is not null then
766 open csr_rat_scale_bus_grp_exist;
767 fetch csr_rat_scale_bus_grp_exist into l_business_group_id;
768 if csr_rat_scale_bus_grp_exist%notfound then
769 close csr_rat_scale_bus_grp_exist;
770 hr_utility.set_message(801,'HR_51452_COMP_RAT_SC_NOT_EXIST');
771 hr_utility.raise_error;
772 end if;
773 close csr_rat_scale_bus_grp_exist;
774 -- check if rating sacel is in the same business group
775 -- ngundura changes done for pa requirements.
776 if p_business_group_id is null then
777 if l_business_group_id is not null then
778 hr_utility.set_message(801,'HR_51453_COMP_DIFF_BUS_GRP');
779 hr_utility.raise_error;
780 end if;
781 else
782 if nvl(l_business_group_id,hr_api.g_number) <> p_business_group_id and l_business_group_id is not null then
783 hr_utility.set_message(801,'HR_51453_COMP_DIFF_BUS_GRP');
784 hr_utility.raise_error;
785 end if;
786 end if;
787 --
788 end if;
789 hr_utility.set_location(l_proc, 3);
790 end if;
791 hr_utility.set_location('Leaving: '|| l_proc, 10);
792 --
793 end chk_rat_scale_bus_grp_exist;
794 -----------------------------------------------------------------------------
795 ------------------------<chk_rating_scale_type>------------------------------
796 -----------------------------------------------------------------------------
797 --
798 -- Description:
799 -- - Validates that only rating scale of type 'PROFICIENCY' is allowed
800 -- to be assigned to a competence
801 --
802 -- Pre_conditions:
803 -- A valid competence name
804 -- A valid business_group_id
808 -- p_competence_id
805 --
806 --
807 -- In Arguments:
809 -- p_rating_scale_id
810 --
811 -- Post Success:
812 -- Process continues if :
813 -- All the in parameters are valid.
814 --
815 -- Post Failure:
816 -- An application error is raised and processing is terminated if any of
817 -- the following cases are found :
818 -- - rating scale is not of type 'Proficiency'
819 --
820 -- Access Status
821 -- Internal Table Handler Use Only.
822 --
823 --
824 procedure chk_rating_scale_type
825 (p_competence_id in per_competences.competence_id%TYPE
826 ,p_object_version_number in per_competences.object_version_number%TYPE
827 ,p_rating_scale_id in per_rating_scales.rating_scale_id%TYPE
828 )
829 is
830 --
831 l_exists varchar2(1);
832 l_api_updating boolean;
833 l_proc varchar2(72) := g_package||'chk_rating_scale_type';
834 --
835 --
836 -- Cursor to check if rating scale is of type 'Proficiency'
837 --
838 Cursor csr_chk_rating_scale_type
839 is
840 select 'Y'
841 from per_rating_scales
842 where rating_scale_id = p_rating_scale_id
843 and upper(type) = 'PROFICIENCY';
844 --
845 --
846 begin
847 hr_utility.set_location('Entering:'|| l_proc, 1);
848 --
849 -- Only proceed with validation if :
850 -- a) The current g_old_rec is current and
851 -- b) The value for rating scale has changed
852 --
853 l_api_updating := per_cpn_shd.api_updating
854 (p_competence_id => p_competence_id
855 ,p_object_version_number => p_object_version_number);
856 --
857 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.rating_scale_id,
858 hr_api.g_number)
859 <> nvl(p_rating_scale_id,hr_api.g_number)
860 ) or
861 (NOT l_api_updating)
862 ) then
863 --
864 hr_utility.set_location(l_proc, 2);
865 --
866 if p_rating_scale_id is not null then
867 open csr_chk_rating_scale_type;
868 fetch csr_chk_rating_scale_type into l_exists;
869 if csr_chk_rating_scale_type%notfound then
870 close csr_chk_rating_scale_type;
871 hr_utility.set_message(801,'HR_51442_COMP_TYPE_NOT_PROF');
872 hr_utility.raise_error;
873 end if;
874 close csr_chk_rating_scale_type;
875 end if;
876 hr_utility.set_location(l_proc, 3);
877 end if;
878 hr_utility.set_location('Leaving: '|| l_proc, 10);
879 --
880 end chk_rating_scale_type;
881 --
882 -----------------------------------------------------------------------------
883 ------------------------<chk_competence_has_prof>----------------------------
884 -----------------------------------------------------------------------------
885 --
886 -- Description:
887 -- - checks if competence has proficiency levels. If yes than
888 -- cannot assign a rating scale to this competence
889 --
890 -- Pre_conditions:
891 -- A valid competence
892 -- A valid business_group_id
893 --
894 --
895 -- In Arguments:
896 -- p_competence_id
897 -- p_rating_scale_id
898 --
899 -- Post Success:
900 -- Process continues if :
901 -- All the in parameters are valid.
902 --
903 -- Post Failure:
904 -- An application error is raised and processing is terminated if any of
905 -- the following cases are found :
906 -- - competence has proficiency levels
907 --
908 -- Access Status
909 -- Internal Table Handler Use Only.
910 --
911 --
912 procedure chk_competence_has_prof
913 (p_competence_id in per_competences.competence_id%TYPE
914 ,p_object_version_number in per_competences.object_version_number%TYPE
915 ,p_rating_scale_id in per_rating_scales.rating_scale_id%TYPE
916 )
917 is
918 --
919 l_exists varchar2(1);
920 l_api_updating boolean;
921 l_proc varchar2(72) := g_package||'chk_competence_has_prof';
922 --
923 --
924 -- Cursor to check if competence has Proficiency levels
925 --
926 cursor csr_comp_has_prof_levels is
927 select 'Y'
928 from per_rating_levels
929 where competence_id = p_competence_id;
930 --
931 begin
932 hr_utility.set_location('Entering:'|| l_proc, 1);
933 --
934 -- Only proceed with validation if :
935 -- a) The current g_old_rec is current and
936 -- b) The value for rating scale has changed
937 --
938 l_api_updating := per_cpn_shd.api_updating
939 (p_competence_id => p_competence_id
940 ,p_object_version_number => p_object_version_number);
941 --
942 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.rating_scale_id,
943 hr_api.g_number)
944 <> nvl(p_rating_scale_id,hr_api.g_number)
948 --
945 ) or
946 (NOT l_api_updating)
947 ) then
949 hr_utility.set_location(l_proc, 2);
950 --
951 if p_rating_scale_id is not null then
952 open csr_comp_has_prof_levels;
953 fetch csr_comp_has_prof_levels into l_exists;
954 if csr_comp_has_prof_levels%found then
955 close csr_comp_has_prof_levels;
956 hr_utility.set_message(801,'HR_51437_COMP_PROF_SCALE');
957 hr_utility.raise_error;
958 end if;
959 close csr_comp_has_prof_levels;
960 end if;
961 hr_utility.set_location(l_proc, 3);
962 end if;
963 hr_utility.set_location('Leaving: '|| l_proc, 10);
964 --
965 end chk_competence_has_prof;
966 -------------------------------------------------------------------------------
967 --------------------------<chk_competence_rating_update>-----------------------
968 -------------------------------------------------------------------------------
969 --
970 -- Description:
971 -- - Validates that a rating scale on competence can only be updated
972 -- if the proficeincy rating levels for that rating scale are not used in
973 -- competence element
974 --
975 -- Pre_conditions:
976 -- A valid competence_id
977 -- A valid business_group_id
978 --
979 --
980 -- In Arguments:
981 -- p_competence_id
982 -- p_rating_scale_id
983 -- p_object_version_number
984 --
985 -- Post Success:
986 -- Process continues if :
987 -- All the in parameters are valid.
988 --
989 -- Post Failure:
990 -- An application error is raised and processing is terminated if any of
991 -- the following cases are found :
992 -- - the rating scale has rating levels that are used in competence
993 -- element
994 --
995 -- Access Status
996 -- Internal Table Handler Use Only.
997 --
998 --
999 procedure chk_competence_rating_update
1000 (p_competence_id in per_competences.competence_id%TYPE
1001 ,p_object_version_number in per_competences.object_version_number%TYPE
1002 ,p_rating_scale_id in per_competences.rating_scale_id%TYPE
1003 )
1004 is
1005 --
1006 l_exists varchar2(1);
1007 l_api_updating boolean;
1008 l_proc varchar2(72) := g_package||'chk_competence_rating_update';
1009 --
1010 --
1011 -- Cursor to check if rating level is used within a competence element
1012 --
1013 -- change made to fix bug 569647 (added competence_id)
1014 --
1015 cursor csr_rating_level_in_ele is
1016 select 'Y'
1017 from per_rating_levels rl,
1018 per_competence_elements ce
1019 where ((rl.rating_level_id = ce.proficiency_level_id) or
1020 (rl.rating_level_id = ce.high_proficiency_level_id))
1021 and rl.rating_scale_id = nvl(per_cpn_shd.g_old_rec.rating_scale_id,-9999)
1022 and ce.competence_id = p_competence_id;
1023 --
1024 begin
1025 hr_utility.set_location('Entering:'|| l_proc, 1);
1026 --
1027 -- Only proceed with validation if :
1028 -- a) The current g_old_rec is current and
1029 -- b) The value for rating scale has changed
1030 --
1031 l_api_updating := per_cpn_shd.api_updating
1032 (p_competence_id => p_competence_id
1033 ,p_object_version_number => p_object_version_number);
1034 --
1035 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.rating_scale_id,
1036 hr_api.g_number)
1037 <> nvl(p_rating_scale_id,hr_api.g_number)
1038 ) or
1039 (NOT l_api_updating)
1040 ) then
1041 --
1042 hr_utility.set_location(l_proc, 2);
1043 --
1044 open csr_rating_level_in_ele;
1045 fetch csr_rating_level_in_ele into l_exists;
1046 if csr_rating_level_in_ele%found then
1047 close csr_rating_level_in_ele;
1048 hr_utility.set_message(801,'HR_51443_COMP_LEVELS_IN_ELE');
1049 hr_utility.raise_error;
1050 hr_utility.set_location(l_proc, 2);
1051 else
1052 close csr_rating_level_in_ele;
1053 end if;
1054 --
1055 hr_utility.set_location(l_proc, 3);
1056 --
1057 end if;
1058 --
1059 hr_utility.set_location('Leaving: '|| l_proc, 10);
1060 --
1061 end chk_competence_rating_update;
1062 --
1063 -------------------------------------------------------------------------------
1064 --------------------------<chk_competence_delete>-------------------------
1065 -------------------------------------------------------------------------------
1066 --
1067 -- Description:
1068 -- - Validates that a competence cannot be deleted if used in:
1069 -- a) Competence element
1070 --
1071 -- b) Proficiency level - this should have been deleted by BP
1072 -- c) Competence Type Usages - this should have been deleted by BP
1073 --
1074 -- If the rows from the proficiency level and Competence Type Usages
1075 -- still exists while deleting the competence then it will error.
1076 --
1077 -- Pre_conditions:
1078 -- A valid competence id
1079 --
1080 --
1081 -- In Arguments:
1082 -- p_competence_id
1086 -- Process continues if:
1083 -- p_object_version_number
1084 --
1085 -- Post Success:
1087 -- competnece is not referenced
1088 --
1089 -- Post Failure:
1090 -- An application error is raised and processing is terminated if any of
1091 -- the following cases are found :
1092 -- - competence id is invalid
1093 -- - competence exists in competence elements and proficiency levels
1094 -- Access Status
1095 -- Internal Table Handler Use Only.
1096 --
1097 --
1098 procedure chk_competence_delete
1099 (p_competence_id in per_competences.competence_id%TYPE
1100 ,p_object_version_number in per_competences.object_version_number%TYPE
1101 )
1102 is
1103 --
1104 l_exists varchar2(1);
1105 l_proc varchar2(72) := g_package||'chk_competence_delete';
1106 --
1107 -- Cursor to check if competence is used in per_competence_elements
1108 --
1109 cursor csr_chk_comp_exists_in_ele is
1110 select 'Y'
1111 from per_competence_elements
1112 where competence_id = p_competence_id;
1113 --
1114 -- Cursor to check if competence is used in per_rating_levels
1115 --
1116 cursor csr_chk_comp_exists_in_rl is
1117 select 'Y'
1118 from per_rating_levels
1119 where competence_id = p_competence_id;
1120 --
1121 -- Cursor to check if competence is used in per_competende_outcomes
1122 --
1123 cursor csr_chk_comp_exists_in_co is
1124 select 'Y'
1125 from per_competence_outcomes
1126 where competence_id = p_competence_id;
1127 --
1128 begin
1129 hr_utility.set_location('Entering:'|| l_proc, 1);
1130 --
1131 -- Check mandatory parameters have been set
1132 --
1133 hr_api.mandatory_arg_error
1134 (p_api_name => l_proc
1135 ,p_argument => 'competence_id'
1136 ,p_argument_value => p_competence_id
1137 );
1138 --
1139 open csr_chk_comp_exists_in_ele;
1140 fetch csr_chk_comp_exists_in_ele into l_exists;
1141 if csr_chk_comp_exists_in_ele%found then
1142 close csr_chk_comp_exists_in_ele;
1143 hr_utility.set_message(801,'HR_51440_COMP_EXIST_IN_ELE');
1144 hr_utility.raise_error;
1145 hr_utility.set_location(l_proc, 2);
1146 else
1147 close csr_chk_comp_exists_in_ele;
1148 open csr_chk_comp_exists_in_rl;
1149 fetch csr_chk_comp_exists_in_rl into l_exists;
1150 if csr_chk_comp_exists_in_rl%found then
1151 close csr_chk_comp_exists_in_rl;
1152 hr_utility.set_message(801,'HR_51439_COMP_PROF_LVL_EXIST');
1153 hr_utility.raise_error;
1154 hr_utility.set_location(l_proc, 3);
1155 end if;
1156 close csr_chk_comp_exists_in_rl;
1157 open csr_chk_comp_exists_in_co;
1158 fetch csr_chk_comp_exists_in_co into l_exists;
1159 if csr_chk_comp_exists_in_co%found then
1160 close csr_chk_comp_exists_in_co;
1161 hr_utility.set_message(800,'HR_449134_QUA_FWK_COMP_TAB_REF');
1162 hr_utility.raise_error;
1163 hr_utility.set_location(l_proc, 4);
1164 end if;
1165 close csr_chk_comp_exists_in_co;
1166 end if;
1167 --
1168 hr_utility.set_location('Leaving: '|| l_proc, 10);
1169 --
1170 end chk_competence_delete;
1171 --
1172 -- -----------------------------------------------------------------------------
1173 -- |-------------------------<chk_set_radio_button>---------------------------|
1174 -- -----------------------------------------------------------------------------
1175 -- Description:
1176 -- Checks if the competence has a prficiency rating scale, if yes
1177 -- returns 'PS' (Proficiency Scale exists).
1178 -- Checks if the competence has levels, if yes returns
1179 -- 'CL' (Competence Levels exists)
1180 -- Else it will return 'PS' as default.
1181 --
1182 -- This function is called by the Competence Base View (too) to set the
1183 -- value of a radio group in the form accordingly.
1184 --
1185 -- In Arguments:
1186 -- p_competence_id
1187 -- p_rating_scale_id
1188 --
1189 -- Access Status
1190 -- Internal Table Handler Use Only.
1191 --
1192 Function chk_set_radio_button (p_competence_id in number,
1193 p_rating_scale_id in number)
1194 Return varchar2 is
1195 --
1196 -- check if the proficiency rating scale has any
1197 -- levels
1198 --
1199 cursor c_competence_levels is
1200 select 1
1201 from per_rating_levels
1202 where competence_id = p_competence_id;
1203 --
1204 -- variables
1205 --
1206 l_levels_exists number;
1207 l_proc varchar2(72) := g_package||'chk_comp_levels_func';
1208 --
1209 -- 'PS' - stands for Proficiency Scale
1210 -- 'CL' - stands for Competence Levels
1211 --
1212 Begin
1213 if p_rating_scale_id is not null then
1214 Return ('PS');
1215 end if;
1216 -- check if levels exist for competence
1217 open c_competence_levels;
1218 fetch c_competence_levels into l_levels_exists;
1219 close c_competence_levels;
1220 if l_levels_exists is not null then
1221 Return ('CL');
1222 else
1223 Return ('PS');
1224 end if;
1225 --
1226 End chk_set_radio_button;
1227 --
1228 -------------------------------------------------------------------------------
1232 -- Description:
1229 --------------------------<chk_competence_cluster>------------------------------
1230 -------------------------------------------------------------------------------
1231 --
1233 -- This procedure checks that a competence_cluster exists in HR_LOOKUPS
1234 -- for the lookup type 'PER_COMPETENCE_CLUSTER'.
1235 --
1236 -- Pre_conditions:
1237 -- None.
1238 --
1239 -- In Arguments:
1240 -- p_competence_id
1241 -- p_competence_cluster
1242 -- p_object_version_number
1243 -- p_effective_date
1244 --
1245 -- Post Success:
1246 -- Process continues if :
1247 -- All the in parameters are valid.
1248 --
1249 -- Post Failure:
1250 -- Error raised.
1251 --
1252 -- Access Status
1253 -- Internal Table Handler Use Only.
1254 --
1255 --
1256 procedure chk_competence_cluster
1257 (p_competence_id in per_competences.competence_id%TYPE
1258 ,p_competence_cluster in per_competences.competence_cluster%TYPE
1259 ,p_object_version_number in per_competences.object_version_number%TYPE
1260 ,p_effective_date in date
1261 )
1262 is
1263 --
1264 l_proc varchar2(72) := g_package||'chk_competence_cluster';
1265 l_api_updating boolean;
1266 --
1267 begin
1268 hr_utility.set_location('Entering:'|| l_proc, 10);
1269 --
1270 -- Only proceed with validation if :
1271 -- a) The current g_old_rec is current and
1272 -- b) The value for cluster name has changed
1273 --
1274 l_api_updating := per_cpn_shd.api_updating
1275 (p_competence_id => p_competence_id
1276 ,p_object_version_number => p_object_version_number);
1277 --
1278 if (p_competence_cluster is not NULL) then
1279 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.competence_cluster,
1280 hr_api.g_varchar2)
1281 <> nvl(p_competence_cluster,hr_api.g_varchar2)
1282 ) or
1283 (NOT l_api_updating)
1284 ) then
1285 --
1286 hr_utility.set_location(l_proc, 20);
1287 --
1288 -- Check that the category exists in HR_LOOKUPS
1289 --
1290 IF hr_api.not_exists_in_hr_lookups
1291 (p_effective_date => p_effective_date
1292 ,p_lookup_type => 'PER_COMPETENCE_CLUSTER'
1293 ,p_lookup_code => p_competence_cluster) THEN
1294 --
1295 hr_utility.set_location(l_proc, 30);
1296 --
1297 hr_utility.set_message(800, 'HR_449088_COMPETENCE_CLSTR_LKP');
1298 hr_utility.raise_error;
1299 --
1300 END IF;
1301 end if;
1302 end if;
1303 --
1304 hr_utility.set_location('Leaving: '|| l_proc, 40);
1305 --
1306 end chk_competence_cluster;
1307 --
1308 -------------------------------------------------------------------------------
1309 --------------------------<chk_unit_standard_id>------------------------------
1310 -------------------------------------------------------------------------------
1311 --
1312 -- Description:
1313 -- This procedure checks that a unit_standard_id is unique
1314 --
1315 -- Pre_conditions:
1316 -- None.
1317 --
1318 -- In Arguments:
1319 -- p_competence_id
1320 -- p_unit_standard_id
1321 -- p_business_group_id
1322 -- p_object_version_number
1323 -- p_effective_date
1324 --
1325 -- Post Success:
1326 -- Process continues if :
1327 -- All the in parameters are valid.
1328 --
1329 -- Post Failure:
1330 -- Error raised.
1331 --
1332 -- Access Status
1333 -- Internal Table Handler Use Only.
1334 --
1335 --
1336 procedure chk_unit_standard_id
1337 (p_competence_id in per_competences.competence_id%TYPE
1338 ,p_unit_standard_id in per_competences.unit_standard_id%TYPE
1339 ,p_business_group_id in per_competences.business_group_id%TYPE
1340 ,p_object_version_number in per_competences.object_version_number%TYPE
1341 ,p_effective_date in date
1342 )
1343 is
1344 --
1345 --
1346 -- declare cursor
1347 --
1348 cursor csr_local_unit_standard_id is
1349 select 1 from per_competences
1350 where unit_standard_id = p_unit_standard_id
1351 and business_group_id = p_business_group_id
1352 and p_effective_date between date_from and NVL(date_to, hr_api.g_eot);
1353 --
1354 cursor csr_global_unit_standard_id is
1355 select 1 from per_competences
1356 where unit_standard_id = p_unit_standard_id
1357 and p_effective_date between date_from and NVL(date_to, hr_api.g_eot);
1358
1359 l_proc varchar2(72) := g_package||'chk_unit_standard_id';
1360 l_api_updating boolean;
1361 l_exists varchar2(1);
1362 --
1363 begin
1364 hr_utility.set_location('Entering:'|| l_proc, 10);
1365 --
1366 -- Only proceed with validation if :
1367 -- a) The current g_old_rec is current and
1368 -- b) The value for unit_standard_id has changed
1369 --
1370 l_api_updating := per_cpn_shd.api_updating
1371 (p_competence_id => p_competence_id
1372 ,p_object_version_number => p_object_version_number);
1373 --
1377 <> nvl(p_unit_standard_id,hr_api.g_varchar2)
1374 if (p_unit_standard_id is not NULL) then
1375 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.unit_standard_id,
1376 hr_api.g_varchar2)
1378 ) or
1379 (NOT l_api_updating)
1380 ) then
1381 --
1382 if p_business_group_id is not NULL then
1383 hr_utility.set_location(l_proc, 20);
1384 --
1385 -- Local competence
1386 --
1387 open csr_local_unit_standard_id;
1388 fetch csr_local_unit_standard_id into l_exists;
1389 if csr_local_unit_standard_id%FOUND then
1390 close csr_local_unit_standard_id;
1391 --
1392 hr_utility.set_location(l_proc, 30);
1393 --
1394 hr_utility.set_message(800, 'HR_449089_UNIT_STD_ID_EXISTS');
1395 hr_utility.raise_error;
1396 --
1397 END IF;
1398 close csr_local_unit_standard_id;
1399 else
1400 hr_utility.set_location(l_proc, 40);
1401 --
1402 -- Global competence
1403 --
1404 open csr_global_unit_standard_id;
1405 fetch csr_global_unit_standard_id into l_exists;
1406 if csr_global_unit_standard_id%FOUND then
1407 close csr_global_unit_standard_id;
1408 --
1409 hr_utility.set_location(l_proc, 50);
1410 --
1411 hr_utility.set_message(800, 'HR_449089_UNIT_STD_ID_EXISTS');
1412 hr_utility.raise_error;
1413 --
1414 END IF;
1415 close csr_global_unit_standard_id;
1416 end if;
1417 end if;
1418 end if;
1419 --
1420 hr_utility.set_location('Leaving: '|| l_proc, 60);
1421 --
1422 end chk_unit_standard_id;
1423 --
1424 -------------------------------------------------------------------------------
1425 ----------------------------< chk_credit_type >--------------------------------
1426 -------------------------------------------------------------------------------
1427 --
1428 -- Description:
1429 -- This procedure checks that a credit_type exists in HR_LOOKUPS
1430 -- for the lookup type 'PER_QUAL_FWK_CREDIT_TYPE'.
1431 --
1432 -- Pre_conditions:
1433 -- None.
1434 --
1435 -- In Arguments:
1436 -- p_competence_id
1437 -- p_credit_type
1438 -- p_object_version_number
1439 -- p_effective_date
1440 --
1441 -- Post Success:
1442 -- Process continues if :
1443 -- All the in parameters are valid.
1444 --
1445 -- Post Failure:
1446 -- Error raised.
1447 --
1448 -- Access Status
1449 -- Internal Table Handler Use Only.
1450 --
1451 --
1452 procedure chk_credit_type
1453 (p_competence_id in per_competences.competence_id%TYPE
1454 ,p_credit_type in per_competences.credit_type%TYPE
1455 ,p_object_version_number in per_competences.object_version_number%TYPE
1456 ,p_effective_date in date
1457 )
1458 is
1459 --
1460 l_proc varchar2(72) := g_package||'chk_credit_type';
1461 l_api_updating boolean;
1462 --
1463 begin
1464 hr_utility.set_location('Entering:'|| l_proc, 10);
1465 --
1466 -- Only proceed with validation if :
1467 -- a) The current g_old_rec is current and
1468 -- b) The value for credit type has changed
1469 --
1470 l_api_updating := per_cpn_shd.api_updating
1471 (p_competence_id => p_competence_id
1472 ,p_object_version_number => p_object_version_number);
1473 --
1474 if p_credit_type is not null then
1475 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.credit_type,
1476 hr_api.g_varchar2)
1477 <> nvl(p_credit_type,hr_api.g_varchar2)
1478 ) or
1479 (NOT l_api_updating)
1480 ) then
1481 --
1482 hr_utility.set_location(l_proc, 20);
1483 --
1484 -- Check that the category exists in HR_LOOKUPS
1485 --
1486 IF hr_api.not_exists_in_hr_lookups
1487 (p_effective_date => p_effective_date
1488 ,p_lookup_type => 'PER_QUAL_FWK_CREDIT_TYPE'
1489 ,p_lookup_code => p_credit_type) THEN
1490 --
1491 hr_utility.set_location(l_proc, 30);
1492 --
1493 hr_utility.set_message(800, 'HR_449092_QUA_FWK_CRDT_TYP_LKP');
1494 hr_utility.raise_error;
1495 --
1496 END IF;
1497 end if;
1498 end if;
1499 --
1500 hr_utility.set_location('Leaving: '|| l_proc, 40);
1501 --
1502 end chk_credit_type;
1503 --
1504 --
1505 -------------------------------------------------------------------------------
1506 ----------------------------< chk_level_type >--------------------------------
1507 -------------------------------------------------------------------------------
1508 --
1509 -- Description:
1510 -- This procedure checks that a level_type exists in HR_LOOKUPS
1511 -- for the lookup type 'PER_QUAL_FWK_LEVEL_TYPE'.
1512 --
1513 -- Pre_conditions:
1514 -- None.
1515 --
1516 -- In Arguments:
1517 -- p_competence_id
1518 -- p_level_type
1522 -- Post Success:
1519 -- p_object_version_number
1520 -- p_effective_date
1521 --
1523 -- Process continues if :
1524 -- All the in parameters are valid.
1525 --
1526 -- Post Failure:
1527 -- Error raised.
1528 --
1529 -- Access Status
1530 -- Internal Table Handler Use Only.
1531 --
1532 --
1533 procedure chk_level_type
1534 (p_competence_id in per_competences.competence_id%TYPE
1535 ,p_level_type in per_competences.credit_type%TYPE
1536 ,p_object_version_number in per_competences.object_version_number%TYPE
1537 ,p_effective_date in date
1538 )
1539 is
1540 --
1541 l_proc varchar2(72) := g_package||'chk_level_type';
1542 l_api_updating boolean;
1543 --
1544 begin
1545 hr_utility.set_location('Entering:'|| l_proc, 10);
1546 --
1547 -- Only proceed with validation if :
1548 -- a) The current g_old_rec is current and
1549 -- b) The value for level type has changed
1550 --
1551 l_api_updating := per_cpn_shd.api_updating
1552 (p_competence_id => p_competence_id
1553 ,p_object_version_number => p_object_version_number);
1554 --
1555 if p_level_type is not null then
1556 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.level_type,
1557 hr_api.g_varchar2)
1558 <> nvl(p_level_type,hr_api.g_varchar2)
1559 ) or
1560 (NOT l_api_updating)
1561 ) then
1562 --
1563 hr_utility.set_location(l_proc, 20);
1564 --
1565 -- Check that the category exists in HR_LOOKUPS
1566 --
1567 IF hr_api.not_exists_in_hr_lookups
1568 (p_effective_date => p_effective_date
1569 ,p_lookup_type => 'PER_QUAL_FWK_LEVEL_TYPE'
1570 ,p_lookup_code => p_level_type) THEN
1571 --
1572 hr_utility.set_location(l_proc, 30);
1573 --
1574 hr_utility.set_message(800, 'HR_449090_QUA_FWK_LVL_TYP_LKP');
1575 hr_utility.raise_error;
1576 --
1577 END IF;
1578 end if;
1579 end if;
1580 --
1581 hr_utility.set_location('Leaving: '|| l_proc, 40);
1582 --
1583 end chk_level_type;
1584 --
1585 --
1586 -------------------------------------------------------------------------------
1587 ----------------------------< chk_level_number >--------------------------------
1588 -------------------------------------------------------------------------------
1589 --
1590 -- Description:
1591 -- This procedure checks that a level_number exists in HR_LOOKUPS
1592 -- for the lookup type 'PER_QUAL_FWK_LEVEL'.
1593 --
1594 -- Pre_conditions:
1595 -- None.
1596 --
1597 -- In Arguments:
1598 -- p_competence_id
1599 -- p_level_number
1600 -- p_object_version_number
1601 -- p_effective_date
1602 --
1603 -- Post Success:
1604 -- Process continues if :
1605 -- All the in parameters are valid.
1606 --
1607 -- Post Failure:
1608 -- Error raised.
1609 --
1610 -- Access Status
1611 -- Internal Table Handler Use Only.
1612 --
1613 --
1614 procedure chk_level_number
1615 (p_competence_id in per_competences.competence_id%TYPE
1616 ,p_level_number in per_competences.level_number%TYPE
1617 ,p_object_version_number in per_competences.object_version_number%TYPE
1618 ,p_effective_date in date
1619 )
1620 is
1621 --
1622 l_proc varchar2(72) := g_package||'chk_level_number';
1623 l_api_updating boolean;
1624 --
1625 begin
1626 hr_utility.set_location('Entering:'|| l_proc, 10);
1627 --
1628 -- Only proceed with validation if :
1629 -- a) The current g_old_rec is current and
1630 -- b) The value for level has changed
1631 --
1632 l_api_updating := per_cpn_shd.api_updating
1633 (p_competence_id => p_competence_id
1634 ,p_object_version_number => p_object_version_number);
1635 --
1636 if p_level_number is not null then
1637 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.level_number,
1638 hr_api.g_number)
1639 <> nvl(p_level_number,hr_api.g_number)
1640 ) or
1641 (NOT l_api_updating)
1642 ) then
1643 --
1644 hr_utility.set_location(l_proc, 20);
1645 --
1646 -- Check that the category exists in HR_LOOKUPS
1647 --
1648 IF hr_api.not_exists_in_hr_lookups
1649 (p_effective_date => p_effective_date
1650 ,p_lookup_type => 'PER_QUAL_FWK_LEVEL'
1651 ,p_lookup_code => p_level_number) THEN
1652 --
1653 hr_utility.set_location(l_proc, 30);
1654 --
1655 hr_utility.set_message(800, 'HR_449091_QUA_FWK_LEVEL_LKP');
1656 hr_utility.raise_error;
1657 --
1658 END IF;
1659 end if;
1660 end if;
1661 --
1662 hr_utility.set_location('Leaving: '|| l_proc, 40);
1663 --
1664 end chk_level_number;
1665 --
1666 --
1667 -------------------------------------------------------------------------------
1671 -- Description:
1668 ----------------------------------< chk_field >--------------------------------
1669 -------------------------------------------------------------------------------
1670 --
1672 -- This procedure checks that a field exists in HR_LOOKUPS
1673 -- for the lookup type 'PER_QUAL_FWK_FIELD'.
1674 --
1675 -- Pre_conditions:
1676 -- None.
1677 --
1678 -- In Arguments:
1679 -- p_competence_id
1680 -- p_field
1681 -- p_object_version_number
1682 -- p_effective_date
1683 --
1684 -- Post Success:
1685 -- Process continues if :
1686 -- All the in parameters are valid.
1687 --
1688 -- Post Failure:
1689 -- Error raised.
1690 --
1691 -- Access Status
1692 -- Internal Table Handler Use Only.
1693 --
1694 --
1695 procedure chk_field
1696 (p_competence_id in per_competences.competence_id%TYPE
1697 ,p_field in per_competences.field%TYPE
1698 ,p_object_version_number in per_competences.object_version_number%TYPE
1699 ,p_effective_date in date
1700 )
1701 is
1702 --
1703 l_proc varchar2(72) := g_package||'chk_field';
1704 l_api_updating boolean;
1705 --
1706 begin
1707 hr_utility.set_location('Entering:'|| l_proc, 10);
1708 --
1709 -- Only proceed with validation if :
1710 -- a) The current g_old_rec is current and
1711 -- b) The value for field has changed
1712 --
1713 l_api_updating := per_cpn_shd.api_updating
1714 (p_competence_id => p_competence_id
1715 ,p_object_version_number => p_object_version_number);
1716 --
1717 if p_field is not null then
1718 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.field,
1719 hr_api.g_varchar2)
1720 <> nvl(p_field,hr_api.g_varchar2)
1721 ) or
1722 (NOT l_api_updating)
1723 ) then
1724 --
1725 hr_utility.set_location(l_proc, 20);
1726 --
1727 -- Check that the category exists in HR_LOOKUPS
1728 --
1729 IF hr_api.not_exists_in_hr_lookups
1730 (p_effective_date => p_effective_date
1731 ,p_lookup_type => 'PER_QUAL_FWK_FIELD'
1732 ,p_lookup_code => p_field) THEN
1733 --
1734 hr_utility.set_location(l_proc, 30);
1735 --
1736 hr_utility.set_message(800, 'HR_449093_QUA_FWK_FIELD_LKP');
1737 hr_utility.raise_error;
1738 --
1739 END IF;
1740 end if;
1741 end if;
1742 --
1743 hr_utility.set_location('Leaving: '|| l_proc, 40);
1744 --
1745 end chk_field;
1746 --
1747 --
1748 -------------------------------------------------------------------------------
1749 ----------------------------< chk_sub_field >----------------------------------
1750 -------------------------------------------------------------------------------
1751 --
1752 -- Description:
1753 -- This procedure checks that a sub_field exists in HR_LOOKUPS
1754 -- for the lookup type 'PER_QUAL_FWK_SUB_FIELD'.
1755 --
1756 -- Pre_conditions:
1757 -- None.
1758 --
1759 -- In Arguments:
1760 -- p_competence_id
1761 -- p_sub_field
1762 -- p_object_version_number
1763 -- p_effective_date
1764 --
1765 -- Post Success:
1766 -- Process continues if :
1767 -- All the in parameters are valid.
1768 --
1769 -- Post Failure:
1770 -- Error raised.
1771 --
1772 -- Access Status
1773 -- Internal Table Handler Use Only.
1774 --
1775 --
1776 procedure chk_sub_field
1777 (p_competence_id in per_competences.competence_id%TYPE
1778 ,p_sub_field in per_competences.sub_field%TYPE
1779 ,p_object_version_number in per_competences.object_version_number%TYPE
1780 ,p_effective_date in date
1781 )
1782 is
1783 --
1784 l_proc varchar2(72) := g_package||'chk_sub_field';
1785 l_api_updating boolean;
1786 --
1787 begin
1788 hr_utility.set_location('Entering:'|| l_proc, 10);
1789 --
1790 -- Only proceed with validation if :
1791 -- a) The current g_old_rec is current and
1792 -- b) The value for sub field has changed
1793 --
1794 l_api_updating := per_cpn_shd.api_updating
1795 (p_competence_id => p_competence_id
1796 ,p_object_version_number => p_object_version_number);
1797 --
1798 if p_sub_field is not null then
1799 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.sub_field,
1800 hr_api.g_varchar2)
1801 <> nvl(p_sub_field,hr_api.g_varchar2)
1802 ) or
1803 (NOT l_api_updating)
1804 ) then
1805 --
1806 hr_utility.set_location(l_proc, 20);
1807 --
1808 -- Check that the category exists in HR_LOOKUPS
1809 --
1810 IF hr_api.not_exists_in_hr_lookups
1811 (p_effective_date => p_effective_date
1812 ,p_lookup_type => 'PER_QUAL_FWK_SUB_FIELD'
1813 ,p_lookup_code => p_sub_field) THEN
1814 --
1815 hr_utility.set_location(l_proc, 30);
1816 --
1820 END IF;
1817 hr_utility.set_message(800, 'HR_449094_QUA_FWK_SUB_FLD_LKP');
1818 hr_utility.raise_error;
1819 --
1821 end if;
1822 end if;
1823 --
1824 hr_utility.set_location('Leaving: '|| l_proc, 40);
1825 --
1826 end chk_sub_field;
1827 --
1828 --
1829 -------------------------------------------------------------------------------
1830 -------------------------------< chk_provider >--------------------------------
1831 -------------------------------------------------------------------------------
1832 --
1833 -- Description:
1834 -- This procedure checks that a provider exists in HR_LOOKUPS
1835 -- for the lookup type 'PER_QUAL_FWK_PROVIDER'.
1836 --
1837 -- Pre_conditions:
1838 -- None.
1839 --
1840 -- In Arguments:
1841 -- p_competence_id
1842 -- p_provider
1843 -- p_object_version_number
1844 -- p_effective_date
1845 --
1846 -- Post Success:
1847 -- Process continues if :
1848 -- All the in parameters are valid.
1849 --
1850 -- Post Failure:
1851 -- Error raised.
1852 --
1853 -- Access Status
1854 -- Internal Table Handler Use Only.
1855 --
1856 --
1857 procedure chk_provider
1858 (p_competence_id in per_competences.competence_id%TYPE
1859 ,p_provider in per_competences.provider%TYPE
1860 ,p_object_version_number in per_competences.object_version_number%TYPE
1861 ,p_effective_date in date
1862 )
1863 is
1864 --
1865 l_proc varchar2(72) := g_package||'chk_provider';
1866 l_api_updating boolean;
1867 --
1868 begin
1869 hr_utility.set_location('Entering:'|| l_proc, 10);
1870 --
1871 -- Only proceed with validation if :
1872 -- a) The current g_old_rec is current and
1873 -- b) The value for provider has changed
1874 --
1875 l_api_updating := per_cpn_shd.api_updating
1876 (p_competence_id => p_competence_id
1877 ,p_object_version_number => p_object_version_number);
1878 --
1879 if p_provider is not null then
1880 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.provider,
1881 hr_api.g_varchar2)
1882 <> nvl(p_provider,hr_api.g_varchar2)
1883 ) or
1884 (NOT l_api_updating)
1885 ) then
1886 --
1887 hr_utility.set_location(l_proc, 20);
1888 --
1889 -- Check that the category exists in HR_LOOKUPS
1890 --
1891 IF hr_api.not_exists_in_hr_lookups
1892 (p_effective_date => p_effective_date
1893 ,p_lookup_type => 'PER_QUAL_FWK_PROVIDER'
1894 ,p_lookup_code => p_provider) THEN
1895 --
1896 hr_utility.set_location(l_proc, 30);
1897 --
1898 hr_utility.set_message(800, 'HR_449095_QUA_FWK_PROVIDER_LKP');
1899 hr_utility.raise_error;
1900 --
1901 END IF;
1902 end if;
1903 end if;
1904 --
1905 hr_utility.set_location('Leaving: '|| l_proc, 40);
1906 --
1907 end chk_provider;
1908 --
1909 --
1910 -------------------------------------------------------------------------------
1911 ------------------------< chk_qa_organization >--------------------------------
1912 -------------------------------------------------------------------------------
1913 --
1914 -- Description:
1915 -- This procedure checks that a qa_organization exists in HR_LOOKUPS
1916 -- for the lookup type 'PER_QUAL_FWK_QA_ORG'.
1917 --
1918 -- Pre_conditions:
1919 -- None.
1920 --
1921 -- In Arguments:
1922 -- p_competence_id
1923 -- p_qa_organization
1924 -- p_object_version_number
1925 -- p_effective_date
1926 --
1927 -- Post Success:
1928 -- Process continues if :
1929 -- All the in parameters are valid.
1930 --
1931 -- Post Failure:
1932 -- Error raised.
1933 --
1934 -- Access Status
1935 -- Internal Table Handler Use Only.
1936 --
1937 --
1938 procedure chk_qa_organization
1939 (p_competence_id in per_competences.competence_id%TYPE
1940 ,p_qa_organization in per_competences.qa_organization%TYPE
1941 ,p_object_version_number in per_competences.object_version_number%TYPE
1942 ,p_effective_date in date
1943 )
1944 is
1945 --
1946 l_proc varchar2(72) := g_package||'chk_qa_organization';
1947 l_api_updating boolean;
1948 --
1949 begin
1950 hr_utility.set_location('Entering:'|| l_proc, 10);
1951 --
1952 -- Only proceed with validation if :
1953 -- a) The current g_old_rec is current and
1954 -- b) The value for qa organization has changed
1955 --
1956 l_api_updating := per_cpn_shd.api_updating
1957 (p_competence_id => p_competence_id
1958 ,p_object_version_number => p_object_version_number);
1959 --
1960 if p_qa_organization is not null then
1961 if ( (l_api_updating and nvl(per_cpn_shd.g_old_rec.qa_organization,
1962 hr_api.g_varchar2)
1963 <> nvl(p_qa_organization,hr_api.g_varchar2)
1964 ) or
1965 (NOT l_api_updating)
1966 ) then
1967 --
1971 --
1968 hr_utility.set_location(l_proc, 20);
1969 --
1970 -- Check that the category exists in HR_LOOKUPS
1972 IF hr_api.not_exists_in_hr_lookups
1973 (p_effective_date => p_effective_date
1974 ,p_lookup_type => 'PER_QUAL_FWK_QA_ORG'
1975 ,p_lookup_code => p_qa_organization) THEN
1976 --
1977 hr_utility.set_location(l_proc, 30);
1978 --
1979 hr_utility.set_message(800, 'HR_449096_QUA_FWK_QA_ORG_LKP');
1980 hr_utility.raise_error;
1981 --
1982 END IF;
1983 end if;
1984 end if;
1985 --
1986 hr_utility.set_location('Leaving: '|| l_proc, 40);
1987 --
1988 end chk_qa_organization;
1989 --
1990 -- -----------------------------------------------------------------------
1991 -- |------------------------------< chk_df >-----------------------------|
1992 -- -----------------------------------------------------------------------
1993 --
1994 -- Description:
1995 -- Validates the all Descriptive Flexfield values.
1996 --
1997 -- Pre-conditions:
1998 -- All other columns have been validated. Must be called as the
1999 -- last step from insert_validate and update_validate.
2000 --
2001 -- In Arguments:
2002 -- p_rec
2003 --
2004 -- Post Success:
2005 -- If the Descriptive Flexfield structure column and data values are
2006 -- all valid this procedure will end normally and processing will
2007 -- continue.
2008 --
2009 -- Post Failure:
2010 -- If the Descriptive Flexfield structure column value or any of
2011 -- the data values are invalid then an application error is raised as
2012 -- a PL/SQL exception.
2013 --
2014 -- Access Status:
2015 -- Internal Row Handler Use Only.
2016 --
2017 procedure chk_df
2018 (p_rec in per_cpn_shd.g_rec_type) is
2019 --
2020 l_proc varchar2(72) := g_package||'chk_df';
2021 --
2022 begin
2023 hr_utility.set_location('Entering:'||l_proc, 10);
2024 --
2025 if (((p_rec.competence_id is not null) and (
2026 nvl(per_cpn_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
2027 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
2028 nvl(per_cpn_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
2029 nvl(p_rec.attribute1, hr_api.g_varchar2) or
2030 nvl(per_cpn_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
2031 nvl(p_rec.attribute2, hr_api.g_varchar2) or
2032 nvl(per_cpn_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
2033 nvl(p_rec.attribute3, hr_api.g_varchar2) or
2034 nvl(per_cpn_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
2035 nvl(p_rec.attribute4, hr_api.g_varchar2) or
2036 nvl(per_cpn_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
2037 nvl(p_rec.attribute5, hr_api.g_varchar2) or
2038 nvl(per_cpn_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
2039 nvl(p_rec.attribute6, hr_api.g_varchar2) or
2040 nvl(per_cpn_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
2041 nvl(p_rec.attribute7, hr_api.g_varchar2) or
2042 nvl(per_cpn_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
2043 nvl(p_rec.attribute8, hr_api.g_varchar2) or
2044 nvl(per_cpn_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
2045 nvl(p_rec.attribute9, hr_api.g_varchar2) or
2046 nvl(per_cpn_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
2047 nvl(p_rec.attribute10, hr_api.g_varchar2) or
2048 nvl(per_cpn_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
2049 nvl(p_rec.attribute11, hr_api.g_varchar2) or
2050 nvl(per_cpn_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
2051 nvl(p_rec.attribute12, hr_api.g_varchar2) or
2052 nvl(per_cpn_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
2053 nvl(p_rec.attribute13, hr_api.g_varchar2) or
2054 nvl(per_cpn_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
2055 nvl(p_rec.attribute14, hr_api.g_varchar2) or
2056 nvl(per_cpn_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
2057 nvl(p_rec.attribute15, hr_api.g_varchar2) or
2058 nvl(per_cpn_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
2059 nvl(p_rec.attribute16, hr_api.g_varchar2) or
2060 nvl(per_cpn_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
2061 nvl(p_rec.attribute17, hr_api.g_varchar2) or
2062 nvl(per_cpn_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
2063 nvl(p_rec.attribute18, hr_api.g_varchar2) or
2064 nvl(per_cpn_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
2065 nvl(p_rec.attribute19, hr_api.g_varchar2) or
2066 nvl(per_cpn_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
2067 nvl(p_rec.attribute20, hr_api.g_varchar2)))
2068 or
2069 p_rec.competence_id is null)
2070 and hr_competences_api.g_ignore_df <> 'Y' then -- BUG3621261
2071 --
2072 -- Only execute the validation if absolutely necessary:
2073 -- a) During update, the structure column value or any
2074 -- of the attribute values have actually changed.
2075 -- b) During insert.
2076 --
2077 hr_dflex_utility.ins_or_upd_descflex_attribs
2078 (p_appl_short_name => 'PER'
2079 ,p_descflex_name => 'PER_COMPETENCES'
2080 ,p_attribute_category => p_rec.attribute_category
2081 ,p_attribute1_name => 'ATTRIBUTE1'
2082 ,p_attribute1_value => p_rec.attribute1
2083 ,p_attribute2_name => 'ATTRIBUTE2'
2084 ,p_attribute2_value => p_rec.attribute2
2085 ,p_attribute3_name => 'ATTRIBUTE3'
2089 ,p_attribute5_name => 'ATTRIBUTE5'
2086 ,p_attribute3_value => p_rec.attribute3
2087 ,p_attribute4_name => 'ATTRIBUTE4'
2088 ,p_attribute4_value => p_rec.attribute4
2090 ,p_attribute5_value => p_rec.attribute5
2091 ,p_attribute6_name => 'ATTRIBUTE6'
2092 ,p_attribute6_value => p_rec.attribute6
2093 ,p_attribute7_name => 'ATTRIBUTE7'
2094 ,p_attribute7_value => p_rec.attribute7
2095 ,p_attribute8_name => 'ATTRIBUTE8'
2096 ,p_attribute8_value => p_rec.attribute8
2097 ,p_attribute9_name => 'ATTRIBUTE9'
2098 ,p_attribute9_value => p_rec.attribute9
2099 ,p_attribute10_name => 'ATTRIBUTE10'
2100 ,p_attribute10_value => p_rec.attribute10
2101 ,p_attribute11_name => 'ATTRIBUTE11'
2102 ,p_attribute11_value => p_rec.attribute11
2103 ,p_attribute12_name => 'ATTRIBUTE12'
2104 ,p_attribute12_value => p_rec.attribute12
2105 ,p_attribute13_name => 'ATTRIBUTE13'
2106 ,p_attribute13_value => p_rec.attribute13
2107 ,p_attribute14_name => 'ATTRIBUTE14'
2108 ,p_attribute14_value => p_rec.attribute14
2109 ,p_attribute15_name => 'ATTRIBUTE15'
2110 ,p_attribute15_value => p_rec.attribute15
2111 ,p_attribute16_name => 'ATTRIBUTE16'
2112 ,p_attribute16_value => p_rec.attribute16
2113 ,p_attribute17_name => 'ATTRIBUTE17'
2114 ,p_attribute17_value => p_rec.attribute17
2115 ,p_attribute18_name => 'ATTRIBUTE18'
2116 ,p_attribute18_value => p_rec.attribute18
2117 ,p_attribute19_name => 'ATTRIBUTE19'
2118 ,p_attribute19_value => p_rec.attribute19
2119 ,p_attribute20_name => 'ATTRIBUTE20'
2120 ,p_attribute20_value => p_rec.attribute20
2121 );
2122 end if;
2123 --
2124 hr_utility.set_location(' Leaving:'||l_proc, 20);
2125
2126 end chk_df;
2127 --
2128 -- ----------------------------------------------------------------------------
2129 -- |-----------------------------< chk_ddf >----------------------------------|
2130 -- ----------------------------------------------------------------------------
2131 --
2132 -- Description:
2133 -- Validates all the Developer Descriptive Flexfield values.
2134 --
2135 -- Prerequisites:
2136 -- All other columns have been validated. Must be called as the
2137 -- last step from insert_validate and update_validate.
2138 --
2139 -- In Arguments:
2140 -- p_rec
2141 --
2142 -- Post Success:
2143 -- If the Developer Descriptive Flexfield structure column and data values
2144 -- are all valid this procedure will end normally and processing will
2145 -- continue.
2146 --
2147 -- Post Failure:
2148 -- If the Developer Descriptive Flexfield structure column value or any of
2149 -- the data values are invalid then an application error is raised as
2150 -- a PL/SQL exception.
2151 --
2152 -- Access Status:
2153 -- Internal Row Handler Use Only.
2154 --
2155 -- ----------------------------------------------------------------------------
2156 procedure chk_ddf
2157 (p_rec in per_cpn_shd.g_rec_type
2158 ) is
2159 --
2160 l_proc varchar2(72) := g_package || 'chk_ddf';
2161 --
2162 begin
2163 hr_utility.set_location('Entering:'||l_proc,10);
2164 --
2165 if (((p_rec.competence_id is not null) and (
2166 nvl(per_cpn_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
2167 nvl(p_rec.information_category, hr_api.g_varchar2) or
2168 nvl(per_cpn_shd.g_old_rec.information1, hr_api.g_varchar2) <>
2169 nvl(p_rec.information1, hr_api.g_varchar2) or
2170 nvl(per_cpn_shd.g_old_rec.information2, hr_api.g_varchar2) <>
2171 nvl(p_rec.information2, hr_api.g_varchar2) or
2172 nvl(per_cpn_shd.g_old_rec.information3, hr_api.g_varchar2) <>
2173 nvl(p_rec.information3, hr_api.g_varchar2) or
2174 nvl(per_cpn_shd.g_old_rec.information4, hr_api.g_varchar2) <>
2175 nvl(p_rec.information4, hr_api.g_varchar2) or
2176 nvl(per_cpn_shd.g_old_rec.information5, hr_api.g_varchar2) <>
2177 nvl(p_rec.information5, hr_api.g_varchar2) or
2178 nvl(per_cpn_shd.g_old_rec.information6, hr_api.g_varchar2) <>
2179 nvl(p_rec.information6, hr_api.g_varchar2) or
2180 nvl(per_cpn_shd.g_old_rec.information7, hr_api.g_varchar2) <>
2181 nvl(p_rec.information7, hr_api.g_varchar2) or
2182 nvl(per_cpn_shd.g_old_rec.information8, hr_api.g_varchar2) <>
2183 nvl(p_rec.information8, hr_api.g_varchar2) or
2184 nvl(per_cpn_shd.g_old_rec.information9, hr_api.g_varchar2) <>
2185 nvl(p_rec.information9, hr_api.g_varchar2) or
2186 nvl(per_cpn_shd.g_old_rec.information10, hr_api.g_varchar2) <>
2187 nvl(p_rec.information10, hr_api.g_varchar2) or
2188 nvl(per_cpn_shd.g_old_rec.information11, hr_api.g_varchar2) <>
2189 nvl(p_rec.information11, hr_api.g_varchar2) or
2190 nvl(per_cpn_shd.g_old_rec.information13, hr_api.g_varchar2) <>
2191 nvl(p_rec.information13, hr_api.g_varchar2) or
2192 nvl(per_cpn_shd.g_old_rec.information14, hr_api.g_varchar2) <>
2193 nvl(p_rec.information14, hr_api.g_varchar2) or
2194 nvl(per_cpn_shd.g_old_rec.information15, hr_api.g_varchar2) <>
2195 nvl(p_rec.information15, hr_api.g_varchar2) or
2196 nvl(per_cpn_shd.g_old_rec.information16, hr_api.g_varchar2) <>
2197 nvl(p_rec.information16, hr_api.g_varchar2) or
2198 nvl(per_cpn_shd.g_old_rec.information17, hr_api.g_varchar2) <>
2202 nvl(per_cpn_shd.g_old_rec.information19, hr_api.g_varchar2) <>
2199 nvl(p_rec.information17, hr_api.g_varchar2) or
2200 nvl(per_cpn_shd.g_old_rec.information18, hr_api.g_varchar2) <>
2201 nvl(p_rec.information18, hr_api.g_varchar2) or
2203 nvl(p_rec.information19, hr_api.g_varchar2) or
2204 nvl(per_cpn_shd.g_old_rec.information20, hr_api.g_varchar2) <>
2205 nvl(p_rec.information20, hr_api.g_varchar2) ))
2206 or (p_rec.competence_id is null))
2207 and hr_competences_api.g_ignore_df <> 'Y' then -- BUG3621261
2208 --
2209 -- Only execute the validation if absolutely necessary:
2210 -- a) During update, the structure column value or any
2211 -- of the attribute values have actually changed.
2212 -- b) During insert.
2213 --
2214 hr_dflex_utility.ins_or_upd_descflex_attribs
2215 (p_appl_short_name => 'PER'
2216 ,p_descflex_name => 'Competence Developer DF'
2217 ,p_attribute_category => p_rec.INFORMATION_CATEGORY
2218 ,p_attribute1_name => 'INFORMATION1'
2219 ,p_attribute1_value => p_rec.information1
2220 ,p_attribute2_name => 'INFORMATION2'
2221 ,p_attribute2_value => p_rec.information2
2222 ,p_attribute3_name => 'INFORMATION3'
2223 ,p_attribute3_value => p_rec.information3
2224 ,p_attribute4_name => 'INFORMATION4'
2225 ,p_attribute4_value => p_rec.information4
2226 ,p_attribute5_name => 'INFORMATION5'
2227 ,p_attribute5_value => p_rec.information5
2228 ,p_attribute6_name => 'INFORMATION6'
2229 ,p_attribute6_value => p_rec.information6
2230 ,p_attribute7_name => 'INFORMATION7'
2231 ,p_attribute7_value => p_rec.information7
2232 ,p_attribute8_name => 'INFORMATION8'
2233 ,p_attribute8_value => p_rec.information8
2234 ,p_attribute9_name => 'INFORMATION9'
2235 ,p_attribute9_value => p_rec.information9
2236 ,p_attribute10_name => 'INFORMATION10'
2237 ,p_attribute10_value => p_rec.information10
2238 ,p_attribute11_name => 'INFORMATION11'
2239 ,p_attribute11_value => p_rec.information11
2240 ,p_attribute12_name => 'INFORMATION13'
2241 ,p_attribute12_value => p_rec.information13
2242 ,p_attribute13_name => 'INFORMATION14'
2243 ,p_attribute13_value => p_rec.information14
2244 ,p_attribute14_name => 'INFORMATION15'
2245 ,p_attribute14_value => p_rec.information15
2246 ,p_attribute15_name => 'INFORMATION16'
2247 ,p_attribute15_value => p_rec.information16
2248 ,p_attribute16_name => 'INFORMATION17'
2249 ,p_attribute16_value => p_rec.information17
2250 ,p_attribute17_name => 'INFORMATION18'
2251 ,p_attribute17_value => p_rec.information18
2252 ,p_attribute18_name => 'INFORMATION19'
2253 ,p_attribute18_value => p_rec.information19
2254 ,p_attribute19_name => 'INFORMATION20'
2255 ,p_attribute19_value => p_rec.information20
2256 );
2257 end if;
2258 --
2259 hr_utility.set_location(' Leaving:'||l_proc,20);
2260 end chk_ddf;
2261 -- ---------------------------------------------------------------------------
2262 -- |---------------------------< insert_validate >----------------------------|
2263 -- ---------------------------------------------------------------------------
2264 --
2265 Procedure insert_validate(p_rec in per_cpn_shd.g_rec_type,
2266 p_effective_date in date) is
2267 --
2268 l_proc varchar2(72) := g_package||'insert_validate';
2269 --
2270 Begin
2271 hr_utility.set_location('Entering:'||l_proc, 5);
2272 --
2273 -- Call all supporting business operations
2274 --
2275 --
2276 -- Rule Check Business group is valid
2277 -- ngundura changes for pa requirements
2278 if p_rec.business_group_id is not null then
2279 hr_api.validate_bus_grp_id(p_rec.business_group_id);
2280 end if;
2281 -- ngundura end of changes.
2282 hr_utility.set_location(l_proc, 10);
2283 --
2284 -- Rule Check unique competence name
2285 --
2286 per_cpn_bus.chk_definition_id
2287 (p_competence_id => p_rec.competence_id
2288 ,p_business_group_id => p_rec.business_group_id
2289 ,p_competence_definition_id => p_rec.competence_definition_id
2290 ,p_object_version_number => p_rec.object_version_number
2291 );
2292 hr_utility.set_location(l_proc, 15);
2293 --
2294 -- Rule Check if rating scale exists and is in same business group as
2295 -- as that of competence
2296 --
2297 per_cpn_bus.chk_rat_scale_bus_grp_exist
2298 (p_competence_id => p_rec.competence_id
2299 ,p_object_version_number => p_rec.object_version_number
2300 ,p_rating_scale_id => p_rec.rating_scale_id
2301 ,p_business_group_id => p_rec.business_group_id);
2302 hr_utility.set_location(l_proc, 16);
2303 --
2304 -- Rule Check if rating scale is valid
2305 --
2306 per_cpn_bus.chk_rating_scale_type
2310 );
2307 (p_competence_id => p_rec.competence_id
2308 ,p_object_version_number => p_rec.object_version_number
2309 ,p_rating_scale_id => p_rec.rating_scale_id
2311 --
2312 --
2313 -- Rule Check Dates
2314 --
2315 per_cpn_bus.chk_competence_dates
2316 (p_date_from => p_rec.date_from
2317 ,p_date_to => p_rec.date_to );
2318 --
2319 -- Rule check Certification required Flag
2320 --
2321 per_cpn_bus.chk_certification_required
2322 (p_competence_id => p_rec.competence_id
2323 ,p_object_version_number => p_rec.object_version_number
2324 ,p_certification_required => p_rec.certification_required
2325 ,p_effective_date => p_effective_date
2326 );
2327 hr_utility.set_location(l_proc, 25);
2328 --
2329 -- Rule check Evaluation Method
2330 --
2331 per_cpn_bus.chk_evaluation_method
2332 (p_competence_id => p_rec.competence_id
2333 ,p_object_version_number => p_rec.object_version_number
2334 ,p_evaluation_method => p_rec.evaluation_method
2335 ,p_effective_date => p_effective_date
2336 );
2337 hr_utility.set_location(l_proc, 30);
2338 --
2339 -- Rule check Renewal period units
2340 --
2341 per_cpn_bus.chk_renewal_period_units
2342 (p_competence_id => p_rec.competence_id
2343 ,p_object_version_number => p_rec.object_version_number
2344 ,P_renewal_period_units => p_rec.renewal_period_units
2345 ,p_effective_date => p_effective_date
2346 );
2347 hr_utility.set_location(l_proc, 35);
2348 --
2349 -- Rule check dependency of period units and period frquency
2350 --
2351 per_cpn_bus.chk_renewable_unit_frequency
2352 (p_competence_id => p_rec.competence_id
2353 ,p_object_version_number => p_rec.object_version_number
2354 ,p_renewal_period_units => p_rec.renewal_period_units
2355 ,p_renewal_period_frequency => p_rec.renewal_period_frequency
2356 );
2357 -- added by ngundura as part of global competence changes
2358 hr_api.mandatory_arg_error (p_api_name => l_proc,
2359 p_argument => 'competence_definition_id',
2360 p_argument_value => p_rec.competence_definition_id );
2361
2362 hr_utility.set_location(l_proc, 40);
2363 --
2364 -- Rule check competence cluster
2365 --
2366 per_cpn_bus.chk_competence_cluster
2367 (p_competence_id => p_rec.competence_id
2368 ,p_competence_cluster => p_rec.competence_cluster
2369 ,p_object_version_number => p_rec.object_version_number
2370 ,p_effective_date => p_effective_date
2371 );
2372 hr_utility.set_location(l_proc, 50);
2373
2374 --
2375 -- Rule check unit_standard_id
2376 --
2377 per_cpn_bus.chk_unit_standard_id
2378 (p_competence_id => p_rec.competence_id
2379 ,p_unit_standard_id => p_rec.unit_standard_id
2380 ,p_business_group_id => p_rec.business_group_id
2381 ,p_object_version_number => p_rec.object_version_number
2382 ,p_effective_date => p_effective_date
2383 );
2384 hr_utility.set_location(l_proc, 60);
2385
2386 --
2387 -- Rule check credit type
2388 --
2389 per_cpn_bus.chk_credit_type
2390 (p_competence_id => p_rec.competence_id
2391 ,p_credit_type => p_rec.credit_type
2392 ,p_object_version_number => p_rec.object_version_number
2393 ,p_effective_date => p_effective_date
2394 );
2395 hr_utility.set_location(l_proc, 70);
2396 --
2397 -- Rule check level type
2398 --
2399 per_cpn_bus.chk_level_type
2400 (p_competence_id => p_rec.competence_id
2401 ,p_level_type => p_rec.level_type
2402 ,p_object_version_number => p_rec.object_version_number
2403 ,p_effective_date => p_effective_date
2404 );
2405 hr_utility.set_location(l_proc, 80);
2406 --
2407 -- Rule check level
2408 --
2409 per_cpn_bus.chk_level_number
2410 (p_competence_id => p_rec.competence_id
2411 ,p_level_number => p_rec.level_number
2412 ,p_object_version_number => p_rec.object_version_number
2413 ,p_effective_date => p_effective_date
2414 );
2415 hr_utility.set_location(l_proc, 90);
2416
2417 --
2418 -- Rule check field
2419 --
2420 per_cpn_bus.chk_field
2421 (p_competence_id => p_rec.competence_id
2422 ,p_field => p_rec.field
2423 ,p_object_version_number => p_rec.object_version_number
2424 ,p_effective_date => p_effective_date
2425 );
2426 hr_utility.set_location(l_proc, 100);
2427
2428 --
2429 -- Rule check sub field
2430 --
2431 per_cpn_bus.chk_sub_field
2432 (p_competence_id => p_rec.competence_id
2433 ,p_sub_field => p_rec.sub_field
2434 ,p_object_version_number => p_rec.object_version_number
2435 ,p_effective_date => p_effective_date
2436 );
2437 hr_utility.set_location(l_proc, 110);
2438
2442 per_cpn_bus.chk_provider
2439 --
2440 -- Rule check provider
2441 --
2443 (p_competence_id => p_rec.competence_id
2444 ,p_provider => p_rec.provider
2445 ,p_object_version_number => p_rec.object_version_number
2446 ,p_effective_date => p_effective_date
2447 );
2448 hr_utility.set_location(l_proc, 120);
2449
2450 --
2451 -- Rule check qa organization
2452 --
2453 per_cpn_bus.chk_qa_organization
2454 (p_competence_id => p_rec.competence_id
2455 ,p_qa_organization => p_rec.qa_organization
2456 ,p_object_version_number => p_rec.object_version_number
2457 ,p_effective_date => p_effective_date
2458 );
2459 hr_utility.set_location(l_proc, 130);
2460
2461 --
2462 --
2463 -- Call descriptive flexfield validation routines
2464 --
2465 per_cpn_bus.chk_ddf(p_rec => p_rec); -- BUG3356369
2466 --
2467 hr_utility.set_location(l_proc, 140);
2468 --
2469 per_cpn_bus.chk_df(p_rec => p_rec);
2470 --
2471 --
2472 hr_utility.set_location(' Leaving:'||l_proc, 150);
2473 End insert_validate;
2474 --
2475 -- ----------------------------------------------------------------------------
2476 -- |---------------------------< update_validate >----------------------------|
2477 -- ----------------------------------------------------------------------------
2478 Procedure update_validate(p_rec in per_cpn_shd.g_rec_type
2479 ,p_effective_date in date ) is
2480 --
2481 l_proc varchar2(72) := g_package||'update_validate';
2482 --
2483 Begin
2484 hr_utility.set_location('Entering:'||l_proc, 5);
2485 --
2486 -- Call all supporting business operations
2487 --
2488 --
2489 -- Rule Check Business group id cannot be updated
2490 --
2491 chk_non_updateable_args(p_rec => p_rec);
2492 --
2493 -- Rule Check unique competence name
2494 --
2495 per_cpn_bus.chk_definition_id
2496 (p_competence_id => p_rec.competence_id
2497 ,p_business_group_id => p_rec.business_group_id
2498 ,p_competence_definition_id => p_rec.competence_definition_id
2499 ,p_object_version_number => p_rec.object_version_number
2500 );
2501 hr_utility.set_location(l_proc, 15);
2502 --
2503 -- Rule Check Dates
2504 --
2505 per_cpn_bus.chk_competence_dates
2506 (p_date_from => p_rec.date_from
2507 ,p_date_to => p_rec.date_to
2508 ,p_competence_id => p_rec.competence_id
2509 ,p_called_from => 'UPDATE'
2510 );
2511 -- Rule check Certification required Flag
2512 --
2513 per_cpn_bus.chk_certification_required
2514 (p_competence_id => p_rec.competence_id
2515 ,p_object_version_number => p_rec.object_version_number
2516 ,p_certification_required => p_rec.certification_required
2517 ,p_effective_date => p_effective_date
2518 );
2519 hr_utility.set_location(l_proc, 25);
2520 --
2521 -- Rule check Evaluation Method
2522 --
2523 per_cpn_bus.chk_evaluation_method
2524 (p_competence_id => p_rec.competence_id
2525 ,p_object_version_number => p_rec.object_version_number
2526 ,p_evaluation_method => p_rec.evaluation_method
2527 ,p_effective_date => p_effective_date
2528 );
2529 hr_utility.set_location(l_proc, 30);
2530 --
2531 -- Rule check Renewal period units
2532 --
2533 per_cpn_bus.chk_renewal_period_units
2534 (p_competence_id => p_rec.competence_id
2535 ,p_object_version_number => p_rec.object_version_number
2536 ,p_renewal_period_units => p_rec.renewal_period_units
2537 ,p_effective_date => p_effective_date
2538 );
2539 hr_utility.set_location(l_proc, 35);
2540 --
2541 -- Rule check dependency of period units and period frquency
2542 --
2543 per_cpn_bus.chk_renewable_unit_frequency
2544 (p_competence_id => p_rec.competence_id
2545 ,p_object_version_number => p_rec.object_version_number
2546 ,p_renewal_period_units => p_rec.renewal_period_units
2547 ,p_renewal_period_frequency => p_rec.renewal_period_frequency
2548 );
2549 hr_utility.set_location(l_proc, 36);
2550 --
2551 --
2552 -- Rule Check if rating scale exists and is in same business group as
2553 -- as that of competence
2554 --
2555 per_cpn_bus.chk_rat_scale_bus_grp_exist
2556 (p_competence_id => p_rec.competence_id
2557 ,p_object_version_number => p_rec.object_version_number
2558 ,p_rating_scale_id => p_rec.rating_scale_id
2559 ,p_business_group_id => p_rec.business_group_id);
2560 hr_utility.set_location(l_proc, 37);
2561 --
2562 --
2563 -- Rule Check if rating scale is valid
2564 --
2565 per_cpn_bus.chk_rating_scale_type
2566 (p_competence_id => p_rec.competence_id
2567 ,p_object_version_number => p_rec.object_version_number
2568 ,p_rating_scale_id => p_rec.rating_scale_id
2569 );
2570 --
2571 hr_utility.set_location(l_proc, 40);
2572 --
2573 -- Rule check if competence has any Proficiency levels. If it has
2577 ,p_object_version_number => p_rec.object_version_number
2574 -- then do not allow any rating scales to be assinged to this competence.
2575 per_cpn_bus.chk_competence_has_prof
2576 (p_competence_id => p_rec.competence_id
2578 ,p_rating_scale_id => p_rec.rating_scale_id
2579 );
2580 --
2581 hr_utility.set_location(l_proc, 45);
2582 --
2583 -- Rule check competence rating scale cannot be updated if the rating
2584 -- level for that rating scale is used in competence element
2585 per_cpn_bus.chk_competence_rating_update
2586 (p_competence_id => p_rec.competence_id
2587 ,p_object_version_number => p_rec.object_version_number
2588 ,p_rating_scale_id => p_rec.rating_scale_id
2589 );
2590 --
2591 hr_utility.set_location(l_proc, 50);
2592 --
2593 hr_api.mandatory_arg_error (p_api_name => l_proc,
2594 p_argument => 'competence_definition_id',
2595 p_argument_value => p_rec.competence_definition_id );
2596 hr_utility.set_location(l_proc, 60);
2597 --
2598 -- Rule check competence cluster
2599 --
2600 per_cpn_bus.chk_competence_cluster
2601 (p_competence_id => p_rec.competence_id
2602 ,p_competence_cluster => p_rec.competence_cluster
2603 ,p_object_version_number => p_rec.object_version_number
2604 ,p_effective_date => p_effective_date
2605 );
2606 hr_utility.set_location(l_proc, 70);
2607
2608 --
2609 -- Rule check unit_standard_id
2610 --
2611 per_cpn_bus.chk_unit_standard_id
2615 ,p_object_version_number => p_rec.object_version_number
2612 (p_competence_id => p_rec.competence_id
2613 ,p_unit_standard_id => p_rec.unit_standard_id
2614 ,p_business_group_id => p_rec.business_group_id
2616 ,p_effective_date => p_effective_date
2617 );
2618 hr_utility.set_location(l_proc, 80);
2619
2620 --
2621 -- Rule check credit type
2622 --
2623 per_cpn_bus.chk_credit_type
2624 (p_competence_id => p_rec.competence_id
2625 ,p_credit_type => p_rec.credit_type
2626 ,p_object_version_number => p_rec.object_version_number
2627 ,p_effective_date => p_effective_date
2628 );
2629 hr_utility.set_location(l_proc, 90);
2630
2631 --
2632 -- Rule check level type
2633 --
2634 per_cpn_bus.chk_level_type
2635 (p_competence_id => p_rec.competence_id
2636 ,p_level_type => p_rec.level_type
2637 ,p_object_version_number => p_rec.object_version_number
2638 ,p_effective_date => p_effective_date
2639 );
2640 hr_utility.set_location(l_proc, 100);
2641
2642 --
2643 -- Rule check level
2644 --
2645 per_cpn_bus.chk_level_number
2646 (p_competence_id => p_rec.competence_id
2647 ,p_level_number => p_rec.level_number
2648 ,p_object_version_number => p_rec.object_version_number
2649 ,p_effective_date => p_effective_date
2650 );
2651 hr_utility.set_location(l_proc, 110);
2652
2653 --
2654 -- Rule check field
2655 --
2656 per_cpn_bus.chk_field
2657 (p_competence_id => p_rec.competence_id
2658 ,p_field => p_rec.field
2659 ,p_object_version_number => p_rec.object_version_number
2660 ,p_effective_date => p_effective_date
2661 );
2662 hr_utility.set_location(l_proc, 120);
2663
2664 --
2665 -- Rule check sub field
2666 --
2667 per_cpn_bus.chk_sub_field
2668 (p_competence_id => p_rec.competence_id
2669 ,p_sub_field => p_rec.sub_field
2670 ,p_object_version_number => p_rec.object_version_number
2671 ,p_effective_date => p_effective_date
2672 );
2673 hr_utility.set_location(l_proc, 130);
2674
2675 --
2676 -- Rule check provider
2677 --
2678 per_cpn_bus.chk_provider
2679 (p_competence_id => p_rec.competence_id
2680 ,p_provider => p_rec.provider
2681 ,p_object_version_number => p_rec.object_version_number
2682 ,p_effective_date => p_effective_date
2683 );
2684
2685 hr_utility.set_location(l_proc, 140);
2686 --
2687 -- Rule check qa organization
2688 --
2689 per_cpn_bus.chk_qa_organization
2690 (p_competence_id => p_rec.competence_id
2691 ,p_qa_organization => p_rec.qa_organization
2692 ,p_object_version_number => p_rec.object_version_number
2693 ,p_effective_date => p_effective_date
2694 );
2695 hr_utility.set_location(l_proc, 150);
2696 --
2697 --
2698 -- Call descriptive flexfield validation routines
2699 --
2700 per_cpn_bus.chk_ddf(p_rec => p_rec); -- BUG3356369
2701 --
2702 hr_utility.set_location(l_proc, 160);
2703 --
2704 per_cpn_bus.chk_df(p_rec => p_rec);
2705 --
2706 --
2707 hr_utility.set_location(' Leaving:'||l_proc, 180);
2708 End update_validate;
2709 --
2710 -- ----------------------------------------------------------------------------
2711 -- |---------------------------< delete_validate >----------------------------|
2712 -- ----------------------------------------------------------------------------
2713 Procedure delete_validate(p_rec in per_cpn_shd.g_rec_type) is
2714 --
2715 l_proc varchar2(72) := g_package||'delete_validate';
2716 --
2717 Begin
2718 hr_utility.set_location('Entering:'||l_proc, 5);
2719 --
2720 -- Call all supporting business operations
2721 --
2722 -- Validate Delete of Competence
2723 --
2724 -- Business Rule mapping
2725 --
2726 per_cpn_bus.chk_competence_delete
2727 (p_competence_id => p_rec.competence_id
2728 ,p_object_version_number => p_rec.object_version_number
2729 );
2730 --
2731 hr_utility.set_location(' Leaving:'||l_proc, 10);
2732 End delete_validate;
2733 --
2734 -- ----------------------------------------------------------------------------
2735 -- |-----------------------< return_legislation_code >-------------------------|
2736 -- ----------------------------------------------------------------------------
2737 Function return_legislation_code
2738 ( p_competence_id in number
2739 ) return varchar2 is
2740 --
2741 -- Declare cursor
2742 --
2743 -- Bug #2536636 --Modified the cursor with outer join
2744 cursor csr_leg_code is
2745 select pbg.legislation_code, pcp.business_group_id
2746 from per_business_groups pbg,
2747 per_competences pcp
2748 where pcp.competence_id = p_competence_id
2749 and pbg.business_group_id(+) = pcp.business_group_id;
2750
2751 l_proc varchar2(72) := g_package||'return_legislation_code';
2752 l_legislation_code varchar2(150);
2753 -- Bug #2536636
2754 l_business_group_id per_business_groups.business_group_id%Type;
2755 --
2756 Begin
2757 hr_utility.set_location('Entering:'||l_proc, 5);
2758 --
2759 -- Ensure that all the mandatory parameters are not null
2760 --
2761 hr_api.mandatory_arg_error (p_api_name => l_proc,
2765 if nvl(g_competence_id, hr_api.g_number) = p_competence_id then
2762 p_argument => 'competence_id',
2763 p_argument_value => p_competence_id );
2764 --
2766 --
2767 -- The legislation code has already been found with a previous
2768 -- call to this function. Just return the value in the global
2769 -- variable.
2770 --
2771 l_legislation_code := g_legislation_code;
2772 hr_utility.set_location(l_proc, 10);
2773 else
2774 --
2775 -- The ID is different to the last call to this function
2776 -- or this is the first call to this function.
2777 --
2778 open csr_leg_code;
2779 fetch csr_leg_code into l_legislation_code, l_business_group_id; --Bug #2536636
2780 if csr_leg_code%notfound then
2781 close csr_leg_code;
2782 --
2783 -- The primary key is invalid therefore we must error out
2784 --
2785 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2786 hr_utility.raise_error;
2787 end if;
2788 --
2789 close csr_leg_code;
2790 -- Bug #2536636
2791 if l_business_group_id is not null then
2792 g_competence_id := p_competence_id;
2793 g_legislation_code := l_legislation_code;
2794 else
2795 return null;
2796 end if;
2797 --
2798 end if;
2799 return l_legislation_code;
2800 --
2801 hr_utility.set_location(' Leaving:'||l_proc, 10);
2802 --
2803 End return_legislation_code;
2804 --
2805 --
2806 end per_cpn_bus;
[Home] [Help]