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