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