1 Package Body per_rtl_bus as
2 /* $Header: pertlrhi.pkb 120.0 2005/05/31 19:57:25 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_rtl_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------<chk_non_updateable_args >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure chk_non_updateable_args(p_rec in per_rtl_shd.g_rec_type) is
14 --
15 l_proc varchar2(72) := g_package||'check_non_updateable_args';
16 l_error exception;
17 l_argument varchar2(30);
18 --
19 Begin
20 hr_utility.set_location('Entering:'||l_proc, 5);
21 --
22 -- Only proceed with validation if a row exists for
23 -- the current record in the HR Schema
24 --
25 if not per_rtl_shd.api_updating
26 (p_rating_level_id => p_rec.rating_level_id
27 ,p_object_version_number => p_rec.object_version_number
28 ) then
29 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
30 hr_utility.set_message_token('PROCEDURE', l_proc);
31 hr_utility.set_message_token('STEP', '5');
32 end if;
33 hr_utility.set_location(l_proc, 6);
34 --
35 if p_rec.business_group_id <> per_rtl_shd.g_old_rec.business_group_id then
36 l_argument := 'business_group_id';
37 raise l_error;
38 end if;
39 hr_utility.set_location(l_proc, 7);
40 --
41 if p_rec.step_value <> per_rtl_shd.g_old_rec.step_value then
42 l_argument := 'step_value';
43 raise l_error;
44 end if;
45 hr_utility.set_location(l_proc, 8);
46 --
47 if p_rec.competence_id <> per_rtl_shd.g_old_rec.competence_id then
48 l_argument := 'competence_id';
49 raise l_error;
50 end if;
51 hr_utility.set_location(l_proc, 9);
52 --
53 if p_rec.rating_scale_id <> per_rtl_shd.g_old_rec.rating_scale_id then
54 l_argument := 'rating_scale_id';
55 raise l_error;
56 end if;
57 hr_utility.set_location(l_proc, 8);
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 ------------------------<chk_rat_comp_bg_exists>------------------------------
70 -----------------------------------------------------------------------------
71 --
72 -- Description:
73 -- - Validates that the rating scale or competence exists and is within the
74 -- same business group as that of rating level
75 --
76 -- Pre_conditions:
77 --
78 --
79 -- In Arguments:
80 -- p_rating_level_id
81 -- p_competence_id
82 -- p_object_version_number
83 -- p_rating_scale_id
84 -- p_business_group_id
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 -- -- rating scale or competence does not exist
94 -- -- rating scale or competence exists but not with the same business group
95 --
96 -- Access Status
97 -- Internal Table Handler Use Only.
98 --
99 --
100 procedure chk_rat_comp_bg_exists
101 (p_rating_level_id in per_rating_levels.rating_level_id%TYPE
102 ,p_object_version_number in per_rating_levels.object_version_number%TYPE
103 ,p_business_group_id in per_rating_levels.business_group_id%TYPE default null
104 ,p_competence_id in per_rating_levels.competence_id%TYPE
105 ,p_rating_scale_id in per_rating_levels.rating_scale_id%TYPE
106 )
107 is
108 --
109 l_exists varchar2(1);
110 l_api_updating boolean;
111 l_proc varchar2(72) := g_package||'chk_rat_comp_bg_exists';
112 l_business_group_id per_rating_levels.business_group_id%TYPE;
113 --
114 --
115 -- Cursor to check if rating scale exists
116 --
117 Cursor csr_rat_scale_bus_grp_exist
118 is
119 select business_group_id
120 from per_rating_scales
121 where rating_scale_id = p_rating_scale_id;
122 --
123 --
124 -- Cursor to check if competence exists
125 --
126 Cursor csr_competence_bus_grp_exist
127 is
128 select business_group_id
129 from per_competences
130 where competence_id = p_competence_id;
131 --
132 begin
133 hr_utility.set_location('Entering:'|| l_proc, 1);
134 --
135 -- Only proceed with validation if :
136 -- a) The current g_old_rec is current
137 --
138 l_api_updating := per_rtl_shd.api_updating
139 (p_rating_level_id => p_rating_level_id
140 ,p_object_version_number => p_object_version_number);
141
142 --
143 hr_utility.set_location(l_proc, 2);
144 --
145 if p_rating_scale_id is not null then
146 open csr_rat_scale_bus_grp_exist;
147 fetch csr_rat_scale_bus_grp_exist into l_business_group_id;
148 if csr_rat_scale_bus_grp_exist%notfound then
149 close csr_rat_scale_bus_grp_exist;
150 hr_utility.set_message(801,'HR_51471_RTL_RSC_NOT_EXIST');
151 hr_utility.raise_error;
152 end if;
153 close csr_rat_scale_bus_grp_exist;
154 -- check if rating scale is in the same business group
155 -- ngundura changes done for pa requirements.
156 if p_business_group_id is null then
157 if l_business_group_id is not null then
158 fnd_message.set_name('PER','HR_52694_ENTER_GLOB_RAT_SCAL');
159 fnd_message.raise_error;
160 end if;
161 else
162 if nvl(l_business_group_id,hr_api.g_number) <> p_business_group_id then
163 hr_utility.set_message(801,'HR_51470_RTL_RSC_DIFF_BUS_GRP');
164 hr_utility.raise_error;
165 end if;
166 end if;
167 -- ngundura changes done for pa requirements.
168 end if;
169 --
170 hr_utility.set_location(l_proc, 3);
171 --
172 if p_competence_id is not null then
173 open csr_competence_bus_grp_exist;
174 fetch csr_competence_bus_grp_exist into l_business_group_id;
175 if csr_competence_bus_grp_exist%notfound then
176 close csr_competence_bus_grp_exist;
177 hr_utility.set_message(801,'HR_51472_RTL_CPN_NOT_EXIST');
178 hr_utility.raise_error;
179 end if;
180 close csr_competence_bus_grp_exist;
181 -- check if rating scale is in the same business group
182 -- ngundura changes for pa requirements..
183 if p_business_group_id is null then
184 if l_business_group_id is not null then
185 fnd_message.set_name('PER','HR_52694_ENTER_GLOB_RAT_SCAL');
186 fnd_message.raise_error;
187 end if;
188 else
189 if nvl(l_business_group_id,hr_api.g_number) <> p_business_group_id then
190 hr_utility.set_message(801,'HR_51473_RTL_CPN_DIFF_BUS_GRP');
191 hr_utility.raise_error;
192 end if;
193 end if;
194 -- ngundura end of changes.
195 end if;
196 --
197 hr_utility.set_location(l_proc, 4);
198 --
199 hr_utility.set_location('Leaving: '|| l_proc, 10);
200 --
201 end chk_rat_comp_bg_exists;
202 --
203 -------------------------------------------------------------------------------
204 -------------------------------< not_used_chk_name >------------------------------------
205 -------------------------------------------------------------------------------
206 --
207 --
208 -- Description:
209 -- - Validates that a valid rating level name is entered
210 -- and is unique for a rating scale or a competence
211 --
212 --
213 -- In Arguments:
214 -- p_rating_level_id
215 -- p_name
216 -- p_object_version_number
217 -- p_rating_scale_id
218 -- p_competence_id
219 --
220 --
221 -- Post Success:
222 -- Process continues if :
223 -- All the in parameters are valid.
224 --
225 -- Post Failure:
226 -- An application error is raised and processing is terminated if any of
227 -- the following cases are found :
228 -- - name is invalid
229 -- - name is not unique
230 --
231 -- Access Status
232 -- Internal Table Handler Use Only.
233 --
234 --
235 procedure not_used_chk_name
236 (p_rating_level_id in per_rating_levels.rating_level_id%TYPE
237 ,p_object_version_number in per_rating_levels.object_version_number%TYPE
238 ,p_name in per_rating_levels.name%TYPE
239 ,p_rating_scale_id in per_rating_levels.rating_scale_id%TYPE
240 ,p_competence_id in per_rating_levels.competence_id%TYPE
241 )
242 is
243 --
244 l_exists varchar2(1);
245 l_api_updating boolean;
246 l_proc varchar2(72) := g_package||'not_used_chk_name';
247 --
248 -- Cursor to check if name is unique for rating scale or competence
249 --
250 cursor csr_chk_name_unique is
251 select 'Y'
252 from per_rating_levels
253 where ( (p_rating_level_id is null)
254 or(p_rating_level_id <> rating_level_id)
255 )
256 and name = p_name
257 and ( (nvl(competence_id,hr_api.g_number)
258 = nvl(p_competence_id,hr_api.g_number) )
259 and(nvl(rating_scale_id,hr_api.g_number)
260 = nvl(p_rating_scale_id,hr_api.g_number))
261 );
262 --
263 begin
264 hr_utility.set_location('Entering:'|| l_proc, 1);
265 --
266 -- Only proceed with validation if :
267 -- a) The current g_old_rec is current and
268 -- b) The value for name has changed
269 --
270 l_api_updating := per_rtl_shd.api_updating
271 (p_rating_level_id => p_rating_level_id
272 ,p_object_version_number => p_object_version_number);
273 --
274 if ( (l_api_updating and (per_rtl_shd.g_old_rec.name
275 <> nvl(p_name,hr_api.g_varchar2))
276 ) or
277 (NOT l_api_updating)
278 ) then
279 --
280 hr_utility.set_location(l_proc, 2);
281 --
282 -- check if the user has entered a name, as name is
283 -- is mandatory column.
284 --
285 if p_name is null then
286 hr_utility.set_message(801,'HR_51475_RTL_NAME_MANDATORY');
287 hr_utility.raise_error;
288 end if;
289 --
290 -- check if name is unique
291 --
292 open csr_chk_name_unique;
293 fetch csr_chk_name_unique into l_exists;
294 if csr_chk_name_unique%found then
295 hr_utility.set_location(l_proc, 3);
296 -- name is not unique
297 close csr_chk_name_unique;
298 hr_utility.set_message(801,'HR_51474_RTL_NOT_UNIQUE');
299 hr_utility.raise_error;
300 end if;
301 close csr_chk_name_unique;
302 end if;
303 hr_utility.set_location('Leaving:'|| l_proc, 10);
304 end not_used_chk_name;
305 --
306 -------------------------------------------------------------------------------
307 -- |----------------------< chk_step_value>--------------------------------| --
308 -------------------------------------------------------------------------------
309 --
310 -- Description
311 -- Validates the STEP_VALUE exists
312 -- Validates that STEP_VALUE is an integer.
313 --
314 -- Pre-conditions
315 --
316 -- In Arguments
317 -- p_step_value
318 --
319 -- Post Success:
320 -- Process continues if:
321 -- Step value is an integer.
322 --
323 -- Post Failure:
324 -- An application error is raised and processing is terminated if any of the
325 -- following cases are found:
326 -- - Step Value is not an integer.
327 --
328 -- Access Status
329 -- Internal Table Handler Use Only.
330 --
331 procedure chk_step_value
332 (p_step_value in per_rating_levels.step_value%TYPE
333 ,p_rating_level_id in per_rating_levels.rating_level_id%TYPE
334 ,p_rating_scale_id in per_rating_levels.rating_scale_id%TYPE
335 ,p_competence_id in per_rating_levels.competence_id%TYPE
336 ) is
337 --
338 l_proc varchar2(72) := g_package||' chk_step_value';
339 l_api_updating boolean;
340 l_intnum number;
341 l_decpoint varchar2(1);
342 l_exists varchar2(1);
343 --
344 --
345 -- Cursor to check if step value is unique for rating scale or competence
346 --
347 cursor csr_chk_step_unique is
348 select 'Y'
349 from per_rating_levels
350 where ( (p_rating_level_id is null)
351 or(p_rating_level_id <> rating_level_id)
352 )
353 and step_value = p_step_value
354 and ( (competence_id = p_competence_id)
355 or(rating_scale_id = p_rating_scale_id)
356 );
357 --
358 begin
359 hr_utility.set_location ('Entering '||l_proc, 1);
360 --
361 -- check if the user has entered a step value, as it
362 -- is a mandatory column.
363 --
364 if p_step_value is null then
365 hr_utility.set_message(801,'HR_51476_RTL_STEP_MANDATORY');
366 hr_utility.raise_error;
367 end if;
368 --
369 -- Check that step value is an integer
370 --
371 l_intnum := to_char(p_step_value);
372 l_decpoint := substr(to_char(1/2),1,1); -- get dec point character.
373 if (instr(l_intnum, l_decpoint) <> 0) then
374 hr_utility.set_location (l_proc, 2);
375 hr_utility.set_message (801, 'HR_51483_RTL_STEP_NOT_INT');
376 hr_utility.raise_error;
377 end if;
378 --
379 -- Check if step value is unique for rating scale or competence
380 --
381 open csr_chk_step_unique;
382 fetch csr_chk_step_unique into l_exists;
383 if csr_chk_step_unique%found then
384 hr_utility.set_location(l_proc, 3);
385 -- step value is not unique
386 close csr_chk_step_unique;
387 hr_utility.set_message(801,'HR_51477_RTL_STEP_NOT_UNIQUE');
388 hr_utility.raise_error;
389 end if;
390 close csr_chk_step_unique;
391 hr_utility.set_location ('Leaving '||l_proc, 3);
392 --
393 end chk_step_value;
394 --
395 -------------------------------------------------------------------------------
396 -- |-------------------------< chk_rating_level_add_del >------------------|
397 -------------------------------------------------------------------------------
398 --
399 -- Description
400 -- This function validates that:
401 -- No new levels can be added to a competence that has a general proficiency
402 -- scale assigned to it.
403 -- No new levels can be added or deleted if rating levels is referenced in:
404 -- - a Competence that is used in Competence Element
405 -- - a Rating Scale that is used in a Competence
406 -- - a Rating Scale that is used in an Assessment
407 -- Type
408 -- - a Performance Rating
409 --
410 -- Pre-conditions
411 --
412 --
413 -- In Arguments
414 -- p_rating_level_id
415 -- p_object_version_number
416 -- p_competence_id
417 -- p_rating_scale_id
418 -- p_mode (is defaulted)
419 --
420 -- Post Success
421 -- The rating scale is not referenced elsewhere and a level is added
422 -- The rating scale step is not referenced elsewhere and a level is deleted
423 --
424 -- Post Failure
425 -- An application error is raised and processing is terminated if the rating
426 -- scale is referenced in any one of the above
427 --
428 -- Access Status
429 -- Internal Table Handler Use Only.
430 --
431 procedure chk_rating_level_add_del
432 (p_rating_level_id in per_rating_levels.rating_level_id%TYPE
433 ,p_object_version_number in per_rating_levels.object_version_number%TYPE
434 ,p_competence_id in per_rating_levels.competence_id%TYPE
435 ,p_rating_scale_id in per_rating_levels.rating_scale_id%TYPE
436 ,p_mode in varchar2 default null
437 ) is
438 --
439 l_proc varchar2(72) := g_package||'chk_rating_level_add_del';
440 l_api_updating boolean;
441 l_exists varchar2(1);
442 --
443 -- Cursor to check that a rating level cannot be added to a competence
444 -- that has a general proficiency scale assigned to it.
445 --
446 Cursor csr_chk_rating_in_competence is
447 select null
448 from per_competences cpn
449 where cpn.competence_id = p_competence_id
450 and cpn.rating_scale_id is not null;
451 --
452 -- Cursor to check if rating level used in a competence that is used
453 -- in a competence element
454 --
455 -- bug fix 4063493
456 -- condition added to cursor to check the competence element type.
457 Cursor csr_chk_competence is
458 select null
459 from per_rating_levels rtl, per_competences cp,
460 per_competence_elements ce
461 where rtl.competence_id = p_competence_id
462 and rtl.competence_id = cp.competence_id
463 and cp.competence_id = ce.competence_id
464 and ce.type in ('ASSESSMENT_COMPETENCE','REQUIREMENT',
465 'PERSONAL');
466 --
467 -- Cursor to check if rating level used in a rating scale that is used in
468 -- in a competence
469 --
470 Cursor csr_chk_rat_competence is
471 select null
472 from per_rating_levels rtl,
473 per_rating_scales rsc,
474 per_competences cp
475 where rtl.rating_scale_id = p_rating_scale_id
476 and rtl.rating_scale_id = rsc.rating_scale_id
477 and rsc.rating_scale_id = cp.rating_scale_id ;
478
479 --
480 -- Cursor to check if rating level used in an Assessment Type that is
481 --
482 Cursor csr_chk_ass_types is
483 select null
484 from per_rating_levels rtl,
485 per_rating_scales rsc,
486 per_assessment_types aty
487 where rtl.rating_scale_id = p_rating_scale_id
488 and rtl.rating_scale_id = rsc.rating_scale_id
489 and ( (rsc.rating_scale_id = aty.rating_scale_id)
490 or(rsc.rating_scale_id = aty.weighting_scale_id)
491 );
492 --
493 begin
494 hr_utility.set_location('Entering: '||l_proc, 1);
495 --
496 -- Check if the rating scale is being used as a proficiency scale
497 -- for a competence
498 --
499 hr_utility.set_location (l_proc, 2);
500 if p_competence_id is not null and p_mode = 'ADD' then
501 open csr_chk_rating_in_competence;
502 fetch csr_chk_rating_in_competence into l_exists;
503 if csr_chk_rating_in_competence%found then
504 hr_utility.set_message (801,'HR_51438_COMP_PROF_LVL_EXIST');
505 hr_utility.raise_error;
506 end if;
507 close csr_chk_rating_in_competence;
508 end if;
509 --
510 -- Check if rating level is for competence that is used in competence
511 -- element.
512 --
513 hr_utility.set_location (l_proc, 3);
514 open csr_chk_competence;
515 fetch csr_chk_competence into l_exists;
516 if csr_chk_competence%found then
517 close csr_chk_competence;
518 hr_utility.set_message (801,'HR_51479_RTL_CPN_EXIST_IN_ELE');
519 hr_utility.raise_error;
520 end if;
521 close csr_chk_competence;
522 --
523 -- Check if rating level is for a Rating Scale that is used in a
524 -- Competence
525 --
526 hr_utility.set_location (l_proc, 4);
527 open csr_chk_rat_competence;
528 fetch csr_chk_rat_competence into l_exists;
529 if csr_chk_rat_competence%found then
530 close csr_chk_rat_competence;
531 hr_utility.set_message (801,'HR_51480_RTL_RSC_IN_CPN');
532 hr_utility.raise_error;
533 end if;
534 close csr_chk_rat_competence;
535 --
536 -- Check if rating level is for a Rating Scale that is used
537 -- in an Assessment Type
538 --
539 hr_utility.set_location (l_proc, 5);
540 open csr_chk_ass_types;
541 fetch csr_chk_ass_types into l_exists;
542 if csr_chk_ass_types%found then
543 close csr_chk_ass_types;
544 hr_utility.set_message (801,'HR_51481_RTL_RSC_IN_AST');
545 hr_utility.raise_error;
546 end if;
547 close csr_chk_ass_types;
548 --
549 end chk_rating_level_add_del;
550 --
551 -------------------------------------------------------------------------------
552 -- |-------------------------< chk_rating_level_in_ele >--------------------|
553 -------------------------------------------------------------------------------
554 --
555 -- Description
556 -- This function validates that:
557 -- - if rating level is used in competence element, then do not allow delete
558 --
559 -- Pre-conditions
560 -- valid rating_level_id
561 --
562 -- In Arguments
563 -- p_rating_level_id
564 -- p_object_version_number
565 --
566 -- Post Success
567 -- The rating scale is not referenced in competence element
568 --
569 -- Post Failure
570 -- An application error is raised and processing is terminated if the rating
571 -- scale is referenced in competence element
572 --
573 -- Access Status
574 -- Internal Table Handler Use Only.
575 --
576 -- Bug 3771360 Starts Here
577 -- Desc: Re written the procedure by using the ref cursor to improve the performance
578 --
579 procedure chk_rating_level_in_ele
580 (p_rating_level_id in per_rating_levels.rating_level_id%TYPE
581 ,p_object_version_number in per_rating_levels.object_version_number%TYPE
582 ) is
583 --
584 l_proc varchar2(72) := g_package||'chk_rating_level_in_ele';
585 l_api_updating boolean;
586 l_exists varchar2(1);
587 l_error varchar2(1); -- bug 3771360
588 --
589 -- Cursor to check if rating level used in competence element
590 --
591 Type cref is Ref Cursor;
592 cmp_csr cref;
593 l_sql_stmt varchar2(2000);
594 l_sql_stmt1 varchar2(2000);
595 l_sql_stmt2 varchar2(2000);
596 l_sql_stmt3 varchar2(2000);
597 l_sql_stmt4 varchar2(2000);
598
599 --
600 begin
601 -- check if rating level is used in competence element
602 hr_utility.set_location (l_proc, 1);
603 l_error := 'N';
604 l_sql_stmt := 'select null
605 from per_rating_levels rtl,per_competence_elements perce
606 where rtl.rating_level_id = '||p_rating_level_id||
607 ' and rtl.rating_level_id = ';
608 l_sql_stmt1 := l_sql_stmt||'perce.rating_level_id';
609
610 -- check if rating level is used in competence element
611 hr_utility.set_location (l_proc, 1);
612
613 open cmp_csr for l_sql_stmt1;
614 fetch cmp_csr into l_exists;
615 if cmp_csr%notfound then
616 close cmp_csr;
617 l_sql_stmt2 := l_sql_stmt||'perce.weighting_level_id';
618 open cmp_csr for l_sql_stmt2;
619 fetch cmp_csr into l_exists;
620 --
621 if cmp_csr%notfound then
622 close cmp_csr;
623 l_sql_stmt3 := l_sql_stmt||'perce.proficiency_level_id';
624 open cmp_csr for l_sql_stmt3;
625 fetch cmp_csr into l_exists;
626 --
627 if cmp_csr%notfound then
628 close cmp_csr;
629 l_sql_stmt4 := l_sql_stmt||'perce.high_proficiency_level_id';
630 open cmp_csr for l_sql_stmt4;
631 fetch cmp_csr into l_exists;
632 if cmp_csr%notfound then
633 close cmp_csr;
634 l_error := 'N';
635 -- l_err = 1;
636 else
637 close cmp_csr;
638 l_error := 'Y';
639 end if;
640 else
641 close cmp_csr;
642 l_error := 'Y';
643 end if;
644 else
645 close cmp_csr;
646 l_error := 'Y';
647 end if;
648 else
649 close cmp_csr;
650 l_error := 'Y';
651 end if;
652 --
653 if l_error = 'Y' then
654 hr_utility.set_message (801,'HR_51479_RTL_CPN_EXIST_IN_ELE');
655 hr_utility.raise_error;
656 end if;
657 --
658 --
659 end chk_rating_level_in_ele;
660 --
661 -- Bug 3771360 Ends Here
662 --
663 -- -----------------------------------------------------------------------
664 -- |------------------------------< chk_df >-----------------------------|
665 -- -----------------------------------------------------------------------
666 --
667 -- Description:
668 -- Validates the all Descriptive Flexfield values.
669 --
670 -- Pre-conditions:
671 -- All other columns have been validated. Must be called as the
672 -- last step from insert_validate and update_validate.
673 --
674 -- In Arguments:
675 -- p_rec
676 --
677 -- Post Success:
678 -- If the Descriptive Flexfield structure column and data values are
679 -- all valid this procedure will end normally and processing will
680 -- continue.
681 --
682 -- Post Failure:
683 -- If the Descriptive Flexfield structure column value or any of
684 -- the data values are invalid then an application error is raised as
685 -- a PL/SQL exception.
686 --
687 -- Access Status:
688 -- Internal Row Handler Use Only.
689 --
690 -- {End Of Comments}
691 -- ----------------------------------------------------------------------------
692 --
693 procedure chk_df
694 (p_rec in per_rtl_shd.g_rec_type) is
695 --
696 l_proc varchar2(72) := g_package||'chk_df';
697 --
698 begin
699 hr_utility.set_location('Entering:'||l_proc, 10);
700 --
701 if (((p_rec.rating_level_id is not null) and (
702 nvl(per_rtl_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
703 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
704 nvl(per_rtl_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
705 nvl(p_rec.attribute1, hr_api.g_varchar2) or
706 nvl(per_rtl_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
707 nvl(p_rec.attribute2, hr_api.g_varchar2) or
708 nvl(per_rtl_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
709 nvl(p_rec.attribute3, hr_api.g_varchar2) or
710 nvl(per_rtl_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
711 nvl(p_rec.attribute4, hr_api.g_varchar2) or
712 nvl(per_rtl_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
713 nvl(p_rec.attribute5, hr_api.g_varchar2) or
714 nvl(per_rtl_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
715 nvl(p_rec.attribute6, hr_api.g_varchar2) or
716 nvl(per_rtl_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
717 nvl(p_rec.attribute7, hr_api.g_varchar2) or
718 nvl(per_rtl_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
722 nvl(per_rtl_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
719 nvl(p_rec.attribute8, hr_api.g_varchar2) or
720 nvl(per_rtl_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
721 nvl(p_rec.attribute9, hr_api.g_varchar2) or
723 nvl(p_rec.attribute10, hr_api.g_varchar2) or
724 nvl(per_rtl_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
725 nvl(p_rec.attribute11, hr_api.g_varchar2) or
726 nvl(per_rtl_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
727 nvl(p_rec.attribute12, hr_api.g_varchar2) or
728 nvl(per_rtl_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
729 nvl(p_rec.attribute13, hr_api.g_varchar2) or
730 nvl(per_rtl_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
731 nvl(p_rec.attribute14, hr_api.g_varchar2) or
732 nvl(per_rtl_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
733 nvl(p_rec.attribute15, hr_api.g_varchar2) or
734 nvl(per_rtl_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
735 nvl(p_rec.attribute16, hr_api.g_varchar2) or
736 nvl(per_rtl_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
737 nvl(p_rec.attribute17, hr_api.g_varchar2) or
738 nvl(per_rtl_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
739 nvl(p_rec.attribute18, hr_api.g_varchar2) or
740 nvl(per_rtl_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
741 nvl(p_rec.attribute19, hr_api.g_varchar2) or
742 nvl(per_rtl_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
743 nvl(p_rec.attribute20, hr_api.g_varchar2)))
744 or
745 (p_rec.rating_level_id is null))
746 and hr_rating_levels_api.g_ignore_df <> 'Y' then -- BUG3621261
747 --
748 -- Only execute the validation if absolutely necessary:
749 -- a) During update, the structure column value or any
750 -- of the attribute values have actually changed.
751 -- b) During insert.
752 --
753 hr_dflex_utility.ins_or_upd_descflex_attribs
754 (p_appl_short_name => 'PER'
755 ,p_descflex_name => 'PER_RATING_LEVELS'
756 ,p_attribute_category => p_rec.attribute_category
757 ,p_attribute1_name => 'ATTRIBUTE1'
758 ,p_attribute1_value => p_rec.attribute1
759 ,p_attribute2_name => 'ATTRIBUTE2'
760 ,p_attribute2_value => p_rec.attribute2
761 ,p_attribute3_name => 'ATTRIBUTE3'
762 ,p_attribute3_value => p_rec.attribute3
763 ,p_attribute4_name => 'ATTRIBUTE4'
764 ,p_attribute4_value => p_rec.attribute4
765 ,p_attribute5_name => 'ATTRIBUTE5'
766 ,p_attribute5_value => p_rec.attribute5
767 ,p_attribute6_name => 'ATTRIBUTE6'
768 ,p_attribute6_value => p_rec.attribute6
769 ,p_attribute7_name => 'ATTRIBUTE7'
770 ,p_attribute7_value => p_rec.attribute7
771 ,p_attribute8_name => 'ATTRIBUTE8'
772 ,p_attribute8_value => p_rec.attribute8
773 ,p_attribute9_name => 'ATTRIBUTE9'
774 ,p_attribute9_value => p_rec.attribute9
775 ,p_attribute10_name => 'ATTRIBUTE10'
776 ,p_attribute10_value => p_rec.attribute10
777 ,p_attribute11_name => 'ATTRIBUTE11'
778 ,p_attribute11_value => p_rec.attribute11
779 ,p_attribute12_name => 'ATTRIBUTE12'
780 ,p_attribute12_value => p_rec.attribute12
781 ,p_attribute13_name => 'ATTRIBUTE13'
782 ,p_attribute13_value => p_rec.attribute13
783 ,p_attribute14_name => 'ATTRIBUTE14'
784 ,p_attribute14_value => p_rec.attribute14
785 ,p_attribute15_name => 'ATTRIBUTE15'
786 ,p_attribute15_value => p_rec.attribute15
787 ,p_attribute16_name => 'ATTRIBUTE16'
788 ,p_attribute16_value => p_rec.attribute16
789 ,p_attribute17_name => 'ATTRIBUTE17'
790 ,p_attribute17_value => p_rec.attribute17
791 ,p_attribute18_name => 'ATTRIBUTE18'
792 ,p_attribute18_value => p_rec.attribute18
793 ,p_attribute19_name => 'ATTRIBUTE19'
794 ,p_attribute19_value => p_rec.attribute19
795 ,p_attribute20_name => 'ATTRIBUTE20'
796 ,p_attribute20_value => p_rec.attribute20);
797 end if;
798 --
799 hr_utility.set_location(' Leaving:'||l_proc, 20);
800 end chk_df;
801 --
802 -- ----------------------------------------------------------------------------
803 -- |---------------------------< insert_validate >----------------------------|
804 -- ----------------------------------------------------------------------------
805 Procedure insert_validate(p_rec in per_rtl_shd.g_rec_type,
806 p_effective_date in date) is
807 --
808 l_proc varchar2(72) := g_package||'insert_validate';
809 --
810 Begin
811 hr_utility.set_location('Entering:'||l_proc, 5);
812 --
813 -- Call all supporting business operations
814 --
815 -- ngundura changes done as per pa requirements.
816 hr_utility.set_location('p_rec.business_group_id :'|| to_char(p_rec.business_group_id),99);
817 if p_rec.business_group_id is not null then
818 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
819 end if;
820 -- end of changes
821 --
822 --
823 -- Check mandatory parameters rating_scale_id or competence_id are set.
824 -- Only one of them has to be set and not both.
825 -- Both cannot be null.
826 --
827 if ( ( p_rec.rating_scale_id is not null and p_rec.competence_id is not null )
828 or ( p_rec.rating_scale_id is null and p_rec.competence_id is null )
829 )
830 then
831 hr_utility.set_message(801,'HR_51482_RTL_RSC_OR_CPN');
832 hr_utility.raise_error;
833 end if;
834 --
835 -- chk rating scale or competence exist within the
836 -- same business group
837 --
838 hr_utility.set_location('Entering per_rtl_bus.chk_rat_comp_bg_exists',9999);
839 per_rtl_bus.chk_rat_comp_bg_exists
840 (p_rating_level_id => p_rec.rating_level_id
841 ,p_object_version_number => p_rec.object_version_number
842 ,p_business_group_id => p_rec.business_group_id
843 ,p_competence_id => p_rec.competence_id
844 ,p_rating_scale_id => p_rec.rating_scale_id
845 );
846 --
847 -- Rule check step value is not null and is an integer value
848 --
849 hr_utility.set_location('Entering per_rtl_bus.chk_step_value',99);
850 per_rtl_bus.chk_step_value
851 (p_step_value => p_rec.step_value
852 ,p_rating_level_id => p_rec.rating_level_id
853 ,p_rating_scale_id => p_rec.rating_scale_id
854 ,p_competence_id => p_rec.competence_id
855 );
856
857 --
858 -- pmfletch Now called from TL row handler
859 --
860 -- Rule Check unique level name
861 --
862 --per_rtl_bus.chk_name
863 --(p_rating_level_id => p_rec.rating_level_id
864 --,p_object_version_number => p_rec.object_version_number
865 --,p_name => p_rec.name
866 --,p_rating_scale_id => p_rec.rating_scale_id
867 --,p_competence_id => p_rec.competence_id
868 --);
869 --
870 -- Check if a new level can be inserted for a
871 -- rating scale or competence
872 --
873 per_rtl_bus.chk_rating_level_add_del
874 (p_rating_level_id => p_rec.rating_level_id
875 ,p_object_version_number => p_rec.object_version_number
876 ,p_competence_id => p_rec.competence_id
877 ,p_rating_scale_id => p_rec.rating_scale_id
878 );
879 --
880 -- call descriptive flexfield validation routines
881 --
882 /*
883 IF hr_general.get_calling_context <>'FORMS' THEN
884 per_rtl_flex.df(p_rec => p_rec);
885 END IF;
886 */
887 --
888 per_rtl_bus.chk_df(p_rec => p_rec);
889 --
890 hr_utility.set_location(' Leaving:'||l_proc, 10);
891 End insert_validate;
892 --
893 -- ----------------------------------------------------------------------------
894 -- |---------------------------< update_validate >----------------------------|
895 -- ----------------------------------------------------------------------------
896 Procedure update_validate(p_rec in per_rtl_shd.g_rec_type,
897 p_effective_date in date) is
898 --
899 l_proc varchar2(72) := g_package||'update_validate';
900 --
901 Begin
902 hr_utility.set_location('Entering:'||l_proc, 5);
903 --
904 -- Call all supporting business operations
905 --
906 if p_rec.business_group_id is not null then
907 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
908 end if;
909 --
910 -- Rule Check Business group id and step value cannot be updated
911 --
912 chk_non_updateable_args(p_rec => p_rec);
913 --
914 -- pmfletch - Now called from TL row handler
915 --
916 -- Rule Check unique level name
917 --
918 --per_rtl_bus.chk_name
919 --(p_rating_level_id => p_rec.rating_level_id
920 --,p_object_version_number => p_rec.object_version_number
921 --,p_name => p_rec.name
922 --,p_rating_scale_id => p_rec.rating_scale_id
923 --,p_competence_id => p_rec.competence_id
924 --);
925 --
926 -- call descriptive flexfield validation routines
927 --
928 /*
929 IF hr_general.get_calling_context <>'FORMS' THEN
930 per_rtl_flex.df(p_rec => p_rec);
931 END IF;
932 */
933 --
934 per_rtl_bus.chk_df(p_rec => p_rec);
935 --
936 hr_utility.set_location(' Leaving:'||l_proc, 10);
937 End update_validate;
938 --
939 -- ----------------------------------------------------------------------------
940 -- |---------------------------< delete_validate >----------------------------|
941 -- ----------------------------------------------------------------------------
942 Procedure delete_validate(p_rec in per_rtl_shd.g_rec_type) is
943 --
944 l_proc varchar2(72) := g_package||'delete_validate';
945 --
946 Begin
947 hr_utility.set_location('Entering:'||l_proc, 5);
948 --
949 -- check if rating level exists in competence element
950 --
951 per_rtl_bus.chk_rating_level_in_ele
952 (p_rating_level_id => p_rec.rating_level_id
953 ,p_object_version_number => p_rec.object_version_number
954 );
955 --
956 -- check other tables
957 --
958 per_rtl_bus.chk_rating_level_add_del
959 (p_rating_level_id => p_rec.rating_level_id
960 ,p_object_version_number => p_rec.object_version_number
961 ,p_competence_id => per_rtl_shd.g_old_rec.competence_id
962 ,p_rating_scale_id => per_rtl_shd.g_old_rec.rating_scale_id
963 );
964 --
965 hr_utility.set_location(' Leaving:'||l_proc, 10);
966 End delete_validate;
967 --
968 --
969 -- ----------------------------------------------------------------------------
970 -- |-----------------------< return_legislation_code >-------------------------|
971 -- ----------------------------------------------------------------------------
972 Function return_legislation_code
973 ( p_rating_level_id in number
974 ) return varchar2 is
975 --
976 -- Declare cursor
977 --
978 cursor csr_leg_code is
979 select legislation_code
980 from per_business_groups pbg,
981 per_rating_levels prl
982 where prl.rating_level_id = p_rating_level_id
983 and pbg.business_group_id = prl.business_group_id;
984
985 l_proc varchar2(72) := g_package||'return_legislation_code';
986 l_legislation_code varchar2(150);
987 l_business_group_flag varchar2(1);
988 --
989 Begin
990 hr_utility.set_location('Entering:'||l_proc, 5);
991 -- ngundura changes for pa requirement
992 select 'Y' into l_business_group_flag
993 from per_rating_levels
994 where rating_level_id = p_rating_level_id
995 and business_group_id is null;
996
997 if l_business_group_flag = 'Y' then
998 return null;
999 end if;
1000 -- ngundura end of changes.
1001 --
1002 -- Ensure that all the mandatory parameters are not null
1003 --
1004 hr_api.mandatory_arg_error (p_api_name => l_proc,
1005 p_argument => 'rating_level_id',
1006 p_argument_value => p_rating_level_id );
1007 open csr_leg_code;
1008 fetch csr_leg_code into l_legislation_code;
1009 if csr_leg_code%notfound then
1010 close csr_leg_code;
1011 --
1012 -- The primary key is invalid therefore we must error out
1013 --
1014 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1015 hr_utility.raise_error;
1016 end if;
1017 --
1018 close csr_leg_code;
1019 return l_legislation_code;
1020 --
1021 hr_utility.set_location(' Leaving:'||l_proc, 10);
1022 --
1023 End return_legislation_code;
1024 --
1025 --
1026 end per_rtl_bus;
[Home] [Help]