[Home] [Help]
PACKAGE BODY: APPS.BEN_XRE_BUS
Source
1 Package Body ben_xre_bus as
2 /* $Header: bexrerhi.pkb 115.8 2002/12/16 17:41:12 glingapp ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_xre_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ext_rslt_err_id >------|
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 -- ext_rslt_err_id 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_ext_rslt_err_id(p_ext_rslt_err_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_ext_rslt_err_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_xre_shd.api_updating
47 (p_ext_rslt_err_id => p_ext_rslt_err_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_ext_rslt_err_id,hr_api.g_number)
52 <> ben_xre_shd.g_old_rec.ext_rslt_err_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_xre_shd.constraint_error('BEN_EXT_RSLT_ERR_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_ext_rslt_err_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_xre_shd.constraint_error('BEN_EXT_RSLT_ERR_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_ext_rslt_err_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_person_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_ext_rslt_err_id PK
89 -- p_person_id ID of FK column
90 -- p_effective_date Session Date of record
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_person_id (p_ext_rslt_err_id in number,
103 p_person_id in number,
104 p_effective_date in date,
105 p_object_version_number in number) is
106 --
107 l_proc varchar2(72) := g_package||'chk_person_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from per_all_people_f a
114 where a.person_id = p_person_id
115 and p_effective_date
116 between a.effective_start_date
117 and a.effective_end_date;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_xre_shd.api_updating
124 (p_ext_rslt_err_id => p_ext_rslt_err_id,
125 p_object_version_number => p_object_version_number);
126 --
127 if (l_api_updating
128 and nvl(p_person_id,hr_api.g_number)
129 <> nvl(ben_xre_shd.g_old_rec.person_id,hr_api.g_number)
130 or not l_api_updating) then
131 --
132 -- check if person_id value exists in per_all_people_f table
133 --
134 open c1;
135 --
136 fetch c1 into l_dummy;
137 if c1%notfound then
138 --
139 close c1;
140 --
141 -- raise error as FK does not relate to PK in per_all_people_f
142 -- table.
143 --
144 ben_xre_shd.constraint_error('BEN_EXT_RSLT_ERR_DT1');
145 --
146 end if;
147 --
148 close c1;
149 --
150 end if;
151 --
152 hr_utility.set_location('Leaving:'||l_proc,10);
153 --
154 End chk_person_id;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------< chk_typ_cd >------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description
161 -- This procedure is used to check that the lookup value is valid.
162 --
163 -- Pre Conditions
164 -- None.
165 --
166 -- In Parameters
167 -- ext_rslt_err_id PK of record being inserted or updated.
168 -- typ_cd Value of lookup code.
169 -- effective_date effective date
170 -- object_version_number Object version number of record being
171 -- inserted or updated.
172 --
173 -- Post Success
174 -- Processing continues
175 --
176 -- Post Failure
177 -- Error handled by procedure
178 --
179 -- Access Status
180 -- Internal table handler use only.
181 --
182 Procedure chk_typ_cd(p_ext_rslt_err_id in number,
183 p_typ_cd in varchar2,
184 p_effective_date in date,
185 p_object_version_number in number) is
186 --
187 l_proc varchar2(72) := g_package||'chk_typ_cd';
188 l_api_updating boolean;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc, 5);
193 --
194 l_api_updating := ben_xre_shd.api_updating
195 (p_ext_rslt_err_id => p_ext_rslt_err_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and p_typ_cd
200 <> nvl(ben_xre_shd.g_old_rec.typ_cd,hr_api.g_varchar2)
201 or not l_api_updating)
202 and p_typ_cd is not null then
203 --
204 -- check if value of lookup falls within lookup type.
205 --
206 if hr_api.not_exists_in_hr_lookups
207 (p_lookup_type => 'BEN_EXT_ERR_TYP',
208 p_lookup_code => p_typ_cd,
209 p_effective_date => p_effective_date) then
210 --
211 -- raise error as does not exist as lookup
212 --
213 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
214 fnd_message.set_token('FIELD','p_typ_cd');
215 fnd_message.set_token('TYPE','BEN_EXT_ERR_TYP');
216 fnd_message.raise_error;
217 --
218 end if;
219 --
220 end if;
221 --
222 hr_utility.set_location('Leaving:'||l_proc,10);
223 --
224 end chk_typ_cd;
225 --
226 -- ---------------------------------------------------------------------------
227 -- |--------------<chk_ext_rslt_id>------------------------------------------|
228 -- ---------------------------------------------------------------------------
229 --
230 -- Description
231 -- This procedure checks that a referenced foreign key actually exists
232 -- in the referenced table.
233 --
234 -- Pre-Conditions
235 -- None
236 --
237 -- In Parameters
238 -- p_ext_rslt_err_id PK
239 -- p_ext_rslt_id ID of FK column
240 -- p_object_version_number object version number
241 --
242 -- Post Success
243 -- Processing continues
244 --
245 -- Post Failure
246 -- Error raised
247 --
248 -- Access Status
249 -- Internal table handler use only
250 --
251 Procedure chk_ext_rslt_id (p_ext_rslt_err_id in number,
252 p_ext_rslt_id in number,
253 p_object_version_number in number) is
254 --
255 l_proc varchar2(72) := g_package||'chk_ext_rslt_id';
256 l_api_updating boolean;
257 l_dummy varchar2(1);
258 --
259 cursor c1 is
260 select null
261 from ben_ext_rslt a
262 where a.ext_rslt_id = p_ext_rslt_id;
263 --
264 Begin
265 --
266 hr_utility.set_location('Entering:'||l_proc, 5);
267 --
268 if p_ext_rslt_id is not null then
269 l_api_updating := ben_xre_shd.api_updating
270 (p_ext_rslt_err_id => p_ext_rslt_err_id,
271 p_object_version_number => p_object_version_number);
272 --
273 if (l_api_updating
274 and nvl(p_ext_rslt_id, hr_api.g_number)
275 <> nvl(ben_xre_shd.g_old_rec.ext_rslt_id, hr_api.g_number)
276 or not l_api_updating) then
277 --
278 -- check if ext_rslt_id value exists in ben_ext_rslt table
279 --
280 open c1;
281 --
282 fetch c1 into l_dummy;
283 if c1%notfound then
284 --
285 close c1;
286 --
287 -- raise error for constraint violation
288 --
289 ben_xre_shd.constraint_error('BEN_EXT_RSLT_ERR_FK2');
290 --
291 end if;
292 --
293 close c1;
294 --
295 end if;
296 --
297 end if;
298 --
299 hr_utility.set_location('Leaving:'||l_proc, 10);
300 --
301 End chk_ext_rslt_id;
302 --
303 -- ----------------------------------------------------------------------------
304 -- |---------------------------< insert_validate >----------------------------|
305 -- ----------------------------------------------------------------------------
306 Procedure insert_validate(p_rec in ben_xre_shd.g_rec_type
307 ,p_effective_date in date) is
308 --
309 l_proc varchar2(72) := g_package||'insert_validate';
310 --
311 Begin
312 hr_utility.set_location('Entering:'||l_proc, 5);
313 --
314 -- Call all supporting business operations
315 --
316 --
317 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
318 --
319 chk_ext_rslt_err_id
320 (p_ext_rslt_err_id => p_rec.ext_rslt_err_id,
321 p_object_version_number => p_rec.object_version_number);
322 --
323 chk_typ_cd
324 (p_ext_rslt_err_id => p_rec.ext_rslt_err_id,
325 p_typ_cd => p_rec.typ_cd,
326 p_effective_date => p_effective_date,
327 p_object_version_number => p_rec.object_version_number);
328 --
329 chk_ext_rslt_id
330 (p_ext_rslt_err_id => p_rec.ext_rslt_err_id,
331 p_ext_rslt_id => p_rec.ext_rslt_id,
332 p_object_version_number => p_rec.object_version_number);
333 --
334 hr_utility.set_location(' Leaving:'||l_proc, 10);
335 End insert_validate;
336 --
337 -- ----------------------------------------------------------------------------
338 -- |---------------------------< update_validate >----------------------------|
339 -- ----------------------------------------------------------------------------
340 Procedure update_validate(p_rec in ben_xre_shd.g_rec_type
341 ,p_effective_date in date) is
342 --
343 l_proc varchar2(72) := g_package||'update_validate';
344 --
345 Begin
346 hr_utility.set_location('Entering:'||l_proc, 5);
347 --
348 -- Call all supporting business operations
349 --
350 --
351 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
352 --
353 chk_ext_rslt_err_id
354 (p_ext_rslt_err_id => p_rec.ext_rslt_err_id,
355 p_object_version_number => p_rec.object_version_number);
356 --
357 chk_typ_cd
358 (p_ext_rslt_err_id => p_rec.ext_rslt_err_id,
359 p_typ_cd => p_rec.typ_cd,
360 p_effective_date => p_effective_date,
361 p_object_version_number => p_rec.object_version_number);
362 --
363 chk_ext_rslt_id
364 (p_ext_rslt_err_id => p_rec.ext_rslt_err_id,
365 p_ext_rslt_id => p_rec.ext_rslt_id,
366 p_object_version_number => p_rec.object_version_number);
367 --
368 hr_utility.set_location(' Leaving:'||l_proc, 10);
369 End update_validate;
370 --
371 -- ----------------------------------------------------------------------------
372 -- |---------------------------< delete_validate >----------------------------|
373 -- ----------------------------------------------------------------------------
374 Procedure delete_validate(p_rec in ben_xre_shd.g_rec_type
375 ,p_effective_date in date) is
376 --
377 l_proc varchar2(72) := g_package||'delete_validate';
378 --
379 Begin
380 hr_utility.set_location('Entering:'||l_proc, 5);
381 --
382 -- Call all supporting business operations
383 --
384 hr_utility.set_location(' Leaving:'||l_proc, 10);
385 End delete_validate;
386 --
387 --
388 -- ---------------------------------------------------------------------------
389 -- |---------------------< return_legislation_code >-------------------------|
390 -- ---------------------------------------------------------------------------
391 --
392 function return_legislation_code
393 (p_ext_rslt_err_id in number) return varchar2 is
394 --
395 -- Declare cursor
396 --
397 cursor csr_leg_code is
398 select a.legislation_code
399 from per_business_groups a,
400 ben_ext_rslt_err b
401 where b.ext_rslt_err_id = p_ext_rslt_err_id
402 and a.business_group_id = b.business_group_id;
403 --
404 -- Declare local variables
405 --
406 l_legislation_code per_business_groups.legislation_code%type ;
407 l_proc varchar2(72) := g_package||'return_legislation_code';
408 --
409 begin
410 --
411 hr_utility.set_location('Entering:'|| l_proc, 10);
412 --
413 -- Ensure that all the mandatory parameter are not null
414 --
415 hr_api.mandatory_arg_error(p_api_name => l_proc,
416 p_argument => 'ext_rslt_err_id',
417 p_argument_value => p_ext_rslt_err_id);
418 --
419 open csr_leg_code;
420 --
421 fetch csr_leg_code into l_legislation_code;
422 --
423 if csr_leg_code%notfound then
424 --
425 close csr_leg_code;
426 --
427 -- The primary key is invalid therefore we must error
428 --
429 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
430 fnd_message.raise_error;
431 --
432 end if;
433 --
434 close csr_leg_code;
435 --
436 hr_utility.set_location(' Leaving:'|| l_proc, 20);
437 --
438 return l_legislation_code;
439 --
440 end return_legislation_code;
441 --
442 end ben_xre_bus;