[Home] [Help]
PACKAGE BODY: APPS.BEN_EPR_BUS
Source
1 Package Body ben_epr_bus as
2 /* $Header: beeprrhi.pkb 115.5 2002/12/09 12:52:58 lakrish ship $ */
3 --
4 -- ----------------------------------------------------------------------------
8 g_package varchar2(33) := ' ben_epr_bus.'; -- Global package name
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_enrt_prem_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 -- enrt_prem_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_enrt_prem_id(p_enrt_prem_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_enrt_prem_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_epr_shd.api_updating
47 (p_enrt_prem_id => p_enrt_prem_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_enrt_prem_id,hr_api.g_number)
52 <> ben_epr_shd.g_old_rec.enrt_prem_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_epr_shd.constraint_error('BEN_ENRT_PREM_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_enrt_prem_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_epr_shd.constraint_error('BEN_ENRT_PREM_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_enrt_prem_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_actl_prem_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_enrt_prem_id PK
89 -- p_actl_prem_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_actl_prem_id (p_enrt_prem_id in number,
103 p_actl_prem_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_actl_prem_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from ben_actl_prem_f a
114 where a.actl_prem_id = p_actl_prem_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_epr_shd.api_updating
124 (p_enrt_prem_id => p_enrt_prem_id,
125 p_object_version_number => p_object_version_number);
126 --
127 if (l_api_updating
128 and nvl(p_actl_prem_id,hr_api.g_number)
129 <> nvl(ben_epr_shd.g_old_rec.actl_prem_id,hr_api.g_number)
130 or not l_api_updating) then
131 --
132 -- check if actl_prem_id value exists in ben_actl_prem_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 ben_actl_prem_f
142 -- table.
143 --
144 ben_epr_shd.constraint_error('BEN_ENRT_PREM_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_actl_prem_id;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------< chk_elig_per_elctbl_chc_id >------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description
161 -- This procedure checks that a referenced foreign key actually exists
162 -- in the referenced table.
163 --
164 -- Pre-Conditions
165 -- None.
166 --
167 -- In Parameters
168 -- p_enrt_prem_id PK
169 -- p_elig_per_elctbl_chc_id ID of FK column
173 -- Processing continues
170 -- p_object_version_number object version number
171 --
172 -- Post Success
174 --
175 -- Post Failure
176 -- Error raised.
177 --
178 -- Access Status
179 -- Internal table handler use only.
180 --
181 Procedure chk_elig_per_elctbl_chc_id (p_enrt_prem_id in number,
182 p_elig_per_elctbl_chc_id in number,
183 p_object_version_number in number) is
184 --
185 l_proc varchar2(72) := g_package||'chk_elig_per_elctbl_chc_id';
186 l_api_updating boolean;
187 l_dummy varchar2(1);
188 --
189 cursor c1 is
190 select null
191 from ben_elig_per_elctbl_chc a
192 where a.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
193 --
194 Begin
195 --
196 hr_utility.set_location('Entering:'||l_proc,5);
197 --
198 l_api_updating := ben_epr_shd.api_updating
199 (p_enrt_prem_id => p_enrt_prem_id,
200 p_object_version_number => p_object_version_number);
201 --
202 if (l_api_updating
203 and nvl(p_elig_per_elctbl_chc_id,hr_api.g_number)
204 <> nvl(ben_epr_shd.g_old_rec.elig_per_elctbl_chc_id,hr_api.g_number)
205 or not l_api_updating) and
206 p_elig_per_elctbl_chc_id is not null then
207 --
208 -- check if elig_per_elctbl_chc_id value exists in ben_elig_per_elctbl_chc table
209 --
210 open c1;
211 --
212 fetch c1 into l_dummy;
213 if c1%notfound then
214 --
215 close c1;
216 --
217 -- raise error as FK does not relate to PK in ben_elig_per_elctbl_chc
218 -- table.
219 --
220 ben_epr_shd.constraint_error('BEN_ENRT_PREM_FK2');
221 --
222 end if;
223 --
224 close c1;
225 --
226 end if;
227 --
228 hr_utility.set_location('Leaving:'||l_proc,10);
229 --
230 End chk_elig_per_elctbl_chc_id;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |------< chk_enrt_bnft_id >------|
234 -- ----------------------------------------------------------------------------
235 --
236 -- Description
237 -- This procedure checks that a referenced foreign key actually exists
238 -- in the referenced table.
239 --
240 -- Pre-Conditions
241 -- None.
242 --
243 -- In Parameters
244 -- p_enrt_prem_id PK
245 -- p_enrt_bnft_id ID of FK column
246 -- p_object_version_number object version number
247 --
248 -- Post Success
249 -- Processing continues
250 --
251 -- Post Failure
252 -- Error raised.
253 --
254 -- Access Status
255 -- Internal table handler use only.
256 --
257 Procedure chk_enrt_bnft_id (p_enrt_prem_id in number,
258 p_enrt_bnft_id in number,
259 p_object_version_number in number) is
260 --
261 l_proc varchar2(72) := g_package||'chk_enrt_bnft_id';
262 l_api_updating boolean;
263 l_dummy varchar2(1);
264 --
265 cursor c1 is
266 select null
267 from ben_enrt_bnft a
268 where a.enrt_bnft_id = p_enrt_bnft_id;
269 --
270 Begin
271 --
272 hr_utility.set_location('Entering:'||l_proc,5);
273 --
274 l_api_updating := ben_epr_shd.api_updating
275 (p_enrt_prem_id => p_enrt_prem_id,
276 p_object_version_number => p_object_version_number);
277 --
278 if (l_api_updating
279 and nvl(p_enrt_bnft_id,hr_api.g_number)
280 <> nvl(ben_epr_shd.g_old_rec.enrt_bnft_id,hr_api.g_number)
281 or not l_api_updating) and
282 p_enrt_bnft_id is not null then
283 --
284 -- check if enrt_bnft_id value exists in ben_enrt_bnft table
285 --
286 open c1;
287 --
288 fetch c1 into l_dummy;
289 if c1%notfound then
290 --
291 close c1;
292 --
293 -- raise error as FK does not relate to PK in ben_enrt_bnft
294 -- table.
295 --
296 ben_epr_shd.constraint_error('BEN_ENRT_PREM_FK1');
297 --
298 end if;
299 --
300 close c1;
301 --
302 end if;
303 --
304 hr_utility.set_location('Leaving:'||l_proc,10);
305 --
306 End chk_enrt_bnft_id;
307 --
308 -- ----------------------------------------------------------------------------
309 -- |---------------------------< insert_validate >----------------------------|
310 -- ----------------------------------------------------------------------------
311 Procedure insert_validate(p_rec in ben_epr_shd.g_rec_type) is
312 --
313 l_proc varchar2(72) := g_package||'insert_validate';
314 --
315 Begin
316 hr_utility.set_location('Entering:'||l_proc, 5);
317 --
318 -- Call all supporting business operations
319 --
320 --
321 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
322 --
323 chk_enrt_prem_id
324 (p_enrt_prem_id => p_rec.enrt_prem_id,
325 p_object_version_number => p_rec.object_version_number);
326 --
327 chk_elig_per_elctbl_chc_id
328 (p_enrt_prem_id => p_rec.enrt_prem_id,
329 p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
330 p_object_version_number => p_rec.object_version_number);
331 --
332 chk_enrt_bnft_id
333 (p_enrt_prem_id => p_rec.enrt_prem_id,
334 p_enrt_bnft_id => p_rec.enrt_bnft_id,
335 p_object_version_number => p_rec.object_version_number);
336 --
337 hr_utility.set_location(' Leaving:'||l_proc, 10);
338 End insert_validate;
339 --
340 -- ----------------------------------------------------------------------------
341 -- |---------------------------< update_validate >----------------------------|
342 -- ----------------------------------------------------------------------------
343 Procedure update_validate(p_rec in ben_epr_shd.g_rec_type) is
344 --
345 l_proc varchar2(72) := g_package||'update_validate';
346 --
347 Begin
348 hr_utility.set_location('Entering:'||l_proc, 5);
349 --
350 -- Call all supporting business operations
351 --
352 --
353 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
354 --
355 chk_enrt_prem_id
356 (p_enrt_prem_id => p_rec.enrt_prem_id,
357 p_object_version_number => p_rec.object_version_number);
358 --
359 chk_elig_per_elctbl_chc_id
360 (p_enrt_prem_id => p_rec.enrt_prem_id,
361 p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
362 p_object_version_number => p_rec.object_version_number);
363 --
364 chk_enrt_bnft_id
365 (p_enrt_prem_id => p_rec.enrt_prem_id,
366 p_enrt_bnft_id => p_rec.enrt_bnft_id,
367 p_object_version_number => p_rec.object_version_number);
368 --
369 hr_utility.set_location(' Leaving:'||l_proc, 10);
370 End update_validate;
371 --
372 -- ----------------------------------------------------------------------------
373 -- |---------------------------< delete_validate >----------------------------|
374 -- ----------------------------------------------------------------------------
375 Procedure delete_validate(p_rec in ben_epr_shd.g_rec_type) 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_enrt_prem_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_enrt_prem b
401 where b.enrt_prem_id = p_enrt_prem_id
402 and a.business_group_id = b.business_group_id;
403 --
404 -- Declare local variables
405 --
406 l_legislation_code varchar2(150);
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 => 'enrt_prem_id',
417 p_argument_value => p_enrt_prem_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_epr_bus;