1 Package Body per_rsc_bus as
2 /* $Header: perscrhi.pkb 120.0 2005/05/31 19:45:58 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_rsc_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be used by the
11 -- return_legislation_code function.
12 --
13 g_rating_scale_id number default null;
14 g_legislation_code varchar2(150) default null;
15 -- ----------------------------------------------------------------------------
16 -- |----------------------< check_non_updateable_args >-----------------------|
17 -- ----------------------------------------------------------------------------
18 --
19 Procedure check_non_updateable_args(p_rec in per_rsc_shd.g_rec_type) is
20 --
21 l_proc varchar2(72) := g_package||'check_non_updateable_args';
22 l_error exception;
23 l_argument varchar2(30);
24 --
25 Begin
26 hr_utility.set_location('Entering:'||l_proc, 5);
27 --
28 -- Only proceed with validation if a row exists for
29 -- the current record in the HR Schema
30 --
31 if not per_rsc_shd.api_updating
32 (p_rating_scale_id => p_rec.rating_scale_id
33 ,p_object_version_number => p_rec.object_version_number
34 ) then
35 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
36 hr_utility.set_message_token('PROCEDURE', l_proc);
37 hr_utility.set_message_token('STEP', '5');
38 end if;
39 --
40 hr_utility.set_location(l_proc, 6);
41 --
42 if p_rec.business_group_id <> per_rsc_shd.g_old_rec.business_group_id then
43 l_argument := 'business_group_id';
44 raise l_error;
45 end if;
46 hr_utility.set_location(l_proc, 7);
47 --
48 if p_rec.rating_scale_id <> per_rsc_shd.g_old_rec.rating_scale_id then
49 l_argument := 'rating_scale_id';
50 raise l_error;
51 end if;
52 hr_utility.set_location(l_proc, 9);
53 --
54 if p_rec.type <> per_rsc_shd.g_old_rec.type then
55 l_argument := 'type';
56 raise l_error;
57 end if;
58 hr_utility.set_location(l_proc, 10);
59 --
60 exception
61 when l_error then
62 hr_api.argument_changed_error
63 (p_api_name => l_proc
64 ,p_argument => l_argument);
65 when others then
66 raise;
67 hr_utility.set_location(' Leaving:'||l_proc, 12);
68 end check_non_updateable_args;
69 --
70 --
71 --
72 -- ----------------------------------------------------------------------------
73 -- |---------------------------<not_used_chk_name>-------------------------------------|
74 -- ----------------------------------------------------------------------------
75 -- {Start Of Comments}
76 --
77 -- Description
78 -- - Validates that NAME exists
79 -- - Validates that NAME is UNIQUE for the BUSINESS_GROUP
80 --
81 -- Pre-conditions
82 -- - A valid BUSINESS_GROUP_ID
83 --
84 --
85 -- In Arguments:
86 -- p_rating_scale_id
87 -- p_business_group_id
88 -- p_name
89 -- p_object_version_number
90 --
91 -- Post Success:
92 -- Process continues if:
93 -- All the in parameters are valid.
94 --
95 -- Post Failure:
96 -- An application error is raised and processing is terminated if any of
97 -- the folowing cases are found:
98 -- - The NAME does not exist.
99 -- - The NAME is not UNIQUE for the BUSINESS_GROUP
100 --
101 -- Access Status
102 -- Internal Table Handler Use Only.
103 --
104 --
105
106 procedure not_used_chk_name
107 (p_rating_scale_id in per_rating_scales.rating_scale_id%TYPE
108 ,p_business_group_id in per_rating_scales.business_group_id%TYPE
109 ,p_name in per_rating_scales.name%TYPE
110 ,p_object_version_number in per_rating_scales.object_version_number%TYPE
111 )
112 is
113 --
114 l_exists per_rating_scales.business_group_id%TYPE;
115 l_api_updating boolean;
116 l_proc varchar2(72) := g_package||'not_used_chk_name';
117 l_business_group_id number(15);
118 --
119 --
120 -- Cursor to check name is unique within business group
121 -- ngundura changes done for pa requirements.
122 cursor csr_name_exists is
123 select business_group_id
124 from per_rating_scales
125 where ( (p_rating_scale_id is null)
126 or(rating_scale_id <> p_rating_scale_id)
127 )
128 and name = p_name
129 and p_business_group_id is null
130 UNION
131 select business_group_id
132 from per_rating_scales
133 where ( (p_rating_scale_id is null)
134 or(rating_scale_id <> p_rating_scale_id)
135 )
136 and name = p_name
137 and (business_group_id = p_business_group_id or
138 business_group_id is null)
139 and p_business_group_id is not null;
140 -- ngundura end of changes
141 --
142 begin
143 hr_utility.set_location ('Entering:'|| l_proc, 1);
144 --
145 --
146 if p_name is null then
147 hr_utility.set_message(801, 'HR_51571_RSC_NAME_MANDATORY');
148 hr_utility.raise_error;
149 end if;
150 --
151 -- ngundura changes as per the pa requirements.
152 /* hr_api.mandatory_arg_error
153 (p_api_name => l_proc
154 ,p_argument => 'business_group_id'
155 ,p_argument_value => p_business_group_id
156 );
157 */
158 -- ngundura end of changes.
159 --
160 -- Only proceed with validation if:
161 -- a) The current g_old_rec is current and
162 -- b) The value for name has changed.
163 --
164 l_api_updating := per_rsc_shd.api_updating
165 (p_rating_scale_id => p_rating_scale_id
166 ,p_object_version_number => p_object_version_number
167 );
168 --
169 hr_utility.set_location (l_proc, 3);
170 --
171 if (l_api_updating AND
172 nvl(per_rsc_shd.g_old_rec.name, hr_api.g_varchar2)
173 <> nvl(p_name, hr_api.g_varchar2)
174 or not l_api_updating)
175 then
176 --
177 hr_utility.set_location (l_proc, 4);
178 --
179 -- Check that NAME is UNIQUE
180 --
181 open csr_name_exists;
182 hr_utility.set_location (l_proc, 100);
183 fetch csr_name_exists into l_exists;
184 if csr_name_exists%found then
185 hr_utility.set_location(l_proc, 10);
186 close csr_name_exists;
187 hr_utility.set_location(to_char(l_exists), 99);
188 if l_exists is null then
189 fnd_message.set_name('PER', 'HR_52696_RSC_NAME_IN_GLOB');
190 fnd_message.raise_error;
191 else
192 fnd_message.set_name('PER', 'HR_52697_RSC_NAME_IN_BUSGRP');
193 fnd_message.raise_error;
194 end if;
195 end if;
196 close csr_name_exists;
197 end if;
198 --
199 hr_utility.set_location ('Leaving '||l_proc, 20);
200 end not_used_chk_name;
201 --
202 --
203 -------------------------------------------------------------------------------
204 -------------------------check_type--------------------------------------------
205 -------------------------------------------------------------------------------
206 --
207 -- Description
208 -- This function validates that:
209 -- if type is not null, it must have a value of 'PROFIECIENCY', 'WEIGHTING' or
210 -- 'PERFORMANCE'.
211 --
212 -- In Arguments
213 -- p_rating_level_id
214 -- p_type
215 -- p_effective_date
216 -- p_object_version_number
217 --
218 -- Post Success
219 -- Process continues if:
220 -- The value of type is 'PROFIECIENCY', 'WEIGHTING' or 'PERFORMANCE'
221 --
222 -- Post Failure
223 -- An application error is raised and processing is terminated if any of
224 -- the following cases are found:
225 -- - Type does not have a value of 'PROFIECIENCY', 'WEIGHTING' or 'PERFORMANCE'
226 --
227 -- Access status
228 -- Internal Table Handler Use Only
229 --
230 procedure chk_type
231 (p_rating_scale_id in per_rating_scales.rating_scale_id%TYPE
232 ,p_type in per_rating_scales.type%TYPE
233 ,p_effective_date in date
234 ,p_object_version_number in per_rating_scales.object_version_number%TYPE
235 ) is
236 --
237 l_proc varchar2(72) := g_package||'chk_not_applicable_flag';
238 l_api_updating boolean;
239 --
240 begin
241 hr_utility.set_location('Entering: '||l_proc, 1);
242 --
243 -- Check mandatory parameters have been set.
244 --
245 hr_api.mandatory_arg_error
246 (p_api_name => l_proc
247 ,p_argument => 'effective_date'
248 ,p_argument_value => p_effective_date
249 );
250 --
251 -- Only proceed with the validation if:
252 -- a) The current g_old_rec is current
253 -- b) The not_applicable value has changed.
254 -- c) A record is being inserted.
255 --
256 l_api_updating := per_rsc_shd.api_updating
257 (p_rating_scale_id => p_rating_scale_id
258 ,p_object_version_number => p_object_version_number
259 );
260 --
261 if ((l_api_updating and nvl(per_rsc_shd.g_old_rec.type,
262 hr_api.g_varchar2)
263 <> nvl(p_type, hr_api.g_varchar2)) or
264 (NOT l_api_updating)) then
265 --
266 hr_utility.set_location(l_proc, 2);
267 --
268 -- If type is not null then
269 -- Check if the type value exists in hr_lookups
270 -- where lookup_type is 'RATING_SCALE_TYPE'
271 --
272 if p_type is not null then
273 if hr_api.not_exists_in_hr_lookups
274 (p_effective_date => p_effective_date
275 ,p_lookup_type => 'RATING_SCALE_TYPE'
276 ,p_lookup_code => p_type
277 ) then
278 hr_utility.set_message(801,'HR_51444_RSC_INVALID_TYPE');
279 hr_utility.raise_error;
280 end if;
281 hr_utility.set_location(l_proc, 3);
282 end if;
283 end if;
284 hr_utility.set_location('Leaving '||l_proc, 4);
285 end chk_type;
286 --
287 --
288 --
289 -------------------------------------------------------------------------------
290 -------------------------check_default_flag------------------------------------
291 -------------------------------------------------------------------------------
292 --
293 -- Description
294 -- This function validates that:
295 -- if default_flag is not null, it must have a value of 'Y' or 'N'
296 -- There is at most only one Default Flag value of 'Y' for each Type within
297 -- a business group.
298 --
299 -- In Arguments
300 -- p_rating_level_id
301 -- p_default_flag
302 -- p_effective_date
303 -- p_object_version_number
304 --
305 -- Post Success
306 -- Process continues if:
307 -- The value of default_flag is 'Y' or 'N'
308 -- There is at most only one Default Flag value of 'Y' for each Type within
309 -- a Business Group
310 --
311 -- Post Failure
312 -- An application error is raised and processing is terminated if any of
313 -- the following cases are found:
314 -- - default_flag does not have a value of 'Y' or 'N'
315 -- - there is more then one Default Flag value of 'Y' for a Type within
316 -- a Business Group.
317 --
318 -- Access status
319 -- Internal Table Handler Use Only
320 --
321 procedure chk_default_flag
322 (p_rating_scale_id in per_rating_scales.rating_scale_id%TYPE
323 ,p_business_group_id in per_rating_scales.business_group_id%TYPE default null
324 ,p_type in per_rating_scales.type%TYPE
325 ,p_default_flag in per_rating_scales.default_flag%TYPE
326 ,p_effective_date in date
327 ,p_object_version_number in per_rating_scales.object_version_number%TYPE
328 ) is
329 --
330 l_proc varchar2(72) := g_package||'chk_default_flag';
331 l_api_updating boolean;
332 l_exists varchar2(1);
333 --
334 -- Define a cursor to get a default flag with a value of 'Y' for a specific
335 -- Business Group
336 -- ngundura changes done for pa requirements..
337 cursor csr_get_default_flag is
338 select null
339 from per_rating_scales
340 where ( (p_rating_scale_id is null)
341 or(rating_scale_id <> p_rating_scale_id)
342 )
343 and default_flag = 'Y'
344 and type = p_type
345 and business_group_id is null
346 and p_business_group_id is null
347 UNION
348 select null
349 from per_rating_scales
350 where ( (p_rating_scale_id is null)
351 or(rating_scale_id <> p_rating_scale_id)
352 )
353 and business_group_id = p_business_group_id
354 and default_flag = 'Y'
355 and type = p_type
356 and p_business_group_id is not null;
357 -- ngundura end of changes
358 begin
359 hr_utility.set_location('Entering: '||l_proc, 1);
360 --
361 -- Check mandatory parameters have been set.
362 --
363 hr_api.mandatory_arg_error
364 (p_api_name => l_proc
365 ,p_argument => 'effective_date'
366 ,p_argument_value => p_effective_date
367 );
368 --
369 -- Only proceed with the validation if:
370 -- a) The current g_old_rec is current
371 -- b) The default flag has changed.
372 -- c) A record is being inserted.
373 --
374 l_api_updating := per_rsc_shd.api_updating
375 (p_rating_scale_id => p_rating_scale_id
376 ,p_object_version_number => p_object_version_number
377 );
378 --
379 if ((l_api_updating and nvl(per_rsc_shd.g_old_rec.default_flag,
380 hr_api.g_varchar2)
381 <> nvl(p_default_flag, hr_api.g_varchar2))
382 or
383 (NOT l_api_updating)) then
384 --
385 hr_utility.set_location(l_proc, 2);
386 --
387 --
388 -- If default_flag is not null then
389 -- Check if the default_flag value exists in hr_lookups
390 -- where lookup_type is 'YES_NO'
391 --
392 if p_default_flag is not null then
393 if hr_api.not_exists_in_hr_lookups
394 (p_effective_date => p_effective_date
395 ,p_lookup_type => 'YES_NO'
396 ,p_lookup_code => p_default_flag
397 )
398 then hr_utility.set_message(801,'HR_51450_RSC_INV_DEF_FLAG');
399 hr_utility.raise_error;
400 end if;
401 hr_utility.set_location(l_proc, 3);
402 end if;
403 end if;
404 --
405 hr_utility.set_location (l_proc, 4);
406 --
407 if p_default_flag = 'Y' then
408 open csr_get_default_flag;
409 fetch csr_get_default_flag into l_exists;
410 if csr_get_default_flag%found then
411 close csr_get_default_flag;
412 hr_utility.set_location (l_proc, 5);
413 hr_utility.set_message (801, 'HR_51451_RSC_DEFAULT_EXISTS');
414 hr_utility.raise_error;
415 end if;
416 close csr_get_default_flag;
417 end if;
418 --
419 hr_utility.set_location('Leaving '||l_proc, 6);
420 end chk_default_flag;
421 --
422 --
423 --
424 -------------------------------------------------------------------------------
425 -- --------------------------chk_rating_scale_delete---------------------------
426 -------------------------------------------------------------------------------
427 --
428 --
429 -- Description
430 -- - Checks the rating scale is not referenced by an assessment type as a
431 -- rating scale id.
432 -- - Checks the rating scale is not referenced by an assessment type as a
433 -- wighting scale id.
434 -- - Checks the rating scale is not referenced by a performance rating.
438 -- which is referenced by a competence element as a weighting level id
435 -- - Checks the rating scale is not referenced by a rating level
436 -- which is referenced by a competence element as a rating level id.
437 -- - Checks the rating scale is not referenced by a rating level
439 --
440 -- Pre-conditions:
441 --
442 --
443 -- In Arguments:
444 -- p_rating_scale_id
445 -- p_object_version_number
446 --
447 -- Post Success:
448 -- Process continues if:
449 -- The rating scale is not referenced anywhere.
450 --
451 -- Post Failure:
452 -- An Application error is raised and processing is terminated if any of
453 -- the following cases are found:
454 -- - The rating scale is referenced by an assessment,
455 -- a performance rating,
456 -- a rating scale step which is
457 -- referenced by a competence element
458 --
459 --
460 procedure chk_rating_scale_delete
461 (p_rating_scale_id in per_rating_scales.rating_scale_id%TYPE
462 ,p_object_version_number in per_rating_scales.object_version_number%TYPE
463 ) is
464 --
465 l_proc varchar2(72) := g_package||' chk_rating_scale_delete';
466 l_exists varchar2(1);
467 --
468 -- Define a cursor to check if rating scale is referenced in
469 -- assessment types
470 --
471 cursor csr_get_rs_assessment_type is
472 select null
473 from per_assessment_types
474 where ( (rating_scale_id = p_rating_scale_id)
475 or(weighting_scale_id = p_rating_scale_id)
476 );
477 --
478 /* **** Not implemented yet ******
479 --
480 -- Define a cursor to get a performance rating which references a rating scale.
481 --
482 cursor csr_get_performance_rating is
483 select null
484 from per_performance_ratings
485 where rating_scale_id = p_rating_scale_id;
486 ******************************* */
487 --
488 -- Define a cursor to get a competence which is referenced by a rating_scale.
489 --
490 cursor csr_get_competence_rating is
491 select null
492 from per_competences
493 where rating_scale_id = p_rating_scale_id;
494 --
495 -- Define a cursor to to check id rating scales has any levels that
496 -- are referenced in competence element
497 --
498 cursor csr_get_rl_rating_level is --Bug fix 3732129
499 select null from per_rating_levels rle
500 where rle.rating_scale_id = p_rating_scale_id and
501 exists (select /*+ INDEX(pce)*/ null
502 from per_competence_elements pce
503 where (rle.rating_level_id= pce.rating_level_id)
504 or (rle.rating_level_id = pce.weighting_level_id) );
505 --
506 begin
507 hr_utility.set_location('Entering '||l_proc, 1);
508 --
509 -- Check a rating scale is not referenced by an assessment type
510 --
511 open csr_get_rs_assessment_type;
512 fetch csr_get_rs_assessment_type into l_exists;
513 if csr_get_rs_assessment_type%found
514 then close csr_get_rs_assessment_type;
515 hr_utility.set_location (l_proc, 2);
516 hr_utility.set_message (801, 'HR_51573_RSC_RSC_IN_AST');
517 hr_utility.raise_error;
518 end if;
519 close csr_get_rs_assessment_type;
520 --
521 /* *** Not impelemented yet **************
522 --
523 -- Check a rating scale is not referenced by a performance rating.
524 --
525 open csr_get_performance_rating;
526 fetch csr_get_performance_rating into l_exists;
527 if csr_get_performance_rating%found
528 then close csr_get_performance_rating;
529 hr_utility.set_location (l_proc, 3);
530 hr_utility.set_message (801, 'HR_<<create new message>>');
531 hr_utility.raise_error;
532 end if;
533 close csr_get_performance_rating;
534 **************************************** */
535 --
536 -- Check a rating scale is not referenced by a competence.
537 --
538 open csr_get_competence_rating;
539 fetch csr_get_competence_rating into l_exists;
540 if csr_get_competence_rating%found
541 then close csr_get_competence_rating;
542 hr_utility.set_location (l_proc, 4);
543 hr_utility.set_message (801, 'HR_51572_RSC_RSC_IN_CPN');
544 hr_utility.raise_error;
545 end if;
546 close csr_get_competence_rating;
547 --
548 -- Check a rating scale is not referenced by a rating LEVEL which is
549 -- referenced by a competence element.
550 --
551 open csr_get_rl_rating_level;
552 fetch csr_get_rl_rating_level into l_exists;
553 if csr_get_rl_rating_level%found
554 then close csr_get_rl_rating_level;
555 hr_utility.set_location (l_proc, 4);
556 hr_utility.set_message (801, 'HR_51574_RSC_IN_RTL_IN_ELE');
557 hr_utility.raise_error;
558 end if;
559 close csr_get_rl_rating_level;
560 --
561 hr_utility.set_location ('Leaving '||l_proc, 5);
562 --
563 end chk_rating_scale_delete;
564 --
565 -- -----------------------------------------------------------------------
566 -- |------------------------------< chk_df >-----------------------------|
570 -- Validates the all Descriptive Flexfield values.
567 -- -----------------------------------------------------------------------
568 --
569 -- Description:
571 --
572 -- Pre-conditions:
573 -- All other columns have been validated. Must be called as the
574 -- last step from insert_validate and update_validate.
575 --
576 -- In Arguments:
577 -- p_rec
578 --
579 -- Post Success:
580 -- If the Descriptive Flexfield structure column and data values are
581 -- all valid this procedure will end normally and processing will
582 -- continue.
583 --
584 -- Post Failure:
585 -- If the Descriptive Flexfield structure column value or any of
586 -- the data values are invalid then an application error is raised as
587 -- a PL/SQL exception.
588 --
589 -- Access Status:
590 -- Internal Row Handler Use Only.
591 --
592 procedure chk_df
593 (p_rec in per_rsc_shd.g_rec_type) is
594 --
595 l_proc varchar2(72) := g_package||'chk_df';
596 --
597 begin
598 hr_utility.set_location('Entering:'||l_proc, 10);
599 --
600 if (((p_rec.rating_scale_id is not null) and (
601 nvl(per_rsc_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
602 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
603 nvl(per_rsc_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
604 nvl(p_rec.attribute1, hr_api.g_varchar2) or
605 nvl(per_rsc_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
606 nvl(p_rec.attribute2, hr_api.g_varchar2) or
607 nvl(per_rsc_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
608 nvl(p_rec.attribute3, hr_api.g_varchar2) or
609 nvl(per_rsc_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
610 nvl(p_rec.attribute4, hr_api.g_varchar2) or
611 nvl(per_rsc_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
612 nvl(p_rec.attribute5, hr_api.g_varchar2) or
613 nvl(per_rsc_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
614 nvl(p_rec.attribute6, hr_api.g_varchar2) or
615 nvl(per_rsc_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
616 nvl(p_rec.attribute7, hr_api.g_varchar2) or
617 nvl(per_rsc_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
618 nvl(p_rec.attribute8, hr_api.g_varchar2) or
619 nvl(per_rsc_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
620 nvl(p_rec.attribute9, hr_api.g_varchar2) or
621 nvl(per_rsc_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
622 nvl(p_rec.attribute10, hr_api.g_varchar2) or
623 nvl(per_rsc_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
624 nvl(p_rec.attribute11, hr_api.g_varchar2) or
625 nvl(per_rsc_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
626 nvl(p_rec.attribute12, hr_api.g_varchar2) or
627 nvl(per_rsc_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
628 nvl(p_rec.attribute13, hr_api.g_varchar2) or
629 nvl(per_rsc_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
630 nvl(p_rec.attribute14, hr_api.g_varchar2) or
631 nvl(per_rsc_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
632 nvl(p_rec.attribute15, hr_api.g_varchar2) or
633 nvl(per_rsc_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
634 nvl(p_rec.attribute16, hr_api.g_varchar2) or
635 nvl(per_rsc_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
636 nvl(p_rec.attribute17, hr_api.g_varchar2) or
637 nvl(per_rsc_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
638 nvl(p_rec.attribute18, hr_api.g_varchar2) or
639 nvl(per_rsc_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
640 nvl(p_rec.attribute19, hr_api.g_varchar2) or
641 nvl(per_rsc_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
642 nvl(p_rec.attribute20, hr_api.g_varchar2)))
643 or
644 (p_rec.rating_scale_id is null))
645 and hr_rating_scales_api.g_ignore_df <> 'Y' then -- BUG3621261
646 --
647 -- Only execute the validation if absolutely necessary:
648 -- a) During update, the structure column value or any
649 -- of the attribute values have actually changed.
650 -- b) During insert.
651 --
652 hr_dflex_utility.ins_or_upd_descflex_attribs
653 (p_appl_short_name => 'PER'
654 ,p_descflex_name => 'PER_RATING_SCALES'
655 ,p_attribute_category => p_rec.attribute_category
656 ,p_attribute1_name => 'ATTRIBUTE1'
657 ,p_attribute1_value => p_rec.attribute1
658 ,p_attribute2_name => 'ATTRIBUTE2'
659 ,p_attribute2_value => p_rec.attribute2
660 ,p_attribute3_name => 'ATTRIBUTE3'
661 ,p_attribute3_value => p_rec.attribute3
662 ,p_attribute4_name => 'ATTRIBUTE4'
663 ,p_attribute4_value => p_rec.attribute4
664 ,p_attribute5_name => 'ATTRIBUTE5'
665 ,p_attribute5_value => p_rec.attribute5
666 ,p_attribute6_name => 'ATTRIBUTE6'
667 ,p_attribute6_value => p_rec.attribute6
668 ,p_attribute7_name => 'ATTRIBUTE7'
669 ,p_attribute7_value => p_rec.attribute7
670 ,p_attribute8_name => 'ATTRIBUTE8'
671 ,p_attribute8_value => p_rec.attribute8
672 ,p_attribute9_name => 'ATTRIBUTE9'
673 ,p_attribute9_value => p_rec.attribute9
674 ,p_attribute10_name => 'ATTRIBUTE10'
675 ,p_attribute10_value => p_rec.attribute10
676 ,p_attribute11_name => 'ATTRIBUTE11'
677 ,p_attribute11_value => p_rec.attribute11
678 ,p_attribute12_name => 'ATTRIBUTE12'
679 ,p_attribute12_value => p_rec.attribute12
683 ,p_attribute14_value => p_rec.attribute14
680 ,p_attribute13_name => 'ATTRIBUTE13'
681 ,p_attribute13_value => p_rec.attribute13
682 ,p_attribute14_name => 'ATTRIBUTE14'
684 ,p_attribute15_name => 'ATTRIBUTE15'
685 ,p_attribute15_value => p_rec.attribute15
686 ,p_attribute16_name => 'ATTRIBUTE16'
687 ,p_attribute16_value => p_rec.attribute16
688 ,p_attribute17_name => 'ATTRIBUTE17'
689 ,p_attribute17_value => p_rec.attribute17
690 ,p_attribute18_name => 'ATTRIBUTE18'
691 ,p_attribute18_value => p_rec.attribute18
692 ,p_attribute19_name => 'ATTRIBUTE19'
693 ,p_attribute19_value => p_rec.attribute19
694 ,p_attribute20_name => 'ATTRIBUTE20'
695 ,p_attribute20_value => p_rec.attribute20
696 );
697 end if;
698 --
699 hr_utility.set_location(' Leaving:'||l_proc, 20);
700 --
701 end chk_df;
702 --
703 -- ----------------------------------------------------------------------------
704 -- |---------------------------< insert_validate >----------------------------|
705 -- ----------------------------------------------------------------------------
706 Procedure insert_validate(p_rec in per_rsc_shd.g_rec_type
707 ,p_effective_date in date) is
708 --
709 l_proc varchar2(72) := g_package||'insert_validate';
710 --
711 Begin
712 hr_utility.set_location('Entering:'||l_proc, 5);
713 --
714 -- Call all supporting business operations. Mapping to the
715 -- appropriate Business Rules in perrsc.bru is provided.
716 --
717 -- Validate business_group_id
718 --
719 -- Business Rule Mapping
720 -- =====================
721 -- Rule CHK_BUSINESS_GROUP_ID a
722 -- ngundura changes done for pa requirements
723 if p_rec.business_group_id is not null then
724 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
725 end if;
726 -- ngundura end of changes
727 --
728 hr_utility.set_location(' Leaving:'||l_proc, 10);
729 --
730 --
731 -- Validate name
732 --
733 -- Business Rule Mapping
734 -- =====================
735 -- Rule CHK_NAME a,b
736 --
737 -- pmfletch. Now moved to TL row handler
738 --
739 -- per_rsc_bus.chk_name
740 -- (p_rating_scale_id => p_rec.rating_scale_id
741 -- ,p_business_group_id => p_rec.business_group_id
742 -- ,p_name => p_rec.name
743 -- ,p_object_version_number => p_rec.object_version_number
744 -- );
745 --
746 hr_utility.set_location (l_proc, 15);
747 --
748 -- Validate type
749 --
750 -- Business Rule Mapping
751 -- =====================
752 -- Rule CHK_TYPE a
753 --
754 per_rsc_bus.chk_type
755 (p_rating_scale_id => p_rec.rating_scale_id
756 ,p_type => p_rec.type
757 ,p_effective_date => p_effective_date
758 ,p_object_version_number => p_rec.object_version_number
759 );
760 --
761 hr_utility.set_location (l_proc, 20);
762 --
763 -- Validate default flag
764 --
765 -- Business Rule Mapping
766 -- =====================
767 -- Rule CHK_DEFAULT_FLAG
768 --
769 per_rsc_bus.chk_default_flag
770 (p_rating_scale_id => p_rec.rating_scale_id
771 ,p_business_group_id => p_rec.business_group_id
772 ,p_type => p_rec.type
773 ,p_default_flag => p_rec.default_flag
774 ,p_effective_date => p_effective_date
775 ,p_object_version_number => p_rec.object_version_number
776 );
777 --
778 -- call descriptive flexfield validation routines
779 --
780 per_rsc_bus.chk_df(p_rec => p_rec);
781 --
782 hr_utility.set_location (l_proc, 25);
783 --
784 hr_utility.set_location ('Leaving '||l_proc, 30);
785 End insert_validate;
786 --
787 -- ----------------------------------------------------------------------------
788 -- |---------------------------< update_validate >----------------------------|
789 -- ----------------------------------------------------------------------------
790 Procedure update_validate(p_rec in per_rsc_shd.g_rec_type
791 ,p_effective_date in date) is
792 --
793 l_proc varchar2(72) := g_package||'update_validate';
794 --
795 Begin
796 hr_utility.set_location('Entering:'||l_proc, 5);
797 --
798 -- Call all supporting business operations. Mapping to the
799 -- appropriate Business Rules in perrsc.bru is provided.
800 --
801 --
802 -- Check those columns which cannot be updated have not changed.
803 --
804 -- Business Rule Mapping
805 -- =====================
806 -- CHK_BUSINESS_GROUP_ID b
807 -- ngundura changes as per pa changes
808 if p_rec.business_group_id is not null then
809 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
810 end if;
811 --
812 -- CHK_TYPE b
813 --
814 per_rsc_bus.check_non_updateable_args
815 (p_rec => p_rec);
816 --
817 hr_utility.set_location (l_proc, 10);
818 --
822 --
819 -- Business Rule Mapping
820 -- =====================
821 -- Rule CHK_NAME a,b
823 -- pmfletch. Now moved to TL row handler
824 --
825 -- per_rsc_bus.chk_name
826 -- (p_rating_scale_id => p_rec.rating_scale_id
827 -- ,p_business_group_id => p_rec.business_group_id
828 -- ,p_name => p_rec.name
829 -- ,p_object_version_number => p_rec.object_version_number
830 -- );
831 --
832 -- Validate default flag
833 --
834 -- Business Rule Mapping
835 -- =====================
836 -- Rule CHK_DEFAULT_FLAG
837 --
838 per_rsc_bus.chk_default_flag
839 (p_rating_scale_id => p_rec.rating_scale_id
840 ,p_business_group_id => p_rec.business_group_id
841 ,p_type => p_rec.type
842 ,p_default_flag => p_rec.default_flag
843 ,p_effective_date => p_effective_date
844 ,p_object_version_number => p_rec.object_version_number
845 );
846 --
847 -- call descriptive flexfield validation routines
848 --
849 -- call descriptive flexfield validation routines
850 --
851 per_rsc_bus.chk_df(p_rec => p_rec);
852 --
853 --
854 hr_utility.set_location (l_proc, 15);
855 --
856 hr_utility.set_location(' Leaving:'||l_proc, 20);
857 End update_validate;
858 --
859 -- ----------------------------------------------------------------------------
860 -- |---------------------------< delete_validate >----------------------------|
861 -- ----------------------------------------------------------------------------
862 Procedure delete_validate(p_rec in per_rsc_shd.g_rec_type) is
863 --
864 l_proc varchar2(72) := g_package||'delete_validate';
865 --
866 Begin
867 hr_utility.set_location('Entering:'||l_proc, 5);
868 --
869 -- Call all supporting business operations
870 --
871 -- Validate delete
872 --
873 -- Business Rule Mapping
874 -- =====================
875 --
876 -- Rule CHK_RATING_SCALE_DELETE a,b,c
877 --
878 per_rsc_bus.chk_rating_scale_delete
879 (p_rating_scale_id => p_rec.rating_scale_id
880 ,p_object_version_number => p_rec.object_version_number
881 );
882 --
883 hr_utility.set_location(' Leaving:'||l_proc, 10);
884 End delete_validate;
885 --
886 --
887 -- ----------------------------------------------------------------------------
888 -- |-----------------------< return_legislation_code >-------------------------|
889 -- ----------------------------------------------------------------------------
890 Function return_legislation_code
891 ( p_rating_scale_id in number
892 ) return varchar2 is
893 --
894 -- Declare cursor
895 --
896 cursor csr_leg_code is
897 select legislation_code
898 from per_business_groups pbg,
899 per_rating_scales prs
900 where prs.rating_scale_id = p_rating_scale_id
901 and pbg.business_group_id = prs.business_group_id;
902
903 l_proc varchar2(72) := g_package||'return_legislation_code';
904 l_legislation_code varchar2(150);
905 l_business_group_flag varchar2(1);
906 --
907 Begin
908 hr_utility.set_location('Entering:'||l_proc, 5);
909 --
910 -- Ensure that all the mandatory parameters are not null
911 --
912 hr_api.mandatory_arg_error (p_api_name => l_proc,
913 p_argument => 'rating_scale_id',
914 p_argument_value => p_rating_scale_id );
915 -- ngundura changes done for pa requirements...
916 select 'Y' into l_business_group_flag
917 from per_rating_scales
918 where rating_scale_id = p_rating_scale_id
919 and business_group_id is null;
920 if l_business_group_flag = 'Y' then
921 return null;
922 end if;
923 -- ngundura end of changes
924 if nvl(g_rating_scale_id, hr_api.g_number) = p_rating_scale_id then
925 --
926 -- The legislation has already been found with a previous
927 -- call to this function. Just return the value in the global
928 -- variable.
929 --
930 l_legislation_code := g_legislation_code;
931 hr_utility.set_location(l_proc, 20);
932 else
933 --
934 -- The ID is different to the last call to this function
935 -- or this is the first call to this function.
936 --
937 open csr_leg_code;
938 fetch csr_leg_code into l_legislation_code;
939 if csr_leg_code%notfound then
940 close csr_leg_code;
941 --
942 -- The primary key is invalid therefore we must error out
943 --
944 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
945 hr_utility.raise_error;
946 end if;
947 --
948 close csr_leg_code;
949 g_rating_scale_id:= p_rating_scale_id;
950 g_legislation_code := l_legislation_code;
951 end if;
952 return l_legislation_code;
953 --
954 hr_utility.set_location(' Leaving:'||l_proc, 10);
955 --
956 End return_legislation_code;
957 --
958 --
959 end per_rsc_bus;