1 Package Body ben_cpy_bus as
2 /* $Header: becpyrhi.pkb 120.2 2005/12/19 12:34:35 kmahendr noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cpy_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-------------------------< chk_popl_yr_perd_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 -- popl_yr_perd_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_popl_yr_perd_id(p_popl_yr_perd_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_popl_yr_perd_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_cpy_shd.api_updating
47 (p_popl_yr_perd_id => p_popl_yr_perd_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_popl_yr_perd_id,hr_api.g_number)
52 <> ben_cpy_shd.g_old_rec.popl_yr_perd_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_cpy_shd.constraint_error('BEN_POPL_YR_PERD_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_popl_yr_perd_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_cpy_shd.constraint_error('BEN_POPL_YR_PERD_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_popl_yr_perd_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |-------------------------< chk_pl_pgm_yr_perd_id >------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure is used to check that the program or plan year period is
82 -- not overlapping another program or plan year period for the same program
83 -- or plan.
84 --
85 -- Pre Conditions
86 -- None.
87 --
88 -- In Parameters
89 -- popl_yr_perd_id PK of record being inserted or updated.
90 -- pgm_id Id of program.
91 -- pl_id Id of plan.
92 -- yr_perd_id Id of year period.
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Errors handled by the procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_pgm_pl_yr_perd_id(p_popl_yr_perd_id in number,
106 p_pgm_id in number,
107 p_pl_id in number,
108 p_yr_perd_id in number,
109 p_business_group_id in number,
110 p_object_version_number in number) is
111 --
112 l_proc varchar2(72) := g_package||'chk_pgm_pl_yr_perd_id';
113 l_api_updating boolean;
114 l_dummy varchar2(1);
115 --
116 cursor c_plan is
117 select null
118 from ben_yr_perd yrp
119 where yrp.yr_perd_id = p_yr_perd_id
120 and yrp.business_group_id = p_business_group_id
121 and exists (select null
122 from ben_popl_yr_perd a,
123 ben_yr_perd b
124 where a.popl_yr_perd_id <> nvl(p_popl_yr_perd_id,-1)
125 and a.pl_id = p_pl_id
126 and a.business_group_id = p_business_group_id
127 and a.yr_perd_id = b.yr_perd_id
128 and (yrp.start_date
129 between b.start_date
130 and b.end_date
131 or
132 yrp.end_date
133 between b.start_date
134 and b.end_date));
135 --
136 cursor c_prog is
137 select null
138 from ben_yr_perd yrp
139 where yrp.yr_perd_id = p_yr_perd_id
140 and yrp.business_group_id = p_business_group_id
141 and exists (select null
142 from ben_popl_yr_perd a,
143 ben_yr_perd b
144 where a.popl_yr_perd_id <> nvl(p_popl_yr_perd_id,-1)
145 and a.pgm_id = p_pgm_id
146 and a.business_group_id = p_business_group_id
147 and a.yr_perd_id = b.yr_perd_id
148 and (yrp.start_date
149 between b.start_date
150 and b.end_date
151 or
152 yrp.end_date
153 between b.start_date
154 and b.end_date));
155 --
156 cursor c_duplicate is
157 select null
158 from ben_popl_yr_perd cpy
159 where (cpy.pgm_id = nvl(p_pgm_id,-1) or
160 cpy.pl_id = nvl(p_pl_id,-1))
161 and cpy.yr_perd_id = p_yr_perd_id
162 and cpy.popl_yr_perd_id <> p_popl_yr_perd_id
163 and cpy.business_group_id+0 = p_business_group_id;
164 --
165 Begin
166 --
167 hr_utility.set_location('Entering:'||l_proc, 5);
168 --
169 l_api_updating := ben_cpy_shd.api_updating
170 (p_popl_yr_perd_id => p_popl_yr_perd_id,
171 p_object_version_number => p_object_version_number);
172 --
173 if (l_api_updating
174 and (nvl(p_popl_yr_perd_id,hr_api.g_number)
175 <> ben_cpy_shd.g_old_rec.popl_yr_perd_id
176 or nvl(p_pgm_id,hr_api.g_number)
177 <> ben_cpy_shd.g_old_rec.pgm_id
178 or nvl(p_pl_id,hr_api.g_number)
179 <> ben_cpy_shd.g_old_rec.pl_id
180 or nvl(p_yr_perd_id,hr_api.g_number)
181 <> ben_cpy_shd.g_old_rec.yr_perd_id)
182 or not l_api_updating) then
183 --
184 -- check if an overlap occurs
185 --
186 if p_pl_id is not null then
187 --
188 open c_plan;
189 --
190 fetch c_plan into l_dummy;
191 if c_plan%found then
192 --
193 close c_plan;
194 fnd_message.set_name('BEN','BEN_91719_CPY_PYR_PERD_OVERLAP');
195 fnd_message.raise_error;
196 --
197 end if;
198 --
199 close c_plan;
200 --
201 else
202 --
203 open c_prog;
204 --
205 fetch c_prog into l_dummy;
206 if c_prog%found then
207 --
208 close c_prog;
209 fnd_message.set_name('BEN','BEN_91719_CPY_PYR_PERD_OVERLAP');
210 fnd_message.raise_error;
211 --
212 end if;
213 --
214 close c_prog;
215 --
216 end if;
217 --
218 open c_duplicate;
219 --
220 fetch c_duplicate into l_dummy;
221 if c_duplicate%found then
222 --
223 close c_duplicate;
224 fnd_message.set_name('BEN','BEN_91719_CPY_PYR_PERD_OVERLAP');
225 fnd_message.raise_error;
226 --
227 end if;
228 --
229 close c_duplicate;
230 --
231 end if;
232 --
233 hr_utility.set_location('Leaving:'||l_proc, 10);
234 --
235 End chk_pgm_pl_yr_perd_id;
236 -- ----------------------------------------------------------------------------
237 -- |----------------------------< chk_pl_id >---------------------------------|
238 -- ----------------------------------------------------------------------------
239 --
240 -- Description
241 -- This procedure checks that a referenced foreign key actually exists
242 -- in the referenced table.
243 --
244 -- Pre-Conditions
245 -- None.
246 --
247 -- In Parameters
248 -- p_popl_yr_perd_id PK
249 -- p_pl_id ID of FK column
250 -- p_effective_date Session Date of record
251 -- p_object_version_number object version number
252 --
253 -- Post Success
254 -- Processing continues
255 --
256 -- Post Failure
257 -- Error raised.
258 --
259 -- Access Status
260 -- Internal table handler use only.
261 --
262 Procedure chk_pl_id (p_popl_yr_perd_id in number,
263 p_pl_id in number,
264 p_effective_date in date,
265 p_object_version_number in number) is
266 --
267 l_proc varchar2(72) := g_package||'chk_pl_id';
268 l_api_updating boolean;
269 l_dummy varchar2(1);
270 --
271 cursor c1 is
272 select null
273 from ben_pl_f a
274 where a.pl_id = p_pl_id
275 and p_effective_date
276 between a.effective_start_date
277 and a.effective_end_date;
278 --
279 Begin
280 --
281 hr_utility.set_location('Entering:'||l_proc,5);
282 --
283 l_api_updating := ben_cpy_shd.api_updating
284 (p_popl_yr_perd_id => p_popl_yr_perd_id,
285 p_object_version_number => p_object_version_number);
286 --
287 if (l_api_updating
288 and nvl(p_pl_id,hr_api.g_number)
289 <> nvl(ben_cpy_shd.g_old_rec.pl_id,hr_api.g_number)
290 or not l_api_updating) then
291 --
292 -- check if pl_id value exists in ben_pl_f table
293 --
294 open c1;
295 --
296 fetch c1 into l_dummy;
297 if c1%notfound then
298 --
299 close c1;
300 --
301 -- raise error as FK does not relate to PK in ben_pl_f
302 -- table.
303 --
304 ben_cpy_shd.constraint_error('DATETRACK-ERROR');
305 --
306 end if;
307 --
308 close c1;
309 --
310 end if;
311 --
312 hr_utility.set_location('Leaving:'||l_proc,10);
313 --
314 End chk_pl_id;
315 --
316 -- ----------------------------------------------------------------------------
317 -- |------------------------------< chk_pgm_id >------------------------------|
318 -- ----------------------------------------------------------------------------
319 --
320 -- Description
321 -- This procedure checks that a referenced foreign key actually exists
322 -- in the referenced table.
323 --
324 -- Pre-Conditions
325 -- None.
326 --
327 -- In Parameters
328 -- p_popl_yr_perd_id PK
329 -- p_pgm_id ID of FK column
330 -- p_effective_date Session Date of record
331 -- p_object_version_number object version number
332 --
333 -- Post Success
334 -- Processing continues
335 --
336 -- Post Failure
337 -- Error raised.
338 --
339 -- Access Status
340 -- Internal table handler use only.
341 --
342 Procedure chk_pgm_id (p_popl_yr_perd_id in number,
343 p_pgm_id in number,
344 p_effective_date in date,
345 p_object_version_number in number) is
346 --
347 l_proc varchar2(72) := g_package||'chk_pgm_id';
348 l_api_updating boolean;
349 l_dummy varchar2(1);
350 --
351 cursor c1 is
352 select null
353 from ben_pgm_f a
354 where a.pgm_id = p_pgm_id
355 and p_effective_date
356 between a.effective_start_date
357 and a.effective_end_date;
358 --
359 Begin
360 --
361 hr_utility.set_location('Entering:'||l_proc,5);
362 --
363 l_api_updating := ben_cpy_shd.api_updating
364 (p_popl_yr_perd_id => p_popl_yr_perd_id,
365 p_object_version_number => p_object_version_number);
366 --
367 if (l_api_updating
368 and nvl(p_pgm_id,hr_api.g_number)
369 <> nvl(ben_cpy_shd.g_old_rec.pgm_id,hr_api.g_number)
370 or not l_api_updating) then
371 --
372 -- check if pgm_id value exists in ben_pgm_f table
373 --
374 open c1;
375 --
376 fetch c1 into l_dummy;
377 if c1%notfound then
378 --
379 close c1;
380 --
381 -- raise error as FK does not relate to PK in ben_pgm_f
382 -- table.
383 --
384 ben_cpy_shd.constraint_error('DATETRACK-ERROR');
385 --
386 end if;
387 --
388 close c1;
389 --
390 end if;
391 --
392 hr_utility.set_location('Leaving:'||l_proc,10);
393 --
394 End chk_pgm_id;
395 --
396 -- ----------------------------------------------------------------------------
397 -- |----------------------------< chk_yr_perd_id >----------------------------|
398 -- ----------------------------------------------------------------------------
399 --
400 -- Description
401 -- This procedure checks that a referenced foreign key actually exists
402 -- in the referenced table.
403 --
404 -- Pre-Conditions
405 -- None.
406 --
407 -- In Parameters
408 -- p_popl_yr_perd_id PK
409 -- p_yr_perd_id ID of FK column
410 -- p_object_version_number object version number
411 --
412 -- Post Success
413 -- Processing continues
414 --
415 -- Post Failure
416 -- Error raised.
417 --
418 -- Access Status
419 -- Internal table handler use only.
420 --
421 Procedure chk_yr_perd_id (p_popl_yr_perd_id in number,
422 p_yr_perd_id in number,
423 p_object_version_number in number,
424 -- Bug 3985729
425 p_ordr_num in number) is
426 --
427 l_proc varchar2(72) := g_package||'chk_yr_perd_id';
428 l_api_updating boolean;
429 l_dummy varchar2(1);
430 --
431 cursor c1 is
432 select null
433 from ben_yr_perd a
434 where a.yr_perd_id = p_yr_perd_id;
435 --
436 Begin
437 --
438 hr_utility.set_location('Entering:'||l_proc,5);
439 --
440 l_api_updating := ben_cpy_shd.api_updating
441 (p_popl_yr_perd_id => p_popl_yr_perd_id,
442 p_object_version_number => p_object_version_number);
443 --
444 --Bug 3985729
445 if((p_ordr_num is not NULL) and (p_yr_perd_id is NULL))
446 then
447 fnd_message.set_name('BEN','BEN_94122_PLAN_YR_PERD_MANDTRY');
448 fnd_message.raise_error;
449 --
450 elsif (l_api_updating
451 and nvl(p_yr_perd_id,hr_api.g_number)
455 -- check if yr_perd_id value exists in ben_yr_perd table
452 <> nvl(ben_cpy_shd.g_old_rec.yr_perd_id,hr_api.g_number)
453 or not l_api_updating) then
454 --
456 --
457 open c1;
458 --
459 fetch c1 into l_dummy;
460 if c1%notfound then
461 --
462 close c1;
463 --
464 -- raise error as FK does not relate to PK in ben_yr_perd
465 -- table.
466 --
467 ben_cpy_shd.constraint_error('BEN_POPL_YR_PERD_FK1');
468 --
469 end if;
470 --
471 close c1;
472 --
473 end if;
474 --
475 hr_utility.set_location('Leaving:'||l_proc,10);
476 --
477 End chk_yr_perd_id;
478 --
479 -- ----------------------------------------------------------------------------
480 -- |----------------------------< chk_pgm_pl_id >-----------------------------|
481 -- ----------------------------------------------------------------------------
482 --
483 -- Description
484 -- This procedure checks that either the pl_id or pgm_id is populated and not
485 -- both.
486 --
487 -- Pre-Conditions
488 -- None.
489 --
490 -- In Parameters
491 -- p_pl_id plan id of record.
492 -- p_pgm_id program id of record.
493 --
494 -- Post Success
495 -- Processing continues
496 --
497 -- Post Failure
498 -- Error raised.
499 --
500 -- Access Status
501 -- Internal table handler use only.
502 --
503 Procedure chk_pgm_pl_id (p_pgm_id in number,
504 p_pl_id in number) is
505 --
506 l_proc varchar2(72) := g_package||'chk_pgm_pl_id';
507 --
508 Begin
509 --
510 hr_utility.set_location('Entering:'||l_proc,5);
511 --
512 -- check that only one of the foreign keys is populated.
513 --
514 if p_pgm_id is not null and
515 p_pl_id is not null then
516 --
517 hr_utility.set_message(801,'PGM_OR_PL_ID_SET');
518 hr_utility.raise_error;
519 --
520 elsif p_pgm_id is null and
521 p_pl_id is null then
522 --
523 hr_utility.set_message(801,'PGM_OR_PL_ID_SET');
524 hr_utility.raise_error;
525 --
526 end if;
527 --
528 hr_utility.set_location('Leaving:'||l_proc,10);
529 --
530 End chk_pgm_pl_id;
531 -- ----------------------------------------------------------------------------
532 -- |---------------------------< chk_acpt_clm_rqsts_thru_dt >-----------------|
533 -- ----------------------------------------------------------------------------
534 --
535 -- Description
536 -- This procedure checks that an acpt_clm_rqsts_thru_dt actually exists
537 -- if the py_clms_thru_dt is not null. We can also verify the the
538 -- acpt_clm_rqsts_thru_dt value is greater than the (on or after)
539 -- the YR_PERD end date.
540 --
541 -- Pre-Conditions
542 -- None.
543 --
544 -- In Parameters
545 -- p_acpt_clm_rqsts_thru_dt date value
546 -- p_py_clms_thru_dt date value
547 -- p_yr_perd_id ID of FK column
548 -- p_object_number_number for record
549 --
550 -- Post Success
551 -- Processing continues
552 --
553 -- Post Failure
554 -- Error raised.
555 --
556 -- Access Status
557 -- Internal table handler use only.
558 --
559 Procedure chk_acpt_clm_rqsts_thru_dt (p_acpt_clm_rqsts_thru_dt in date,
560 p_py_clms_thru_dt in date,
561 p_yr_perd_id in number,
562 p_popl_yr_perd_id in number,
563 p_object_version_number in number) is
564 --
565 l_proc varchar2(72) := g_package||'chk_acpt_clm_rqsts_thru_dt';
566 l_api_updating boolean;
567 l_start date;
568 l_end date;
569 --
570 cursor c1 is
571 select start_date, end_date
572 from ben_yr_perd a
573 where a.yr_perd_id = p_yr_perd_id;
574 --
575 Begin
576 --
577 hr_utility.set_location('Entering:'||l_proc,5);
578 --
579 l_api_updating := ben_cpy_shd.api_updating
580 (p_popl_yr_perd_id => p_popl_yr_perd_id,
581 p_object_version_number => p_object_version_number);
582 --
583 -- if p_py_clms_thru_dt is not filled in then we need to tell user we
584 -- have one value but we need the other
585 --
586 if (l_api_updating
587 and (nvl(p_yr_perd_id,hr_api.g_number)
588 <> nvl(ben_cpy_shd.g_old_rec.yr_perd_id,hr_api.g_number)
589 or nvl(p_py_clms_thru_dt,hr_api.g_date)
590 <> nvl(ben_cpy_shd.g_old_rec.py_clms_thru_dt,hr_api.g_date)
591 or nvl(p_acpt_clm_rqsts_thru_dt,hr_api.g_date)
592 <> nvl(ben_cpy_shd.g_old_rec.acpt_clm_rqsts_thru_dt,hr_api.g_date))
593 or not l_api_updating) then
594 --
595 -- OK we are updating or we are inserting so lets check if the values
596 -- of the dates are valid. They must both be null or both be not null
597 -- if the incurred date can be not null , but not otherway arround - tilak
598 --
599 if (p_py_clms_thru_dt is null and
603 p_acpt_clm_rqsts_thru_dt is null) bug 1716967 */
600 p_acpt_clm_rqsts_thru_dt is not null)
601 /* or
602 (p_py_clms_thru_dt is not null and
604 then
605 --
606 -- error py_clms_thru_dt or acpt_rqsts_clm_thru_dt are null
607 --
608 hr_utility.set_message(805,'BEN_91317_PY_CLMS_OR_ACPT_NULL');
609 hr_utility.raise_error;
610 --
611 end if;
612
613 if (p_py_clms_thru_dt is null and p_acpt_clm_rqsts_thru_dt is not null)
614 and ( p_py_clms_thru_dt > p_acpt_clm_rqsts_thru_dt) then
615 hr_utility.set_message(805,'BEN_92696_RQST_DT_TO_INCRD_DT');
616 hr_utility.raise_error;
617 end if ;
618 --
619 -- check if yr_perd_id yields a start and end for a comparison
620 --
621 open c1;
622 --
623 fetch c1 into l_start, l_end;
624 if c1%notfound then
625 --
626 close c1;
627 --
628 -- raise error as FK does not relate to PK in ben_yr_perd
629 -- table.
630 --
631 ben_cpy_shd.constraint_error('BEN_POPL_YR_PERD_FK1');
632 --
633 end if;
634 --
635 -- compare acpt_clm_rqsts_dt to be sure it is greater or equal to
636 -- l_end date
637 --
638 if (p_acpt_clm_rqsts_thru_dt < l_end) then
639 --
640 -- error acpt_clm_rqsts_thru_dt less than yr_perd end_date
641 --
642 hr_utility.set_message(805,'BEN_91318_ACPT_LT_YR_PERD');
643 hr_utility.raise_error;
644 --
645 end if;
646 --
647 close c1;
648 --
649 end if;
650 --
651 hr_utility.set_location('Leaving:'||l_proc,10);
652 --
653 End chk_acpt_clm_rqsts_thru_dt;
654 --
655 -- ----------------------------------------------------------------------------
656 -- |---------------------------< chk_py_clms_thru_dt >------------------------|
657 -- ----------------------------------------------------------------------------
658 --
659 -- Description
660 -- This procedure checks that py_clms_thru_dt is before or equal to the
661 -- acpt_clm_rqsts_thru_dt. Also that the py_clms_thru_dt in on or before
662 -- the yr_perd_id end_date of yr_perd selected.
663 -- if the py_clms_thru_dt is not null.
664 --
665 -- Pre-Conditions
666 -- None.
667 --
668 -- In Parameters
669 -- p_acpt_clm_rqsts_thru_dt date value
670 -- p_py_clms_thru_dt date value
671 -- p_yr_perd_id ID of FK column
672 -- p_object_version_number for record
673 --
674 --
675 -- Post Success
676 -- Processing continues
677 --
678 -- Post Failure
679 -- Error raised.
680 --
681 -- Access Status
682 -- Internal table handler use only.
683 --
684 Procedure chk_py_clms_thru_dt (p_acpt_clm_rqsts_thru_dt in date,
685 p_py_clms_thru_dt in date,
686 p_yr_perd_id in number,
687 p_popl_yr_perd_id in number,
688 p_object_version_number in number) is
689 --
690 l_proc varchar2(72) := g_package||'chk_py_clms_thru_dt';
691 l_api_updating boolean;
692 l_start date;
693 l_end date;
694 --
695 cursor c1 is
696 select start_date, end_date
697 from ben_yr_perd a
698 where a.yr_perd_id = p_yr_perd_id;
699 --
700 cursor c_claims (p_pl_id number) is
701 select null
702 from ben_prtt_reimbmt_rqst_f prc
703 where prc.EXP_INCURD_DT > p_py_clms_thru_dt
704 and prc.pl_id = p_pl_id
705 and prc.popl_yr_perd_id_1 = p_yr_perd_id
706 and prc.prtt_reimbmt_rqst_stat_cd in ('APPRVD','PDINFL','PRTLYPD');
707 --
708 l_Claims varchar2(1);
709 --
710 Begin
711 --
712 hr_utility.set_location('Entering:'||l_proc,5);
713 --
714 l_api_updating := ben_cpy_shd.api_updating
715 (p_popl_yr_perd_id => p_popl_yr_perd_id,
716 p_object_version_number => p_object_version_number);
717 --
718 -- if p_py_clms_thru_dt is not filled in then we need to tell user we
719 -- have one value but we need the other
720 --
721 if (l_api_updating
722 and (nvl(p_yr_perd_id,hr_api.g_number)
723 <> nvl(ben_cpy_shd.g_old_rec.yr_perd_id,hr_api.g_number)
724 or nvl(p_py_clms_thru_dt,hr_api.g_date)
725 <> nvl(ben_cpy_shd.g_old_rec.py_clms_thru_dt,hr_api.g_date)
726 or nvl(p_acpt_clm_rqsts_thru_dt,hr_api.g_date)
727 <> nvl(ben_cpy_shd.g_old_rec.acpt_clm_rqsts_thru_dt,hr_api.g_date))
728 or not l_api_updating) then
729 --
730 -- OK we are updating or we are inserting so lets check if the values
731 -- of the dates are valid. They must both be null or both be not null
732 --
733 if (p_py_clms_thru_dt <= p_acpt_clm_rqsts_thru_dt) and
734 p_py_clms_thru_dt is not null and
735 p_acpt_clm_rqsts_thru_dt is not null then
736 --
737 -- check if yr_perd_id yields a start and end for a comparison
738 --
739 open c1;
740 --
744 close c1;
741 fetch c1 into l_start, l_end;
742 if c1%notfound then
743 --
745 --
746 -- raise error as FK does not relate to PK in ben_yr_perd
747 -- table.
748 --
749 ben_cpy_shd.constraint_error('BEN_POPL_YR_PERD_FK1');
750 --
751 end if;
752 --
753 -- compare py_clms_dt to be sure it is on or before (less than)
754 -- l_end date
755 --
756 /* fsa grace period enh - pay claims thru date may be greater than
757 year period end date
758 if (p_py_clms_thru_dt > l_end) then
759 --
760 -- error py_clms_thru_dt is greater than yr_perd end_date
761 --
762 hr_utility.set_message(805,'BEN_91319_PY_CLMS_GT_YR_END');
763 hr_utility.raise_error;
764 --
765 end if;
766 */
767 --
768 close c1;
769 --
770 else
771 --
772 -- Only fail here if they the values are both not null
773 --
774 if p_py_clms_thru_dt is not null and
775 p_acpt_clm_rqsts_thru_dt is not null then
776 --
777 -- error py_clms_thru_dt must be less than acpt_rqsts_clm_thru_dt
778 --
779 hr_utility.set_message(805,'BEN_91316_PY_CLMS_LT_ACPT');
780 hr_utility.raise_error;
781 --
782 end if;
783 --
784 end if;
785 --
786 end if;
787 --
788 if p_py_clms_thru_dt < ben_cpy_shd.g_old_rec.py_clms_thru_dt then
789 --
790 open c_claims (ben_cpy_shd.g_old_rec.pl_id);
791 fetch c_claims into l_claims;
792 if c_claims%found then
793 --
794 close c_claims;
795 hr_utility.set_message(805,'BEN_91316_PY_CLMS_LT_ACPT');
796 hr_utility.raise_error;
797 else
798 --
799 close c_claims;
800 --
801 end if;
802 --
803 end if;
804
805 hr_utility.set_location('Leaving:'||l_proc,10);
806 --
807 End chk_py_clms_thru_dt;
808 --
809 -- ---------------------------------------------------------------------------
810 -- |-----------------------< chk_ordr_num_unique >---------------------------|
811 -- ---------------------------------------------------------------------------
812 --
813 -- Description
814 -- ensure that the Sequence Number is unique
815 -- within business_group
816 --
817 -- Pre Conditions
818 -- None.
819 --
820 -- In Parameters
821 -- p_ordr_num Sequence Number
822 -- p_popl_yr_perd_id Primary Key of BEN_POPL_YR_PERD
823 -- p_pl_id
824 -- p_business_group_id
825 --
826 -- Post Success
827 -- Processing continues
828 --
829 -- Post Failure
830 -- Errors handled by the procedure
831 --
832 -- Access Status
833 -- Internal table handler use only.
834 --
835 -- ----------------------------------------------------------------------------
836 Procedure chk_ordr_num_unique
837 ( p_popl_yr_perd_id in number
838 /* bug 2923047 */
839 ,p_pgm_id in number
840 ,p_pl_id in number
841 ,p_ordr_num in number
842 ,p_business_group_id in number)
843 is
844 l_proc varchar2(72) := g_package||'chk_ordr_num_unique';
845 l_dummy char(1);
846 cursor c1 is select null
847 from ben_popl_yr_perd
848 Where popl_yr_perd_id <> nvl(p_popl_yr_perd_id,-1)
849 /* bug 2923047 -- add pgm_id also */
850 and
851 (pl_id = p_pl_id
852 or
853 pgm_id = p_pgm_id
854 )
855 and ordr_num = p_ordr_num
856 and business_group_id = p_business_group_id;
857 --
858 Begin
859 hr_utility.set_location('Entering:'||l_proc, 5);
860 --
861 open c1;
862 fetch c1 into l_dummy;
863 if c1%found then
864 close c1;
865 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
866 fnd_message.raise_error;
867 end if;
868 --
869 hr_utility.set_location('Leaving:'||l_proc, 15);
870 End chk_ordr_num_unique;
871 --
872 -- ----------------------------------------------------------------------------
873 -- |---------------------------< insert_validate >----------------------------|
874 -- ----------------------------------------------------------------------------
875 Procedure insert_validate(p_rec in ben_cpy_shd.g_rec_type) is
876 --
877 l_proc varchar2(72) := g_package||'insert_validate';
878 --
879 Begin
880 hr_utility.set_location('Entering:'||l_proc, 5);
881 --
882 -- Call all supporting business operations
883 --
884 --
885 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
886 --
887 chk_popl_yr_perd_id
888 (p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
889 p_object_version_number => p_rec.object_version_number);
890 --
891 chk_yr_perd_id
895 --Bug 3985729
892 (p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
893 p_yr_perd_id => p_rec.yr_perd_id,
894 p_object_version_number => p_rec.object_version_number,
896 p_ordr_num => p_rec.ordr_num);
897 --
898 chk_pgm_pl_id
899 (p_pgm_id => p_rec.pgm_id,
900 p_pl_id => p_rec.pl_id);
901 --
902 chk_pgm_pl_yr_perd_id
903 (p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
904 p_pgm_id => p_rec.pgm_id,
905 p_pl_id => p_rec.pl_id,
906 p_business_group_id => p_rec.business_group_id,
907 p_yr_perd_id => p_rec.yr_perd_id,
908 p_object_version_number => p_rec.object_version_number);
909 --
910 chk_acpt_clm_rqsts_thru_dt
911 (p_acpt_clm_rqsts_thru_dt => p_rec.acpt_clm_rqsts_thru_dt,
912 p_py_clms_thru_dt => p_rec.py_clms_thru_dt,
913 p_yr_perd_id => p_rec.yr_perd_id,
914 p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
915 p_object_version_number => p_rec.object_version_number);
916 --
917 chk_py_clms_thru_dt
918 (p_acpt_clm_rqsts_thru_dt => p_rec.acpt_clm_rqsts_thru_dt,
919 p_py_clms_thru_dt => p_rec.py_clms_thru_dt,
920 p_yr_perd_id => p_rec.yr_perd_id,
921 p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
922 p_object_version_number => p_rec.object_version_number);
923 --
924 chk_ordr_num_unique
925 (p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
926 p_pl_id => p_rec.pl_id,
927 /* bug 2923047 */
928 p_pgm_id => p_rec.pgm_id,
929 p_ordr_num => p_rec.ordr_num,
930 p_business_group_id => p_rec.business_group_id);
931 --
932 hr_utility.set_location(' Leaving:'||l_proc, 10);
933 End insert_validate;
934 --
935 -- ----------------------------------------------------------------------------
936 -- |---------------------------< update_validate >----------------------------|
937 -- ----------------------------------------------------------------------------
938 Procedure update_validate(p_rec in ben_cpy_shd.g_rec_type) is
939 --
940 l_proc varchar2(72) := g_package||'update_validate';
941 --
942 Begin
943 hr_utility.set_location('Entering:'||l_proc, 5);
944 --
945 -- Call all supporting business operations
946 --
947 --
948 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
949 --
950 chk_popl_yr_perd_id
951 (p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
952 p_object_version_number => p_rec.object_version_number);
953 --
954 chk_yr_perd_id
955 (p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
956 p_yr_perd_id => p_rec.yr_perd_id,
957 p_object_version_number => p_rec.object_version_number,
958 --Bug 3985729
959 p_ordr_num => p_rec.ordr_num);
960 --
961 chk_pgm_pl_id
962 (p_pgm_id => p_rec.pgm_id,
963 p_pl_id => p_rec.pl_id);
964 --
965 chk_pgm_pl_yr_perd_id
966 (p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
967 p_pgm_id => p_rec.pgm_id,
968 p_pl_id => p_rec.pl_id,
969 p_business_group_id => p_rec.business_group_id,
970 p_yr_perd_id => p_rec.yr_perd_id,
971 p_object_version_number => p_rec.object_version_number);
972 --
973 chk_acpt_clm_rqsts_thru_dt
974 (p_acpt_clm_rqsts_thru_dt => p_rec.acpt_clm_rqsts_thru_dt,
975 p_py_clms_thru_dt => p_rec.py_clms_thru_dt,
976 p_yr_perd_id => p_rec.yr_perd_id,
977 p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
978 p_object_version_number => p_rec.object_version_number);
979 --
980 chk_py_clms_thru_dt
981 (p_acpt_clm_rqsts_thru_dt => p_rec.acpt_clm_rqsts_thru_dt,
982 p_py_clms_thru_dt => p_rec.py_clms_thru_dt,
983 p_yr_perd_id => p_rec.yr_perd_id,
984 p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
985 p_object_version_number => p_rec.object_version_number);
986 --
987 chk_ordr_num_unique
988 (p_popl_yr_perd_id => p_rec.popl_yr_perd_id,
989 p_pl_id => p_rec.pl_id,
990 /* bug 2923047 */
991 p_pgm_id => p_rec.pgm_id,
992 p_ordr_num => p_rec.ordr_num,
993 p_business_group_id => p_rec.business_group_id);
994 --
995 hr_utility.set_location(' Leaving:'||l_proc, 10);
996 End update_validate;
997 --
998 -- ----------------------------------------------------------------------------
999 -- |---------------------------< delete_validate >----------------------------|
1000 -- ----------------------------------------------------------------------------
1001 Procedure delete_validate(p_rec in ben_cpy_shd.g_rec_type) is
1002 --
1003 l_proc varchar2(72) := g_package||'delete_validate';
1004 --
1005 Begin
1006 hr_utility.set_location('Entering:'||l_proc, 5);
1007 --
1008 -- Call all supporting business operations
1009 --
1010 hr_utility.set_location(' Leaving:'||l_proc, 10);
1011 End delete_validate;
1012 --
1013 -- ---------------------------------------------------------------------------
1014 -- |---------------------< return_legislation_code >-------------------------|
1015 -- ---------------------------------------------------------------------------
1016 --
1017 function return_legislation_code
1018 (p_popl_yr_perd_id in number) return varchar2 is
1019 --
1020 -- Declare cursor
1021 --
1022 cursor csr_leg_code is
1023 select a.legislation_code
1024 from per_business_groups a,
1025 ben_popl_yr_perd b
1026 where b.popl_yr_perd_id = p_popl_yr_perd_id
1027 and a.business_group_id = b.business_group_id;
1028 --
1029 -- Declare local variables
1030 --
1031 l_legislation_code varchar2(150);
1032 l_proc varchar2(72) := g_package||'return_legislation_code';
1033 --
1034 begin
1035 --
1036 hr_utility.set_location('Entering:'|| l_proc, 10);
1037 --
1038 -- Ensure that all the mandatory parameter are not null
1039 --
1040 hr_api.mandatory_arg_error(p_api_name => l_proc,
1041 p_argument => 'popl_yr_perd_id',
1042 p_argument_value => p_popl_yr_perd_id);
1043 --
1044 open csr_leg_code;
1045 --
1046 fetch csr_leg_code into l_legislation_code;
1047 --
1048 if csr_leg_code%notfound then
1049 --
1050 close csr_leg_code;
1051 --
1052 -- The primary key is invalid therefore we must error
1053 --
1054 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1055 hr_utility.raise_error;
1056 --
1057 end if;
1058 --
1059 close csr_leg_code;
1060 --
1061 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1062 --
1063 return l_legislation_code;
1064 --
1065 end return_legislation_code;
1066 --
1067 end ben_cpy_bus;
[Home] [Help]