[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIG_RL_CACHE
Source
1 package body ben_elig_rl_cache as
2 /* $Header: benelrch.pkb 120.0.12010000.2 2008/11/16 16:15:23 krupani 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 11-Jun-99 bbulusu Created.
15 115.1 02-Aug-99 gperry Added support for plip and ptip.
16 115.2 30-Dec-02 ikasire nocopy changes
17 115.3 28-Oct-03 mhoyes Revamp for bug 3125540.
18 115.4 30-Mar-04 ikasire fonm changes
19 115.5 16-Nov-08 krupani Bug 7537076: passed p_lf_evt_ocrd_dt while calling write_odcache
20 -----------------------------------------------------------------------------
21 */
22 --
23 -- Globals.
24 --
25 g_package varchar2(50) := 'ben_elig_rl_cache.';
26 --
27 g_odlookup ben_cache.g_cache_lookup_table;
28 g_nxelenum number;
29 g_odinst ben_elig_rl_cache.g_elig_rl_inst_tbl;
30 g_odcached pls_integer := 0;
31 --
32 g_hash_key pls_integer := 1299827;
33 g_hash_jump pls_integer := 100;
34 --
35 procedure write_odcache
36 (p_effective_date in date
37 ,p_pgm_id in number default hr_api.g_number
38 ,p_ptip_id in number default hr_api.g_number
39 ,p_plip_id in number default hr_api.g_number
40 ,p_pl_id in number default hr_api.g_number
41 ,p_oipl_id in number default hr_api.g_number
42 --
43 ,p_hv out nocopy pls_integer
44 )
45 is
46 --
47 l_proc varchar2(72) := 'write_odcache';
48 --
49 l_odlookup_rec ben_cache.g_cache_lookup;
50 --
51 l_hv pls_integer;
52 l_not_hash_found boolean;
53 l_torrwnum pls_integer;
54 l_starttorele_num pls_integer;
55 --
56 cursor c_pgminstance
57 (c_pgm_id number
58 ,c_effective_date date
59 )
60 is
61 select tab1.prtn_elig_id,
62 tab1.pgm_id,
63 tab1.pl_id,
64 tab1.oipl_id,
65 tab1.plip_id,
66 tab1.ptip_id,
67 tab2.formula_id,
68 tab2.mndtry_flag,
69 tab2.ordr_to_aply_num
70 from ben_prtn_elig_f tab1,
71 ben_prtn_eligy_rl_f tab2
72 where tab1.pgm_id = c_pgm_id
73 and tab1.prtn_elig_id = tab2.prtn_elig_id
74 and c_effective_date
75 between tab1.effective_start_date and tab1.effective_end_date
76 and c_effective_date
77 between tab2.effective_start_date and tab2.effective_end_date
78 order by decode(tab2.mndtry_flag, 'Y', 2, 3),
79 tab2.ordr_to_aply_num;
80 --
81 l_instance c_pgminstance%rowtype;
82 --
83 cursor c_ptipinstance
84 (c_ptip_id number
85 ,c_effective_date date
86 )
87 is
88 select tab1.prtn_elig_id,
89 tab1.pgm_id,
90 tab1.pl_id,
91 tab1.oipl_id,
92 tab1.plip_id,
93 tab1.ptip_id,
94 tab2.formula_id,
95 tab2.mndtry_flag,
96 tab2.ordr_to_aply_num
97 from ben_prtn_elig_f tab1,
98 ben_prtn_eligy_rl_f tab2
99 where tab1.ptip_id = c_ptip_id
100 and tab1.prtn_elig_id = tab2.prtn_elig_id
101 and c_effective_date
102 between tab1.effective_start_date and tab1.effective_end_date
103 and c_effective_date
104 between tab2.effective_start_date and tab2.effective_end_date
105 order by decode(tab2.mndtry_flag, 'Y', 2, 3),
106 tab2.ordr_to_aply_num;
107 --
108 cursor c_plipinstance
109 (c_plip_id number
110 ,c_effective_date date
111 )
112 is
113 select tab1.prtn_elig_id,
114 tab1.pgm_id,
115 tab1.pl_id,
116 tab1.oipl_id,
117 tab1.plip_id,
118 tab1.ptip_id,
119 tab2.formula_id,
120 tab2.mndtry_flag,
121 tab2.ordr_to_aply_num
122 from ben_prtn_elig_f tab1,
123 ben_prtn_eligy_rl_f tab2
124 where tab1.plip_id = c_plip_id
125 and tab1.prtn_elig_id = tab2.prtn_elig_id
126 and c_effective_date
127 between tab1.effective_start_date and tab1.effective_end_date
128 and c_effective_date
129 between tab2.effective_start_date and tab2.effective_end_date
130 order by decode(tab2.mndtry_flag, 'Y', 2, 3),
131 tab2.ordr_to_aply_num;
132 --
133 cursor c_plinstance
134 (c_pl_id number
135 ,c_effective_date date
136 )
137 is
138 select tab1.prtn_elig_id,
139 tab1.pgm_id,
140 tab1.pl_id,
141 tab1.oipl_id,
142 tab1.plip_id,
143 tab1.ptip_id,
144 tab2.formula_id,
145 tab2.mndtry_flag,
146 tab2.ordr_to_aply_num
147 from ben_prtn_elig_f tab1,
148 ben_prtn_eligy_rl_f tab2
149 where tab1.pl_id = c_pl_id
150 and tab1.prtn_elig_id = tab2.prtn_elig_id
151 and c_effective_date
155 order by decode(tab2.mndtry_flag, 'Y', 2, 3),
152 between tab1.effective_start_date and tab1.effective_end_date
153 and c_effective_date
154 between tab2.effective_start_date and tab2.effective_end_date
156 tab2.ordr_to_aply_num;
157 --
158 cursor c_oiplinstance
159 (c_oipl_id number
160 ,c_effective_date date
161 )
162 is
163 select tab1.prtn_elig_id,
164 tab1.pgm_id,
165 tab1.pl_id,
166 tab1.oipl_id,
167 tab1.plip_id,
168 tab1.ptip_id,
169 tab2.formula_id,
170 tab2.mndtry_flag,
171 tab2.ordr_to_aply_num
172 from ben_prtn_elig_f tab1,
173 ben_prtn_eligy_rl_f tab2
174 where tab1.oipl_id = c_oipl_id
175 and tab1.prtn_elig_id = tab2.prtn_elig_id
176 and c_effective_date
177 between tab1.effective_start_date and tab1.effective_end_date
178 and c_effective_date
179 between tab2.effective_start_date and tab2.effective_end_date
180 order by decode(tab2.mndtry_flag, 'Y', 2, 3),
181 tab2.ordr_to_aply_num;
182 --
183 begin
184 --
185 hr_utility.set_location(' Entering '||l_proc,10);
186 --
187 -- Get the instance details
188 --
189 l_hv := mod(nvl(p_pgm_id,1)+nvl(p_ptip_id,2)+nvl(p_plip_id,3)
190 +nvl(p_pl_id,4)+nvl(p_oipl_id,5),g_hash_key);
191 --
192 -- Get a unique hash value
193 --
194 if g_odlookup.exists(l_hv) then
195 --
196 if nvl(g_odlookup(l_hv).id,-1) = nvl(p_pgm_id,-1)
197 and nvl(g_odlookup(l_hv).fk_id,-1) = nvl(p_ptip_id,-1)
198 and nvl(g_odlookup(l_hv).fk1_id,-1) = nvl(p_plip_id,-1)
199 and nvl(g_odlookup(l_hv).fk2_id,-1) = nvl(p_pl_id,-1)
200 and nvl(g_odlookup(l_hv).fk3_id,-1) = nvl(p_oipl_id,-1)
201 then
202 --
203 null;
204 --
205 else
206 --
207 l_not_hash_found := false;
208 --
209 -- Loop until un-allocated has value is derived
210 --
211 while not l_not_hash_found loop
212 --
213 l_hv := l_hv+g_hash_jump;
214 --
215 -- Check if the hash index exists, and compare the values
216 --
217 if g_odlookup.exists(l_hv) then
218 --
219 if nvl(g_odlookup(l_hv).id,-1) = nvl(p_pgm_id,-1)
220 and nvl(g_odlookup(l_hv).fk_id,-1) = nvl(p_ptip_id,-1)
221 and nvl(g_odlookup(l_hv).fk1_id,-1) = nvl(p_plip_id,-1)
222 and nvl(g_odlookup(l_hv).fk2_id,-1) = nvl(p_pl_id,-1)
223 and nvl(g_odlookup(l_hv).fk3_id,-1) = nvl(p_oipl_id,-1)
224 then
225 --
226 l_not_hash_found := true;
227 exit;
228 --
229 else
230 --
231 l_not_hash_found := false;
232 --
233 end if;
234 --
235 else
236 --
237 exit;
238 --
239 end if;
240 --
241 end loop;
242 --
243 end if;
244 --
245 end if;
246 --
247 g_odlookup(l_hv).id := p_pgm_id;
248 g_odlookup(l_hv).fk_id := p_ptip_id;
249 g_odlookup(l_hv).fk1_id := p_plip_id;
250 g_odlookup(l_hv).fk2_id := p_pl_id;
251 g_odlookup(l_hv).fk3_id := p_oipl_id;
252 --
253 hr_utility.set_location(' Dn Look '||l_proc,10);
254 --
255 l_starttorele_num := nvl(g_nxelenum,0);
256 l_torrwnum := l_starttorele_num;
257 --
258 hr_utility.set_location(' Bef inst loop '||l_proc,10);
259 --
260 if p_pgm_id is not null then
261 --
262 open c_pgminstance
263 (c_pgm_id => p_pgm_id
264 ,c_effective_date => p_effective_date
265 );
266 --
267 elsif p_ptip_id is not null then
268 --
269 open c_ptipinstance
270 (c_ptip_id => p_ptip_id
271 ,c_effective_date => p_effective_date
272 );
273 --
274 elsif p_plip_id is not null then
275 --
276 open c_plipinstance
277 (c_plip_id => p_plip_id
278 ,c_effective_date => p_effective_date
279 );
280 --
281 elsif p_pl_id is not null then
282 --
283 open c_plinstance
284 (c_pl_id => p_pl_id
285 ,c_effective_date => p_effective_date
286 );
287 --
288 elsif p_oipl_id is not null then
289 --
290 open c_oiplinstance
291 (c_oipl_id => p_oipl_id
292 ,c_effective_date => p_effective_date
293 );
294 --
295 end if;
296 --
297 loop
298 --
299 if p_pgm_id is not null then
300 --
301 fetch c_pgminstance into l_instance;
302 exit when c_pgminstance%NOTFOUND;
303 --
304 elsif p_ptip_id is not null then
305 --
306 fetch c_ptipinstance into l_instance;
307 exit when c_ptipinstance%NOTFOUND;
308 --
309 elsif p_plip_id is not null then
310 --
311 fetch c_plipinstance into l_instance;
312 exit when c_plipinstance%NOTFOUND;
313 --
314 elsif p_pl_id is not null then
315 --
316 fetch c_plinstance into l_instance;
317 exit when c_plinstance%NOTFOUND;
318 --
319 elsif p_oipl_id is not null then
320 --
321 fetch c_oiplinstance into l_instance;
322 exit when c_oiplinstance%NOTFOUND;
323 --
324 end if;
325 --
329 g_odinst(l_torrwnum).pgm_id := l_instance.pgm_id;
326 hr_utility.set_location(' Assign inst '||l_proc,10);
327 --
328 g_odinst(l_torrwnum).id := l_instance.prtn_elig_id;
330 g_odinst(l_torrwnum).pl_id := l_instance.pl_id;
331 g_odinst(l_torrwnum).oipl_id := l_instance.oipl_id;
332 g_odinst(l_torrwnum).plip_id := l_instance.plip_id;
333 g_odinst(l_torrwnum).ptip_id := l_instance.ptip_id;
334 g_odinst(l_torrwnum).formula_id := l_instance.formula_id;
335 g_odinst(l_torrwnum).mndtry_flag := l_instance.mndtry_flag;
336 g_odinst(l_torrwnum).ordr_to_aply_num := l_instance.ordr_to_aply_num;
337 --
338 l_torrwnum := l_torrwnum+1;
339 --
340 end loop;
341 --
342 if p_pgm_id is not null then
343 --
344 close c_pgminstance;
345 --
346 elsif p_ptip_id is not null then
347 --
348 close c_ptipinstance;
349 --
350 elsif p_plip_id is not null then
351 --
352 close c_plipinstance;
353 --
354 elsif p_pl_id is not null then
355 --
356 close c_plinstance;
357 --
358 elsif p_oipl_id is not null then
359 --
360 close c_oiplinstance;
361 --
362 end if;
363 --
364 -- Check if any rows were found
365 --
366 if l_torrwnum > nvl(g_nxelenum,0)
367 then
368 --
369 g_odlookup(l_hv).starttorele_num := l_starttorele_num;
370 g_odlookup(l_hv).endtorele_num := l_torrwnum-1;
371 g_nxelenum := l_torrwnum;
372 --
373 p_hv := l_hv;
374 --
375 else
376 --
377 -- Delete and free PGA with assignment
378 --
379 g_odlookup.delete(l_hv);
380 g_odlookup(l_hv) := l_odlookup_rec;
381 --
382 p_hv := null;
383 --
384 end if;
385 --
386 hr_utility.set_location(' Leaving '||l_proc,10);
387 end write_odcache;
388 --
389 procedure get_elig_rl_cache
390 (p_pgm_id in number
391 ,p_pl_id in number
392 ,p_oipl_id in number
393 ,p_plip_id in number
394 ,p_ptip_id in number
395 ,p_effective_date in date
396 ,p_lf_evt_ocrd_dt in date
397 ,p_business_group_id in number
398 ,p_inst_set out nocopy ben_elig_rl_cache.g_elig_rl_inst_tbl
399 ,p_inst_count out nocopy number
400 )
401 is
402 --
403 l_inst_set ben_elig_rl_cache.g_elig_rl_inst_tbl;
404 --
405 l_hv pls_integer;
406 l_hash_found boolean;
407 l_insttorrw_num pls_integer;
408 l_torrwnum pls_integer;
409 --
410 l_clash_count pls_integer;
411 --FONM
412 l_fonm_cvg_strt_dt DATE ;
413 --END FONM
414 begin
415 --
416 if g_odcached = 0
417 then
418 --
419 -- Build the cache
420 --
421 clear_down_cache;
422 --
423 g_odcached := 1;
424 --
425 end if;
426 -- hr_utility.set_location(' Derive hv '||l_proc,10);
427 if ben_manage_life_events.fonm = 'Y'
428 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
429 --
430 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
431 --
432 end if;
433 --
434 -- Get the instance details
435 --
436 l_hv := mod(nvl(p_pgm_id,1)+nvl(p_ptip_id,2)+nvl(p_plip_id,3)
437 +nvl(p_pl_id,4)+nvl(p_oipl_id,5),g_hash_key);
438 --
439 -- Check if hashed value is already allocated
440 --
441 l_hash_found := false;
442 --
443 if g_odlookup.exists(l_hv) then
444 --
445 if nvl(g_odlookup(l_hv).id,-1) = nvl(p_pgm_id,-1)
446 and nvl(g_odlookup(l_hv).fk_id,-1) = nvl(p_ptip_id,-1)
447 and nvl(g_odlookup(l_hv).fk1_id,-1) = nvl(p_plip_id,-1)
448 and nvl(g_odlookup(l_hv).fk2_id,-1) = nvl(p_pl_id,-1)
449 and nvl(g_odlookup(l_hv).fk3_id,-1) = nvl(p_oipl_id,-1)
450 then
451 --
452 null;
453 --
454 else
455 --
456 l_hash_found := false;
457 --
458 -- Loop until un-allocated has value is derived
459 --
460 l_clash_count := 0;
461 --
462 while not l_hash_found loop
463 --
464 l_hv := l_hv+g_hash_jump;
465 --
466 if g_odlookup.exists(l_hv) then
467 --
468 -- Check if the hash index exists, and compare the values
469 --
470 if nvl(g_odlookup(l_hv).id,-1) = nvl(p_pgm_id,-1)
471 and nvl(g_odlookup(l_hv).fk_id,-1) = nvl(p_ptip_id,-1)
472 and nvl(g_odlookup(l_hv).fk1_id,-1) = nvl(p_plip_id,-1)
473 and nvl(g_odlookup(l_hv).fk2_id,-1) = nvl(p_pl_id,-1)
474 and nvl(g_odlookup(l_hv).fk3_id,-1) = nvl(p_oipl_id,-1)
475 then
476 --
477 l_hash_found := true;
478 exit;
479 --
480 else
481 --
482 l_clash_count := l_clash_count+1;
483 l_hash_found := false;
484 --
485 end if;
486 --
487 -- Check for high clash counts and defrag
488 --
489 if l_clash_count > 50
490 then
491 --
492 l_hv := null;
493 clear_down_cache;
494 exit;
495 --
496 end if;
497 --
498 else
499 --
500 l_hv := null;
504 --
501 exit;
502 --
503 end if;
505 end loop;
506 --
507 end if;
508 --
509 else
510 --
511 l_hv := null;
512 --
513 end if;
514 --
515 if l_hv is null
516 then
517 --
518 -- bug 7537076 : passed p_lf_evt_ocrd_dt while calling write_odcache instead of p_effective_date
519 write_odcache
520 (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_lf_evt_ocrd_dt)
521 ,p_pgm_id => p_pgm_id
522 ,p_pl_id => p_pl_id
523 ,p_oipl_id => p_oipl_id
524 ,p_plip_id => p_plip_id
525 ,p_ptip_id => p_ptip_id
526 --
527 ,p_hv => l_hv
528 );
529 --
530 end if;
531 --
532 -- hr_utility.set_location(' Got hv '||l_proc,10);
533 --
534 if l_hv is not null then
535 --
536 l_torrwnum := 0;
537 --
538 -- hr_utility.set_location(' Get loop '||l_proc,10);
539 for l_insttorrw_num in g_odlookup(l_hv).starttorele_num ..
540 g_odlookup(l_hv).endtorele_num
541 loop
542 --
543 l_inst_set(l_torrwnum) := g_odinst(l_insttorrw_num);
544 l_torrwnum := l_torrwnum+1;
545 --
546 end loop;
547 --
548 -- hr_utility.set_location(' Dn Get loop '||l_proc,10);
549 --
550 end if;
551 --
552 p_inst_set := l_inst_set;
553 p_inst_count := l_inst_set.count;
554 --
555 -- hr_utility.set_location(' Leaving '||l_proc,10);
556 exception
557 --
558 when no_data_found then
559 --
560 p_inst_set := l_inst_set;
561 p_inst_count := 0;
562 --
563 end get_elig_rl_cache;
564 --
565 procedure clear_down_cache
566 is
567 --
568 l_odlookup ben_cache.g_cache_lookup_table;
569 l_odinst ben_elig_rl_cache.g_elig_rl_inst_tbl;
570 --
571 begin
572 --
573 -- On demand cache structures
574 --
575 g_odlookup := l_odlookup;
576 g_odinst := l_odinst;
577 g_odcached := 0;
578 g_nxelenum := null;
579 --
580 -- Grab back memory
581 --
582 begin
583 --
584 dbms_session.free_unused_user_memory;
585 --
586 end;
587 --
588 end clear_down_cache;
589 --
590 end ben_elig_rl_cache;