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