1 package body ben_cel_cache as
2 /* $Header: bencelch.pkb 115.9 2002/12/24 15:44:13 bmanyam ship $ */
3 --
4 g_package varchar2(50) := 'ben_cel_cache.';
5 --
6 --
7 -- Hand coded
8 --
9 -- plan participating eligibility profile by plan
10 --
11 g_cache_plnelp_lookup ben_cache.g_cache_lookup_table;
12 g_cache_plnelp_inst ben_cel_cache.g_cache_cepelp_instor;
13 --
14 -- program participating eligibility profile by program
15 --
16 g_cache_pgmelp_lookup ben_cache.g_cache_lookup_table;
17 g_cache_pgmelp_inst ben_cel_cache.g_cache_cepelp_instor;
18 --
19 -- oipl participating eligibility profile by oipl
20 --
21 g_cache_copelp_lookup ben_cache.g_cache_lookup_table;
22 g_cache_copelp_inst ben_cel_cache.g_cache_cepelp_instor;
23 --
24 -- plip participating eligibility profile by plip
25 --
26 g_cache_cppelp_lookup ben_cache.g_cache_lookup_table;
27 g_cache_cppelp_inst ben_cel_cache.g_cache_cepelp_instor;
28 --
29 -- ptip participating eligibility profile by ptip
30 --
31 g_cache_ctpelp_lookup ben_cache.g_cache_lookup_table;
32 g_cache_ctpelp_inst ben_cel_cache.g_cache_cepelp_instor;
33 --
34 procedure plnelp_writecache
35 (p_effective_date in date,
36 p_refresh_cache in boolean default FALSE) is
37 --
38 l_proc varchar2(72) := g_package||'plnelp_writecache';
39 l_torrwnum binary_integer;
40 l_prev_id number;
41 l_id number;
42 l_not_hash_found boolean;
43 --
44 cursor c_plnelp_look is
45 select pln.pl_id,
46 pln.business_group_id
47 from ben_pl_f pln
48 where p_effective_date
49 between pln.effective_start_date
50 and pln.effective_end_date
51 and exists(select null
52 from ben_prtn_elig_f epa,
53 ben_prtn_elig_prfl_f cep,
54 ben_eligy_prfl_f elp
55 where elp.eligy_prfl_id = cep.eligy_prfl_id
56 and elp.business_group_id = cep.business_group_id
57 and cep.prtn_elig_id = epa.prtn_elig_id
58 and cep.business_group_id = epa.business_group_id
59 and p_effective_date
60 between elp.effective_start_date
61 and elp.effective_end_date
62 and p_effective_date
63 between epa.effective_start_date
64 and epa.effective_end_date
65 and p_effective_date
66 between cep.effective_start_date
67 and cep.effective_end_date
68 and epa.pl_id = pln.pl_id)
69 order by pln.pl_id;
70 --
71 cursor c_plnelp_inst is
72 select epa.pl_id,
73 epa.prtn_elig_id,
74 cep.mndtry_flag,
75 elp.eligy_prfl_id
76 from ben_prtn_elig_f epa,
77 ben_prtn_elig_prfl_f cep,
78 ben_eligy_prfl_f elp
79 where elp.eligy_prfl_id = cep.eligy_prfl_id
80 and elp.business_group_id = cep.business_group_id
81 and cep.prtn_elig_id = epa.prtn_elig_id
82 and cep.business_group_id = epa.business_group_id
83 and epa.pl_id is not null
84 and p_effective_date
85 between elp.effective_start_date
86 and elp.effective_end_date
87 and p_effective_date
88 between epa.effective_start_date
89 and epa.effective_end_date
90 and p_effective_date
91 between cep.effective_start_date
92 and cep.effective_end_date
93 order by epa.pl_id, decode(cep.mndtry_flag,'y',1,2);
94 --
95 begin
96 --
97 for objlook in c_plnelp_look loop
98 --
99 l_id := ben_hash_utility.get_hashed_index(p_id => objlook.pl_id);
100 --
101 -- Check if hashed value is already allocated
102 --
103 if g_cache_plnelp_lookup.exists(l_id) then
104 --
105 l_not_hash_found := false;
106 --
107 -- Loop until un-allocated has value is derived
108 --
109 while not l_not_hash_found loop
110 --
111 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
112 --
113 -- Check if the hash index exists, if not we can use it
114 --
115 if not g_cache_plnelp_lookup.exists(l_id) then
116 --
117 -- Lets store the hash value in the index
118 --
119 l_not_hash_found := true;
120 commit;
121 exit;
122 --
123 else
124 --
125 l_not_hash_found := false;
126 --
127 end if;
128 --
129 end loop;
130 --
131 end if;
132 --
133 g_cache_plnelp_lookup(l_id).id := objlook.pl_id;
134 g_cache_plnelp_lookup(l_id).fk_id := objlook.business_group_id;
135 --
136 end loop;
137 --
138 l_torrwnum := 0;
139 l_prev_id := -1;
140 --
141 for objinst in c_plnelp_inst loop
142 --
143 -- Populate the cache lookup details
144 --
145 l_id := ben_hash_utility.get_hashed_index(p_id => objinst.pl_id);
146 --
147 -- Check if hashed value is already allocated
148 --
149 if g_cache_plnelp_inst.exists(l_id) then
150 --
151 l_not_hash_found := false;
152 --
153 -- Loop until un-allocated has value is derived
154 --
155 while not l_not_hash_found loop
156 --
157 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
158 --
159 -- Check if the hash index exists, if not we can use it
160 --
161 if not g_cache_plnelp_inst.exists(l_id) then
162 --
163 -- Lets store the hash value in the index
164 --
165 l_not_hash_found := true;
166 commit;
167 exit;
168 --
169 else
170 --
171 l_not_hash_found := false;
172 --
173 end if;
174 --
175 end loop;
176 --
177 end if;
178 --
179 -- Check for first row
180 --
181 if l_prev_id = -1 then
182 --
183 g_cache_plnelp_lookup(l_id).starttorele_num := l_torrwnum;
184 --
185 elsif l_id <> l_prev_id then
186 --
187 g_cache_plnelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
188 g_cache_plnelp_lookup(l_id).starttorele_num := l_torrwnum;
189 --
190 end if;
191 --
192 -- Populate the cache instance details
193 --
194 g_cache_plnelp_inst(l_torrwnum).pl_id := objinst.pl_id;
195 g_cache_plnelp_inst(l_torrwnum).prtn_elig_id := objinst.prtn_elig_id;
196 g_cache_plnelp_inst(l_torrwnum).mndtry_flag := objinst.mndtry_flag;
197 g_cache_plnelp_inst(l_torrwnum).eligy_prfl_id := objinst.eligy_prfl_id;
198 --
199 l_torrwnum := l_torrwnum+1;
200 l_prev_id := l_id;
201 --
202 end loop;
203 --
204 g_cache_plnelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
205 --
206 end plnelp_writecache;
207 --
208 procedure plnelp_getcacdets
209 (p_effective_date in date,
210 p_business_group_id in number,
211 p_pl_id in number,
212 p_refresh_cache in boolean default FALSE,
213 p_inst_set out nocopy ben_cel_cache.g_cache_cepelp_instor,
214 p_inst_count out nocopy number) is
215 --
216 l_proc varchar2(72) := g_package||'plnelp_getcacdets';
217 l_torrwnum binary_integer;
218 l_insttorrw_num binary_integer;
219 l_index binary_integer;
220 l_not_hash_found boolean;
221 --
222 begin
223 --
224 -- Flush the cache
225 --
226 if p_refresh_cache then
227 --
228 g_cache_plnelp_lookup.delete;
229 g_cache_plnelp_inst.delete;
230 --
231 end if;
232 --
233 -- Populate the global cache
234 --
235 if g_cache_plnelp_lookup.count = 0 then
236 --
237 -- Build the cache
238 --
239 ben_cel_cache.plnelp_writecache
240 (p_effective_date => p_effective_date,
241 p_refresh_cache => p_refresh_cache);
242 --
243 end if;
244 --
245 -- Get the instance details
246 --
247 l_torrwnum := 0;
248 l_index := ben_hash_utility.get_hashed_index(p_id => p_pl_id);
249 --
250 -- Check if hashed value is already allocated
251 --
252 if g_cache_plnelp_lookup.exists(l_index) then
253 --
254 -- If it does exist make sure its the right one
255 --
256 if g_cache_plnelp_lookup(l_index).id <> p_pl_id then
257 --
258 l_not_hash_found := false;
259 --
260 -- Loop until un-allocated has value is derived
261 --
262 while not l_not_hash_found loop
263 --
264 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
265 --
266 -- Check if the hash index exists, if not we can use it
267 --
268 if not g_cache_plnelp_lookup.exists(l_index) then
269 --
270 -- Lets store the hash value in the index
271 --
272 l_not_hash_found := true;
273 commit;
274 exit;
275 --
276 else
277 --
278 l_not_hash_found := false;
279 --
280 end if;
281 --
282 end loop;
283 --
284 end if;
285 --
286 end if;
287 --
288 for l_insttorrw_num in g_cache_plnelp_lookup(l_index).starttorele_num ..
289 g_cache_plnelp_lookup(l_index).endtorele_num loop
290 --
291 p_inst_set(l_torrwnum) := g_cache_plnelp_inst(l_insttorrw_num);
292 l_torrwnum := l_torrwnum+1;
293 --
294 end loop;
295 --
296 p_inst_count := l_torrwnum;
297 --
298 exception
299 --
300 when no_data_found then
301 --
302 p_inst_count := 0;
303 --
304 end plnelp_getcacdets;
305 --
306 procedure pgmelp_writecache
307 (p_effective_date in date,
308 p_refresh_cache in boolean default FALSE) is
309 --
310 l_proc varchar2(72) := g_package||'pgmelp_writecache';
311 l_torrwnum binary_integer;
312 l_prev_id number;
313 l_id number;
314 l_not_hash_found boolean;
315 --
316 cursor c_pgmelp_look is
317 select pgm.pgm_id, pgm.business_group_id
318 from ben_pgm_f pgm
319 where p_effective_date
320 between pgm.effective_start_date
321 and pgm.effective_end_date
322 and exists(select null
323 from ben_prtn_elig_f epa,
324 ben_prtn_elig_prfl_f cep,
325 ben_eligy_prfl_f elp
326 where elp.eligy_prfl_id = cep.eligy_prfl_id
327 and elp.business_group_id = cep.business_group_id
328 and cep.prtn_elig_id = epa.prtn_elig_id
329 and cep.business_group_id = epa.business_group_id
330 and p_effective_date
331 between elp.effective_start_date
332 and elp.effective_end_date
333 and p_effective_date
334 between epa.effective_start_date
335 and epa.effective_end_date
336 and p_effective_date
337 between cep.effective_start_date
338 and cep.effective_end_date
339 and epa.pgm_id = pgm.pgm_id)
340 order by pgm.pgm_id;
341 --
342 cursor c_pgmelp_inst is
343 select epa.pgm_id,
344 epa.prtn_elig_id,
345 cep.mndtry_flag,
346 elp.eligy_prfl_id
347 from ben_prtn_elig_f epa,
348 ben_prtn_elig_prfl_f cep,
349 ben_eligy_prfl_f elp
350 where elp.eligy_prfl_id = cep.eligy_prfl_id
351 and elp.business_group_id = cep.business_group_id
352 and cep.prtn_elig_id = epa.prtn_elig_id
353 and cep.business_group_id = epa.business_group_id
354 and epa.pgm_id is not null
355 and p_effective_date
356 between elp.effective_start_date
357 and elp.effective_end_date
358 and p_effective_date
359 between epa.effective_start_date
360 and epa.effective_end_date
361 and p_effective_date
362 between cep.effective_start_date
363 and cep.effective_end_date
364 order by epa.pgm_id, decode(cep.mndtry_flag,'Y',1,2);
365 --
366 begin
367 --
368 for objlook in c_pgmelp_look loop
369 --
370 l_id := ben_hash_utility.get_hashed_index(p_id => objlook.pgm_id);
371 --
372 -- Check if hashed value is already allocated
373 --
374 if g_cache_pgmelp_lookup.exists(l_id) then
375 --
376 l_not_hash_found := false;
377 --
378 -- Loop until un-allocated has value is derived
379 --
380 while not l_not_hash_found loop
381 --
382 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
383 --
384 -- Check if the hash index exists, if not we can use it
385 --
386 if not g_cache_pgmelp_lookup.exists(l_id) then
387 --
388 -- Lets store the hash value in the index
389 --
390 l_not_hash_found := true;
391 commit;
392 exit;
393 --
394 else
395 --
396 l_not_hash_found := false;
397 --
398 end if;
399 --
400 end loop;
401 --
402 end if;
403 --
404 g_cache_pgmelp_lookup(l_id).id := objlook.pgm_id;
405 g_cache_pgmelp_lookup(l_id).fk_id := objlook.business_group_id;
406 --
407 end loop;
408 --
409 l_torrwnum := 0;
410 l_prev_id := -1;
411 --
412 for objinst in c_pgmelp_inst loop
413 --
414 -- Populate the cache lookup details
415 --
416 l_id := ben_hash_utility.get_hashed_index(p_id => objinst.pgm_id);
417 --
418 -- Check if hashed value is already allocated
419 --
420 if g_cache_pgmelp_inst.exists(l_id) then
421 --
422 l_not_hash_found := false;
423 --
424 -- Loop until un-allocated has value is derived
425 --
426 while not l_not_hash_found loop
427 --
428 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
429 --
430 -- Check if the hash index exists, if not we can use it
431 --
432 if not g_cache_pgmelp_inst.exists(l_id) then
433 --
434 -- Lets store the hash value in the index
435 --
436 l_not_hash_found := true;
437 commit;
438 exit;
439 --
440 else
441 --
442 l_not_hash_found := false;
443 --
444 end if;
445 --
446 end loop;
447 --
448 end if;
449 --
450 --
451 -- Check for first row
452 --
453 if l_prev_id = -1 then
454 --
455 g_cache_pgmelp_lookup(l_id).starttorele_num := l_torrwnum;
456 --
457 elsif l_id <> l_prev_id then
458 --
459 g_cache_pgmelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
460 g_cache_pgmelp_lookup(l_id).starttorele_num := l_torrwnum;
461 --
462 end if;
463 --
464 -- Populate the cache instance details
465 --
466 g_cache_pgmelp_inst(l_torrwnum).pgm_id := objinst.pgm_id;
467 g_cache_pgmelp_inst(l_torrwnum).prtn_elig_id := objinst.prtn_elig_id;
468 g_cache_pgmelp_inst(l_torrwnum).mndtry_flag := objinst.mndtry_flag;
472 l_prev_id := l_id;
469 g_cache_pgmelp_inst(l_torrwnum).eligy_prfl_id := objinst.eligy_prfl_id;
470 --
471 l_torrwnum := l_torrwnum+1;
473 --
474 end loop;
475 --
476 g_cache_pgmelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
477 --
478 end pgmelp_writecache;
479 --
480 procedure pgmelp_getcacdets
481 (p_effective_date in date,
482 p_business_group_id in number,
483 p_pgm_id in number,
484 p_refresh_cache in boolean default FALSE,
485 p_inst_set out nocopy ben_cel_cache.g_cache_cepelp_instor,
486 p_inst_count out nocopy number) is
487 --
488 l_proc varchar2(72) := g_package||'pgmelp_getcacdets';
489 l_torrwnum binary_integer;
490 l_insttorrw_num binary_integer;
491 l_index binary_integer;
492 l_not_hash_found boolean;
493 --
494 begin
495 --
496 -- Flush the cache
497 --
498 if p_refresh_cache then
499 --
500 g_cache_pgmelp_lookup.delete;
501 g_cache_pgmelp_inst.delete;
502 --
503 end if;
504 --
505 -- Populate the global cache
506 --
507 if g_cache_pgmelp_lookup.count = 0 then
508 --
509 -- Build the cache
510 --
511 ben_cel_cache.pgmelp_writecache
512 (p_effective_date => p_effective_date,
513 p_refresh_cache => p_refresh_cache);
514 --
515 end if;
516 --
517 -- Get the instance details
518 --
519 l_torrwnum := 0;
520 l_index := ben_hash_utility.get_hashed_index(p_id => p_pgm_id);
521 --
522 -- Check if hashed value is already allocated
523 --
524 if g_cache_pgmelp_lookup.exists(l_index) then
525 --
526 -- If it does exist make sure its the right one
527 --
528 if g_cache_pgmelp_lookup(l_index).id <> p_pgm_id then
529 --
530 l_not_hash_found := false;
531 --
532 -- Loop until un-allocated has value is derived
533 --
534 while not l_not_hash_found loop
535 --
536 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
537 --
538 -- Check if the hash index exists, if not we can use it
539 --
540 if not g_cache_pgmelp_lookup.exists(l_index) then
541 --
542 -- Lets store the hash value in the index
543 --
544 l_not_hash_found := true;
545 commit;
546 exit;
547 --
548 else
549 --
550 l_not_hash_found := false;
551 --
552 end if;
553 --
554 end loop;
555 --
556 end if;
557 --
558 end if;
559 --
560 for l_insttorrw_num in g_cache_pgmelp_lookup(l_index).starttorele_num ..
561 g_cache_pgmelp_lookup(l_index).endtorele_num loop
562 --
563 p_inst_set(l_torrwnum) := g_cache_pgmelp_inst(l_insttorrw_num);
564 l_torrwnum := l_torrwnum+1;
565 --
566 end loop;
567 --
568 p_inst_count := l_torrwnum;
569 --
570 exception
571 --
572 when no_data_found then
573 --
574 p_inst_count := 0;
575 --
576 end pgmelp_getcacdets;
577 --
578 procedure copelp_writecache
579 (p_effective_date in date,
580 p_refresh_cache in boolean default FALSE) is
581 --
582 l_proc varchar2(72) := g_package||'copelp_writecache';
583 l_torrwnum binary_integer;
584 l_prev_id number;
585 l_id number;
586 l_not_hash_found boolean;
587 --
588 cursor c_copelp_look is
589 select cop.oipl_id,
590 cop.business_group_id
591 from ben_oipl_f cop
592 where p_effective_date
593 between cop.effective_start_date
594 and cop.effective_end_date
595 and exists(select null
596 from ben_prtn_elig_f epa,
597 ben_prtn_elig_prfl_f cep,
598 ben_eligy_prfl_f elp
599 where elp.eligy_prfl_id = cep.eligy_prfl_id
600 and elp.business_group_id = cep.business_group_id
601 and cep.prtn_elig_id = epa.prtn_elig_id
602 and cep.business_group_id = epa.business_group_id
603 and p_effective_date
604 between elp.effective_start_date
605 and elp.effective_end_date
606 and p_effective_date
607 between epa.effective_start_date
608 and epa.effective_end_date
609 and p_effective_date
610 between cep.effective_start_date
611 and cep.effective_end_date
612 and epa.oipl_id = cop.oipl_id)
613 order by cop.oipl_id;
614 --
615 cursor c_copelp_inst is
616 select epa.oipl_id,
617 epa.prtn_elig_id,
618 cep.mndtry_flag,
619 elp.eligy_prfl_id
620 from ben_prtn_elig_f epa,
621 ben_prtn_elig_prfl_f cep,
622 ben_eligy_prfl_f elp
623 where elp.eligy_prfl_id = cep.eligy_prfl_id
624 and elp.business_group_id = cep.business_group_id
625 and cep.prtn_elig_id = epa.prtn_elig_id
626 and cep.business_group_id = epa.business_group_id
627 and epa.oipl_id is not null
628 and p_effective_date
629 between elp.effective_start_date
630 and elp.effective_end_date
631 and p_effective_date
632 between epa.effective_start_date
633 and epa.effective_end_date
634 and p_effective_date
638 --
635 between cep.effective_start_date
636 and cep.effective_end_date
637 order by epa.oipl_id, decode(cep.mndtry_flag,'Y',1,2);
639 begin
640 --
641 for objlook in c_copelp_look loop
642 --
643 l_id := ben_hash_utility.get_hashed_index(p_id => objlook.oipl_id);
644 --
645 -- Check if hashed value is already allocated
646 --
647 if g_cache_copelp_lookup.exists(l_id) then
648 --
649 l_not_hash_found := false;
650 --
651 -- Loop until un-allocated has value is derived
652 --
653 while not l_not_hash_found loop
654 --
655 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
656 --
657 -- Check if the hash index exists, if not we can use it
658 --
659 if not g_cache_copelp_lookup.exists(l_id) then
660 --
661 -- Lets store the hash value in the index
662 --
663 l_not_hash_found := true;
664 commit;
665 exit;
666 --
667 else
668 --
669 l_not_hash_found := false;
670 --
671 end if;
672 --
673 end loop;
674 --
675 end if;
676 --
677 g_cache_copelp_lookup(l_id).id := objlook.oipl_id;
678 g_cache_copelp_lookup(l_id).fk_id := objlook.business_group_id;
679 --
680 end loop;
681 --
682 l_torrwnum := 0;
683 l_prev_id := -1;
684 --
685 for objinst in c_copelp_inst loop
686 --
687 -- Populate the cache lookup details
688 --
689 l_id := ben_hash_utility.get_hashed_index(p_id => objinst.oipl_id);
690 --
691 -- Check if hashed value is already allocated
692 --
693 if g_cache_copelp_inst.exists(l_id) then
694 --
695 l_not_hash_found := false;
696 --
697 -- Loop until un-allocated has value is derived
698 --
699 while not l_not_hash_found loop
700 --
701 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
702 --
703 -- Check if the hash index exists, if not we can use it
704 --
705 if not g_cache_copelp_inst.exists(l_id) then
706 --
707 -- Lets store the hash value in the index
708 --
709 l_not_hash_found := true;
710 commit;
711 exit;
712 --
713 else
714 --
715 l_not_hash_found := false;
716 --
717 end if;
718 --
719 end loop;
720 --
721 end if;
722 --
723 -- Check for first row
724 --
725 if l_prev_id = -1 then
726 --
727 g_cache_copelp_lookup(l_id).starttorele_num := l_torrwnum;
728 --
729 elsif l_id <> l_prev_id then
730 --
731 g_cache_copelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
732 g_cache_copelp_lookup(l_id).starttorele_num := l_torrwnum;
733 --
734 end if;
735 --
736 -- Populate the cache instance details
737 --
738 g_cache_copelp_inst(l_torrwnum).oipl_id := objinst.oipl_id;
739 g_cache_copelp_inst(l_torrwnum).prtn_elig_id := objinst.prtn_elig_id;
740 g_cache_copelp_inst(l_torrwnum).mndtry_flag := objinst.mndtry_flag;
741 g_cache_copelp_inst(l_torrwnum).eligy_prfl_id := objinst.eligy_prfl_id;
742 --
743 l_torrwnum := l_torrwnum+1;
744 l_prev_id := l_id;
745 --
746 end loop;
747 --
748 g_cache_copelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
749 --
750 end copelp_writecache;
751 --
752 procedure copelp_getcacdets
753 (p_effective_date in date,
754 p_business_group_id in number,
755 p_oipl_id in number,
756 p_refresh_cache in boolean default FALSE,
757 p_inst_set out nocopy ben_cel_cache.g_cache_cepelp_instor,
758 p_inst_count out nocopy number) is
759 --
760 l_proc varchar2(72) := g_package||'copelp_getcacdets';
761 l_torrwnum binary_integer;
762 l_insttorrw_num binary_integer;
763 l_index binary_integer;
764 l_not_hash_found boolean;
765 --
766 begin
767 --
768 -- Flush the cache
769 --
770 if p_refresh_cache then
771 --
772 g_cache_copelp_lookup.delete;
773 g_cache_copelp_inst.delete;
774 --
775 end if;
776 --
777 -- Populate the global cache
778 --
779 if g_cache_copelp_lookup.count = 0 then
780 --
781 -- Build the cache
782 --
783 ben_cel_cache.copelp_writecache
784 (p_effective_date => p_effective_date,
785 p_refresh_cache => p_refresh_cache);
786 --
787 end if;
788 --
789 -- Get the instance details
790 --
791 l_torrwnum := 0;
792 l_index := ben_hash_utility.get_hashed_index(p_id => p_oipl_id);
793 --
794 -- Check if hashed value is already allocated
795 --
796 if g_cache_copelp_lookup.exists(l_index) then
797 --
798 -- If it does exist make sure its the right one
799 --
800 if g_cache_copelp_lookup(l_index).id <> p_oipl_id then
801 --
802 l_not_hash_found := false;
803 --
804 -- Loop until un-allocated has value is derived
805 --
806 while not l_not_hash_found loop
807 --
808 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
809 --
810 -- Check if the hash index exists, if not we can use it
814 -- Lets store the hash value in the index
811 --
812 if not g_cache_copelp_lookup.exists(l_index) then
813 --
815 --
816 l_not_hash_found := true;
817 commit;
818 exit;
819 --
820 else
821 --
822 l_not_hash_found := false;
823 --
824 end if;
825 --
826 end loop;
827 --
828 end if;
829 --
830 end if;
831 --
832 for l_insttorrw_num in g_cache_copelp_lookup(l_index).starttorele_num ..
833 g_cache_copelp_lookup(l_index).endtorele_num loop
834 --
835 p_inst_set(l_torrwnum) := g_cache_copelp_inst(l_insttorrw_num);
836 l_torrwnum := l_torrwnum+1;
837 --
838 end loop;
839 --
840 p_inst_count := l_torrwnum;
841 --
842 exception
843 --
844 when no_data_found then
845 --
846 p_inst_count := 0;
847 --
848 end copelp_getcacdets;
849 --
850 procedure cppelp_writecache
851 (p_effective_date in date,
852 p_refresh_cache in boolean default FALSE) is
853 --
854 l_proc varchar2(72) := g_package||'cppelp_writecache';
855 l_torrwnum binary_integer;
856 l_prev_id number;
857 l_id number;
858 l_not_hash_found boolean;
859 --
860 cursor c_cppelp_look is
861 select cpp.plip_id,
862 cpp.business_group_id
863 from ben_plip_f cpp
864 where p_effective_date
865 between cpp.effective_start_date
866 and cpp.effective_end_date
867 and exists(select null
868 from ben_prtn_elig_f epa,
869 ben_prtn_elig_prfl_f cep,
870 ben_eligy_prfl_f elp
871 where elp.eligy_prfl_id = cep.eligy_prfl_id
872 and elp.business_group_id = cep.business_group_id
873 and cep.prtn_elig_id = epa.prtn_elig_id
874 and cep.business_group_id = epa.business_group_id
875 and p_effective_date
876 between elp.effective_start_date
877 and elp.effective_end_date
878 and p_effective_date
879 between epa.effective_start_date
880 and epa.effective_end_date
881 and p_effective_date
882 between cep.effective_start_date
883 and cep.effective_end_date
884 and epa.plip_id = cpp.plip_id)
885 order by cpp.plip_id;
886 --
887 cursor c_cppelp_inst is
888 select epa.plip_id,
889 epa.prtn_elig_id,
890 cep.mndtry_flag,
891 elp.eligy_prfl_id
892 from ben_prtn_elig_f epa,
893 ben_prtn_elig_prfl_f cep,
894 ben_eligy_prfl_f elp
895 where elp.eligy_prfl_id = cep.eligy_prfl_id
896 and elp.business_group_id = cep.business_group_id
897 and cep.prtn_elig_id = epa.prtn_elig_id
898 and cep.business_group_id = epa.business_group_id
899 and epa.plip_id is not null
900 and p_effective_date
901 between elp.effective_start_date
902 and elp.effective_end_date
903 and p_effective_date
904 between epa.effective_start_date
905 and epa.effective_end_date
906 and p_effective_date
907 between cep.effective_start_date
908 and cep.effective_end_date
909 order by epa.plip_id, decode(cep.mndtry_flag,'Y',1,2);
910 --
911 begin
912 --
913 for objlook in c_cppelp_look loop
914 --
915 l_id := ben_hash_utility.get_hashed_index(p_id => objlook.plip_id);
916 --
917 -- Check if hashed value is already allocated
918 --
919 if g_cache_cppelp_lookup.exists(l_id) then
920 --
921 l_not_hash_found := false;
922 --
923 -- Loop until un-allocated has value is derived
924 --
925 while not l_not_hash_found loop
926 --
927 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
928 --
929 -- Check if the hash index exists, if not we can use it
930 --
931 if not g_cache_cppelp_lookup.exists(l_id) then
932 --
933 -- Lets store the hash value in the index
934 --
935 l_not_hash_found := true;
936 commit;
937 exit;
938 --
939 else
940 --
941 l_not_hash_found := false;
942 --
943 end if;
944 --
945 end loop;
946 --
947 end if;
948 --
949 g_cache_cppelp_lookup(l_id).id := objlook.plip_id;
950 g_cache_cppelp_lookup(l_id).fk_id := objlook.business_group_id;
951 --
952 end loop;
953 --
954 l_torrwnum := 0;
955 l_prev_id := -1;
956 --
957 for objinst in c_cppelp_inst loop
958 --
959 -- Populate the cache lookup details
960 --
961 l_id := ben_hash_utility.get_hashed_index(p_id => objinst.plip_id);
962 --
963 -- Check if hashed value is already allocated
964 --
965 if g_cache_cppelp_inst.exists(l_id) then
966 --
967 l_not_hash_found := false;
968 --
969 -- Loop until un-allocated has value is derived
970 --
971 while not l_not_hash_found loop
972 --
973 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
974 --
975 -- Check if the hash index exists, if not we can use it
976 --
980 --
977 if not g_cache_cppelp_inst.exists(l_id) then
978 --
979 -- Lets store the hash value in the index
981 l_not_hash_found := true;
982 commit;
983 exit;
984 --
985 else
986 --
987 l_not_hash_found := false;
988 --
989 end if;
990 --
991 end loop;
992 --
993 end if;
994 --
995 -- Check for first row
996 --
997 if l_prev_id = -1 then
998 --
999 g_cache_cppelp_lookup(l_id).starttorele_num := l_torrwnum;
1000 --
1001 elsif l_id <> l_prev_id then
1002 --
1003 g_cache_cppelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
1004 g_cache_cppelp_lookup(l_id).starttorele_num := l_torrwnum;
1005 --
1006 end if;
1007 --
1008 -- Populate the cache instance details
1009 --
1010 g_cache_cppelp_inst(l_torrwnum).plip_id := objinst.plip_id;
1011 g_cache_cppelp_inst(l_torrwnum).prtn_elig_id := objinst.prtn_elig_id;
1012 g_cache_cppelp_inst(l_torrwnum).mndtry_flag := objinst.mndtry_flag;
1013 g_cache_cppelp_inst(l_torrwnum).eligy_prfl_id := objinst.eligy_prfl_id;
1014 --
1015 l_torrwnum := l_torrwnum+1;
1016 l_prev_id := l_id;
1017 --
1018 end loop;
1019 --
1020 g_cache_cppelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
1021 --
1022 end cppelp_writecache;
1023 --
1024 procedure cppelp_getcacdets
1025 (p_effective_date in date,
1026 p_business_group_id in number,
1027 p_plip_id in number,
1028 p_refresh_cache in boolean default FALSE,
1029 p_inst_set out nocopy ben_cel_cache.g_cache_cepelp_instor,
1030 p_inst_count out nocopy number) is
1031 --
1032 l_proc varchar2(72) := g_package||'cppelp_getcacdets';
1033 l_torrwnum binary_integer;
1034 l_insttorrw_num binary_integer;
1035 l_index binary_integer;
1036 l_not_hash_found boolean;
1037 --
1038 begin
1039 --
1040 -- Flush the cache
1041 --
1042 if p_refresh_cache then
1043 --
1044 g_cache_cppelp_lookup.delete;
1045 g_cache_cppelp_inst.delete;
1046 --
1047 end if;
1048 --
1049 -- Populate the global cache
1050 --
1051 if g_cache_cppelp_lookup.count = 0 then
1052 --
1053 -- Build the cache
1054 --
1055 ben_cel_cache.cppelp_writecache
1056 (p_effective_date => p_effective_date,
1057 p_refresh_cache => p_refresh_cache);
1058 --
1059 end if;
1060 --
1061 -- Get the instance details
1062 --
1063 l_torrwnum := 0;
1064 l_index := ben_hash_utility.get_hashed_index(p_id => p_plip_id);
1065 --
1066 -- Check if hashed value is already allocated
1067 --
1068 if g_cache_cppelp_lookup.exists(l_index) then
1069 --
1070 -- If it does exist make sure its the right one
1071 --
1072 if g_cache_cppelp_lookup(l_index).id <> p_plip_id then
1073 --
1074 l_not_hash_found := false;
1075 --
1076 -- Loop until un-allocated has value is derived
1077 --
1078 while not l_not_hash_found loop
1079 --
1080 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
1081 --
1082 -- Check if the hash index exists, if not we can use it
1083 --
1084 if not g_cache_cppelp_lookup.exists(l_index) then
1085 --
1086 -- Lets store the hash value in the index
1087 --
1088 l_not_hash_found := true;
1089 commit;
1090 exit;
1091 --
1092 else
1093 --
1094 l_not_hash_found := false;
1095 --
1096 end if;
1097 --
1098 end loop;
1099 --
1100 end if;
1101 --
1102 end if;
1103 --
1104 for l_insttorrw_num in g_cache_cppelp_lookup(l_index).starttorele_num ..
1105 g_cache_cppelp_lookup(l_index).endtorele_num loop
1106 --
1107 p_inst_set(l_torrwnum) := g_cache_cppelp_inst(l_insttorrw_num);
1108 l_torrwnum := l_torrwnum+1;
1109 --
1110 end loop;
1111 --
1112 p_inst_count := l_torrwnum;
1113 --
1114 exception
1115 --
1116 when no_data_found then
1117 --
1118 p_inst_count := 0;
1119 --
1120 end cppelp_getcacdets;
1121 --
1122 procedure ctpelp_writecache
1123 (p_effective_date in date,
1124 p_refresh_cache in boolean default FALSE) is
1125 --
1126 l_proc varchar2(72) := g_package||'ctpelp_writecache';
1127 l_torrwnum binary_integer;
1128 l_prev_id number;
1129 l_id number;
1130 l_not_hash_found boolean;
1131 --
1132 cursor c_ctpelp_look is
1133 select ctp.ptip_id,
1134 ctp.business_group_id
1135 from ben_ptip_f ctp
1136 where p_effective_date
1137 between ctp.effective_start_date
1138 and ctp.effective_end_date
1139 and exists(select null
1140 from ben_prtn_elig_f epa,
1141 ben_prtn_elig_prfl_f cep,
1142 ben_eligy_prfl_f elp
1143 where elp.eligy_prfl_id = cep.eligy_prfl_id
1144 and elp.business_group_id = cep.business_group_id
1145 and cep.prtn_elig_id = epa.prtn_elig_id
1146 and cep.business_group_id = epa.business_group_id
1147 and p_effective_date
1148 between elp.effective_start_date
1149 and elp.effective_end_date
1150 and p_effective_date
1154 between cep.effective_start_date
1151 between epa.effective_start_date
1152 and epa.effective_end_date
1153 and p_effective_date
1155 and cep.effective_end_date
1156 and epa.ptip_id = ctp.ptip_id)
1157 order by ctp.ptip_id;
1158 --
1159 cursor c_ctpelp_inst is
1160 select epa.ptip_id,
1161 epa.prtn_elig_id,
1162 cep.mndtry_flag,
1163 elp.eligy_prfl_id
1164 from ben_prtn_elig_f epa,
1165 ben_prtn_elig_prfl_f cep,
1166 ben_eligy_prfl_f elp
1167 where elp.eligy_prfl_id = cep.eligy_prfl_id
1168 and elp.business_group_id = cep.business_group_id
1169 and cep.prtn_elig_id = epa.prtn_elig_id
1170 and cep.business_group_id = epa.business_group_id
1171 and epa.ptip_id is not null
1172 and p_effective_date
1173 between elp.effective_start_date
1174 and elp.effective_end_date
1175 and p_effective_date
1176 between epa.effective_start_date
1177 and epa.effective_end_date
1178 and p_effective_date
1179 between cep.effective_start_date
1180 and cep.effective_end_date
1181 order by epa.ptip_id, decode(cep.mndtry_flag,'Y',1,2);
1182 --
1183 begin
1184 --
1185 for objlook in c_ctpelp_look loop
1186 --
1187 l_id := ben_hash_utility.get_hashed_index(p_id => objlook.ptip_id);
1188 --
1189 -- Check if hashed value is already allocated
1190 --
1191 if g_cache_ctpelp_lookup.exists(l_id) then
1192 --
1193 l_not_hash_found := false;
1194 --
1195 -- Loop until un-allocated has value is derived
1196 --
1197 while not l_not_hash_found loop
1198 --
1199 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
1200 --
1201 -- Check if the hash index exists, if not we can use it
1202 --
1203 if not g_cache_ctpelp_lookup.exists(l_id) then
1204 --
1205 -- Lets store the hash value in the index
1206 --
1207 l_not_hash_found := true;
1208 commit;
1209 exit;
1210 --
1211 else
1212 --
1213 l_not_hash_found := false;
1214 --
1215 end if;
1216 --
1217 end loop;
1218 --
1219 end if;
1220 --
1221 g_cache_ctpelp_lookup(l_id).id := objlook.ptip_id;
1222 g_cache_ctpelp_lookup(l_id).fk_id := objlook.business_group_id;
1223 --
1224 end loop;
1225 --
1226 l_torrwnum := 0;
1227 l_prev_id := -1;
1228 --
1229 for objinst in c_ctpelp_inst loop
1230 --
1231 -- Populate the cache lookup details
1232 --
1233 l_id := ben_hash_utility.get_hashed_index(p_id => objinst.ptip_id);
1234 --
1235 -- Check if hashed value is already allocated
1236 --
1237 if g_cache_ctpelp_inst.exists(l_id) then
1238 --
1239 l_not_hash_found := false;
1240 --
1241 -- Loop until un-allocated has value is derived
1242 --
1243 while not l_not_hash_found loop
1244 --
1245 l_id := ben_hash_utility.get_next_hash_index(p_hash_index => l_id);
1246 --
1247 -- Check if the hash index exists, if not we can use it
1248 --
1249 if not g_cache_ctpelp_inst.exists(l_id) then
1250 --
1251 -- Lets store the hash value in the index
1252 --
1253 l_not_hash_found := true;
1254 commit;
1255 exit;
1256 --
1257 else
1258 --
1259 l_not_hash_found := false;
1260 --
1261 end if;
1262 --
1263 end loop;
1264 --
1265 end if;
1266 --
1267 -- Check for first row
1268 --
1269 if l_prev_id = -1 then
1270 --
1271 g_cache_ctpelp_lookup(l_id).starttorele_num := l_torrwnum;
1272 --
1273 elsif l_id <> l_prev_id then
1274 --
1275 g_cache_ctpelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
1276 g_cache_ctpelp_lookup(l_id).starttorele_num := l_torrwnum;
1277 --
1278 end if;
1279 --
1280 -- Populate the cache instance details
1281 --
1282 g_cache_ctpelp_inst(l_torrwnum).plip_id := objinst.ptip_id;
1283 g_cache_ctpelp_inst(l_torrwnum).prtn_elig_id := objinst.prtn_elig_id;
1284 g_cache_ctpelp_inst(l_torrwnum).mndtry_flag := objinst.mndtry_flag;
1285 g_cache_ctpelp_inst(l_torrwnum).eligy_prfl_id := objinst.eligy_prfl_id;
1286 --
1287 l_torrwnum := l_torrwnum+1;
1288 l_prev_id := l_id;
1289 --
1290 end loop;
1291 --
1292 g_cache_ctpelp_lookup(l_prev_id).endtorele_num := l_torrwnum-1;
1293 --
1294 end ctpelp_writecache;
1295 --
1296 procedure ctpelp_getcacdets
1297 (p_effective_date in date,
1298 p_business_group_id in number,
1299 p_ptip_id in number,
1300 p_refresh_cache in boolean default FALSE,
1301 p_inst_set out nocopy ben_cel_cache.g_cache_cepelp_instor,
1302 p_inst_count out nocopy number) is
1303 --
1304 l_proc varchar2(72) := g_package||'ctpelp_getcacdets';
1305 l_torrwnum binary_integer;
1306 l_insttorrw_num binary_integer;
1307 l_index binary_integer;
1308 l_not_hash_found boolean;
1309 --
1310 begin
1311 --
1312 -- Flush the cache
1313 --
1314 if p_refresh_cache then
1315 --
1316 g_cache_ctpelp_lookup.delete;
1317 g_cache_ctpelp_inst.delete;
1321 -- Populate the global cache
1318 --
1319 end if;
1320 --
1322 --
1323 if g_cache_ctpelp_lookup.count = 0 then
1324 --
1325 -- Build the cache
1326 --
1327 ben_cel_cache.ctpelp_writecache
1328 (p_effective_date => p_effective_date,
1329 p_refresh_cache => p_refresh_cache);
1330 --
1331 end if;
1332 --
1333 -- Get the instance details
1334 --
1335 l_torrwnum := 0;
1336 l_index := ben_hash_utility.get_hashed_index(p_id => p_ptip_id);
1337 --
1338 -- Check if hashed value is already allocated
1339 --
1340 if g_cache_ctpelp_lookup.exists(l_index) then
1341 --
1342 -- If it does exist make sure its the right one
1343 --
1344 if g_cache_ctpelp_lookup(l_index).id <> p_ptip_id then
1345 --
1346 l_not_hash_found := false;
1347 --
1348 -- Loop until un-allocated has value is derived
1349 --
1350 while not l_not_hash_found loop
1351 --
1352 l_index := ben_hash_utility.get_next_hash_index(p_hash_index => l_index);
1353 --
1354 -- Check if the hash index exists, if not we can use it
1355 --
1356 if not g_cache_ctpelp_lookup.exists(l_index) then
1357 --
1358 -- Lets store the hash value in the index
1359 --
1360 l_not_hash_found := true;
1361 commit;
1362 exit;
1363 --
1364 else
1365 --
1366 l_not_hash_found := false;
1367 --
1368 end if;
1369 --
1370 end loop;
1371 --
1372 end if;
1373 --
1374 end if;
1375 --
1376 for l_insttorrw_num in g_cache_ctpelp_lookup(l_index).starttorele_num ..
1377 g_cache_ctpelp_lookup(l_index).endtorele_num loop
1378 --
1379 p_inst_set(l_torrwnum) := g_cache_ctpelp_inst(l_insttorrw_num);
1380 l_torrwnum := l_torrwnum+1;
1381 --
1382 end loop;
1383 --
1384 p_inst_count := l_torrwnum;
1385 --
1386 exception
1387 --
1388 when no_data_found then
1389 --
1390 p_inst_count := 0;
1391 --
1392 end ctpelp_getcacdets;
1393 --
1394 procedure cepelp_getdets
1395 (p_business_group_id in number,
1396 p_effective_date in date,
1397 p_pgm_id in number,
1398 p_pl_id in number,
1399 p_oipl_id in number,
1400 p_plip_id in number,
1401 p_ptip_id in number,
1402 p_refresh_cache in boolean default FALSE,
1403 p_inst_set out nocopy ben_cel_cache.g_cache_cepelp_instor,
1404 p_inst_count out nocopy number) is
1405 --
1406 l_proc varchar2(72) := g_package||'cepelp_getdets';
1407 --
1408 begin
1409 --
1410 -- Populate the local cache from the global cache
1411 --
1412 if p_pl_id is not null and
1413 p_pgm_id is null and
1414 p_plip_id is null and
1415 p_ptip_id is null and
1416 p_oipl_id is null then
1417 --
1418 -- PLNELP
1419 --
1420 ben_cel_cache.plnelp_getcacdets
1421 (p_effective_date => p_effective_date,
1422 p_business_group_id => p_business_group_id,
1423 p_pl_id => p_pl_id,
1424 p_inst_set => p_inst_set,
1425 p_inst_count => p_inst_count);
1426 --
1427 elsif p_pl_id is null and
1428 p_pgm_id is not null and
1429 p_plip_id is null and
1430 p_ptip_id is null and
1431 p_oipl_id is null then
1432 --
1433 -- PGMELP
1434 --
1435 ben_cel_cache.pgmelp_getcacdets
1436 (p_effective_date => p_effective_date,
1437 p_business_group_id => p_business_group_id,
1438 p_pgm_id => p_pgm_id,
1439 p_inst_set => p_inst_set,
1440 p_inst_count => p_inst_count);
1441 --
1442 elsif p_pl_id is null and
1443 p_pgm_id is null and
1444 p_plip_id is null and
1445 p_ptip_id is null and
1446 p_oipl_id is not null then
1447 --
1448 -- COPELP
1449 --
1450 ben_cel_cache.copelp_getcacdets
1451 (p_effective_date => p_effective_date,
1452 p_business_group_id => p_business_group_id,
1453 p_oipl_id => p_oipl_id,
1454 p_inst_set => p_inst_set,
1455 p_inst_count => p_inst_count);
1456 --
1457 elsif p_pl_id is null and
1458 p_pgm_id is null and
1459 p_plip_id is not null and
1460 p_ptip_id is null and
1461 p_oipl_id is null then
1462 --
1463 -- CPPELP
1464 --
1465 ben_cel_cache.cppelp_getcacdets
1466 (p_effective_date => p_effective_date,
1467 p_business_group_id => p_business_group_id,
1468 p_plip_id => p_plip_id,
1469 p_inst_set => p_inst_set,
1470 p_inst_count => p_inst_count);
1471 --
1472 elsif p_pl_id is null and
1473 p_pgm_id is null and
1474 p_ptip_id is not null and
1475 p_plip_id is null and
1476 p_oipl_id is null then
1477 --
1478 -- CPPELP
1479 --
1480 ben_cel_cache.ctpelp_getcacdets
1481 (p_effective_date => p_effective_date,
1482 p_business_group_id => p_business_group_id,
1483 p_ptip_id => p_ptip_id,
1484 p_inst_set => p_inst_set,
1485 p_inst_count => p_inst_count);
1486 --
1487 end if;
1488 --
1489 end cepelp_getdets;
1490 --
1491 procedure clear_down_cache is
1492 --
1493 begin
1494 --
1495 g_cache_plnelp_lookup.delete;
1496 g_cache_plnelp_inst.delete;
1497 g_cache_pgmelp_lookup.delete;
1498 g_cache_pgmelp_inst.delete;
1499 g_cache_copelp_lookup.delete;
1500 g_cache_copelp_inst.delete;
1501 g_cache_cppelp_lookup.delete;
1502 g_cache_cppelp_lookup.delete;
1503 g_cache_ctpelp_inst.delete;
1504 g_cache_ctpelp_inst.delete;
1505 --
1506 end clear_down_cache;
1507 --
1508 end ben_cel_cache;
[Home] [Help]