1 package body PER_SPINAL_POINTS_PKG as
2 /* $Header: pespo01t.pkb 115.1 99/07/18 15:08:24 porting ship $ */
3
4
5 procedure chk_unq_point(p_spoint IN VARCHAR2,
6 p_pspine_id IN NUMBER,
7 p_rowid IN VARCHAR2) is
8 l_exists VARCHAR2(1);
9
10 cursor c1 is
11 select 'x'
12 from per_spinal_points
13 where spinal_point = p_spoint
14 and parent_spine_id = p_pspine_id
15 and ((p_rowid is null)
16 or (p_rowid is not null and chartorowid(p_rowid) <> rowid));
17 --
18 begin
19 --
20 hr_utility.set_location('per_spinal_points_pkg.chk_unq_point',1);
21 --
22 open c1;
23 --
24 fetch c1 into l_exists;
25 --
26 IF c1%found THEN
27 hr_utility.set_message(801, 'PER_7924_POINT_EXISTS');
28 close c1;
29 hr_utility.raise_error;
30 END IF;
31 --
32 close c1;
33
34 end chk_unq_point;
35
36
37
38 procedure chk_unq_seq(p_seq IN NUMBER,
39 p_pspine_id IN NUMBER,
40 p_rowid IN VARCHAR2) is
41 l_exists VARCHAR2(1);
42
43 cursor c2 is
44 select 'x'
45 from per_spinal_points
46 where sequence = p_seq
47 and parent_spine_id = p_pspine_id
48 and ((p_rowid is null)
49 or (p_rowid is not null and chartorowid(p_rowid) <> rowid));
50 --
51 begin
52 --
53 hr_utility.set_location('per_spinal_points_pkg.chk_unq_seq',1);
54 --
55 open c2;
56 --
57 fetch c2 into l_exists;
58 --
59 IF c2%found THEN
60 hr_utility.set_message(801, 'PER_7925_POINT_SEQ_EXISTS');
61 close c2;
62 hr_utility.raise_error;
63 END IF;
64 --
65 close c2;
66
67 end chk_unq_seq;
68
69
70
71 procedure rules_steps_update(p_seq IN NUMBER,
72 p_spoint_id IN NUMBER) is
73
74 begin
75 --
76 begin
77 --
78 update pay_grade_rules_f
79 set sequence = p_seq
80 where rate_type = 'SP'
81 and grade_or_spinal_point_id = p_spoint_id;
82 --
83 end;
84 --
85 begin
86 --
87 update per_spinal_point_steps
88 set sequence = p_seq
89 where spinal_point_id = p_spoint_id;
90 --
91 end;
92 --
93 end rules_steps_update;
94
95
96
97 procedure get_id(p_spoint_id IN OUT NUMBER) is
98
99 cursor c3 is
100 select per_spinal_points_s.nextval
101 from sys.dual;
102 --
103 begin
104 --
105 hr_utility.set_location('per_spinal_points_pkg.get_id',1);
106 --
107 open c3;
108 --
109 fetch c3 into p_spoint_id;
110 --
111 close c3;
112 --
113 end get_id;
114
115
116
117 procedure stb_del_validation(p_pspine_id IN NUMBER,
118 p_spoint_id IN NUMBER) is
119 l_exists1 VARCHAR2(1);
120 l_exists2 VARCHAR2(1);
121
122 cursor c4 is
123 select 'x'
124 from per_spinal_point_steps_f f,
125 per_grade_spines g
126 where f.grade_spine_id = g.grade_spine_id
127 and g.parent_spine_id = p_pspine_id
128 and f.spinal_point_id = p_spoint_id;
129 --
130 cursor c5 is
131 select 'x'
132 from pay_grade_rules_f
133 where grade_or_spinal_point_id = p_spoint_id
134 and rate_type = 'SP';
135 --
136 begin
137 --
138 hr_utility.set_location('per_spinal_points_pkg.stb_del_validation',1);
139 --
140 open c4;
141 --
142 fetch c4 into l_exists1;
143 IF c4%found THEN
144 hr_utility.set_message(801, 'PER_7926_DEL_POINT_STEP');
145 close c4;
146 hr_utility.raise_error;
147 END IF;
148 --
149 close c4;
150 --
151 hr_utility.set_location('per_spinal_points_pkg.stb_del_validation',2);
152 --
153 open c5;
154 --
155 fetch c5 into l_exists2;
156 IF c5%found THEN
157 hr_utility.set_message(801, 'PER_7927_DEL_POINT_VALUE');
158 close c5;
159 hr_utility.raise_error;
160 END IF;
161 --
162 close c5;
163 --
164 end stb_del_validation;
165
166
167
168 end PER_SPINAL_POINTS_PKG;
[Home] [Help]