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