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_grade_spines_f pgs
32: where pgs.grade_spine_id = p_grade_spine_id
33: and pbg.business_group_id = pgs.business_group_id;
34: --
35: -- Declare local variables
97: --
98: cursor csr_leg_code is
99: select pbg.legislation_code
100: from per_business_groups_perf pbg
101: , per_grade_spines_f pgs
102: where pgs.grade_spine_id = p_grade_spine_id
103: and pbg.business_group_id = pgs.business_group_id;
104: --
105: -- Declare local variables
187: --
188: -- {End Of Comments}
189: -- ----------------------------------------------------------------------------
190: Procedure chk_grade_spine_id
191: ( p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
192: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
193: ,p_effective_date in date
194: ) is
195: --
188: -- {End Of Comments}
189: -- ----------------------------------------------------------------------------
190: Procedure chk_grade_spine_id
191: ( p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
192: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
193: ,p_effective_date in date
194: ) is
195: --
196: l_proc varchar2(72) := g_package||'chk_grade_spine_id';
210: <> per_pgs_shd.g_old_rec.grade_spine_id) then
211: --
212: -- raise error as PK has changed
213: --
214: per_pgs_shd.constraint_error('PER_GRADE_SPINES_F_PK');
215: --
216: elsif not l_api_updating then
217: --
218: -- check if PK is null
220: if p_grade_spine_id is not null then
221: --
222: -- raise error as PK is not null
223: --
224: per_pgs_shd.constraint_error('PER_GRADE_SPINES_F_PK');
225: --
226: end if;
227: --
228: end if;
264: -- {End Of Comments}
265: -- ----------------------------------------------------------------------------
266: --
267: procedure chk_parent_spine_id
268: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
269: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
270: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
271: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
272: ,p_effective_date in date
265: -- ----------------------------------------------------------------------------
266: --
267: procedure chk_parent_spine_id
268: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
269: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
270: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
271: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
272: ,p_effective_date in date
273: )
266: --
267: procedure chk_parent_spine_id
268: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
269: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
270: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
271: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
272: ,p_effective_date in date
273: )
274: is
267: procedure chk_parent_spine_id
268: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
269: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
270: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
271: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
272: ,p_effective_date in date
273: )
274: is
275: --
313: open csr_valid_parent_spines;
314: fetch csr_valid_parent_spines into l_exists;
315: if csr_valid_parent_spines%notfound then
316: --
317: per_pgs_shd.constraint_error(p_constraint_name => 'PER_GRADE_SPINES_F_FK2');
318: --
319: end if;
320: close csr_valid_parent_spines;
321:
354: --
355: -- {End Of Comments}
356: -- ----------------------------------------------------------------------------
357: Procedure chk_grade_id(
358: p_grade_id in per_grade_spines_f.grade_id%TYPE
359: ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
360: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
361: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
362: ,p_effective_date in date
355: -- {End Of Comments}
356: -- ----------------------------------------------------------------------------
357: Procedure chk_grade_id(
358: p_grade_id in per_grade_spines_f.grade_id%TYPE
359: ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
360: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
361: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
362: ,p_effective_date in date
363: ) is
356: -- ----------------------------------------------------------------------------
357: Procedure chk_grade_id(
358: p_grade_id in per_grade_spines_f.grade_id%TYPE
359: ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
360: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
361: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
362: ,p_effective_date in date
363: ) is
364: --
357: Procedure chk_grade_id(
358: p_grade_id in per_grade_spines_f.grade_id%TYPE
359: ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
360: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
361: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
362: ,p_effective_date in date
363: ) is
364: --
365: l_proc varchar2(72) := g_package||'chk_grade_id';
412: open csr_valid_grade_id;
413: fetch csr_valid_grade_id into l_exists;
414: if csr_valid_grade_id%notfound then
415: close csr_valid_grade_id;
416: per_pgs_shd.constraint_error(p_constraint_name => 'PER_GRADE_SPINES_F_FK3');
417: hr_utility.raise_error;
418: end if;
419: close csr_valid_grade_id;
420: --
454: -- {End Of Comments}
455: -- ----------------------------------------------------------------------------
456: --
457: procedure chk_ceiling_step_id
458: (p_ceiling_step_id in per_grade_spines_f.ceiling_step_id%TYPE
459: ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
460: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
461: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
462: ,p_effective_date in date
455: -- ----------------------------------------------------------------------------
456: --
457: procedure chk_ceiling_step_id
458: (p_ceiling_step_id in per_grade_spines_f.ceiling_step_id%TYPE
459: ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
460: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
461: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
462: ,p_effective_date in date
463: )
456: --
457: procedure chk_ceiling_step_id
458: (p_ceiling_step_id in per_grade_spines_f.ceiling_step_id%TYPE
459: ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
460: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
461: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
462: ,p_effective_date in date
463: )
464: is
457: procedure chk_ceiling_step_id
458: (p_ceiling_step_id in per_grade_spines_f.ceiling_step_id%TYPE
459: ,p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
460: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
461: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
462: ,p_effective_date in date
463: )
464: is
465: --
613: -- Post Success:
614: -- If the combination is unique; processing continues.
615: --
616: -- Post Failure:
617: -- If the combination exists in per_grade_spines_f,
618: -- then an error will be raised and processing terminated.
619: --
620: -- Access Status:
621: -- Internal Table Handler Use Only.
623: -- {End Of Comments}
624: -- ----------------------------------------------------------------------------
625: --
626: procedure chk_uniq_grade_pay_scale
627: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
628: ,p_grade_id in per_grade_spines_f.grade_id%TYPE
629: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
630: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
631: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
624: -- ----------------------------------------------------------------------------
625: --
626: procedure chk_uniq_grade_pay_scale
627: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
628: ,p_grade_id in per_grade_spines_f.grade_id%TYPE
629: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
630: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
631: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
632: ,p_effective_date in date
625: --
626: procedure chk_uniq_grade_pay_scale
627: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
628: ,p_grade_id in per_grade_spines_f.grade_id%TYPE
629: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
630: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
631: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
632: ,p_effective_date in date
633: )
626: procedure chk_uniq_grade_pay_scale
627: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
628: ,p_grade_id in per_grade_spines_f.grade_id%TYPE
629: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
630: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
631: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
632: ,p_effective_date in date
633: )
634: is
627: (p_grade_spine_id in per_grade_spines_f.grade_spine_id%TYPE
628: ,p_grade_id in per_grade_spines_f.grade_id%TYPE
629: ,p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
630: ,p_business_group_id in per_grade_spines_f.business_group_id%TYPE
631: ,p_object_version_number in per_grade_spines_f.object_version_number%TYPE
632: ,p_effective_date in date
633: )
634: is
635: --
638: l_exists varchar2(1);
639: --
640: cursor csr_uniq_grade_pay_scale is
641: select null
642: from per_grade_spines_f
643: where business_group_id = p_business_group_id
644: and grade_id = p_grade_id
645: and parent_spine_id = p_parent_spine_id
646: and p_effective_date
717: -- {End Of Comments}
718: -- ----------------------------------------------------------------------------
719: --
720: PROCEDURE chk_delete(
721: p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
722: ,p_grade_id in per_grade_spines_f.grade_id%TYPE
723: ) is
724: --
725: l_proc varchar2(72) := g_package||'chk_delete';
718: -- ----------------------------------------------------------------------------
719: --
720: PROCEDURE chk_delete(
721: p_parent_spine_id in per_grade_spines_f.parent_spine_id%TYPE
722: ,p_grade_id in per_grade_spines_f.grade_id%TYPE
723: ) is
724: --
725: l_proc varchar2(72) := g_package||'chk_delete';
726: l_exists varchar2(1);
727: --
728: cursor csr_spinal_point_placements is
729: select 'x'
730: from per_spinal_point_steps_f sps
731: ,per_grade_spines_f gs
732: where gs.grade_spine_id = sps.grade_spine_id
733: and gs.parent_spine_id = p_parent_spine_id
734: and gs.grade_id = p_grade_id
735: and exists
739:
740: cursor csr_assignments is
741: select 'x'
742: from per_spinal_point_steps_f sps
743: ,per_grade_spines_f gs
744: where gs.grade_spine_id = sps.grade_spine_id
745: and gs.parent_spine_id = p_parent_spine_id
746: and gs.grade_id = p_grade_id
747: and exists