[Home] [Help]
PACKAGE BODY: APPS.BEN_BDI_BUS
Source
1 Package Body ben_bdi_bus as
2 /* $Header: bebdirhi.pkb 120.0.12000000.2 2007/09/11 12:48:32 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_bdi_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< chk_batch_dpnt_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 --
25 -- inserted or updated.
22 -- In Parameters
23 -- batch_dpnt_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
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_batch_dpnt_id(p_batch_dpnt_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_batch_dpnt_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_bdi_shd.api_updating
47 (p_batch_dpnt_id => p_batch_dpnt_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_batch_dpnt_id,hr_api.g_number)
52 <> ben_bdi_shd.g_old_rec.batch_dpnt_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_bdi_shd.constraint_error('BEN_BATCH_DPNT_INFO_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_batch_dpnt_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_bdi_shd.constraint_error('BEN_BATCH_DPNT_INFO_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_batch_dpnt_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_batch_dpnt_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_batch_dpnt_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_bdi_shd.api_updating
124 (p_batch_dpnt_id => p_batch_dpnt_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_bdi_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_bdi_shd.constraint_error('BEN_BATCH_DPNT_INFO_DT5');
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_oipl_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_batch_dpnt_id PK
169 -- p_oipl_id ID of FK column
170 -- p_effective_date Session Date of record
171 -- p_object_version_number object version number
172 --
173 -- Post Success
174 -- Processing continues
175 --
176 -- Post Failure
177 -- Error raised.
178 --
179 -- Access Status
180 -- Internal table handler use only.
181 --
182 Procedure chk_oipl_id (p_batch_dpnt_id in number,
183 p_oipl_id in number,
184 p_effective_date in date,
185 p_object_version_number in number) is
186 --
187 l_proc varchar2(72) := g_package||'chk_oipl_id';
188 l_api_updating boolean;
189 l_dummy varchar2(1);
190 --
194 where a.oipl_id = p_oipl_id
191 cursor c1 is
192 select null
193 from ben_oipl_f a
195 and p_effective_date
196 between a.effective_start_date
197 and a.effective_end_date;
198 --
199 Begin
200 --
201 hr_utility.set_location('Entering:'||l_proc,5);
202 --
203 l_api_updating := ben_bdi_shd.api_updating
204 (p_batch_dpnt_id => p_batch_dpnt_id,
205 p_object_version_number => p_object_version_number);
206 --
207 if ((l_api_updating
208 and nvl(p_oipl_id,hr_api.g_number)
209 <> nvl(ben_bdi_shd.g_old_rec.oipl_id,hr_api.g_number)
210 or not l_api_updating) and
211 p_oipl_id is not null) then
212 --
213 -- check if oipl_id value exists in ben_oipl_f table
214 --
215 open c1;
216 --
217 fetch c1 into l_dummy;
218 if c1%notfound then
219 --
220 close c1;
221 --
222 -- raise error as FK does not relate to PK in ben_oipl_f
223 -- table.
224 --
225 ben_bdi_shd.constraint_error('BEN_BATCH_DPNT_INFO_DT4');
226 --
227 end if;
228 --
229 close c1;
230 --
231 end if;
232 --
233 hr_utility.set_location('Leaving:'||l_proc,10);
234 --
235 End chk_oipl_id;
236 --
237 -- ----------------------------------------------------------------------------
238 -- |--------------------------< chk_pl_id >-----------------------------------|
239 -- ----------------------------------------------------------------------------
240 --
241 -- Description
242 -- This procedure checks that a referenced foreign key actually exists
243 -- in the referenced table.
244 --
245 -- Pre-Conditions
246 -- None.
247 --
248 -- In Parameters
249 -- p_batch_dpnt_id PK
250 -- p_pl_id ID of FK column
251 -- p_effective_date Session Date of record
252 -- p_object_version_number object version number
253 --
254 -- Post Success
255 -- Processing continues
256 --
257 -- Post Failure
258 -- Error raised.
259 --
260 -- Access Status
261 -- Internal table handler use only.
262 --
263 Procedure chk_pl_id (p_batch_dpnt_id in number,
264 p_pl_id in number,
265 p_effective_date in date,
266 p_object_version_number in number) is
267 --
268 l_proc varchar2(72) := g_package||'chk_pl_id';
269 l_api_updating boolean;
270 l_dummy varchar2(1);
271 --
272 cursor c1 is
273 select null
274 from ben_pl_f a
275 where a.pl_id = p_pl_id
276 and p_effective_date
277 between a.effective_start_date
278 and a.effective_end_date;
279 --
280 Begin
281 --
282 hr_utility.set_location('Entering:'||l_proc,5);
283 --
284 l_api_updating := ben_bdi_shd.api_updating
285 (p_batch_dpnt_id => p_batch_dpnt_id,
286 p_object_version_number => p_object_version_number);
287 --
288 if ((l_api_updating
289 and nvl(p_pl_id,hr_api.g_number)
290 <> nvl(ben_bdi_shd.g_old_rec.pl_id,hr_api.g_number)
291 or not l_api_updating) and
292 p_pl_id is not null) then -- Bug 6407095
293 --
294 -- check if pl_id value exists in ben_pl_f table
295 --
296 open c1;
297 --
298 fetch c1 into l_dummy;
299 if c1%notfound then
300 --
301 close c1;
302 --
303 -- raise error as FK does not relate to PK in ben_pl_f
304 -- table.
305 --
306 ben_bdi_shd.constraint_error('BEN_BATCH_DPNT_INFO_DT3');
307 --
308 end if;
309 --
310 close c1;
311 --
312 end if;
313 --
314 hr_utility.set_location('Leaving:'||l_proc,10);
315 --
316 End chk_pl_id;
317 --
318 -- ----------------------------------------------------------------------------
319 -- |----------------------------< chk_pgm_id >--------------------------------|
320 -- ----------------------------------------------------------------------------
321 --
322 -- Description
323 -- This procedure checks that a referenced foreign key actually exists
324 -- in the referenced table.
325 --
326 -- Pre-Conditions
327 -- None.
328 --
329 -- In Parameters
330 -- p_batch_dpnt_id PK
331 -- p_pgm_id ID of FK column
332 -- p_effective_date Session Date of record
333 -- p_object_version_number object version number
334 --
335 -- Post Success
336 -- Processing continues
337 --
338 -- Post Failure
339 -- Error raised.
340 --
341 -- Access Status
342 -- Internal table handler use only.
343 --
344 Procedure chk_pgm_id (p_batch_dpnt_id in number,
345 p_pgm_id in number,
346 p_effective_date in date,
347 p_object_version_number in number) is
348 --
349 l_proc varchar2(72) := g_package||'chk_pgm_id';
350 l_api_updating boolean;
351 l_dummy varchar2(1);
352 --
353 cursor c1 is
354 select null
355 from ben_pgm_f a
356 where a.pgm_id = p_pgm_id
357 and p_effective_date
358 between a.effective_start_date
359 and a.effective_end_date;
360 --
361 Begin
362 --
363 hr_utility.set_location('Entering:'||l_proc,5);
364 --
365 l_api_updating := ben_bdi_shd.api_updating
369 if ((l_api_updating
366 (p_batch_dpnt_id => p_batch_dpnt_id,
367 p_object_version_number => p_object_version_number);
368 --
370 and nvl(p_pgm_id,hr_api.g_number)
371 <> nvl(ben_bdi_shd.g_old_rec.pgm_id,hr_api.g_number)
372 or not l_api_updating) and
373 p_pgm_id is not null) then
374 --
375 -- check if pgm_id value exists in ben_pgm_f table
376 --
377 open c1;
378 --
379 fetch c1 into l_dummy;
380 if c1%notfound then
381 --
382 close c1;
383 --
384 -- raise error as FK does not relate to PK in ben_pgm_f
385 -- table.
386 --
387 ben_bdi_shd.constraint_error('BEN_BATCH_DPNT_INFO_DT2');
388 --
389 end if;
390 --
391 close c1;
392 --
393 end if;
394 --
395 hr_utility.set_location('Leaving:'||l_proc,10);
396 --
397 End chk_pgm_id;
398 --
399 -- ----------------------------------------------------------------------------
400 -- |--------------------------< chk_benefit_action_id >-----------------------|
401 -- ----------------------------------------------------------------------------
402 --
403 -- Description
404 -- This procedure checks that a referenced foreign key actually exists
405 -- in the referenced table.
406 --
407 -- Pre-Conditions
408 -- None.
409 --
410 -- In Parameters
411 -- p_batch_dpnt_id PK
412 -- p_benefit_action_id ID of FK column
413 -- p_object_version_number object version number
414 --
415 -- Post Success
416 -- Processing continues
417 --
418 -- Post Failure
419 -- Error raised.
420 --
421 -- Access Status
422 -- Internal table handler use only.
423 --
424 Procedure chk_benefit_action_id (p_batch_dpnt_id in number,
425 p_benefit_action_id in number,
426 p_object_version_number in number) is
427 --
428 l_proc varchar2(72) := g_package||'chk_benefit_action_id';
429 l_api_updating boolean;
430 l_dummy varchar2(1);
431 --
432 cursor c1 is
433 select null
434 from ben_benefit_actions a
435 where a.benefit_action_id = p_benefit_action_id;
436 --
437 Begin
438 --
439 hr_utility.set_location('Entering:'||l_proc,5);
440 --
441 l_api_updating := ben_bdi_shd.api_updating
442 (p_batch_dpnt_id => p_batch_dpnt_id,
443 p_object_version_number => p_object_version_number);
444 --
445 if (l_api_updating
446 and nvl(p_benefit_action_id,hr_api.g_number)
447 <> nvl(ben_bdi_shd.g_old_rec.benefit_action_id,hr_api.g_number)
448 or not l_api_updating) then
449 --
450 -- check if benefit_action_id value exists in ben_benefit_actions table
451 --
452 open c1;
453 --
454 fetch c1 into l_dummy;
455 if c1%notfound then
456 --
457 close c1;
458 --
459 -- raise error as FK does not relate to PK in ben_benefit_actions
460 -- table.
461 --
462 ben_bdi_shd.constraint_error('BEN_BATCH_DPNT_INFO_FK1');
463 --
464 end if;
465 --
466 close c1;
467 --
468 end if;
469 --
470 hr_utility.set_location('Leaving:'||l_proc,10);
471 --
472 End chk_benefit_action_id;
473 --
474 -- ----------------------------------------------------------------------------
475 -- |---------------------------< chk_contact_typ_cd >-------------------------|
476 -- ----------------------------------------------------------------------------
477 --
478 -- Description
479 -- This procedure is used to check that the lookup value is valid.
480 --
481 -- Pre Conditions
482 -- None.
483 --
484 -- In Parameters
485 -- batch_dpnt_id PK of record being inserted or updated.
486 -- contact_typ_cd Value of lookup code.
487 -- effective_date effective date
488 -- object_version_number Object version number of record being
489 -- inserted or updated.
490 --
491 -- Post Success
492 -- Processing continues
493 --
494 -- Post Failure
495 -- Error handled by procedure
496 --
497 -- Access Status
498 -- Internal table handler use only.
499 --
500 Procedure chk_contact_typ_cd(p_batch_dpnt_id in number,
501 p_contact_typ_cd in varchar2,
502 p_effective_date in date,
503 p_object_version_number in number) is
504 --
505 l_proc varchar2(72) := g_package||'chk_contact_typ_cd';
506 l_api_updating boolean;
507 --
508 Begin
509 --
510 hr_utility.set_location('Entering:'||l_proc, 5);
511 --
512 l_api_updating := ben_bdi_shd.api_updating
513 (p_batch_dpnt_id => p_batch_dpnt_id,
514 p_object_version_number => p_object_version_number);
515 --
516 if (l_api_updating
517 and p_contact_typ_cd
518 <> nvl(ben_bdi_shd.g_old_rec.contact_typ_cd,hr_api.g_varchar2)
519 or not l_api_updating)
520 and p_contact_typ_cd is not null then
521 --
522 -- check if value of lookup falls within lookup type.
523 --
524 if hr_api.not_exists_in_hr_lookups
525 (p_lookup_type => 'CONTACT',
526 p_lookup_code => p_contact_typ_cd,
527 p_effective_date => p_effective_date) then
528 --
529 -- raise error as does not exist as lookup
530 --
534 fnd_message.raise_error;
531 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
532 fnd_message.set_token('FIELD','p_contact_typ_cd');
533 fnd_message.set_token('TYPE','CONTACT');
535 --
536 end if;
537 --
538 end if;
539 --
540 hr_utility.set_location('Leaving:'||l_proc,10);
541 --
542 end chk_contact_typ_cd;
543 --
544 -- ----------------------------------------------------------------------------
545 -- |---------------------------< insert_validate >----------------------------|
546 -- ----------------------------------------------------------------------------
547 Procedure insert_validate(p_rec in ben_bdi_shd.g_rec_type
548 ,p_effective_date in date) is
549 --
550 l_proc varchar2(72) := g_package||'insert_validate';
551 --
552 Begin
553 hr_utility.set_location('Entering:'||l_proc, 5);
554 --
555 -- Call all supporting business operations
556 --
557 --
558 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
559 --
560 chk_batch_dpnt_id
561 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
562 p_object_version_number => p_rec.object_version_number);
563 --
564 chk_person_id
565 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
566 p_person_id => p_rec.person_id,
567 p_effective_date => p_effective_date,
568 p_object_version_number => p_rec.object_version_number);
569 --
570 chk_pgm_id
571 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
572 p_pgm_id => p_rec.pgm_id,
573 p_effective_date => p_effective_date,
574 p_object_version_number => p_rec.object_version_number);
575 --
576 chk_pl_id
577 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
578 p_pl_id => p_rec.pl_id,
579 p_effective_date => p_effective_date,
580 p_object_version_number => p_rec.object_version_number);
581 --
582 chk_oipl_id
583 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
584 p_oipl_id => p_rec.oipl_id,
585 p_effective_date => p_effective_date,
586 p_object_version_number => p_rec.object_version_number);
587 --
588 chk_person_id
589 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
590 p_person_id => p_rec.dpnt_person_id,
591 p_effective_date => p_effective_date,
592 p_object_version_number => p_rec.object_version_number);
593 --
594 chk_benefit_action_id
595 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
596 p_benefit_action_id => p_rec.benefit_action_id,
597 p_object_version_number => p_rec.object_version_number);
598 --
599 chk_contact_typ_cd
600 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
601 p_contact_typ_cd => p_rec.contact_typ_cd,
602 p_effective_date => p_effective_date,
603 p_object_version_number => p_rec.object_version_number);
604 --
605 hr_utility.set_location(' Leaving:'||l_proc, 10);
606 End insert_validate;
607 --
608 -- ----------------------------------------------------------------------------
609 -- |---------------------------< update_validate >----------------------------|
610 -- ----------------------------------------------------------------------------
611 Procedure update_validate(p_rec in ben_bdi_shd.g_rec_type
612 ,p_effective_date in date) is
613 --
614 l_proc varchar2(72) := g_package||'update_validate';
615 --
616 Begin
617 hr_utility.set_location('Entering:'||l_proc, 5);
618 --
619 -- Call all supporting business operations
620 --
621 --
622 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
623 --
624 chk_batch_dpnt_id
625 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
626 p_object_version_number => p_rec.object_version_number);
627 --
628 chk_person_id
629 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
630 p_person_id => p_rec.person_id,
631 p_effective_date => p_effective_date,
632 p_object_version_number => p_rec.object_version_number);
633 --
634 chk_pgm_id
635 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
636 p_pgm_id => p_rec.pgm_id,
637 p_effective_date => p_effective_date,
638 p_object_version_number => p_rec.object_version_number);
639 --
640 chk_pl_id
641 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
642 p_pl_id => p_rec.pl_id,
643 p_effective_date => p_effective_date,
644 p_object_version_number => p_rec.object_version_number);
645 --
646 chk_oipl_id
647 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
648 p_oipl_id => p_rec.oipl_id,
649 p_effective_date => p_effective_date,
650 p_object_version_number => p_rec.object_version_number);
651 --
652 chk_person_id
653 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
654 p_person_id => p_rec.dpnt_person_id,
655 p_effective_date => p_effective_date,
656 p_object_version_number => p_rec.object_version_number);
657 --
658 chk_benefit_action_id
659 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
660 p_benefit_action_id => p_rec.benefit_action_id,
661 p_object_version_number => p_rec.object_version_number);
662 --
663 chk_contact_typ_cd
664 (p_batch_dpnt_id => p_rec.batch_dpnt_id,
665 p_contact_typ_cd => p_rec.contact_typ_cd,
666 p_effective_date => p_effective_date,
667 p_object_version_number => p_rec.object_version_number);
668 --
669 hr_utility.set_location(' Leaving:'||l_proc, 10);
670 End update_validate;
671 --
672 -- ----------------------------------------------------------------------------
673 -- |---------------------------< delete_validate >----------------------------|
674 -- ----------------------------------------------------------------------------
675 Procedure delete_validate(p_rec in ben_bdi_shd.g_rec_type
676 ,p_effective_date in date) is
677 --
678 l_proc varchar2(72) := g_package||'delete_validate';
679 --
680 Begin
681 hr_utility.set_location('Entering:'||l_proc, 5);
682 --
683 -- Call all supporting business operations
684 --
685 hr_utility.set_location(' Leaving:'||l_proc, 10);
686 End delete_validate;
687 --
688 --
689 -- ---------------------------------------------------------------------------
690 -- |---------------------< return_legislation_code >-------------------------|
691 -- ---------------------------------------------------------------------------
692 --
693 function return_legislation_code
694 (p_batch_dpnt_id in number) return varchar2 is
695 --
696 -- Declare cursor
697 --
698 cursor csr_leg_code is
699 select a.legislation_code
700 from per_business_groups a,
701 ben_batch_dpnt_info b
702 where b.batch_dpnt_id = p_batch_dpnt_id
703 and a.business_group_id = b.business_group_id;
704 --
705 -- Declare local variables
706 --
707 l_legislation_code varchar2(150);
708 l_proc varchar2(72) := g_package||'return_legislation_code';
709 --
710 begin
711 --
712 hr_utility.set_location('Entering:'|| l_proc, 10);
713 --
714 -- Ensure that all the mandatory parameter are not null
715 --
716 hr_api.mandatory_arg_error(p_api_name => l_proc,
717 p_argument => 'batch_dpnt_id',
718 p_argument_value => p_batch_dpnt_id);
719 --
720 open csr_leg_code;
721 --
722 fetch csr_leg_code into l_legislation_code;
723 --
724 if csr_leg_code%notfound then
725 --
726 close csr_leg_code;
727 --
728 -- The primary key is invalid therefore we must error
729 --
730 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
731 hr_utility.raise_error;
732 --
733 end if;
734 --
735 close csr_leg_code;
736 --
737 hr_utility.set_location(' Leaving:'|| l_proc, 20);
738 --
739 return l_legislation_code;
740 --
741 end return_legislation_code;
742 --
743 end ben_bdi_bus;