[Home] [Help]
PACKAGE BODY: APPS.BEN_COBJ_CACHE
Source
1 package body ben_cobj_cache as
2 /* $Header: becobjch.pkb 120.2.12020000.3 2012/07/03 11:59:47 amnaraya ship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Author Comments
13 --------- --------- ---------- --------------------------------------------
14 115.0 28-Jun-99 mhoyes Created.
15 115.1 28-Jun-99 mhoyes Added pgm,ptip,plip,prel and etpr caches.
16 115.2 25-Sep-00 mhoyes - Upgraded to new caching.
17 115.3 26-Oct-00 mhoyes - Fixed record initialization problems.
18 115.4 26-Oct-00 mhoyes - Initialized record in exception for get routines
19 rather than on entry of the get routines.
20 115.5 17-May-01 maagrawa Added columns to pgm,pl,plip,ptip,oipl
21 records and modified their queries.
22 115.6 22-May-01 mhoyes - Upgraded comp object caches to be context
23 sensitive. Hence when the refresh routine is
24 not called then the cache will use SQL.
25 115.7 26-Jul-01 ikasire Bug 1895874 adding nip_dflt_flag column
26 to ben_pl_f table
27 115.10 29-Nov-05 abparekh Bug 4766118 - Added ALWS_QDRO_FLAG to G_PL_INST_ROW
28 115.11 13-Mar-06 kmahendr bug#5082245 - added svgs_pl_flag to g_pl_inst_row
29 115.12 27-Dec-11 velvanop Bug 12558830: Creating Ineligible Flag for Compensation Object. ALL_INELIG column ben_pl_f,ben_pgm_f,ben_ptip_f,ben_plip_f,ben_oipl_f
30 needs to cached to check whether the comp object is Ineligible or not.
31 -----------------------------------------------------------------------------
32 */
33 --
34 -- Globals.
35 --
36 g_package varchar2(50) := 'ben_cobj_cache.';
37 g_hash_key number := ben_hash_utility.get_hash_key;
38 g_hash_jump number := ben_hash_utility.get_hash_jump;
39 --
40 procedure write_oiplip_cache
41 (p_business_group_id in number
42 ,p_effective_date in date
43 )
44 is
45 --
46 l_proc varchar2(72) := 'write_oiplip_cache';
47 --
48 l_hv pls_integer;
49 --
50 cursor c_instance
51 (c_business_group_id NUMBER
52 ,c_effective_date DATE
53 )
54 is
55 SELECT TAB1.OIPLIP_ID,
56 TAB1.PLIP_ID,
57 TAB1.OIPL_ID
58 FROM BEN_OIPLIP_F TAB1
59 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
60 AND c_effective_date
61 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
62 --
63 begin
64 --
65 for objinst in c_instance
66 (c_business_group_id => p_business_group_id
67 ,c_effective_date => p_effective_date
68 )
69 loop
70 --
71 l_hv := mod(objinst.oiplip_id,ben_hash_utility.get_hash_key);
72 --
73 while ben_cobj_cache.g_oiplip_instance.exists(l_hv)
74 loop
75 --
76 l_hv := l_hv+g_hash_jump;
77 --
78 end loop;
79 --
80 ben_cobj_cache.g_oiplip_instance(l_hv) := objinst;
81 --
82 end loop;
83 --
84 end write_oiplip_cache;
85 --
86 procedure get_oiplip_dets
87 (p_business_group_id in number
88 ,p_effective_date in date
89 ,p_oiplip_id in number default null
90 ,p_inst_row in out NOCOPY g_oiplip_inst_row
91 )
92 is
93 --
94 l_proc varchar2(72) := 'get_oiplip_dets';
95 --
96 l_hv pls_integer;
97 l_reset g_oiplip_inst_row;
98 --
99 cursor c_instance
100 (c_oiplip_id NUMBER
101 ,c_effective_date DATE
102 )
103 is
104 SELECT TAB1.OIPLIP_ID,
105 TAB1.PLIP_ID,
106 TAB1.OIPL_ID
107 FROM BEN_OIPLIP_F TAB1
108 WHERE TAB1.oiplip_id = c_oiplip_id
109 AND c_effective_date
110 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
111 --
112 begin
113 --
114 if g_oiplip_cached > 0
115 then
116 --
117 if g_oiplip_cached = 1 then
118 --
119 write_oiplip_cache
120 (p_business_group_id => p_business_group_id
121 ,p_effective_date => p_effective_date
122 );
123 --
124 g_oiplip_cached := 2;
125 --
126 end if;
127 --
128 -- Get the instance details
129 --
130 l_hv := mod(p_oiplip_id,ben_hash_utility.get_hash_key);
131 --
132 if g_oiplip_instance(l_hv).oiplip_id = p_oiplip_id
133 then
134 -- Matched row
135 null;
136 else
137 --
138 -- Loop through the hash using the jump routine to check further
139 -- indexes if none exists at current index the NO_DATA_FOUND expection
140 -- will fire
141 --
142 l_hv := l_hv+g_hash_jump;
143 while g_oiplip_instance(l_hv).oiplip_id <> p_oiplip_id loop
144 --
145 l_hv := l_hv+g_hash_jump;
146 --
147 end loop;
148 --
149 end if;
150 --
151 p_inst_row := g_oiplip_instance(l_hv);
152 --
153 else
154 --
155 open c_instance
156 (c_oiplip_id => p_oiplip_id
157 ,c_effective_date => p_effective_date
158 );
159 fetch c_instance into p_inst_row;
160 close c_instance;
161 --
162 end if;
163 --
164 exception
165 --
166 when no_data_found then
167 --
168 p_inst_row := l_reset;
169 --
170 end get_oiplip_dets;
171 --
172 procedure write_opt_cache
173 (p_business_group_id in number
174 ,p_effective_date in date
175 )
176 is
177 --
178 l_hv pls_integer;
179 --
180 cursor c_instance
181 (c_business_group_id NUMBER
182 ,c_effective_date DATE
183 )
184 is
185 SELECT tab1.opt_id
186 ,tab1.name
187 ,tab1.effective_start_date
188 ,tab1.effective_end_date
189 ,tab1.rqd_perd_enrt_nenrt_uom
190 ,tab1.rqd_perd_enrt_nenrt_val
191 ,tab1.rqd_perd_enrt_nenrt_rl
192 FROM BEN_OPT_F TAB1
193 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
194 AND c_effective_date
195 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
196 --
197 begin
198 --
199 for objinst in c_instance
200 (c_business_group_id => p_business_group_id
201 ,c_effective_date => p_effective_date
202 )
203 loop
204 --
205 l_hv := mod(objinst.opt_id,ben_hash_utility.get_hash_key);
206 --
207 while ben_cobj_cache.g_opt_instance.exists(l_hv)
208 loop
209 --
210 l_hv := l_hv+g_hash_jump;
211 --
212 end loop;
213 --
214 ben_cobj_cache.g_opt_instance(l_hv) := objinst;
215 --
216 end loop;
217 --
218 end write_opt_cache;
219 --
220 procedure get_opt_dets
221 (p_business_group_id in number
222 ,p_effective_date in date
223 ,p_opt_id in number default null
224 ,p_inst_row in out NOCOPY g_opt_inst_row
225 )
226 is
227 --
228 l_proc varchar2(72) := 'get_opt_dets';
229 --
230 l_hv pls_integer;
231 l_reset g_opt_inst_row;
232 --
233 cursor c_instance
234 (c_opt_id NUMBER
235 ,c_effective_date DATE
236 )
237 is
238 SELECT tab1.opt_id
239 ,tab1.name
240 ,tab1.effective_start_date
241 ,tab1.effective_end_date
242 ,tab1.rqd_perd_enrt_nenrt_uom
243 ,tab1.rqd_perd_enrt_nenrt_val
244 ,tab1.rqd_perd_enrt_nenrt_rl
245 FROM BEN_OPT_F TAB1
246 WHERE TAB1.opt_id = c_opt_id
247 AND c_effective_date
248 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
249 --
250 begin
251 --
252 if g_opt_cached > 0
253 then
254 --
255 if g_opt_cached = 1 then
256 --
257 write_opt_cache
258 (p_business_group_id => p_business_group_id
259 ,p_effective_date => p_effective_date
260 );
261 --
262 g_opt_cached := 2;
263 --
264 end if;
265 --
266 -- Get the instance details
267 --
268 l_hv := mod(p_opt_id,ben_hash_utility.get_hash_key);
269 --
270 if g_opt_instance(l_hv).opt_id = p_opt_id
271 then
272 -- Matched row
273 null;
274 else
275 --
276 -- Loop through the hash using the jump routine to check further
277 -- indexes if none exists at current index the NO_DATA_FOUND expection
278 -- will fire
279 --
280 l_hv := l_hv+g_hash_jump;
281 while g_opt_instance(l_hv).opt_id <> p_opt_id loop
282 --
283 l_hv := l_hv+g_hash_jump;
284 --
285 end loop;
286 --
287 end if;
288 --
289 p_inst_row := g_opt_instance(l_hv);
290 --
291 else
292 --
293 open c_instance
294 (c_opt_id => p_opt_id
295 ,c_effective_date => p_effective_date
296 );
297 fetch c_instance into p_inst_row;
298 close c_instance;
299 --
300 end if;
301 --
302 exception
303 --
304 when no_data_found then
305 --
306 p_inst_row := l_reset;
307 --
308 end get_opt_dets;
309 --
310 procedure write_oipl_cache
311 (p_business_group_id in number
312 ,p_effective_date in date
313 )
314 is
315 --
316 l_proc varchar2(72) := 'write_oipl_cache';
317 --
318 l_hv pls_integer;
319 --
320 cursor c_instance
321 (c_business_group_id NUMBER
322 ,c_effective_date DATE
323 )
324 is
325 SELECT tab1.oipl_id
326 ,tab1.effective_start_date
327 ,tab1.effective_end_date
328 ,tab1.opt_id
329 ,tab1.pl_id
330 ,tab1.trk_inelig_per_flag
331 ,tab1.ordr_num
332 ,tab1.elig_apls_flag
333 ,tab1.prtn_elig_ovrid_alwd_flag
334 ,tab1.vrfy_fmly_mmbr_cd
335 ,tab1.vrfy_fmly_mmbr_rl
336 ,tab1.per_cvrd_cd
337 ,tab1.dflt_flag
338 ,tab1.mndtry_flag
339 ,tab1.mndtry_rl
340 ,tab1.auto_enrt_flag
341 ,tab1.auto_enrt_mthd_rl
342 ,tab1.enrt_cd
343 ,tab1.enrt_rl
344 ,tab1.dflt_enrt_cd
345 ,tab1.dflt_enrt_det_rl
346 ,tab1.rqd_perd_enrt_nenrt_uom
347 ,tab1.rqd_perd_enrt_nenrt_val
348 ,tab1.rqd_perd_enrt_nenrt_rl
349 ,tab1.actl_prem_id
350 ,tab1.postelcn_edit_rl
351 ,tab1.all_inelig
352 FROM BEN_OIPL_F TAB1
353 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
354 AND c_effective_date
355 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
356 --
357 begin
358 --
359 for objinst in c_instance
360 (c_business_group_id => p_business_group_id
361 ,c_effective_date => p_effective_date
362 )
363 loop
364 --
365 l_hv := mod(objinst.oipl_id,ben_hash_utility.get_hash_key);
366 --
367 while ben_cobj_cache.g_oipl_instance.exists(l_hv)
368 loop
369 --
370 l_hv := l_hv+g_hash_jump;
371 --
372 end loop;
373 --
374 ben_cobj_cache.g_oipl_instance(l_hv) := objinst;
375 --
376 end loop;
377 --
378 end write_oipl_cache;
379 --
380 procedure get_oipl_dets
381 (p_business_group_id in number
382 ,p_effective_date in date
383 ,p_oipl_id in number default null
384 ,p_inst_row in out NOCOPY g_oipl_inst_row
385 )
386 is
387 --
388 l_proc varchar2(72) := 'get_oipl_dets';
389 l_reset g_oipl_inst_row;
390 --
391 l_hv pls_integer;
392 --
393 cursor c_instance
394 (c_oipl_id NUMBER
395 ,c_effective_date DATE
396 )
397 is
398 SELECT tab1.oipl_id
399 ,tab1.effective_start_date
400 ,tab1.effective_end_date
401 ,tab1.opt_id
402 ,tab1.pl_id
403 ,tab1.trk_inelig_per_flag
404 ,tab1.ordr_num
405 ,tab1.elig_apls_flag
406 ,tab1.prtn_elig_ovrid_alwd_flag
407 ,tab1.vrfy_fmly_mmbr_cd
408 ,tab1.vrfy_fmly_mmbr_rl
409 ,tab1.per_cvrd_cd
410 ,tab1.dflt_flag
411 ,tab1.mndtry_flag
412 ,tab1.mndtry_rl
413 ,tab1.auto_enrt_flag
414 ,tab1.auto_enrt_mthd_rl
415 ,tab1.enrt_cd
416 ,tab1.enrt_rl
417 ,tab1.dflt_enrt_cd
418 ,tab1.dflt_enrt_det_rl
419 ,tab1.rqd_perd_enrt_nenrt_uom
420 ,tab1.rqd_perd_enrt_nenrt_val
421 ,tab1.rqd_perd_enrt_nenrt_rl
422 ,tab1.actl_prem_id
423 ,tab1.postelcn_edit_rl
424 ,tab1.all_inelig
425 FROM BEN_OIPL_F TAB1
426 WHERE TAB1.oipl_id = c_oipl_id
427 AND c_effective_date
428 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
429 --
430 begin
431 --
432 if g_oipl_cached > 0
433 then
434 --
435 if g_oipl_cached = 1 then
436 --
437 write_oipl_cache
438 (p_business_group_id => p_business_group_id
439 ,p_effective_date => p_effective_date
440 );
441 --
442 g_oipl_cached := 2;
443 --
444 end if;
445 --
446 -- Get the instance details
447 --
448 l_hv := mod(p_oipl_id,ben_hash_utility.get_hash_key);
449 --
450 if g_oipl_instance(l_hv).oipl_id = p_oipl_id
451 then
452 -- Matched row
453 null;
454 else
455 --
456 -- Loop through the hash using the jump routine to check further
457 -- indexes if none exists at current index the NO_DATA_FOUND expection
458 -- will fire
459 --
460 l_hv := l_hv+g_hash_jump;
461 while g_oipl_instance(l_hv).oipl_id <> p_oipl_id loop
462 --
463 l_hv := l_hv+g_hash_jump;
464 --
465 end loop;
466 --
467 end if;
468 --
469 p_inst_row := g_oipl_instance(l_hv);
470 --
471 else
472 --
473 open c_instance
474 (c_oipl_id => p_oipl_id
475 ,c_effective_date => p_effective_date
476 );
477 fetch c_instance into p_inst_row;
478 close c_instance;
479 --
480 end if;
481 --
482 exception
483 --
484 when no_data_found then
485 --
486 p_inst_row := l_reset;
487 --
488 end get_oipl_dets;
489 --
490 procedure write_pgm_cache
491 (p_business_group_id in number
492 ,p_effective_date in date
493 )
494 is
495 --
496 l_proc varchar2(72) := 'write_pgm_cache';
497 --
498 l_hv pls_integer;
499 --
500 cursor c_instance
501 (c_business_group_id NUMBER
502 ,c_effective_date DATE
503 )
504 is
505 SELECT tab1.pgm_id,
506 tab1.effective_start_date,
507 tab1.effective_end_date,
508 tab1.enrt_cvg_strt_dt_cd,
509 tab1.enrt_cvg_strt_dt_rl,
510 tab1.enrt_cvg_end_dt_cd,
511 tab1.enrt_cvg_end_dt_rl,
512 tab1.rt_strt_dt_cd,
513 tab1.rt_strt_dt_rl,
514 tab1.rt_end_dt_cd,
515 tab1.rt_end_dt_rl,
516 tab1.elig_apls_flag,
517 tab1.prtn_elig_ovrid_alwd_flag,
518 tab1.trk_inelig_per_flag,
519 tab1.vrfy_fmly_mmbr_cd,
520 tab1.vrfy_fmly_mmbr_rl,
521 tab1.dpnt_dsgn_lvl_cd,
522 tab1.dpnt_dsgn_cd,
523 tab1.dpnt_cvg_strt_dt_cd,
524 tab1.dpnt_cvg_strt_dt_rl,
525 tab1.dpnt_cvg_end_dt_cd,
526 tab1.dpnt_cvg_end_dt_rl,
527 tab1.pgm_typ_cd,
528 tab1.all_inelig
529 FROM BEN_PGM_F TAB1
530 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
531 AND c_effective_date
532 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
533 --
534 begin
535 --
536 for objinst in c_instance
537 (c_business_group_id => p_business_group_id
538 ,c_effective_date => p_effective_date
539 )
540 loop
541 --
542 l_hv := mod(objinst.pgm_id,ben_hash_utility.get_hash_key);
543 --
544 while ben_cobj_cache.g_pgm_instance.exists(l_hv)
545 loop
546 --
547 l_hv := l_hv+g_hash_jump;
548 --
549 end loop;
550 --
551 ben_cobj_cache.g_pgm_instance(l_hv) := objinst;
552 --
553 end loop;
554 --
555 end write_pgm_cache;
556 --
557 procedure get_pgm_dets
558 (p_business_group_id in number
559 ,p_effective_date in date
560 ,p_pgm_id in number default null
561 ,p_inst_row in out NOCOPY g_pgm_inst_row
562 )
563 is
564 --
565 l_proc varchar2(72) := 'get_pgm_dets';
566 --
567 l_reset g_pgm_inst_row;
568 l_hv pls_integer;
569 --
570 cursor c_instance
571 (c_pgm_id NUMBER
572 ,c_effective_date DATE
573 )
574 is
575 SELECT tab1.pgm_id,
576 tab1.effective_start_date,
577 tab1.effective_end_date,
578 tab1.enrt_cvg_strt_dt_cd,
579 tab1.enrt_cvg_strt_dt_rl,
580 tab1.enrt_cvg_end_dt_cd,
581 tab1.enrt_cvg_end_dt_rl,
582 tab1.rt_strt_dt_cd,
583 tab1.rt_strt_dt_rl,
584 tab1.rt_end_dt_cd,
585 tab1.rt_end_dt_rl,
586 tab1.elig_apls_flag,
587 tab1.prtn_elig_ovrid_alwd_flag,
588 tab1.trk_inelig_per_flag,
589 tab1.vrfy_fmly_mmbr_cd,
590 tab1.vrfy_fmly_mmbr_rl,
591 tab1.dpnt_dsgn_lvl_cd,
592 tab1.dpnt_dsgn_cd,
593 tab1.dpnt_cvg_strt_dt_cd,
594 tab1.dpnt_cvg_strt_dt_rl,
595 tab1.dpnt_cvg_end_dt_cd,
596 tab1.dpnt_cvg_end_dt_rl,
597 tab1.pgm_typ_cd,
598 tab1.all_inelig
599 FROM BEN_PGM_F TAB1
600 WHERE TAB1.pgm_id = c_pgm_id
601 AND c_effective_date
602 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
603 --
604 begin
605 --
606 if g_pgm_cached > 0
607 then
608 --
609 if g_pgm_cached = 1 then
610 --
611 write_pgm_cache
612 (p_business_group_id => p_business_group_id
613 ,p_effective_date => p_effective_date
614 );
615 --
616 g_pgm_cached := 2;
617 --
618 end if;
619 --
620 -- Get the instance details
621 --
622 l_hv := mod(p_pgm_id,ben_hash_utility.get_hash_key);
623 --
624 if g_pgm_instance(l_hv).pgm_id = p_pgm_id
625 then
626 -- Matched row
627 null;
628 else
629 --
630 -- Loop through the hash using the jump routine to check further
631 -- indexes if none exists at current index the NO_DATA_FOUND expection
632 -- will fire
633 --
634 l_hv := l_hv+g_hash_jump;
635 while g_pgm_instance(l_hv).pgm_id <> p_pgm_id loop
636 --
637 l_hv := l_hv+g_hash_jump;
638 --
639 end loop;
640 --
641 end if;
642 --
643 p_inst_row := g_pgm_instance(l_hv);
644 --
645 else
646 --
647 open c_instance
648 (c_pgm_id => p_pgm_id
649 ,c_effective_date => p_effective_date
650 );
651 fetch c_instance into p_inst_row;
652 close c_instance;
653 --
654 end if;
655 --
656 exception
657 --
658 when no_data_found then
659 --
660 p_inst_row := l_reset;
661 --
662 end get_pgm_dets;
663 --
664 procedure write_ptip_cache
665 (p_business_group_id in number
666 ,p_effective_date in date
667 )
668 is
669 --
670 l_proc varchar2(72) := 'write_ptip_cache';
671 --
672 l_hv pls_integer;
673 --
674 cursor c_instance
675 (c_business_group_id NUMBER
676 ,c_effective_date DATE
677 )
678 is
679 SELECT tab1.ptip_id,
680 tab1.effective_start_date,
681 tab1.effective_end_date,
682 tab1.enrt_cvg_strt_dt_cd,
683 tab1.enrt_cvg_strt_dt_rl,
684 tab1.enrt_cvg_end_dt_cd,
685 tab1.enrt_cvg_end_dt_rl,
686 tab1.rt_strt_dt_cd,
687 tab1.rt_strt_dt_rl,
688 tab1.rt_end_dt_cd,
689 tab1.rt_end_dt_rl,
690 tab1.elig_apls_flag,
691 tab1.prtn_elig_ovrid_alwd_flag,
692 tab1.trk_inelig_per_flag,
693 tab1.ordr_num,
694 tab1.vrfy_fmly_mmbr_cd,
695 tab1.vrfy_fmly_mmbr_rl,
696 tab1.rqd_perd_enrt_nenrt_tm_uom,
697 tab1.rqd_perd_enrt_nenrt_val,
698 tab1.rqd_perd_enrt_nenrt_rl,
699 tab1.dpnt_dsgn_cd,
700 tab1.dpnt_cvg_strt_dt_cd,
701 tab1.dpnt_cvg_strt_dt_rl,
702 tab1.dpnt_cvg_end_dt_cd,
703 tab1.dpnt_cvg_end_dt_rl,
704 tab1.postelcn_edit_rl,
705 tab1.all_inelig
706 FROM BEN_PTIP_F TAB1
707 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
708 AND c_effective_date
709 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
710 --
714 (c_business_group_id => p_business_group_id
711 begin
712 --
713 for objinst in c_instance
715 ,c_effective_date => p_effective_date
716 )
717 loop
718 --
719 l_hv := mod(objinst.ptip_id,ben_hash_utility.get_hash_key);
720 --
721 while ben_cobj_cache.g_ptip_instance.exists(l_hv)
722 loop
723 --
724 l_hv := l_hv+g_hash_jump;
725 --
726 end loop;
727 --
728 ben_cobj_cache.g_ptip_instance(l_hv) := objinst;
729 --
730 end loop;
731 --
732 end write_ptip_cache;
733 --
734 procedure get_ptip_dets
735 (p_business_group_id in number
736 ,p_effective_date in date
737 ,p_ptip_id in number default null
738 ,p_inst_row in out NOCOPY g_ptip_inst_row
739 )
740 is
741 --
742 l_proc varchar2(72) := 'get_ptip_dets';
743 --
744 l_reset g_ptip_inst_row;
745 l_hv pls_integer;
746 --
747 cursor c_instance
748 (c_ptip_id NUMBER
749 ,c_effective_date DATE
750 )
751 is
752 SELECT tab1.ptip_id,
753 tab1.effective_start_date,
754 tab1.effective_end_date,
755 tab1.enrt_cvg_strt_dt_cd,
756 tab1.enrt_cvg_strt_dt_rl,
757 tab1.enrt_cvg_end_dt_cd,
758 tab1.enrt_cvg_end_dt_rl,
759 tab1.rt_strt_dt_cd,
760 tab1.rt_strt_dt_rl,
761 tab1.rt_end_dt_cd,
762 tab1.rt_end_dt_rl,
763 tab1.elig_apls_flag,
764 tab1.prtn_elig_ovrid_alwd_flag,
765 tab1.trk_inelig_per_flag,
766 tab1.ordr_num,
767 tab1.vrfy_fmly_mmbr_cd,
768 tab1.vrfy_fmly_mmbr_rl,
769 tab1.rqd_perd_enrt_nenrt_tm_uom,
770 tab1.rqd_perd_enrt_nenrt_val,
771 tab1.rqd_perd_enrt_nenrt_rl,
772 tab1.dpnt_dsgn_cd,
773 tab1.dpnt_cvg_strt_dt_cd,
774 tab1.dpnt_cvg_strt_dt_rl,
775 tab1.dpnt_cvg_end_dt_cd,
776 tab1.dpnt_cvg_end_dt_rl,
777 tab1.postelcn_edit_rl,
778 tab1.all_inelig
779 FROM BEN_PTIP_F TAB1
780 WHERE TAB1.ptip_id = c_ptip_id
781 AND c_effective_date
782 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
783 --
784 begin
785 --
786 if g_ptip_cached > 0
787 then
788 --
789 if g_ptip_cached = 1 then
790 --
791 write_ptip_cache
792 (p_business_group_id => p_business_group_id
793 ,p_effective_date => p_effective_date
794 );
795 --
796 g_ptip_cached := 2;
797 --
798 end if;
799 --
800 -- Get the instance details
801 --
802 l_hv := mod(p_ptip_id,ben_hash_utility.get_hash_key);
803 --
804 if g_ptip_instance(l_hv).ptip_id = p_ptip_id
805 then
806 -- Matched row
807 null;
808 else
809 --
810 -- Loop through the hash using the jump routine to check further
811 -- indexes if none exists at current index the NO_DATA_FOUND expection
812 -- will fire
813 --
814 l_hv := l_hv+g_hash_jump;
815 while g_ptip_instance(l_hv).ptip_id <> p_ptip_id loop
816 --
817 l_hv := l_hv+g_hash_jump;
818 --
819 end loop;
820 --
821 end if;
822 --
823 p_inst_row := g_ptip_instance(l_hv);
824 --
825 else
826 --
827 open c_instance
828 (c_ptip_id => p_ptip_id
829 ,c_effective_date => p_effective_date
830 );
831 fetch c_instance into p_inst_row;
832 close c_instance;
833 --
834 end if;
835 --
836 exception
837 --
838 when no_data_found then
839 --
840 p_inst_row := l_reset;
841 --
842 end get_ptip_dets;
843 --
844 procedure write_plip_cache
845 (p_business_group_id in number
846 ,p_effective_date in date
847 )
848 is
849 --
850 l_proc varchar2(72) := 'write_plip_cache';
851 --
852 l_hv pls_integer;
853 --
854 cursor c_instance
855 (c_business_group_id NUMBER
856 ,c_effective_date DATE
857 )
858 is
859 SELECT tab1.plip_id,
860 tab1.effective_start_date,
861 tab1.effective_end_date,
862 tab1.enrt_cvg_strt_dt_cd,
863 tab1.enrt_cvg_strt_dt_rl,
864 tab1.enrt_cvg_end_dt_cd,
865 tab1.enrt_cvg_end_dt_rl,
866 tab1.rt_strt_dt_cd,
867 tab1.rt_strt_dt_rl,
868 tab1.rt_end_dt_cd,
869 tab1.rt_end_dt_rl,
870 tab1.elig_apls_flag,
871 tab1.prtn_elig_ovrid_alwd_flag,
872 tab1.trk_inelig_per_flag,
873 tab1.ordr_num,
874 tab1.vrfy_fmly_mmbr_cd,
875 tab1.vrfy_fmly_mmbr_rl,
876 tab1.bnft_or_option_rstrctn_cd,
877 tab1.pl_id,
878 tab1.pgm_id,
879 tab1.cvg_incr_r_decr_only_cd,
880 tab1.mx_cvg_mlt_incr_num,
881 tab1.mx_cvg_mlt_incr_wcf_num,
882 tab1.postelcn_edit_rl,
883 tab1.all_inelig
884 FROM BEN_PLIP_F TAB1
885 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
886 AND c_effective_date
887 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
888 --
889 begin
890 --
891 for objinst in c_instance
895 loop
892 (c_business_group_id => p_business_group_id
893 ,c_effective_date => p_effective_date
894 )
896 --
897 l_hv := mod(objinst.plip_id,ben_hash_utility.get_hash_key);
898 --
899 while ben_cobj_cache.g_plip_instance.exists(l_hv)
900 loop
901 --
902 l_hv := l_hv+g_hash_jump;
903 --
904 end loop;
905 --
906 ben_cobj_cache.g_plip_instance(l_hv) := objinst;
907 --
908 end loop;
909 --
910 end write_plip_cache;
911 --
912 procedure get_plip_dets
913 (p_business_group_id in number
914 ,p_effective_date in date
915 ,p_plip_id in number default null
916 ,p_inst_row in out NOCOPY g_plip_inst_row
917 )
918 is
919 --
920 l_proc varchar2(72) := 'get_plip_dets';
921 --
922 l_reset g_plip_inst_row;
923 l_hv pls_integer;
924 --
925 cursor c_instance
926 (c_plip_id NUMBER
927 ,c_effective_date DATE
928 )
929 is
930 SELECT tab1.plip_id,
931 tab1.effective_start_date,
932 tab1.effective_end_date,
933 tab1.enrt_cvg_strt_dt_cd,
934 tab1.enrt_cvg_strt_dt_rl,
935 tab1.enrt_cvg_end_dt_cd,
936 tab1.enrt_cvg_end_dt_rl,
937 tab1.rt_strt_dt_cd,
938 tab1.rt_strt_dt_rl,
939 tab1.rt_end_dt_cd,
940 tab1.rt_end_dt_rl,
941 tab1.elig_apls_flag,
942 tab1.prtn_elig_ovrid_alwd_flag,
943 tab1.trk_inelig_per_flag,
944 tab1.ordr_num,
945 tab1.vrfy_fmly_mmbr_cd,
946 tab1.vrfy_fmly_mmbr_rl,
947 tab1.bnft_or_option_rstrctn_cd,
948 tab1.pl_id,
949 tab1.pgm_id,
950 tab1.cvg_incr_r_decr_only_cd,
951 tab1.mx_cvg_mlt_incr_num,
952 tab1.mx_cvg_mlt_incr_wcf_num,
953 tab1.postelcn_edit_rl,
954 tab1.all_inelig
955 FROM BEN_PLIP_F TAB1
956 WHERE TAB1.plip_id = c_plip_id
957 AND c_effective_date
958 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
959 --
960 begin
961 --
962 if g_plip_cached > 0
963 then
964 --
965 if g_plip_cached = 1 then
966 --
967 write_plip_cache
968 (p_business_group_id => p_business_group_id
969 ,p_effective_date => p_effective_date
970 );
971 --
972 g_plip_cached := 2;
973 --
974 end if;
975 --
976 -- Get the instance details
977 --
978 l_hv := mod(p_plip_id,ben_hash_utility.get_hash_key);
979 --
980 if g_plip_instance(l_hv).plip_id = p_plip_id
981 then
982 -- Matched row
983 null;
984 else
985 --
986 -- Loop through the hash using the jump routine to check further
987 -- indexes if none exists at current index the NO_DATA_FOUND expection
988 -- will fire
989 --
990 l_hv := l_hv+g_hash_jump;
991 while g_plip_instance(l_hv).plip_id <> p_plip_id loop
992 --
993 l_hv := l_hv+g_hash_jump;
994 --
995 end loop;
996 --
997 end if;
998 --
999 p_inst_row := g_plip_instance(l_hv);
1000 --
1001 else
1002 --
1003 open c_instance
1004 (c_plip_id => p_plip_id
1005 ,c_effective_date => p_effective_date
1006 );
1007 fetch c_instance into p_inst_row;
1008 close c_instance;
1009 --
1010 end if;
1011 --
1012 exception
1013 --
1014 when no_data_found then
1015 --
1016 p_inst_row := l_reset;
1017 --
1018 end get_plip_dets;
1019 --
1020 procedure write_pl_cache
1021 (p_business_group_id in number
1022 ,p_effective_date in date
1023 )
1024 is
1025 --
1026 l_proc varchar2(72) := 'write_pl_cache';
1027 --
1028 l_hv pls_integer;
1029 --
1030 cursor c_instance
1031 (c_business_group_id NUMBER
1032 ,c_effective_date DATE
1033 )
1034 is
1035 SELECT tab1.pl_id,
1036 tab1.effective_start_date,
1037 tab1.effective_end_date,
1038 tab1.enrt_cvg_strt_dt_cd,
1039 tab1.enrt_cvg_strt_dt_rl,
1040 tab1.enrt_cvg_end_dt_cd,
1041 tab1.enrt_cvg_end_dt_rl,
1042 tab1.rt_strt_dt_cd,
1043 tab1.rt_strt_dt_rl,
1044 tab1.rt_end_dt_cd,
1045 tab1.rt_end_dt_rl,
1046 tab1.elig_apls_flag,
1047 tab1.prtn_elig_ovrid_alwd_flag,
1048 tab1.per_cvrd_cd,
1049 tab1.pl_typ_id,
1050 tab1.trk_inelig_per_flag,
1051 tab1.ordr_num,
1052 tab1.mx_wtg_dt_to_use_cd,
1053 tab1.mx_wtg_dt_to_use_rl,
1054 tab1.mx_wtg_perd_rl,
1055 tab1.mx_wtg_perd_prte_uom,
1056 tab1.mx_wtg_perd_prte_val,
1057 tab1.vrfy_fmly_mmbr_cd,
1058 tab1.vrfy_fmly_mmbr_rl,
1059 tab1.bnft_or_option_rstrctn_cd,
1060 tab1.nip_dflt_enrt_cd,
1061 tab1.nip_dflt_enrt_det_rl,
1062 tab1.rqd_perd_enrt_nenrt_uom,
1063 tab1.rqd_perd_enrt_nenrt_val,
1064 tab1.rqd_perd_enrt_nenrt_rl,
1065 tab1.cvg_incr_r_decr_only_cd,
1066 tab1.mx_cvg_mlt_incr_num,
1067 tab1.mx_cvg_mlt_incr_wcf_num,
1068 tab1.name,
1069 tab1.actl_prem_id,
1070 tab1.bnf_dsgn_cd,
1074 tab1.dpnt_cvg_end_dt_cd,
1071 tab1.enrt_pl_opt_flag,
1072 tab1.dpnt_cvg_strt_dt_cd,
1073 tab1.dpnt_cvg_strt_dt_rl,
1075 tab1.dpnt_cvg_end_dt_rl,
1076 tab1.alws_qmcso_flag,
1077 tab1.alws_qdro_flag, /* Bug 4766118 */
1078 tab1.dpnt_dsgn_cd,
1079 tab1.postelcn_edit_rl,
1080 tab1.dpnt_cvd_by_othr_apls_flag,
1081 tab1.nip_dflt_flag,
1082 tab1.svgs_pl_flag,
1083 tab1.all_inelig
1084 FROM BEN_PL_F TAB1
1085 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
1086 AND c_effective_date
1087 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
1088 --
1089 begin
1090 --
1091 for objinst in c_instance
1092 (c_business_group_id => p_business_group_id
1093 ,c_effective_date => p_effective_date
1094 )
1095 loop
1096 --
1097 l_hv := mod(objinst.pl_id,ben_hash_utility.get_hash_key);
1098 --
1099 while ben_cobj_cache.g_pl_instance.exists(l_hv)
1100 loop
1101 --
1102 l_hv := l_hv+g_hash_jump;
1103 --
1104 end loop;
1105 --
1106 ben_cobj_cache.g_pl_instance(l_hv) := objinst;
1107 --
1108 end loop;
1109 --
1110 end write_pl_cache;
1111 --
1112 procedure get_pl_dets
1113 (p_business_group_id in number
1114 ,p_effective_date in date
1115 ,p_pl_id in number default null
1116 ,p_inst_row in out NOCOPY g_pl_inst_row
1117 )
1118 is
1119 --
1120 l_proc varchar2(72) := 'get_pl_dets';
1121 --
1122 l_reset g_pl_inst_row;
1123 l_hv pls_integer;
1124 --
1125 cursor c_instance
1126 (c_pl_id NUMBER
1127 ,c_effective_date DATE
1128 )
1129 is
1130 SELECT tab1.pl_id,
1131 tab1.effective_start_date,
1132 tab1.effective_end_date,
1133 tab1.enrt_cvg_strt_dt_cd,
1134 tab1.enrt_cvg_strt_dt_rl,
1135 tab1.enrt_cvg_end_dt_cd,
1136 tab1.enrt_cvg_end_dt_rl,
1137 tab1.rt_strt_dt_cd,
1138 tab1.rt_strt_dt_rl,
1139 tab1.rt_end_dt_cd,
1140 tab1.rt_end_dt_rl,
1141 tab1.elig_apls_flag,
1142 tab1.prtn_elig_ovrid_alwd_flag,
1143 tab1.per_cvrd_cd,
1144 tab1.pl_typ_id,
1145 tab1.trk_inelig_per_flag,
1146 tab1.ordr_num,
1147 tab1.mx_wtg_dt_to_use_cd,
1148 tab1.mx_wtg_dt_to_use_rl,
1149 tab1.mx_wtg_perd_rl,
1150 tab1.mx_wtg_perd_prte_uom,
1151 tab1.mx_wtg_perd_prte_val,
1152 tab1.vrfy_fmly_mmbr_cd,
1153 tab1.vrfy_fmly_mmbr_rl,
1154 tab1.bnft_or_option_rstrctn_cd,
1155 tab1.nip_dflt_enrt_cd,
1156 tab1.nip_dflt_enrt_det_rl,
1157 tab1.rqd_perd_enrt_nenrt_uom,
1158 tab1.rqd_perd_enrt_nenrt_val,
1159 tab1.rqd_perd_enrt_nenrt_rl,
1160 tab1.cvg_incr_r_decr_only_cd,
1161 tab1.mx_cvg_mlt_incr_num,
1162 tab1.mx_cvg_mlt_incr_wcf_num,
1163 tab1.name,
1164 tab1.actl_prem_id,
1165 tab1.bnf_dsgn_cd,
1166 tab1.enrt_pl_opt_flag,
1167 tab1.dpnt_cvg_strt_dt_cd,
1168 tab1.dpnt_cvg_strt_dt_rl,
1169 tab1.dpnt_cvg_end_dt_cd,
1170 tab1.dpnt_cvg_end_dt_rl,
1171 tab1.alws_qmcso_flag,
1172 tab1.alws_qdro_flag, /* Bug 4766118 */
1173 tab1.dpnt_dsgn_cd,
1174 tab1.postelcn_edit_rl,
1175 tab1.dpnt_cvd_by_othr_apls_flag,
1176 tab1.nip_dflt_flag,
1177 tab1.SVGS_PL_FLAG,
1178 tab1.all_inelig
1179 FROM BEN_PL_F TAB1
1180 WHERE TAB1.pl_id = c_pl_id
1181 AND c_effective_date
1182 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
1183 --
1184 begin
1185 --
1186 if g_pl_cached > 0
1187 then
1188 --
1189 if g_pl_cached = 1 then
1190 --
1191 write_pl_cache
1192 (p_business_group_id => p_business_group_id
1193 ,p_effective_date => p_effective_date
1194 );
1195 --
1196 g_pl_cached := 2;
1197 --
1198 end if;
1199 --
1200 -- Get the instance details
1201 --
1202 l_hv := mod(p_pl_id,ben_hash_utility.get_hash_key);
1203 --
1204 if g_pl_instance(l_hv).pl_id = p_pl_id
1205 then
1206 -- Matched row
1207 null;
1208 else
1209 --
1210 -- Loop through the hash using the jump routine to check further
1211 -- indexes if none exists at current index the NO_DATA_FOUND expection
1212 -- will fire
1213 --
1214 l_hv := l_hv+g_hash_jump;
1215 while g_pl_instance(l_hv).pl_id <> p_pl_id loop
1216 --
1217 l_hv := l_hv+g_hash_jump;
1218 --
1219 end loop;
1220 --
1221 end if;
1222 --
1223 p_inst_row := g_pl_instance(l_hv);
1224 --
1225 else
1226 --
1227 open c_instance
1228 (c_pl_id => p_pl_id
1229 ,c_effective_date => p_effective_date
1230 );
1231 fetch c_instance into p_inst_row;
1232 close c_instance;
1233 --
1234 end if;
1235 --
1236 exception
1237 --
1238 when no_data_found then
1239 --
1240 p_inst_row := l_reset;
1241 --
1242 end get_pl_dets;
1243 --
1244 procedure write_etpr_cache
1245 (p_business_group_id in number
1246 ,p_effective_date in date
1250 l_hv pls_integer;
1247 )
1248 is
1249 --
1251 --
1252 cursor c_instance
1253 (c_business_group_id NUMBER
1254 ,c_effective_date DATE
1255 )
1256 is
1257 SELECT tab1.elig_to_prte_rsn_id
1258 ,tab1.effective_start_date
1259 ,tab1.effective_end_date
1260 ,tab1.ler_id
1261 ,tab1.pgm_id
1262 ,tab1.ptip_id
1263 ,tab1.plip_id
1264 ,tab1.pl_id
1265 ,tab1.oipl_id
1266 ,tab1.wait_perd_dt_to_use_cd
1267 ,tab1.wait_perd_dt_to_use_rl
1268 ,tab1.wait_perd_rl
1269 ,tab1.wait_perd_uom
1270 ,tab1.wait_perd_val
1271 ,tab1.prtn_eff_strt_dt_rl
1272 ,tab1.prtn_eff_end_dt_rl
1273 ,tab1.prtn_eff_strt_dt_cd
1274 ,tab1.prtn_eff_end_dt_cd
1275 ,tab1.elig_inelig_cd
1276 ,tab1.ignr_prtn_ovrid_flag
1277 ,tab1.vrfy_fmly_mmbr_cd
1278 ,tab1.vrfy_fmly_mmbr_rl
1279 FROM ben_elig_to_prte_rsn_f TAB1
1280 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
1281 AND c_effective_date
1282 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
1283 --
1284 begin
1285 --
1286 for objinst in c_instance
1287 (c_business_group_id => p_business_group_id
1288 ,c_effective_date => p_effective_date
1289 )
1290 loop
1291 --
1292 l_hv := ben_hash_utility.get_hashed_index(p_id => nvl(objinst.ler_id,1)
1293 +nvl(objinst.pgm_id,1)+nvl(objinst.ptip_id,1)+nvl(objinst.plip_id,1)
1294 +nvl(objinst.pl_id,1)+nvl(objinst.oipl_id,1));
1295 --
1296 while ben_cobj_cache.g_etpr_instance.exists(l_hv)
1297 loop
1298 --
1299 l_hv := l_hv+g_hash_jump;
1300 --
1301 end loop;
1302 --
1303 ben_cobj_cache.g_etpr_instance(l_hv) := objinst;
1304 --
1305 end loop;
1306 --
1307 end write_etpr_cache;
1308 --
1309 procedure get_etpr_dets
1310 (p_business_group_id in number
1311 ,p_effective_date in date
1312 ,p_ler_id in number default null
1313 ,p_pgm_id in number default null
1314 ,p_ptip_id in number default null
1315 ,p_plip_id in number default null
1316 ,p_pl_id in number default null
1317 ,p_oipl_id in number default null
1318 ,p_inst_row in out NOCOPY g_etpr_inst_row
1319 )
1320 is
1321 --
1322 l_proc varchar2(72) := 'get_etpr_dets';
1323 --
1324 l_hv pls_integer;
1325 l_reset g_etpr_inst_row;
1326 --
1327 begin
1328 --
1329 -- check comp object type
1330 --
1331 if not g_etpr_cached
1332 then
1333 --
1334 -- Build the cache
1335 --
1336 write_etpr_cache
1337 (p_business_group_id => p_business_group_id
1338 ,p_effective_date => p_effective_date
1339 );
1340 --
1341 g_etpr_cached := TRUE;
1342 --
1343 end if;
1344 --
1345 -- Get the instance details
1346 --
1347 l_hv := ben_hash_utility.get_hashed_index(p_id => nvl(p_ler_id,1)
1348 +nvl(p_pgm_id,1)+nvl(p_ptip_id,1)+nvl(p_plip_id,1)+nvl(p_pl_id,1)
1349 +nvl(p_oipl_id,1));
1350 --
1351 if nvl(g_etpr_instance(l_hv).ler_id,-1) = nvl(p_ler_id,-1)
1352 and nvl(g_etpr_instance(l_hv).pgm_id,-1) = nvl(p_pgm_id,-1)
1353 and nvl(g_etpr_instance(l_hv).ptip_id,-1) = nvl(p_ptip_id,-1)
1354 and nvl(g_etpr_instance(l_hv).plip_id,-1) = nvl(p_plip_id,-1)
1355 and nvl(g_etpr_instance(l_hv).pl_id,-1) = nvl(p_pl_id,-1)
1356 and nvl(g_etpr_instance(l_hv).oipl_id,-1) = nvl(p_oipl_id,-1)
1357 then
1358 -- Matched row
1359 null;
1360 --
1361 else
1362 --
1363 -- Loop through the hash using the jump routine to check further
1364 -- indexes if none exists at current index the NO_DATA_FOUND expection
1365 -- will fire
1366 --
1367 l_hv := l_hv+g_hash_jump;
1368 --
1369 loop
1370 --
1371 if nvl(g_etpr_instance(l_hv).ler_id,-1) = nvl(p_ler_id,-1)
1372 and nvl(g_etpr_instance(l_hv).pgm_id,-1) = nvl(p_pgm_id,-1)
1373 and nvl(g_etpr_instance(l_hv).ptip_id,-1) = nvl(p_ptip_id,-1)
1374 and nvl(g_etpr_instance(l_hv).plip_id,-1) = nvl(p_plip_id,-1)
1375 and nvl(g_etpr_instance(l_hv).pl_id,-1) = nvl(p_pl_id,-1)
1376 and nvl(g_etpr_instance(l_hv).oipl_id,-1) = nvl(p_oipl_id,-1)
1377 then
1378 --
1379 exit;
1380 --
1381 else
1382 --
1383 l_hv := l_hv+g_hash_jump;
1384 --
1385 end if;
1386 --
1387 end loop;
1388 --
1389 end if;
1390 --
1391 p_inst_row := g_etpr_instance(l_hv);
1392 --
1393 exception
1394 --
1395 when no_data_found then
1396 --
1397 p_inst_row := l_reset;
1398 --
1399 end get_etpr_dets;
1400 --
1401 procedure write_prel_cache
1402 (p_business_group_id in number
1403 ,p_effective_date in date
1404 )
1405 is
1406 --
1407 l_hv pls_integer;
1408 --
1409 cursor c_instance
1410 (c_business_group_id NUMBER
1411 ,c_effective_date DATE
1412 )
1413 is
1414 SELECT tab1.prtn_elig_id,
1415 tab1.effective_start_date,
1416 tab1.effective_end_date,
1417 tab1.pgm_id,
1418 tab1.ptip_id,
1419 tab1.plip_id,
1420 tab1.pl_id,
1421 tab1.oipl_id,
1422 tab1.wait_perd_dt_to_use_cd,
1426 tab1.wait_perd_val,
1423 tab1.wait_perd_dt_to_use_rl,
1424 tab1.wait_perd_rl,
1425 tab1.wait_perd_uom,
1427 tab1.prtn_eff_strt_dt_rl,
1428 tab1.prtn_eff_end_dt_rl,
1429 tab1.prtn_eff_strt_dt_cd,
1430 tab1.prtn_eff_end_dt_cd
1431 FROM ben_prtn_elig_f TAB1
1432 WHERE TAB1.BUSINESS_GROUP_ID = c_business_group_id
1433 AND c_effective_date
1434 BETWEEN TAB1.EFFECTIVE_START_DATE AND TAB1.EFFECTIVE_END_DATE;
1435 --
1436 begin
1437 --
1438 for objinst in c_instance
1439 (c_business_group_id => p_business_group_id
1440 ,c_effective_date => p_effective_date
1441 )
1442 loop
1443 --
1444 l_hv := ben_hash_utility.get_hashed_index(p_id => nvl(objinst.pgm_id,1)
1445 +nvl(objinst.ptip_id,1)+nvl(objinst.plip_id,1)+nvl(objinst.pl_id,1)
1446 +nvl(objinst.oipl_id,1));
1447 --
1448 while ben_cobj_cache.g_prel_instance.exists(l_hv)
1449 loop
1450 --
1451 l_hv := l_hv+g_hash_jump;
1452 --
1453 end loop;
1454 --
1455 ben_cobj_cache.g_prel_instance(l_hv) := objinst;
1456 --
1457 end loop;
1458 --
1459 end write_prel_cache;
1460 --
1461 procedure get_prel_dets
1462 (p_business_group_id in number
1463 ,p_effective_date in date
1464 ,p_pgm_id in number default null
1465 ,p_ptip_id in number default null
1466 ,p_plip_id in number default null
1467 ,p_pl_id in number default null
1468 ,p_oipl_id in number default null
1469 ,p_inst_row in out NOCOPY g_prel_inst_row
1470 )
1471 is
1472 --
1473 l_proc varchar2(72) := 'get_prel_dets';
1474 --
1475 l_reset g_prel_inst_row;
1476 l_hv pls_integer;
1477 --
1478 begin
1479 --
1480 -- check comp object type
1481 --
1482 if not g_prel_cached
1483 then
1484 --
1485 -- Build the cache
1486 --
1487 write_prel_cache
1488 (p_business_group_id => p_business_group_id
1489 ,p_effective_date => p_effective_date
1490 );
1491 --
1492 g_prel_cached := TRUE;
1493 --
1494 end if;
1495 --
1496 -- Get the instance details
1497 --
1498 l_hv := ben_hash_utility.get_hashed_index(p_id => nvl(p_pgm_id,1)
1499 +nvl(p_ptip_id,1)+nvl(p_plip_id,1)+nvl(p_pl_id,1)
1500 +nvl(p_oipl_id,1));
1501 --
1502 if nvl(g_prel_instance(l_hv).pgm_id,-1) = nvl(p_pgm_id,-1)
1503 and nvl(g_prel_instance(l_hv).ptip_id,-1) = nvl(p_ptip_id,-1)
1504 and nvl(g_prel_instance(l_hv).plip_id,-1) = nvl(p_plip_id,-1)
1505 and nvl(g_prel_instance(l_hv).pl_id,-1) = nvl(p_pl_id,-1)
1506 and nvl(g_prel_instance(l_hv).oipl_id,-1) = nvl(p_oipl_id,-1)
1507 then
1508 -- Matched row
1509 --
1510 null;
1511 --
1512 else
1513 --
1514 -- Loop through the hash using the jump routine to check further
1515 -- indexes if none exists at current index the NO_DATA_FOUND expection
1516 -- will fire
1517 --
1518 l_hv := l_hv+g_hash_jump;
1519 --
1520 loop
1521 --
1522 if nvl(g_prel_instance(l_hv).pgm_id,-1) = nvl(p_pgm_id,-1)
1523 and nvl(g_prel_instance(l_hv).ptip_id,-1) = nvl(p_ptip_id,-1)
1524 and nvl(g_prel_instance(l_hv).plip_id,-1) = nvl(p_plip_id,-1)
1525 and nvl(g_prel_instance(l_hv).pl_id,-1) = nvl(p_pl_id,-1)
1526 and nvl(g_prel_instance(l_hv).oipl_id,-1) = nvl(p_oipl_id,-1)
1527 then
1528 --
1529 exit;
1530 --
1531 else
1532 --
1533 l_hv := l_hv+g_hash_jump;
1534 --
1535 end if;
1536 --
1537 end loop;
1538 --
1539 end if;
1540 --
1541 p_inst_row := g_prel_instance(l_hv);
1542 --
1543 exception
1544 --
1545 when no_data_found then
1546 --
1547 p_inst_row := l_reset;
1548 --
1549 end get_prel_dets;
1550 --
1551 ------------------------------------------------------------------------
1552 -- DELETE ALL CACHED DATA
1553 ------------------------------------------------------------------------
1554 procedure clear_down_cache
1555 is
1556
1557 l_pgm_currow g_pgm_inst_row;
1558 l_ptip_currow g_ptip_inst_row;
1559 l_plip_currow g_plip_inst_row;
1560 l_pl_currow g_pl_inst_row;
1561 l_oiplip_currow g_oiplip_inst_row;
1562 l_oipl_currow g_oipl_inst_row;
1563 l_opt_currow g_opt_inst_row;
1564 l_etpr_currow g_etpr_inst_row;
1565 l_prel_currow g_prel_inst_row;
1566
1567 begin
1568 --
1569 g_oiplip_instance.delete;
1570 g_oiplip_currow := l_oiplip_currow;
1571 g_oiplip_cached := 1;
1572 --
1573 g_opt_instance.delete;
1574 g_opt_currow := l_opt_currow;
1575 g_opt_cached := 1;
1576 --
1577 g_oipl_instance.delete;
1578 g_oipl_currow := l_oipl_currow;
1579 g_oipl_cached := 1;
1580 --
1581 g_pgm_instance.delete;
1582 g_pgm_currow := l_pgm_currow;
1583 g_pgm_cached := 1;
1584 --
1585 g_ptip_instance.delete;
1586 g_ptip_currow := l_ptip_currow;
1587 g_ptip_cached := 1;
1588 --
1589 g_plip_instance.delete;
1590 g_plip_currow := l_plip_currow;
1591 g_plip_cached := 1;
1592 --
1593 g_pl_instance.delete;
1594 g_pl_currow := l_pl_currow;
1595 g_pl_cached := 1;
1596 --
1597 g_etpr_instance.delete;
1598 g_etpr_cached := FALSE;
1599 --
1600 g_pgmetpr_currow := l_etpr_currow;
1601 g_ptipetpr_currow := l_etpr_currow;
1602 g_plipetpr_currow := l_etpr_currow;
1603 g_pletpr_currow := l_etpr_currow;
1604 g_oipletpr_currow := l_etpr_currow;
1605 --
1606 g_prel_instance.delete;
1607 g_prel_cached := FALSE;
1608 g_pgmprel_currow := l_prel_currow;
1609 g_ptipprel_currow := l_prel_currow;
1610 g_plipprel_currow := l_prel_currow;
1611 g_plprel_currow := l_prel_currow;
1612 g_oiplprel_currow := l_prel_currow;
1613 --
1614 end clear_down_cache;
1615 --
1616 procedure set_no_cache_context
1617 is
1618
1619 l_pgm_currow g_pgm_inst_row;
1620 l_ptip_currow g_ptip_inst_row;
1621 l_plip_currow g_plip_inst_row;
1622 l_pl_currow g_pl_inst_row;
1623 l_oiplip_currow g_oiplip_inst_row;
1624 l_oipl_currow g_oipl_inst_row;
1625 l_opt_currow g_opt_inst_row;
1626 l_etpr_currow g_etpr_inst_row;
1627 l_prel_currow g_prel_inst_row;
1628
1629 begin
1630 g_oiplip_instance.delete;
1631 g_oiplip_currow := l_oiplip_currow;
1632 g_oiplip_cached := 0;
1633 --
1634 g_opt_instance.delete;
1635 g_opt_currow := l_opt_currow;
1636 g_opt_cached := 0;
1637 --
1638 g_oipl_instance.delete;
1639 g_oipl_currow := l_oipl_currow;
1640 g_oipl_cached := 0;
1641 --
1642 g_pgm_instance.delete;
1643 g_pgm_currow := l_pgm_currow;
1644 g_pgm_cached := 0;
1645 --
1646 g_ptip_instance.delete;
1647 g_ptip_currow := l_ptip_currow;
1648 g_ptip_cached := 0;
1649 --
1650 g_plip_instance.delete;
1651 g_plip_currow := l_plip_currow;
1652 g_plip_cached := 0;
1653 --
1654 g_pl_instance.delete;
1655 g_pl_currow := l_pl_currow;
1656 g_pl_cached := 0;
1657 --
1658 g_etpr_instance.delete;
1659 g_etpr_cached := FALSE;
1660 --
1661 g_pgmetpr_currow := l_etpr_currow;
1662 g_ptipetpr_currow := l_etpr_currow;
1663 g_plipetpr_currow := l_etpr_currow;
1664 g_pletpr_currow := l_etpr_currow;
1665 g_oipletpr_currow := l_etpr_currow;
1666 --
1667 g_prel_instance.delete;
1668 g_prel_cached := FALSE;
1669 g_pgmprel_currow := l_prel_currow;
1670 g_ptipprel_currow := l_prel_currow;
1671 g_plipprel_currow := l_prel_currow;
1672 g_plprel_currow := l_prel_currow;
1673 g_oiplprel_currow := l_prel_currow;
1674 --
1675 end set_no_cache_context;
1676 --
1677 end ben_cobj_cache;