1 Package Body per_bil_bus as
2 /* $Header: pebilrhi.pkb 115.10 2003/04/10 09:19:39 jheer noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_bil_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_id_value >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- id_value PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_id_value(p_id_value in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_id_value';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := per_bil_shd.api_updating
47 (p_id_value => p_id_value,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_id_value,hr_api.g_number)
52 <> per_bil_shd.g_old_rec.id_value) then
53 --
54 -- raise error as PK has changed
55 --
56 per_bil_shd.constraint_error('hr_summary_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_id_value is not null then
63 --
64 -- raise error as PK is not null
65 --
66 per_bil_shd.constraint_error('hr_summary_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_id_value;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |---------------------------< insert_validate >----------------------------|
78 -- ----------------------------------------------------------------------------
79 Procedure insert_validate(p_rec in per_bil_shd.g_rec_type) is
80 --
81 l_proc varchar2(72) := g_package||'insert_validate';
82 l_stmt hr_summary_restriction_type.restriction_sql%type;
83 --
84 Begin
85 hr_utility.set_location('Entering:'||l_proc, 5);
86 --
87 -- Call all supporting business operations
88 --
89 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
90 --
91 chk_id_value
92 (p_id_value => p_rec.id_value,
93 p_object_version_number => p_rec.object_version_number);
94 --
95 if p_rec.fk_value1 is not null
96 or p_rec.fk_value2 is not null
97 or p_rec.fk_value3 is not null then
98 --
99 if not per_bil_shd.parent_found(p_rec => p_rec) then
100 per_bil_shd.constraint_error('PARENT_RECORD');
101 end if;
102 --
103 end if;
104 --
105 if per_bil_shd.row_exist (p_rec => p_rec) then
106 per_bil_shd.constraint_error('UNIQUE_ROW');
107 end if;
108 --
109 if p_rec.type in ('ITEM_TYPE','RESTRICTION_TYPE','KEY_TYPE') then
110 --
111 per_bil_shd.lookup_exists(p_code => p_rec.text_value1
112 ,p_type => 'GSP_'||p_rec.type);
113 --
114 if p_rec.type = 'RESTRICTION_TYPE' then
115 if p_rec.text_value4 is not null then
116 l_stmt := p_rec.text_value4;
117 per_bil_shd.check_restriction_sql (p_stmt => l_stmt
118 ,p_business_group_id => p_rec.business_group_id);
119 end if;
120 end if;
121 --
122 end if;
123 --
124 if p_rec.type = 'ITEM_TYPE_USAGE' and per_bil_shd.sequence_exist (p_rec => p_rec) then
125 per_bil_shd.constraint_error('UNIQUE_SEQUENCE');
126 end if;
127 --
128 if p_rec.type = 'RESTRICTION_VALUE' then
129 --
130 if not per_bil_shd.chk_date_valid(p_rec => p_rec) then
131 fnd_message.set_name('PER','HR_51155_INVAL_DATE_FORMAT');
132 fnd_message.raise_error;
133 end if;
134 --
135 per_bil_shd.valid_value(p_rec => p_rec);
136 --
137 end if;
138 --
139 hr_utility.set_location(' Leaving:'||l_proc, 10);
140 End insert_validate;
141 --
142 -- ----------------------------------------------------------------------------
143 -- |---------------------------< update_validate >----------------------------|
144 -- ----------------------------------------------------------------------------
145 Procedure update_validate(p_rec in per_bil_shd.g_rec_type) is
146 --
147 l_proc varchar2(72) := g_package||'update_validate';
148 l_stmt hr_summary_restriction_type.restriction_sql%type;
149 --
150 Begin
151 hr_utility.set_location('Entering:'||l_proc, 5);
152 --
153 -- Call all supporting business operations
154 --
155 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
156 --
157 chk_id_value
158 (p_id_value => p_rec.id_value,
159 p_object_version_number => p_rec.object_version_number);
160 --
161 -- Check to see if any of the (user-owned) fk values or name have changed
162 -- If they have then check too see if it is unique/valid
163 --
164 if (per_bil_shd.g_old_rec.fk_value1 <> p_rec.fk_value1 or
165 per_bil_shd.g_old_rec.fk_value2 <> p_rec.fk_value2 or
166 per_bil_shd.g_old_rec.text_value1 <> p_rec.text_value1) then
167 if per_bil_shd.row_exist (p_rec => p_rec) then
168 per_bil_shd.constraint_error('UNIQUE_ROW');
169 end if;
170 end if;
171 --
172 -- only check for unique sequence number if row being updated is not system-owned
173 --
174 if p_rec.type = 'ITEM_TYPE_USAGE'
175 and per_bil_shd.g_old_rec.num_value1 <> p_rec.num_value1 then
176 if per_bil_shd.sequence_exist (p_rec => p_rec) then
177 per_bil_shd.constraint_error('UNIQUE_SEQUENCE');
178 end if;
179 end if;
180 --
181 if p_rec.type in ('ITEM_TYPE','RESTRICTION_TYPE','KEY_TYPE') then
182 --
183 per_bil_shd.lookup_exists(p_code => p_rec.text_value1
184 ,p_type => 'GSP_'||p_rec.type);
185 --
186 if p_rec.type = 'RESTRICTION_TYPE' then
187 if p_rec.text_value4 is not null then
188 l_stmt := p_rec.text_value4;
189 per_bil_shd.check_restriction_sql (p_stmt => l_stmt
190 ,p_business_group_id => p_rec.business_group_id);
191 end if;
192 end if;
193 --
194 end if;
195 --
196 if p_rec.type = 'RESTRICTION_VALUE' then
197 if not per_bil_shd.chk_date_valid(p_rec => p_rec) then
198 fnd_message.set_name('PER','HR_51155_INVAL_DATE_FORMAT');
199 fnd_message.raise_error;
200 end if;
201 end if;
202 --
203 if p_rec.type = 'RESTRICTION_VALUE' then
204 per_bil_shd.valid_value(p_rec => p_rec);
205 end if;
206 --
207 hr_utility.set_location(' Leaving:'||l_proc, 10);
208 --
209 End update_validate;
210 --
211 -- ----------------------------------------------------------------------------
212 -- |---------------------------< delete_validate >----------------------------|
213 -- ----------------------------------------------------------------------------
214 Procedure delete_validate(p_rec in per_bil_shd.g_rec_type) is
215 --
216 l_proc varchar2(72) := g_package||'delete_validate';
217 l_type hr_summary.type%type;
218 --
219 cursor csr_check_children is
220 select type
221 from hr_summary
222 where (fk_value1 = p_rec.id_value
223 or fk_value2 = p_rec.id_value
224 or fk_value3 = p_rec.id_value);
225 --
226 Begin
227 hr_utility.set_location('Entering:'||l_proc, 5);
228 --
229 open csr_check_children;
230 fetch csr_check_children into l_type;
231 if csr_check_children%found then
232 close csr_check_children;
233 if l_type = 'ITEM_TYPE_USAGE' then
234 per_bil_shd.constraint_error('CHILD_RECORD_ITU');
235 elsif l_type = 'VALID_RESTRICTION' then
236 per_bil_shd.constraint_error('CHILD_RECORD_VR');
237 elsif l_type = 'RESTRICTION_USAGE' then
238 per_bil_shd.constraint_error('CHILD_RECORD_RTU');
239 elsif l_type = 'KEY_TYPE_USAGE' then
240 per_bil_shd.constraint_error('CHILD_RECORD_KTU');
241 elsif l_type = 'RESTRICTION_VALUE' then
242 per_bil_shd.constraint_error('CHILD_RECORD_RV');
243 elsif l_type = 'ITEM_VALUE' then
244 per_bil_shd.constraint_error('CHILD_RECORD_IV');
245 elsif l_type = 'KEY_VALUE' then
246 per_bil_shd.constraint_error('CHILD_RECORD_KV');
247 elsif l_type = 'VALID_KEY_TYPE' then
248 per_bil_shd.constraint_error('CHILD_RECORD_VKT');
249 elsif l_type = 'PROCESS_RUN' then
250 per_bil_shd.constraint_error('CHILD_RECORD_PR');
251 end if;
252 else
253 close csr_check_children;
254 end if;
255 --
256 hr_utility.set_location(' Leaving:'||l_proc, 10);
257 End delete_validate;
258 --
259 --
260 -- ---------------------------------------------------------------------------
261 -- |---------------------< return_legislation_code >-------------------------|
262 -- ---------------------------------------------------------------------------
263 --
264 function return_legislation_code
265 (p_id_value in number) return varchar2 is
266 --
267 -- Declare cursor
268 --
269 cursor csr_leg_code is
270 select a.legislation_code
271 from per_business_groups a,
272 hr_summary b
273 where b.id_value = p_id_value
274 and a.business_group_id = b.business_group_id;
275 --
276 -- Declare local variables
277 --
278 l_legislation_code varchar2(150);
279 l_proc varchar2(72) := g_package||'return_legislation_code';
280 --
281 begin
282 --
283 hr_utility.set_location('Entering:'|| l_proc, 10);
284 --
285 -- Ensure that all the mandatory parameter are not null
286 --
287 hr_api.mandatory_arg_error(p_api_name => l_proc,
288 p_argument => 'id_value',
289 p_argument_value => p_id_value);
290 --
291 open csr_leg_code;
292 --
293 fetch csr_leg_code into l_legislation_code;
294 --
295 if csr_leg_code%notfound then
296 --
297 close csr_leg_code;
298 --
299 -- The primary key is invalid therefore we must error
300 --
301 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
302 hr_utility.raise_error;
303 --
304 end if;
305 --
306 close csr_leg_code;
307 --
308 hr_utility.set_location(' Leaving:'|| l_proc, 20);
309 --
310 return l_legislation_code;
311 --
312 end return_legislation_code;
313 --
314 end per_bil_bus;
[Home] [Help]