1 Package Body per_ast_bus as
2 /* $Header: peastrhi.pkb 120.7.12010000.2 2008/10/20 14:11:39 kgowripe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ast_bus.'; -- Global package name
9 --
10 --
11 -- The following two global variables are only to be used by the
12 -- return_legislation_code function.
13 --
14 g_assessment_type_id number default null;
15 g_legislation_code varchar2(150) default null;
16 --
17 -- ----------------------------------------------------------------------------
18 -- |-----------------------< chk_non_updateable_args >------------------------|
19 -- ----------------------------------------------------------------------------
20 --
21 -- Aside from the primary key, the only non updatable argument is the business
22 -- group. Certain other arguments : rating_scale_id, weighting_scale_id and
23 -- assessment_classification can only be updated if they are not being used
24 -- by an assessment.
25 --
26 Procedure chk_non_updateable_args(p_rec in per_ast_shd.g_rec_type) is
27 --
28 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
29 l_error exception;
30 l_argument varchar2(30);
31 --
32 Begin
33 --
34 hr_utility.set_location('Entering:'|| l_proc,5);
35 --
36 -- Only proceed with validation if a row exists for
37 -- the current record in the HR Schema.
38 --
39 if not per_ast_shd.api_updating
40 (p_assessment_type_id => p_rec.assessment_type_id
41 ,p_object_version_number => p_rec.object_version_number
42 ) then
43 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
44 hr_utility.set_message_token('PROCEDURE', l_proc);
45 hr_utility.set_message_token('STEP', '5');
46 end if;
47 --
48 hr_utility.set_location (l_proc, 6);
49 --
50 if p_rec.business_group_id <> per_ast_shd.g_old_rec.business_group_id then
51 l_argument := 'business_group_id';
52 raise l_error;
53 end if;
54 --
55 hr_utility.set_location(l_proc, 7);
56 --
57 --
58 exception
59 when l_error then
60 hr_api.argument_changed_error
61 (p_api_name => l_proc
62 ,p_argument => l_argument);
63 when others then
64 raise;
65 hr_utility.set_location(' Leaving : '|| l_proc, 9);
66 end chk_non_updateable_args;
67 -- ----------------------------------------------------------------------------
68 -- |-------------------------------< chk_name >-------------------------------|
69 -- ----------------------------------------------------------------------------
70 -- Description
71 -- A check to make sure that the name entered is unique within the business
72 -- group and that the field isn't NULL.
73 -- Pre-Conditions :
74 -- None
75 -- In Arguments :
76 -- name
77 -- business_group_id
78 -- Post success :
79 -- Processing continues if the name entered is unique within BG.
80 -- Post failure :
81 -- An application error is raised and processing is terminated if the
82 -- name is not unique within BG.
83 --
84 Procedure chk_name
85 (p_name in per_assessment_types.name%TYPE,
86 p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE,
87 p_type in per_assessment_types.type%TYPE,
88 p_business_group_id in per_assessment_types.business_group_id%TYPE,
89 p_object_version_number in per_assessment_types.object_version_number%TYPE
90 )
91 is
92 --
93 l_proc varchar2(72) := g_package||'chk_name';
94 l_name per_assessment_types.name%TYPE;
95 l_exists per_assessment_types.business_group_id%TYPE;
96 l_api_updating boolean;
97 --
98 -- Cursor to get rows which have duplicate names
99 --
100 cursor csr_name is
101 select business_group_id from per_assessment_types
102 where ((p_assessment_type_id is NULL)
103 or (p_assessment_type_id <> assessment_type_id))
104 and name = p_name -- there is a duplicate name.
105 and nvl(type , 'COMPETENCE') = nvl(p_type , 'COMPETENCE')
106 and p_business_group_id is null
107 union
108 select business_group_id from per_assessment_types
109 where ((p_assessment_type_id is NULL)
110 or (p_assessment_type_id <> assessment_type_id))
111 and name = p_name -- there is a duplicate name.
112 and nvl(type , 'COMPETENCE') = nvl(p_type , 'COMPETENCE')
113 and (business_group_id = p_business_group_id or business_group_id is null)
114 and p_business_group_id is not null;
115
116 begin
117 -- Bug#885806
118 -- dbms_output.put_line('chk_name := '||p_name);
119 hr_utility.trace('chk_name := '||p_name);
120 hr_utility.set_location('Entering:'|| l_proc, 1);
121 --
122 -- Check that the business_group_id is not null.
123 --
124
125 -- Only proceed with validation if:
126 -- a) The current g_old_rec is current and
127 -- b) The value for name has changed.
128 -- c) The value is being inserted.
129 --
130 l_api_updating := per_ast_shd.api_updating
131 (p_assessment_type_id => p_assessment_type_id
132 ,p_object_version_number => p_object_version_number
133 );
134 --
135 hr_utility.set_location (l_proc, 2);
136 --
137 if (l_api_updating AND
138 nvl(per_ast_shd.g_old_rec.name, hr_api.g_varchar2)
139 <> nvl(p_name, hr_api.g_varchar2)
140 or not l_api_updating)
141 then
142 --
143 hr_utility.set_location (l_proc, 3);
144 --
145 -- Check that the name isn't NULL
146 --
147 if p_name is NULL then
148 hr_utility.set_message(801,'HR_51498_AST_NAME_NULL');
149 hr_utility.raise_error;
150 end if;
151 --
152 -- Check that the name is unique within the business group.
153 --
154 open csr_name;
155 fetch csr_name into l_exists;
156 if csr_name%found then
157 -- The name already exists in the business group
158 close csr_name;
159 if l_exists is null then
160 if nvl(p_type,'COMPETENCE')='COMPETENCE' then
161 hr_utility.set_message(800,'HR_AST_NAME_IN_GLOB');
162 hr_utility.raise_error;
163 else
164 hr_utility.set_message(800,'HR_OBJ_NAME_IN_GLOB');
165 hr_utility.raise_error;
166 end if;
167 else
168 if nvl(p_type,'COMPETENCE')='COMPETENCE' then
169 hr_utility.set_message(800,'HR_51499_AST_NAME_NOT_UNIQ');
170 hr_utility.raise_error;
171 else
172 hr_utility.set_message(800,'HR_51499_OBJ_NAME_NOT_UNIQ');
173 hr_utility.raise_error;
174 end if;
175 end if;
176 end if;
177 --
178 hr_utility.set_location(' Leaving:' || l_proc,2);
179 --
180 close csr_name;
181 --
182 end if;
183 end chk_name;
184 -- ----------------------------------------------------------------------------
185 -- |------------------< chk_disp_assess_comments >---------------------|
186 -- ----------------------------------------------------------------------------
187 -- Description
188 -- Validate display_assessment_comments against the HR_LOOKUP where
189 -- lookup_type = 'YES_NO'.
190 --
191 -- Pre-conditions
192 -- None
193 --
194 -- In Arguments:
195 -- p_display_assessment_comments
196 -- p_assessment_type_id
197 -- p_effective_date
198 -- p_object_version_number
199 --
200 -- Post Success:
201 -- If display_assessment_comments is valid, processing continues.
202 -- Post Failure:
203 -- If the valid for comments displayed isn't in lookup 'YES_NO'.
204 -- Access Status:
205 -- Internal Table Handler Use Only.
206 --
207 Procedure chk_disp_assess_comments
208 (p_display_assessment_comments in per_assessment_types.display_assessment_comments%TYPE
209
210 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
211 ,p_effective_date in date
212 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
213 )
214 is
215 --
216 l_proc varchar2(72):=g_package||'chk_display_assessment_comments';
217 l_api_updating boolean;
218 --
219 begin
220 -- Bug#885806
221 -- dbms_output.put_line('Inside the chk_display_assessment_comments procedure');
222 hr_utility.trace('Inside the chk_display_assessment_comments procedure');
223 hr_utility.set_location('Entering:'|| l_proc, 1);
224 --
225 -- Check mandatory parameters have been set
226 --
227 hr_api.mandatory_arg_error
228 (p_api_name => l_proc
229 ,p_argument => 'effective_date'
230 ,p_argument_value => p_effective_date
231 );
232 --
233 -- Only proceed with the validation if :
234 -- a) The current g_old_rec is current
235 -- b) The value has changed.
236 -- c) A record is being inserted
237 --
238 l_api_updating := per_ast_shd.api_updating
239 (p_assessment_type_id => p_assessment_type_id
240 ,p_object_version_number => p_object_version_number
241 );
242 --
243 if ((l_api_updating and nvl(per_ast_shd.g_old_rec.display_assessment_comments,
244 hr_api.g_varchar2)
245 <> nvl(p_display_assessment_comments, hr_api.g_varchar2))
246 or
247 (NOT l_api_updating)) then
248 --
249 hr_utility.set_location(l_proc, 2);
250 --
251 -- Check the value in p_display_assessment_comments exists in hr_lookups
252 --
253 if hr_api.not_exists_in_hr_lookups
254 (p_effective_date => p_effective_date
255 ,p_lookup_type => 'YES_NO'
256 ,p_lookup_code => p_display_assessment_comments
257 ) then
258 hr_utility.set_location(l_proc, 10);
259 hr_utility.set_message(801,'HR_51500_AST_COMS_DISP_INVAL');
260 hr_utility.raise_error;
261 end if;
262 --
263 hr_utility.set_location(' Leaving:'|| l_proc, 15);
264 end if;
265 end chk_disp_assess_comments;
266 --
267 -- ----------------------------------------------------------------------------
268 -- |-----------------------------< chk_date_from_to>-------------------------|
269 -- ----------------------------------------------------------------------------
270 --
271 -- Description
272 -- Validate the date_from and date_to fields to make sure
273 -- 1) The date_to in on or after the date_from date
274 -- 2) Any assessments already created using the assessment_type are not
275 -- invalidated by changing the dates.
276 -- The date_from and the date_to can both (or either) be null.
277 --
278 -- Pre-conditions
279 -- None
280 --
281 -- In Arguments:
282 -- p_date_from
283 -- p_date_to
284 -- p_assessment_type_id
285 -- p_effective_date
286 -- p_object_version_number
287 --
288 -- Post Success:
289 -- If all test pass, processing continues.
290 -- Post Failure:
291 -- If the date_from is after the date_to, processing halts.
292 -- If a current assessment is invalidated, processing halts.
293 -- Access Status:
294 -- Internal Table Handler Use Only.
295 --
296 Procedure chk_date_from_to
297 (p_date_from in per_assessment_types.date_from%TYPE
298 ,p_date_to in per_assessment_types.date_to%TYPE
299 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
300 ,p_business_group_id in per_assessment_types.business_group_id%TYPE
301 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
302 )
303 is
304 --
305 l_proc varchar2(72):=g_package||'chk_date_from_to';
306 l_api_updating boolean;
307 --
308 -- Set up a cursor to get the minimum and maximum dates of assessments using
309 -- the assessment_type being checked
310 --
311 cursor csr_assessment_dates is
312 select min(assessment_date) , max(assessment_date)
313 from per_assessments ass
314 where p_assessment_type_id = ass.assessment_type_id
315 and (p_business_group_id = business_group_id or p_business_group_id is null) ;
316
317 --
318 l_min_assessment_date per_assessments.assessment_date%TYPE;
319 l_max_assessment_date per_assessments.assessment_date%TYPE;
320 --
321 begin
322 hr_utility.set_location('Entering:'|| l_proc, 1);
323 --
324 --
325 -- Only proceed with the validation if :
326 -- a) The current g_old_rec is current.
327 -- b) The date_from has changed.
328 -- c) The date_to has changed.
329 -- d) a record is being inserted.
330 --
331 l_api_updating := per_ast_shd.api_updating
332 (p_assessment_type_id => p_assessment_type_id
333 ,p_object_version_number => p_object_version_number
334 );
335 --
336 if (((l_api_updating and nvl(per_ast_shd.g_old_rec.date_from,
337 hr_api.g_date)
338 <> nvl(p_date_from, hr_api.g_date))
339 or
340 (l_api_updating and nvl(per_ast_shd.g_old_rec.date_to,
341 hr_api.g_date)
342 <> nvl(p_date_to, hr_api.g_date))
343 )
344 or
345 (NOT l_api_updating)) then
346 --
347 -- If the date_from is greater then the date_to, raise an error
348 --
349 if ((p_date_from is not null) and (p_date_to is not null)
350 and (p_date_from > p_date_to)) then
351 --
352 hr_utility.set_message(801,'HR_51859_AST_DATE_FROM_DATE_TO');
353 hr_utility.raise_error;
354 --
355 end if;
356 --
357 -- Make sure that any assessments using this assesment_type aren't
358 -- invalidated by changing the date.
359 --
360 open csr_assessment_dates;
361 fetch csr_assessment_dates into l_min_assessment_date, l_max_assessment_date;
362 close csr_assessment_dates;
363 --
364 if ((p_date_to is not null) and (l_max_assessment_date is not null)
365 and ( l_max_assessment_date > p_date_to)) then
366 --
367 -- Assessment exists which were created later than the type, so error
368 --
369 hr_utility.set_message(801,'HR_51860_AST_DATE_OUT_RANGE1');
370 hr_utility.raise_error;
371 --
372 end if;
373 --
374 if ((p_date_from is not null) and (l_min_assessment_date is not null)
375 and (l_min_assessment_date < p_date_from)) then
376 --
377 -- Assessment(s) exists which were created before the type, so error
378 --
379 hr_utility.set_message(801,'HR_51861_AST_DATE_OUT_RANGE2');
380 hr_utility.raise_error;
381 --
382 end if;
383 --
384 end if;
385 --
386 hr_utility.set_location(' Leaving:'|| l_proc, 15);
387 --
388 end chk_date_from_to;
389 --
390 -- ----------------------------------------------------------------------------
391 -- |----------------------< chk_assessment_classification >-------------------|
392 -- ----------------------------------------------------------------------------
393 --
394 -- Description
395 -- Validate assessment_classification against lookup_type of
396 -- 'ASSESSMENT_CLASSIFICATION'. Valid values can be one of three :
397 -- PERFORMANCE
398 -- PROFICIENCY
399 -- BOTH
400 -- The assessment_classification can only be updated if the assessment
401 -- type isn't being used by an assessment.
402 --
403 -- Pre-conditions
404 -- None
405 --
406 -- In Arguments:
407 -- p_assessment_classification
408 -- p_effective_date
409 --
410 -- Post Success:
411 -- If assessment_classification is valid, processing continues.
412 -- Post Failure:
413 -- Processing fails
414 -- Access Status:
415 -- Internal Table Handler Use Only.
416 --
417 Procedure chk_assessment_classification
418 (p_assessment_classification in per_assessment_types.assessment_classification%TYPE
419 ,p_business_group_id in per_assessment_types.business_group_id%TYPE
420 ,p_effective_date in date
421 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
422 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
423 )
424 is
425 --
426 -- Define the cursor to check if the assessment_type is used in an assessment
427 --
428 cursor csr_assessment_type_usage is
429 select null
430 from per_assessments
431 where assessment_type_id = p_assessment_type_id
432 and (p_business_group_id = business_group_id or p_business_group_id is null) ;
433
434 --
435 l_proc varchar2(72):=g_package||'chk_assessment_classification';
436 l_exists varchar2(1);
437 l_api_updating boolean;
438 --
439 begin
440 --
441 hr_utility.set_location('Entering:'|| l_proc, 1);
442 --
443 -- Check mandatory parameters have been set
444 --
445 hr_api.mandatory_arg_error
446 (p_api_name => l_proc
447 ,p_argument => 'effective_date'
448 ,p_argument_value => p_effective_date
449 );
450 --
451 -- Bug#885806
452 -- dbms_output.put_line('About to check the assessment_classification');
453 hr_utility.trace('About to check the assessment_classification');
454 --
455 -- If record is being updated, and the assessment_classification is being
456 -- changed check to see whether the assessment_type is being used or not by an
457 -- assessment. If it is, then the assessment_classification cannot be
458 -- updated.
459 --
460 l_api_updating := per_ast_shd.api_updating
461 (p_assessment_type_id => p_assessment_type_id
462 ,p_object_version_number => p_object_version_number
463 );
464 --
465 --
466 --
467 if (l_api_updating and nvl(per_ast_shd.g_old_rec.assessment_classification,
468 hr_api.g_varchar2)
469 <> nvl(p_assessment_classification, hr_api.g_varchar2)) then
470 --
471 -- Assessment Classification is being updated
472 -- Bug#885806
473 -- dbms_output.put_line('The assessment_classification is being updated');
474 hr_utility.trace('The assessment_classification is being updated');
475 --
476 open csr_assessment_type_usage;
477 fetch csr_assessment_type_usage into l_exists;
478 if csr_assessment_type_usage%found then
479 --
480 -- The assessment_type is being used so the asssessment_classification
481 -- can't be updated
482 --
483 hr_utility.set_message(801,'HR_51578_AST_USED_NO_UP_AS_CL');
484 hr_utility.raise_error;
485 end if;
486 end if;
487 --
488 --
489 -- Check that the value in p_assessment_classifications exist in hr_lookups.
490 --
491 if(p_assessment_classification <> 'FASTFORMULA') then
492 if hr_api.not_exists_in_hr_lookups
493 (p_effective_date => p_effective_date
494 ,p_lookup_type => 'ASSESSMENT_CLASSIFICATION'
495 ,p_lookup_code => p_assessment_classification
496 ) then
497 hr_utility.set_location(l_proc, 10);
498 hr_utility.set_message(801,'HR_51502_AST_ASS_CLASS_INVAL');
499 hr_utility.raise_error;
500 end if;
501 end if;
502 --
503 hr_utility.set_location(' Leaving:'|| l_proc, 15);
504 end chk_assessment_classification;
505 --
506 --
507 -- |---------------------------------------------------------------------------
508 -- |-------------------------< chk_weighting_scale_id >-----------------------|
509 -- ----------------------------------------------------------------------------
510 --
511 -- Description
512 -- WEIGHTING_SCALE_ID is a foreign key to PER_RATING_SCALES(RATING_SCALE_ID),
513 -- therefore values entered for this column must exist in the referenced table
514 -- where PER_RATING_SCALES(TYPE) = 'WEIGHTING' and the business group is the
515 -- same.
516 --
517 -- The weighting_scale_id can only be updated if it the assessment_type is
518 -- not being used by an assessment.
519 -- Pre-conditions
520 -- business_group_id is valid
521 -- assessment_classification is valid
522 -- In Arguments:
523 -- p_weighting_scale
524 -- p_business_group_id
525 -- p_assessment_classification
526 -- Post Success:
527 -- Processing continues.
528 -- Post Failure:
529 -- Processing terminates
530 -- Access Status:
531 -- Internal table handler use only.
532 --
533 Procedure chk_weighting_scale_id
534 (p_weighting_scale_id in per_assessment_types.weighting_scale_id%TYPE
535 ,p_business_group_id in per_assessment_types.business_group_id%TYPE
536 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
537 ,p_assessment_classification in per_assessment_types.assessment_classification%TYPE
538 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
539 )
540 is
541 --
542 -- Define the cursor the get the type and bg from per_rating_scales
543 -- ngundura changes for pa requirements.
544 cursor csr_weighting_scale_id is
545 select type,business_group_id
546 from per_rating_scales
547 where p_weighting_scale_id = rating_scale_id
548 and (nvl(p_business_group_id,-1) = nvl(business_group_id,-1) or business_group_id is null);
549
550
551 -- ngundura end of changes
552 -- Define the cursor to check if the assessment_type is used in an assessment
553 --
554 cursor csr_assessment_type_usage is
555 select null
556 from per_assessments
557 where assessment_type_id = p_assessment_type_id
558 and ( business_group_id = p_business_group_id or p_business_group_id is null) ;
559 --
560 -- Define the local variables for the cursor to go
561 --
562 l_exists varchar2(1);
563 l_api_updating boolean;
564 --
565 -- Define the local variables for the cursor to use
566 --
567 l_type per_rating_scales.type%TYPE;
568 l_business_group_id per_rating_scales.business_group_id%TYPE;
569 l_proc varchar2(72):=g_package||'chk_weighting_scale_id';
570 l_csr_not_found boolean := FALSE;
571 --
572 begin
573 hr_utility.set_location('Entering:'||l_proc,1);
574
575 -- Check that the assessment_classification is not NULL
576 --
577 hr_api.mandatory_arg_error
578 (p_api_name => l_proc
579 ,p_argument => 'assessment_classification'
580 ,p_argument_value => p_assessment_classification
581 );
582 --
583 --
584 -- If record is being updated, and the weighting_scale_id is being changed
585 -- check to see whether the assessment_type is being used or not by an
586 -- assessment. If it is, then the weighting_scale_id cannot be updated.
587 -- Even if the value hasn't changed for the weighting_scale_id, the validation
588 -- still takes place as compatibility with other columns is checked
589 --
590 l_api_updating := per_ast_shd.api_updating
591 (p_assessment_type_id => p_assessment_type_id
592 ,p_object_version_number => p_object_version_number
593 );
594 --
595 if (l_api_updating and nvl(per_ast_shd.g_old_rec.weighting_scale_id,
596 hr_api.g_number)
597 <> nvl(p_weighting_scale_id, hr_api.g_number)) then
598 --
599 -- Weighting scale id is being updated, so check if it is being used
600 --
601 open csr_assessment_type_usage;
602 fetch csr_assessment_type_usage into l_exists;
603 if csr_assessment_type_usage%found then
604 --
605 -- The assessment_type being used so the weighting_scale can't be updated
606 --
607 hr_utility.set_message(801,'HR_51577_AST_USED_NO_UP_WE');
608 hr_utility.raise_error;
609 end if;
610 end if;
611 --
612 -- Check if a value for weighting_scale_id exists(as it can be NULL)
613 -- Bug#885806
614 -- dbms_output.put_line('About to check if the weighting_scale_id exists');
615 hr_utility.trace('About to check if the weighting_scale_id exists');
616 --
617 if p_weighting_scale_id is not null then
618 --
619 -- Bug#885806
620 -- dbms_output.put_line('It exists');
621 hr_utility.trace('It exists');
622 hr_utility.set_location (l_proc,10);
623 open csr_weighting_scale_id;
624 fetch csr_weighting_scale_id into l_type,l_business_group_id;
625 --
626 --
627 -- Check that the weighting_scale_id exists.
628 --
629 if csr_weighting_scale_id%notfound then
630 l_csr_not_found := TRUE;
631 end if;
632 --
633 close csr_weighting_scale_id;
634 --
635 if l_csr_not_found = TRUE then
636 -- Bug#885806
637 -- dbms_output.put_line('and weighting_scale_id doesnt exist in other tab');
638 hr_utility.trace('and weighting_scale_id doesnt exist in other tab');
639 hr_utility.set_location(l_proc,20);
640 per_ast_shd.constraint_error
641 (p_constraint_name => 'PER_ASSESSMENT_TYPES_FK3');
642 --
643 --
644 -- Check if the weighting_scale_id is of the wrong type
645 -- ie. if the type of the weighing_scale in per_rating_scales is not equal
646 -- to 'WEIGHTING' then error.
647 --
648 elsif l_type <> 'WEIGHTING' then
649 hr_utility.set_location(l_proc,30);
650 --
651 -- The same error that is displayed when the value doesn't exist in the
652 -- table per_rating_scales can be used again, because as far as the api
653 -- is concerned the value doesn't exist even if it does but is of a
654 -- different type.
655 --
656 per_ast_shd.constraint_error
657 (p_constraint_name => 'PER_ASSESSMENT_TYPES_FK3');
658 --
659 end if;
660 --
661 end if;
662 end chk_weighting_scale_id;
663 --
664 --
665 -- ----------------------------------------------------------------------------
666 -- |------------------------< chk_weighting_scale_comment >-------------------|
667 -- ----------------------------------------------------------------------------
668 -- Description
669 -- Check that weighting_scale_comment is null if weighting_scale_id is null.
670 -- Pre-condition
671 -- weighing_scale_id is valid
672 -- business_group_id is valid
673 -- In Arguments
674 -- p_weighting_scale_comment
675 -- p_weighting_scale_id
676 -- Post Success
677 -- If weighting_scale_comment is valid, processing continues.
678 -- Post Failure:
679 -- Processing terminates
680 -- Access Status:
681 -- Internal table handler use only
682 --
683 Procedure chk_weighting_scale_comment
684 (p_weighting_scale_comment in per_assessment_types.weighting_scale_comment%TYPE
685
686 ,p_weighting_scale_id in per_assessment_types.weighting_scale_id%TYPE
687 )
688 is
689 l_proc varchar2(72):=g_package||'chk_weighting_scale_comment';
690 begin
691 -- Bug#885806
692 -- dbms_output.put_line('AST_weighting_scale_comment = '||p_weighting_scale_comment);
693 hr_utility.trace('AST_weighting_scale_comment = '||p_weighting_scale_comment);
694
695 hr_utility.set_location(l_proc, 1);
696 --
697 -- If p_weighting_scale_id is NULL but p_weighting_scale_comment isn't, error.
698 --
699 if ((p_weighting_scale_id is NULL) and
700 (p_weighting_scale_comment is not NULL)) then
701 --
702 -- comments exists without a weighting scale.
703 --
704 hr_utility.set_location(l_proc, 10);
705 hr_utility.set_message(801,'HR_51505_AST_W_ID_COMS_NOTNUL');
706 hr_utility.raise_error;
707 end if;
708 end chk_weighting_scale_comment;
709 -- ----------------------------------------------------------------------------
710 -- |---------------------------< chk_rating_scale_id >------------------------|
711 -- ----------------------------------------------------------------------------
712 --
713 -- Description
714 -- RATING_SCALE_ID is a foreign key to PER_RATING_SCALES(RATING_SCALE_ID),
715 -- therefore values entered for this column must exist in the referenced
716 -- table where PER_RATING_SCALES(TYPE)= 'PERFORMANCE'.
717 -- Also if rating_scale_id exists, assessment_classification must be of type
718 -- 'PERFORMANCE' or 'BOTH' and not of type 'PROFICIENCY'.
719 -- Pre-conditions
720 -- business_group_id is valid
721 -- assessment_classification is valid
722 -- In Arguments:
723 -- p_rating_scale_id
724 -- p_business_group_id
725 -- p_assessment_type_id
726 -- p_assessment_classification
727 -- p_object_version_number
728 --
729 -- Post Success:
730 -- Post Failure:
731 -- Processing fails
732 -- Access Status:
736 (p_rating_scale_id in per_assessment_types.rating_scale_id%TYPE
733 -- Internal Table Handler Use Only.
734 --
735 Procedure chk_rating_scale_id
737 ,p_business_group_id in per_assessment_types.business_group_id%TYPE
738 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
739 ,p_assessment_classification in per_assessment_types.assessment_classification%TYPE
740 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
741 ,p_weighting_classification in per_assessment_types.weighting_classification%TYPE
742 )
743 is
744 -- Define the cursor to get the info. from per_rating_scales
745 -- ngundura changes done for pa requirements
746 cursor csr_rating_scale_id is
747 select type
748 from per_rating_scales
749 where p_rating_scale_id = rating_scale_id
750 and (nvl(p_business_group_id,-1) = nvl(business_group_id,-1) or business_group_id is null) ;
751
752 -- ngundura end of changes
753 -- Define the cursor to check if the assessment_type is used in an assessment
754 --
755 cursor csr_assessment_type_usage is
756 select null
757 from per_assessments
758 where assessment_type_id = p_assessment_type_id
759 and business_group_id = p_business_group_id;
760 --
761 -- Define the local variables for the cursor to go
762 --
763 l_type per_rating_scales.type%TYPE;
764 l_proc varchar2(72):=g_package||'chk_rating_scale_id';
765 l_csr_not_found boolean := FALSE;
766 l_exists varchar2(1);
767 l_api_updating boolean;
768 --
769 --
770 begin
771 -- Bug#885806
772 -- dbms_output.put_line('AST_rating_scale_id = '||p_rating_scale_id);
773 hr_utility.trace('AST_rating_scale_id = '||p_rating_scale_id);
774 hr_utility.set_location('Entering:'|| l_proc, 1);
775 --
776
777 -- Check that assessment_classification is not null.
778 --
779
780 hr_api.mandatory_arg_error
781 (p_api_name => l_proc
782 ,p_argument => 'assessment_classification'
783 ,p_argument_value => p_assessment_classification
784 );
785 --
786 -- If record is being updated, and the rating_scale_id is being changed
787 -- check to see whether the assessment_type is being used or not by an
788 -- assessment. If it is, then the rating_scale_id cannot be updated.
789 -- Even if the value hasn't changed for the rating_scale_id, the validation
790 -- still takes place as compatibility with other columns is checked
791 --
792 l_api_updating := per_ast_shd.api_updating
793 (p_assessment_type_id => p_assessment_type_id
794 ,p_object_version_number => p_object_version_number
795 );
796 --
797 if (l_api_updating and nvl(per_ast_shd.g_old_rec.rating_scale_id,
798 hr_api.g_number)
799 <> nvl(p_rating_scale_id, hr_api.g_number)) then
800 --
801 -- Rating scale id is being updated, so check if it is being used
802 --
803 open csr_assessment_type_usage;
804 fetch csr_assessment_type_usage into l_exists;
805 if csr_assessment_type_usage%found then
806 --
807 -- The assessment_type is being used so the rating_scale can't be updated
808 --
809 hr_utility.set_message(801,'HR_51576_AST_USED_NO_UP_RA');
810 hr_utility.raise_error;
811 end if;
812 end if;
813
814 --
815
816 if (p_rating_scale_id IS NOT NULL) then
817 -- Bug#885806
818 --dbms_output.put_line('chk_rating_scale_id:Rating scale ID isnt NULL');
819 hr_utility.trace('chk_rating_scale_id:Rating scale ID isnt NULL');
820 hr_utility.set_location(l_proc,5);
821 --
822 -- If rating_scale_id is NOT NULL, assessment_classification must be
823 -- either PERFORMANCE or BOTH, otherwise raise an error.
824 --
825 if p_assessment_classification not in ('PERFORMANCE','BOTH','FASTFORMULA') then
826 -- Bug#885806
827 -- dbms_output.put_line('chk_rating_scale_id:AssessClass not in Per or Bo');
828 hr_utility.trace('chk_rating_scale_id:AssessClass not in Per or Bo or FF');
829 hr_utility.set_location(l_proc,10);
830 per_ast_shd.constraint_error
831 (p_constraint_name => 'PER_ASS_TYPES_RATE_NOTNULL_CHK');
832 --
833 end if;
834 --
835 hr_utility.set_location(l_proc, 15);
836 open csr_rating_scale_id ;
837 fetch csr_rating_scale_id into l_type;
838 --
839 -- Check if the rating_scale_id exists.
840 --
841 if csr_rating_scale_id%notfound then
842 l_csr_not_found := TRUE;
843 end if;
844 --
845 -- Close the cursor as it isn't needed anymore
846 --
847 close csr_rating_scale_id;
848 --
849 if l_csr_not_found = TRUE then
850 -- Bug#885806
851 -- dbms_output.put_line('chk_rating_scale_id:Rating scale not found');
852 hr_utility.trace('chk_rating_scale_id:Rating scale not found');
853 hr_utility.set_location(l_proc,20);
854 per_ast_shd.constraint_error
855 (p_constraint_name => 'PER_ASSESSMENT_TYPES_FK2');
856 --
857 -- Check if the rating_scale_id is of the wrong type.
858 -- ie if the type of the rating_scale in per_rating_scales
859 -- is not equal to PERFORMANCE, then error.
860 --
861 elsif l_type <> 'PERFORMANCE' then
862 -- Bug#885806
863 --dbms_output.put_line('chk_rating_scale_id:Rating scale found, type diff');
864 hr_utility.trace('chk_rating_scale_id:Rating scale found, type diff');
868 hr_utility.set_location(l_proc,30);
865 -- dbms_output.put_line('AST_rating_scale_type = '||l_type);
866 hr_utility.trace('AST_rating_scale_type = '||l_type);
867 --
869 --
870 -- The same error message that gets used when the value can be found
871 -- in the table per_rating_scales can be used here as even thought the
872 -- value entered exists it is of the wrong type therefore it may as well
873 -- not exist.
874 --
875
876 per_ast_shd.constraint_error
877 (p_constraint_name => 'PER_ASSESSMENT_TYPES_FK2');
878
879 --
880 end if;
881 --
882 -- RATING_SCALE_ID must be NULL so check that assessment_classification
883 -- is 'PROFICIENCY'
884 --
885 elsif (p_assessment_classification <> 'PROFICIENCY') then
886 -- Bug#885806
887 --dbms_output.put_line('chk_rating_scale_id:Rating scale NULL, type not Pro');
888 hr_utility.trace('chk_rating_scale_id:Rating scale ID is NULL. assessment classif is '||p_assessment_classification);
889 if((p_assessment_classification <> 'FASTFORMULA')) then
890 hr_utility.trace('chk_rating_scale_id:Rating scale NULL, type not FastFormula');
891
892
893 per_ast_shd.constraint_error
894 (p_constraint_name => 'PER_ASS_TYPES_RATE_NULL_CHK');
895 end if;
896 end if;
897 --
898 end chk_rating_scale_id;
899 -- ----------------------------------------------------------------------------
900 -- |------------------------< chk_rating_scale_comment >-------------------|
901 -- ----------------------------------------------------------------------------
902 -- Description
903 -- Check that rating_scale_comment is null if rating_scale_id is null.
904 -- Pre-condition
905 -- rating_scale_id is valid
906 -- business_group_id is valid
907 -- In Arguments
908 -- p_rating_scale_comment
909 -- p_rating_scale_id
910 -- Post Success
911 -- If rating_scale_comment is valid, processing continues.
912 -- Post Failure:
913 -- Processing terminates
914 -- Access Status:
915 -- Internal table handler use only
916 --
917 Procedure chk_rating_scale_comment
918 (p_rating_scale_comment in per_assessment_types.rating_scale_comment%TYPE
919 ,p_rating_scale_id in per_assessment_types.rating_scale_id%TYPE
920 )
921 is
922 l_proc varchar2(72):=g_package||'chk_rating_scale_comment';
923 begin
924 hr_utility.set_location(l_proc, 1);
925 --
926 -- If p_rating_scale_id is NULL but p_rating_scale_comment isn't, error.
927 --
928 if ((p_rating_scale_id is NULL) and
929 (p_rating_scale_comment is not NULL)) then
930 --
931 -- comments exists without a rating scale.
932 --
933 hr_utility.set_location(l_proc, 10);
934 hr_utility.set_message(801,'HR_51509_AST_R_ID_COMS_NOTNUL');
935 end if;
936 end chk_rating_scale_comment;
937 -- ----------------------------------------------------------------------------
938 -- |--------------------< chk_weighting_classification >----------------------|
939 -- ----------------------------------------------------------------------------
940 -- Description
941 -- WEIGHTING_CLASSIFICATION can be one of two values :
942 -- PERFORMANCE
943 -- PROFICIENCY
944 -- The values are based on the lookup_type of 'ASSESSMENT_CLASSIFICATION'.
945 -- It should only be NOT NULL if weighting_scale_id is NOT NULL
946 --
947 -- Preconditions
948 -- assessment_classification is not null
949 -- In Arguments
950 -- p_weighting_classification
951 -- p_weighting_scale_id
952 -- p_assessment_classification
953 -- Post Success:
954 -- If weighting_classification is valid, processing continues
955 -- Post Failure:
956 -- Processing halts.
957 -- Access Status:
958 -- Internal Table Handler Use Only.
959 --
960 Procedure chk_weighting_classification
961 (p_weighting_classification in per_assessment_types.weighting_classification%TYPE
962
963 ,p_weighting_scale_id in per_assessment_types.weighting_scale_id%TYPE
964 ,p_assessment_classification in per_assessment_types.assessment_classification%TYPE
965
966 )
967 is
968 --
969 l_proc varchar2(72) := g_package||'chk_weighting_classification';
970 --
971 begin
972 -- Bug#885806
973 -- dbms_output.put_line('AST_weighting_classification = '||p_weighting_classification);
974 hr_utility.trace('AST_weighting_classification = '||p_weighting_classification);
975
976 hr_utility.set_location('Entering:'||l_proc,5);
977 --
978 -- Check assessment_classification is not null
979 --
980 hr_api.mandatory_arg_error
981 (p_api_name => l_proc
982 ,p_argument => 'assessment_classification'
983 ,p_argument_value => p_assessment_classification
984 );
985 --
986 -- Weighting_classification is MANDATORY if weighting_scale_id is NOT NULL.
987 --
988 if ((p_weighting_scale_id IS NOT NULL) and
989 (p_weighting_classification IS NULL and p_weighting_classification <> 'FASTFORMULA')) then
990 hr_utility.set_location(l_proc,10);
991 hr_utility.set_message(801,'HR_51510_AST_WEIG_CLAS_NULL');
992 hr_utility.raise_error;
993 end if;
994 --
995 -- The weighting_classification should be NULL if weighting_scale_id is NULL.
996 --
997 if ((p_weighting_scale_id IS NULL) and
998 (p_weighting_classification IS NOT NULL and p_weighting_classification <> 'FASTFORMULA' )) then
999 hr_utility.set_location(l_proc,20);
1000 hr_utility.set_message(801,'HR_51512_AST_WEIG_ID_NULL');
1001 hr_utility.raise_error;
1002 end if;
1003 --
1004 -- If this far, necessary values exist.
1005 -- Further checks only need to be performed if weighting_class. exists
1006 --
1007 if (p_weighting_classification IS NOT NULL) then
1008 --
1009 if (p_weighting_classification not in ('PERFORMANCE','PROFICIENCY','FASTFORMULA')) then
1010 hr_utility.set_location(l_proc,30);
1011 hr_utility.set_message(801,'HR_51513_AST_WEIG_CLAS_INVAL');
1012 hr_utility.raise_error;
1013 elsif ((p_assessment_classification <> 'BOTH') and
1014 (p_assessment_classification <> p_weighting_classification)) then
1015 --
1016 -- At this stage, we know that the assessment_classification is valid
1017 -- ie BOTH, PERFORMANCE or PROFICIENCY, so if assessment_classification
1018 -- is not = both, and the weighting classification isn't the same as
1019 -- the assessment_classification, then it must be valid but the wrong
1020 -- one. (ie. it's performance and should be proficiency or visa versa)
1021 --
1022 hr_utility.set_location(l_proc,30);
1023 hr_utility.set_message(801,'HR_51514_AST_WEIG_CLAS_INCOMP');
1024 hr_utility.raise_error;
1025 end if;
1026 end if;
1027 end chk_weighting_classification;
1028 -- ----------------------------------------------------------------------------
1029 -- |-------------------< chk_line_score_formula >----------------------------|
1030 -- ----------------------------------------------------------------------------
1031 -- Description
1032 -- LINE_SCORE_FORMULA should be validated against the lookup type of
1033 -- 'ASSESSMENT_LINE_FORMULA'.
1034 -- This lookup will be user updatable
1035 -- Preconditions
1036 -- none
1037 -- In Arguments
1038 -- p_line_score_formula
1039 -- p_assessment_type_id
1040 -- p_effective_date
1041 -- p_object_version_number
1042 -- Post Success:
1043 -- If line_score_formula is valid, processing continues
1044 -- Post Failure
1045 -- Processing halts.
1046 --
1047 Procedure chk_line_score_formula
1048 (p_line_score_formula in per_assessment_types.line_score_formula%TYPE
1049 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
1050 ,p_effective_date in date
1051 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
1052 )
1053 is
1054 --
1055 l_proc varchar2(72) := g_package||'chk_line_score_formula';
1056 --
1057 begin
1058 hr_utility.set_location('Entering:'||l_proc,1);
1059 --
1060 -- Check mandatory parameters have been set
1061 --
1062 hr_api.mandatory_arg_error
1063 (p_api_name => l_proc
1064 ,p_argument => 'effective_date'
1065 ,p_argument_value => p_effective_date
1066 );
1067 -- Check that value exists in hr_lookups
1068 --
1069 if (p_line_score_formula IS NOT NULL) then
1070 if hr_api.not_exists_in_hr_lookups
1071 (p_effective_date => p_effective_date
1072 ,p_lookup_type => 'ASSESSMENT_LINE_FORMULA'
1073 ,p_lookup_code => p_line_score_formula
1074 ) then
1075 hr_utility.set_location(l_proc,10);
1076 hr_utility.set_message(801,'HR_51515_AST_LINE_FORMU_INVAL');
1077 hr_utility.raise_error;
1078 end if;
1079 end if;
1080 hr_utility.set_location('Leaving:'||l_proc,20);
1081 end chk_line_score_formula;
1082 -- ----------------------------------------------------------------------------
1083 -- |-------------------< chk_total_score_formula >----------------------------|
1084 -- ----------------------------------------------------------------------------
1085 -- Description
1086 -- TOTAL_SCORE_FORMULA should be validated against the lookup type of
1087 -- 'ASSESSMENT_TOTAL_FORMULA'.
1088 -- This lookup will be user updatable
1089 -- Preconditions
1090 -- none
1091 -- In Arguments
1092 -- p_total_score_formula
1093 -- p_assessment_type_id
1094 -- p_effective_date
1095 -- p_object_version_number
1096 -- Post Success:
1097 -- If total_score_formula is valid, processing continues
1098 -- Post Failure
1099 -- Processing halts.
1100 --
1101 Procedure chk_total_score_formula
1102 (p_total_score_formula in per_assessment_types.total_score_formula%TYPE
1103 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
1104 ,p_effective_date in date
1105 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
1106 )
1107 is
1108 --
1109 l_proc varchar2(72) := g_package||'chk_total_score_formula';
1110 l_api_updating boolean;
1111 --
1112 begin
1113 hr_utility.set_location('Entering:'||l_proc,1);
1114 --
1115 -- Check mandatory parameters have been set
1116 --
1117 hr_api.mandatory_arg_error
1118 (p_api_name => l_proc
1119 ,p_argument => 'effective_date'
1120 ,p_argument_value => p_effective_date
1121 );
1122 --
1123 -- Only proceed with the validation if:
1124 -- a) The current g_old_rec is current.
1125 -- b) The total_score_formula has changed.
1126 -- c) A record is being inserted.
1127 --
1128 l_api_updating := per_ast_shd.api_updating
1129 (p_assessment_type_id => p_assessment_type_id
1130 ,p_object_version_number => p_object_version_number
1131 );
1132 --
1133 if ((l_api_updating and nvl(per_ast_shd.g_old_rec.total_score_formula,
1134 hr_api.g_varchar2)
1135 <> nvl(p_total_score_formula, hr_api.g_varchar2))
1136 or
1137 (NOT l_api_updating)) then
1138 --
1139 -- Check that value exists in hr_lookups
1140 --
1141 if (p_total_score_formula IS NOT NULL) then
1142 if hr_api.not_exists_in_hr_lookups
1143 (p_effective_date => p_effective_date
1144 ,p_lookup_type => 'ASSESSMENT_TOTAL_FORMULA'
1145 ,p_lookup_code => p_total_score_formula
1146 ) then
1147 hr_utility.set_location(l_proc,10);
1148 hr_utility.set_message(801,'HR_51516_AST_TOTA_FORMU_INVAL');
1149 hr_utility.raise_error;
1150 end if;
1151 end if;
1152 end if;
1153 --
1154 hr_utility.set_location('Leaving:'||l_proc,20);
1155 --
1156 -- Bug#885806
1157 -- dbms_output.put_line('AST_total score formula ');
1158 hr_utility.trace('AST_total score formula ');
1159 end chk_total_score_formula;
1160
1161 -- ----------------------------------------------------------------------------
1162 -- |-------------------< chk_type >----------------------------|
1163 -- ----------------------------------------------------------------------------
1164 -- Description
1165 -- TYPE should be validated against the lookup type of
1166 -- 'ASSESSMENT_TYPE'.
1167 -- This lookup will be system lookup
1168 -- Preconditions
1169 -- none
1170 -- In Arguments
1171 -- p_type
1172 -- p_assessment_type_id
1173 -- p_effective_date
1174 -- p_object_version_number
1175 -- Post Success:
1176 -- If type is valid, processing continues
1177 -- Post Failure
1178 -- Processing halts.
1179 --
1180 Procedure chk_type
1181 (p_type in per_assessment_types.type%TYPE
1182 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
1183 ,p_effective_date in date
1184 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
1185 )
1186 is
1187 --
1188 l_proc varchar2(72) := g_package||'chk_type';
1189 l_api_updating boolean;
1190 --
1191 begin
1192 hr_utility.set_location('Entering:'||l_proc,1);
1193 --
1194 -- Check mandatory parameters have been set
1195 --
1196 hr_api.mandatory_arg_error
1197 (p_api_name => l_proc
1198 ,p_argument => 'effective_date'
1199 ,p_argument_value => p_effective_date
1200 );
1201 --
1202 -- Only proceed with the validation if:
1203 -- a) The current g_old_rec is current.
1204 -- b) The type has changed.
1205 -- c) A record is being inserted.
1206 --
1207 l_api_updating := per_ast_shd.api_updating
1208 (p_assessment_type_id => p_assessment_type_id
1209 ,p_object_version_number => p_object_version_number
1210 );
1211 --
1212 if ((l_api_updating and nvl(per_ast_shd.g_old_rec.type,
1213 hr_api.g_varchar2)
1214 <> nvl(p_type, hr_api.g_varchar2))
1215 or
1216 (NOT l_api_updating)) then
1217 --
1218 -- Check that value exists in hr_lookups
1219 --
1220 if (p_type IS NOT NULL) then
1221 if hr_api.not_exists_in_hr_lookups
1222 (p_effective_date => p_effective_date
1223 ,p_lookup_type => 'ASSESSMENT_TYPE'
1224 ,p_lookup_code => p_type
1225 ) then
1226 hr_utility.set_location(l_proc,10);
1227 hr_utility.set_message(801,'HR_AST_INVALID_TYPE');
1228 hr_utility.raise_error;
1229 end if;
1230 end if;
1231 end if;
1232
1233 --
1234 hr_utility.set_location('Leaving:'||l_proc,20);
1235 --
1236 -- Bug#885806
1237 -- dbms_output.put_line('AST_total score formula ');
1238 hr_utility.trace('AST_type ');
1239 end chk_type;
1240 -- ----------------------------------------------------------------------------
1241 -- |---------------------< chk_assessment_type_used >-------------------------|
1242 -- ----------------------------------------------------------------------------
1243 -- Description
1244 -- A check to see if there are any assessments which use this assessment_type
1245 -- . The table which holds the competence elements isn't checked as there
1246 -- will be rows in here when the assessment_type is set up.
1247 -- This procedure is used before the row can be deleted.
1248 -- Preconditions
1249 -- none
1250 -- In Arguments
1251 -- p_assessment_type_id
1252 -- p_object_version_number
1253 -- Post Success:
1254 -- If the assessment_type isn't being used by a assessment, then it can be
1255 -- removed until the assessment is first removed.
1256 -- Post Failure
1257 -- Processing halts.
1258 -- Access Status
1259 -- Internal processing only
1260 --
1261 Procedure chk_assessment_type_used
1262 (p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
1263 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
1264 ) is
1265 --
1266 l_proc varchar2(72) := g_package || 'chk_assessment_type_used';
1267 l_exists varchar2(1);
1268 --
1269 -- Define a cursor to get an assessment which uses this assessment_type.
1270 --
1271 cursor csr_assessment_type_usage is
1272 select null
1273 from per_assessments
1274 where assessment_type_id = p_assessment_type_id;
1275 --
1276 Begin
1277 hr_utility.set_location('Entering '||l_proc,1);
1278 --
1279 -- Check that assessment_type is not referenced by an assessment
1280 --
1281 open csr_assessment_type_usage;
1282 fetch csr_assessment_type_usage into l_exists;
1283 if csr_assessment_type_usage%found then
1284 close csr_assessment_type_usage;
1285 hr_utility.set_location (l_proc, 5);
1286 hr_utility.set_message (801, 'HR_51579_AST_REF_BY_ASSESS');
1287 hr_utility.raise_error;
1288 end if;
1289 close csr_assessment_type_usage;
1290 --
1291 end chk_assessment_type_used;
1292
1293 -------------------------------<chk_available_flag>----------------------+
1294 --------------------------------------------------------------------------+
1295 -- Description:
1296 -- Validate available_flag
1297 -- against the HR_LOOKUP where lookup_type = 'TEMPLATE_AVAILABILITY_FLAG'.
1298 --
1299
1300 -- Pre_conditions:
1301 -- - Valid p_assessment_type_id
1302
1303 -- In Arguments:
1304 -- p_available_flag
1305 -- p_effective_date
1306 -- p_object_version_number
1307
1308 -- Post Success:
1309 -- Process continues if :
1310 -- All the in parameters are valid.
1311
1312 -- Post Failure:
1313 -- An application error is raised and processing is terminated if any of
1314 -- the following cases are found :
1315 -- available_flag value is not validated against lookup 'PROFICIENCY_SOURCE'.
1316
1317 -- Access Status
1318 -- Internal Table Handler Use Only.
1319
1320
1321 -- Access Status:
1322 -- Internal Table Handler Use Only.
1323 --
1324
1325 Procedure chk_available_flag
1326 (
1327 p_available_flag in per_assessment_types.available_flag%TYPE
1328 ,p_effective_date in date
1329 ,p_object_version_number in per_assessment_types.object_version_number%TYPE
1330 ,p_assessment_type_id in per_assessment_types.assessment_type_id%TYPE
1331 )
1332 is
1333 --
1334 l_proc varchar2(72):=g_package||'chk_available_flag';
1335 l_api_updating boolean;
1336 --
1337 begin
1338 -- Bug#885806
1339 -- dbms_output.put_line('Inside the chk_display_assessment_comments procedure');
1340 hr_utility.trace('Inside the chk_available_flag procedure');
1341 hr_utility.set_location('Entering:'|| l_proc, 1);
1342
1343
1344 --
1345 -- Check mandatory parameters have been set
1346 --
1347 hr_api.mandatory_arg_error
1348 (p_api_name => l_proc
1349 ,p_argument => 'effective_date'
1350 ,p_argument_value => p_effective_date
1351 );
1352 --
1353 -- Only proceed with the validation if :
1354 -- a) The current g_old_rec is current
1355 -- b) The value has changed.
1356 -- c) A record is being inserted
1357 --
1358
1359 l_api_updating := per_ast_shd.api_updating
1360 (p_assessment_type_id => p_assessment_type_id
1361 ,p_object_version_number => p_object_version_number);
1362 --
1363 if ((l_api_updating and nvl(per_ast_shd.g_old_rec.available_flag,
1364 hr_api.g_varchar2)
1365 <> nvl(p_available_flag, hr_api.g_varchar2))
1366 or
1367 (NOT l_api_updating)) then
1368 --
1369 hr_utility.set_location(l_proc, 5);
1370 --
1371 -- Check the value in p_available_flag exists in hr_lookups
1372 --
1373 if hr_api.not_exists_in_hr_lookups
1374 (p_effective_date => p_effective_date
1375 ,p_lookup_type => 'TEMPLATE_AVAILABILITY_FLAG'
1376 ,p_lookup_code => p_available_flag
1377 ) then
1378 hr_utility.set_location(l_proc, 10);
1379 hr_utility.set_message(800,'HR_AVAIL_FLAG_INVAL');
1380 hr_utility.raise_error;
1381 end if;
1382 --
1383 hr_utility.set_location(' Leaving:'|| l_proc, 15);
1384 end if;
1385 end chk_available_flag;
1386 --
1387 --
1388 -- -----------------------------------------------------------------------
1389 -- |------------------------------< chk_df >-----------------------------|
1390 -- -----------------------------------------------------------------------
1391 --
1392 -- Description:
1393 -- Validates the all Descriptive Flexfield values.
1394 --
1395 -- Pre-conditions:
1396 -- All other columns have been validated. Must be called as the
1397 -- last step from insert_validate and update_validate.
1398 --
1399 -- In Arguments:
1400 -- p_rec
1401 --
1402 -- Post Success:
1403 -- If the Descriptive Flexfield structure column and data values are
1404 -- all valid this procedure will end normally and processing will
1405 -- continue.
1406 --
1407 -- Post Failure:
1408 -- If the Descriptive Flexfield structure column value or any of
1409 -- the data values are invalid then an application error is raised as
1410 -- a PL/SQL exception.
1411 --
1412 -- Access Status:
1413 -- Internal Row Handler Use Only.
1414 --
1415 procedure chk_df
1416 (p_rec in per_ast_shd.g_rec_type) is
1417 --
1418 l_proc varchar2(72) := g_package||'chk_df';
1419 --
1420 begin
1421 hr_utility.set_location('Entering:'||l_proc, 10);
1422 --
1423 if ((p_rec.assessment_type_id is not null) and (
1424 nvl(per_ast_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1425 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1426 nvl(per_ast_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1427 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1428 nvl(per_ast_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1429 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1430 nvl(per_ast_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1431 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1432 nvl(per_ast_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1433 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1434 nvl(per_ast_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1435 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1436 nvl(per_ast_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1437 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1438 nvl(per_ast_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1439 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1440 nvl(per_ast_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1441 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1442 nvl(per_ast_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1443 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1444 nvl(per_ast_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1445 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1446 nvl(per_ast_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1447 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1448 nvl(per_ast_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1449 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1450 nvl(per_ast_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1451 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1452 nvl(per_ast_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1453 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1454 nvl(per_ast_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1455 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1456 nvl(per_ast_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1457 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1458 nvl(per_ast_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1459 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1460 nvl(per_ast_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1461 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1462 nvl(per_ast_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1463 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1464 nvl(per_ast_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1465 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1466 or
1467 (p_rec.assessment_type_id is null) then
1468 --
1469 -- Only execute the validation if absolutely necessary:
1470 -- a) During update, the structure column value or any
1471 -- of the attribute values have actually changed.
1472 -- b) During insert.
1473 --
1474 hr_dflex_utility.ins_or_upd_descflex_attribs
1475 (p_appl_short_name => 'PER'
1476 ,p_descflex_name => 'PER_ASSESSMENT_TYPES'
1477 ,p_attribute_category => p_rec.attribute_category
1478 ,p_attribute1_name => 'ATTRIBUTE1'
1479 ,p_attribute1_value => p_rec.attribute1
1480 ,p_attribute2_name => 'ATTRIBUTE2'
1481 ,p_attribute2_value => p_rec.attribute2
1482 ,p_attribute3_name => 'ATTRIBUTE3'
1483 ,p_attribute3_value => p_rec.attribute3
1484 ,p_attribute4_name => 'ATTRIBUTE4'
1485 ,p_attribute4_value => p_rec.attribute4
1486 ,p_attribute5_name => 'ATTRIBUTE5'
1487 ,p_attribute5_value => p_rec.attribute5
1488 ,p_attribute6_name => 'ATTRIBUTE6'
1489 ,p_attribute6_value => p_rec.attribute6
1490 ,p_attribute7_name => 'ATTRIBUTE7'
1491 ,p_attribute7_value => p_rec.attribute7
1492 ,p_attribute8_name => 'ATTRIBUTE8'
1493 ,p_attribute8_value => p_rec.attribute8
1497 ,p_attribute10_value => p_rec.attribute10
1494 ,p_attribute9_name => 'ATTRIBUTE9'
1495 ,p_attribute9_value => p_rec.attribute9
1496 ,p_attribute10_name => 'ATTRIBUTE10'
1498 ,p_attribute11_name => 'ATTRIBUTE11'
1499 ,p_attribute11_value => p_rec.attribute11
1500 ,p_attribute12_name => 'ATTRIBUTE12'
1501 ,p_attribute12_value => p_rec.attribute12
1502 ,p_attribute13_name => 'ATTRIBUTE13'
1503 ,p_attribute13_value => p_rec.attribute13
1504 ,p_attribute14_name => 'ATTRIBUTE14'
1505 ,p_attribute14_value => p_rec.attribute14
1506 ,p_attribute15_name => 'ATTRIBUTE15'
1507 ,p_attribute15_value => p_rec.attribute15
1508 ,p_attribute16_name => 'ATTRIBUTE16'
1509 ,p_attribute16_value => p_rec.attribute16
1510 ,p_attribute17_name => 'ATTRIBUTE17'
1511 ,p_attribute17_value => p_rec.attribute17
1512 ,p_attribute18_name => 'ATTRIBUTE18'
1513 ,p_attribute18_value => p_rec.attribute18
1514 ,p_attribute19_name => 'ATTRIBUTE19'
1515 ,p_attribute19_value => p_rec.attribute19
1516 ,p_attribute20_name => 'ATTRIBUTE20'
1517 ,p_attribute20_value => p_rec.attribute20
1518 );
1519 end if;
1520 --
1521 hr_utility.set_location(' Leaving:'||l_proc, 20);
1522
1523 end chk_df;
1524 --
1525 -- ----------------------------------------------------------------------------
1526 -- |---------------------------< insert_validate >----------------------------|
1527 -- ----------------------------------------------------------------------------
1528 Procedure insert_validate
1529 (p_rec in per_ast_shd.g_rec_type
1530 ,p_effective_date in date) is
1531 --
1532 l_proc varchar2(72) := g_package||'insert_validate';
1533 --
1534 Begin
1535 hr_utility.set_location('Entering:'||l_proc, 5);
1536 --
1537 -- Call all supporting business operations. Mapping to the appropiate
1538 -- Business Rules in perast.bru is provided.
1539 --
1540 -- VALIDATE BUSINESS_GROUP_ID
1541 -- Business Rule Mapping
1542 -- =====================
1543 -- Rule CHK_BUSINESS_GROUP_ID a
1544 --
1545 -- Bug#885806
1546 -- dbms_output.put_line('AST_bus grp id = '||p_rec.business_group_id);
1547 hr_utility.trace('AST_bus grp id = '||p_rec.business_group_id);
1548 --
1549 if p_rec.business_group_id is not null then
1550
1551 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1552 end if;
1553 --
1554 hr_utility.set_location(' Location:'||l_proc, 10);
1555
1556 --Validate available_flag
1557 -- rule Chk_Available_flag
1558 --
1559 PER_AST_BUS.chk_available_flag
1560 (
1561 p_available_flag => p_rec.available_flag
1562 ,p_effective_date => p_effective_date
1563 ,p_object_version_number => p_rec.object_version_number
1564 ,p_assessment_type_id => p_rec.assessment_type_id
1565 );
1566 --
1567 --
1568 -- VALIDATE TYPE
1569 -- Rule CHK_TYPE
1570 --
1571 per_ast_bus.chk_type
1572 (p_type => p_rec.type
1573 ,p_assessment_type_id => p_rec.assessment_type_id
1574 ,p_effective_date => p_effective_date
1575 ,p_object_version_number => p_rec.object_version_number
1576 );
1577 --
1578 --
1579 -- VALIDATE NAME
1580 -- Business Rule Mapping
1581 -- =====================
1582 -- Rule CHK_NAME a
1583 --
1584 per_ast_bus.chk_name
1585 (p_name => p_rec.name
1586 ,p_assessment_type_id => p_rec.assessment_type_id
1587 ,p_type => p_rec.type
1588 ,p_business_group_id => p_rec.business_group_id
1589 ,p_object_version_number => p_rec.object_version_number
1590 );
1591 --
1592 hr_utility.set_location(l_proc, 15);
1593 --
1594 --
1595 -- VALIDATE DISPLAY_ASSESSMENT_COMMENTS
1596 -- Business Rule Mapping
1597 -- =====================
1598 -- Rule CHK DISPLAY_ASSESSMENT_COMMENTS a
1599 --
1600 per_ast_bus.chk_disp_assess_comments
1601 (p_display_assessment_comments => p_rec.display_assessment_comments
1602 ,p_assessment_type_id => p_rec.assessment_type_id
1603 ,p_effective_date => p_effective_date
1604 ,p_object_version_number => p_rec.object_version_number
1605 );
1606 --
1607 hr_utility.set_location(l_proc,20);
1608 --
1609 --
1610 -- VALIDATE ACTIVE
1611 -- Business Rule Mapping
1612 -- =====================
1613 -- Rule CHK_DATE_FROM_TO
1614 --
1615 per_ast_bus.chk_date_from_to
1616 (p_date_from => p_rec.date_from
1617 ,p_date_to => p_rec.date_to
1618 ,p_assessment_type_id => p_rec.assessment_type_id
1619 ,p_business_group_id => p_rec.business_group_id
1620 ,p_object_version_number => p_rec.object_version_number
1621 );
1622 --
1623 --
1624 hr_utility.set_location (l_proc, 25);
1625 --
1626 -- Bug#885806
1627 -- dbms_output.put_line('About to validate assessment_classification');
1628 hr_utility.trace('About to validate assessment_classification');
1629 --
1630 -- VALIDATE ASSESSMENT_CLASSIFICATION
1631 -- Business Rule Mapping
1632 -- =====================
1633 -- Rule CHK_ASSESSMENT_CLASSIFICATION a
1634 --
1635 per_ast_bus.chk_assessment_classification
1636 (p_assessment_classification => p_rec.assessment_classification
1637 ,p_business_group_id => p_rec.business_group_id
1638 ,p_effective_date => p_effective_date
1639 ,p_assessment_type_id => p_rec.assessment_type_id
1643 hr_utility.set_location (l_proc, 30);
1640 ,p_object_version_number => p_rec.object_version_number
1641 );
1642 --
1644 --
1645 --
1646 -- Bug#885806
1647 -- dbms_output.put_line('About to validate rating_scale_id');
1648 hr_utility.trace('About to validate rating_scale_id');
1649 --
1650 -- VALIDATE RATING_SCALE_ID
1651 -- Business Rule Mapping
1652 -- =====================
1653 -- Rule CHK_RATING_SCALE_ID
1654 --
1655 per_ast_bus.chk_rating_scale_id
1656 (p_rating_scale_id => p_rec.rating_scale_id
1657 ,p_business_group_id => p_rec.business_group_id
1658 ,p_assessment_type_id => p_rec.assessment_type_id
1659 ,p_assessment_classification=> p_rec.assessment_classification
1660 ,p_object_version_number => p_rec.object_version_number
1661 ,p_weighting_classification => p_rec.weighting_classification
1662 );
1663 --
1664 hr_utility.set_location (l_proc, 35);
1665 --
1666 --
1667 -- VALIDATE WEIGHTING_SCALE_ID
1668 -- Business Rule Mapping
1669 -- =====================
1670 -- Rule CHK_WEIGHTING_SCALE_ID a, c
1671 --
1672 per_ast_bus.chk_weighting_scale_id
1673 (p_weighting_scale_id => p_rec.weighting_scale_id
1674 ,p_business_group_id => p_rec.business_group_id
1675 ,p_assessment_type_id => p_rec.assessment_type_id
1676 ,p_assessment_classification => p_rec.assessment_classification
1677 ,p_object_version_number => p_rec.object_version_number
1678 );
1679 --
1680 hr_utility.set_location (l_proc, 40);
1681 --
1682 --
1683 -- VALIDATE WEIGHTING_SCALE_COMMENT
1684 -- Business Rule Mapping
1685 -- =====================
1686 -- Rule CHK_WEIGHTING_SCALE_COMMENT a
1687 --
1688 per_ast_bus.chk_weighting_scale_comment
1689 (p_weighting_scale_comment => p_rec.weighting_scale_comment
1690 ,p_weighting_scale_id => p_rec.weighting_scale_id
1691 );
1692 --
1693 hr_utility.set_location (l_proc, 45);
1694 --
1695 --
1696 -- VALIDATE RATING_SCALE_COMMENT
1697 -- Business Rule Mapping
1698 -- =====================
1699 -- Rule CHK_RATING_SCALE_COMMENT a
1700 --
1701 per_ast_bus.chk_rating_scale_comment
1702 (p_rating_scale_comment => p_rec.rating_scale_comment
1703 ,p_rating_scale_id => p_rec.rating_scale_id
1704 );
1705 --
1706 hr_utility.set_location (l_proc, 50);
1707 --
1708 --
1709 -- VALIDATE WEIGHTING_CLASSIFICATION
1710 -- Business Rule Mapping
1711 -- =====================
1712 -- Rule CHK_WEIGHTING_CLASSIFICATION a, b, c
1713 --
1714 per_ast_bus.chk_weighting_classification
1715 (p_weighting_classification => p_rec.weighting_classification
1716 ,p_weighting_scale_id => p_rec.weighting_scale_id
1717 ,p_assessment_classification=> p_rec.assessment_classification
1718 );
1719 --
1720 hr_utility.set_location (l_proc, 55);
1721 --
1722 -- Bug#885806
1723 -- dbms_output.put_line('About to validate line_score_formula');
1724 hr_utility.trace('About to validate line_score_formula');
1725 --
1726 -- VALIDATE LINE_SCORE_FORMULA
1727 -- Business Rule Mapping
1728 -- =====================
1729 -- Rule CHK_LINE_SCORE_FORMULA a
1730 --
1731 per_ast_bus.chk_line_score_formula
1732 (p_line_score_formula => p_rec.line_score_formula
1733 ,p_assessment_type_id => p_rec.assessment_type_id
1734 ,p_effective_date => p_effective_date
1735 ,p_object_version_number => p_rec.object_version_number
1736 );
1737 --
1738 hr_utility.set_location (l_proc, 60);
1739 --
1740 -- Bug#885806
1741 -- dbms_output.put_line('About to validate TOTAL_score_formula');
1742 hr_utility.trace('About to validate TOTAL_score_formula');
1743 --
1744 -- VALIDATE TOTAL_SCORE_FORMULA
1745 -- Business Rule Mapping
1746 -- =====================
1747 -- Rule CHK_TOTAL_SCORE_FORMULA a
1748 --
1749 per_ast_bus.chk_total_score_formula
1750 (p_total_score_formula => p_rec.total_score_formula
1751 ,p_assessment_type_id => p_rec.assessment_type_id
1752 ,p_effective_date => p_effective_date
1753 ,p_object_version_number => p_rec.object_version_number
1754 );
1755 --
1756 hr_utility.set_location (l_proc, 65);
1757 --
1758 --
1759 -- Bug#885806
1760 -- dbms_output.put_line('About to validate flex');
1761 hr_utility.trace('About to validate flex');
1762 --
1763 -- Call Descriptive Flexfield Validation routines
1764 --
1765 per_ast_bus.chk_df(p_rec => p_rec);
1766 --
1767 hr_utility.set_location (l_proc, 75);
1768 --
1769 hr_utility.set_location(' Leaving:'||l_proc, 85);
1770 -- Bug#885806
1771 -- dbms_output.put_line('finished insert_validate');
1772 hr_utility.trace('finished insert_validate');
1773 --
1774 End insert_validate;
1775 --
1776 -- ----------------------------------------------------------------------------
1777 -- |---------------------------< update_validate >----------------------------|
1778 -- ----------------------------------------------------------------------------
1779 Procedure update_validate
1780 (p_rec in per_ast_shd.g_rec_type
1781 ,p_effective_date in date
1782 ) is
1783 --
1784 l_proc varchar2(72) := g_package||'update_validate';
1785 --
1786 Begin
1787 hr_utility.set_location('Entering:'||l_proc, 5);
1788 --
1789 -- Call all supporting business operations. Mapping to the appropiate
1790 -- Business Rules in perast.bru is provided.
1791 --
1792 --
1793 if p_rec.business_group_id is not null then
1797 --Validate available_flag
1794 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1795 end if;
1796 --
1798 -- rule Chk_Available_flag
1799 --
1800 PER_AST_BUS.chk_available_flag
1801 (
1802 p_available_flag => p_rec.available_flag
1803 ,p_effective_date => p_effective_date
1804 ,p_object_version_number => p_rec.object_version_number
1805 ,p_assessment_type_id => p_rec.assessment_type_id
1806 );
1807 --
1808 -- VALIDATE TYPE
1809 -- Rule CHK_TYPE
1810 --
1811 per_ast_bus.chk_type
1812 (p_type => p_rec.type
1813 ,p_assessment_type_id => p_rec.assessment_type_id
1814 ,p_effective_date => p_effective_date
1815 ,p_object_version_number => p_rec.object_version_number
1816 );
1817 --
1818
1819 -- VALIDATE CHK_NON_UPDATABLE_ARGS
1820 -- Check those columns which cannot be updated have not changed.
1821 -- Business Rule Mapping
1822 -- =====================
1823 -- Rule CHK_BUSINESS_GROUP_ID a
1824 --
1825 per_ast_bus.chk_non_updateable_args
1826 (p_rec => p_rec);
1827 --
1828 --
1829 -- VALIDATE NAME
1830 -- Business Rule Mapping
1831 -- =====================
1832 -- Rule CHK_NAME a
1833 --
1834 per_ast_bus.chk_name
1835 (p_name => p_rec.name
1836 ,p_assessment_type_id => p_rec.assessment_type_id
1837 ,p_type => p_rec.type
1838 ,p_business_group_id => p_rec.business_group_id
1839 ,p_object_version_number => p_rec.object_version_number
1840 );
1841 --
1842 hr_utility.set_location(l_proc, 15);
1843 --
1844 --
1845 -- VALIDATE DISPLAY_ASSESSMENT_COMMENTS
1846 -- Business Rule Mapping
1847 -- =====================
1848 -- Rule CHK DISPLAY_ASSESSMENT_COMMENTS a
1849 --
1850 per_ast_bus.chk_disp_assess_comments
1851 (p_display_assessment_comments => p_rec.display_assessment_comments
1852 ,p_assessment_type_id => p_rec.assessment_type_id
1853 ,p_effective_date => p_effective_date
1854 ,p_object_version_number => p_rec.object_version_number
1855 );
1856 --
1857 hr_utility.set_location(l_proc,20);
1858 --
1859 --
1860 -- VALIDATE ACTIVE
1861 -- Business Rule Mapping
1862 -- =====================
1863 -- Rule CHK_DATE_FROM_TO
1864 --
1865 per_ast_bus.chk_date_from_to
1866 (p_date_from => p_rec.date_from
1867 ,p_date_to => p_rec.date_to
1868 ,p_assessment_type_id => p_rec.assessment_type_id
1869 ,p_business_group_id => p_rec.business_group_id
1870 ,p_object_version_number => p_rec.object_version_number
1871 );
1872 --
1873 -- VALIDATE ASSESSMENT_CLASSIFICATION
1874 -- Business Rule Mapping
1875 -- =====================
1876 -- Rule CHK_ASSESSMENT_CLASSIFICATION
1877 --
1878 per_ast_bus.chk_assessment_classification
1879 (p_assessment_classification => p_rec.assessment_classification
1880 ,p_business_group_id => p_rec.business_group_id
1881 ,p_effective_date => p_effective_date
1882 ,p_assessment_type_id => p_rec.assessment_type_id
1883 ,p_object_version_number => p_rec.object_version_number
1884 );
1885 --
1886 hr_utility.set_location (l_proc, 30);
1887 -- VALIDATE RATING_SCALE_ID
1888 -- Business Rule Mapping
1889 -- =====================
1890 -- Rule CHK_RATING_SCALE_ID
1891 --
1892 per_ast_bus.chk_rating_scale_id
1893 (p_rating_scale_id => p_rec.rating_scale_id
1894 ,p_business_group_id => p_rec.business_group_id
1895 ,p_assessment_type_id => p_rec.assessment_type_id
1896 ,p_assessment_classification=> p_rec.assessment_classification
1897 ,p_object_version_number => p_rec.object_version_number
1898 ,p_weighting_classification => p_rec.weighting_classification
1899 );
1900 --
1901 hr_utility.set_location (l_proc, 15);
1902 --
1903 --
1904 -- VALIDATE WEIGHTING_SCALE_ID
1905 -- Business Rule Mapping
1906 -- =====================
1907 -- Rule CHK_WEIGHTING_SCALE_ID a, c
1908 --
1909 per_ast_bus.chk_weighting_scale_id
1910 (p_weighting_scale_id => p_rec.weighting_scale_id
1911 ,p_business_group_id => p_rec.business_group_id
1912 ,p_assessment_type_id => p_rec.assessment_type_id
1913 ,p_assessment_classification => p_rec.assessment_classification
1914 ,p_object_version_number => p_rec.object_version_number
1915 );
1916 --
1917 hr_utility.set_location (l_proc, 25);
1918 --
1919 --
1920 -- VALIDATE WEIGHTING_SCALE_COMMENT
1921 -- Business Rule Mapping
1922 -- =====================
1923 -- Rule CHK_WEIGHTING_SCALE_COMMENT a
1924 --
1925 per_ast_bus.chk_weighting_scale_comment
1926 (p_weighting_scale_comment => p_rec.weighting_scale_comment
1927 ,p_weighting_scale_id => p_rec.weighting_scale_id
1928 );
1929 --
1930 hr_utility.set_location (l_proc, 30);
1931 --
1932 --
1933 -- VALIDATE RATING_SCALE_COMMENT
1934 -- Business Rule Mapping
1935 -- =====================
1936 -- Rule CHK_RATING_SCALE_COMMENT a
1937 --
1938 per_ast_bus.chk_rating_scale_comment
1939 (p_rating_scale_comment => p_rec.rating_scale_comment
1940 ,p_rating_scale_id => p_rec.rating_scale_id
1941 );
1942 --
1946 -- VALIDATE WEIGHTING_CLASSIFICATION
1943 hr_utility.set_location (l_proc, 35);
1944 --
1945 --
1947 -- Business Rule Mapping
1948 -- =====================
1949 -- Rule CHK_WEIGHTING_CLASSIFICATION a, b, c
1950 --
1951 per_ast_bus.chk_weighting_classification
1952 (p_weighting_classification => p_rec.weighting_classification
1953 ,p_weighting_scale_id => p_rec.weighting_scale_id
1954 ,p_assessment_classification=> p_rec.assessment_classification
1955 );
1956 --
1957 hr_utility.set_location (l_proc, 40);
1958 --
1959 --
1960 -- VALIDATE LINE_SCORE_FORMULA
1961 -- Business Rule Mapping
1962 -- =====================
1963 -- Rule CHK_LINE_SCORE_FORMULA a
1964 --
1965 per_ast_bus.chk_line_score_formula
1966 (p_line_score_formula => p_rec.line_score_formula
1967 ,p_assessment_type_id => p_rec.assessment_type_id
1968 ,p_effective_date => p_effective_date
1969 ,p_object_version_number => p_rec.object_version_number
1970 );
1971 --
1972 hr_utility.set_location (l_proc, 45);
1973 --
1974 --
1975 -- VALIDATE TOTAL_SCORE_FORMULA
1976 -- Business Rule Mapping
1977 -- =====================
1978 -- Rule CHK_TOTAL_SCORE_FORMULA a
1979 --
1980 per_ast_bus.chk_total_score_formula
1981 (p_total_score_formula => p_rec.total_score_formula
1982 ,p_assessment_type_id => p_rec.assessment_type_id
1983 ,p_effective_date => p_effective_date
1984 ,p_object_version_number => p_rec.object_version_number
1985 );
1986 --
1987 hr_utility.set_location (l_proc, 50);
1988 --
1989 --
1990 -- Call Discriptive Flexfield Validation routines.
1991 --
1992 per_ast_bus.chk_df(p_rec => p_rec);
1993 --
1994 hr_utility.set_location (l_proc, 55);
1995 --
1996 hr_utility.set_location(' Leaving:'||l_proc, 60);
1997 End update_validate;
1998 --
1999 -- ----------------------------------------------------------------------------
2000 -- |---------------------------< delete_validate >----------------------------|
2001 -- ----------------------------------------------------------------------------
2002 Procedure delete_validate(p_rec in per_ast_shd.g_rec_type) is
2003 --
2004 l_proc varchar2(72) := g_package||'delete_validate';
2005 --
2006 Begin
2007 hr_utility.set_location('Entering:'||l_proc, 5);
2008 --
2009 -- Call all supporting business operations and show business rule mapping.
2010 --
2011 -- VALIDATE ASSESSMENT_TYPE_USED
2012 -- Business Rule Mapping
2013 -- =====================
2014 -- CHK_ASSESSMENT_TYPE_USED a
2015 --
2016 per_ast_bus.chk_assessment_type_used
2017 (p_assessment_type_id => p_rec.assessment_type_id
2018 ,p_object_version_number => p_rec.object_version_number
2019 );
2020 --
2021 hr_utility.set_location(' Leaving:'||l_proc, 10);
2022 End delete_validate;
2023 --
2024 -- ----------------------------------------------------------------------------
2025 -- |-----------------------< return_legislation_code >-------------------------|
2026 -- ----------------------------------------------------------------------------
2027 Function return_legislation_code
2028 ( p_assessment_type_id in number
2029 ) return varchar2 is
2030 --
2031 -- Declare cursor
2032 --
2033 cursor csr_leg_code is
2034 select legislation_code
2035 from per_business_groups pbg,
2036 per_assessment_types pat
2037 where pat.assessment_type_id = p_assessment_type_id
2038 and pbg.business_group_id = pat.business_group_id;
2039
2040 l_proc varchar2(72) := g_package||'return_legislation_code';
2041 l_legislation_code varchar2(150);
2042 --
2043 Begin
2044 hr_utility.set_location('Entering:'||l_proc, 5);
2045 --
2046 -- Ensure that all the mandatory parameters are not null
2047 --
2048 hr_api.mandatory_arg_error (p_api_name => l_proc,
2049 p_argument => 'assessment_type_id',
2050 p_argument_value => p_assessment_type_id );
2051 --
2052 if nvl(g_assessment_type_id, hr_api.g_number) = p_assessment_type_id then
2053 --
2054 -- The legislation code has already been found with a previous
2055 -- call to this function. Just return the value in the global
2056 -- variable.
2057 --
2058 l_legislation_code := g_legislation_code;
2059 hr_utility.set_location(l_proc, 20);
2060 else
2061 --
2062 -- The ID is different to the last call to this function
2063 -- or this is the first call to this function.
2064 --
2065 open csr_leg_code;
2066 fetch csr_leg_code into l_legislation_code;
2067 if csr_leg_code%notfound then
2068 close csr_leg_code;
2069 --
2070 -- The primary key is invalid therefore we must error out
2071 --
2072 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2073 hr_utility.raise_error;
2074 end if;
2075 --
2076 close csr_leg_code;
2077 g_assessment_type_id:= p_assessment_type_id;
2078 g_legislation_code := l_legislation_code;
2079 end if;
2080 return l_legislation_code;
2081 --
2082 hr_utility.set_location(' Leaving:'||l_proc, 10);
2083 --
2084 End return_legislation_code;
2085 --
2086 end per_ast_bus;
2087
[Home] [Help]