[Home] [Help]
PACKAGE BODY: APPS.BEN_WYP_BUS
Source
1 Package Body ben_wyp_bus as
2 /* $Header: bewyprhi.pkb 115.12 2003/01/01 00:03:22 mmudigon ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_wyp_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_wthn_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 -- wthn_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_wthn_yr_perd_id(p_wthn_yr_perd_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_wthn_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_wyp_shd.api_updating
47 (p_wthn_yr_perd_id => p_wthn_yr_perd_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_wthn_yr_perd_id,hr_api.g_number)
52 <> ben_wyp_shd.g_old_rec.wthn_yr_perd_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_wyp_shd.constraint_error('BEN_WTHN_YR_PERD_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_wthn_yr_perd_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_wyp_shd.constraint_error('BEN_WTHN_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_wthn_yr_perd_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_yr_perd_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_wthn_yr_perd_id PK
89 -- p_yr_perd_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_yr_perd_id (p_wthn_yr_perd_id in number,
102 p_yr_perd_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_yr_perd_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from ben_yr_perd a
112 where a.yr_perd_id = p_yr_perd_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := ben_wyp_shd.api_updating
119 (p_wthn_yr_perd_id => p_wthn_yr_perd_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_yr_perd_id,hr_api.g_number)
124 <> nvl(ben_wyp_shd.g_old_rec.yr_perd_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_yr_perd_id is not null then
127 --
128 -- check if yr_perd_id value exists in ben_yr_perd table
129 --
130 open c1;
131 --
132 fetch c1 into l_dummy;
133 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in ben_yr_perd
138 -- table.
139 --
140 ben_wyp_shd.constraint_error('BEN_WTHN_YR_PERD_FK1');
141 --
142 end if;
143 --
144 close c1;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 End chk_yr_perd_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |---------------< chk_day_and_month_validation >------------------------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- Ensures that the start month and end month fall within the start and
158 -- end dates. Also ensures that the start day and end day fall within the
159 -- start and end dates.
160 --
161 -- Pre Conditions
162 -- None.
163 --
164 -- In Parameters
165 -- p_start_date
166 -- p_end_date
167 -- p_business_group_id
168 --
169 -- Post Success
170 -- Processing continues
171 --
172 -- Post Failure
173 -- Errors handled by the procedure
174 --
175 -- Access Status
176 -- Internal table handler use only.
177 --
178 -- ----------------------------------------------------------------------------
179 Procedure chk_day_and_month_validation
180 (p_yr_perd_id in number
181 ,p_strt_day in number
182 ,p_strt_mo in number
183 ,p_end_day in number
184 ,p_end_mo in number
185 ,p_business_group_id in number)
186 is
187 l_proc varchar2(72) := g_package||'chk_day_and_month_validation';
188 strt_dd number(2);
189 strt_mm number(2);
190 end_dd number(2);
191 end_mm number(2);
192 l_perd_typ_cd varchar2(30);
193 cursor c1 is select to_number(to_char(start_date, 'DD')) strt_dd,
194 to_number(to_char(start_date, 'MM')) strt_mm,
195 to_number(to_char(end_date, 'DD')) end_dd,
196 to_number(to_char(end_date, 'MM')) end_mm,
197 perd_typ_cd
198 from ben_yr_perd
199 where yr_perd_id = p_yr_perd_id
200 and business_group_id = p_business_group_id;
201
202 --
203 Begin
204 hr_utility.set_location('Entering:'||l_proc, 5);
205 --
206 open c1;
207 fetch c1 into strt_dd, strt_mm, end_dd, end_mm , l_perd_typ_cd;
208 close c1;
209
210 if l_perd_typ_cd = 'CLNDR' then
211
212 if p_strt_mo < strt_mm or p_strt_mo > end_mm then
213 --
214 fnd_message.set_name('BEN','BEN_92132_INVALID_START_MONTH');
215 fnd_message.raise_error;
216 --
217 elsif p_end_mo > end_mm or p_end_mo < strt_mm then
218 --
219 fnd_message.set_name('BEN','BEN_92133_INVALID_END_MONTH');
223 --
220 fnd_message.raise_error;
221 --
222 elsif p_end_mo < p_strt_mo then
224 fnd_message.set_name('BEN','BEN_92134_INVALID_MONTH_ORDER');
225 fnd_message.raise_error;
226 --
227 end if;
228
229 --
230 if p_strt_mo = strt_mm then
231 --
232 if p_strt_day < strt_dd then
233 --
234 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
235 fnd_message.raise_error;
236 --
237 end if;
238 --
239 elsif p_end_mo = end_mm then
240 --
241 if p_end_day > end_dd then
242 --
243 fnd_message.set_name('BEN','BEN_92136_INVALID_END_DAY');
244 fnd_message.raise_error;
245 --
246 end if;
247 --
248 end if;
249 --
250
251 -- Fix for Bug 1646921
252 elsif l_perd_typ_cd = 'FISCAL' then
253
254
255 -- Case I: Plan Period fall within the same year
256
257 if strt_mm < end_mm then
258
259 if p_end_mo < p_strt_mo then
260 --
261 fnd_message.set_name('BEN','BEN_92134_INVALID_MONTH_ORDER');
262 fnd_message.raise_error;
263 --
264 end if;
265 if p_strt_mo < strt_mm then
266 --
267 fnd_message.set_name('BEN','BEN_92132_INVALID_START_MONTH');
268 fnd_message.raise_error;
269 --
270 end if;
271 if p_strt_mo = strt_mm and p_strt_day < strt_dd then
272 --
273 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
274 fnd_message.raise_error;
275 --
276 end if;
277
278 if p_strt_mo > strt_mm then
279 if p_strt_mo > end_mm then
280 --
281 fnd_message.set_name('BEN','BEN_92132_INVALID_START_MONTH');
282 fnd_message.raise_error;
283 --
284 end if;
285
286 if p_strt_mo = end_mm and p_strt_day > end_dd then
287 --
288 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
289 fnd_message.raise_error;
290 --
291 end if;
292 end if;
293
294 if p_end_mo > end_mm then
295 --
296 fnd_message.set_name('BEN','BEN_92133_INVALID_END_MONTH');
297 fnd_message.raise_error;
298 --
299 end if;
300 if p_end_mo = end_mm and p_end_day > end_dd then
301 --
302 fnd_message.set_name('BEN','BEN_92136_INVALID_END_DAY');
303 fnd_message.raise_error;
304 --
305 end if;
306 if p_end_mo <end_mm then
307 if p_end_mo < strt_mm then
308 --
309 fnd_message.set_name('BEN','BEN_92133_INVALID_END_MONTH');
310 fnd_message.raise_error;
311 --
312 end if;
313 if p_end_mo = strt_mm and p_end_day < strt_dd then
314 --
315 fnd_message.set_name('BEN','BEN_92136_INVALID_END_DAY');
316 fnd_message.raise_error;
317 --
318 end if;
319 end if;
320
321 if p_strt_mo = p_end_mo and p_strt_day > p_end_day then
322 --
323 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
324 fnd_message.raise_error;
325 --
326 end if;
327
328 end if;
329
330
331 -- Case II: Plan Period span over years
332
333 if (strt_mm > end_mm) or
334 (strt_mm = end_mm and strt_dd > end_dd) then
335
336 if p_strt_mo < strt_mm then
337
338 if (p_strt_mo > end_mm) or
339 (p_end_mo > strt_mm) or
340 (p_end_mo < p_strt_mo) then
341 --
342 fnd_message.set_name('BEN','BEN_92132_INVALID_START_MONTH');
343 fnd_message.raise_error;
344 --
345 end if;
346 if p_strt_mo = end_mm and p_strt_day > end_dd then
347 --
348 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
349 fnd_message.raise_error;
350 --
351 end if;
352 if p_end_mo = strt_mm and p_end_day > strt_dd then
353 --
354 fnd_message.set_name('BEN','BEN_92136_INVALID_END_DAY');
355 fnd_message.raise_error;
356 --
357 end if;
358 end if;
359
360 if p_strt_mo = strt_mm then
361
362 if p_strt_day < strt_dd then
363 if p_end_mo < p_strt_mo or ( p_end_mo = p_strt_mo and p_end_day < p_strt_day) then
364 --
365 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
366 fnd_message.raise_error;
367 --
368 end if;
369
370 if ( strt_mm <> end_mm ) or (strt_mm = end_mm and p_strt_day > end_dd) then
371 --
372 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
373 fnd_message.raise_error;
374 --
375 end if;
376 end if;
377 end if;
378
379 if p_end_mo > end_mm then
380
381 if (p_end_mo < strt_mm) or
382 (p_strt_mo < end_mm) or
383 (p_strt_mo > p_end_mo) then
384 --
385 fnd_message.set_name('BEN','BEN_92133_INVALID_END_MONTH');
386 fnd_message.raise_error;
387 --
388 end if;
389 if p_end_mo = strt_mm and p_end_day < strt_dd then
390 --
391 fnd_message.set_name('BEN','BEN_92136_INVALID_END_DAY');
392 fnd_message.raise_error;
393 --
394 end if;
395 if p_strt_mo = end_mm and p_strt_day < end_dd then
396 --
397 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
398 fnd_message.raise_error;
399 --
400 end if;
401
402
403 end if;
404
405 if p_end_mo = end_mm then
406 if p_end_day > end_dd then
407 if p_strt_mo > p_end_mo or (p_strt_mo = p_end_mo and p_strt_day > p_end_day) then
408 --
409 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
410 fnd_message.raise_error;
411 --
412 end if;
413
414 if ( end_mm <> strt_mm ) or ( end_mm = strt_mm and p_end_day < strt_dd ) then
415 --
416 fnd_message.set_name('BEN','BEN_92136_INVALID_END_DAY');
417 fnd_message.raise_error;
418 --
419 end if;
420 end if;
421 end if;
422 end if;
423
424
425 -- Case III Plan Year falls within the same month
426
427 if strt_mm = end_mm and strt_dd <= end_dd then
428
429 if (p_strt_mo <> strt_mm) then
430 --
431 fnd_message.set_name('BEN','BEN_92132_INVALID_START_MONTH');
432 fnd_message.raise_error;
433 --
434 end if;
435 if (p_end_mo <> end_mm) then
436 --
437 fnd_message.set_name('BEN','BEN_92133_INVALID_END_MONTH');
438 fnd_message.raise_error;
439 --
440 end if;
441
442 if p_strt_day < strt_dd or p_strt_day > p_end_day then
443 --
444 fnd_message.set_name('BEN','BEN_92135_INVALID_START_DAY');
445 fnd_message.raise_error;
446 --
447 end if;
448 if p_end_day > end_dd then
449 --
450 fnd_message.set_name('BEN','BEN_92136_INVALID_END_DAY');
451 fnd_message.raise_error;
452 --
453 end if;
454
455 end if;
456 -- End of fix, Bug 1646921
457
458 end if;
459
460 hr_utility.set_location('Leaving:'||l_proc, 15);
461 End chk_day_and_month_validation;
462 --
463 -- ----------------------------------------------------------------------------
464 -- |---------------------< chk_no_overlapping >------------------------|
465 -- ----------------------------------------------------------------------------
466 --
467 -- Description
468 -- This procedure is used to check that the starting dates and ending dates
469 -- do not overlap within the same unit of measure
470 -- on insert and update.
471 --
472 -- Pre Conditions
473 -- None.
474 --
475 -- In Parameters
476 -- wthn_yr_perd_id PK of record being inserted or updated
477 -- strt_day Starting Day
478 -- strt_mo Starting Month
479 -- end_day Ending Day
480 -- end_mo Ending Month
481 -- business_group_id of the record beeing inserted or updated
482 -- effective_date effective date of the session
483 -- object_version_number Object version number of record being
484 -- inserted or updated.
485 --
486 -- Post Success
487 -- Processing continues
488 --
489 -- Post Failure
490 -- Error handled by procedure
491 --
492 -- Access Status
493 -- Internal table handler use only.
494 --
495 Procedure chk_no_overlapping(p_wthn_yr_perd_id in number
496 ,p_yr_perd_id in number
497 ,p_strt_day in number
498 ,p_strt_mo in number
499 ,p_end_day in number
500 ,p_end_mo in number
501 ,p_tm_uom in varchar2
502 ,p_business_group_id in number) is
503 --
504 l_proc varchar2(72) := g_package||'chk_no_overlapping';
505 existing_strt_dd number(2);
506 existing_strt_mm number(2);
507 existing_end_dd number(2);
508 existing_end_mm number(2);
509 --
510 --
511 cursor uom is
515 and yr_perd_id = p_yr_perd_id
512 select strt_day, strt_mo, end_day, end_mo
513 from ben_wthn_yr_perd
514 where tm_uom = p_tm_uom
516 and wthn_yr_perd_id <> nvl(p_wthn_yr_perd_id, hr_api.g_number)
517 and business_group_id + 0 = p_business_group_id;
518 --
519 Begin
520 --
521 hr_utility.set_location('Entering:'||l_proc, 5);
522 --
523 -- check if this combination of dates overlaps within the same uom
524 --
525 open uom;
526 --
527 loop
528 --
529 fetch uom into existing_strt_dd,
530 existing_strt_mm,
531 existing_end_dd,
532 existing_end_mm;
533 exit when uom%notfound;
534 --
535 if (p_strt_mo < existing_end_mm and p_strt_mo > existing_strt_mm) or
536 (p_end_mo > existing_strt_mm and p_end_mo < existing_end_mm) then
537 --
538 close uom;
539 fnd_message.set_name('BEN','BEN_92352_MAY_NOT_OVERLAP');
540 fnd_message.raise_error;
541 --
542 elsif p_strt_mo = existing_strt_mm then
543 --
544 if p_strt_mo = existing_end_mm then
545 --
546 if p_strt_day < existing_end_dd then
547 --
548 close uom;
549 fnd_message.set_name('BEN','BEN_92352_MAY_NOT_OVERLAP');
550 fnd_message.raise_error;
551 --
552 end if;
553 --
554 else
555 --
556 if p_strt_day > existing_strt_dd then
557 --
558 close uom;
559 fnd_message.set_name('BEN','BEN_92352_MAY_NOT_OVERLAP');
560 fnd_message.raise_error;
561 --
562 end if;
563 --
564 end if;
565 --
566 elsif p_strt_mo = existing_end_mm then
567 --
568 if p_strt_day <= existing_end_dd then
569 --
570 close uom;
571 fnd_message.set_name('BEN','BEN_92352_MAY_NOT_OVERLAP');
572 fnd_message.raise_error;
573 --
574 end if;
575 --
576 elsif p_end_mo = existing_strt_mm then
577 --
578 if p_end_day > existing_strt_dd then
579 --
580 close uom;
581 fnd_message.set_name('BEN','BEN_92352_MAY_NOT_OVERLAP');
582 fnd_message.raise_error;
583 --
584 end if;
585 --
586 elsif p_end_mo = existing_end_mm then
587 --
588 if p_end_day < existing_end_dd then
589 --
590 close uom;
591 fnd_message.set_name('BEN','BEN_92352_MAY_NOT_OVERLAP');
592 fnd_message.raise_error;
593 --
594 end if;
595 --
596 end if;
597 --
598 end loop;
599 --
600 close uom;
601 --
602 hr_utility.set_location('Leaving:'||l_proc, 20);
603 --
604 End chk_no_overlapping;
605 --
606 -- ----------------------------------------------------------------------------
607 -- |---------------------< chk_unique_combination >------------------------|
608 -- ----------------------------------------------------------------------------
609 --
610 -- Description
611 -- This procedure is used to check that the starting dates and ending dates
612 -- do not overlap within the same unit of measure
613 -- on insert and update.
614 --
615 -- Pre Conditions
616 -- None.
617 --
618 -- In Parameters
619 -- wthn_yr_perd_id PK of record being inserted or updated
620 -- strt_day Starting Day
621 -- strt_mo Starting Month
622 -- end_day Ending Day
623 -- end_mo Ending Month
624 -- business_group_id of the record beeing inserted or updated
625 -- effective_date effective date of the session
626 -- object_version_number Object version number of record being
627 -- inserted or updated.
628 --
629 -- Post Success
630 -- Processing continues
631 --
632 -- Post Failure
633 -- Error handled by procedure
634 --
635 -- Access Status
636 -- Internal table handler use only.
637 --
638 --
639 -- Bug 2167540: Added p_yr_perd_id to chk_unique_combination procedure
640 --
641 Procedure chk_unique_combination(p_wthn_yr_perd_id in number
642 ,p_yr_perd_id in number
643 ,p_strt_day in number
644 ,p_strt_mo in number
645 ,p_end_day in number
646 ,p_end_mo in number
647 ,p_tm_uom in varchar2
648 ,p_business_group_id in number) is
649 --
650 l_proc varchar2(72) := g_package||'chk_unique_combination';
651 l_exists char(1);
652 --
653 cursor c1 is
654 select null
655 from ben_wthn_yr_perd
656 where tm_uom = p_tm_uom
657 and yr_perd_id = p_yr_perd_id
658 and strt_day = p_strt_day
659 and strt_mo = p_strt_mo
660 and end_day = p_end_day
661 and end_mo = p_end_mo
662 and wthn_yr_perd_id <> nvl(p_wthn_yr_perd_id, hr_api.g_number)
663 and business_group_id + 0 = p_business_group_id;
664 --
665 Begin
666 --
667 hr_utility.set_location('Entering:'||l_proc, 5);
668 --
669 --
670 -- check if this combination already exists within the same uom
671 --
672 open c1;
673 fetch c1 into l_exists;
674 if c1%found then
675 close c1;
676
677 -- raise error as that combination of starting day, starting
678 -- month, ending day and ending month already exists within
679 -- the selected uom
680 --
681 fnd_message.set_name('BEN','BEN_92351_COMBO_NOT_UNIQUE');
682 fnd_message.raise_error;
683 --
684 end if;
685 --
686 close c1;
687 --
688 hr_utility.set_location('Leaving:'||l_proc, 20);
689 --
690 End chk_unique_combination;
691 --
692 -- ----------------------------------------------------------------------------
693 -- |------< chk_tm_uom >------|
694 -- ----------------------------------------------------------------------------
695 --
696 -- Description
697 -- This procedure is used to check that the lookup value is valid.
698 --
699 -- Pre Conditions
700 -- None.
701 --
702 -- In Parameters
703 -- wthn_yr_perd_id PK of record being inserted or updated.
704 -- tm_uom Value of lookup code.
705 -- effective_date effective date
706 -- object_version_number Object version number of record being
707 -- inserted or updated.
708 --
709 -- Post Success
710 -- Processing continues
711 --
712 -- Post Failure
713 -- Error handled by procedure
714 --
715 -- Access Status
716 -- Internal table handler use only.
717 --
718 Procedure chk_tm_uom(p_wthn_yr_perd_id in number,
719 p_tm_uom in varchar2,
720 p_effective_date in date,
721 p_object_version_number in number) is
722 --
723 l_proc varchar2(72) := g_package||'chk_tm_uom';
724 l_api_updating boolean;
725 --
726 Begin
727 --
728 hr_utility.set_location('Entering:'||l_proc, 5);
729 --
730 l_api_updating := ben_wyp_shd.api_updating
731 (p_wthn_yr_perd_id => p_wthn_yr_perd_id,
732 p_object_version_number => p_object_version_number);
733 --
734 if (l_api_updating
735 and p_tm_uom
736 <> nvl(ben_wyp_shd.g_old_rec.tm_uom,hr_api.g_varchar2)
737 or not l_api_updating)
738 and p_tm_uom is not null then
739 --
740 -- check if value of lookup falls within lookup type.
741 --
742 if hr_api.not_exists_in_hr_lookups
743 (p_lookup_type => 'BEN_TM_UOM',
744 p_lookup_code => p_tm_uom,
745 p_effective_date => p_effective_date) then
746 --
747 -- raise error as does not exist as lookup
748 --
749 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
750 fnd_message.set_token('FIELD', 'p_tm_uom');
751 fnd_message.set_token('TYPE', 'BEN_TM_UOM');
752 fnd_message.raise_error;
753 --
754 end if;
755 --
756 end if;
757 --
758 hr_utility.set_location('Leaving:'||l_proc,10);
759 --
760 end chk_tm_uom;
761 --
762
763 --
764 -- ----------------------------------------------------------------------------
765 -- |---------------< chk_valid_date >------------------------------------------|
766 -- ----------------------------------------------------------------------------
767 --
768 -- Description
769 -- Ensures that the Start Day and Start Month are valid dates in the
770 -- selected Year period. Also endures that the End Day and End Month are
771 -- valid dates in the selected Year period.
772 --
773 -- Pre Conditions
774 -- None.
775 --
776 -- In Parameters
777 -- p_day
778 -- p_month
779 -- p_type
780 -- p_yr_perd_id
781 -- p_business_group_id
782 --
783 -- Post Success
784 -- Processing continues
785 --
786 -- Post Failure
787 -- Errors handled by the procedure
788 --
789 -- Access Status
790 -- Internal table handler use only.
791 --
792 -- ----------------------------------------------------------------------------
793 Procedure chk_valid_date
794 (p_day in number
795 ,p_month in number
796 ,p_type in varchar2
797 ,p_yr_perd_id in number
798 ,p_business_group_id in number)
799 is
800 l_proc varchar2(72) := g_package||'chk_valid_date';
801 l_strt_dd number(2);
802 l_strt_mm number(2);
803 l_strt_yy number(4);
804 l_end_dd number(2);
805 l_end_mm number(2);
806 l_end_yy number(4);
807
808 l_year number(4);
812 cursor c1 is select to_number(to_char(start_date, 'DD')) l_strt_dd,
809 l_date_str varchar2(10);
810 l_valid_date date;
811
813 to_number(to_char(start_date, 'MM')) l_strt_mm,
814 to_number(to_char(start_date, 'YYYY')) l_strt_yy,
815 to_number(to_char(end_date, 'DD')) l_end_dd,
816 to_number(to_char(end_date, 'MM')) l_end_mm,
817 to_number(to_char(end_date, 'YYYY')) l_end_yy
818 from ben_yr_perd
819 where yr_perd_id = p_yr_perd_id
820 and business_group_id = p_business_group_id;
821
822 --
823 Begin
824 hr_utility.set_location('Entering:'||l_proc, 5);
825 --
826 open c1;
827 fetch c1 into l_strt_dd, l_strt_mm, l_strt_yy,l_end_dd, l_end_mm,l_end_yy;
828 close c1;
829 --
830
831 -- From the Year period, determine the Year in which the user entered Month falls
832
833 if l_strt_yy = l_end_yy then
834 l_year := l_strt_yy;
835 elsif p_month between l_strt_mm and 12 then
836 l_year := l_strt_yy;
837 else
838 l_year := l_end_yy;
839 end if;
840
841 l_date_str := l_year||'/'||p_month||'/'||p_day;
842
843 --
844 -- check if the date is valid
845 --
846
847 begin
848 l_valid_date := fnd_date.canonical_to_date(l_date_str);
849 exception
850 when others then
851 --
852 -- raise error as the date is not valid.
853 --
854 fnd_message.set_name('BEN','BEN_93012_INVALID_DATE');
855 fnd_message.set_token('TYPE', p_type);
856 fnd_message.raise_error;
857 end;
858
859 hr_utility.set_location('Leaving:'||l_proc, 15);
860 End chk_valid_date;
861 --
862 -- ----------------------------------------------------------------------------
863 -- |---------------------------< insert_validate >----------------------------|
864 -- ----------------------------------------------------------------------------
865 Procedure insert_validate(p_rec in ben_wyp_shd.g_rec_type
866 ,p_effective_date in date) is
867 --
868 l_proc varchar2(72) := g_package||'insert_validate';
869 --
870 Begin
871 hr_utility.set_location('Entering:'||l_proc, 5);
872 --
873 -- Call all supporting business operations
874 --
875 --
876 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
877 --
878 chk_wthn_yr_perd_id
879 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
880 p_object_version_number => p_rec.object_version_number);
881 --
882 chk_yr_perd_id
883 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
884 p_yr_perd_id => p_rec.yr_perd_id,
885 p_object_version_number => p_rec.object_version_number);
886 --
887
888 -- Bug - 2248735 Check for valid Within Year Period Start and End Dates
889
890 chk_valid_date
891 (p_day => p_rec.strt_day,
892 p_month => p_rec.strt_mo,
893 p_type => 'Start',
894 p_yr_perd_id => p_rec.yr_perd_id,
895 p_business_group_id => p_rec.business_group_id);
896 --
897 chk_valid_date
898 (p_day => p_rec.end_day,
899 p_month => p_rec.end_mo,
900 p_type => 'End',
901 p_yr_perd_id => p_rec.yr_perd_id,
902 p_business_group_id => p_rec.business_group_id);
903 --
904 -- End Bug 2248735
905
906 --
907 chk_day_and_month_validation
908 (p_yr_perd_id => p_rec.yr_perd_id,
909 p_strt_day => p_rec.strt_day,
910 p_strt_mo => p_rec.strt_mo,
911 p_end_day => p_rec.end_day,
912 p_end_mo => p_rec.end_mo,
913 p_business_group_id => p_rec.business_group_id);
914 --
915 -- Bug - 2167540 : Commented to allow overlapping of start and end values for CWB.
916 /*
917 chk_no_overlapping
918 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
919 p_yr_perd_id => p_rec.yr_perd_id,
920 p_strt_day => p_rec.strt_day,
921 p_strt_mo => p_rec.strt_mo,
922 p_end_day => p_rec.end_day,
923 p_end_mo => p_rec.end_mo,
924 p_tm_uom => p_rec.tm_uom,
925 p_business_group_id => p_rec.business_group_id);
926 */
927 --
928 --
929 -- Bug 2167540: Added p_yr_perd_id to chk_unique_combination procedure
930 --
931 chk_unique_combination
932 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
933 p_yr_perd_id => p_rec.yr_perd_id,
934 p_strt_day => p_rec.strt_day,
935 p_strt_mo => p_rec.strt_mo,
936 p_end_day => p_rec.end_day,
937 p_end_mo => p_rec.end_mo,
938 p_tm_uom => p_rec.tm_uom,
939 p_business_group_id => p_rec.business_group_id);
940 --
941 chk_tm_uom
942 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
943 p_tm_uom => p_rec.tm_uom,
944 p_effective_date => p_effective_date,
945 p_object_version_number => p_rec.object_version_number);
946 --
947 hr_utility.set_location(' Leaving:'||l_proc, 10);
948 End insert_validate;
949 --
950 -- ----------------------------------------------------------------------------
951 -- |---------------------------< update_validate >----------------------------|
952 -- ----------------------------------------------------------------------------
953 Procedure update_validate(p_rec in ben_wyp_shd.g_rec_type
954 ,p_effective_date in date) is
955 --
956 l_proc varchar2(72) := g_package||'update_validate';
957 --
958 Begin
959 hr_utility.set_location('Entering:'||l_proc, 5);
960 --
961 -- Call all supporting business operations
962 --
963 --
964 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
965 --
966 chk_wthn_yr_perd_id
967 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
968 p_object_version_number => p_rec.object_version_number);
969 --
970 chk_yr_perd_id
971 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
972 p_yr_perd_id => p_rec.yr_perd_id,
973 p_object_version_number => p_rec.object_version_number);
974 --
975
976 -- Bug - 2248735 Check for valid Within Year Period Start and End Dates
977
978 chk_valid_date
979 (p_day => p_rec.strt_day,
980 p_month => p_rec.strt_mo,
981 p_type => 'Start',
982 p_yr_perd_id => p_rec.yr_perd_id,
983 p_business_group_id => p_rec.business_group_id);
984 --
985 chk_valid_date
986 (p_day => p_rec.end_day,
987 p_month => p_rec.end_mo,
988 p_type => 'End',
989 p_yr_perd_id => p_rec.yr_perd_id,
990 p_business_group_id => p_rec.business_group_id);
991 --
992 -- End Bug 2248735
993
994 chk_day_and_month_validation
995 (p_yr_perd_id => p_rec.yr_perd_id,
996 p_strt_day => p_rec.strt_day,
997 p_strt_mo => p_rec.strt_mo,
998 p_end_day => p_rec.end_day,
999 p_end_mo => p_rec.end_mo,
1000 p_business_group_id => p_rec.business_group_id);
1001 --
1002 -- Bug - 2167540 : Commented to allow overlapping of start and end values for CWB.
1003 /* chk_no_overlapping
1004 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
1005 p_yr_perd_id => p_rec.yr_perd_id,
1006 p_strt_day => p_rec.strt_day,
1007 p_strt_mo => p_rec.strt_mo,
1008 p_end_day => p_rec.end_day,
1009 p_end_mo => p_rec.end_mo,
1010 p_tm_uom => p_rec.tm_uom,
1011 p_business_group_id => p_rec.business_group_id);
1012 */
1013 --
1014 --
1015 -- Bug 2167540: Added p_yr_perd_id to chk_unique_combination procedure
1016 --
1017 chk_unique_combination
1018 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
1019 p_yr_perd_id => p_rec.yr_perd_id,
1020 p_strt_day => p_rec.strt_day,
1021 p_strt_mo => p_rec.strt_mo,
1022 p_end_day => p_rec.end_day,
1023 p_end_mo => p_rec.end_mo,
1024 p_tm_uom => p_rec.tm_uom,
1025 p_business_group_id => p_rec.business_group_id);
1026 --
1027 chk_tm_uom
1028 (p_wthn_yr_perd_id => p_rec.wthn_yr_perd_id,
1029 p_tm_uom => p_rec.tm_uom,
1030 p_effective_date => p_effective_date,
1031 p_object_version_number => p_rec.object_version_number);
1032 --
1033 hr_utility.set_location(' Leaving:'||l_proc, 10);
1034 End update_validate;
1035 --
1036 -- ----------------------------------------------------------------------------
1037 -- |---------------------------< delete_validate >----------------------------|
1038 -- ----------------------------------------------------------------------------
1039 Procedure delete_validate(p_rec in ben_wyp_shd.g_rec_type
1040 ,p_effective_date in date) is
1041 --
1042 l_proc varchar2(72) := g_package||'delete_validate';
1043 --
1044 Begin
1045 hr_utility.set_location('Entering:'||l_proc, 5);
1046 --
1047 -- Call all supporting business operations
1048 --
1049 hr_utility.set_location(' Leaving:'||l_proc, 10);
1050 End delete_validate;
1051 --
1052 --
1053 -- ---------------------------------------------------------------------------
1054 -- |---------------------< return_legislation_code >-------------------------|
1055 -- ---------------------------------------------------------------------------
1056 --
1057 function return_legislation_code
1058 (p_wthn_yr_perd_id in number) return varchar2 is
1059 --
1060 -- Declare cursor
1061 --
1062 cursor csr_leg_code is
1063 select a.legislation_code
1064 from per_business_groups a,
1065 ben_wthn_yr_perd b
1066 where b.wthn_yr_perd_id = p_wthn_yr_perd_id
1067 and a.business_group_id = b.business_group_id;
1068 --
1069 -- Declare local variables
1070 --
1071 l_legislation_code varchar2(150);
1072 l_proc varchar2(72) := g_package||'return_legislation_code';
1073 --
1074 begin
1075 --
1076 hr_utility.set_location('Entering:'|| l_proc, 10);
1077 --
1078 -- Ensure that all the mandatory parameter are not null
1079 --
1080 hr_api.mandatory_arg_error(p_api_name => l_proc,
1081 p_argument => 'wthn_yr_perd_id',
1082 p_argument_value => p_wthn_yr_perd_id);
1083 --
1084 open csr_leg_code;
1085 --
1086 fetch csr_leg_code into l_legislation_code;
1087 --
1088 if csr_leg_code%notfound then
1089 --
1090 close csr_leg_code;
1091 --
1092 -- The primary key is invalid therefore we must error
1093 --
1094 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1095 hr_utility.raise_error;
1096 --
1097 end if;
1098 --
1099 close csr_leg_code;
1100 --
1101 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1102 --
1103 return l_legislation_code;
1104 --
1105 end return_legislation_code;
1106 --
1107 end ben_wyp_bus;