[Home] [Help]
PACKAGE BODY: APPS.PER_ASN_BUS
Source
1 Package Body per_asn_bus as
2 /* $Header: peasnrhi.pkb 115.11 2003/09/01 08:19:06 bdivvela ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_asn_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be used by the
11 -- return_legislation_code function.
12 --
13 g_assessment_id number default null;
14 g_legislation_code varchar2(150) default null;
15 -- ----------------------------------------------------------------------------
16 -- |-----------------------< chk_non_updateable_args >------------------------|
17 -- ----------------------------------------------------------------------------
18 --
19 -- In the ASSESSMENT_ENTITY, there three non-updateable arguments :
20 -- business_group_id
21 -- person_id
22 -- assessor_person_id
23 --
24 Procedure chk_non_updateable_args(p_rec in per_asn_shd.g_rec_type) is
25 --
26 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
27 l_error exception;
28 l_argument varchar2(30);
29 --
30 Begin
31 --
32 hr_utility.set_location('Entering:'|| l_proc,5);
33 --
34 -- Only proceed with validation if a row exists for
35 -- the current record in the HR Schema.
36 if not per_asn_shd.api_updating
37 --
38 (p_assessment_id => p_rec.assessment_id
39 ,p_object_version_number => p_rec.object_version_number
40 ) then
41 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
42 hr_utility.set_message_token('PROCEDURE', l_proc);
43 hr_utility.set_message_token('STEP', '5');
44 end if;
45 --
46 hr_utility.set_location (l_proc, 6);
47 --
48 if p_rec.business_group_id <> per_asn_shd.g_old_rec.business_group_id then
49 l_argument := 'business_group_id';
50 raise l_error;
51 end if;
52 --
53 if p_rec.person_id <> per_asn_shd.g_old_rec.person_id then
54 l_argument := 'person_id';
55 raise l_error;
56 end if;
57 --
58 if p_rec.assessor_person_id <> per_asn_shd.g_old_rec.assessor_person_id then
59 l_argument := 'assessor_person_id';
60 raise l_error;
61 end if;
62 --
63 exception
64 when l_error then
65 hr_api.argument_changed_error
66 (p_api_name => l_proc
67 ,p_argument => l_argument
68 ,p_base_table => per_asn_shd.g_tab_nam);
69 when others then
70 raise;
71 --
72 hr_utility.set_location(' Leaving : '|| l_proc, 10);
73 --
74 end chk_non_updateable_args;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------------------------< chk_assessment_type_id >------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- DESCRIPTION
81 -- ASSESSMENT_TYPE_ID is a foreign key to the table PER_ASSESSMENT_TYPES.
82 -- The value can be updated only if there are no rows in
83 -- PER_COMPETENCE_ELEMENTS for the particular assessment ie. if the
84 -- assessment hasn't been filled in at all (no ratings assigned to the
85 -- competences), then the ASSESSMENT_TYPE_ID can be updated.
86 --
87 -- Also the assessment_date has to be between the date_from and the date_to.
88 --
89 -- PRE-REQUISITES
90 --
91 -- IN PARAMETERS
92 -- assessment_id (chk for referenced rows)
93 -- assessment_type_id
94 -- date_from
95 -- date_to
96 -- business_group_id (as the assesment_type has to belong to the same BG)
97 --
98 -- POST SUCCESS
99 -- Processing continues
100 --
101 -- POST FAILURE
102 -- Processing terminates and a relevent error message is displayed.
103 --
104 -- ACCESS STATUS
105 -- Internal Development Use Only
106 --
107 Procedure chk_assessment_type_id
108 (p_assessment_id in per_assessments.assessment_id%TYPE
109 ,p_assessment_type_id in per_assessments.assessment_type_id%TYPE
110 ,p_assessment_date in per_assessments.assessment_date%TYPE
111 ,p_business_group_id in per_assessments.business_group_id%TYPE
112 ,p_object_version_number in per_assessments.object_version_number%TYPE
113 )
114 is
115 --
116 l_proc varchar2(72):=g_package||'chk_assessment_type_id';
117 l_exists varchar2(1);
118 l_api_updating boolean;
119 --
120 -- DEFINE CURSORS and the variable to hold results of fetch (if any)
121 --
122 cursor csr_ass_used_in_comp is
123 select null
124 from per_competence_elements
125 where p_assessment_id = assessment_id;
126 --
127 cursor csr_ast_date_from_date_to is
128 select date_from , date_to
129 from per_assessment_types
130 where p_assessment_type_id = assessment_type_id
131 and p_business_group_id = business_group_id;
132 --
133 l_ast_date_from per_assessment_types.date_from%TYPE;
134 l_ast_date_to per_assessment_types.date_to%TYPE;
135 --
136 begin
137 hr_utility.set_location('Entering:'|| l_proc, 1);
138 hr_utility.set_location('assessment_type_id = '|| p_assessment_type_id,1);
139 --
140 -- Processing continues if :
141 -- a) The row is being inserted.
142 -- b) The row is being updated and
143 -- 1: The new value is different then the old value
144 --
145 l_api_updating := per_asn_shd.api_updating
146 (p_assessment_id => p_assessment_id
147 ,p_object_version_number => p_object_version_number
148 );
149 --
150 if ((l_api_updating and nvl(per_asn_shd.g_old_rec.assessment_type_id,
151 hr_api.g_number)
152 <> nvl(p_assessment_type_id, hr_api.g_number))
153 or (not l_api_updating)) then
154 --
155 -- If updating, check whether rows exist in PER_COMPETENCE_ELEMENTS
156 --
157 if (l_api_updating) then
158 --
159 open csr_ass_used_in_comp;
160 fetch csr_ass_used_in_comp into l_exists;
161 --
162 if csr_ass_used_in_comp%found then
163 --
164 close csr_ass_used_in_comp;
165 hr_utility.set_message(801, 'HR_51582_ASN_USED_IN_COMP_ELE');
166 hr_utility.raise_error;
167 --
168 end if;
169 --
170 close csr_ass_used_in_comp;
171 --
172 end if;
173 --
174 --
175 /*
176 --
177 open csr_ast_date_from_date_to;
178 fetch csr_ast_date_from_date_to into l_ast_date_from , l_ast_date_to;
179 --
180 if csr_ast_date_from_date_to%notfound then
181 --
182 close csr_ast_date_from_date_to;
183 --
184 per_asn_shd.constraint_error
185 (p_constraint_name => 'PER_ASSESSMENTS_FK1');
186 --
187 end if;
188 --
189 close csr_ast_date_from_date_to;
190 */
191 --
192 -- For insert and update, check whether the assessment_type is active
193 --
194 if (((p_assessment_date < l_ast_date_from) and (l_ast_date_from is not null)) or ((p_assessment_date > l_ast_date_to) and (l_ast_date_to is not null))) then
195 --
196 hr_utility.set_message(801, 'HR_51584_ASN_AST_IS_INACTIVE');
197 hr_utility.raise_error;
198 --
199 end if;
200 --
201 end if;
202 --
203 hr_utility.set_location('Leaving:'|| l_proc, 1);
204
205 EXCEPTION
206
207 when app_exception.application_exception then
208 if hr_multi_message.exception_add
209 (p_associated_column1 => 'PER_ASSESSMENTS.ASSESSMENT_ID'
210 ) then
211 raise;
212 end if;
213
214 end chk_assessment_type_id;
215 --
216 -- ----------------------------------------------------------------------------
217 -- |--------------------------< chk_assessment_date >-------------------------|
218 -- ----------------------------------------------------------------------------
219 --
220 -- DESCRIPTION
221 -- ASSESSMENT_DATE cannot be NULL
222 --
223 -- PRE-REQUISITES
224 --
225 -- IN PARAMETERS
226 -- assessment_date
227 --
228 -- POST SUCCESS
229 -- Processing continues
230 --
231 -- POST FAILURE
232 -- Processing terminates
233 --
234 -- ACCESS STATUS
235 -- Internal Development Use Only
236 --
237 Procedure chk_assessment_date
238 (p_assessment_date in per_assessments.assessment_date%TYPE
239 ,p_assessment_id in per_assessments.assessment_id%TYPE
240 ,p_object_version_number in per_assessments.object_version_number%TYPE
241 )
242 is
243 --
244 l_api_updating boolean;
245 l_proc varchar2(72):=g_package||'chk_assessment_date';
246 --
247 begin
248 hr_utility.set_location('Entering:'|| l_proc, 1);
249 l_api_updating := per_asn_shd.api_updating
250 (p_assessment_id => p_assessment_id
251 ,p_object_version_number => p_object_version_number
252 );
253
254 --
255 if ((l_api_updating and nvl(per_asn_shd.g_old_rec.assessment_date,
256 hr_api.g_date)
257 <> nvl(p_assessment_date, hr_api.g_date))
258 or (not l_api_updating)) then
259
260 if (p_assessment_date is NULL) then
261 hr_utility.set_message(801, 'HR_51784_ASN_DATE_NULL');
262 hr_utility.raise_error;
263 end if;
264 end if;
265 hr_utility.set_location('Leaving:'|| l_proc, 1);
266
267 EXCEPTION
268
269 when app_exception.application_exception then
270 if hr_multi_message.exception_add
271 (p_associated_column1 => 'PER_ASSESSMENTS.ASSESSMENT_DATE'
272 ) then
273 raise;
274 end if;
275
276 end chk_assessment_date;
277 -- ----------------------------------------------------------------------------
278 -- |-----------------------------< chk_person_id >----------------------------|
279 -- ----------------------------------------------------------------------------
280 --
281 -- DESCRIPTION
282 -- PERSON_ID must be of the same business group and must exist on the
283 -- assessment date. More rules to come
284 --
285 -- PRE-REQUISITES
286 --
287 -- IN PARAMETERS
288 -- person_id
289 -- business_group_id
290 -- assessment_date
291 --
292 -- POST SUCCESS
293 -- Processing continues
294 --
295 -- POST FAILURE
296 -- Processing terminates
297 --
298 -- ACCESS STATUS
299 -- Internal Development Use Only
300 --
301 Procedure chk_person_id
302 (p_assessment_id in per_assessments.assessment_id%TYPE
303 ,p_person_id in per_assessments.person_id%TYPE
304 ,p_business_group_id in per_assessments.business_group_id%TYPE
305 ,p_assessment_date in per_assessments.assessment_date%TYPE
306 ,p_object_version_number in per_assessments.object_version_number%TYPE
307 )
308 is
309 --
310 l_proc varchar2(72):=g_package||'chk_person_id';
311 --
312 -- Define cursors and their necessary variables
313 --
314 cursor csr_chk_person_sta_date is
315 select distinct(min(effective_start_date)), business_group_id
316 from per_all_people_f per
317 where per.person_id = p_person_id
318 group by business_group_id;
319 --
320 l_ASN_PERS_STA_DATE per_people_f.start_date%TYPE;
321 l_ASN_PERS_BG per_people_f.business_group_id%TYPE;
322 --
323 l_api_updating boolean;
324 --
325 begin
326 hr_utility.set_location('Entering:'|| l_proc, 1);
327 --
328
329 open csr_chk_person_sta_date;
330 fetch csr_chk_person_sta_date into l_ASN_PERS_STA_DATE, l_ASN_PERS_BG;
331 --
332 if (csr_chk_person_sta_date%notfound or l_ASN_PERS_STA_DATE is null) then
333 --
334 close csr_chk_person_sta_date;
335 --
336 -- raise an error as the person_id doesn't exist
337 --
338 hr_utility.set_message(801, 'HR_51586_ASN_PER_NOT_EXIST');
339 hr_utility.raise_error;
340 --
341 end if;
342 close csr_chk_person_sta_date;
343 --
344 -- The person has to be in the correct business group
345 --
346 if (l_ASN_PERS_BG <> p_business_group_id) then
347 --
348 -- raise an error as the person is in the wrong business_group
349 --
350 hr_utility.set_message(801, 'HR_51806_ASN_PER_NOT_BG');
351 hr_utility.raise_error;
352 --
353 end if;
354 --
355 -- The assessment_date has to be on or after the person start date
356 --
357 if (p_assessment_date < l_ASN_PERS_STA_DATE) then
358 --
359 hr_utility.set_message(801, 'HR_51587_ASN_PER_NOT_EXIST_DA');
360 hr_utility.raise_error;
361 --
362 end if;
363 --
364 hr_utility.set_location('Leaving:'|| l_proc, 1);
365
366 EXCEPTION
367
368 when app_exception.application_exception then
369 if hr_multi_message.exception_add
370 (p_associated_column1 => 'PER_ASSESSMENTS.PERSON_ID'
371 ) then
372 raise;
373 end if;
374
375 end chk_person_id;
376 --
377 -- ----------------------------------------------------------------------------
378 -- |----------------------< chk_assessor_person_id >--------------------------|
379 -- ----------------------------------------------------------------------------
380 --
381 -- DESCRIPTION
382 -- ASSESSOR_PERSON_ID must be of the same business group and must exist on
383 -- the assessment date.
384 --
385 -- PRE-REQUISITES
386 --
387 -- IN PARAMETERS
388 -- assessor_person_id
389 -- business_group_id
390 -- assessment_date
391 --
392 -- POST SUCCESS
393 -- Processing continues
394 --
395 -- POST FAILURE
396 -- Processing terminates
397 --
398 -- ACCESS STATUS
399 -- Internal Development Use Only
400 --
401 Procedure chk_assessor_person_id
402 (p_assessor_person_id in per_assessments.assessor_person_id%TYPE
403 ,p_business_group_id in per_assessments.business_group_id%TYPE
404 ,p_assessment_date in per_assessments.assessment_date%TYPE
405 ,p_assessment_id in per_assessments.assessment_id%TYPE
406 ,p_object_version_number in per_assessments.object_version_number%TYPE
407 )
408 is
409 --
410 l_proc varchar2(72):=g_package||'chk_assessor_person_id';
411 --
412 lv_cross_business_group varchar2(10); -- bug 1980440 fix
413 --
414 cursor csr_chk_assessper_sta_date is
415 select distinct(min(effective_start_date)), business_group_id
416 from per_all_people_f per
417 where per.person_id = p_assessor_person_id
418 group by business_group_id;
419 -- Fix 3122878. Using per_all_people_f instead of per_people_f.
420 --
421 l_ASN_ASSPERS_STA_DATE per_people_f.start_date%TYPE;
422 l_ASN_ASSPERS_BG per_people_f.business_group_id%TYPE;
423 --
424 begin
425 hr_utility.set_location('Entering:'|| l_proc, 1);
426
427 --
428 -- Tests are carried out on insert, and update (even if values haven't changed)
429 -- as data in the referenced table may have.
430 --
431 open csr_chk_assessper_sta_date;
432 fetch csr_chk_assessper_sta_date into l_ASN_ASSPERS_STA_DATE,l_ASN_ASSPERS_BG;
433 --
434 if (csr_chk_assessper_sta_date%notfound or l_ASN_ASSPERS_STA_DATE is NULL) then
435 --
436 close csr_chk_assessper_sta_date;
437 --
438 -- raise an error as the person_id doesn't exist
439 --
440 hr_utility.set_message(801, 'HR_51588_ASN_ASSPER_NOT_EXIST');
441 hr_utility.raise_error;
442 --
443 end if;
444 close csr_chk_assessper_sta_date;
445 --
446 -- The person has to be in the correct business group
447 --
448 -- bug 1980440 fix starts
449 -- if CROSS_BUSINESS_GROUP option is enabled we shouldn't do a comparison
453 if lv_cross_business_group <> 'Y' THEN
450 -- between Assessment BG and Assessor BG as they may be different
451 lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
452
454 if (l_ASN_ASSPERS_BG <> p_business_group_id) then
455 --
456 -- raise an error as the person is in the wrong business_group
457 --
458 hr_utility.set_message(801, 'HR_51808_ASN_ASSPER_NOT_BG');
459 hr_utility.raise_error;
460 --
461 end if;
462 end if;
463 -- bug 1980440 fix ends
464
465 -- The assessment_date has to be on or after the assessors start date
466 --
467 if (p_assessment_date < l_ASN_ASSPERS_STA_DATE) then
468 --
469 hr_utility.set_message(801, 'HR_51589_ASN_ASSPER_NO_XIST_DA');
470 hr_utility.raise_error;
471 --
472 end if;
473 --
474 hr_utility.set_location('Leaving:'|| l_proc, 1);
475 --
476 EXCEPTION
477
478 when app_exception.application_exception then
479 if hr_multi_message.exception_add
480 (p_associated_column1 => 'PER_ASSESSMENTS.ASSESSOR_PERSON_ID'
481 ) then
482 raise;
483 end if;
484
485 end chk_assessor_person_id;
486 --
487 -- ----------------------------------------------------------------------------
488 -- |----------------------< chk_group_date_id >--------------------------------|
489 -- ----------------------------------------------------------------------------
490 --
491 -- DESCRIPTION
492 -- If the GROUP_INITIATOR_ID is not null, the GROUP_DATE must also be not null
493 -- and vica versa.
494 --
495 -- PRE-REQUISITES
496 --
497 -- IN PARAMETERS
498 -- group_initiator_id
499 -- group_date
500 --
501 -- POST SUCCESS
502 -- Processing continues
503 --
504 -- POST FAILURE
505 -- Processing terminates
506 --
507 -- ACCESS STATUS
508 -- Internal Development Use Only
509 --
510 Procedure chk_group_date_id
511 (p_group_initiator_id in per_assessments.group_initiator_id%TYPE
512 ,p_group_date in per_assessments.group_date%TYPE
513 ,p_assessment_id in per_assessments.assessment_id%TYPE
514 ,p_object_version_number in per_assessments.object_version_number%TYPE
515 )
516 is
517 --
518 l_api_updating boolean;
519 l_proc varchar2(72):=g_package||'chk_group_date_id';
520 --
521 --
522 begin
523 hr_utility.set_location('Entering:'|| l_proc, 1);
524 --
525 -- Tests are carried out on insert only.
526 --
527 --
528 l_api_updating := per_asn_shd.api_updating
529 (p_assessment_id => p_assessment_id
530 ,p_object_version_number => p_object_version_number
531 );
532
533 if ((l_api_updating and nvl(per_asn_shd.g_old_rec.group_initiator_id,
534 hr_api.g_number)
535 <> nvl(p_group_initiator_id, hr_api.g_number))
536 or (not l_api_updating)) then
537
538 If ((p_group_initiator_id is not null And p_group_date is null) Or
539 (p_group_initiator_id is null And p_group_date is not null)) Then
540 --
541 -- raise an error as the either both should exist or neither should.
542 --
543 hr_utility.set_message(801, 'HR_52308_CM_GPR_DATE_ID_PROB');
544 hr_utility.raise_error;
545 --
546 end if;
547 --
548 end if;
549 hr_utility.set_location('Leaving:'|| l_proc, 2);
550 --
551 EXCEPTION
552
553 when app_exception.application_exception then
554 if hr_multi_message.exception_add
555 (p_associated_column1 => 'PER_ASSESSMENTS.GROUP_INITIATOR_ID'
556 ,p_associated_column2 => 'PER_ASSESSMENTS.P_GROUP_DATE'
557 ) then
558 raise;
559 end if;
560
561 end chk_group_date_id;
562 --
563 --
567 --
564 -- ----------------------------------------------------------------------------
565 -- |----------------------< chk_group_initiator_id >--------------------------|
566 -- ----------------------------------------------------------------------------
568 -- DESCRIPTION
569 -- GROUP_INITIATOR_ID must be of the same business group and must exist on
570 -- the group_date.
571 --
572 -- PRE-REQUISITES
573 --
574 -- IN PARAMETERS
575 -- group_initiator_id
576 -- business_group_id
577 -- group_date
578 --
579 -- POST SUCCESS
580 -- Processing continues
581 --
582 -- POST FAILURE
583 -- Processing terminates
584 --
585 -- ACCESS STATUS
586 -- Internal Development Use Only
587 --
588 Procedure chk_group_initiator_id
589 (p_group_initiator_id in per_assessments.group_initiator_id%TYPE
590 ,p_business_group_id in per_assessments.business_group_id%TYPE
591 ,p_group_date in per_assessments.group_date%TYPE
592 ,p_assessment_id in per_assessments.assessment_id%TYPE
593 ,p_object_version_number in per_assessments.object_version_number%TYPE
594 )
595 is
596 --
597 l_api_updating boolean;
598 l_proc varchar2(72):=g_package||'chk_group_initiator_id';
599 --
600 cursor csr_chk_grp_per_sta_date is
601 select distinct(min(effective_start_date)), business_group_id
602 from per_all_people_f per
603 where per.person_id = p_group_initiator_id
604 group by business_group_id;
605 --
606 l_asn_grp_pers_sta_date per_people_f.start_date%TYPE;
607 l_asn_grp_pers_bg per_people_f.business_group_id%TYPE;
608 --
609 begin
610 hr_utility.set_location('Entering:'|| l_proc, 1);
611 --
612 l_api_updating := per_asn_shd.api_updating
613 (p_assessment_id => p_assessment_id
614 ,p_object_version_number => p_object_version_number
615 );
616
617 --
618 -- Tests are carried out on insert only.
619 --
620 if ((l_api_updating and nvl(per_asn_shd.g_old_rec.group_initiator_id,
621 hr_api.g_number)
622 <> nvl(p_group_initiator_id, hr_api.g_number))
623 or (not l_api_updating)) then
624
625 IF p_group_initiator_id is not null THEN
626 --
627 open csr_chk_grp_per_sta_date;
628 fetch csr_chk_grp_per_sta_date into l_asn_grp_pers_sta_date,l_asn_grp_pers_bg;
629 --
630 if (csr_chk_grp_per_sta_date%notfound or l_asn_grp_pers_sta_date IS NULL) then
631 --
632 close csr_chk_grp_per_sta_date;
633 --
634 -- raise an error as the person_id doesn't exist
635 --
636 hr_utility.set_message(801, 'HR_52305_ASN_GRPPER_NOT_EXIST');
637 hr_utility.raise_error;
638 --
639 end if;
640 close csr_chk_grp_per_sta_date;
641 --
642 -- The person has to be in the correct business group
643 --
644 IF (l_asn_grp_pers_bg <> p_business_group_id) THEN
645 --
646 -- raise an error as the person is in the wrong business_group
647 --
648 hr_utility.set_message(801, 'HR_52306_ASN_GRPPER_NOT_BG');
649 hr_utility.raise_error;
650 --
651 END IF;
652 --
653 -- The group_date has to be on or after the group initiators start date
654 --
655 IF (p_group_date < l_asn_grp_pers_sta_date) then
656 --
657 hr_utility.set_message(801, 'HR_52307_ASN_GRPPER_NO_XIST_DA');
658 hr_utility.raise_error;
659 --
660 END IF;
661 --
662 END IF;
663 end if;
664 --
665 hr_utility.set_location('Leaving:'|| l_proc, 1);
666 --
667 EXCEPTION
668
669 when app_exception.application_exception then
670 if hr_multi_message.exception_add
671 (p_associated_column1 => 'PER_ASSESSMENTS.GROUP_INITIATOR_ID'
672 ) then
673 raise;
674 end if;
675
676 end chk_group_initiator_id;
677 --
678 --
679 -- ----------------------------------------------------------------------------
680 -- |-----------------------------< chk_status >-----------------------------|
681 -- ----------------------------------------------------------------------------
682 --
683 -- DESCRIPTION
684 -- The status flag is based on a user-defined lookup,
685 -- APPRAISAL_ASSESSMENT_STATUS. At the moment there is no validation on
686 -- this (only that the value exists in hr-lookups)
687 -- it can be null
688 --
689 -- PRE-REQUISITES
690 --
691 -- IN PARAMETERS
692 -- p_effective_date
693 -- POST SUCCESS
694 -- Processing continues
695 --
696 -- POST FAILURE
697 -- Prcessing halts and a suitable error is raised.
698 -- ACCESS STATUS
699 -- Internal Development Use Only
700 --
701 Procedure chk_status
702 (p_status in per_assessments.status%TYPE
703 ,p_effective_date in date
704 ,p_assessment_id in per_assessments.assessment_id%TYPE
705 ,p_object_version_number in per_assessments.object_version_number%TYPE
706 )
707 is
708 --
709 l_api_updating boolean;
710 l_proc varchar2(72):=g_package||'chk_status';
711 --
712 begin
713 hr_utility.set_location('Entering:'|| l_proc, 1);
717 );
714 l_api_updating := per_asn_shd.api_updating
715 (p_assessment_id => p_assessment_id
716 ,p_object_version_number => p_object_version_number
718 if ((l_api_updating and nvl(per_asn_shd.g_old_rec.status,
719 hr_api.g_varchar2)
720 <> nvl(p_status, hr_api.g_varchar2))
721 or (not l_api_updating)) then
722
723 --
724 -- Check that the value in p_status exist in hr_lookups
725 --
726 if p_status is not Null Then
727 hr_utility.set_location(l_proc||':Value>'||p_status, 5);
728 if hr_api.not_exists_in_hr_lookups
729 (p_effective_date => p_effective_date
730 ,p_lookup_type => 'APPRAISAL_ASSESSMENT_STATUS'
731 ,p_lookup_code => p_status
732 ) then
733 hr_utility.set_location(l_proc, 10);
734 hr_utility.set_message(801,'HR_51585_ASN_COMPLETE_INVAL');
735 hr_utility.raise_error;
736 end if;
737 end if;
738 end if;
739 --
740 hr_utility.set_location('Leaving:'|| l_proc, 100);
741 EXCEPTION
742
743 when app_exception.application_exception then
744 if hr_multi_message.exception_add
745 (p_associated_column1 => 'PER_ASSESSMENTS.STATUS'
746 ) then
747 raise;
748 end if;
749
750
751 end chk_status;
752 -- ----------------------------------------------------------------------------
753 -- |------------------------< CHK_ASSESSMENT_PERIOD >-------------------------|
754 -- ----------------------------------------------------------------------------
755 --
756 -- DESCRIPTION
757 -- Perform check to make sure that :
758 -- 1) If the ASSESSMENT_PERIOD_END exists, the ASSESSMENT_PERIOD_START
759 -- date also exists
760 -- 2) The ASSESSMENT_PERIOD_END is >= ASSESSMENT_PERIOD_START
761 --
762 -- PRE-REQUISITES
763 --
764 -- IN PARAMETERS
765 -- p_assessment_period_start_date
766 -- p_assessment_period_end_date
767 --
768 -- POST SUCCESS
769 -- Processing continues.
770 --
771 -- POST FAILURE
772 -- Processing halts.
773 --
774 -- ACCESS STATUS
775 -- Internal Development Use Only
776 --
777 Procedure chk_assessment_period
778 (p_assessment_period_start_date in per_assessments.assessment_period_start_date%TYPE
779 ,p_assessment_period_end_date in per_assessments.assessment_period_end_date%TYPE
780 ,p_assessment_id in per_assessments.assessment_id%TYPE
781 ,p_object_version_number in per_assessments.object_version_number%TYPE
782 )
783 is
784 --
785 l_api_updating boolean;
786 l_proc varchar2(72):=g_package||'chk_assessment_period';
787 --
788 begin
789 hr_utility.set_location('Entering:'|| l_proc, 1);
790 l_api_updating := per_asn_shd.api_updating
791 (p_assessment_id => p_assessment_id
792 ,p_object_version_number => p_object_version_number
793 );
794
795 --
796 if ((l_api_updating
797 and (nvl(per_asn_shd.g_old_rec.assessment_period_start_date,
798 hr_api.g_date)
799 <> nvl(p_assessment_period_start_date, hr_api.g_date)
800 or nvl(per_asn_shd.g_old_rec.assessment_period_end_date,
801 hr_api.g_date)
802 <> nvl(p_assessment_period_end_date, hr_api.g_date)))
803 or (not l_api_updating)) then
804
805 if (p_assessment_period_end_date is not null) then
806 --
807 -- As end_date <> NULL, start_date becomes mandatory
808 --
809 if (p_assessment_period_start_date is NULL) then
810 --
811 per_asn_shd.constraint_error
812 (p_constraint_name => 'PER_ASSESSMENTS_DATE_END_CHK');
813 --
814 end if;
815 --
816 -- The end date has to be >= the start date, else error.
817 --
818 if (p_assessment_period_start_date > p_assessment_period_end_date) then
819 --
820 per_asn_shd.constraint_error
821 (p_constraint_name => 'PER_ASSESSMENTS_DATE_CHK');
822 --
823 end if;
824 --
825 end if;
826 --
827 end if;
828 EXCEPTION
829
830 when app_exception.application_exception then
831 if hr_multi_message.exception_add
832 (p_associated_column1 => 'PER_ASSESSMENTS.ASSESSMENT_PERIOD_START_DATE'
833 ,p_associated_column2 => 'PER_ASSESSMENTS.ASSESSMENT_PERIOD_END_DATE'
834 ) then
835 raise;
836 end if;
837
838 end chk_assessment_period;
839 --
840 -- ----------------------------------------------------------------------------
841 -- |------------------------< CHK_UNIQUE_COMBINATION >------------------------|
842 -- ----------------------------------------------------------------------------
843 --
844 -- DESCRIPTION
845 -- The fields assessment_type, assessment_date, assessor_person_id, person_id,
846 -- and group_date have to be unique.
847 -- The column ASSESSMENT_GROUP is no longer used for holding the fk to
848 -- the assessment_group, so the column group_date will uniquely identify
849 -- the group.
850 --
851 -- PRE-REQUISITES
852 --
853 -- IN PARAMETERS
854 -- p_assessment_id
855 -- p_assessment_type_id
856 -- p_assessment_date
857 -- p_person_id
858 -- p_assessor_person_id
859 -- p_group_date
863 --
860 --
861 -- POST SUCCESS
862 -- Processing continues
864 -- POST FAILURE
865 -- Processing halts
866 --
867 -- ACCESS STATUS
868 -- Internal Development Use Only
869 --
870 Procedure chk_unique_combination
871 (p_assessment_id in per_assessments.assessment_id%TYPE
872 ,p_assessment_type_id in per_assessments.assessment_type_id%TYPE
873 ,p_assessment_date in per_assessments.assessment_date%TYPE
874 ,p_person_id in per_assessments.person_id%TYPE
875 ,p_assessor_person_id in per_assessments.assessor_person_id%TYPE
876 ,p_group_date in per_assessments.group_date%TYPE
877 ,p_object_version_number in per_assessments.object_version_number%TYPE
878 )
879 is
880 --
881 l_proc varchar2(72):=g_package||'chk_unique_combination';
882 --
883 -- Create a cursor to get duplicate rows
884 --
885 cursor csr_duplicate_rows is
886 select null
887 from per_assessments
888 where assessment_type_id = p_assessment_type_id
889 and assessment_date = p_assessment_date
890 and person_id = p_person_id
891 and assessor_person_id = p_assessor_person_id
892 and group_date = group_date
893 and (assessment_id <> p_assessment_id
894 OR p_assessment_id is NULL);
895 --
896 l_exists varchar2(1);
897 --
898 begin
899 hr_utility.set_location('Entering:'|| l_proc, 1);
900 --
901 -- always check ins upd even if values not changed
902
903
904 --
905 /* Disabling this validation as we now allow multiple assessments
906 to be created by same person on same dates. This is done for new
907 Appraisals build
908 open csr_duplicate_rows;
909 fetch csr_duplicate_rows into l_exists;
910 --
911 if csr_duplicate_rows%found then
912 hr_utility.set_location('Dup.found:'|| l_proc, 3);
913 close csr_duplicate_rows;
914 --
915 per_asn_shd.constraint_error
916 (p_constraint_name => 'PER_ASSESSMENTS_UK1');
917 --
918 end if;
919 close csr_duplicate_rows;
920 */
921 --
922
923 EXCEPTION
924
925 when app_exception.application_exception then
926 if hr_multi_message.exception_add
927 (p_associated_column1 => 'PER_ASSESSMENTS.ASSESSMENT_TYPE_ID'
928 ,p_associated_column2 => 'PER_ASSESSMENTS.ASSESSMENT_DATE'
929 ,p_associated_column3 => 'PER_ASSESSMENTS.PERSON_ID'
930 ,p_associated_column4 => 'PER_ASSESSMENTS.ASSESSOR_PERSON_ID'
931 ) then
932 raise;
933 end if;
934
935 end chk_unique_combination;
936 --
937 -- ----------------------------------------------------------------------------
938 -- |-----------------------< CHK_ASSESSMENT_GROUP_ID >------------------------|
939 -- ----------------------------------------------------------------------------
940 --
941 -- DESCRIPTION
942 -- ASSESSMENT_GROUP is a foreign key to the table PER_ASSESSMENT_GROUPS.
943 -- If ASSESSMENT_GROUP is NOT NULL, then the value must exist in the
944 -- referenced table with the same business group.
945 -- PRE-REQUISITES
946 --
947 -- IN PARAMETERS
948 -- p_assessment_group_id
949 -- p_business_group_id
950 -- POST SUCCESS
951 -- Processing continues
952 -- POST FAILURE
953 -- Processing halts
954 -- ACCESS STATUS
955 -- Internal Development Use Only
956 --
957 Procedure chk_assessment_group_id
958 (p_assessment_group_id in per_assessments.assessment_group_id%TYPE
959 ,p_business_group_id in per_assessments.business_group_id%TYPE
960 ,p_assessment_id in per_assessments.assessment_id%TYPE
961 ,p_object_version_number in per_assessments.object_version_number%TYPE
962 )
963 is
964
965 --
966 l_api_updating boolean;
967 l_proc varchar2(72):=g_package||'chk_assessment_group_id';
968 --
969 -- Define the necessary cursor
970 --
971 cursor csr_chk_ass_group_id is
972 select null
973 from per_assessment_groups
974 where assessment_group_id = p_assessment_group_id
975 and business_group_id = p_business_group_Id;
976
977 l_exists varchar2(1);
978
979 begin
980 hr_utility.set_location('Entering:'|| l_proc, 1);
981 l_api_updating := per_asn_shd.api_updating
982 (p_assessment_id => p_assessment_id
983 ,p_object_version_number => p_object_version_number
984 );
985 if ((l_api_updating and nvl(per_asn_shd.g_old_rec.assessment_group_id,
986 hr_api.g_number)
987 <> nvl(p_assessment_group_id, hr_api.g_number))
988 or (not l_api_updating)) then
989
990 --
991 if (p_assessment_group_id is not null) then
992 hr_utility.set_location('assessment_group_id must be NULL:'|| l_proc, 1);
993 --
994 open csr_chk_ass_group_id;
995 fetch csr_chk_ass_group_id into l_exists;
996 --
997 -- If the group isn't in the referenced table, raise an error
998 --
999 hr_utility.set_location('assessment_group_id :'|| p_assessment_group_id, 1);
1000 if (csr_chk_ass_group_id%notfound) then
1001 close csr_chk_ass_group_id;
1002 --
1003 per_asn_shd.constraint_error
1007 close csr_chk_ass_group_id;
1004 (p_constraint_name => 'PER_ASSESSMENTS_FK3');
1005 --
1006 end if;
1008 end if;
1009 end if;
1010 --
1011 EXCEPTION
1012
1013 when app_exception.application_exception then
1014 if hr_multi_message.exception_add
1015 (p_associated_column1 => 'PER_ASSESSMENTS.ASSESSMENT_GROUP_ID'
1016 ) then
1017 raise;
1018 end if;
1019
1020 end chk_assessment_group_id;
1021 --
1022 -- ----------------------------------------------------------------------------
1023 -- |--------------------------< CHK_APPRAISAL_ID >---------------------------|
1024 -- ----------------------------------------------------------------------------
1025 --
1026 -- DESCRIPTION
1027 -- APPRAISAL_ID is a foreign key to the table PER_APPRAISALS.
1028 -- If APPRAISAL_ID is NOT NULL, then the value must exist in the
1029 -- referenced table with the same business group.
1030 -- PRE-REQUISITES
1031 --
1032 -- IN PARAMETERS
1033 -- p_appraisal_id
1034 -- p_business_group_id
1035 --
1036 -- POST SUCCESS
1037 -- Processing continues
1038 -- POST FAILURE
1039 -- Processing halts
1040 -- ACCESS STATUS
1041 -- Internal Development Use Only
1042 --
1043 Procedure chk_appraisal_id
1044 (p_appraisal_id in per_assessments.assessment_group_id%TYPE
1045 ,p_business_group_id in per_assessments.business_group_id%TYPE
1046 ,p_assessment_id in per_assessments.assessment_id%TYPE
1047 ,p_object_version_number in per_assessments.object_version_number%TYPE
1048 )
1049 is
1050
1051 --
1052 l_api_updating boolean;
1053 l_proc varchar2(72):=g_package||'chk_appraisal_id';
1054 --
1055 -- Define the necessary cursor
1056 --
1057 cursor csr_chk_appraisal_id is
1058 select null
1059 from per_appraisals
1060 where appraisal_id = p_appraisal_id
1061 and business_group_id = p_business_group_id;
1062
1063 l_exists varchar2(1);
1064
1065 begin
1066 hr_utility.set_location('Entering:'|| l_proc, 1);
1067 l_api_updating := per_asn_shd.api_updating
1068 (p_assessment_id => p_assessment_id
1069 ,p_object_version_number => p_object_version_number
1070 );
1071 if ((l_api_updating and nvl(per_asn_shd.g_old_rec.appraisal_id,
1072 hr_api.g_number)
1073 <> nvl(p_appraisal_id, hr_api.g_number))
1074 or (not l_api_updating)) then
1075
1076 --
1077 if (p_appraisal_id is NOT NULL) then
1078 --
1079 open csr_chk_appraisal_id;
1080 fetch csr_chk_appraisal_id into l_exists;
1081 --
1082 -- If the group isn't in the referenced table, raise an error
1083 --
1084 if (csr_chk_appraisal_id%notfound) then
1085 close csr_chk_appraisal_id;
1086 --
1087 per_asn_shd.constraint_error
1088 (p_constraint_name => 'PER_ASSESSMENTS_FK4');
1089 --
1090 end if;
1091 close csr_chk_appraisal_id;
1092 end if;
1093 --
1094 end if;
1095 EXCEPTION
1096
1097 when app_exception.application_exception then
1098 if hr_multi_message.exception_add
1099 (p_associated_column1 => 'PER_ASSESSMENTS.ASSESSMENT_GROUP_ID'
1100 ) then
1101 raise;
1102 end if;
1103
1104 end chk_appraisal_id;
1105 -- ----------------------------------------------------------------------------
1106 -- |---------------------------< CHK_COMP_ELEMENTS >--------------------------|
1107 -- ----------------------------------------------------------------------------
1108 --
1109 -- DESCRIPTION
1110 -- Used to validate before the delete. This process checks to make sure that
1111 -- no rows exist in the PER_COMPETENCE_ELEMENTS table
1112 -- PRE-REQUISITES
1113 --
1114 -- IN PARAMETERS
1115 -- p_assessment_id
1116 -- p_object_version_number
1117 -- POST SUCCESS
1118 -- Processing continues
1119 -- POST FAILURE
1120 -- Processing fails.
1121 -- ACCESS STATUS
1122 -- Internal Development Use Only
1123 Procedure chk_comp_elements
1124 (p_assessment_id in per_assessments.assessment_id%TYPE
1125 ,p_object_version_number in per_assessments.object_version_number%TYPE
1126 ) is
1127 --
1128 l_proc varchar2(72):=g_package||'chk_comp_elements';
1129 --
1130 cursor csr_comp_elements_usage is
1131 select null
1132 from per_competence_elements
1133 where assessment_id = p_assessment_id;
1134 --
1135 l_exists varchar2(1);
1136 --
1137 begin
1138 hr_utility.set_location('Entering:'|| l_proc, 1);
1139 --
1140 -- Check that the assessment is not referenced by a competence element
1141 --
1145 close csr_comp_elements_usage;
1142 open csr_comp_elements_usage;
1143 fetch csr_comp_elements_usage into l_exists;
1144 if csr_comp_elements_usage%found then
1146 --
1147 hr_utility.set_location(l_proc,5);
1148 hr_utility.set_message (801, 'HR_51812_ASN_REF_BY_COMP');
1149 hr_utility.raise_error;
1150 --
1151 end if;
1152 close csr_comp_elements_usage;
1153 --
1154 EXCEPTION
1155
1156 when app_exception.application_exception then
1157 if hr_multi_message.exception_add
1158 (p_associated_column1 => 'PER_ASSESSMENTS.ASSESSMENT_ID'
1159 ) then
1160 raise;
1161 end if;
1162 end chk_comp_elements;
1163 --
1164 -- -----------------------------------------------------------------------
1165 -- |------------------------------< chk_df >-----------------------------|
1166 -- -----------------------------------------------------------------------
1167 --
1168 -- Description:
1169 -- Validates the all Descriptive Flexfield values.
1170 --
1171 -- Pre-conditions:
1172 -- All other columns have been validated. Must be called as the
1173 -- last step from insert_validate and update_validate.
1174 --
1175 -- In Arguments:
1176 -- p_rec
1177 --
1178 -- Post Success:
1179 -- If the Descriptive Flexfield structure column and data values are
1180 -- all valid this procedure will end normally and processing will
1181 -- continue.
1182 --
1183 -- Post Failure:
1184 -- If the Descriptive Flexfield structure column value or any of
1185 -- the data values are invalid then an application error is raised as
1186 -- a PL/SQL exception.
1187 --
1188 -- Access Status:
1189 -- Internal Row Handler Use Only.
1190 --
1191 procedure chk_df
1192 (p_rec in per_asn_shd.g_rec_type) is
1193 --
1194 l_proc varchar2(72) := g_package||'chk_df';
1195 --
1196 begin
1197 hr_utility.set_location('Entering:'||l_proc, 10);
1198 --
1199 if ((p_rec.assessment_id is not null) and (
1200 nvl(per_asn_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1201 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1202 nvl(per_asn_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1203 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1204 nvl(per_asn_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1205 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1206 nvl(per_asn_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1207 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1208 nvl(per_asn_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1209 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1210 nvl(per_asn_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1211 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1212 nvl(per_asn_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1213 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1217 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1214 nvl(per_asn_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1215 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1216 nvl(per_asn_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1218 nvl(per_asn_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1219 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1220 nvl(per_asn_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1221 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1222 nvl(per_asn_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1223 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1224 nvl(per_asn_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1225 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1226 nvl(per_asn_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1227 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1228 nvl(per_asn_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1229 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1230 nvl(per_asn_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1231 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1232 nvl(per_asn_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1233 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1234 nvl(per_asn_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1235 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1236 nvl(per_asn_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1237 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1238 nvl(per_asn_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1239 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1240 nvl(per_asn_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1241 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1242 or
1243 (p_rec.assessment_id is null) then
1244 --
1245 -- Only execute the validation if absolutely necessary:
1246 -- a) During update, the structure column value or any
1247 -- of the attribute values have actually changed.
1248 -- b) During insert.
1249 --
1250 hr_dflex_utility.ins_or_upd_descflex_attribs
1251 (p_appl_short_name => 'PER'
1252 ,p_descflex_name => 'PER_ASSESSMENTS'
1253 ,p_attribute_category => p_rec.attribute_category
1254 ,p_attribute1_name => 'ATTRIBUTE1'
1255 ,p_attribute1_value => p_rec.attribute1
1256 ,p_attribute2_name => 'ATTRIBUTE2'
1257 ,p_attribute2_value => p_rec.attribute2
1258 ,p_attribute3_name => 'ATTRIBUTE3'
1259 ,p_attribute3_value => p_rec.attribute3
1260 ,p_attribute4_name => 'ATTRIBUTE4'
1261 ,p_attribute4_value => p_rec.attribute4
1262 ,p_attribute5_name => 'ATTRIBUTE5'
1263 ,p_attribute5_value => p_rec.attribute5
1264 ,p_attribute6_name => 'ATTRIBUTE6'
1265 ,p_attribute6_value => p_rec.attribute6
1266 ,p_attribute7_name => 'ATTRIBUTE7'
1267 ,p_attribute7_value => p_rec.attribute7
1268 ,p_attribute8_name => 'ATTRIBUTE8'
1269 ,p_attribute8_value => p_rec.attribute8
1270 ,p_attribute9_name => 'ATTRIBUTE9'
1271 ,p_attribute9_value => p_rec.attribute9
1272 ,p_attribute10_name => 'ATTRIBUTE10'
1273 ,p_attribute10_value => p_rec.attribute10
1274 ,p_attribute11_name => 'ATTRIBUTE11'
1275 ,p_attribute11_value => p_rec.attribute11
1276 ,p_attribute12_name => 'ATTRIBUTE12'
1277 ,p_attribute12_value => p_rec.attribute12
1278 ,p_attribute13_name => 'ATTRIBUTE13'
1279 ,p_attribute13_value => p_rec.attribute13
1280 ,p_attribute14_name => 'ATTRIBUTE14'
1281 ,p_attribute14_value => p_rec.attribute14
1282 ,p_attribute15_name => 'ATTRIBUTE15'
1283 ,p_attribute15_value => p_rec.attribute15
1284 ,p_attribute16_name => 'ATTRIBUTE16'
1285 ,p_attribute16_value => p_rec.attribute16
1286 ,p_attribute17_name => 'ATTRIBUTE17'
1287 ,p_attribute17_value => p_rec.attribute17
1288 ,p_attribute18_name => 'ATTRIBUTE18'
1289 ,p_attribute18_value => p_rec.attribute18
1290 ,p_attribute19_name => 'ATTRIBUTE19'
1291 ,p_attribute19_value => p_rec.attribute19
1292 ,p_attribute20_name => 'ATTRIBUTE20'
1293 ,p_attribute20_value => p_rec.attribute20
1294 );
1295 end if;
1296 --
1297 hr_utility.set_location(' Leaving:'||l_proc, 20);
1298
1299 end chk_df;
1300 --
1301 -- ----------------------------------------------------------------------------
1302 -- |---------------------------< insert_validate >----------------------------|
1303 -- ----------------------------------------------------------------------------
1304 Procedure insert_validate
1305 (p_rec in per_asn_shd.g_rec_type
1309 --
1306 ,p_effective_date in date) is
1307 --
1308 l_proc varchar2(72) := g_package||'insert_validate';
1310 Begin
1311 hr_utility.set_location('Entering:'||l_proc, 5);
1312 --
1313 -- Call all supporting business operations. Mapping to the appropiate
1314 -- Business Rules in perasn.bru is provided.
1315 --
1316 -- VALIDATE BUSINESS_GROUP_ID
1317 -- Business Rule Mapping
1318 -- =====================
1319 -- Rule CHK_BUSINESS_GROUP_ID a
1320 --
1321 hr_api.validate_bus_grp_id(p_rec.business_group_id
1322 ,p_associated_column1 => per_asn_shd.g_tab_nam ||
1323 '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1324
1325
1326 hr_multi_message.end_validation_set;
1327 --
1328 -- VALIDATE ASSESSMENT_DATE
1329 -- Business Rule Mapping
1330 -- =====================
1331 -- Rule CHK_ASSESSMENT_DATE a)
1332 --
1333 per_asn_bus.chk_assessment_date
1334 (p_assessment_date => p_rec.assessment_date
1335 ,p_assessment_id => p_rec.assessment_id
1336 ,p_object_version_number => p_rec.object_version_number
1337 );
1338
1339 --
1340 -- VALIDATE STATUS
1341 -- Business Rule Mapping
1342 -- =====================
1343 -- Rule CHK_STATUS a)
1344 --
1345 per_asn_bus.chk_status
1346 (p_status => p_rec.status
1347 ,p_effective_date => p_effective_date
1348 ,p_assessment_id => p_rec.assessment_id
1349 ,p_object_version_number => p_rec.object_version_number
1350 );
1351 --
1352 --
1353 -- VALIDATE CHK_ASSESSMENT_GROUP_ID
1354 -- Business Rule Mapping
1355 -- =====================
1356 -- Rule CHK_ASSESSMENT_GROUP_ID a)
1357 --
1358 per_asn_bus.chk_assessment_group_id
1359 (p_assessment_group_id => p_rec.assessment_group_id
1360 ,p_business_group_id => p_rec.business_group_id
1361 ,p_assessment_id => p_rec.assessment_id
1362 ,p_object_version_number => p_rec.object_version_number
1363 );
1364 --
1365 -- VALIDATE CHK_ASSESSMENT_TYPE_ID
1366 -- Business Rule Mapping
1367 -- =====================
1368 -- Rule CHK_ASSESSMENT_TYPE_ID a,b,c,d
1369 --
1370 per_asn_bus.chk_assessment_type_id
1371 (p_assessment_id => p_rec.assessment_id
1372 ,p_assessment_type_id => p_rec.assessment_type_id
1373 ,p_assessment_date => p_rec.assessment_date
1374 ,p_business_group_id => p_rec.business_group_id
1375 ,p_object_version_number => p_rec.object_version_number
1376 );
1377 --
1378 -- VALIDATE CHK_PERSON_ID
1379 -- Business Rule Mapping
1380 -- =====================
1381 -- Rule CHK_PERSON_ID a,b,c
1382 --
1383 per_asn_bus.chk_person_id
1384 (p_assessment_id => p_rec.assessment_id
1385 ,p_person_id => p_rec.person_id
1386 ,p_business_group_id => p_rec.business_group_id
1387 ,p_assessment_date => p_rec.assessment_date
1388 ,p_object_version_number => p_rec.object_version_number
1389 );
1390 --
1391 -- VALIDATE CHK_ASSESSOR_PERSON_ID
1392 -- Business Rule Mapping
1393 -- =====================
1394 -- Rule CHK_ASSESSOR_PERSON_ID a
1395 --
1396 per_asn_bus.chk_assessor_person_id
1397 (p_assessor_person_id => p_rec.assessor_person_id
1398 ,p_business_group_id => p_rec.business_group_id
1399 ,p_assessment_date => p_rec.assessment_date
1400 ,p_assessment_id => p_rec.assessment_id
1401 ,p_object_version_number => p_rec.object_version_number
1402 );
1403 --
1404 -- VALIDATE CHK_APPRAISAL_ID
1405 -- Business Rule Mapping
1406 -- =====================
1407 -- Rule CHK_APPRAISAL_ID a) b)
1408 --
1409 per_asn_bus.chk_appraisal_id
1410 (p_appraisal_id => p_rec.appraisal_id
1411 ,p_business_group_id => p_rec.business_group_id
1412 ,p_assessment_id => p_rec.assessment_id
1413 ,p_object_version_number => p_rec.object_version_number
1414 );
1415 --
1416 -- VALIDATE CHK_ASSESSMENT_PERIOD
1417 -- Business Rule Mapping
1418 -- =====================
1419 -- Rule CHK_ASSESSMENT_PERIOD a,b
1420 --
1421 per_asn_bus.chk_assessment_period
1422 (p_assessment_period_start_date => p_rec.assessment_period_start_date
1423 ,p_assessment_period_end_date => p_rec.assessment_period_end_date
1424 ,p_assessment_id => p_rec.assessment_id
1425 ,p_object_version_number => p_rec.object_version_number
1426 );
1427 --
1428 -- VALIDATE CHK_UNIQUE_COMBINATION
1429 -- Business Rule Mapping
1430 -- =====================
1431 -- Rule CHK_UNIQUE_COMBINATION a
1432 --
1433 per_asn_bus.chk_unique_combination
1434 (p_assessment_id => p_rec.assessment_id
1435 ,p_assessment_type_id => p_rec.assessment_type_id
1436 ,p_assessment_date => p_rec.assessment_date
1437 ,p_person_id => p_rec.person_id
1438 ,p_assessor_person_id => p_rec.assessor_person_id
1439 ,p_group_date => p_rec.group_date
1440 ,p_object_version_number => p_rec.object_version_number
1441 );
1442 --
1443 --
1444 per_asn_bus.chk_group_date_id
1445 (p_group_initiator_id => p_rec.group_initiator_id
1446 ,p_group_date => p_rec.group_date
1450 --
1447 ,p_assessment_id => p_rec.assessment_id
1448 ,p_object_version_number => p_rec.object_version_number
1449 );
1451 per_asn_bus.chk_group_initiator_id
1452 (p_group_initiator_id => p_rec.group_initiator_id
1453 ,p_business_group_id => p_rec.business_group_id
1454 ,p_group_date => p_rec.group_date
1455 ,p_assessment_id => p_rec.assessment_id
1456 ,p_object_version_number => p_rec.object_version_number
1457 );
1458 --
1459 -- Call descriptive flexfield validation routines
1460 --
1461 per_asn_bus.chk_df(p_rec => p_rec);
1462 --
1463 hr_utility.set_location(' Leaving:'||l_proc, 10);
1464 End insert_validate;
1465 --
1466 -- ----------------------------------------------------------------------------
1467 -- |---------------------------< update_validate >----------------------------|
1468 -- ----------------------------------------------------------------------------
1469 Procedure update_validate
1470 (p_rec in per_asn_shd.g_rec_type
1471 ,p_effective_date in date) is
1472 --
1473 l_proc varchar2(72) := g_package||'update_validate';
1474 --
1475 Begin
1476 hr_utility.set_location('Entering:'||l_proc, 5);
1477 --
1478 -- Call all supporting business operations. Mapping to the appropiate
1479 -- business rules provided.
1480 --
1481 --
1482 hr_api.validate_bus_grp_id(p_rec.business_group_id
1483 ,p_associated_column1 => per_asn_shd.g_tab_nam ||
1484 '.BUSINESS_GROUP_ID'); -- Validate Business Group
1485 hr_multi_message.end_validation_set;
1486 --
1487 -- VALIDATE CHK_NON_UPDATABLE_ARGS
1488 -- Check those columns which cannot be updated have not changed.
1489 -- Business Rule Mapping
1490 -- =====================
1491 -- Rule CHK_BUSINESS_GROUP_ID a
1492 -- Rule CHK_PERSON_ID c
1493 -- Rule CHK_ASSESSOR_PERSON_ID b
1494 --
1495 per_asn_bus.chk_non_updateable_args
1496 (p_rec => p_rec);
1497 --
1498 -- VALIDATE ASSESSMENT_DATE
1499 -- Business Rule Mapping
1500 -- =====================
1501 -- Rule CHK_ASSESSMENT_DATE a)
1502 --
1503 per_asn_bus.chk_assessment_date
1504 (p_assessment_date => p_rec.assessment_date
1505 ,p_assessment_id => p_rec.assessment_id
1506 ,p_object_version_number => p_rec.object_version_number
1507 );
1508
1509 --
1510 -- VALIDATE CHK_ASSESSMENT_GROUP_ID
1511 -- Business Rule Mapping
1512 -- =====================
1513 -- Rule CHK_ASSESSMENT_GROUP_ID a)
1514 --
1515 per_asn_bus.chk_assessment_group_id
1516 (p_assessment_group_id => p_rec.assessment_group_id
1517 ,p_business_group_id => p_rec.business_group_id
1518 ,p_assessment_id => p_rec.assessment_id
1519 ,p_object_version_number => p_rec.object_version_number
1520 );
1521 --
1522 -- VALIDATE STATUS
1523 -- Business Rule Mapping
1524 -- =====================
1525 -- Rule CHK_STATUS a)
1526 --
1527 per_asn_bus.chk_status
1528 (p_status => p_rec.status
1529 ,p_effective_date => p_effective_date
1530 ,p_assessment_id => p_rec.assessment_id
1531 ,p_object_version_number => p_rec.object_version_number
1532 );
1533 --
1534 -- VALIATE CHK_ASSESSMENT_TYPE_ID
1535 -- Business Rule Mapping
1536 -- =====================
1537 -- Rule CHK_ASSESSMENT_TYPE_ID a,b,c,d
1538 --
1539 per_asn_bus.chk_assessment_type_id
1540 (p_assessment_id => p_rec.assessment_id
1541 ,p_assessment_type_id => p_rec.assessment_type_id
1542 ,p_assessment_date => p_rec.assessment_date
1543 ,p_business_group_id => p_rec.business_group_id
1544 ,p_object_version_number => p_rec.object_version_number
1545 );
1546 --
1547 -- VALIDATE CHK_ASSESSMENT_PERIOD
1548 -- Business Rule Mapping
1549 -- =====================
1550 -- Rule CHK_ASSESSMENT_PERIOD a,b
1551 --
1552 per_asn_bus.chk_assessment_period
1553 (p_assessment_period_start_date => p_rec.assessment_period_start_date
1554 ,p_assessment_period_end_date => p_rec.assessment_period_end_date
1555 ,p_assessment_id => p_rec.assessment_id
1556 ,p_object_version_number => p_rec.object_version_number
1557 );
1558 --
1559 -- VALIDATE CHK_UNIQUE_COMBINATION
1560 -- Business Rule Mapping
1561 -- =====================
1562 -- Rule CHK_UNIQUE_COMBINATION a
1563 --
1564 per_asn_bus.chk_unique_combination
1565 (p_assessment_id => p_rec.assessment_id
1566 ,p_assessment_type_id => p_rec.assessment_type_id
1567 ,p_assessment_date => p_rec.assessment_date
1568 ,p_person_id => p_rec.person_id
1572 );
1569 ,p_assessor_person_id => p_rec.assessor_person_id
1570 ,p_group_date => p_rec.group_date
1571 ,p_object_version_number => p_rec.object_version_number
1573 --
1574 -- Call descriptive flexfield validation routines
1575 --
1576 per_asn_bus.chk_df(p_rec => p_rec);
1577 --
1581 -- ----------------------------------------------------------------------------
1578 hr_utility.set_location(' Leaving:'||l_proc, 10);
1579 End update_validate;
1580 --
1582 -- |---------------------------< delete_validate >----------------------------|
1583 -- ----------------------------------------------------------------------------
1584 Procedure delete_validate(p_rec in per_asn_shd.g_rec_type) is
1585 --
1586 l_proc varchar2(72) := g_package||'delete_validate';
1587 --
1588 Begin
1589 hr_utility.set_location('Entering:'||l_proc, 5);
1590 --
1591 -- Call all supporting business operations and show mapping
1592 --
1593 per_asn_bus.chk_comp_elements
1594 (p_assessment_id => p_rec.assessment_id
1595 ,p_object_version_number => p_rec.object_version_number
1596 );
1597 --
1598 hr_utility.set_location(' Leaving:'||l_proc, 10);
1599 End delete_validate;
1600 --
1601 -- ----------------------------------------------------------------------------
1602 -- |-----------------------< return_legislation_code >-------------------------|
1603 -- ----------------------------------------------------------------------------
1604 Function return_legislation_code
1605 ( p_assessment_id in number
1606 ) return varchar2 is
1607 --
1608 -- Declare cursor
1609 --
1610 cursor csr_leg_code is
1611 select legislation_code
1612 from per_business_groups pbg,
1613 per_assessments pas
1614 where pas.assessment_id = p_assessment_id
1615 and pbg.business_group_id = pas.business_group_id;
1616
1617 l_proc varchar2(72) := g_package||'return_legislation_code';
1618 l_legislation_code varchar2(150);
1619 --
1620 Begin
1621 hr_utility.set_location('Entering:'||l_proc, 5);
1622 --
1623 -- Ensure that all the mandatory parameters are not null
1624 --
1625 hr_api.mandatory_arg_error (p_api_name => l_proc,
1626 p_argument => 'assessment_id',
1627 p_argument_value => p_assessment_id );
1628
1629 if nvl(g_assessment_id, hr_api.g_number) = p_assessment_id then
1630 --
1631 -- The legislation code has already been found with a previous
1632 -- call to this function. Just return the value in the global
1633 -- variable.
1634 --
1635 l_legislation_code := g_legislation_code;
1636 hr_utility.set_location(l_proc, 15);
1637 else
1638 --
1639 -- The ID is different to the last call to this function
1640 -- or this is the first call to this function.
1641 --
1642 open csr_leg_code;
1643 fetch csr_leg_code into l_legislation_code;
1644 if csr_leg_code%notfound then
1645 close csr_leg_code;
1646 --
1647 -- The primary key is invalid therefore we must error out
1648 --
1649 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1650 hr_utility.raise_error;
1651 end if;
1652 --
1653 close csr_leg_code;
1654 --
1655 g_assessment_id := p_assessment_id;
1656 g_legislation_code := l_legislation_code;
1657 end if;
1658 --
1659 return l_legislation_code;
1660 --
1661 hr_utility.set_location(' Leaving:'||l_proc, 20);
1662 --
1663 End return_legislation_code;
1664 --
1665 --
1666 end per_asn_bus;