[Home] [Help]
PACKAGE BODY: APPS.BEN_CQB_BUS
Source
1 Package Body ben_cqb_bus as
2 /* $Header: becqbrhi.pkb 115.7 2002/12/16 10:30:14 rpgupta ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cqb_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_cbr_quald_bnf_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 -- cbr_quald_bnf_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_cbr_quald_bnf_id(p_cbr_quald_bnf_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_cbr_quald_bnf_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_cqb_shd.api_updating
47 (p_cbr_quald_bnf_id => p_cbr_quald_bnf_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_cbr_quald_bnf_id,hr_api.g_number)
52 <> ben_cqb_shd.g_old_rec.cbr_quald_bnf_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_cqb_shd.constraint_error('BEN_CBR_QUALD_BNF_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_cbr_quald_bnf_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_cqb_shd.constraint_error('BEN_CBR_QUALD_BNF_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_cbr_quald_bnf_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |---------------------< chk_pgm_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_cbr_quald_bnf_id PK
89 -- p_pgm_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_pgm_id (p_cbr_quald_bnf_id in number,
102 p_pgm_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_pgm_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from ben_pgm_f a
112 where a.pgm_id = p_pgm_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := ben_cqb_shd.api_updating
119 (p_cbr_quald_bnf_id => p_cbr_quald_bnf_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_pgm_id,hr_api.g_number)
124 <> nvl(ben_cqb_shd.g_old_rec.pgm_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if pgm_id value exists in ben_pgm_f table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in ben_pgm_f
137 -- table.
138 --
139 ben_cqb_shd.constraint_error('BEN_CBR_QUALD_BNF_DT3');
140 --
141 end if;
142 --
143 close c1;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_pgm_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |---------------------< chk_ptip_id >--------------------------------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure checks that a referenced foreign key actually exists
157 -- in the referenced table.
158 --
159 -- Pre-Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- p_cbr_quald_bnf_id PK
164 -- p_ptip_id ID of FK column
165 -- p_object_version_number object version number
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error raised.
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_ptip_id(p_cbr_quald_bnf_id in number,
177 p_ptip_id in number,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_ptip_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from ben_ptip_f a
187 where a.ptip_id = p_ptip_id;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := ben_cqb_shd.api_updating
194 (p_cbr_quald_bnf_id => p_cbr_quald_bnf_id,
195 p_object_version_number => p_object_version_number);
196 --
197 if (l_api_updating
198 and nvl(p_ptip_id,hr_api.g_number)
199 <> nvl(ben_cqb_shd.g_old_rec.ptip_id,hr_api.g_number)
200 or not l_api_updating)and
201 p_ptip_id is not null then
202 --
203 -- check if ptip_id value exists in ben_ptip_f table
204 --
205 open c1;
206 --
207 fetch c1 into l_dummy;
208 if c1%notfound then
209 --
210 close c1;
211 --
212 -- raise error as FK does not relate to PK in ben_ptip_f
213 -- table.
214 --
215 ben_cqb_shd.constraint_error('BEN_CBR_QUALD_BNF_DT5');
216 --
217 end if;
218 --
219 close c1;
220 --
221 end if;
222 --
223 hr_utility.set_location('Leaving:'||l_proc,10);
224 --
225 End chk_ptip_id;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |---------------------< chk_pl_typ_id >--------------------------------|
229 -- ----------------------------------------------------------------------------
230 --
231 -- Description
232 -- This procedure checks that a referenced foreign key actually exists
233 -- in the referenced table.
234 --
235 -- Pre-Conditions
236 -- None.
237 --
238 -- In Parameters
239 -- p_cbr_quald_bnf_id PK
240 -- p_pl_typ_id ID of FK column
241 -- p_object_version_number object version number
242 --
243 -- Post Success
244 -- Processing continues
245 --
246 -- Post Failure
247 -- Error raised.
248 --
249 -- Access Status
250 -- Internal table handler use only.
251 --
252 Procedure chk_pl_typ_id(p_cbr_quald_bnf_id in number,
253 p_pl_typ_id in number,
254 p_object_version_number in number) is
255 --
256 l_proc varchar2(72) := g_package||'chk_pl_typ_id';
257 l_api_updating boolean;
258 l_dummy varchar2(1);
259 --
260 cursor c1 is
261 select null
262 from ben_pl_typ_f a
263 where a.pl_typ_id = p_pl_typ_id;
264 --
265 Begin
266 --
267 hr_utility.set_location('Entering:'||l_proc,5);
268 --
269 l_api_updating := ben_cqb_shd.api_updating
270 (p_cbr_quald_bnf_id => p_cbr_quald_bnf_id,
271 p_object_version_number => p_object_version_number);
272 --
273 if (l_api_updating
274 and nvl(p_pl_typ_id,hr_api.g_number)
275 <> nvl(ben_cqb_shd.g_old_rec.pl_typ_id,hr_api.g_number)
276 or not l_api_updating) and
277 p_pl_typ_id is not null then
278 --
279 -- check if pl_typ_id value exists in ben_pl_typ_f table
280 --
281 open c1;
282 --
283 fetch c1 into l_dummy;
284 if c1%notfound then
285 --
286 close c1;
287 --
288 -- raise error as FK does not relate to PK in ben_pl_typ_f
289 -- table.
290 --
291 ben_cqb_shd.constraint_error('BEN_CBR_QUALD_BNF_DT4');
292 --
293 end if;
294 --
295 close c1;
296 --
297 end if;
298 --
299 hr_utility.set_location('Leaving:'||l_proc,10);
300 --
301 End chk_pl_typ_id;
302 --
303 -- ----------------------------------------------------------------------------
304 -- |------< chk_quald_bnf_flag >------|
305 -- ----------------------------------------------------------------------------
306 --
307 -- Description
308 -- This procedure is used to check that the lookup value is valid.
309 --
310 -- Pre Conditions
311 -- None.
312 --
313 -- In Parameters
314 -- cbr_quald_bnf_id PK of record being inserted or updated.
315 -- quald_bnf_flag Value of lookup code.
316 -- effective_date effective date
317 -- object_version_number Object version number of record being
318 -- inserted or updated.
319 --
320 -- Post Success
321 -- Processing continues
322 --
323 -- Post Failure
324 -- Error handled by procedure
325 --
326 -- Access Status
327 -- Internal table handler use only.
328 --
329 Procedure chk_quald_bnf_flag(p_cbr_quald_bnf_id in number,
330 p_quald_bnf_flag in varchar2,
331 p_cbr_elig_perd_strt_dt in date,
332 p_cbr_elig_perd_end_dt in date,
333 p_effective_date in date,
334 p_object_version_number in number) is
335 --
336 l_proc varchar2(72) := g_package||'chk_quald_bnf_flag';
337 l_api_updating boolean;
338 --
339 Begin
340 --
341 hr_utility.set_location('Entering:'||l_proc, 5);
342 --
343 l_api_updating := ben_cqb_shd.api_updating
344 (p_cbr_quald_bnf_id => p_cbr_quald_bnf_id,
345 p_object_version_number => p_object_version_number);
346 --
347 if (l_api_updating
348 and p_quald_bnf_flag
349 <> nvl(ben_cqb_shd.g_old_rec.quald_bnf_flag,hr_api.g_varchar2)
350 or not l_api_updating) then
351 --
352 -- check if value of lookup falls within lookup type.
353 --
354 --
355 if hr_api.not_exists_in_hr_lookups
356 (p_lookup_type => 'YES_NO',
357 p_lookup_code => p_quald_bnf_flag,
358 p_effective_date => p_effective_date) then
359 --
360 -- raise error as does not exist as lookup
361 --
362 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
363 fnd_message.set_token('FIELD','p_quald_bnf_flag');
364 fnd_message.set_token('TYPE','YES_NO');
365 fnd_message.raise_error;
366 --
367 end if;
368 --
369 -- If qualified beneficiary, check that the cobra eligibility
370 -- start and end dates are entered.
371 --
372 if (p_quald_bnf_flag = 'Y' and
373 (p_cbr_elig_perd_strt_dt is null or
374 p_cbr_elig_perd_end_dt is null)) then
375 --
376 -- raise error as does not exist as lookup
377 --
378 fnd_message.set_name('BEN','BEN_92461_CBR_DATES_NULL');
379 fnd_message.raise_error;
380 end if;
381 --
382 end if;
383 --
384 hr_utility.set_location('Leaving:'||l_proc,10);
385 --
386 end chk_quald_bnf_flag;
387 --
388 -- ----------------------------------------------------------------------------
389 -- |------< chk_cbr_inelg_rsn_cd >--------------------------------------------|
390 -- ----------------------------------------------------------------------------
391 --
392 -- Description
393 -- This procedure is used to check that the lookup value is valid.
394 --
395 -- Pre Conditions
396 -- None.
397 --
398 -- In Parameters
399 -- cbr_quald_bnf_id PK of record being inserted or updated.
400 -- cbr_inelg_rsn_cd Value of lookup code.
401 -- effective_date effective date
402 -- object_version_number Object version number of record being
403 -- inserted or updated.
404 --
405 -- Post Success
406 -- Processing continues
407 --
408 -- Post Failure
409 -- Error handled by procedure
410 --
411 -- Access Status
412 -- Internal table handler use only.
413 --
414 Procedure chk_cbr_inelg_rsn_cd(p_cbr_quald_bnf_id in number,
415 p_cbr_inelg_rsn_cd in varchar2,
416 p_effective_date in date,
417 p_object_version_number in number) is
418 --
419 l_proc varchar2(72) := g_package||'chk_cbr_inelg_rsn_cd';
420 l_api_updating boolean;
421 --
422 Begin
423 --
424 hr_utility.set_location('Entering:'||l_proc, 5);
425 --
426 l_api_updating := ben_cqb_shd.api_updating
427 (p_cbr_quald_bnf_id => p_cbr_quald_bnf_id,
428 p_object_version_number => p_object_version_number);
429 --
430 if (l_api_updating
431 and p_cbr_inelg_rsn_cd
432 <> nvl(ben_cqb_shd.g_old_rec.cbr_inelg_rsn_cd,hr_api.g_varchar2)
433 or not l_api_updating)
434 and p_cbr_inelg_rsn_cd is not null then
435 --
436 -- check if value of lookup falls within lookup type.
437 --
438 --
439 if hr_api.not_exists_in_hr_lookups
440 (p_lookup_type => 'BEN_CBR_INELG_RSN',
441 p_lookup_code => p_cbr_inelg_rsn_cd,
442 p_effective_date => p_effective_date) then
443 --
444 -- raise error as does not exist as lookup
445 --
446 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
447 fnd_message.set_token('FIELD','p_cbr_inelg_rsn_cd');
448 fnd_message.set_token('TYPE','BEN_CBR_INELG_RSN');
449 fnd_message.raise_error;
450 --
451 end if;
452 --
453 end if;
454 --
455 hr_utility.set_location('Leaving:'||l_proc,10);
456 --
457 end chk_cbr_inelg_rsn_cd;
458 --
459 -- ----------------------------------------------------------------------------
460 -- |---------------------------< insert_validate >----------------------------|
461 -- ----------------------------------------------------------------------------
462 Procedure insert_validate(p_rec in ben_cqb_shd.g_rec_type
463 ,p_effective_date in date) is
464 --
465 l_proc varchar2(72) := g_package||'insert_validate';
466 --
467 Begin
468 hr_utility.set_location('Entering:'||l_proc, 5);
469 --
470 -- Call all supporting business operations
471 --
472 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
473 --
474 chk_cbr_quald_bnf_id
475 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
476 p_object_version_number => p_rec.object_version_number);
477 --
478 chk_pgm_id
479 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
480 p_pgm_id => p_rec.pgm_id,
481 p_object_version_number => p_rec.object_version_number);
482 --
483 chk_ptip_id
484 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
485 p_ptip_id => p_rec.ptip_id,
486 p_object_version_number => p_rec.object_version_number);
487 --
488 chk_pl_typ_id
489 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
490 p_pl_typ_id => p_rec.pl_typ_id,
491 p_object_version_number => p_rec.object_version_number);
492 --
493 chk_quald_bnf_flag
494 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
495 p_quald_bnf_flag => p_rec.quald_bnf_flag,
496 p_cbr_elig_perd_strt_dt => p_rec.cbr_elig_perd_strt_dt,
497 p_cbr_elig_perd_end_dt => p_rec.cbr_elig_perd_end_dt,
498 p_effective_date => p_effective_date,
499 p_object_version_number => p_rec.object_version_number);
500 --
501 chk_cbr_inelg_rsn_cd
502 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
503 p_cbr_inelg_rsn_cd => p_rec.cbr_inelg_rsn_cd,
504 p_effective_date => p_effective_date,
505 p_object_version_number => p_rec.object_version_number);
506 --
507 hr_utility.set_location(' Leaving:'||l_proc, 10);
508 End insert_validate;
509 --
510 -- ----------------------------------------------------------------------------
511 -- |---------------------------< update_validate >----------------------------|
512 -- ----------------------------------------------------------------------------
513 Procedure update_validate(p_rec in ben_cqb_shd.g_rec_type
514 ,p_effective_date in date) is
515 --
516 l_proc varchar2(72) := g_package||'update_validate';
517 --
518 Begin
519 hr_utility.set_location('Entering:'||l_proc, 5);
520 --
521 -- Call all supporting business operations
522 --
523 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
524 --
525 chk_cbr_quald_bnf_id
526 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
527 p_object_version_number => p_rec.object_version_number);
528 --
529 chk_pgm_id
530 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
531 p_pgm_id => p_rec.pgm_id,
532 p_object_version_number => p_rec.object_version_number);
533 --
534 chk_ptip_id
535 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
536 p_ptip_id => p_rec.ptip_id,
537 p_object_version_number => p_rec.object_version_number);
538 --
539 chk_pl_typ_id
540 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
541 p_pl_typ_id => p_rec.pl_typ_id,
542 p_object_version_number => p_rec.object_version_number);
543 --
544 chk_quald_bnf_flag
545 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
546 p_quald_bnf_flag => p_rec.quald_bnf_flag,
547 p_cbr_elig_perd_strt_dt => p_rec.cbr_elig_perd_strt_dt,
548 p_cbr_elig_perd_end_dt => p_rec.cbr_elig_perd_end_dt,
549 p_effective_date => p_effective_date,
550 p_object_version_number => p_rec.object_version_number);
551 --
552 chk_cbr_inelg_rsn_cd
553 (p_cbr_quald_bnf_id => p_rec.cbr_quald_bnf_id,
554 p_cbr_inelg_rsn_cd => p_rec.cbr_inelg_rsn_cd,
555 p_effective_date => p_effective_date,
556 p_object_version_number => p_rec.object_version_number);
557 --
558 hr_utility.set_location(' Leaving:'||l_proc, 10);
559 End update_validate;
560 --
561 -- ----------------------------------------------------------------------------
562 -- |---------------------------< delete_validate >----------------------------|
563 -- ----------------------------------------------------------------------------
564 Procedure delete_validate(p_rec in ben_cqb_shd.g_rec_type
565 ,p_effective_date in date) is
566 --
567 l_proc varchar2(72) := g_package||'delete_validate';
568 --
569 Begin
570 hr_utility.set_location('Entering:'||l_proc, 5);
571 --
572 -- Call all supporting business operations
573 --
574 hr_utility.set_location(' Leaving:'||l_proc, 10);
575 End delete_validate;
576 --
577 --
578 -- ---------------------------------------------------------------------------
579 -- |---------------------< return_legislation_code >-------------------------|
580 -- ---------------------------------------------------------------------------
581 --
582 function return_legislation_code
583 (p_cbr_quald_bnf_id in number) return varchar2 is
584 --
585 -- Declare cursor
586 --
587 cursor csr_leg_code is
588 select a.legislation_code
589 from per_business_groups a,
590 ben_cbr_quald_bnf b
591 where b.cbr_quald_bnf_id = p_cbr_quald_bnf_id
592 and a.business_group_id = b.business_group_id;
593 --
594 -- Declare local variables
595 --
596 l_legislation_code varchar2(150);
597 l_proc varchar2(72) := g_package||'return_legislation_code';
598 --
599 begin
600 --
601 hr_utility.set_location('Entering:'|| l_proc, 10);
602 --
603 -- Ensure that all the mandatory parameter are not null
604 --
605 hr_api.mandatory_arg_error(p_api_name => l_proc,
606 p_argument => 'cbr_quald_bnf_id',
607 p_argument_value => p_cbr_quald_bnf_id);
608 --
609 open csr_leg_code;
610 --
611 fetch csr_leg_code into l_legislation_code;
612 --
613 if csr_leg_code%notfound then
614 --
615 close csr_leg_code;
616 --
617 -- The primary key is invalid therefore we must error
618 --
619 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
620 fnd_message.raise_error;
621 --
622 end if;
623 --
624 close csr_leg_code;
625 --
626 hr_utility.set_location(' Leaving:'|| l_proc, 20);
627 --
628 return l_legislation_code;
629 --
630 end return_legislation_code;
631 --
632 end ben_cqb_bus;