[Home] [Help]
PACKAGE BODY: APPS.BEN_BATCH_DT_API
Source
4 /*
1 package body ben_batch_dt_api as
2 /* $Header: bendtapi.pkb 120.1 2011/03/06 10:46:48 pvelvano ship $ */
3 --------------------------------------------------------------------------------
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Comp Object Caching Routine
13 Purpose
14 This package is used to return comp object information.
15 History
19 20 Dec 00 Tmathers 115.1 fixed check_sql errors.
16 Date Who Version What?
17 ---- --- ------- -----
18 15 May 99 mhoyes 115.0 Created
20 22 May 01 mhoyes 115.2 Added batch_validate_bgp_id.
21 18 Sep 03 mhoyes 115.4 3150329 - Update eligibility
22 APIs.
23 */
24 --------------------------------------------------------------------------------
25 --
26 g_package varchar2(30) := 'ben_batch_dt_api.';
27 g_hash_key number := ben_hash_utility.get_hash_key;
28 g_hash_jump number := ben_hash_utility.get_hash_jump;
29 --
30 g_batch_api pls_integer := 0;
31 --
32 procedure Get_DtIns_Start_and_End_Dates
33 (p_effective_date in date
34 ,p_parcolumn_name in varchar2
35 ,p_min_esd in date
36 ,p_max_eed in date
37 --
38 ,p_esd in out nocopy date
39 ,p_eed in out nocopy date
40 )
41 Is
42 --
43 l_proc varchar2(72) := g_package||'Get_DtIns_Start_and_End_Dates';
44 --
45 Begin
46 --
47 -- If the max eed is null or less than the
48 -- effective_date then error because a parental row does NOT exist.
49 --
50 If ( p_max_eed is null
51 or
52 (p_max_eed < p_effective_date)
53 )
54 then
55 --
56 -- The parental rows specified do not exist as of the effective date
57 -- therefore a serious integrity problem has ocurred
58 --
59 hr_utility.set_message(801, 'HR_7423_DT_INVALID_ID');
60 hr_utility.set_message_token('ARGUMENT', upper(p_parcolumn_name));
61 hr_utility.raise_error;
62 --
63 Else
64 --
65 -- The LEAST function will then compare the working l_min_date with the
66 -- returned miniumum effective start date (l_temp_date) and set the
67 -- l_min_date to the maximum of these dates
68 --
69 p_eed := least(p_eed, p_max_eed);
70 --
71 End If;
72 --
73 p_esd := p_effective_date;
74 --
75 End Get_DtIns_Start_and_End_Dates;
76 --
77 procedure set_personobject
78 (p_rec in out NOCOPY gtyp_dtsum_row
79 )
80 is
81 --
82 l_hash_value pls_integer;
83 --
84 begin
85 --
86 -- Get hashed index value
87 --
88 l_hash_value := mod(p_rec.id,g_hash_key);
89 --
90 if g_person_dtsum_odcache(l_hash_value).id = p_rec.id then
91 --
92 null;
93 --
94 else
95 --
96 -- Loop through the hash using the jump routine to check further
97 -- indexes
98 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
99 --
100 l_hash_value := l_hash_value+g_hash_jump;
101 --
102 while g_person_dtsum_odcache(l_hash_value).id <> p_rec.id loop
103 --
104 l_hash_value := l_hash_value+g_hash_jump;
105 end loop;
106 --
107 end if;
108 --
109 exception when NO_DATA_FOUND then
110 -- set cache entry at current index location
111 g_person_dtsum_odcache(l_hash_value):=p_rec;
112 --
113 end set_personobject;
114 --
115 procedure get_personobject
116 (p_person_id in number
117 ,p_rec in out NOCOPY gtyp_dtsum_row
118 )
119 is
120 --
121 l_rec gtyp_dtsum_row;
122 l_odcache_row gtyp_dtsum_row;
123 --
124 l_hash_value pls_integer;
125 l_id number;
126 l_min_esd date;
127 l_max_eed date;
128 --
129 cursor c1
130 (c_id in number
131 )
132 is
133 select person_id id,
134 min(effective_start_date) min_esd,
135 max(effective_end_date) max_eed
136 from per_all_people_f
137 where person_id = c_id
138 group by person_id;
139 --
140 begin
141 --
142 -- Check for a match in the current row cache
143 --
144 if g_lastperson_dtsum_row.id = p_person_id then
145 --
146 p_rec := g_lastperson_dtsum_row;
147 return;
148 --
149 end if;
150 --
151 -- Get hashed index value
152 --
153 l_hash_value := mod(p_person_id,g_hash_key);
154 --
155 l_odcache_row := g_person_dtsum_odcache(l_hash_value);
156 --
157 if l_odcache_row.id = p_person_id then
158 --
159 -- Set the current row cache
160 --
161 g_lastperson_dtsum_row := l_odcache_row;
162 p_rec := l_odcache_row;
163 --
164 else
165 --
166 -- We need to loop through all the hashed indexes
167 -- if none exists at current index the NO_DATA_FOUND expection will fire
168 --
169 l_hash_value := l_hash_value+g_hash_jump;
170 while g_person_dtsum_odcache(l_hash_value).id <> p_person_id loop
171 --
172 l_hash_value := l_hash_value+g_hash_jump;
173 --
174 end loop;
175 --
176 l_odcache_row := g_person_dtsum_odcache(l_hash_value);
177 --
178 g_lastperson_dtsum_row := l_odcache_row;
179 p_rec := l_odcache_row;
180 --
181 end if;
182 --
183 exception
184 --
185 when no_data_found then
186 --
187 open c1
188 (c_id => p_person_id
189 );
190 fetch c1 into l_rec;
191 if c1%notfound then
195 fnd_message.set_token('PROC','ben_batch_dt_api.get_personobject');
192 --
193 close c1;
194 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
196 fnd_message.set_token('person',p_person_id);
197 fnd_message.raise_error;
198 --
199 end if;
200 --
201 close c1;
202 --
203 -- Add details to the cache
204 --
205 set_personobject
206 (p_rec => l_rec
207 );
208 --
209 g_lastperson_dtsum_row := l_rec;
210 p_rec := l_rec;
211 --
212 end get_personobject;
213 --
214 procedure set_lerobject
215 (p_rec in out NOCOPY gtyp_dtsum_row
216 )
217 is
218 --
219 l_hash_value pls_integer;
220 --
221 begin
222 --
223 -- Get hashed index value
224 --
225 l_hash_value := mod(p_rec.id,g_hash_key);
226 --
227 if g_ler_dtsum_odcache(l_hash_value).id = p_rec.id then
228 --
229 null;
230 --
231 else
232 --
233 -- Loop through the hash using the jump routine to check further
234 -- indexes
235 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
236 --
237 l_hash_value := l_hash_value+g_hash_jump;
238 --
239 while g_ler_dtsum_odcache(l_hash_value).id <> p_rec.id loop
240 --
241 l_hash_value := l_hash_value+g_hash_jump;
242 end loop;
243 --
244 end if;
245 --
246 exception when NO_DATA_FOUND then
247 -- set cache entry at current index location
248 g_ler_dtsum_odcache(l_hash_value):=p_rec;
249 --
250 end set_lerobject;
251 --
252 procedure get_lerobject
253 (p_ler_id in number
254 ,p_rec in out NOCOPY gtyp_dtsum_row
255 )
256 is
257 --
258 l_rec gtyp_dtsum_row;
259 l_odcache_row gtyp_dtsum_row;
260 --
261 l_hash_value pls_integer;
262 l_id number;
263 l_min_esd date;
264 l_max_eed date;
265 --
266 cursor c1
267 (c_id in number
268 )
269 is
270 select ler_id id,
271 min(effective_start_date) min_esd,
272 max(effective_end_date) max_eed
273 from ben_ler_f
274 where ler_id = c_id
275 group by ler_id;
276 --
277 begin
278 --
279 -- Check for a match in the current row cache
280 --
281 if g_lastler_dtsum_row.id = p_ler_id then
282 --
283 p_rec := g_lastler_dtsum_row;
284 return;
285 --
286 end if;
287 --
288 -- Get hashed index value
289 --
290 l_hash_value := mod(p_ler_id,g_hash_key);
291 --
292 l_odcache_row := g_ler_dtsum_odcache(l_hash_value);
293 --
294 if l_odcache_row.id = p_ler_id then
295 --
296 -- Set the current row cache
297 --
298 g_lastler_dtsum_row := l_odcache_row;
299 p_rec := l_odcache_row;
300 --
301 else
302 --
303 -- We need to loop through all the hashed indexes
304 -- if none exists at current index the NO_DATA_FOUND expection will fire
305 --
306 l_hash_value := l_hash_value+g_hash_jump;
307 while g_ler_dtsum_odcache(l_hash_value).id <> p_ler_id loop
308 --
309 l_hash_value := l_hash_value+g_hash_jump;
310 --
311 end loop;
312 --
313 l_odcache_row := g_ler_dtsum_odcache(l_hash_value);
314 --
315 g_lastler_dtsum_row := l_odcache_row;
316 p_rec := l_odcache_row;
317 --
318 end if;
319 --
320 exception
321 --
322 when no_data_found then
323 --
324 open c1
325 (c_id => p_ler_id
326 );
327 fetch c1 into l_rec;
328 if c1%notfound then
329 --
330 close c1;
331 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
332 fnd_message.set_token('PROC','ben_batch_dt_api.get_lerobject');
333 fnd_message.set_token('ler',p_ler_id);
334 fnd_message.raise_error;
335 --
336 end if;
337 --
338 close c1;
339 --
340 -- Add details to the cache
341 --
342 set_lerobject
343 (p_rec => l_rec
344 );
345 --
346 g_lastler_dtsum_row := l_rec;
347 p_rec := l_rec;
348 --
349 end get_lerobject;
350 --
351 procedure set_pgmobject
352 (p_rec in out NOCOPY gtyp_dtsum_row
353 )
354 is
355 --
356 l_hash_value pls_integer;
357 --
358 begin
359 --
360 -- Get hashed index value
361 --
362 l_hash_value := mod(p_rec.id,g_hash_key);
363 --
364 if g_pgm_dtsum_odcache(l_hash_value).id = p_rec.id then
365 --
366 null;
367 --
368 else
369 --
370 -- Loop through the hash using the jump routine to check further
371 -- indexes
372 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
373 --
374 l_hash_value := l_hash_value+g_hash_jump;
375 --
376 while g_pgm_dtsum_odcache(l_hash_value).id <> p_rec.id loop
377 --
378 l_hash_value := l_hash_value+g_hash_jump;
379 end loop;
380 --
381 end if;
382 --
383 exception when NO_DATA_FOUND then
384 -- set cache entry at current index location
385 g_pgm_dtsum_odcache(l_hash_value):=p_rec;
386 --
387 end set_pgmobject;
388 --
392 )
389 procedure get_pgmobject
390 (p_pgm_id in number
391 ,p_rec in out NOCOPY gtyp_dtsum_row
393 is
394 --
395 l_rec gtyp_dtsum_row;
396 l_odcache_row gtyp_dtsum_row;
397 --
398 l_hash_value pls_integer;
399 l_id number;
400 l_min_esd date;
401 l_max_eed date;
402 --
403 cursor c1
404 (c_id in number
405 )
406 is
407 select pgm_id id,
408 min(effective_start_date) min_esd,
409 max(effective_end_date) max_eed
410 from ben_pgm_f
411 where pgm_id = c_id
412 group by pgm_id;
413 --
414 begin
415 --
416 -- Check for a match in the current row cache
417 --
418 if g_lastpgm_dtsum_row.id = p_pgm_id then
419 --
420 p_rec := g_lastpgm_dtsum_row;
421 return;
422 --
423 end if;
424 --
425 -- Get hashed index value
426 --
427 l_hash_value := mod(p_pgm_id,g_hash_key);
428 --
429 l_odcache_row := g_pgm_dtsum_odcache(l_hash_value);
430 --
431 if l_odcache_row.id = p_pgm_id then
432 --
433 -- Set the current row cache
434 --
435 g_lastpgm_dtsum_row := l_odcache_row;
436 p_rec := l_odcache_row;
437 --
438 else
439 --
440 -- We need to loop through all the hashed indexes
441 -- if none exists at current index the NO_DATA_FOUND expection will fire
442 --
443 l_hash_value := l_hash_value+g_hash_jump;
444 while g_pgm_dtsum_odcache(l_hash_value).id <> p_pgm_id loop
445 --
446 l_hash_value := l_hash_value+g_hash_jump;
447 --
448 end loop;
449 --
450 l_odcache_row := g_pgm_dtsum_odcache(l_hash_value);
451 --
452 g_lastpgm_dtsum_row := l_odcache_row;
453 p_rec := l_odcache_row;
454 --
455 end if;
456 --
457 exception
458 --
459 when no_data_found then
460 --
461 open c1
462 (c_id => p_pgm_id
463 );
464 fetch c1 into l_rec;
465 if c1%notfound then
466 --
467 close c1;
468 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
469 fnd_message.set_token('PROC','ben_batch_dt_api.get_pgmobject');
470 fnd_message.set_token('PGM',p_pgm_id);
471 fnd_message.raise_error;
472 --
473 end if;
474 --
475 close c1;
476 --
477 -- Add details to the cache
478 --
479 set_pgmobject
480 (p_rec => l_rec
481 );
482 --
483 g_lastpgm_dtsum_row := l_rec;
484 p_rec := l_rec;
485 --
486 end get_pgmobject;
487 --
488 procedure set_ptipobject
489 (p_rec in out NOCOPY gtyp_dtsum_row
490 )
491 is
492 --
493 l_hash_value pls_integer;
494 --
495 begin
496 --
497 -- Get hashed index value
498 --
499 l_hash_value := mod(p_rec.id,g_hash_key);
500 --
501 if g_ptip_dtsum_odcache(l_hash_value).id = p_rec.id then
502 --
503 null;
504 --
505 else
506 --
507 -- Loop through the hash using the jump routine to check further
508 -- indexes
509 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
510 --
511 l_hash_value := l_hash_value+g_hash_jump;
512 --
513 while g_ptip_dtsum_odcache(l_hash_value).id <> p_rec.id loop
514 --
515 l_hash_value := l_hash_value+g_hash_jump;
516 end loop;
517 --
518 end if;
519 --
520 exception when NO_DATA_FOUND then
521 -- set cache entry at current index location
522 g_ptip_dtsum_odcache(l_hash_value):=p_rec;
523 --
524 end set_ptipobject;
525 --
526 procedure get_ptipobject
527 (p_ptip_id in number
528 ,p_rec in out NOCOPY gtyp_dtsum_row
529 )
530 is
531 --
532 l_rec gtyp_dtsum_row;
533 l_odcache_row gtyp_dtsum_row;
534 --
535 l_hash_value pls_integer;
536 l_id number;
537 l_min_esd date;
538 l_max_eed date;
539 --
540 cursor c1
541 (c_id in number
542 )
543 is
544 select ptip_id id,
545 min(effective_start_date) min_esd,
546 max(effective_end_date) max_eed
547 from ben_ptip_f
548 where ptip_id = c_id
549 group by ptip_id;
550 --
551 begin
552 --
553 -- Check for a match in the current row cache
554 --
555 if g_lastptip_dtsum_row.id = p_ptip_id then
556 --
557 p_rec := g_lastptip_dtsum_row;
558 return;
559 --
560 end if;
561 --
562 -- Get hashed index value
563 --
564 l_hash_value := mod(p_ptip_id,g_hash_key);
565 --
566 l_odcache_row := g_ptip_dtsum_odcache(l_hash_value);
567 --
568 if l_odcache_row.id = p_ptip_id then
569 --
570 -- Set the current row cache
571 --
572 g_lastptip_dtsum_row := l_odcache_row;
573 p_rec := l_odcache_row;
574 --
575 else
576 --
577 -- We need to loop through all the hashed indexes
578 -- if none exists at current index the NO_DATA_FOUND expection will fire
579 --
580 l_hash_value := l_hash_value+g_hash_jump;
581 while g_ptip_dtsum_odcache(l_hash_value).id <> p_ptip_id loop
582 --
583 l_hash_value := l_hash_value+g_hash_jump;
584 --
585 end loop;
586 --
587 l_odcache_row := g_ptip_dtsum_odcache(l_hash_value);
588 --
589 g_lastptip_dtsum_row := l_odcache_row;
590 p_rec := l_odcache_row;
591 --
592 end if;
593 --
594 exception
595 --
596 when no_data_found then
597 --
598 open c1
599 (c_id => p_ptip_id
600 );
601 fetch c1 into l_rec;
602 if c1%notfound then
603 --
604 close c1;
605 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
606 fnd_message.set_token('PROC','ben_batch_dt_api.get_ptipobject');
607 fnd_message.set_token('ptip',p_ptip_id);
608 fnd_message.raise_error;
609 --
610 end if;
611 --
612 close c1;
613 --
614 -- Add details to the cache
615 --
616 set_ptipobject
617 (p_rec => l_rec
618 );
619 --
620 g_lastptip_dtsum_row := l_rec;
621 p_rec := l_rec;
622 --
623 end get_ptipobject;
624 --
625 procedure set_plipobject
626 (p_rec in out NOCOPY gtyp_dtsum_row
627 )
628 is
629 --
630 l_hash_value pls_integer;
631 --
632 begin
633 --
634 -- Get hashed index value
635 --
636 l_hash_value := mod(p_rec.id,g_hash_key);
637 --
638 if g_plip_dtsum_odcache(l_hash_value).id = p_rec.id then
639 --
640 null;
641 --
642 else
643 --
644 -- Loop through the hash using the jump routine to check further
645 -- indexes
646 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
647 --
648 l_hash_value := l_hash_value+g_hash_jump;
649 --
650 while g_plip_dtsum_odcache(l_hash_value).id <> p_rec.id loop
651 --
652 l_hash_value := l_hash_value+g_hash_jump;
653 end loop;
654 --
655 end if;
656 --
657 exception when NO_DATA_FOUND then
658 -- set cache entry at current index location
659 g_plip_dtsum_odcache(l_hash_value):=p_rec;
660 --
661 end set_plipobject;
662 --
663 procedure get_plipobject
664 (p_plip_id in number
665 ,p_rec in out NOCOPY gtyp_dtsum_row
666 )
667 is
668 --
669 l_rec gtyp_dtsum_row;
670 l_odcache_row gtyp_dtsum_row;
671 --
672 l_hash_value pls_integer;
673 l_id number;
674 l_min_esd date;
675 l_max_eed date;
676 --
677 cursor c1
678 (c_id in number
679 )
680 is
681 select plip_id id,
682 min(effective_start_date) min_esd,
683 max(effective_end_date) max_eed
684 from ben_plip_f
685 where plip_id = c_id
686 group by plip_id;
687 --
688 begin
689 --
690 -- Check for a match in the current row cache
691 --
692 if g_lastplip_dtsum_row.id = p_plip_id then
693 --
694 p_rec := g_lastplip_dtsum_row;
695 return;
696 --
697 end if;
698 --
699 -- Get hashed index value
700 --
701 l_hash_value := mod(p_plip_id,g_hash_key);
702 --
703 l_odcache_row := g_plip_dtsum_odcache(l_hash_value);
704 --
705 if l_odcache_row.id = p_plip_id then
706 --
707 -- Set the current row cache
708 --
709 g_lastplip_dtsum_row := l_odcache_row;
710 p_rec := l_odcache_row;
711 --
712 else
713 --
714 -- We need to loop through all the hashed indexes
715 -- if none exists at current index the NO_DATA_FOUND expection will fire
716 --
717 l_hash_value := l_hash_value+g_hash_jump;
718 while g_plip_dtsum_odcache(l_hash_value).id <> p_plip_id loop
719 --
720 l_hash_value := l_hash_value+g_hash_jump;
721 --
722 end loop;
723 --
724 l_odcache_row := g_plip_dtsum_odcache(l_hash_value);
725 --
726 g_lastplip_dtsum_row := l_odcache_row;
727 p_rec := l_odcache_row;
728 --
729 end if;
730 --
731 exception
732 --
733 when no_data_found then
734 --
735 open c1
736 (c_id => p_plip_id
737 );
738 fetch c1 into l_rec;
739 if c1%notfound then
740 --
741 close c1;
742 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
743 fnd_message.set_token('PROC','ben_batch_dt_api.get_plipobject');
744 fnd_message.set_token('plip',p_plip_id);
745 fnd_message.raise_error;
746 --
747 end if;
748 --
749 close c1;
750 --
751 -- Add details to the cache
752 --
753 set_plipobject
754 (p_rec => l_rec
755 );
756 --
757 g_lastplip_dtsum_row := l_rec;
758 p_rec := l_rec;
759 --
760 end get_plipobject;
761 --
762 procedure set_plobject
763 (p_rec in out NOCOPY gtyp_dtsum_row
764 )
765 is
766 --
767 l_hash_value pls_integer;
768 --
769 begin
770 --
771 -- Get hashed index value
772 --
773 l_hash_value := mod(p_rec.id,g_hash_key);
774 --
775 if g_pl_dtsum_odcache(l_hash_value).id = p_rec.id then
776 --
777 null;
778 --
779 else
780 --
781 -- Loop through the hash using the jump routine to check further
782 -- indexes
783 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
784 --
785 l_hash_value := l_hash_value+g_hash_jump;
786 --
787 while g_pl_dtsum_odcache(l_hash_value).id <> p_rec.id loop
788 --
789 l_hash_value := l_hash_value+g_hash_jump;
790 end loop;
791 --
792 end if;
793 --
794 exception when NO_DATA_FOUND then
795 -- set cache entry at current index location
796 g_pl_dtsum_odcache(l_hash_value):=p_rec;
797 --
798 end set_plobject;
799 --
800 procedure get_plobject
801 (p_pl_id in number
802 ,p_rec in out NOCOPY gtyp_dtsum_row
803 )
804 is
805 --
806 l_rec gtyp_dtsum_row;
807 l_odcache_row gtyp_dtsum_row;
808 --
809 l_hash_value pls_integer;
810 l_id number;
811 l_min_esd date;
812 l_max_eed date;
813 --
814 cursor c1
815 (c_id in number
816 )
817 is
818 select pl_id id,
819 min(effective_start_date) min_esd,
820 max(effective_end_date) max_eed
821 from ben_pl_f
822 where pl_id = c_id
823 group by pl_id;
824 --
825 begin
826 --
827 -- Check for a match in the current row cache
828 --
829 if g_lastpl_dtsum_row.id = p_pl_id then
830 --
831 p_rec := g_lastpl_dtsum_row;
832 return;
833 --
834 end if;
835 --
836 -- Get hashed index value
837 --
838 l_hash_value := mod(p_pl_id,g_hash_key);
839 --
840 l_odcache_row := g_pl_dtsum_odcache(l_hash_value);
841 --
842 if l_odcache_row.id = p_pl_id then
843 --
844 -- Set the current row cache
845 --
846 g_lastpl_dtsum_row := l_odcache_row;
847 p_rec := l_odcache_row;
848 --
849 else
850 --
851 -- We need to loop through all the hashed indexes
852 -- if none exists at current index the NO_DATA_FOUND expection will fire
853 --
854 l_hash_value := l_hash_value+g_hash_jump;
855 while g_pl_dtsum_odcache(l_hash_value).id <> p_pl_id loop
856 --
857 l_hash_value := l_hash_value+g_hash_jump;
858 --
859 end loop;
860 --
861 l_odcache_row := g_pl_dtsum_odcache(l_hash_value);
862 --
863 g_lastpl_dtsum_row := l_odcache_row;
864 p_rec := l_odcache_row;
865 --
866 end if;
867 --
868 exception
869 --
870 when no_data_found then
871 --
872 open c1
873 (c_id => p_pl_id
874 );
875 fetch c1 into l_rec;
876 if c1%notfound then
877 --
878 close c1;
879 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
880 fnd_message.set_token('PROC','ben_batch_dt_api.get_plobject');
881 fnd_message.set_token('pl',p_pl_id);
882 fnd_message.raise_error;
883 --
884 end if;
885 --
886 close c1;
887 --
888 -- Add details to the cache
889 --
890 set_plobject
891 (p_rec => l_rec
892 );
893 --
894 g_lastpl_dtsum_row := l_rec;
895 p_rec := l_rec;
896 --
897 end get_plobject;
898 --
899 procedure set_elig_perobject
900 (p_rec in out NOCOPY gtyp_dtsum_row
901 )
902 is
903 --
904 l_hash_value pls_integer;
905 --
906 begin
907 --
908 -- Get hashed index value
909 --
910 l_hash_value := mod(p_rec.id,g_hash_key);
911 --
912 if g_elig_per_dtsum_odcache(l_hash_value).id = p_rec.id then
913 --
914 null;
915 --
916 else
917 --
918 -- Loop through the hash using the jump routine to check further
919 -- indexes
920 -- 115.10 if none exists at current index the NO_DATA_FOUND expection will fire
921 --
922 l_hash_value := l_hash_value+g_hash_jump;
923 --
924 while g_elig_per_dtsum_odcache(l_hash_value).id <> p_rec.id loop
925 --
926 l_hash_value := l_hash_value+g_hash_jump;
927 end loop;
928 --
929 end if;
930 --
931 exception when NO_DATA_FOUND then
932 -- set cache entry at current index location
933 g_elig_per_dtsum_odcache(l_hash_value):=p_rec;
934 --
935 end set_elig_perobject;
936 --
937 procedure get_elig_perobject
938 (p_elig_per_id in number
939 ,p_rec in out NOCOPY gtyp_dtsum_row
940 )
941 is
942 --
943 l_rec gtyp_dtsum_row;
944 l_odcache_row gtyp_dtsum_row;
945 --
946 l_hash_value pls_integer;
947 l_id number;
948 l_min_esd date;
949 l_max_eed date;
950 --
951 cursor c1
952 (c_id in number
953 )
954 is
955 select elig_per_id id,
956 min(effective_start_date) min_esd,
957 max(effective_end_date) max_eed
958 from ben_elig_per_f
959 where elig_per_id = c_id
960 group by elig_per_id;
961 --
962 begin
963 --
964 -- Check for a match in the current row cache
965 --
966 if g_lastelig_per_dtsum_row.id = p_elig_per_id then
967 --
968 p_rec := g_lastelig_per_dtsum_row;
969 return;
970 --
971 end if;
972 --
973 -- Get hashed index value
974 --
975 l_hash_value := mod(p_elig_per_id,g_hash_key);
976 --
977 l_odcache_row := g_elig_per_dtsum_odcache(l_hash_value);
978 --
979 if l_odcache_row.id = p_elig_per_id then
980 --
981 -- Set the current row cache
982 --
983 g_lastelig_per_dtsum_row := l_odcache_row;
984 p_rec := l_odcache_row;
985 --
986 else
987 --
988 -- We need to loop through all the hashed indexes
989 -- if none exists at current index the NO_DATA_FOUND expection will fire
990 --
991 l_hash_value := l_hash_value+g_hash_jump;
992 while g_elig_per_dtsum_odcache(l_hash_value).id <> p_elig_per_id loop
993 --
994 l_hash_value := l_hash_value+g_hash_jump;
995 --
996 end loop;
997 --
998 l_odcache_row := g_elig_per_dtsum_odcache(l_hash_value);
999 --
1000 g_lastelig_per_dtsum_row := l_odcache_row;
1001 p_rec := l_odcache_row;
1002 --
1003 end if;
1004 --
1005 exception
1006 --
1007 when no_data_found then
1008 --
1009 open c1
1010 (c_id => p_elig_per_id
1011 );
1012 fetch c1 into l_rec;
1013 if c1%notfound then
1014 --
1015 close c1;
1016 fnd_message.set_name('BEN','BEN_92204_OBJECT_NOT_FOUND');
1017 fnd_message.set_token('PROC','ben_batch_dt_api.get_elig_perobject');
1018 fnd_message.set_token('elig_per',p_elig_per_id);
1019 fnd_message.raise_error;
1020 --
1021 end if;
1022 --
1023 close c1;
1024 --
1025 -- Add details to the cache
1026 --
1027 set_elig_perobject
1028 (p_rec => l_rec
1029 );
1030 --
1031 g_lastelig_per_dtsum_row := l_rec;
1032 p_rec := l_rec;
1033 --
1034 end get_elig_perobject;
1035 --
1036 procedure clear_down_cache is
1037 --
1038 l_person_rec gtyp_dtsum_row;
1039 l_ler_rec gtyp_dtsum_row;
1040 l_pgm_rec gtyp_dtsum_row;
1041 l_ptip_rec gtyp_dtsum_row;
1042 l_plip_rec gtyp_dtsum_row;
1043 l_pl_rec gtyp_dtsum_row;
1044 l_elig_per_rec gtyp_dtsum_row;
1045 --
1046 begin
1047 --
1048 g_person_dtsum_odcache.delete;
1049 g_lastperson_dtsum_row := l_person_rec;
1050 --
1051 g_ler_dtsum_odcache.delete;
1052 g_lastler_dtsum_row := l_ler_rec;
1053 --
1054 g_pgm_dtsum_odcache.delete;
1055 g_lastpgm_dtsum_row := l_pgm_rec;
1056 --
1057 g_ptip_dtsum_odcache.delete;
1058 g_lastptip_dtsum_row := l_ptip_rec;
1059 --
1060 g_plip_dtsum_odcache.delete;
1061 g_lastplip_dtsum_row := l_plip_rec;
1062 --
1063 g_pl_dtsum_odcache.delete;
1064 g_lastpl_dtsum_row := l_pl_rec;
1065 --
1066 g_elig_per_dtsum_odcache.delete;
1067 g_lastelig_per_dtsum_row := l_elig_per_rec;
1068 --
1069 g_batch_api := 1;
1070 --
1071 end clear_down_cache;
1072 --
1073 procedure batch_validate_bgp_id
1074 (p_business_group_id in number
1075 )
1076 is
1077 --
1078
1079 --
1080 begin
1081 --
1082 if g_batch_api = 0 then
1083 --
1084 hr_api.validate_bus_grp_id
1085 (p_business_group_id
1086 );
1087 --
1088 end if;
1089 --
1090 end batch_validate_bgp_id;
1091 --
1092 procedure validate_dt_mode_insert
1093 (p_effective_date in date
1094 ,p_person_id in number default null
1095 ,p_ler_id in number default null
1096 ,p_pgm_id in number default null
1097 ,p_ptip_id in number default null
1098 ,p_plip_id in number default null
1099 ,p_pl_id in number default null
1100 --
1101 ,p_effective_start_date in out nocopy date
1102 ,p_effective_end_date in out nocopy date
1103 )
1104 is
1105 --
1106 l_minmax_rec ben_batch_dt_api.gtyp_dtsum_row;
1107 --
1108 l_effective_start_date date;
1109 l_effective_end_date date;
1110 --
1111 begin
1112 --
1113 l_effective_start_date := p_effective_date;
1114 l_effective_end_date := hr_api.g_eot;
1115 --
1116 -- Person
1117 --
1118 ben_batch_dt_api.get_personobject
1119 (p_person_id => p_person_id
1120 ,p_rec => l_minmax_rec
1121 );
1122 --
1123 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
1124 (p_effective_date => p_effective_date
1125 ,p_parcolumn_name => 'person_id'
1126 ,p_min_esd => l_minmax_rec.min_esd
1127 ,p_max_eed => l_minmax_rec.max_eed
1128 --
1129 ,p_esd => l_effective_start_date
1130 ,p_eed => l_effective_end_date
1131 );
1132 --
1133 if p_ler_id is not null then
1134 --
1135 ben_batch_dt_api.get_lerobject
1136 (p_ler_id => p_ler_id
1137 ,p_rec => l_minmax_rec
1138 );
1139 --
1140 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
1141 (p_effective_date => p_effective_date
1142 ,p_parcolumn_name => 'ler_id'
1143 ,p_min_esd => l_minmax_rec.min_esd
1144 ,p_max_eed => l_minmax_rec.max_eed
1145 --
1146 ,p_esd => l_effective_start_date
1147 ,p_eed => l_effective_end_date
1148 );
1149 --
1150 end if;
1151 --
1152 -- Pgm
1153 --
1154 if p_pgm_id is not null then
1155 --
1156 ben_batch_dt_api.get_pgmobject
1157 (p_pgm_id => p_pgm_id
1158 ,p_rec => l_minmax_rec
1159 );
1160 --
1161 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
1162 (p_effective_date => p_effective_date
1163 ,p_parcolumn_name => 'pgm_id'
1164 ,p_min_esd => l_minmax_rec.min_esd
1165 ,p_max_eed => l_minmax_rec.max_eed
1166 --
1167 ,p_esd => l_effective_start_date
1168 ,p_eed => l_effective_end_date
1169 );
1170 --
1171 end if;
1172 --
1173 -- Ptip
1174 --
1175 if p_ptip_id is not null then
1176 --
1177 ben_batch_dt_api.get_ptipobject
1178 (p_ptip_id => p_ptip_id
1179 ,p_rec => l_minmax_rec
1180 );
1181 --
1182 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
1183 (p_effective_date => p_effective_date
1184 ,p_parcolumn_name => 'ptip_id'
1185 ,p_min_esd => l_minmax_rec.min_esd
1186 ,p_max_eed => l_minmax_rec.max_eed
1187 --
1188 ,p_esd => l_effective_start_date
1189 ,p_eed => l_effective_end_date
1190 );
1191 --
1192 end if;
1193 --
1194 -- Plip
1195 --
1196 if p_plip_id is not null then
1197 --
1198 ben_batch_dt_api.get_plipobject
1199 (p_plip_id => p_plip_id
1200 ,p_rec => l_minmax_rec
1201 );
1202 --
1203 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
1204 (p_effective_date => p_effective_date
1205 ,p_parcolumn_name => 'plip_id'
1206 ,p_min_esd => l_minmax_rec.min_esd
1207 ,p_max_eed => l_minmax_rec.max_eed
1208 --
1209 ,p_esd => l_effective_start_date
1210 ,p_eed => l_effective_end_date
1211 );
1212 --
1213 end if;
1214 --
1215 -- Plan
1216 --
1217 if p_pl_id is not null then
1218 --
1219 ben_batch_dt_api.get_plobject
1220 (p_pl_id => p_pl_id
1221 ,p_rec => l_minmax_rec
1222 );
1223 --
1224 ben_batch_dt_api.Get_DtIns_Start_and_End_Dates
1225 (p_effective_date => p_effective_date
1226 ,p_parcolumn_name => 'pl_id'
1227 ,p_min_esd => l_minmax_rec.min_esd
1228 ,p_max_eed => l_minmax_rec.max_eed
1229 --
1230 ,p_esd => l_effective_start_date
1231 ,p_eed => l_effective_end_date
1232 );
1233 --
1234 end if;
1235 --
1236 p_effective_start_date := l_effective_start_date;
1237 p_effective_end_date := l_effective_end_date;
1238 --
1239 end validate_dt_mode_insert;
1240 --
1241 PROCEDURE return_effective_dates
1242 (p_base_table_name IN varchar2
1243 ,p_effective_date IN DATE
1244 ,p_base_key_value IN NUMBER
1245 --
1246 ,p_effective_start_date in out nocopy date
1247 ,p_effective_end_date in out nocopy date
1248 )
1249 IS
1250 --
1251 l_proc VARCHAR2(72) := g_package||'return_effective_dates';
1252 --
1253 cursor c_effdates
1254 (c_eff_date date
1255 ,c_pep_id number
1256 )
1257 is
1258 select pep.effective_start_date,
1259 pep.effective_end_date
1260 from ben_elig_per_f pep
1261 where pep.elig_per_id = c_pep_id
1262 and c_eff_date
1263 between pep.effective_start_date and pep.effective_end_date;
1264 --
1265 cursor c_epoeffdates
1266 (c_eff_date date
1267 ,c_epo_id number
1268 )
1269 is
1270 select epo.effective_start_date,
1271 epo.effective_end_date
1272 from ben_elig_per_opt_f epo
1273 where epo.elig_per_opt_id = c_epo_id
1274 and c_eff_date
1275 between epo.effective_start_date and epo.effective_end_date;
1276 --
1277 BEGIN
1278 hr_utility.set_location('Entering:'||l_proc, 5);
1279 --
1280 p_effective_start_date := null;
1281 p_effective_end_date := null;
1282 --
1286 open c_effdates
1283 if p_base_table_name = 'BEN_ELIG_PER_F'
1284 then
1285 --
1287 (c_eff_date => p_effective_date
1288 ,c_pep_id => p_base_key_value
1289 );
1290 fetch c_effdates into p_effective_start_date, p_effective_end_date;
1291 close c_effdates;
1292 --
1293 elsif p_base_table_name = 'BEN_ELIG_PER_OPT_F'
1294 then
1295 --
1296 open c_epoeffdates
1297 (c_eff_date => p_effective_date
1298 ,c_epo_id => p_base_key_value
1299 );
1300 fetch c_epoeffdates into p_effective_start_date, p_effective_end_date;
1301 close c_epoeffdates;
1302 --
1303 end if;
1304 hr_utility.set_location('Leaving :'||l_proc, 45);
1305 EXCEPTION
1306 WHEN NO_DATA_FOUND THEN
1307 -- As no rows were returned we must error
1308 hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
1309 hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
1310 hr_utility.set_message_token
1311 ('SESSION_DATE'
1312 ,fnd_date.date_to_chardate(p_effective_date,calendar_aware=>2)
1313 );
1314 hr_utility.raise_error;
1315 WHEN TOO_MANY_ROWS THEN
1316 hr_utility.set_message(801, 'HR_7181_DT_OVERLAP_ROWS');
1317 hr_utility.set_message_token('TABLE_NAME', p_base_table_name);
1318 hr_utility.set_message_token
1319 ('SESSION_DATE'
1320 ,fnd_date.date_to_chardate(p_effective_date,calendar_aware=>2)
1321 );
1322 hr_utility.set_message_token('PRIMARY_VALUE', to_char(p_base_key_value));
1323 hr_utility.raise_error;
1324 WHEN OTHERS THEN
1325 RAISE;
1326 --
1327 END return_effective_dates;
1328 --
1329 -- ----------------------------------------------------------------------------
1330 -- |------------------------< Return_Max_End_Date >---------------------------|
1331 -- ----------------------------------------------------------------------------
1332 --
1333 FUNCTION return_max_end_date
1334 (p_base_table_name IN varchar2
1335 ,p_base_key_value IN NUMBER
1336 )
1337 RETURN DATE
1338 IS
1339 --
1340 l_proc VARCHAR2(72) := g_package||'return_max_end_date';
1341 l_max_date DATE;
1342 --
1343 cursor c_maxedate
1344 (c_id number
1345 )
1346 is
1347 select max(pep.effective_end_date)
1348 from ben_elig_per_f pep
1349 where pep.elig_per_id = c_id;
1350 --
1351 cursor c_epomaxedate
1352 (c_id number
1353 )
1354 is
1355 select max(epo.effective_end_date)
1356 from ben_elig_per_opt_f epo
1357 where epo.elig_per_opt_id = c_id;
1358 --
1359 BEGIN
1360 hr_utility.set_location('Entering:'||l_proc, 5);
1361 --
1362 if p_base_table_name = 'BEN_ELIG_PER_F'
1363 then
1364 --
1365 open c_maxedate
1366 (c_id => p_base_key_value
1367 );
1368 fetch c_maxedate into l_max_date;
1369 close c_maxedate;
1370 --
1371 elsif p_base_table_name = 'BEN_ELIG_PER_OPT_F'
1372 then
1373 --
1374 open c_epomaxedate
1375 (c_id => p_base_key_value
1376 );
1377 fetch c_epomaxedate into l_max_date;
1378 close c_epomaxedate;
1379 --
1380 end if;
1381 hr_utility.set_location('Leaving :'||l_proc, 10);
1382 RETURN(l_max_date);
1383 --
1384 END return_max_end_date;
1385 -- ----------------------------------------------------------------------------
1386 -- |-------------------------< Future_Rows_Exists >---------------------------|
1387 -- ----------------------------------------------------------------------------
1388 --
1389 Function Future_Rows_Exist
1390 (p_base_table_name IN varchar2
1391 ,p_effective_date in date
1392 ,p_base_key_value in number
1393 )
1394 return Boolean
1395 Is
1396 --
1397 l_proc varchar2(72) := g_package||'Future_Rows_Exist';
1398 l_boolean boolean := false;
1399 l_dummy_esd date; -- Not required
1400 l_effective_end_date date; -- Current effective end date
1401 l_max_effective_end_date date; -- Maximum effective end date
1402 --
1403 Begin
1404 Hr_Utility.Set_Location('Entering:'||l_proc, 5);
1405 --
1406 -- Must ensure that a row exists as of the effective date supplied
1407 -- and we need the current effective end date
1408 --
1409 ben_batch_dt_api.return_effective_dates
1410 (p_base_table_name => p_base_table_name
1411 ,p_effective_date => p_effective_date
1412 ,p_base_key_value => p_base_key_value
1413 ,p_effective_start_date => l_dummy_esd
1414 ,p_effective_end_date => l_effective_end_date
1415 );
1416 --
1417 -- We must select the maximum effective end date for the datetracked
1418 -- rows
1419 --
1420 l_max_effective_end_date :=
1421 ben_batch_dt_api.return_max_end_date
1422 (p_base_table_name => p_base_table_name
1423 ,p_base_key_value => p_base_key_value
1424 );
1425 --
1426 -- If the maximum effective end date is greater than the current effective
1427 -- end date then future rows exist
1428 --
1429 If (l_max_effective_end_date > l_effective_end_date) then
1430 l_boolean := TRUE;
1431 End If;
1432 --
1433 Hr_Utility.Set_Location('Leaving :'||l_proc, 15);
1434 Return(l_boolean);
1435 --
1436 End Future_Rows_Exist;
1437 --
1438 procedure validate_dt_mode_pep
1439 (p_effective_date in date
1440 ,p_datetrack_mode in varchar2
1441 ,p_elig_per_id in number
1442 --
1443 ,p_validation_start_date in out nocopy date
1444 ,p_validation_end_date in out nocopy date
1445 )
1446 is
1447 --
1451 l_effective_start_date date;
1448 l_validation_start_date date;
1449 l_validation_end_date date;
1450 --
1452 l_effective_end_date date;
1453 --
1454 l_table_name varchar2(100);
1455 --
1456 begin
1457 --
1458 l_table_name := 'BEN_ELIG_PER_F';
1459 --
1460 if p_datetrack_mode = hr_api.g_correction
1461 then
1462 --
1463 ben_batch_dt_api.return_effective_dates
1464 (p_base_table_name => l_table_name
1465 ,p_effective_date => p_effective_date
1466 ,p_base_key_value => p_elig_per_id
1467 ,p_effective_start_date => l_validation_start_date
1468 ,p_effective_end_date => l_validation_end_date
1469 );
1470 --
1471 elsif p_datetrack_mode = hr_api.g_update
1472 then
1473 --
1474 -- Determine if any future rows exist
1475 --
1476 If NOT (ben_batch_dt_api.Future_Rows_Exist
1477 (p_base_table_name => l_table_name
1478 ,p_effective_date => p_effective_date
1479 ,p_base_key_value => p_elig_per_id
1480 )
1481 )
1482 then
1483 --
1484 ben_batch_dt_api.return_effective_dates
1485 (p_base_table_name => l_table_name
1486 ,p_effective_date => p_effective_date
1487 ,p_base_key_value => p_elig_per_id
1488 ,p_effective_start_date => l_effective_start_date
1489 ,p_effective_end_date => l_effective_end_date
1490 );
1491 --
1492 -- Providing the current effective start date is not equal to the effective
1493 -- date we must return the the validation start and end dates
1494 --
1495 If (l_effective_start_date <> p_effective_date)
1496 then
1497 --
1498 l_validation_start_date := p_effective_date;
1499 l_validation_end_date := l_effective_end_date;
1500 --
1501 Else
1502 --
1503 -- We cannot perform a DateTrack update operation where the effective
1504 -- date is the same as the current effective end date
1505 --
1506 hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1507 hr_utility.raise_error;
1508 --
1509 End If;
1510 --
1511 Else
1512 --
1513 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1514 hr_utility.raise_error;
1515 --
1516 End If;
1517 --
1518 else
1519 --
1520 dt_api.validate_dt_mode
1521 (p_effective_date => p_effective_date
1522 ,p_datetrack_mode => p_datetrack_mode
1523 ,p_base_table_name => 'ben_elig_per_f'
1524 ,p_base_key_column => 'elig_per_id'
1525 ,p_base_key_value => p_elig_per_id
1526 ,p_parent_table_name1 => 'ben_ler_f'
1527 ,p_parent_key_column1 => 'ler_id'
1528 ,p_parent_key_value1 => ben_pep_shd.g_old_rec.ler_id
1529 ,p_parent_table_name2 => 'ben_pgm_f'
1530 ,p_parent_key_column2 => 'pgm_id'
1531 ,p_parent_key_value2 => ben_pep_shd.g_old_rec.pgm_id
1532 ,p_parent_table_name3 => 'ben_pl_f'
1533 ,p_parent_key_column3 => 'pl_id'
1534 ,p_parent_key_value3 => ben_pep_shd.g_old_rec.pl_id
1535 ,p_parent_table_name4 => 'per_all_people_f'
1536 ,p_parent_key_column4 => 'person_id'
1537 ,p_parent_key_value4 => ben_pep_shd.g_old_rec.person_id
1538 ,p_parent_table_name5 => 'ben_plip_f'
1539 ,p_parent_key_column5 => 'plip_id'
1540 ,p_parent_key_value5 => ben_pep_shd.g_old_rec.plip_id
1541 ,p_parent_table_name6 => 'ben_ptip_f'
1542 ,p_parent_key_column6 => 'ptip_id'
1543 ,p_parent_key_value6 => ben_pep_shd.g_old_rec.ptip_id
1544 ,p_child_table_name1 => 'ben_elig_per_opt_f'
1545 ,p_child_key_column1 => 'elig_per_opt_id'
1546 ,p_enforce_foreign_locking => false
1547 ,p_validation_start_date => l_validation_start_date
1548 ,p_validation_end_date => l_validation_end_date
1549 );
1550 --
1551 end if;
1552 --
1553 p_validation_start_date := l_validation_start_date;
1554 p_validation_end_date := l_validation_end_date;
1555 --
1556 end validate_dt_mode_pep;
1557 --
1558 procedure validate_dt_mode_epo
1559 (p_effective_date in date
1560 ,p_datetrack_mode in varchar2
1561 ,p_elig_per_opt_id in number
1562 --
1563 ,p_validation_start_date in out nocopy date
1564 ,p_validation_end_date in out nocopy date
1565 )
1566 is
1567 --
1568 l_validation_start_date date;
1569 l_validation_end_date date;
1570 --
1571 l_effective_start_date date;
1572 l_effective_end_date date;
1573 --
1574 l_table_name varchar2(100);
1575 --
1576 begin
1577 --
1578 l_table_name := 'BEN_ELIG_PER_OPT_F';
1579 --
1580 if p_datetrack_mode = hr_api.g_correction
1581 then
1582 --
1583 ben_batch_dt_api.return_effective_dates
1584 (p_base_table_name => l_table_name
1585 ,p_effective_date => p_effective_date
1586 ,p_base_key_value => p_elig_per_opt_id
1587 ,p_effective_start_date => l_validation_start_date
1588 ,p_effective_end_date => l_validation_end_date
1589 );
1590 --
1591 elsif p_datetrack_mode = hr_api.g_update
1592 then
1593 --
1594 -- Determine if any future rows exist
1595 --
1596 If NOT (ben_batch_dt_api.Future_Rows_Exist
1597 (p_base_table_name => l_table_name
1598 ,p_effective_date => p_effective_date
1599 ,p_base_key_value => p_elig_per_opt_id
1603 --
1600 )
1601 )
1602 then
1604 ben_batch_dt_api.return_effective_dates
1605 (p_base_table_name => l_table_name
1606 ,p_effective_date => p_effective_date
1607 ,p_base_key_value => p_elig_per_opt_id
1608 ,p_effective_start_date => l_effective_start_date
1609 ,p_effective_end_date => l_effective_end_date
1610 );
1611 --
1612 -- Providing the current effective start date is not equal to the effective
1613 -- date we must return the the validation start and end dates
1614 --
1615 If (l_effective_start_date <> p_effective_date)
1616 then
1617 --
1618 l_validation_start_date := p_effective_date;
1619 l_validation_end_date := l_effective_end_date;
1620 --
1621 Else
1622 --
1623 -- We cannot perform a DateTrack update operation where the effective
1624 -- date is the same as the current effective end date
1625 --
1626 hr_utility.set_message(801, 'HR_7179_DT_UPD_NOT_ALLOWED');
1627 hr_utility.raise_error;
1628 --
1629 End If;
1630 --
1631 Else
1632 --
1633 hr_utility.set_message(801, 'HR_7211_DT_UPD_ROWS_IN_FUTURE');
1634 hr_utility.raise_error;
1635 --
1636 End If;
1637 --
1638 else
1639 --
1640 dt_api.validate_dt_mode
1641 (p_effective_date => p_effective_date
1642 ,p_datetrack_mode => p_datetrack_mode
1643 ,p_base_table_name => 'ben_elig_per_opt_f'
1644 ,p_base_key_column => 'elig_per_opt_id'
1645 ,p_base_key_value => p_elig_per_opt_id
1646 ,p_parent_table_name1 => 'ben_elig_per_f'
1647 ,p_parent_key_column1 => 'elig_per_id'
1648 ,p_parent_key_value1 => ben_epo_shd.g_old_rec.elig_per_id
1649 ,p_parent_table_name2 => 'ben_opt_f'
1650 ,p_parent_key_column2 => 'opt_id'
1651 ,p_parent_key_value2 => ben_epo_shd.g_old_rec.opt_id
1652 ,p_enforce_foreign_locking => false
1653 ,p_validation_start_date => l_validation_start_date
1654 ,p_validation_end_date => l_validation_end_date
1655 );
1656 --
1657 end if;
1658 --
1659 p_validation_start_date := l_validation_start_date;
1660 p_validation_end_date := l_validation_end_date;
1661 --
1662 end validate_dt_mode_epo;
1663 --
1664 end ben_batch_dt_api;