[Home] [Help]
PACKAGE BODY: APPS.PER_CEO_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body per_ceo_bus as
2 /* $Header: peceorhi.pkb 120.1.12000000.2 2007/04/16 11:05:48 arumukhe ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ceo_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_comp_element_outcome_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_comp_element_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_comp_element_outcomes ceo
32 where ceo.comp_element_outcome_id = p_comp_element_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 --
47 (p_api_name => l_proc
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
48 ,p_argument => 'comp_element_outcome_id'
49 ,p_argument_value => p_comp_element_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_comp_element_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_comp_element_outcomes ceo
101 where ceo.comp_element_outcome_id = p_comp_element_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 => 'comp_element_outcome_id'
117 ,p_argument_value => p_comp_element_outcome_id
118 );
119 --
120 if ( nvl(per_ceo_bus.g_comp_element_outcome_id, hr_api.g_number)
121 = p_comp_element_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_ceo_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_ceo_bus.g_comp_element_outcome_id := p_comp_element_outcome_id;
152 per_ceo_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_ceo_shd.g_rec_type
188 ) IS
189 --
190 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
191 --
192 Begin
193 --
197 IF NOT per_ceo_shd.api_updating
194 -- Only proceed with the validation if a row exists for the current
195 -- record in the HR Schema.
196 --
198 (p_comp_element_outcome_id => p_rec.comp_element_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 -- ----------------------------------------------------------------------------
211 -- |-----------------< chk_comp_element_outcome_id >---------------------------|
212 -- ----------------------------------------------------------------------------
213 --
214 -- Description
215 -- This procedure is used to check that the primary key for the table
216 -- is created properly. It should be null on insert and
217 -- should not be able to be updated.
218 --
219 -- Pre Conditions
220 -- None.
221 --
222 -- In Parameters
223 -- comp_element_outcome_id PK of record being inserted or updated.
224 -- object_version_number Object version number of record being
225 -- inserted or updated.
226 --
227 -- Post Success
228 -- Processing continues
229 --
230 -- Post Failure
231 -- Errors handled by the procedure
232 --
233 -- Access Status
234 -- Internal table handler use only.
235 --
236 --
237 -- {End Of Comments}
238 -- ----------------------------------------------------------------------------
239 Procedure chk_comp_element_outcome_id
240 ( p_comp_element_outcome_id in per_comp_element_outcomes.comp_element_outcome_id%TYPE
241 ,p_object_version_number in per_comp_element_outcomes.object_version_number%TYPE
242 ) is
243 --
244 l_proc varchar2(72) := g_package||'chk_comp_element_outcome_id';
245 l_api_updating boolean;
246 --
247 Begin
248 hr_utility.set_location('Entering:'||l_proc, 5);
249 --
250 l_api_updating := per_ceo_shd.api_updating
251 (p_comp_element_outcome_id => p_comp_element_outcome_id
252 ,p_object_version_number => p_object_version_number
253 );
254 --
255 if (l_api_updating
256 and nvl(p_comp_element_outcome_id,hr_api.g_number)
257 <> per_ceo_shd.g_old_rec.comp_element_outcome_id) then
258 --
259 -- raise error as PK has changed
260 --
261 per_ceo_shd.constraint_error('PER_COMP_ELEMENT_OUTCOMES_PK');
262 --
263 elsif not l_api_updating then
264 --
265 -- check if PK is null
266 --
267 if p_comp_element_outcome_id is not null then
268 --
269 -- raise error as PK is not null
270 --
271 per_ceo_shd.constraint_error('PER_COMP_ELEMENT_OUTCOMES_PK');
272 --
273 end if;
274 --
275 end if;
276 --
277 hr_utility.set_location('Leaving:'||l_proc, 10);
278 --
279 End chk_comp_element_outcome_id;
280 --
281 -- ---------------------------------------------------------------------------
282 -- |-------------------< chk_competence_element_id >--------------------------|
283 -- ---------------------------------------------------------------------------
284 --
285 -- Desciption :
286 --
287 -- Validates that compence_element_id is mandatory and foreign key to
288 -- per_competence_elements table
289 --
290 -- Pre-conditions:
291 --
292 --
293 -- In Arguments :
294 -- p_comp_element_outcome_id
295 -- p_competence_element_id
296 -- p_object_version_number
297 --
298 -- Post Success :
299 -- If the above business rules are satisfied, processing continues
300 --
301 -- Post Failure :
302 -- If the above business rules are violated, an application error
303 -- is raised and processing terminates
304 --
305 -- Access Status :
306 -- Internal Table Handler Use only.
307 --
308 -- {End of Comments}
309 --
310 -- ---------------------------------------------------------------------------
311 procedure chk_competence_element_id
312 (p_comp_element_outcome_id in number
313 ,p_competence_element_id in number
314 ,p_object_version_number in number
315 ) is
316 --
317 --
318 -- Declare cursor
319 --
320 cursor csr_competence_element is
321 select 'x' from per_competence_elements
322 where competence_element_id = p_competence_element_id;
323
324 --
325 -- Declare local variables
326 --
327 l_proc varchar2(72) := g_package||'chk_competence_element_id';
328 l_api_updating boolean;
329 l_exists varchar2(1);
330 --
331 begin
332 hr_utility.set_location('Entering:'||l_proc, 10);
333 --
334 -- Check mandatory parameters have been set
335 --
336
337 hr_api.mandatory_arg_error
338 (p_api_name => l_proc
339 ,p_argument => 'competence_element_id'
340 ,p_argument_value => p_competence_element_id
341 );
342
343 hr_utility.set_location(l_proc, 20);
344
345 --
346 -- Only proceed with validation if :
347 -- a) The current g_old_rec is current and
348 -- b) The date_end value has changed
352 ,p_object_version_number => p_object_version_number);
349 --
350 l_api_updating := per_ceo_shd.api_updating
351 (p_comp_element_outcome_id => p_comp_element_outcome_id
353 --
354
355 if ((l_api_updating and
356 (per_ceo_shd.g_old_rec.competence_element_id <>
357 p_competence_element_id)) or
358 (NOT l_api_updating)) then
359
360 --
361 -- Check that competence_element_id is a foreign key
362 --
363 hr_utility.set_location(l_proc, 30);
364 --
365 open csr_competence_element;
366 fetch csr_competence_element into l_exists;
367 if csr_competence_element%NOTFOUND then
368 close csr_competence_element;
369 per_ceo_shd.constraint_error
370 (
371 p_constraint_name => 'PER_COMP_ELEMENT_OUTCOME_FK1'
372 );
373 end if;
374 close csr_competence_element;
375 --
376 end if;
377 --
378 hr_utility.set_location(' Leaving:'||l_proc, 40);
379 end chk_competence_element_id;
380 --
381 --
382 --
383 -- ---------------------------------------------------------------------------
384 -- |---------------------------< chk_outcome_id >----------------------------|
385 -- ---------------------------------------------------------------------------
386 --
387 -- Desciption :
388 --
389 -- Validates that compence_id is mandatory and foreign key to
390 -- per_competence_outcomes table
391 --
392 -- Pre-conditions:
393 -- None
394 --
395 -- In Arguments :
396 -- p_comp_element_outcome_id
397 -- p_outcome_id
398 -- p_object_version_number
399 --
400 -- Post Success :
401 -- If the above business rules are satisfied, processing continues
402 --
403 -- Post Failure :
404 -- If the above business rules are violated, an application error
405 -- is raised and processing terminates
406 --
407 -- Access Status :
408 -- Internal Table Handler Use only.
409 --
410 -- {End of Comments}
411 --
412 -- ---------------------------------------------------------------------------
413 procedure chk_outcome_id
414 (p_comp_element_outcome_id in number
415 ,p_outcome_id in number
416 ,p_object_version_number in number
417 ) is
418 --
419 --
420 -- Declare cursor
421 --
425
422 cursor csr_competence_outcome is
423 select 'x' from per_competence_outcomes
424 where outcome_id = p_outcome_id;
426 --
427 -- Declare local variables
428 --
429 l_proc varchar2(72) := g_package||'chk_outcome_id';
430 l_api_updating boolean;
431 l_exists varchar2(1);
432 --
433 begin
434 hr_utility.set_location('Entering:'||l_proc, 10);
435 --
436 -- Check mandatory parameters have been set
437 --
438
439 hr_api.mandatory_arg_error
440 (p_api_name => l_proc
441 ,p_argument => 'outcome_id'
442 ,p_argument_value => p_outcome_id
443 );
444
445 hr_utility.set_location(l_proc, 20);
446
447 --
448 -- Only proceed with validation if :
449 -- a) The current g_old_rec is current and
450 -- b) The date_end value has changed
451 --
452 l_api_updating := per_ceo_shd.api_updating
453 (p_comp_element_outcome_id => p_comp_element_outcome_id
454 ,p_object_version_number => p_object_version_number);
455 --
456
457 if ((l_api_updating and
458 (per_ceo_shd.g_old_rec.outcome_id <> p_outcome_id)) or
459 (NOT l_api_updating)) then
460
461 --
462 -- Check that outcome_id is a foreign key
463 --
464 hr_utility.set_location(l_proc, 30);
465 --
466 open csr_competence_outcome;
467 fetch csr_competence_outcome into l_exists;
468 if csr_competence_outcome%NOTFOUND then
469 close csr_competence_outcome;
470 per_ceo_shd.constraint_error
471 (
472 p_constraint_name => 'PER_COMP_ELEMENT_OUTCOME_FK2'
473 );
474 end if;
475 close csr_competence_outcome;
476 --
477 end if;
478 --
479 hr_utility.set_location(' Leaving:'||l_proc, 40);
480 end chk_outcome_id;
481 --
482 -- ---------------------------------------------------------------------------
483 -- |-------------------< chk_uniq_comp_element_outcome >----------------------|
484 -- ---------------------------------------------------------------------------
485 --
486 -- Desciption :
487 --
488 -- Validates that a combination of COMPETENCE_ELEMENT_ID and OUTCOME_ID
489 -- is unique.
490 --
491 -- Pre-conditions:
492 -- competence_element_id and outcome_id are validated
493 --
494 -- In Arguments :
495 -- p_comp_element_outcome_id
496 -- p_competence_element_id
497 -- p_outcome_id
498 -- p_date_from
499 -- p_date_to
500 -- p_object_version_number
501 --
502 -- Post Success :
503 -- If the above business rules are satisfied, processing continues
504 --
505 -- Post Failure :
506 -- If the above business rules are violated, an application error
507 -- is raised and processing terminates
508 --
509 -- Access Status :
510 -- Internal Table Handler Use only.
511 --
512 -- {End of Comments}
513 --
514 -- ---------------------------------------------------------------------------
515 procedure chk_uniq_comp_element_outcome
516 (p_comp_element_outcome_id in number
517 ,p_competence_element_id in number
518 ,p_outcome_id in number
519 ,p_date_from in date
520 ,p_date_to in date
521 ,p_object_version_number in number
522 ) is
523 --
524 --
525 -- Declare cursor
526 --
527 cursor csr_uniq_comp_element_outcome is
528 select 'x' from per_comp_element_outcomes o
529 where o.competence_element_id = p_competence_element_id
530 and o.outcome_id = p_outcome_id
531 and p_date_from < nvl(o.date_to,hr_api.g_eot)
532 and nvl(p_date_to,hr_api.g_eot) > o.date_from;
533
534 /* cursor csr_upd_uniq_comp_ele_outcome is
535 select 'x' from per_comp_element_outcomes o
536 where o.comp_element_outcome_id = p_comp_element_outcome_id
537 and o.competence_element_id = p_competence_element_id
538 and o.outcome_id = p_outcome_id
539 and p_date_from < nvl(o.date_to,hr_api.g_eot)
540 and nvl(p_date_to,hr_api.g_eot) > o.date_from;*/
541
542 l_outcome_name per_competence_outcomes.name%type;
543
544 cursor csr_upd_uniq_comp_ele_outcome(p_name per_competence_outcomes.name%type)is
545 select 'x' from per_comp_element_outcomes o,per_competence_outcomes co
546 where o.comp_element_outcome_id <> p_comp_element_outcome_id
547 and o.competence_element_id = p_competence_element_id
548 and co.outcome_id = o.outcome_id
549 and co.name = p_name
550 and(p_date_from between o.date_from and nvl(o.date_to,hr_api.g_eot)
551 or p_date_to between o.date_from and nvl(o.date_to,hr_api.g_eot)
552 or o.date_from between p_date_from and p_date_to );
553
554
555 --
556 -- Declare local variables
557 --
558 l_proc varchar2(72) := g_package||'chk_uniq_comp_element_outcome';
559 l_api_updating boolean;
560 l_exists varchar2(1);
561 --
562 begin
563 hr_utility.set_location('Entering:'||l_proc, 10);
564
565 --
566 -- Only proceed with validation if :
567 -- a) The current g_old_rec is current and
568 -- b) The date_end value has changed
569 --
570 l_api_updating := per_ceo_shd.api_updating
574
571 (p_comp_element_outcome_id => p_comp_element_outcome_id
572 ,p_object_version_number => p_object_version_number);
573 --
575 if (l_api_updating and
576 (per_ceo_shd.g_old_rec.competence_element_id <>
577 p_competence_element_id or
578 per_ceo_shd.g_old_rec.outcome_id <> p_outcome_id or
579 per_ceo_shd.g_old_rec.date_from <> p_date_from or
580 nvl(per_ceo_shd.g_old_rec.date_to,hr_api.g_eot) <>
581 nvl(p_date_to,hr_api.g_eot))) then
582 --
583 -- Check that a combination is unique
584 --
585
586 hr_utility.set_location(l_proc, 20);
587
588 select name
589 into l_outcome_name
590 from per_competence_outcomes
591 where outcome_id = p_outcome_id;
592
593 --
594 open csr_upd_uniq_comp_ele_outcome(l_outcome_name);
595 fetch csr_upd_uniq_comp_ele_outcome into l_exists;
596 if csr_upd_uniq_comp_ele_outcome%FOUND then
597 close csr_upd_uniq_comp_ele_outcome;
598 hr_utility.set_message(800,'HR_449132_QUA_FWK_OUTCM_EXISTS');
599 hr_multi_message.add
600 (p_associated_column1 => 'PER_COMP_ELEMENT_OUTCOMES.DATE_FROM');
601
602 hr_utility.raise_error;
603 end if;
604 close csr_upd_uniq_comp_ele_outcome;
605 --
606 elsif (NOT l_api_updating) then
607 hr_utility.set_location(l_proc, 30);
608
609 --
610 open csr_uniq_comp_element_outcome;
611 fetch csr_uniq_comp_element_outcome into l_exists;
612 if csr_uniq_comp_element_outcome%FOUND then
613 close csr_uniq_comp_element_outcome;
614 hr_utility.set_message(800,'HR_449132_QUA_FWK_OUTCM_EXISTS');
615 hr_multi_message.add
616 (p_associated_column1 => 'PER_COMP_ELEMENT_OUTCOMES.DATE_FROM');
617
618 hr_utility.raise_error;
619 end if;
620 close csr_uniq_comp_element_outcome;
621 end if;
622 --
623 hr_utility.set_location(' Leaving:'||l_proc, 40);
624
625 end chk_uniq_comp_element_outcome;
626 --
627 -- ---------------------------------------------------------------------------
628 -- |---------------------------< chk_dates >----------------------------------|
629 -- ---------------------------------------------------------------------------
630 --
631 -- Desciption :
632 --
633 -- Validates DATE_FROM is not null
634 --
635 -- Validates that DATE_FROM is less than or equal to the value for
636 -- DATE_TO on the same OUTCOME record
637 --
638 -- Pre-conditions:
639 -- Format of p_date_from must be correct
640 --
641 -- In Arguments :
642 -- p_comp_element_outcome_id
643 -- p_date_from
644 -- p_date_to
645 -- p_object_version_number
646 --
647 -- Post Success :
648 -- If the above business rules are satisfied, processing continues
649 --
650 -- Post Failure :
651 -- If the above business rules are violated, an application error
652 -- is raised and processing terminates
653 --
654 -- Access Status :
655 -- Internal Table Handler Use only.
656 --
657 -- {End of Comments}
658 --
659 -- ---------------------------------------------------------------------------
660 procedure chk_dates
661 (p_comp_element_outcome_id in number
662 ,p_outcome_id in number
663 ,p_date_from in date
664 ,p_date_to in date
665 ,p_object_version_number in number
666 ) is
667 --
668
669 cursor csr_get_outcome_active_date is
670 select date_from from per_competence_outcomes cpo
671 where cpo.outcome_id = p_outcome_id ;
672
673 l_outcome_active_date date ;
674 l_proc varchar2(72) := g_package||'chk_dates';
675 l_api_updating boolean;
676 --
677 begin
678 hr_utility.set_location('Entering:'||l_proc, 10);
679 --
680 -- Check mandatory parameters have been set
681 --
682
683 hr_api.mandatory_arg_error
684 (p_api_name => l_proc
685 ,p_argument => 'date_from'
686 ,p_argument_value => p_date_from
687 );
688 hr_utility.set_location(l_proc, 20);
689 --
690 -- Only proceed with validation if :
691 -- a) The current g_old_rec is current and
692 -- b) The date_end value has changed
693 --
694 l_api_updating := per_ceo_shd.api_updating
695 (p_comp_element_outcome_id => p_comp_element_outcome_id
696 ,p_object_version_number => p_object_version_number);
697 --
698 if (((l_api_updating and
699 (nvl(per_ceo_shd.g_old_rec.date_to,hr_api.g_eot) <>
700 nvl(p_date_to,hr_api.g_eot)) or
701 (per_ceo_shd.g_old_rec.date_from <> p_date_from)) or
702 (NOT l_api_updating))) then
703 --
704 -- Check that date_from <= date_to
705 --
706 hr_utility.set_location(l_proc, 30);
707 --
708 if p_date_from > nvl(p_date_to,hr_api.g_eot) then
709 hr_utility.set_message(800,'HR_6758_APPL_DATE_FROM_CHK');
710 hr_multi_message.add
711 (p_associated_column1 => 'PER_COMP_ELEMENT_OUTCOMES.DATE_FROM');
712 hr_utility.raise_error;
713 end if;
714 --
715 --
716 -- Check that date_from >= date on which the outcome was defined in the system
717 --
721 fetch csr_get_outcome_active_date into l_outcome_active_date;
718 hr_utility.set_location(l_proc, 50);
719 --
720 open csr_get_outcome_active_date ;
722 close csr_get_outcome_active_date;
723
724 if p_date_from < nvl( l_outcome_active_date,hr_api.g_eot) then
725 hr_utility.set_message(800,'HR_34724_COMP_OUTCME_DATE_INVL');
726 hr_multi_message.add
727 (p_associated_column1 => 'PER_COMP_ELEMENT_OUTCOMES.DATE_FROM');
728 hr_utility.raise_error;
729 end if;
730 --
731
732 end if;
733 --
734 hr_utility.set_location(' Leaving:'||l_proc, 40);
735 end chk_dates;
736 --
737 -- ----------------------------------------------------------------------------
738 -- |-----------------------------< chk_ddf >----------------------------------|
739 -- ----------------------------------------------------------------------------
740 --
741 -- Description:
742 -- Validates all the Developer Descriptive Flexfield values.
743 --
744 -- Prerequisites:
745 -- All other columns have been validated. Must be called as the
746 -- last step from insert_validate and update_validate.
747 --
748 -- In Arguments:
749 -- p_rec
750 --
751 -- Post Success:
752 -- If the Developer Descriptive Flexfield structure column and data values
753 -- are all valid this procedure will end normally and processing will
754 -- continue.
755 --
756 -- Post Failure:
757 -- If the Developer Descriptive Flexfield structure column value or any of
758 -- the data values are invalid then an application error is raised as
759 -- a PL/SQL exception.
760 --
761 -- Access Status:
762 -- Internal Row Handler Use Only.
763 --
764 -- ----------------------------------------------------------------------------
765 procedure chk_ddf
766 (p_rec in per_ceo_shd.g_rec_type
767 ) is
768 --
769 l_proc varchar2(72) := g_package || 'chk_ddf';
770 --
771 begin
772 hr_utility.set_location('Entering:'||l_proc,10);
773 --
774 if ((p_rec.comp_element_outcome_id is not null) and (
775 nvl(per_ceo_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
776 nvl(p_rec.information_category, hr_api.g_varchar2) or
777 nvl(per_ceo_shd.g_old_rec.information1, hr_api.g_varchar2) <>
778 nvl(p_rec.information1, hr_api.g_varchar2) or
779 nvl(per_ceo_shd.g_old_rec.information2, hr_api.g_varchar2) <>
780 nvl(p_rec.information2, hr_api.g_varchar2) or
781 nvl(per_ceo_shd.g_old_rec.information3, hr_api.g_varchar2) <>
782 nvl(p_rec.information3, hr_api.g_varchar2) or
783 nvl(per_ceo_shd.g_old_rec.information4, hr_api.g_varchar2) <>
784 nvl(p_rec.information4, hr_api.g_varchar2) or
785 nvl(per_ceo_shd.g_old_rec.information5, hr_api.g_varchar2) <>
786 nvl(p_rec.information5, hr_api.g_varchar2) or
790 nvl(p_rec.information7, hr_api.g_varchar2) or
787 nvl(per_ceo_shd.g_old_rec.information6, hr_api.g_varchar2) <>
788 nvl(p_rec.information6, hr_api.g_varchar2) or
789 nvl(per_ceo_shd.g_old_rec.information7, hr_api.g_varchar2) <>
791 nvl(per_ceo_shd.g_old_rec.information8, hr_api.g_varchar2) <>
792 nvl(p_rec.information8, hr_api.g_varchar2) or
793 nvl(per_ceo_shd.g_old_rec.information9, hr_api.g_varchar2) <>
794 nvl(p_rec.information9, hr_api.g_varchar2) or
795 nvl(per_ceo_shd.g_old_rec.information10, hr_api.g_varchar2) <>
796 nvl(p_rec.information10, hr_api.g_varchar2) or
797 nvl(per_ceo_shd.g_old_rec.information11, hr_api.g_varchar2) <>
798 nvl(p_rec.information11, hr_api.g_varchar2) or
799 nvl(per_ceo_shd.g_old_rec.information13, hr_api.g_varchar2) <>
800 nvl(p_rec.information13, hr_api.g_varchar2) or
801 nvl(per_ceo_shd.g_old_rec.information14, hr_api.g_varchar2) <>
802 nvl(p_rec.information14, hr_api.g_varchar2) or
803 nvl(per_ceo_shd.g_old_rec.information15, hr_api.g_varchar2) <>
804 nvl(p_rec.information15, hr_api.g_varchar2) or
805 nvl(per_ceo_shd.g_old_rec.information16, hr_api.g_varchar2) <>
806 nvl(p_rec.information16, hr_api.g_varchar2) or
807 nvl(per_ceo_shd.g_old_rec.information17, hr_api.g_varchar2) <>
808 nvl(p_rec.information17, hr_api.g_varchar2) or
809 nvl(per_ceo_shd.g_old_rec.information18, hr_api.g_varchar2) <>
810 nvl(p_rec.information18, hr_api.g_varchar2) or
811 nvl(per_ceo_shd.g_old_rec.information19, hr_api.g_varchar2) <>
812 nvl(p_rec.information19, hr_api.g_varchar2) or
813 nvl(per_ceo_shd.g_old_rec.information20, hr_api.g_varchar2) <>
814 nvl(p_rec.information20, hr_api.g_varchar2) ))
815 or (p_rec.comp_element_outcome_id is null) then
816 --
817 -- Only execute the validation if absolutely necessary:
818 -- a) During update, the structure column value or any
819 -- of the attribute values have actually changed.
820 -- b) During insert.
821 --
822 hr_dflex_utility.ins_or_upd_descflex_attribs
823 (p_appl_short_name => 'PER'
824 ,p_descflex_name => 'Comp Element Outcomes DDF'
825 ,p_attribute_category => p_rec.INFORMATION_CATEGORY
826 ,p_attribute1_name => 'INFORMATION1'
827 ,p_attribute1_value => p_rec.information1
828 ,p_attribute2_name => 'INFORMATION2'
829 ,p_attribute2_value => p_rec.information2
830 ,p_attribute3_name => 'INFORMATION3'
831 ,p_attribute3_value => p_rec.information3
832 ,p_attribute4_name => 'INFORMATION4'
833 ,p_attribute4_value => p_rec.information4
834 ,p_attribute5_name => 'INFORMATION5'
835 ,p_attribute5_value => p_rec.information5
836 ,p_attribute6_name => 'INFORMATION6'
837 ,p_attribute6_value => p_rec.information6
838 ,p_attribute7_name => 'INFORMATION7'
839 ,p_attribute7_value => p_rec.information7
840 ,p_attribute8_name => 'INFORMATION8'
841 ,p_attribute8_value => p_rec.information8
842 ,p_attribute9_name => 'INFORMATION9'
843 ,p_attribute9_value => p_rec.information9
844 ,p_attribute10_name => 'INFORMATION10'
845 ,p_attribute10_value => p_rec.information10
846 ,p_attribute11_name => 'INFORMATION11'
847 ,p_attribute11_value => p_rec.information11
848 ,p_attribute12_name => 'INFORMATION12'
849 ,p_attribute12_value => p_rec.information12
850 ,p_attribute13_name => 'INFORMATION13'
851 ,p_attribute13_value => p_rec.information13
852 ,p_attribute14_name => 'INFORMATION14'
853 ,p_attribute14_value => p_rec.information14
854 ,p_attribute15_name => 'INFORMATION15'
855 ,p_attribute15_value => p_rec.information15
856 ,p_attribute16_name => 'INFORMATION16'
857 ,p_attribute16_value => p_rec.information16
858 ,p_attribute17_name => 'INFORMATION17'
859 ,p_attribute17_value => p_rec.information17
860 ,p_attribute18_name => 'INFORMATION18'
861 ,p_attribute18_value => p_rec.information18
862 ,p_attribute19_name => 'INFORMATION19'
863 ,p_attribute19_value => p_rec.information19
864 ,p_attribute20_name => 'INFORMATION20'
865 ,p_attribute20_value => p_rec.information20
866 );
867 end if;
868 --
869 hr_utility.set_location(' Leaving:'||l_proc,20);
870 end chk_ddf;
871 --
872 -- ----------------------------------------------------------------------------
873 -- |------------------------------< chk_df >----------------------------------|
874 -- ----------------------------------------------------------------------------
875 --
876 -- Description:
877 -- Validates all the Descriptive Flexfield values.
878 --
879 -- Prerequisites:
880 -- All other columns have been validated. Must be called as the
881 -- last step from insert_validate and update_validate.
882 --
883 -- In Arguments:
884 -- p_rec
885 --
886 -- Post Success:
887 -- If the Descriptive Flexfield structure column and data values are
888 -- all valid this procedure will end normally and processing will
889 -- continue.
890 --
894 -- a PL/SQL exception.
891 -- Post Failure:
892 -- If the Descriptive Flexfield structure column value or any of
893 -- the data values are invalid then an application error is raised as
895 --
896 -- Access Status:
897 -- Internal Row Handler Use Only.
898 --
899 -- ----------------------------------------------------------------------------
900 procedure chk_df
901 (p_rec in per_ceo_shd.g_rec_type
902 ) is
903 --
904 l_proc varchar2(72) := g_package || 'chk_df';
905 --
906 begin
907 hr_utility.set_location('Entering:'||l_proc,10);
908 --
909 if ((p_rec.comp_element_outcome_id is not null) and (
910 nvl(per_ceo_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
911 nvl(p_rec.attribute1, hr_api.g_varchar2) or
912 nvl(per_ceo_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
913 nvl(p_rec.attribute2, hr_api.g_varchar2) or
914 nvl(per_ceo_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
915 nvl(p_rec.attribute3, hr_api.g_varchar2) or
916 nvl(per_ceo_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
917 nvl(p_rec.attribute4, hr_api.g_varchar2) or
918 nvl(per_ceo_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
919 nvl(p_rec.attribute5, hr_api.g_varchar2) or
920 nvl(per_ceo_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
921 nvl(p_rec.attribute6, hr_api.g_varchar2) or
922 nvl(per_ceo_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
923 nvl(p_rec.attribute7, hr_api.g_varchar2) or
924 nvl(per_ceo_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
925 nvl(p_rec.attribute8, hr_api.g_varchar2) or
926 nvl(per_ceo_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
927 nvl(p_rec.attribute9, hr_api.g_varchar2) or
928 nvl(per_ceo_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
929 nvl(p_rec.attribute10, hr_api.g_varchar2) or
930 nvl(per_ceo_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
931 nvl(p_rec.attribute11, hr_api.g_varchar2) or
932 nvl(per_ceo_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
933 nvl(p_rec.attribute12, hr_api.g_varchar2) or
934 nvl(per_ceo_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
935 nvl(p_rec.attribute13, hr_api.g_varchar2) or
936 nvl(per_ceo_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
937 nvl(p_rec.attribute14, hr_api.g_varchar2) or
938 nvl(per_ceo_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
939 nvl(p_rec.attribute15, hr_api.g_varchar2) or
940 nvl(per_ceo_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
941 nvl(p_rec.attribute16, hr_api.g_varchar2) or
942 nvl(per_ceo_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
943 nvl(p_rec.attribute17, hr_api.g_varchar2) or
944 nvl(per_ceo_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
945 nvl(p_rec.attribute18, hr_api.g_varchar2) or
946 nvl(per_ceo_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
947 nvl(p_rec.attribute19, hr_api.g_varchar2) or
948 nvl(per_ceo_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
949 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
950 or (p_rec.comp_element_outcome_id is null) then
951 --
952 -- Only execute the validation if absolutely necessary:
953 -- a) During update, the structure column value or any
954 -- of the attribute values have actually changed.
955 -- b) During insert.
956 --
957 hr_dflex_utility.ins_or_upd_descflex_attribs
958 (p_appl_short_name => 'PER'
959 ,p_descflex_name => 'PER_COMP_ELEMENT_OUTCOMES'
960 ,p_attribute_category => p_rec.attribute_category
961 ,p_attribute1_name => 'ATTRIBUTE1'
962 ,p_attribute1_value => p_rec.attribute1
963 ,p_attribute2_name => 'ATTRIBUTE2'
964 ,p_attribute2_value => p_rec.attribute2
965 ,p_attribute3_name => 'ATTRIBUTE3'
966 ,p_attribute3_value => p_rec.attribute3
967 ,p_attribute4_name => 'ATTRIBUTE4'
968 ,p_attribute4_value => p_rec.attribute4
969 ,p_attribute5_name => 'ATTRIBUTE5'
970 ,p_attribute5_value => p_rec.attribute5
971 ,p_attribute6_name => 'ATTRIBUTE6'
972 ,p_attribute6_value => p_rec.attribute6
973 ,p_attribute7_name => 'ATTRIBUTE7'
974 ,p_attribute7_value => p_rec.attribute7
975 ,p_attribute8_name => 'ATTRIBUTE8'
976 ,p_attribute8_value => p_rec.attribute8
977 ,p_attribute9_name => 'ATTRIBUTE9'
978 ,p_attribute9_value => p_rec.attribute9
979 ,p_attribute10_name => 'ATTRIBUTE10'
980 ,p_attribute10_value => p_rec.attribute10
981 ,p_attribute11_name => 'ATTRIBUTE11'
982 ,p_attribute11_value => p_rec.attribute11
983 ,p_attribute12_name => 'ATTRIBUTE12'
984 ,p_attribute12_value => p_rec.attribute12
985 ,p_attribute13_name => 'ATTRIBUTE13'
986 ,p_attribute13_value => p_rec.attribute13
987 ,p_attribute14_name => 'ATTRIBUTE14'
988 ,p_attribute14_value => p_rec.attribute14
989 ,p_attribute15_name => 'ATTRIBUTE15'
990 ,p_attribute15_value => p_rec.attribute15
991 ,p_attribute16_name => 'ATTRIBUTE16'
992 ,p_attribute16_value => p_rec.attribute16
993 ,p_attribute17_name => 'ATTRIBUTE17'
997 ,p_attribute19_name => 'ATTRIBUTE19'
994 ,p_attribute17_value => p_rec.attribute17
995 ,p_attribute18_name => 'ATTRIBUTE18'
996 ,p_attribute18_value => p_rec.attribute18
998 ,p_attribute19_value => p_rec.attribute19
999 ,p_attribute20_name => 'ATTRIBUTE20'
1000 ,p_attribute20_value => p_rec.attribute20
1001 );
1002 end if;
1003 --
1004 hr_utility.set_location(' Leaving:'||l_proc,20);
1005 end chk_df;
1006 --
1007 --
1008 -- ----------------------------------------------------------------------------
1009 -- |---------------------------< insert_validate >----------------------------|
1010 -- ----------------------------------------------------------------------------
1011 Procedure insert_validate
1012 (p_effective_date in date
1013 ,p_rec in per_ceo_shd.g_rec_type
1014 ) is
1015 --
1016 l_proc varchar2(72) := g_package||'insert_validate';
1017 --
1018 Begin
1019 hr_utility.set_location('Entering:'||l_proc, 5);
1020 --
1021 -- Call all supporting business operations
1022 --
1023
1024 --
1025 -- Validate comp_element_outcome_id
1026 --
1027 chk_comp_element_outcome_id
1028 (p_comp_element_outcome_id => p_rec.comp_element_outcome_id
1029 ,p_object_version_number => p_rec.object_version_number
1030 );
1031
1032 hr_utility.set_location(l_proc, 10);
1033
1034 --
1035 -- Validate competence_element_id
1036 --
1037 chk_competence_element_id
1038 (p_comp_element_outcome_id => p_rec.comp_element_outcome_id
1039 ,p_competence_element_id => p_rec.competence_element_id
1040 ,p_object_version_number => p_rec.object_version_number
1041 );
1042
1043 hr_utility.set_location(l_proc, 20);
1044
1045 --
1046 -- Validate dates
1047 --
1048 chk_dates
1049 (p_comp_element_outcome_id => p_rec.comp_element_outcome_id
1050 ,p_outcome_id => p_rec.outcome_id
1051 ,p_date_from => p_rec.date_from
1052 ,p_date_to => p_rec.date_to
1053 ,p_object_version_number => p_rec.object_version_number
1054 );
1055
1056 hr_utility.set_location(l_proc, 30);
1057
1058 --
1059 -- Validate a combination of competence_element_id and outcome_id is unique
1060 --
1061 chk_uniq_comp_element_outcome
1062 (p_comp_element_outcome_id => p_rec.comp_element_outcome_id
1063 ,p_competence_element_id => p_rec.competence_element_id
1064 ,p_outcome_id => p_rec.outcome_id
1065 ,p_date_from => p_rec.date_from
1066 ,p_date_to => p_rec.date_to
1067 ,p_object_version_number => p_rec.object_version_number
1068 );
1069
1070 hr_utility.set_location(l_proc, 40);
1071
1072 --
1073 -- Validate Dependent Attributes
1074 --
1075 --
1076 per_ceo_bus.chk_df(p_rec);
1077
1078 hr_utility.set_location(l_proc, 50);
1079
1080 per_ceo_bus.chk_ddf(p_rec);
1081 --
1082 hr_utility.set_location(' Leaving:'||l_proc, 60);
1083 --
1084 End insert_validate;
1085 --
1086 -- ----------------------------------------------------------------------------
1087 -- |---------------------------< update_validate >----------------------------|
1088 -- ----------------------------------------------------------------------------
1089 Procedure update_validate
1090 (p_effective_date in date
1091 ,p_rec in per_ceo_shd.g_rec_type
1092 ) is
1093 --
1094 l_proc varchar2(72) := g_package||'update_validate';
1095 --
1096 Begin
1097 hr_utility.set_location('Entering:'||l_proc, 5);
1098 --
1099 -- Call all supporting business operations
1100 --
1101
1102 --
1103 -- Validate comp_element_outcome_id
1104 --
1105 chk_comp_element_outcome_id
1106 (p_comp_element_outcome_id => p_rec.comp_element_outcome_id
1107 ,p_object_version_number => p_rec.object_version_number
1108 );
1109
1110 hr_utility.set_location(l_proc, 10);
1111
1112 --
1113 -- Validate competence_element_id
1114 --
1115 chk_competence_element_id
1116 (p_comp_element_outcome_id => p_rec.comp_element_outcome_id
1117 ,p_competence_element_id => p_rec.competence_element_id
1118 ,p_object_version_number => p_rec.object_version_number
1119 );
1120
1121 hr_utility.set_location(l_proc, 20);
1122
1123 --
1124 -- Validate dates
1125 --
1126 chk_dates
1127 (p_comp_element_outcome_id => p_rec.comp_element_outcome_id,
1128 p_outcome_id => p_rec.outcome_id,
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, 30);
1135
1136 --
1137 -- Validate a combination of competence_element_id and outcome_id is unique
1138 --
1139 chk_uniq_comp_element_outcome
1140 (p_comp_element_outcome_id => p_rec.comp_element_outcome_id
1141 ,p_competence_element_id => p_rec.competence_element_id
1142 ,p_outcome_id => p_rec.outcome_id
1143 ,p_date_from => p_rec.date_from
1144 ,p_date_to => p_rec.date_to
1145 ,p_object_version_number => p_rec.object_version_number
1146 );
1147
1148 hr_utility.set_location(l_proc, 40);
1149
1150 --
1151 -- Validate Dependent Attributes
1152 --
1153 chk_non_updateable_args
1154 (p_effective_date => p_effective_date
1155 ,p_rec => p_rec
1156 );
1157
1158 hr_utility.set_location(l_proc, 50);
1159 --
1160 per_ceo_bus.chk_df(p_rec);
1161 --
1162 hr_utility.set_location(l_proc, 60);
1163
1164 per_ceo_bus.chk_ddf(p_rec);
1165 --
1166 hr_utility.set_location(' Leaving:'||l_proc, 70);
1167 End update_validate;
1168 --
1169 -- ----------------------------------------------------------------------------
1170 -- |---------------------------< delete_validate >----------------------------|
1171 -- ----------------------------------------------------------------------------
1172 Procedure delete_validate
1173 (p_rec in per_ceo_shd.g_rec_type
1174 ) is
1175 --
1176 l_proc varchar2(72) := g_package||'delete_validate';
1177 --
1178 Begin
1179 hr_utility.set_location('Entering:'||l_proc, 5);
1180 --
1181 -- Call all supporting business operations
1182 --
1183 per_ceo_bus.chk_df(p_rec);
1184
1185 hr_utility.set_location(l_proc, 10);
1186
1187 per_ceo_bus.chk_ddf(p_rec);
1188
1189 hr_utility.set_location(' Leaving:'||l_proc, 10);
1190 End delete_validate;
1191 --
1192 -- ----------------------------------------------------------------------------
1193 -- |------------------------< check_outcome_achieved >-------------------------|
1194 -- ----------------------------------------------------------------------------
1195 --
1196 function check_outcome_achieved
1197 (p_effective_date in date
1198 ,p_competence_element_id in number
1199 ,p_competence_id in number
1200 ) return date
1201 as
1202 --
1203 -- Declare cursors and local variables
1204 --
1205 cursor csr_get_outcome_number is
1206 select count(cpo.outcome_id) from per_competence_outcomes cpo
1207 where cpo.competence_id = p_competence_id
1208 and p_effective_date between date_from
1209 and nvl(date_to,hr_api.g_eot);
1210
1211 cursor csr_get_achieved_number is
1212 select count(ceo.comp_element_outcome_id)
1213 ,max(ceo.date_from)
1214 from per_comp_element_outcomes ceo
1215 where ceo.competence_element_id = p_competence_element_id
1216 -- and p_effective_date between ceo.date_from
1217 -- and nvl(date_to,hr_api.g_eot)
1218 and ceo.outcome_id in (
1219 select cpo2.outcome_id from per_competence_outcomes cpo2
1220 where cpo2.competence_id = p_competence_id
1221 and p_effective_date between cpo2.date_from
1222 and nvl(cpo2.date_to,hr_api.g_eot));
1223
1224 --
1225 --
1226 l_proc varchar2(72) := g_package||'check_outcome_achieved';
1227 l_exists varchar2(1);
1228 l_return date;
1229 l_outcome_number number;
1230 l_achieved_number number;
1231 l_max_date_from date;
1232
1233 begin
1234 hr_utility.set_location('Entering:'|| l_proc, 10);
1235 hr_utility.trace('p_competence_id : ' || p_competence_id);
1236 hr_utility.trace('p_competence_element_id : ' || p_competence_element_id);
1237
1238 open csr_get_outcome_number;
1239 fetch csr_get_outcome_number into l_outcome_number;
1240 close csr_get_outcome_number;
1241 hr_utility.trace('l_outcome_number : ' || l_outcome_number);
1242
1243 open csr_get_achieved_number;
1244 fetch csr_get_achieved_number into l_achieved_number,l_max_date_from;
1245 close csr_get_achieved_number;
1246 hr_utility.trace('l_achieved_number : ' || l_achieved_number);
1247 hr_utility.trace('l_max_date_from : ' || l_max_date_from);
1248
1249 if l_outcome_number = l_achieved_number then
1250 l_return := l_max_date_from;
1251 hr_utility.set_location(l_proc, 20);
1252 else
1253 l_return := NULL;
1254 hr_utility.set_location(l_proc, 30);
1255 end if;
1256 --
1257 hr_utility.set_location('Leaving:' || l_proc, 40);
1258 return(l_return);
1259 --
1260 end check_outcome_achieved;
1261 --
1262 end per_ceo_bus;
1263