[Home] [Help]
PACKAGE BODY: APPS.BEN_ECC_BUS
Source
1 Package Body ben_ecc_bus as
2 /* $Header: beeccrhi.pkb 120.0 2005/05/28 01:49:03 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ecc_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_elctbl_chc_ctfn_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 -- elctbl_chc_ctfn_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_elctbl_chc_ctfn_id(p_elctbl_chc_ctfn_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_elctbl_chc_ctfn_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_ecc_shd.api_updating
47 (p_elctbl_chc_ctfn_id => p_elctbl_chc_ctfn_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_elctbl_chc_ctfn_id,hr_api.g_number)
52 <> ben_ecc_shd.g_old_rec.elctbl_chc_ctfn_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_ecc_shd.constraint_error('BEN_ELCTBL_CHC_CTFN_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_elctbl_chc_ctfn_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_ecc_shd.constraint_error('BEN_ELCTBL_CHC_CTFN_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_elctbl_chc_ctfn_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_enrt_bnft_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_elctbl_chc_ctfn_id PK
89 -- p_enrt_bnft_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_enrt_bnft_id (p_elctbl_chc_ctfn_id in number,
102 p_enrt_bnft_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_enrt_bnft_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from ben_enrt_bnft a
112 where a.enrt_bnft_id = p_enrt_bnft_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 return;
119 l_api_updating := ben_enb_shd.api_updating
120 (p_enrt_bnft_id => p_enrt_bnft_id,
121 p_object_version_number => null);
122 --
123 if (l_api_updating
124 and nvl(p_enrt_bnft_id,hr_api.g_number)
125 <> nvl(ben_ecc_shd.g_old_rec.enrt_bnft_id,hr_api.g_number)
126 or not l_api_updating) and
127 p_enrt_bnft_id is not null then
128 --
129 -- check if enrt_bnft_id value exists in ben_enrt_bnft table
130 --
131 open c1;
132 --
133 fetch c1 into l_dummy;
134 if c1%notfound then
135 --
136 close c1;
137 --
138 -- raise error as FK does not relate to PK in ben_enrt_bnft
139 -- table.
140 --
141 ben_ecc_shd.constraint_error('BEN_ELCTBL_CHC_CTFN_FK2');
142 --
143 end if;
144 --
145 close c1;
146 --
147 end if;
148 --
149 hr_utility.set_location('Leaving:'||l_proc,10);
150 --
151 End chk_enrt_bnft_id;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |------< chk_rqd_flag >------|
155 -- ----------------------------------------------------------------------------
156 --
157 -- Description
158 -- This procedure is used to check that the lookup value is valid.
159 --
160 -- Pre Conditions
161 -- None.
162 --
163 -- In Parameters
164 -- elctbl_chc_ctfn_id PK of record being inserted or updated.
165 -- rqd_flag Value of lookup code.
166 -- effective_date effective date
167 -- object_version_number Object version number of record being
168 -- inserted or updated.
169 --
170 -- Post Success
171 -- Processing continues
172 --
173 -- Post Failure
174 -- Error handled by procedure
175 --
176 -- Access Status
177 -- Internal table handler use only.
178 --
179 Procedure chk_rqd_flag(p_elctbl_chc_ctfn_id in number,
180 p_rqd_flag in varchar2,
181 p_effective_date in date,
182 p_object_version_number in number) is
183 --
184 l_proc varchar2(72) := g_package||'chk_rqd_flag';
185 l_api_updating boolean;
186 --
187 Begin
188 --
189 hr_utility.set_location('Entering:'||l_proc, 5);
190 --
191 l_api_updating := ben_ecc_shd.api_updating
192 (p_elctbl_chc_ctfn_id => p_elctbl_chc_ctfn_id,
193 p_object_version_number => p_object_version_number);
194 --
195 if (l_api_updating
196 and p_rqd_flag
197 <> nvl(ben_ecc_shd.g_old_rec.rqd_flag,hr_api.g_varchar2)
198 or not l_api_updating) then
199 --
200 -- check if value of lookup falls within lookup type.
201 --
202 --
203 if hr_api.not_exists_in_hr_lookups
204 (p_lookup_type => 'YES_NO',
205 p_lookup_code => p_rqd_flag,
206 p_effective_date => p_effective_date) then
207 --
208 -- raise error as does not exist as lookup
209 --
210 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
211 fnd_message.raise_error;
212 --
213 end if;
214 --
215 end if;
216 --
217 hr_utility.set_location('Leaving:'||l_proc,10);
218 --
219 end chk_rqd_flag;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |------< chk_enrt_ctfn_typ_cd >------|
223 -- ----------------------------------------------------------------------------
224 --
225 -- Description
226 -- This procedure is used to check that the lookup value is valid.
227 --
228 -- Pre Conditions
229 -- None.
230 --
231 -- In Parameters
232 -- elctbl_chc_ctfn_id PK of record being inserted or updated.
233 -- enrt_ctfn_typ_cd Value of lookup code.
234 -- effective_date effective date
235 -- object_version_number Object version number of record being
236 -- inserted or updated.
237 --
238 -- Post Success
239 -- Processing continues
240 --
241 -- Post Failure
242 -- Error handled by procedure
243 --
244 -- Access Status
245 -- Internal table handler use only.
246 --
247 Procedure chk_enrt_ctfn_typ_cd(p_elctbl_chc_ctfn_id in number,
248 p_enrt_ctfn_typ_cd in varchar2,
249 p_effective_date in date,
250 p_object_version_number in number) is
251 --
252 l_proc varchar2(72) := g_package||'chk_enrt_ctfn_typ_cd';
253 l_api_updating boolean;
254 --
255 Begin
256 --
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 l_api_updating := ben_ecc_shd.api_updating
260 (p_elctbl_chc_ctfn_id => p_elctbl_chc_ctfn_id,
261 p_object_version_number => p_object_version_number);
262 --
263 if (l_api_updating
264 and p_enrt_ctfn_typ_cd
265 <> nvl(ben_ecc_shd.g_old_rec.enrt_ctfn_typ_cd,hr_api.g_varchar2)
266 or not l_api_updating) then
267 --
268 -- check if value of lookup falls within lookup type.
269 --
270 --
271 if hr_api.not_exists_in_hr_lookups
272 (p_lookup_type => 'BEN_ENRT_CTFN_TYP',
273 p_lookup_code => p_enrt_ctfn_typ_cd,
274 p_effective_date => p_effective_date) then
275 --
276 -- raise error as does not exist as lookup
277 --
278 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
279 fnd_message.raise_error;
280 --
281 end if;
282 --
283 end if;
284 --
285 hr_utility.set_location('Leaving:'||l_proc,10);
286 --
287 end chk_enrt_ctfn_typ_cd;
288 --
289 Procedure chk_ctfn_determine_cd (p_elctbl_chc_ctfn_id in number,
290 p_ctfn_determine_cd in varchar2,
291 p_effective_date in date,
292 p_object_version_number in number) is
293 --
294 l_proc varchar2(72) := g_package||'chk_ctfn_determine_cd';
295 l_api_updating boolean;
296 --
297 Begin
298 --
299 hr_utility.set_location('Entering:'||l_proc, 5);
300 --
301 l_api_updating := ben_ecc_shd.api_updating
302 (p_elctbl_chc_ctfn_id => p_elctbl_chc_ctfn_id,
303 p_object_version_number => p_object_version_number);
304 --
305 if (l_api_updating
306 and p_ctfn_determine_cd
307 <> nvl(ben_ecc_shd.g_old_rec.ctfn_determine_cd,hr_api.g_varchar2)
308 or not l_api_updating) then
309 --
310 -- check if value of lookup falls within lookup type.
311 --
312 --
313 if hr_api.not_exists_in_hr_lookups
314 (p_lookup_type => 'BEN_DET_CTFN',
315 p_lookup_code => p_ctfn_determine_cd,
316 p_effective_date => p_effective_date) then
317 --
318 -- raise error as does not exist as lookup
319 --
320 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
321 fnd_message.raise_error;
322 --
323 end if;
324 --
325 end if;
326 --
327 hr_utility.set_location('Leaving:'||l_proc,10);
328 --
329 end chk_ctfn_determine_cd;
330 --
331 Procedure chk_susp_if_ctfn_not_prvd
332 (p_elctbl_chc_ctfn_id in number,
333 p_susp_if_ctfn_not_prvd_flag in varchar2,
334 p_effective_date in date,
335 p_object_version_number in number) is
336 --
337 l_proc varchar2(72) := g_package||'chk_susp_if_ctfn_not_prvd';
338 l_api_updating boolean;
339 --
340 Begin
341 --
342 hr_utility.set_location('Entering:'||l_proc, 5);
343 --
344 l_api_updating := ben_ecc_shd.api_updating
345 (p_elctbl_chc_ctfn_id => p_elctbl_chc_ctfn_id,
346 p_object_version_number => p_object_version_number);
347 --
348 if (l_api_updating
349 and p_susp_if_ctfn_not_prvd_flag
350 <> nvl(ben_ecc_shd.g_old_rec.susp_if_ctfn_not_prvd_flag,hr_api.g_varchar2)
351 or not l_api_updating) then
352 --
353 -- check if value of lookup falls within lookup type.
354 --
355 --
356 if hr_api.not_exists_in_hr_lookups
357 (p_lookup_type => 'YES_NO',
358 p_lookup_code => p_susp_if_ctfn_not_prvd_flag ,
359 p_effective_date => p_effective_date) then
360 --
361 -- raise error as does not exist as lookup
362 --
363 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
364 fnd_message.raise_error;
365 --
366 end if;
367 --
368 end if;
369 --
370 hr_utility.set_location('Leaving:'||l_proc,10);
371 --
372 end chk_susp_if_ctfn_not_prvd;
373
374 -- ----------------------------------------------------------------------------
375 -- |---------------------------< insert_validate >----------------------------|
376 -- ----------------------------------------------------------------------------
377 Procedure insert_validate(p_rec in ben_ecc_shd.g_rec_type
378 ,p_effective_date in date) is
379 --
380 l_proc varchar2(72) := g_package||'insert_validate';
381 --
382 Begin
383 hr_utility.set_location('Entering:'||l_proc, 5);
384 --
385 -- Call all supporting business operations
386 --
387 --
388 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
389 --
390 chk_elctbl_chc_ctfn_id
391 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
392 p_object_version_number => p_rec.object_version_number);
393 --
394 chk_enrt_bnft_id
395 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
396 p_enrt_bnft_id => p_rec.enrt_bnft_id,
397 p_object_version_number => p_rec.object_version_number);
398 --
399 chk_rqd_flag
400 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
401 p_rqd_flag => p_rec.rqd_flag,
402 p_effective_date => p_effective_date,
403 p_object_version_number => p_rec.object_version_number);
404 --
405 chk_enrt_ctfn_typ_cd
406 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
407 p_enrt_ctfn_typ_cd => p_rec.enrt_ctfn_typ_cd,
408 p_effective_date => p_effective_date,
409 p_object_version_number => p_rec.object_version_number);
410 --
411 /* chk_ctfn_determine_cd (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
412 p_ctfn_determine_cd => p_rec.ctfn_determine_cd,
413 p_effective_date => p_effective_date,
414 p_object_version_number => p_rec.object_version_number) ;
415 */
416 --
417 chk_susp_if_ctfn_not_prvd
418 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
419 p_susp_if_ctfn_not_prvd_flag => p_rec.susp_if_ctfn_not_prvd_flag,
420 p_effective_date => p_effective_date,
421 p_object_version_number => p_rec.object_version_number) ;
422 --
423 hr_utility.set_location(' Leaving:'||l_proc, 10);
424 End insert_validate;
425 --
426 -- ----------------------------------------------------------------------------
427 -- |---------------------------< update_validate >----------------------------|
428 -- ----------------------------------------------------------------------------
429 Procedure update_validate(p_rec in ben_ecc_shd.g_rec_type
430 ,p_effective_date in date) is
431 --
432 l_proc varchar2(72) := g_package||'update_validate';
433 --
434 Begin
435 hr_utility.set_location('Entering:'||l_proc, 5);
436 --
437 -- Call all supporting business operations
438 --
439 --
440 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
441 --
442 chk_elctbl_chc_ctfn_id
443 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
444 p_object_version_number => p_rec.object_version_number);
445 --
446 chk_enrt_bnft_id
447 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
448 p_enrt_bnft_id => p_rec.enrt_bnft_id,
449 p_object_version_number => p_rec.object_version_number);
450 --
451 chk_rqd_flag
452 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
453 p_rqd_flag => p_rec.rqd_flag,
454 p_effective_date => p_effective_date,
455 p_object_version_number => p_rec.object_version_number);
456 --
457 chk_enrt_ctfn_typ_cd
458 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
459 p_enrt_ctfn_typ_cd => p_rec.enrt_ctfn_typ_cd,
460 p_effective_date => p_effective_date,
461 p_object_version_number => p_rec.object_version_number);
462 --
463 /*
464 chk_ctfn_determine_cd (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
465 p_ctfn_determine_cd => p_rec.ctfn_determine_cd,
466 p_effective_date => p_effective_date,
467 p_object_version_number => p_rec.object_version_number) ;
468 */
469 --
470 chk_susp_if_ctfn_not_prvd
471 (p_elctbl_chc_ctfn_id => p_rec.elctbl_chc_ctfn_id,
472 p_susp_if_ctfn_not_prvd_flag => p_rec.susp_if_ctfn_not_prvd_flag,
473 p_effective_date => p_effective_date,
474 p_object_version_number => p_rec.object_version_number) ;
475 --
476
477 hr_utility.set_location(' Leaving:'||l_proc, 10);
478 End update_validate;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |---------------------------< delete_validate >----------------------------|
482 -- ----------------------------------------------------------------------------
483 Procedure delete_validate(p_rec in ben_ecc_shd.g_rec_type
484 ,p_effective_date in date) is
485 --
486 l_proc varchar2(72) := g_package||'delete_validate';
487 --
488 Begin
489 hr_utility.set_location('Entering:'||l_proc, 5);
490 --
491 -- Call all supporting business operations
492 --
493 hr_utility.set_location(' Leaving:'||l_proc, 10);
494 End delete_validate;
495 --
496 --
497 -- ---------------------------------------------------------------------------
498 -- |---------------------< return_legislation_code >-------------------------|
499 -- ---------------------------------------------------------------------------
500 --
501 function return_legislation_code
502 (p_elctbl_chc_ctfn_id in number) return varchar2 is
503 --
504 -- Declare cursor
505 --
506 cursor csr_leg_code is
507 select a.legislation_code
508 from per_business_groups a,
509 ben_elctbl_chc_ctfn b
510 where b.elctbl_chc_ctfn_id = p_elctbl_chc_ctfn_id
511 and a.business_group_id = b.business_group_id;
512 --
513 -- Declare local variables
514 --
515 l_legislation_code varchar2(150);
516 l_proc varchar2(72) := g_package||'return_legislation_code';
517 --
518 begin
519 --
520 hr_utility.set_location('Entering:'|| l_proc, 10);
521 --
522 -- Ensure that all the mandatory parameter are not null
523 --
524 hr_api.mandatory_arg_error(p_api_name => l_proc,
525 p_argument => 'elctbl_chc_ctfn_id',
526 p_argument_value => p_elctbl_chc_ctfn_id);
527 --
528 open csr_leg_code;
529 --
530 fetch csr_leg_code into l_legislation_code;
531 --
532 if csr_leg_code%notfound then
533 --
534 close csr_leg_code;
535 --
536 -- The primary key is invalid therefore we must error
537 --
538 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
539 fnd_message.raise_error;
540 --
541 end if;
542 --
543 close csr_leg_code;
544 --
545 hr_utility.set_location(' Leaving:'|| l_proc, 20);
546 --
547 return l_legislation_code;
548 --
549 end return_legislation_code;
550 --
551 end ben_ecc_bus;