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