[Home] [Help]
PACKAGE BODY: APPS.BEN_PIL_BUS
Source
1 Package Body ben_pil_bus as
2 /* $Header: bepilrhi.pkb 120.4 2011/09/02 10:55:27 pvelvano ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pil_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_per_in_ler_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 -- per_in_ler_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_per_in_ler_id(p_per_in_ler_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_per_in_ler_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_pil_shd.api_updating
47 (p_per_in_ler_id => p_per_in_ler_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_per_in_ler_id,hr_api.g_number)
52 <> ben_pil_shd.g_old_rec.per_in_ler_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_pil_shd.constraint_error('BEN_PER_IN_LER_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_per_in_ler_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_pil_shd.constraint_error('BEN_PER_IN_LER_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_per_in_ler_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_ler_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_per_in_ler_id PK
89 -- p_ler_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_ler_id (p_per_in_ler_id in number,
103 p_ler_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_ler_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from ben_ler_f a
114 where a.ler_id = p_ler_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_pil_shd.api_updating
124 (p_per_in_ler_id => p_per_in_ler_id,
125 p_object_version_number => p_object_version_number);
126 --
127 if (l_api_updating
128 and nvl(p_ler_id,hr_api.g_number)
129 <> nvl(ben_pil_shd.g_old_rec.ler_id,hr_api.g_number)
130 or not l_api_updating) then
131 --
132 -- check if ler_id value exists in ben_ler_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_ler_f
142 -- table.
143 --
144 ben_pil_shd.constraint_error('BEN_PER_IN_LER_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_ler_id;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------< chk_person_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_per_in_ler_id PK
169 -- p_person_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_person_id (p_per_in_ler_id in number,
183 p_person_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_person_id';
188 l_api_updating boolean;
189 l_dummy varchar2(1);
190 --
191 cursor c1 is
192 select null
193 from per_all_people_f a
194 where a.person_id = p_person_id
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_pil_shd.api_updating
204 (p_per_in_ler_id => p_per_in_ler_id,
205 p_object_version_number => p_object_version_number);
206 --
207 if (l_api_updating
208 and nvl(p_person_id,hr_api.g_number)
209 <> nvl(ben_pil_shd.g_old_rec.person_id,hr_api.g_number)
210 or not l_api_updating) then
211 --
212 -- check if person_id value exists in per_all_people_f table
213 --
214 open c1;
215 --
216 fetch c1 into l_dummy;
217 if c1%notfound then
218 --
219 close c1;
220 --
221 -- raise error as FK does not relate to PK in per_all_people_f
222 -- table.
223 --
224 ben_pil_shd.constraint_error('BEN_PER_IN_LER_DT2');
225 --
226 end if;
227 --
228 close c1;
229 --
230 end if;
231 --
232 hr_utility.set_location('Leaving:'||l_proc,10);
233 --
234 End chk_person_id;
235 --
236 --
237 -- ----------------------------------------------------------------------------
238 -- |------< chk_prvs_stat_cd >------|
239 -- ----------------------------------------------------------------------------
240 --
241 -- Description
242 -- This procedure is used to check that the lookup value is valid.
243 --
244 -- Pre Conditions
245 -- None.
246 --
247 -- In Parameters
248 -- per_in_ler_id PK of record being inserted or updated.
249 -- prvs_stat_cd Value of lookup code.
250 -- effective_date effective date
251 -- object_version_number Object version number of record being
252 -- inserted or updated.
253 --
254 -- Post Success
255 -- Processing continues
256 --
257 -- Post Failure
258 -- Error handled by procedure
259 --
260 -- Access Status
261 -- Internal table handler use only.
262 --
263 Procedure chk_prvs_stat_cd(p_per_in_ler_id in number,
264 p_prvs_stat_cd in varchar2,
265 p_effective_date in date,
266 p_object_version_number in number) is
267 --
268 l_proc varchar2(72) := g_package||'chk_prvs_stat_cd';
269 l_api_updating boolean;
270 --
271 Begin
272 --
273 hr_utility.set_location('Entering:'||l_proc, 5);
274 --
275 l_api_updating := ben_pil_shd.api_updating
276 (p_per_in_ler_id => p_per_in_ler_id,
277 p_object_version_number => p_object_version_number);
278 --
279 if (l_api_updating
280 and p_prvs_stat_cd
281 <> nvl(ben_pil_shd.g_old_rec.prvs_stat_cd,hr_api.g_varchar2)
282 or not l_api_updating)
283 and p_prvs_stat_cd is not null then
284 --
285 -- check if value of lookup falls within lookup type.
286 --
287 if hr_api.not_exists_in_hr_lookups
288 (p_lookup_type => 'BEN_PER_IN_LER_STAT',
289 p_lookup_code => p_prvs_stat_cd,
290 p_effective_date => p_effective_date) then
291 --
292 -- raise error as does not exist as lookup
293 --
294 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
295 fnd_message.set_token('FIELD','p_prvs_stat_cd');
296 fnd_message.set_token('TYPE','BEN_PER_IN_LER_STAT_CD');
297 fnd_message.raise_error;
298 --
299 end if;
300 --
301 end if;
302 --
303 hr_utility.set_location('Leaving:'||l_proc,10);
304 --
305 end chk_prvs_stat_cd;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |------< chk_per_in_ler_stat_cd >------|
309 -- ----------------------------------------------------------------------------
310 --
311 -- Description
312 -- This procedure is used to check that the lookup value is valid.
313 --
314 -- Pre Conditions
315 -- None.
316 --
317 -- In Parameters
318 -- per_in_ler_id PK of record being inserted or updated.
319 -- per_in_ler_stat_cd Value of lookup code.
320 -- effective_date effective date
321 -- object_version_number Object version number of record being
322 -- inserted or updated.
323 --
324 -- Post Success
325 -- Processing continues
326 --
327 -- Post Failure
328 -- Error handled by procedure
329 --
330 -- Access Status
331 -- Internal table handler use only.
332 --
333 Procedure chk_per_in_ler_stat_cd(p_per_in_ler_id in number,
334 p_per_in_ler_stat_cd in varchar2,
335 p_effective_date in date,
336 p_object_version_number in number) is
337 --
338 l_proc varchar2(72) := g_package||'chk_per_in_ler_stat_cd';
339 l_api_updating boolean;
340 --
341 Begin
342 --
343 hr_utility.set_location('Entering:'||l_proc, 5);
344 --
345 l_api_updating := ben_pil_shd.api_updating
346 (p_per_in_ler_id => p_per_in_ler_id,
347 p_object_version_number => p_object_version_number);
348 --
349 if (l_api_updating
350 and p_per_in_ler_stat_cd
351 <> nvl(ben_pil_shd.g_old_rec.per_in_ler_stat_cd,hr_api.g_varchar2)
352 or not l_api_updating)
353 and p_per_in_ler_stat_cd is not null then
354 --
355 -- check if value of lookup falls within lookup type.
356 --
357 if hr_api.not_exists_in_hr_lookups
358 (p_lookup_type => 'BEN_PER_IN_LER_STAT',
359 p_lookup_code => p_per_in_ler_stat_cd,
360 p_effective_date => p_effective_date) then
361 --
362 -- raise error as does not exist as lookup
363 --
364 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
365 fnd_message.set_token('FIELD','p_per_in_ler_stat_cd');
366 fnd_message.set_token('TYPE','BEN_PER_IN_LER_STAT_CD');
367 fnd_message.raise_error;
368 --
369 end if;
370 --
371 end if;
372 --
373 hr_utility.set_location('Leaving:'||l_proc,10);
374 --
375 end chk_per_in_ler_stat_cd;
376 --
377 -- ----------------------------------------------------------------------------
378 -- |------< chk_bckt_per_in_ler_id >------|
379 -- ----------------------------------------------------------------------------
380 --
381 -- Description
382 -- This procedure checks that a referenced foreign key actually exists
383 -- in the referenced table.
384 --
385 -- Pre-Conditions
386 -- None.
387 --
388 -- In Parameters
389 -- p_per_in_ler_id PK
390 -- p_bckt_per_in_ler_id ID of FK column
391 -- p_effective_date Session Date of record
392 -- p_object_version_number object version number
393 --
394 -- Post Success
395 -- Processing continues
396 --
397 -- Post Failure
398 -- Error raised.
399 --
400 -- Access Status
401 -- Internal table handler use only.
402 --
403 Procedure chk_bckt_per_in_ler_id (p_per_in_ler_id in number,
404 p_bckt_per_in_ler_id in number,
405 p_effective_date in date,
406 p_object_version_number in number) is
407 --
408 l_proc varchar2(72) := g_package||'chk_bckt_per_in_ler_id';
409 l_api_updating boolean;
410 l_dummy varchar2(1);
411 --
412 cursor c1 is
413 select null
414 from ben_per_in_ler a
415 where a.per_in_ler_id = p_bckt_per_in_ler_id;
416 --
417 Begin
418 --
419 hr_utility.set_location('Entering:'||l_proc,5);
420 --
421 l_api_updating := ben_pil_shd.api_updating
422 (p_per_in_ler_id => p_per_in_ler_id,
423 p_object_version_number => p_object_version_number);
424 --
425 if (l_api_updating
426 and nvl(p_bckt_per_in_ler_id,hr_api.g_number)
427 <> nvl(ben_pil_shd.g_old_rec.bckt_per_in_ler_id,hr_api.g_number)
428 or not l_api_updating)
429 and p_bckt_per_in_ler_id is not null
430 then
431 --
432 -- check if bckt_per_in_ler_id value exists in ben_per_in_ler table
433 --
434 open c1;
435 --
436 fetch c1 into l_dummy;
437 if c1%notfound then
438 --
439 close c1;
440 --
441 -- raise error as FK does not relate to PK in ben_per_in_ler
442 -- table.
443 --
444 ben_pil_shd.constraint_error('BEN_PER_IN_LER_FK2');
445 --
446 end if;
447 --
448 close c1;
449 --
450 end if;
451 --
452 hr_utility.set_location('Leaving:'||l_proc,10);
453 --
454 End chk_bckt_per_in_ler_id;
455 --
456 -- ----------------------------------------------------------------------------
457 -- |------------------------------< chk_ws_mgr_id >---------------------------|
458 -- ----------------------------------------------------------------------------
459 -- Validates ws_mgr_id. Any person B, below in the hierarchy to person A,
460 -- cannot be re-assigned as the manager to A.
461 --
462 procedure chk_ws_mgr_id(
463 p_per_in_ler_id number,
464 p_ws_mgr_id number,
465 p_effective_date date) is
466
467 cursor c1 is
468 select per1.full_name person1,
469 per2.full_name person2
470 from ben_cwb_group_hrchy cwb1,
471 ben_per_in_ler pil1,
472 per_all_people_f per1,
473 per_all_people_f per2
474 where cwb1.mgr_per_in_ler_id = p_per_in_ler_id
475 and cwb1.lvl_num > 0
476 and pil1.per_in_ler_id = cwb1.mgr_per_in_ler_id
477 and per1.person_id = pil1.person_id
478 and trunc(p_effective_date) between per1.effective_start_date
479 and per1.effective_end_date
480 and per2.person_id = p_ws_mgr_id
481 and trunc(p_effective_date) between per2.effective_start_date
482 and per2.effective_end_date
483 and exists
484 ( select 'x'
485 from ben_per_in_ler pil2
486 where pil2.person_id = p_ws_mgr_id
487 and pil2.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
488 and pil2.ler_id = pil1.ler_id
489 and pil2.per_in_ler_id = cwb1.emp_per_in_ler_id);
490
491 l_person1 per_all_people_f.full_name%type;
492 l_person2 per_all_people_f.full_name%type;
493 l_proc varchar2(72) := g_package||'chk_ws_mgr_id';
494
495 begin
496
497 hr_utility.set_location(' Entering:'||l_proc, 10);
498
499 if (p_ws_mgr_id
500 <> nvl(ben_pil_shd.g_old_rec.ws_mgr_id,hr_api.g_number)) then
501
502 open c1;
503 fetch c1 into l_person1,l_person2;
504 if c1%found then
505 close c1;
506 fnd_message.set_name('BEN','BEN_93251_CWB_CANNOT_REASSIGN');
507 fnd_message.set_token('PERSON1', l_person1);
508 fnd_message.set_token('PERSON2', l_person2);
509 fnd_message.raise_error;
510 end if;
511 close c1;
512
513 end if;
514
515 hr_utility.set_location(' Leaving:'||l_proc, 20);
516 end chk_ws_mgr_id;
517 -- ----------------------------------------------------------------------------
518 -- |---------------------------< insert_validate >----------------------------|
519 -- ----------------------------------------------------------------------------
520 Procedure insert_validate(p_rec in ben_pil_shd.g_rec_type
521 ,p_effective_date in date) is
522 --
523 l_proc varchar2(72) := g_package||'insert_validate';
524 --
525 Begin
526 hr_utility.set_location('Entering:'||l_proc, 5);
527 --
528 -- Call all supporting business operations
529 --
530 --
531 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
532 --
533 chk_per_in_ler_id
534 (p_per_in_ler_id => p_rec.per_in_ler_id,
535 p_object_version_number => p_rec.object_version_number);
536 --
537 chk_per_in_ler_stat_cd
538 (p_per_in_ler_id => p_rec.per_in_ler_id,
539 p_per_in_ler_stat_cd => p_rec.per_in_ler_stat_cd,
540 p_effective_date => p_effective_date,
541 p_object_version_number => p_rec.object_version_number);
542 --
543 chk_prvs_stat_cd
544 (p_per_in_ler_id => p_rec.per_in_ler_id,
545 p_prvs_stat_cd => p_rec.prvs_stat_cd,
546 p_effective_date => p_effective_date,
547 p_object_version_number => p_rec.object_version_number);
548 --
549 --
550 chk_bckt_per_in_ler_id
551 (p_per_in_ler_id => p_rec.per_in_ler_id,
552 p_bckt_per_in_ler_id => p_rec.bckt_per_in_ler_id,
553 p_effective_date => p_effective_date,
554 p_object_version_number => p_rec.object_version_number);
555 --
556 hr_utility.set_location(' Leaving:'||l_proc, 10);
557 End insert_validate;
558 --
559 -- ----------------------------------------------------------------------------
560 -- |---------------------------< update_validate >----------------------------|
561 -- ----------------------------------------------------------------------------
562 Procedure update_validate(p_rec in ben_pil_shd.g_rec_type
563 ,p_effective_date in date) is
564 --
565 l_proc varchar2(72) := g_package||'update_validate';
566 --
567 Begin
568 hr_utility.set_location('Entering:'||l_proc, 5);
569 --
570 -- Call all supporting business operations
571 --
572 --
573 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
574 --
575 chk_per_in_ler_id
576 (p_per_in_ler_id => p_rec.per_in_ler_id,
577 p_object_version_number => p_rec.object_version_number);
578 --
579 chk_per_in_ler_stat_cd
580 (p_per_in_ler_id => p_rec.per_in_ler_id,
581 p_per_in_ler_stat_cd => p_rec.per_in_ler_stat_cd,
582 p_effective_date => p_effective_date,
583 p_object_version_number => p_rec.object_version_number);
584 --
585 chk_prvs_stat_cd
586 (p_per_in_ler_id => p_rec.per_in_ler_id,
587 p_prvs_stat_cd => p_rec.prvs_stat_cd,
588 p_effective_date => p_effective_date,
589 p_object_version_number => p_rec.object_version_number);
590 --
591 chk_bckt_per_in_ler_id
592 (p_per_in_ler_id => p_rec.per_in_ler_id,
593 p_bckt_per_in_ler_id => p_rec.bckt_per_in_ler_id,
594 p_effective_date => p_effective_date,
595 p_object_version_number => p_rec.object_version_number);
596 --
597 chk_ws_mgr_id
598 (p_per_in_ler_id => p_rec.per_in_ler_id,
599 p_ws_mgr_id => p_rec.ws_mgr_id ,
600 p_effective_date => p_effective_date
601 );
602 hr_utility.set_location(' Leaving:'||l_proc, 10);
603 End update_validate;
604 --
605 -- ----------------------------------------------------------------------------
606 -- |---------------------------< delete_validate >----------------------------|
607 -- ----------------------------------------------------------------------------
608 Procedure delete_validate(p_rec in ben_pil_shd.g_rec_type
609 ,p_effective_date in date) is
610 --
611 l_proc varchar2(72) := g_package||'delete_validate';
612 --
613 Begin
614 hr_utility.set_location('Entering:'||l_proc, 5);
615 --
616 -- Call all supporting business operations
617 --
618 hr_utility.set_location(' Leaving:'||l_proc, 10);
619 End delete_validate;
620 --
621 --
622 -- ---------------------------------------------------------------------------
623 -- |---------------------< return_legislation_code >-------------------------|
624 -- ---------------------------------------------------------------------------
625 --
626 function return_legislation_code
627 (p_per_in_ler_id in number) return varchar2 is
628 --
629 -- Declare cursor
630 --
631 cursor csr_leg_code is
632 select a.legislation_code
633 from per_business_groups a,
634 ben_per_in_ler b
635 where b.per_in_ler_id = p_per_in_ler_id
636 and a.business_group_id = b.business_group_id;
637 --
638 -- Declare local variables
639 --
640 l_legislation_code varchar2(150);
641 l_proc varchar2(72) := g_package||'return_legislation_code';
642 --
643 begin
644 --
645 hr_utility.set_location('Entering:'|| l_proc, 10);
646 --
647 -- Ensure that all the mandatory parameter are not null
648 --
649 hr_api.mandatory_arg_error(p_api_name => l_proc,
650 p_argument => 'per_in_ler_id',
651 p_argument_value => p_per_in_ler_id);
652 --
653 open csr_leg_code;
654 --
655 fetch csr_leg_code into l_legislation_code;
656 --
657 if csr_leg_code%notfound then
658 --
659 close csr_leg_code;
660 --
661 -- The primary key is invalid therefore we must error
662 --
663 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
664 hr_utility.raise_error;
665 --
666 end if;
667 --
668 close csr_leg_code;
669 --
670 hr_utility.set_location(' Leaving:'|| l_proc, 20);
671 --
672 return l_legislation_code;
673 --
674 end return_legislation_code;
675 --
676 end ben_pil_bus;