[Home] [Help]
PACKAGE BODY: APPS.PER_CPO_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body per_cpo_bus as
2 /* $Header: pecporhi.pkb 115.0 2004/03/17 10:23 ynegoro noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cpo_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_outcome_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_outcome_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , per_competence_outcomes cpo
32 where cpo.outcome_id = p_outcome_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 l_legislation_code varchar2(150);
39 --
40 begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
47 (p_api_name => l_proc
48 ,p_argument => 'outcome_id'
49 ,p_argument_value => p_outcome_id
50 );
51 --
52 open csr_sec_grp;
53 fetch csr_sec_grp into l_security_group_id
54 , l_legislation_code;
55 --
56 if csr_sec_grp%notfound then
57 --
58 close csr_sec_grp;
59 --
60 -- The primary key is invalid therefore we must error
61 --
62 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
63 hr_multi_message.add
64 (p_associated_column1
65 => nvl(p_associated_column1,'OUTCOME_ID')
66 );
67 --
68 else
69 close csr_sec_grp;
70 --
71 -- Set the security_group_id in CLIENT_INFO
72 --
73 hr_api.set_security_group_id
74 (p_security_group_id => l_security_group_id
75 );
76 --
77 -- Set the sessions legislation context in HR_SESSION_DATA
78 --
79 hr_api.set_legislation_context(l_legislation_code);
80 end if;
81 --
82 hr_utility.set_location(' Leaving:'|| l_proc, 20);
83 --
84 end set_security_group_id;
85 --
86 -- ---------------------------------------------------------------------------
87 -- |---------------------< return_legislation_code >-------------------------|
88 -- ---------------------------------------------------------------------------
89 --
90 Function return_legislation_code
91 (p_outcome_id in number
92 )
93 Return Varchar2 Is
94 --
95 -- Declare cursor
96 --
97 cursor csr_leg_code is
98 select pbg.legislation_code
99 from per_business_groups_perf pbg
100 , per_competence_outcomes cpo
101 where cpo.outcome_id = p_outcome_id;
102 --
103 -- Declare local variables
104 --
105 l_legislation_code varchar2(150);
106 l_proc varchar2(72) := g_package||'return_legislation_code';
107 --
108 Begin
109 --
110 hr_utility.set_location('Entering:'|| l_proc, 10);
111 --
112 -- Ensure that all the mandatory parameter are not null
113 --
114 hr_api.mandatory_arg_error
115 (p_api_name => l_proc
116 ,p_argument => 'outcome_id'
117 ,p_argument_value => p_outcome_id
118 );
119 --
120 if ( nvl(per_cpo_bus.g_outcome_id, hr_api.g_number)
121 = p_outcome_id) then
122 --
123 -- The legislation code has already been found with a previous
124 -- call to this function. Just return the value in the global
125 -- variable.
126 --
127 l_legislation_code := per_cpo_bus.g_legislation_code;
128 hr_utility.set_location(l_proc, 20);
129 else
130 --
131 -- The ID is different to the last call to this function
132 -- or this is the first call to this function.
133 --
134 open csr_leg_code;
135 fetch csr_leg_code into l_legislation_code;
136 --
137 if csr_leg_code%notfound then
138 --
139 -- The primary key is invalid therefore we must error
140 --
141 close csr_leg_code;
142 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
143 fnd_message.raise_error;
144 end if;
145 hr_utility.set_location(l_proc,30);
146 --
147 -- Set the global variables so the values are
148 -- available for the next call to this function.
149 --
150 close csr_leg_code;
151 per_cpo_bus.g_outcome_id := p_outcome_id;
152 per_cpo_bus.g_legislation_code := l_legislation_code;
153 end if;
154 hr_utility.set_location(' Leaving:'|| l_proc, 40);
155 return l_legislation_code;
156 end return_legislation_code;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |-----------------------< chk_non_updateable_args >------------------------|
160 -- ----------------------------------------------------------------------------
161 -- {Start Of Comments}
162 --
163 -- Description:
164 -- This procedure is used to ensure that non updateable attributes have
165 -- not been updated. If an attribute has been updated an error is generated.
166 --
167 -- Pre Conditions:
168 -- g_old_rec has been populated with details of the values currently in
169 -- the database.
170 --
171 -- In Arguments:
172 -- p_rec has been populated with the updated values the user would like the
173 -- record set to.
174 --
175 -- Post Success:
176 -- Processing continues if all the non updateable attributes have not
177 -- changed.
178 --
179 -- Post Failure:
180 -- An application error is raised if any of the non updatable attributes
181 -- have been altered.
182 --
183 -- {End Of Comments}
184 -- ----------------------------------------------------------------------------
185 Procedure chk_non_updateable_args
186 (p_effective_date in date
187 ,p_rec in per_cpo_shd.g_rec_type
188 ) IS
189 --
190 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
191 --
192 Begin
193 --
194 -- Only proceed with the validation if a row exists for the current
195 -- record in the HR Schema.
196 --
197 IF NOT per_cpo_shd.api_updating
198 (p_outcome_id => p_rec.outcome_id
199 ,p_object_version_number => p_rec.object_version_number
200 ) THEN
201 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
202 fnd_message.set_token('PROCEDURE ', l_proc);
203 fnd_message.set_token('STEP ', '5');
204 fnd_message.raise_error;
205 END IF;
206 --
207 End chk_non_updateable_args;
208 --
209 -- ----------------------------------------------------------------------------
210 -- |-----------------------------< chk_outcome_id >----------------------------|
211 -- ----------------------------------------------------------------------------
212 --
213 -- Description
214 -- This procedure is used to check that the primary key for the table
215 -- is created properly. It should be null on insert and
216 -- should not be able to be updated.
217 --
218 -- Pre Conditions
219 -- None.
220 --
221 -- In Parameters
222 -- outcome_id PK of record being inserted or updated.
223 -- object_version_number Object version number of record being
224 -- inserted or updated.
225 --
226 -- Post Success
227 -- Processing continues
228 --
229 -- Post Failure
230 -- Errors handled by the procedure
231 --
232 -- Access Status
233 -- Internal table handler use only.
234 --
235 --
236 -- {End Of Comments}
237 -- ----------------------------------------------------------------------------
238 Procedure chk_outcome_id
239 ( p_outcome_id in per_competence_outcomes.outcome_id%TYPE
240 ,p_object_version_number in per_competence_outcomes.object_version_number%TYPE
241 ) is
242 --
243 l_proc varchar2(72) := g_package||'chk_outcome_id';
244 l_api_updating boolean;
245 --
246 Begin
247 hr_utility.set_location('Entering:'||l_proc, 5);
248 --
249 l_api_updating := per_cpo_shd.api_updating
250 (p_outcome_id => p_outcome_id
251 ,p_object_version_number => p_object_version_number
252 );
253 --
254 if (l_api_updating
255 and nvl(p_outcome_id,hr_api.g_number)
256 <> per_cpo_shd.g_old_rec.outcome_id) then
257 --
258 -- raise error as PK has changed
259 --
260 per_cpo_shd.constraint_error('PER_COMPETENCE_OUTCOMES_PK');
261 --
262 elsif not l_api_updating then
263 --
264 -- check if PK is null
265 --
266 if p_outcome_id is not null then
267 --
268 -- raise error as PK is not null
269 --
270 per_cpo_shd.constraint_error('PER_COMPETENCE_OUTCOMES_PK');
271 --
272 end if;
273 --
274 end if;
275 --
276 hr_utility.set_location('Leaving:'||l_proc, 10);
277 --
278 End chk_outcome_id;
279 --
280 -- ---------------------------------------------------------------------------
281 -- |----------------------< chk_competence_id >-------------------------------|
282 -- ---------------------------------------------------------------------------
283 --
284 -- Desciption :
285 --
286 -- Validates that compence_id is mandatory and foreign key to
287 -- per_competences table
288 --
289 -- Pre-conditions:
290 --
291 --
292 -- In Arguments :
293 -- p_outcome_id
294 -- p_competence_id
295 -- p_object_version_number
296 --
297 -- Post Success :
298 -- If the above business rules are satisfied, processing continues
299 --
300 -- Post Failure :
301 -- If the above business rules are violated, an application error
302 -- is raised and processing terminates
303 --
304 -- Access Status :
305 -- Internal Table Handler Use only.
306 --
307 -- {End of Comments}
308 --
309 -- ---------------------------------------------------------------------------
310 procedure chk_competence_id
311 (p_outcome_id in number
312 ,p_competence_id in number
313 ,p_object_version_number in number
314 ) is
315 --
316 --
317 -- Declare cursor
318 --
319 cursor csr_competence is
320 select '1' from per_competences
321 where competence_id = p_competence_id;
322
323 --
324 -- Declare local variables
325 --
326 l_proc varchar2(72) := g_package||'chk_competence_id';
327 l_api_updating boolean;
328 l_exists varchar2(1);
329 --
330 begin
331 hr_utility.set_location('Entering:'||l_proc, 10);
332 --
333 -- Check mandatory parameters have been set
334 --
335
336 hr_api.mandatory_arg_error
337 (p_api_name => l_proc
338 ,p_argument => 'competence_id'
339 ,p_argument_value => p_competence_id
340 );
341
342 hr_utility.set_location(l_proc, 20);
343
344 --
345 -- Only proceed with validation if :
346 -- a) The current g_old_rec is current and
347 -- b) The date_end value has changed
348 --
349 l_api_updating := per_cpo_shd.api_updating
350 (p_outcome_id => p_outcome_id
351 ,p_object_version_number => p_object_version_number);
352 --
353
354 if ((l_api_updating and
355 (per_cpo_shd.g_old_rec.competence_id <> p_competence_id)) or
356 (NOT l_api_updating)) then
357
358 --
359 -- Check that competence_id is a foreign key
360 --
361 hr_utility.set_location(l_proc, 30);
362 --
363 open csr_competence;
364 fetch csr_competence into l_exists;
365 if csr_competence%NOTFOUND then
366 close csr_competence;
367 per_cpo_shd.constraint_error
368 (
369 p_constraint_name => 'PER_COMPETENCE_OUTCOME_FK1'
370 );
371 end if;
372 close csr_competence;
373 --
374 end if;
375 --
376 hr_utility.set_location(' Leaving:'||l_proc, 40);
377 end chk_competence_id;
378 --
379 -- ---------------------------------------------------------------------------
380 -- |-----------------------< chk_outcome_number>------------------------------|
381 -- ---------------------------------------------------------------------------
382 --
383 -- Desciption :
384 --
385 -- Validates that a OUTCOME_NUMBER is unique within a COMEPTENCE_ID
386 --
387 -- Pre-conditions:
388 -- None.
389 --
390 -- In Arguments :
391 -- p_outcome_id
392 -- p_competence_id
393 -- p_outcome_number
394 -- p_date_from
395 -- p_date_to
396 -- p_object_version_number
397 --
398 -- Post Success :
399 -- If the above business rules are satisfied, processing continues
400 --
401 -- Post Failure :
402 -- If the above business rules are violated, an application error
403 -- is raised and processing terminates
404 --
405 -- Access Status :
406 -- Internal Table Handler Use only.
407 --
408 -- {End of Comments}
409 --
410 -- ---------------------------------------------------------------------------
411 procedure chk_outcome_number
412 (p_outcome_id in number
413 ,p_competence_id in number
414 ,p_outcome_number in number
415 ,p_date_from in date
416 ,p_date_to in date
417 ,p_object_version_number in number
418 ) is
419 --
420 --
421 -- Declare cursor
422 --
423 cursor csr_outcome_number is
424 select null from per_competence_outcomes
425 where competence_id = p_competence_id
426 and outcome_number = p_outcome_number
427 and (p_date_from <= nvl(date_to, hr_api.g_eot)
431 select null from per_competence_outcomes
428 and NVL(p_date_to, hr_api.g_eot) >= date_from);
429
430 cursor csr_upd_outcome_number is
432 where outcome_id <> p_outcome_id
433 and competence_id = p_competence_id
434 and outcome_number = p_outcome_number
435 and (p_date_from <= nvl(date_to, hr_api.g_eot)
436 and NVL(p_date_to, hr_api.g_eot) >= date_from);
437
438 --
439 -- Declare local variables
440 --
441 l_proc varchar2(72) := g_package||'chk_outcome_number';
442 l_api_updating boolean;
443 l_exists varchar2(1);
444 --
445 begin
446 hr_utility.set_location('Entering:'||l_proc, 10);
447 hr_utility.trace('outcome_id : ' || p_outcome_id);
448 hr_utility.trace('date_from : ' || p_date_from);
449 hr_utility.trace('date_to : ' || p_date_to);
450 hr_utility.trace('outcome_number : ' || p_outcome_number);
451 hr_utility.trace('competence_id : ' || p_competence_id);
452 --
453 -- Check mandatory parameters have been set
454 --
455
456 hr_api.mandatory_arg_error
457 (p_api_name => l_proc
458 ,p_argument => 'outcome_number'
459 ,p_argument_value => p_outcome_number
460 );
461
462 hr_utility.set_location(l_proc, 20);
463
464 --
465 -- Only proceed with validation if :
466 -- a) The current g_old_rec is current and
467 -- b) The date_end value has changed
468 --
469 l_api_updating := per_cpo_shd.api_updating
470 (p_outcome_id => p_outcome_id
471 ,p_object_version_number => p_object_version_number);
472 --
473
474 if (l_api_updating and
475 (per_cpo_shd.g_old_rec.outcome_number <> p_outcome_number or
476 per_cpo_shd.g_old_rec.date_from <> p_date_from or
477 nvl(per_cpo_shd.g_old_rec.date_to,hr_api.g_eot) <>
478 nvl(p_date_to,hr_api.g_eot)))
479 then
480 --
481 -- Check that outcome_number is unique
482 --
483 hr_utility.set_location(l_proc, 30);
484 open csr_upd_outcome_number;
485 fetch csr_upd_outcome_number into l_exists;
486 if csr_upd_outcome_number%FOUND then
487 close csr_upd_outcome_number;
488 hr_utility.set_message(800, 'HR_449129_QUA_FWK_NUMBER_EXIST');
489 hr_utility.raise_error;
490 end if;
491 close csr_upd_outcome_number;
492 --
493 elsif (NOT l_api_updating) then
494 hr_utility.set_location(l_proc, 35);
495 open csr_outcome_number;
496 fetch csr_outcome_number into l_exists;
497 if csr_outcome_number%FOUND then
498 close csr_outcome_number;
499 hr_utility.set_message(800, 'HR_449129_QUA_FWK_NUMBER_EXIST');
500 hr_utility.raise_error;
501 end if;
502 close csr_outcome_number;
503 end if;
504 --
505 hr_utility.set_location(' Leaving:'||l_proc, 40);
506 end chk_outcome_number;
507 --
508 -- ---------------------------------------------------------------------------
509 -- |------------------------------< chk_name >--------------------------------|
510 -- ---------------------------------------------------------------------------
511 --
512 -- Desciption :
513 --
514 -- Validates that a NAME is unique within a COMEPTENCE_ID
515 --
516 -- Pre-conditions:
517 -- None.
518 --
519 -- In Arguments :
520 -- p_outcome_id
521 -- p_competence_id
522 -- p_name
523 -- p_date_from
524 -- p_date_to
525 -- p_object_version_number
526 --
527 -- Post Success :
528 -- If the above business rules are satisfied, processing continues
529 --
530 -- Post Failure :
531 -- If the above business rules are violated, an application error
532 -- is raised and processing terminates
533 --
534 -- Access Status :
535 -- Internal Table Handler Use only.
536 --
537 -- {End of Comments}
538 --
539 -- ---------------------------------------------------------------------------
540 procedure chk_name
541 (p_outcome_id in number
542 ,p_competence_id in number
543 ,p_name in varchar2
544 ,p_date_from in date
545 ,p_date_to in date
546 ,p_object_version_number in number
547 ) is
548 --
549 --
550 -- Declare cursor
551 --
552 cursor csr_name is
553 select null from per_competence_outcomes
554 where competence_id = p_competence_id
555 and name = p_name
556 and (p_date_from <= nvl(date_to, hr_api.g_eot)
557 and NVL(p_date_to, hr_api.g_eot) >= date_from);
558
559 cursor csr_upd_name is
560 select null from per_competence_outcomes
561 where outcome_id <> p_outcome_id
562 and competence_id = p_competence_id
563 and name = p_name
564 and (p_date_from <= nvl(date_to, hr_api.g_eot)
565 and NVL(p_date_to, hr_api.g_eot) >= date_from);
566
567 --
568 -- Declare local variables
569 --
570 l_proc varchar2(72) := g_package||'chk_name';
571 l_api_updating boolean;
572 l_exists varchar2(1);
573 --
574 begin
575 hr_utility.set_location('Entering:'||l_proc, 10);
576 --
577 -- Check mandatory parameters have been set
578 --
579
580 hr_api.mandatory_arg_error
584 );
581 (p_api_name => l_proc
582 ,p_argument => 'name'
583 ,p_argument_value => p_name
585
586 hr_utility.set_location(l_proc, 20);
587
588 --
589 -- Only proceed with validation if :
590 -- a) The current g_old_rec is current and
591 -- b) The date_end value has changed
592
593 l_api_updating := per_cpo_shd.api_updating
594 (p_outcome_id => p_outcome_id
595 ,p_object_version_number => p_object_version_number);
596 --
597
598 if (l_api_updating and
599 per_cpo_shd.g_old_rec.name <> p_name or
600 per_cpo_shd.g_old_rec.date_from <> p_date_from or
601 nvl(per_cpo_shd.g_old_rec.date_to,hr_api.g_eot) <>
602 nvl(p_date_to,hr_api.g_eot))
603 then
604 --
605 -- Check that outcome_number is unique
606 --
607 hr_utility.set_location(l_proc, 30);
608 open csr_upd_name;
609 fetch csr_upd_name into l_exists;
610 if csr_upd_name%FOUND then
611 close csr_upd_name;
612 hr_utility.set_message(800, 'HR_449130_QUA_FWK_NAME_EXISTS');
613 hr_utility.raise_error;
614 end if;
615 close csr_upd_name;
616 --
617 elsif (NOT l_api_updating) then
618 hr_utility.set_location(l_proc, 35);
619 open csr_name;
620 fetch csr_name into l_exists;
621 if csr_name%FOUND then
622 close csr_name;
623 hr_utility.set_message(800, 'HR_449130_QUA_FWK_NAME_EXISTS');
624 hr_utility.raise_error;
625 end if;
626 close csr_name;
627 end if;
628 --
629 hr_utility.set_location(' Leaving:'||l_proc, 40);
630 end chk_name;
631 --
632 -- ---------------------------------------------------------------------------
633 -- |---------------------------< chk_dates >---------------------------------|
634 -- ---------------------------------------------------------------------------
635 --
636 -- Desciption :
637 --
638 -- Validates DATE_FROM is not null
639 --
640 -- Validates that DATE_FROM is less than or equal to the value for
641 -- DATE_TO on the same OUTCOME record
642 --
643 -- Pre-conditions:
644 -- Format of p_date_from must be correct
645 --
646 -- In Arguments :
647 -- p_outcome_id
648 -- p_date_from
649 -- p_date_to
650 -- p_object_version_number
651 --
652 -- Post Success :
653 -- If the above business rules are satisfied, processing continues
654 --
655 -- Post Failure :
656 -- If the above business rules are violated, an application error
657 -- is raised and processing terminates
658 --
659 -- Access Status :
660 -- Internal Table Handler Use only.
661 --
662 -- {End of Comments}
663 --
664 -- ---------------------------------------------------------------------------
665 procedure chk_dates
666 (p_outcome_id in number default null
667 ,p_date_from in date
668 ,p_date_to in date
669 ,p_object_version_number in number default null
670 ) is
671 --
672 l_proc varchar2(72) := g_package||'chk_dates';
673 l_api_updating boolean;
674 --
675 begin
676 hr_utility.set_location('Entering:'||l_proc, 10);
677 --
678 -- Check mandatory parameters have been set
679 --
680
681 hr_api.mandatory_arg_error
682 (p_api_name => l_proc
683 ,p_argument => 'date_from'
684 ,p_argument_value => p_date_from
685 );
686 hr_utility.set_location(l_proc, 20);
687 --
688 -- Only proceed with validation if :
689 -- a) The current g_old_rec is current and
690 -- b) The date_end value has changed
691 --
692 l_api_updating := per_cpo_shd.api_updating
693 (p_outcome_id => p_outcome_id
694 ,p_object_version_number => p_object_version_number);
695 --
696 if (((l_api_updating and
697 (nvl(per_cpo_shd.g_old_rec.date_to,hr_api.g_eot) <>
698 nvl(p_date_to,hr_api.g_eot)) or
699 (per_cpo_shd.g_old_rec.date_from <> p_date_from)) or
700 (NOT l_api_updating))) then
701 --
702 -- Check that date_from <= date_to
703 --
704 hr_utility.set_location(l_proc, 30);
705 --
706 if p_date_from > nvl(p_date_to,hr_api.g_eot) then
707 hr_utility.set_message(800,'HR_6758_APPL_DATE_FROM_CHK');
708 hr_utility.raise_error;
709 end if;
710 --
711 end if;
712 --
713 hr_utility.set_location(' Leaving:'||l_proc, 40);
714 end chk_dates;
715 --
716 -- ---------------------------------------------------------------------------
717 -- |---------------------------< chk_delete >--------------------------------|
718 -- ---------------------------------------------------------------------------
719 --
720 -- Description
721 -- This procedure is used to check that there are no values in
722 -- per_comp_element_outcomes.
723 --
724 -- Pre Conditions
725 -- None.
726 --
727 -- In Parameters
728 -- p_outcome_id
729 --
730 -- Post Success
731 -- Processing continues
732 --
733 -- Post Failure
734 -- Errors handled by the procedure
735 --
736 -- Access Status
737 -- Internal table handler use only.
738 --
739 --
743 PROCEDURE chk_delete(
740 -- {End Of Comments}
741 -- ----------------------------------------------------------------------------
742 --
744 p_outcome_id in per_competence_outcomes.outcome_id%TYPE
745 ) is
746 --
747 l_proc varchar2(72) := g_package||'chk_delete';
748 l_exists varchar2(1);
749 --
750 cursor csr_outcome is
751 select 'x'
752 FROM per_comp_element_outcomes
753 WHERE outcome_id = p_outcome_id;
754 --
755 Begin
756 --
757 hr_utility.set_location('Entering:'||l_proc, 10);
758 --
759 -- Check there are no values in per_comp_element_outcomes
760 --
761 open csr_outcome;
762 --
763 fetch csr_outcome into l_exists;
764 --
765 If csr_outcome%found Then
766 --
767 close csr_outcome;
768 --
769 hr_utility.set_message(800, 'HR_449131_QUA_FWK_DEL_OUTCOME');
770 fnd_message.raise_error;
771 --
772 End If;
773 --
774 close csr_outcome;
775 --
776 hr_utility.set_location('Leaving:' || l_proc, 40);
777 --
778 end chk_delete;
779 --
780 --
781 -- ----------------------------------------------------------------------------
782 -- |-----------------------------< chk_ddf >----------------------------------|
783 -- ----------------------------------------------------------------------------
784 --
785 -- Description:
786 -- Validates all the Developer Descriptive Flexfield values.
787 --
788 -- Prerequisites:
789 -- All other columns have been validated. Must be called as the
790 -- last step from insert_validate and update_validate.
791 --
792 -- In Arguments:
793 -- p_rec
794 --
795 -- Post Success:
796 -- If the Developer Descriptive Flexfield structure column and data values
797 -- are all valid this procedure will end normally and processing will
798 -- continue.
799 --
800 -- Post Failure:
801 -- If the Developer Descriptive Flexfield structure column value or any of
802 -- the data values are invalid then an application error is raised as
803 -- a PL/SQL exception.
804 --
805 -- Access Status:
806 -- Internal Row Handler Use Only.
807 --
808 -- ----------------------------------------------------------------------------
809 procedure chk_ddf
810 (p_rec in per_cpo_shd.g_rec_type
811 ) is
812 --
813 l_proc varchar2(72) := g_package || 'chk_ddf';
814 --
815 begin
816 hr_utility.set_location('Entering:'||l_proc,10);
817 --
818 if ((p_rec.outcome_id is not null) and (
819 nvl(per_cpo_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
820 nvl(p_rec.information_category, hr_api.g_varchar2) or
821 nvl(per_cpo_shd.g_old_rec.information1, hr_api.g_varchar2) <>
822 nvl(p_rec.information1, hr_api.g_varchar2) or
823 nvl(per_cpo_shd.g_old_rec.information2, hr_api.g_varchar2) <>
824 nvl(p_rec.information2, hr_api.g_varchar2) or
825 nvl(per_cpo_shd.g_old_rec.information3, hr_api.g_varchar2) <>
826 nvl(p_rec.information3, hr_api.g_varchar2) or
827 nvl(per_cpo_shd.g_old_rec.information4, hr_api.g_varchar2) <>
828 nvl(p_rec.information4, hr_api.g_varchar2) or
829 nvl(per_cpo_shd.g_old_rec.information5, hr_api.g_varchar2) <>
830 nvl(p_rec.information5, hr_api.g_varchar2) or
831 nvl(per_cpo_shd.g_old_rec.information6, hr_api.g_varchar2) <>
832 nvl(p_rec.information6, hr_api.g_varchar2) or
833 nvl(per_cpo_shd.g_old_rec.information7, hr_api.g_varchar2) <>
834 nvl(p_rec.information7, hr_api.g_varchar2) or
835 nvl(per_cpo_shd.g_old_rec.information8, hr_api.g_varchar2) <>
836 nvl(p_rec.information8, hr_api.g_varchar2) or
837 nvl(per_cpo_shd.g_old_rec.information9, hr_api.g_varchar2) <>
838 nvl(p_rec.information9, hr_api.g_varchar2) or
839 nvl(per_cpo_shd.g_old_rec.information10, hr_api.g_varchar2) <>
840 nvl(p_rec.information10, hr_api.g_varchar2) or
841 nvl(per_cpo_shd.g_old_rec.information11, hr_api.g_varchar2) <>
842 nvl(p_rec.information11, hr_api.g_varchar2) or
843 nvl(per_cpo_shd.g_old_rec.information13, hr_api.g_varchar2) <>
844 nvl(p_rec.information13, hr_api.g_varchar2) or
845 nvl(per_cpo_shd.g_old_rec.information14, hr_api.g_varchar2) <>
846 nvl(p_rec.information14, hr_api.g_varchar2) or
847 nvl(per_cpo_shd.g_old_rec.information15, hr_api.g_varchar2) <>
848 nvl(p_rec.information15, hr_api.g_varchar2) or
849 nvl(per_cpo_shd.g_old_rec.information16, hr_api.g_varchar2) <>
850 nvl(p_rec.information16, hr_api.g_varchar2) or
851 nvl(per_cpo_shd.g_old_rec.information17, hr_api.g_varchar2) <>
852 nvl(p_rec.information17, hr_api.g_varchar2) or
853 nvl(per_cpo_shd.g_old_rec.information18, hr_api.g_varchar2) <>
854 nvl(p_rec.information18, hr_api.g_varchar2) or
855 nvl(per_cpo_shd.g_old_rec.information19, hr_api.g_varchar2) <>
856 nvl(p_rec.information19, hr_api.g_varchar2) or
857 nvl(per_cpo_shd.g_old_rec.information20, hr_api.g_varchar2) <>
858 nvl(p_rec.information20, hr_api.g_varchar2) ))
859 or (p_rec.outcome_id is null) then
860 --
861 -- Only execute the validation if absolutely necessary:
862 -- a) During update, the structure column value or any
863 -- of the attribute values have actually changed.
864 -- b) During insert.
865 --
866 hr_dflex_utility.ins_or_upd_descflex_attribs
867 (p_appl_short_name => 'PER'
868 ,p_descflex_name => 'Competence Outcomes DDF'
869 ,p_attribute_category => p_rec.INFORMATION_CATEGORY
870 ,p_attribute1_name => 'INFORMATION1'
871 ,p_attribute1_value => p_rec.information1
875 ,p_attribute3_value => p_rec.information3
872 ,p_attribute2_name => 'INFORMATION2'
873 ,p_attribute2_value => p_rec.information2
874 ,p_attribute3_name => 'INFORMATION3'
876 ,p_attribute4_name => 'INFORMATION4'
877 ,p_attribute4_value => p_rec.information4
878 ,p_attribute5_name => 'INFORMATION5'
879 ,p_attribute5_value => p_rec.information5
880 ,p_attribute6_name => 'INFORMATION6'
881 ,p_attribute6_value => p_rec.information6
882 ,p_attribute7_name => 'INFORMATION7'
883 ,p_attribute7_value => p_rec.information7
884 ,p_attribute8_name => 'INFORMATION8'
885 ,p_attribute8_value => p_rec.information8
886 ,p_attribute9_name => 'INFORMATION9'
887 ,p_attribute9_value => p_rec.information9
888 ,p_attribute10_name => 'INFORMATION10'
889 ,p_attribute10_value => p_rec.information10
890 ,p_attribute11_name => 'INFORMATION11'
891 ,p_attribute11_value => p_rec.information11
892 ,p_attribute12_name => 'INFORMATION12'
893 ,p_attribute12_value => p_rec.information12
894 ,p_attribute13_name => 'INFORMATION13'
895 ,p_attribute13_value => p_rec.information13
896 ,p_attribute14_name => 'INFORMATION14'
897 ,p_attribute14_value => p_rec.information14
898 ,p_attribute15_name => 'INFORMATION15'
899 ,p_attribute15_value => p_rec.information15
900 ,p_attribute16_name => 'INFORMATION16'
901 ,p_attribute16_value => p_rec.information16
902 ,p_attribute17_name => 'INFORMATION17'
903 ,p_attribute17_value => p_rec.information17
904 ,p_attribute18_name => 'INFORMATION18'
905 ,p_attribute18_value => p_rec.information18
906 ,p_attribute19_name => 'INFORMATION19'
907 ,p_attribute19_value => p_rec.information19
908 ,p_attribute20_name => 'INFORMATION20'
909 ,p_attribute20_value => p_rec.information20
910 );
911 end if;
912 --
913 hr_utility.set_location(' Leaving:'||l_proc,20);
914 end chk_ddf;
915 --
916 -- ----------------------------------------------------------------------------
917 -- |------------------------------< chk_df >----------------------------------|
918 -- ----------------------------------------------------------------------------
919 --
920 -- Description:
921 -- Validates all the Descriptive Flexfield values.
922 --
923 -- Prerequisites:
924 -- All other columns have been validated. Must be called as the
925 -- last step from insert_validate and update_validate.
926 --
927 -- In Arguments:
928 -- p_rec
929 --
930 -- Post Success:
931 -- If the Descriptive Flexfield structure column and data values are
932 -- all valid this procedure will end normally and processing will
933 -- continue.
934 --
935 -- Post Failure:
936 -- If the Descriptive Flexfield structure column value or any of
937 -- the data values are invalid then an application error is raised as
938 -- a PL/SQL exception.
939 --
940 -- Access Status:
941 -- Internal Row Handler Use Only.
942 --
943 -- ----------------------------------------------------------------------------
944 procedure chk_df
945 (p_rec in per_cpo_shd.g_rec_type
946 ) is
947 --
948 l_proc varchar2(72) := g_package || 'chk_df';
949 --
950 begin
951 hr_utility.set_location('Entering:'||l_proc,10);
952 --
953 if ((p_rec.outcome_id is not null) and (
954 nvl(per_cpo_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
955 nvl(p_rec.attribute1, hr_api.g_varchar2) or
956 nvl(per_cpo_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
957 nvl(p_rec.attribute2, hr_api.g_varchar2) or
958 nvl(per_cpo_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
959 nvl(p_rec.attribute3, hr_api.g_varchar2) or
960 nvl(per_cpo_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
961 nvl(p_rec.attribute4, hr_api.g_varchar2) or
962 nvl(per_cpo_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
963 nvl(p_rec.attribute5, hr_api.g_varchar2) or
964 nvl(per_cpo_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
965 nvl(p_rec.attribute6, hr_api.g_varchar2) or
966 nvl(per_cpo_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
967 nvl(p_rec.attribute7, hr_api.g_varchar2) or
968 nvl(per_cpo_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
969 nvl(p_rec.attribute8, hr_api.g_varchar2) or
970 nvl(per_cpo_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
971 nvl(p_rec.attribute9, hr_api.g_varchar2) or
972 nvl(per_cpo_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
973 nvl(p_rec.attribute10, hr_api.g_varchar2) or
974 nvl(per_cpo_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
975 nvl(p_rec.attribute11, hr_api.g_varchar2) or
976 nvl(per_cpo_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
977 nvl(p_rec.attribute12, hr_api.g_varchar2) or
978 nvl(per_cpo_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
979 nvl(p_rec.attribute13, hr_api.g_varchar2) or
980 nvl(per_cpo_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
981 nvl(p_rec.attribute14, hr_api.g_varchar2) or
982 nvl(per_cpo_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
983 nvl(p_rec.attribute15, hr_api.g_varchar2) or
984 nvl(per_cpo_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
988 nvl(per_cpo_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
985 nvl(p_rec.attribute16, hr_api.g_varchar2) or
986 nvl(per_cpo_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
987 nvl(p_rec.attribute17, hr_api.g_varchar2) or
989 nvl(p_rec.attribute18, hr_api.g_varchar2) or
990 nvl(per_cpo_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
991 nvl(p_rec.attribute19, hr_api.g_varchar2) or
992 nvl(per_cpo_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
993 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
994 or (p_rec.outcome_id is null) then
995 --
996 -- Only execute the validation if absolutely necessary:
997 -- a) During update, the structure column value or any
998 -- of the attribute values have actually changed.
999 -- b) During insert.
1000 --
1001 hr_dflex_utility.ins_or_upd_descflex_attribs
1002 (p_appl_short_name => 'PER'
1003 ,p_descflex_name => 'PER_COMPETENCE_OUTCOMES'
1004 ,p_attribute_category => p_rec.attribute_category
1005 ,p_attribute1_name => 'ATTRIBUTE1'
1006 ,p_attribute1_value => p_rec.attribute1
1007 ,p_attribute2_name => 'ATTRIBUTE2'
1008 ,p_attribute2_value => p_rec.attribute2
1009 ,p_attribute3_name => 'ATTRIBUTE3'
1010 ,p_attribute3_value => p_rec.attribute3
1011 ,p_attribute4_name => 'ATTRIBUTE4'
1012 ,p_attribute4_value => p_rec.attribute4
1013 ,p_attribute5_name => 'ATTRIBUTE5'
1014 ,p_attribute5_value => p_rec.attribute5
1015 ,p_attribute6_name => 'ATTRIBUTE6'
1016 ,p_attribute6_value => p_rec.attribute6
1017 ,p_attribute7_name => 'ATTRIBUTE7'
1018 ,p_attribute7_value => p_rec.attribute7
1019 ,p_attribute8_name => 'ATTRIBUTE8'
1020 ,p_attribute8_value => p_rec.attribute8
1021 ,p_attribute9_name => 'ATTRIBUTE9'
1022 ,p_attribute9_value => p_rec.attribute9
1023 ,p_attribute10_name => 'ATTRIBUTE10'
1024 ,p_attribute10_value => p_rec.attribute10
1025 ,p_attribute11_name => 'ATTRIBUTE11'
1026 ,p_attribute11_value => p_rec.attribute11
1027 ,p_attribute12_name => 'ATTRIBUTE12'
1028 ,p_attribute12_value => p_rec.attribute12
1029 ,p_attribute13_name => 'ATTRIBUTE13'
1030 ,p_attribute13_value => p_rec.attribute13
1031 ,p_attribute14_name => 'ATTRIBUTE14'
1032 ,p_attribute14_value => p_rec.attribute14
1033 ,p_attribute15_name => 'ATTRIBUTE15'
1034 ,p_attribute15_value => p_rec.attribute15
1035 ,p_attribute16_name => 'ATTRIBUTE16'
1036 ,p_attribute16_value => p_rec.attribute16
1037 ,p_attribute17_name => 'ATTRIBUTE17'
1038 ,p_attribute17_value => p_rec.attribute17
1039 ,p_attribute18_name => 'ATTRIBUTE18'
1040 ,p_attribute18_value => p_rec.attribute18
1041 ,p_attribute19_name => 'ATTRIBUTE19'
1042 ,p_attribute19_value => p_rec.attribute19
1043 ,p_attribute20_name => 'ATTRIBUTE20'
1044 ,p_attribute20_value => p_rec.attribute20
1045 );
1046 end if;
1047 --
1048 hr_utility.set_location(' Leaving:'||l_proc,20);
1049 end chk_df;
1050 --
1051 --
1052 -- ----------------------------------------------------------------------------
1053 -- |---------------------------< insert_validate >----------------------------|
1054 -- ----------------------------------------------------------------------------
1055 Procedure insert_validate
1056 (p_effective_date in date
1057 ,p_rec in per_cpo_shd.g_rec_type
1058 ) is
1059 --
1060 l_proc varchar2(72) := g_package||'insert_validate';
1061 --
1062 Begin
1063 hr_utility.set_location('Entering:'||l_proc, 5);
1064 --
1065 -- Call all supporting business operations
1066 --
1067
1068 --
1069 -- Validate outcome_id
1070 --
1071 chk_outcome_id
1072 (p_outcome_id => p_rec.outcome_id
1073 ,p_object_version_number => p_rec.object_version_number
1074 );
1075
1076 hr_utility.set_location(l_proc, 10);
1077
1078 --
1079 -- After validating the set of important attributes,
1080 -- if Multiple Message detection is enabled and at least
1081 -- one error has been found then abort further validation.
1082 --
1083 hr_multi_message.end_validation_set;
1084
1085 --
1086 -- Validate competence_id
1087 --
1088 chk_competence_id
1089 (p_outcome_id => p_rec.outcome_id
1090 ,p_competence_id => p_rec.competence_id
1091 ,p_object_version_number => p_rec.object_version_number
1092 );
1093
1094 hr_utility.set_location(l_proc, 20);
1095
1096 --
1097 -- Validate dates
1098 --
1099 chk_dates
1100 (p_outcome_id => p_rec.outcome_id
1101 ,p_date_from => p_rec.date_from
1102 ,p_date_to => p_rec.date_to
1103 ,p_object_version_number => p_rec.object_version_number
1104 );
1105
1106 hr_utility.set_location(l_proc, 30);
1107
1108 --
1109 -- Validate outcome_number
1110 --
1111 chk_outcome_number
1112 (p_outcome_id => p_rec.outcome_id
1113 ,p_competence_id => p_rec.competence_id
1114 ,p_outcome_number => p_rec.outcome_number
1118 );
1115 ,p_date_from => p_rec.date_from
1116 ,p_date_to => p_rec.date_to
1117 ,p_object_version_number => p_rec.object_version_number
1119
1120 hr_utility.set_location(l_proc, 40);
1121
1122 --
1123 -- Validate name
1124 --
1125 chk_name
1126 (p_outcome_id => p_rec.outcome_id
1127 ,p_competence_id => p_rec.competence_id
1128 ,p_name => p_rec.name
1129 ,p_date_from => p_rec.date_from
1130 ,p_date_to => p_rec.date_to
1131 ,p_object_version_number => p_rec.object_version_number
1132 );
1133
1134 hr_utility.set_location(l_proc, 50);
1135
1136
1137 --
1138 -- Validate Dependent Attributes
1139 --
1140 --
1141 per_cpo_bus.chk_df(p_rec);
1142
1143 hr_utility.set_location(l_proc, 60);
1144
1145 per_cpo_bus.chk_ddf(p_rec);
1146 --
1147 hr_utility.set_location(' Leaving:'||l_proc, 70);
1148 --
1149 End insert_validate;
1150 --
1151 -- ----------------------------------------------------------------------------
1152 -- |---------------------------< update_validate >----------------------------|
1153 -- ----------------------------------------------------------------------------
1154 Procedure update_validate
1155 (p_effective_date in date
1156 ,p_rec in per_cpo_shd.g_rec_type
1157 ) is
1158 --
1159 l_proc varchar2(72) := g_package||'update_validate';
1160 --
1161 Begin
1162 hr_utility.set_location('Entering:'||l_proc, 5);
1163 --
1164 -- Call all supporting business operations
1165 --
1166
1167 --
1168 -- Validate outcome_id
1169 --
1170 chk_outcome_id
1171 (p_outcome_id => p_rec.outcome_id
1172 ,p_object_version_number => p_rec.object_version_number
1173 );
1174
1175 hr_utility.set_location(l_proc, 10);
1176
1177 --
1178 -- After validating the set of important attributes,
1179 -- if Multiple Message detection is enabled and at least
1180 -- one error has been found then abort further validation.
1181 --
1182 hr_multi_message.end_validation_set;
1183
1184 --
1185 -- Validate competence_id
1186 --
1187 chk_competence_id
1188 (p_outcome_id => p_rec.outcome_id
1189 ,p_competence_id => p_rec.competence_id
1190 ,p_object_version_number => p_rec.object_version_number
1191 );
1192
1193 hr_utility.set_location(l_proc, 20);
1194
1195 --
1196 -- Validate date effective
1197 --
1198 chk_dates
1199 (p_outcome_id => p_rec.outcome_id
1200 ,p_date_from => p_rec.date_from
1201 ,p_date_to => p_rec.date_to
1202 ,p_object_version_number => p_rec.object_version_number
1203 );
1204
1205 hr_utility.set_location(l_proc, 30);
1206
1207 --
1208 -- Validate outcome_number
1209 --
1210 chk_outcome_number
1211 (p_outcome_id => p_rec.outcome_id
1212 ,p_competence_id => p_rec.competence_id
1213 ,p_outcome_number => p_rec.outcome_number
1214 ,p_date_from => p_rec.date_from
1215 ,p_date_to => p_rec.date_to
1216 ,p_object_version_number => p_rec.object_version_number
1217 );
1218
1219 hr_utility.set_location(l_proc, 40);
1220
1221 --
1222 -- Validate name
1223 --
1224 chk_name
1225 (p_outcome_id => p_rec.outcome_id
1226 ,p_competence_id => p_rec.competence_id
1227 ,p_name => p_rec.name
1228 ,p_date_from => p_rec.date_from
1229 ,p_date_to => p_rec.date_to
1230 ,p_object_version_number => p_rec.object_version_number
1231 );
1232
1233 hr_utility.set_location(l_proc, 50);
1234
1235 --
1236 -- Validate Dependent Attributes
1237 --
1238 chk_non_updateable_args
1239 (p_effective_date => p_effective_date
1240 ,p_rec => p_rec
1241 );
1242 --
1243 hr_utility.set_location(l_proc, 60);
1244 --
1245 per_cpo_bus.chk_df(p_rec);
1246
1247 hr_utility.set_location(l_proc, 70);
1248
1249 per_cpo_bus.chk_ddf(p_rec);
1250 --
1251 hr_utility.set_location(' Leaving:'||l_proc, 80);
1252 End update_validate;
1253 --
1254 -- ----------------------------------------------------------------------------
1255 -- |---------------------------< delete_validate >----------------------------|
1256 -- ----------------------------------------------------------------------------
1257 Procedure delete_validate
1258 (p_rec in per_cpo_shd.g_rec_type
1259 ) is
1260 --
1261 l_proc varchar2(72) := g_package||'delete_validate';
1262 --
1263 Begin
1264 hr_utility.set_location('Entering:'||l_proc, 5);
1265 --
1266 -- Call all supporting business operations
1267 --
1268 chk_delete
1269 (p_outcome_id => p_rec.outcome_id
1270 );
1271
1272 hr_utility.set_location(l_proc, 10);
1273
1274 per_cpo_bus.chk_df(p_rec);
1275
1276 hr_utility.set_location(l_proc, 20);
1277
1278 per_cpo_bus.chk_ddf(p_rec);
1279
1280 hr_utility.set_location(' Leaving:'||l_proc, 30);
1281 End delete_validate;
1282 --
1283 end per_cpo_bus;