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