[Home] [Help]
PACKAGE BODY: APPS.PER_PSP_BUS
Source
1 Package Body per_psp_bus as
2 /* $Header: pepsprhi.pkb 115.5 2003/11/17 13:06:07 tpapired noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
9 --
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_psp_bus.'; -- Global package name
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_spinal_point_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_spinal_point_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_spinal_points psp
32 where psp.spinal_point_id = p_spinal_point_id
33 and pbg.business_group_id = psp.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 => 'spinal_point_id'
50 ,p_argument_value => p_spinal_point_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,'SPINAL_POINT_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_spinal_point_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_spinal_points psp
102 where psp.spinal_point_id = p_spinal_point_id
103 and pbg.business_group_id = psp.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 => 'spinal_point_id'
119 ,p_argument_value => p_spinal_point_id
120 );
121 --
122 if ( nvl(per_psp_bus.g_spinal_point_id, hr_api.g_number)
123 = p_spinal_point_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_psp_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_psp_bus.g_spinal_point_id := p_spinal_point_id;
154 per_psp_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 -- ----------------------------------------------------------------------------
162 -- |---------------------------< chk_spinal_point_id >------------------------|
163 -- ----------------------------------------------------------------------------
164 --
165 -- Description
166 -- This procedure is used to check that the primary key for the table
167 -- is created properly. It should be null on insert and
168 -- should not be able to be updated.
169 --
170 -- Pre Conditions
171 -- None.
172 --
173 -- In Parameters
174 -- spinal_point_id PK of record being inserted or updated.
175 -- object_version_number Object version number of record being
176 -- inserted or updated.
177 --
178 -- Post Success
179 -- Processing continues
180 --
181 -- Post Failure
182 -- Errors handled by the procedure
183 --
184 -- Access Status
185 -- Internal table handler use only.
186 --
187 --
188 -- {End Of Comments}
189 -- ----------------------------------------------------------------------------
190 Procedure chk_spinal_point_id
191 ( p_spinal_point_id in per_spinal_points.spinal_point_id%TYPE
192 ,p_object_version_number in per_spinal_points.object_version_number%TYPE
193 ) is
194 --
195 l_proc varchar2(72) := g_package||'chk_spinal_point_id';
196 l_api_updating boolean;
197 --
198 Begin
199 hr_utility.set_location('Entering:'||l_proc, 10);
200 --
201 l_api_updating := per_psp_shd.api_updating
202 (p_spinal_point_id => p_spinal_point_id
203 ,p_object_version_number => p_object_version_number
204 );
205 --
206 if (l_api_updating
207 and nvl(p_spinal_point_id,hr_api.g_number)
208 <> per_psp_shd.g_old_rec.spinal_point_id) then
209 --
210 -- raise error as PK has changed
211 --
212 per_psp_shd.constraint_error('PER_SPINAL_POINTS_PK');
213 --
214 elsif not l_api_updating then
215 --
216 -- check if PK is null
217 --
218 if p_spinal_point_id is not null then
219 --
220 -- raise error as PK is not null
221 --
222 per_psp_shd.constraint_error('PER_SPINAL_POINTS_PK');
223 --
224 end if;
225 --
226 end if;
227 --
228 hr_utility.set_location('Leaving:'||l_proc, 20);
229 --
230 End chk_spinal_point_id;
231 --
232 -- ---------------------------------------------------------------------------
233 -- |-----------------< chk_parent_spine_id >--------------------------------|
234 -- ---------------------------------------------------------------------------
235 --
236 -- Description:
237 -- Validates that a parent_spine_id exists in table per_parent_spines.
238 --
239 -- Pre-conditions:
240 -- parent_spine_id must be valid.
241 -- business_group_id must be valid.
242 -- effective_date must be valid.
243 --
244 -- In Arguments:
245 -- p_spinal_point_id
246 -- p_parent_spine_id
247 -- p_business_group_id
248 -- p_object_version_number
249 --
250 -- Post Success:
251 -- If a row does exist; processing continues.
252 --
253 -- Post Failure:
254 -- If a row does not exist in per_parent_spines for
255 -- a given reason id then an error will be raised and processing terminated.
256 --
257 -- Access Status:
258 -- Internal Table Handler Use Only.
259 --
260 -- {End Of Comments}
261 -- ----------------------------------------------------------------------------
262 --
263 procedure chk_parent_spine_id
264 (p_spinal_point_id in per_spinal_points.spinal_point_id%TYPE
265 ,p_parent_spine_id in per_spinal_points.parent_spine_id%TYPE
266 ,p_business_group_id in per_spinal_points.business_group_id%TYPE
267 ,p_object_version_number in per_spinal_points.object_version_number%TYPE
268 )
269 is
270 --
271 l_exists varchar2(1);
272 l_proc varchar2(72) := g_package||'chk_parent_spine_id';
273 --
274 l_api_updating boolean;
275 l_business_group_id number;
276 --
277 cursor csr_valid_parent_spines is
278 select null
279 from per_parent_spines pps
280 where pps.business_group_id = p_business_group_id
281 and pps.parent_spine_id = p_parent_spine_id;
282 --
283 begin
284
285 hr_utility.set_location('Entering:'|| l_proc, 10);
286 --
287 -- Check mandatory parameters have been set
288 --
289 hr_api.mandatory_arg_error
290 (p_api_name => l_proc
291 ,p_argument => 'parent_spine_id'
292 ,p_argument_value => p_parent_spine_id
293 );
294
295 --
296 -- Only proceed with validation if :
297 -- a) The current g_old_rec is current and
298 -- b) The value for parent_spine_id has changed
299 --
300 l_api_updating := per_psp_shd.api_updating
301 (p_spinal_point_id => p_spinal_point_id
302 ,p_object_version_number => p_object_version_number);
303 --
304 if (l_api_updating and nvl(per_psp_shd.g_old_rec.parent_spine_id,
305 hr_api.g_number) = nvl(p_parent_spine_id, hr_api.g_number)) then
306 return;
307 end if;
308
309 open csr_valid_parent_spines;
310 fetch csr_valid_parent_spines into l_exists;
311 if csr_valid_parent_spines%notfound then
312 close csr_valid_parent_spines;
313 --
314 per_psp_shd.constraint_error(p_constraint_name => 'PER_SPINAL_POINTS_FK2');
315 --
316 end if;
317 close csr_valid_parent_spines;
318
319 hr_utility.set_location(' Leaving:'|| l_proc, 20);
320
321 end chk_parent_spine_id;
322 --
323 -- ---------------------------------------------------------------------------
324 -- |-------------------------< chk_sequence >--------------------------------|
325 -- ---------------------------------------------------------------------------
326 --
327 -- Desciption :
328 --
329 -- Validates SEQUENCE is not null
330 --
331 -- Validates that SEQUENCE is UNIQUE for each parent_spine_id
332 --
333 -- Pre-conditions:
334 -- None.
335 --
336 -- In Arguments :
337 -- p_sequence
338 -- p_parent_spine_id
339 -- p_spinal_point
340 --
341 -- Post Success :
342 -- If the above business rules are satisfied, processing continues
343 --
344 -- Post Failure :
345 -- If the above business rules are violated, an application error
346 -- is raised and processing terminates
347 --
348 -- Access Status :
349 -- Internal Table Handler Use only.
350 --
351 -- {End of Comments}
352 -- ----------------------------------------------------------------------------
353 procedure chk_sequence(
354 p_sequence in per_spinal_points.sequence%TYPE
355 ,p_parent_spine_id in per_spinal_points.parent_spine_id%TYPE
356 ,p_spinal_point in per_spinal_points.spinal_point%TYPE
357 ,p_spinal_point_id in per_spinal_points.spinal_point_id%TYPE
358 ,p_object_version_number in per_spinal_points.object_version_number%TYPE
359 ) IS
360 --
361 l_proc varchar2(72) := g_package||'chk_sequence';
362 l_exists VARCHAR2(2);
363 l_api_updating boolean;
364 --
365 cursor csr_unq_seq is
366 select 'x'
367 from per_spinal_points
368 where sequence = p_sequence
369 and parent_spine_id = p_parent_spine_id
370 and spinal_point = p_spinal_point;
371 --
372 BEGIN
373 --
374 hr_utility.set_location('Entering:'||l_proc, 10);
375 --
376 hr_api.mandatory_arg_error
377 (p_api_name => l_proc,
378 p_argument => 'sequence',
379 p_argument_value => p_sequence);
380 --
381 hr_utility.set_location(l_proc,20);
382 --
383 l_api_updating := per_psp_shd.api_updating
384 (p_spinal_point_id => p_spinal_point_id
385 ,p_object_version_number => p_object_version_number);
386 --
387 if (l_api_updating and nvl(per_psp_shd.g_old_rec.sequence,
388 hr_api.g_number) = nvl(p_sequence, hr_api.g_number)) then
389 hr_utility.set_location('Leaving:'||l_proc, 30);
390 return;
391 end if;
392
393 open csr_unq_seq;
394 --
395 FETCH csr_unq_seq INTO l_exists;
396
397 hr_utility.set_location(l_proc,30);
398
399 IF csr_unq_seq%FOUND THEN
400 hr_utility.set_message(801, 'PER_7925_POINT_SEQ_EXISTS');
401 CLOSE csr_unq_seq;
402 hr_utility.raise_error;
403 END IF;
404
405 CLOSE csr_unq_seq;
406 --
407 hr_utility.set_location('Leaving:'||l_proc, 40);
408 --
409 end chk_sequence;
410
411 --
412 -- ---------------------------------------------------------------------------
413 -- |-------------------------< chk_spinal_point >----------------------------|
414 -- ---------------------------------------------------------------------------
415 --
416 -- Desciption :
417 --
418 -- Validates spinal_point is not null
419 --
420 -- Validates that spinal_point is UNIQUE for each parent_spine_id
421 --
422 -- Pre-conditions:
423 -- None.
424 --
425 -- In Arguments :
426 -- p_spinal_point
427 -- p_parent_spine_id
428 -- p_spinal_point_id
429 -- p_object_version_number
430 --
431 -- Post Success :
432 -- If the above business rules are satisfied, processing continues
433 --
434 -- Post Failure :
435 -- If the above business rules are violated, an application error
436 -- is raised and processing terminates
437 --
438 -- Access Status :
439 -- Internal Table Handler Use only.
440 --
441 -- {End of Comments}
442 -- ----------------------------------------------------------------------------
443 procedure chk_spinal_point(
444 p_spinal_point in per_spinal_points.spinal_point%TYPE
445 ,p_parent_spine_id in per_spinal_points.parent_spine_id%TYPE
446 ,p_spinal_point_id in per_spinal_points.spinal_point_id%TYPE
447 ,p_object_version_number in per_spinal_points.object_version_number%TYPE
448 ) IS
449 --
450 l_proc varchar2(72) := g_package||'chk_spinal_point';
451 l_exists VARCHAR2(2);
452 l_api_updating boolean;
453 --
454 cursor csr_unq_spinal_point is
455 select 'x'
456 from per_spinal_points
457 where spinal_point = p_spinal_point
458 and parent_spine_id = p_parent_spine_id;
459 --
460 BEGIN
461 --
462 hr_utility.set_location('Entering:'||l_proc, 10);
463 --
464 hr_api.mandatory_arg_error
465 (p_api_name => l_proc,
466 p_argument => 'spinal_point',
467 p_argument_value => p_spinal_point);
468 --
469 hr_utility.set_location(l_proc,20);
470 --
471 l_api_updating := per_psp_shd.api_updating
472 (p_spinal_point_id => p_spinal_point_id
473 ,p_object_version_number => p_object_version_number);
474 --
475 if (l_api_updating and nvl(per_psp_shd.g_old_rec.spinal_point,
476 hr_api.g_varchar2) = nvl(p_spinal_point, hr_api.g_varchar2)) then
477 hr_utility.set_location('Leaving:'||l_proc, 30);
478 return;
479 end if;
480
481 hr_utility.set_location(l_proc,40);
482
483 open csr_unq_spinal_point;
484 --
485 FETCH csr_unq_spinal_point INTO l_exists;
486
487 IF csr_unq_spinal_point%FOUND THEN
488 hr_utility.set_message(801, 'PER_7924_POINT_EXISTS');
489 CLOSE csr_unq_spinal_point;
490 fnd_message.raise_error;
491 END IF;
492
493 CLOSE csr_unq_spinal_point;
494 --
495 hr_utility.set_location('Leaving:'||l_proc, 50);
496 --
497 end chk_spinal_point;
498
499 --
500 -- ---------------------------------------------------------------------------
501 -- |---------------------------< chk_delete >--------------------------------|
502 -- ---------------------------------------------------------------------------
503 --
504 -- Description
505 -- This procedure is used to check that there are no values in
506 -- per_grade_spines_f, per_spinal_point_steps_f and pay_grade_rules_f
507 --
508 -- Pre Conditions
509 -- None.
510 --
511 -- In Parameters
512 -- p_spinal_point_id
513 -- p_parent_spine_id
514 --
515 -- Post Success
516 -- Processing continues
517 --
518 -- Post Failure
519 -- Errors handled by the procedure
520 --
521 -- Access Status
522 -- Internal table handler use only.
523 --
524 --
525 -- {End Of Comments}
526 -- ----------------------------------------------------------------------------
527 --
528 PROCEDURE chk_delete(
529 p_spinal_point_id in per_spinal_points.spinal_point_id%TYPE
530 ,p_parent_spine_id in per_spinal_points.parent_spine_id%TYPE
531 ) is
532 --
533 l_proc varchar2(72) := g_package||'chk_delete';
534 l_exists varchar2(1);
535 --
536 cursor csr_step_grade_spine is
537 select 'x'
538 from per_spinal_point_steps_f sps
539 ,per_grade_spines_f pgs
540 where sps.grade_spine_id = pgs.grade_spine_id
541 and pgs.parent_spine_id = p_parent_spine_id
542 and sps.spinal_point_id = p_spinal_point_id;
543 --
544 cursor csr_grade_rule is
545 select 'x'
546 from pay_grade_rules_f
547 where grade_or_spinal_point_id = p_spinal_point_id
548 and rate_type = 'SP';
549
550 --
551 Begin
552 --
553 hr_utility.set_location('Entering:'||l_proc, 10);
554
555 --
556 -- Check there are no values in per_spinal_point_steps_f and
557 -- per_grade_spines_f
558 --
559 open csr_step_grade_spine;
560 --
561 fetch csr_step_grade_spine into l_exists;
562 --
563 If csr_step_grade_spine%found Then
564 --
565 close csr_step_grade_spine;
566 --
567 hr_utility.set_message(801, 'PER_7926_DEL_POINT_STEP');
568 hr_utility.raise_error;
569 --
570 End If;
571 --
572 close csr_step_grade_spine;
573
574 hr_utility.set_location(l_proc, 20);
575
576 --
577 -- Check there are no values in per_grade_rules_f
578 --
579 open csr_grade_rule;
580 --
581 fetch csr_grade_rule into l_exists;
582 --
583 If csr_grade_rule%found Then
584 --
585 close csr_grade_rule;
586 --
587 hr_utility.set_message(801, 'PER_7927_DEL_POINT_VALUE');
588 hr_utility.raise_error;
589 --
590 End If;
591 --
592 close csr_grade_rule;
593
594 hr_utility.set_location('Leaving:' || l_proc, 30);
595 --
596 end chk_delete;
597 --
598 -- ----------------------------------------------------------------------------
599 -- |------------------------------< chk_ddf >----------------------------------|
600 -- ----------------------------------------------------------------------------
601 --
602 -- Description:
603 -- Validates all the Developer Descriptive Flexfield values.
604 --
605 -- Prerequisites:
606 -- All other columns have been validated. Must be called as the
607 -- last step from insert_validate and update_validate.
608 --
609 -- In Arguments:
610 -- p_rec
611 --
612 -- Post Success:
613 -- If the Developer Descriptive Flexfield structure column and data values
614 -- are all valid this procedure will end normally and processing will
615 -- continue.
616 --
617 -- Post Failure:
618 -- If the Developer Descriptive Flexfield structure column value or any of
619 -- the data values are invalid then an application error is raised as
620 -- a PL/SQL exception.
621 --
622 -- Access Status:
623 -- Internal Row Handler Use Only.
624 --
625 -- ----------------------------------------------------------------------------
626 procedure chk_ddf
627 (p_rec in per_psp_shd.g_rec_type
628 ) is
629 --
630 l_proc varchar2(72) := g_package || 'chk_ddf';
631 --
632 begin
633 hr_utility.set_location('Entering:'||l_proc,10);
634 --
635 if ((p_rec.spinal_point_id is not null) and (
636 nvl(per_psp_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
637 nvl(p_rec.information_category, hr_api.g_varchar2) or
638 nvl(per_psp_shd.g_old_rec.information1, hr_api.g_varchar2) <>
639 nvl(p_rec.information1, hr_api.g_varchar2) or
640 nvl(per_psp_shd.g_old_rec.information2, hr_api.g_varchar2) <>
641 nvl(p_rec.information2, hr_api.g_varchar2) or
642 nvl(per_psp_shd.g_old_rec.information3, hr_api.g_varchar2) <>
643 nvl(p_rec.information3, hr_api.g_varchar2) or
644 nvl(per_psp_shd.g_old_rec.information4, hr_api.g_varchar2) <>
645 nvl(p_rec.information4, hr_api.g_varchar2) or
646 nvl(per_psp_shd.g_old_rec.information5, hr_api.g_varchar2) <>
647 nvl(p_rec.information5, hr_api.g_varchar2) or
648 nvl(per_psp_shd.g_old_rec.information6, hr_api.g_varchar2) <>
649 nvl(p_rec.information6, hr_api.g_varchar2) or
650 nvl(per_psp_shd.g_old_rec.information7, hr_api.g_varchar2) <>
651 nvl(p_rec.information7, hr_api.g_varchar2) or
652 nvl(per_psp_shd.g_old_rec.information8, hr_api.g_varchar2) <>
653 nvl(p_rec.information8, hr_api.g_varchar2) or
654 nvl(per_psp_shd.g_old_rec.information9, hr_api.g_varchar2) <>
655 nvl(p_rec.information9, hr_api.g_varchar2) or
656 nvl(per_psp_shd.g_old_rec.information10, hr_api.g_varchar2) <>
657 nvl(p_rec.information10, hr_api.g_varchar2) or
658 nvl(per_psp_shd.g_old_rec.information11, hr_api.g_varchar2) <>
659 nvl(p_rec.information11, hr_api.g_varchar2) or
660 nvl(per_psp_shd.g_old_rec.information12, hr_api.g_varchar2) <>
661 nvl(p_rec.information12, hr_api.g_varchar2) or
662 nvl(per_psp_shd.g_old_rec.information13, hr_api.g_varchar2) <>
663 nvl(p_rec.information13, hr_api.g_varchar2) or
664 nvl(per_psp_shd.g_old_rec.information14, hr_api.g_varchar2) <>
665 nvl(p_rec.information14, hr_api.g_varchar2) or
666 nvl(per_psp_shd.g_old_rec.information15, hr_api.g_varchar2) <>
667 nvl(p_rec.information15, hr_api.g_varchar2) or
668 nvl(per_psp_shd.g_old_rec.information16, hr_api.g_varchar2) <>
669 nvl(p_rec.information16, hr_api.g_varchar2) or
670 nvl(per_psp_shd.g_old_rec.information17, hr_api.g_varchar2) <>
671 nvl(p_rec.information17, hr_api.g_varchar2) or
672 nvl(per_psp_shd.g_old_rec.information18, hr_api.g_varchar2) <>
673 nvl(p_rec.information18, hr_api.g_varchar2) or
674 nvl(per_psp_shd.g_old_rec.information19, hr_api.g_varchar2) <>
675 nvl(p_rec.information19, hr_api.g_varchar2) or
676 nvl(per_psp_shd.g_old_rec.information20, hr_api.g_varchar2) <>
677 nvl(p_rec.information20, hr_api.g_varchar2) or
678 nvl(per_psp_shd.g_old_rec.information21, hr_api.g_varchar2) <>
679 nvl(p_rec.information21, hr_api.g_varchar2) or
680 nvl(per_psp_shd.g_old_rec.information22, hr_api.g_varchar2) <>
681 nvl(p_rec.information22, hr_api.g_varchar2) or
682 nvl(per_psp_shd.g_old_rec.information23, hr_api.g_varchar2) <>
683 nvl(p_rec.information23, hr_api.g_varchar2) or
684 nvl(per_psp_shd.g_old_rec.information24, hr_api.g_varchar2) <>
685 nvl(p_rec.information24, hr_api.g_varchar2) or
686 nvl(per_psp_shd.g_old_rec.information25, hr_api.g_varchar2) <>
687 nvl(p_rec.information25, hr_api.g_varchar2) or
688 nvl(per_psp_shd.g_old_rec.information26, hr_api.g_varchar2) <>
689 nvl(p_rec.information26, hr_api.g_varchar2) or
690 nvl(per_psp_shd.g_old_rec.information27, hr_api.g_varchar2) <>
691 nvl(p_rec.information27, hr_api.g_varchar2) or
692 nvl(per_psp_shd.g_old_rec.information28, hr_api.g_varchar2) <>
693 nvl(p_rec.information28, hr_api.g_varchar2) or
694 nvl(per_psp_shd.g_old_rec.information29, hr_api.g_varchar2) <>
695 nvl(p_rec.information29, hr_api.g_varchar2) or
696 nvl(per_psp_shd.g_old_rec.information30, hr_api.g_varchar2) <>
697 nvl(p_rec.information30, hr_api.g_varchar2) ))
698 or (p_rec.spinal_point_id is null) then
699 --
700 -- Only execute the validation if absolutely necessary:
701 -- a) During update, the structure column value or any
702 -- of the attribute values have actually changed.
703 -- b) During insert.
704 --
705 hr_utility.set_location('Entering:'||l_proc,20);
706
707 hr_dflex_utility.ins_or_upd_descflex_attribs
708 (p_appl_short_name => 'PER'
709 ,p_descflex_name => 'Further Spinal Point DF'
710 ,p_attribute_category => p_rec.INFORMATION_CATEGORY
711 ,p_attribute1_name => 'INFORMATION1'
712 ,p_attribute1_value => p_rec.information1
713 ,p_attribute2_name => 'INFORMATION2'
714 ,p_attribute2_value => p_rec.information2
715 ,p_attribute3_name => 'INFORMATION3'
716 ,p_attribute3_value => p_rec.information3
717 ,p_attribute4_name => 'INFORMATION4'
718 ,p_attribute4_value => p_rec.information4
719 ,p_attribute5_name => 'INFORMATION5'
720 ,p_attribute5_value => p_rec.information5
721 ,p_attribute6_name => 'INFORMATION6'
722 ,p_attribute6_value => p_rec.information6
723 ,p_attribute7_name => 'INFORMATION7'
724 ,p_attribute7_value => p_rec.information7
725 ,p_attribute8_name => 'INFORMATION8'
726 ,p_attribute8_value => p_rec.information8
727 ,p_attribute9_name => 'INFORMATION9'
728 ,p_attribute9_value => p_rec.information9
729 ,p_attribute10_name => 'INFORMATION10'
730 ,p_attribute10_value => p_rec.information10
731 ,p_attribute11_name => 'INFORMATION11'
732 ,p_attribute11_value => p_rec.information11
733 ,p_attribute12_name => 'INFORMATION12'
734 ,p_attribute12_value => p_rec.information12
735 ,p_attribute13_name => 'INFORMATION13'
736 ,p_attribute13_value => p_rec.information13
737 ,p_attribute14_name => 'INFORMATION14'
738 ,p_attribute14_value => p_rec.information14
739 ,p_attribute15_name => 'INFORMATION15'
740 ,p_attribute15_value => p_rec.information15
741 ,p_attribute16_name => 'INFORMATION16'
742 ,p_attribute16_value => p_rec.information16
743 ,p_attribute17_name => 'INFORMATION17'
744 ,p_attribute17_value => p_rec.information17
745 ,p_attribute18_name => 'INFORMATION18'
746 ,p_attribute18_value => p_rec.information18
747 ,p_attribute19_name => 'INFORMATION19'
748 ,p_attribute19_value => p_rec.information19
749 ,p_attribute20_name => 'INFORMATION20'
750 ,p_attribute20_value => p_rec.information20
751 ,p_attribute21_name => 'INFORMATION21'
752 ,p_attribute21_value => p_rec.information21
753 ,p_attribute22_name => 'INFORMATION22'
754 ,p_attribute22_value => p_rec.information22
755 ,p_attribute23_name => 'INFORMATION23'
756 ,p_attribute23_value => p_rec.information23
757 ,p_attribute24_name => 'INFORMATION24'
758 ,p_attribute24_value => p_rec.information24
759 ,p_attribute25_name => 'INFORMATION25'
760 ,p_attribute25_value => p_rec.information25
761 ,p_attribute26_name => 'INFORMATION26'
762 ,p_attribute26_value => p_rec.information26
766 ,p_attribute28_value => p_rec.information28
763 ,p_attribute27_name => 'INFORMATION27'
764 ,p_attribute27_value => p_rec.information27
765 ,p_attribute28_name => 'INFORMATION28'
767 ,p_attribute29_name => 'INFORMATION29'
768 ,p_attribute29_value => p_rec.information29
769 ,p_attribute30_name => 'INFORMATION30'
770 ,p_attribute30_value => p_rec.information30
771 );
772 end if;
773 --
774 hr_utility.set_location(' Leaving:'||l_proc,20);
775 end chk_ddf;
776 --
777 --
778 -- ----------------------------------------------------------------------------
779 -- |-----------------------< chk_non_updateable_args >------------------------|
780 -- ----------------------------------------------------------------------------
781 -- {Start Of Comments}
782 --
783 -- Description:
784 -- This procedure is used to ensure that non updateable attributes have
785 -- not been updated. If an attribute has been updated an error is generated.
786 --
787 -- Pre Conditions:
788 -- g_old_rec has been populated with details of the values currently in
789 -- the database.
790 --
791 -- In Arguments:
792 -- p_rec has been populated with the updated values the user would like the
793 -- record set to.
794 --
795 -- Post Success:
796 -- Processing continues if all the non updateable attributes have not
797 -- changed.
798 --
799 -- Post Failure:
800 -- An application error is raised if any of the non updatable attributes
801 -- have been altered.
802 --
803 -- {End Of Comments}
804 -- ----------------------------------------------------------------------------
805 Procedure chk_non_updateable_args
806 (p_effective_date in date
807 ,p_rec in per_psp_shd.g_rec_type
808 ) IS
809 --
810 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
811 --
812 Begin
813 --
814 -- Only proceed with the validation if a row exists for the current
815 -- record in the HR Schema.
816 --
817 IF NOT per_psp_shd.api_updating
818 (p_spinal_point_id => p_rec.spinal_point_id
819 ,p_object_version_number => p_rec.object_version_number
820 ) THEN
821 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
822 fnd_message.set_token('PROCEDURE ', l_proc);
823 fnd_message.set_token('STEP ', '5');
824 fnd_message.raise_error;
825 END IF;
826 --
827 -- Check business_group_id is not updated
828 --
829 IF nvl(p_rec.business_group_id, hr_api.g_number) <>
830 per_psp_shd.g_old_rec.business_group_id then
831 hr_api.argument_changed_error
832 (p_api_name => l_proc
833 ,p_argument => 'BUSINESS_GROUP_ID'
834 ,p_base_table => per_psp_shd.g_tab_nam
835 );
836 END IF;
837 --
838 -- Check parent_spine_id is not updated
839 --
840 IF nvl(p_rec.parent_spine_id, hr_api.g_number) <>
841 per_psp_shd.g_old_rec.parent_spine_id then
842 hr_api.argument_changed_error
843 (p_api_name => l_proc
844 ,p_argument => 'PARENT_SPINE_ID'
845 ,p_base_table => per_psp_shd.g_tab_nam
846 );
847 END IF;
848 End chk_non_updateable_args;
849 --
850 -- ----------------------------------------------------------------------------
851 -- |---------------------------< insert_validate >----------------------------|
852 -- ----------------------------------------------------------------------------
853 Procedure insert_validate
854 (p_effective_date in date
855 ,p_rec in per_psp_shd.g_rec_type
856 ) is
857 --
858 l_proc varchar2(72) := g_package||'insert_validate';
859 --
860 Begin
861 hr_utility.set_location('Entering:'||l_proc, 10);
862 --
863 -- Call all supporting business operations
864 --
865 hr_api.validate_bus_grp_id
866 (p_business_group_id => p_rec.business_group_id
867 ,p_associated_column1 => per_psp_shd.g_tab_nam
868 || '.BUSINESS_GROUP_ID');
869 --
870 -- Validate spinal point id
871 --
872 chk_spinal_point_id
873 (p_spinal_point_id => p_rec.spinal_point_id
874 ,p_object_version_number => p_rec.object_version_number
875 );
876
877 hr_utility.set_location(l_proc, 20);
878
879 -- After validating the set of important attributes,
880 -- if Multiple Message detection is enabled and at least
881 -- one error has been found then abort further validation.
882 --
883 hr_multi_message.end_validation_set;
884
885 --
886 -- Validate Dependent Attributes
887 --
888
889 --
890 -- Validate parent spine id
891 --
892 chk_parent_spine_id
893 (p_spinal_point_id => p_rec.spinal_point_id
894 ,p_parent_spine_id => p_rec.parent_spine_id
895 ,p_business_group_id => p_rec.business_group_id
896 ,p_object_version_number => p_rec.object_version_number
897 );
898
899 hr_utility.set_location(l_proc, 30);
900
901 --
902 -- Validate sequence
903 --
904 chk_sequence(
905 p_sequence => p_rec.sequence
906 ,p_parent_spine_id => p_rec.parent_spine_id
907 ,p_spinal_point => p_rec.spinal_point
908 ,p_spinal_point_id => p_rec.spinal_point_id
909 ,p_object_version_number => p_rec.object_version_number
910 ) ;
911
912 hr_utility.set_location(l_proc, 40);
913
914 --
915 -- Validate spinal point
916 --
917 chk_spinal_point(
918 p_spinal_point => p_rec.spinal_point
919 ,p_parent_spine_id => p_rec.parent_spine_id
920 ,p_spinal_point_id => p_rec.spinal_point_id
921 ,p_object_version_number => p_rec.object_version_number
922 ) ;
923
924 hr_utility.set_location(' Leaving:'||l_proc, 50);
925
926 --
927 -- Developer Descriptive Flexfield Validation
928 --
929 per_psp_bus.chk_ddf(p_rec);
930
931 hr_utility.set_location(' Leaving:'||l_proc, 100);
932
933 End insert_validate;
934 --
935 -- ----------------------------------------------------------------------------
936 -- |---------------------------< update_validate >----------------------------|
937 -- ----------------------------------------------------------------------------
938 Procedure update_validate
939 (p_effective_date in date
940 ,p_rec in per_psp_shd.g_rec_type
941 ) is
942 --
943 l_proc varchar2(72) := g_package||'update_validate';
944 --
945 Begin
946 hr_utility.set_location('Entering:'||l_proc, 10);
947 --
948 -- Call all supporting business operations
949 --
950 hr_api.validate_bus_grp_id
951 (p_business_group_id => p_rec.business_group_id
952 ,p_associated_column1 => per_psp_shd.g_tab_nam
953 || '.BUSINESS_GROUP_ID');
954
955 --
956 -- Validate spinal point id
957 --
958 chk_spinal_point_id
959 (p_spinal_point_id => p_rec.spinal_point_id
960 ,p_object_version_number => p_rec.object_version_number
961 );
962
963 hr_utility.set_location(l_proc, 20);
964
965 --
966 -- After validating the set of important attributes,
967 -- if Multiple Message detection is enabled and at least
968 -- one error has been found then abort further validation.
969 --
970 hr_multi_message.end_validation_set;
971
972 --
973 -- Validate Dependent Attributes
974 --
975 chk_non_updateable_args
976 (p_effective_date => p_effective_date
977 ,p_rec => p_rec
978 );
979
980 hr_utility.set_location(l_proc, 30);
981
982 --
983 -- Validate parent spine id
984 --
985 chk_parent_spine_id
986 (p_spinal_point_id => p_rec.spinal_point_id
987 ,p_parent_spine_id => p_rec.parent_spine_id
988 ,p_business_group_id => p_rec.business_group_id
989 ,p_object_version_number => p_rec.object_version_number
990 );
991
992 hr_utility.set_location(l_proc, 40);
993
994 --
995 -- Validate sequence
996 --
997 chk_sequence(
998 p_sequence => p_rec.sequence
999 ,p_parent_spine_id => p_rec.parent_spine_id
1000 ,p_spinal_point => p_rec.spinal_point
1001 ,p_spinal_point_id => p_rec.spinal_point_id
1002 ,p_object_version_number => p_rec.object_version_number
1003 ) ;
1004
1005 hr_utility.set_location(l_proc, 50);
1006
1007 --
1008 -- Validate spinal point
1009 --
1010 chk_spinal_point(
1011 p_spinal_point => p_rec.spinal_point
1012 ,p_parent_spine_id => p_rec.parent_spine_id
1013 ,p_spinal_point_id => p_rec.spinal_point_id
1014 ,p_object_version_number => p_rec.object_version_number
1015 ) ;
1016
1017 hr_utility.set_location(' Leaving:'||l_proc, 60);
1018
1019 --
1020 -- Developer Descriptive Flexfield Validation
1021 --
1022 per_psp_bus.chk_ddf(p_rec);
1023
1024 hr_utility.set_location(' Leaving:'||l_proc, 100);
1025
1026 End update_validate;
1027 --
1028 -- ----------------------------------------------------------------------------
1029 -- |---------------------------< delete_validate >----------------------------|
1030 -- ----------------------------------------------------------------------------
1031 Procedure delete_validate
1032 (p_rec in per_psp_shd.g_rec_type
1033 ) is
1034 --
1035 l_proc varchar2(72) := g_package||'delete_validate';
1036 --
1037 Begin
1038 hr_utility.set_location('Entering:'||l_proc, 10);
1039
1040 --
1041 -- Call all supporting business operations
1042 --
1043 chk_delete(p_spinal_point_id => p_rec.spinal_point_id
1044 ,p_parent_spine_id => p_rec.parent_spine_id);
1045
1046 hr_utility.set_location(' Leaving:'||l_proc, 20);
1047 End delete_validate;
1048 --
1049 end per_psp_bus;