[Home] [Help]
PACKAGE BODY: APPS.BEN_PEP_CACHE
Source
1 package body ben_pep_cache as
2 /* $Header: benpepch.pkb 120.3 2005/10/21 01:58:44 abparekh 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 14-Sep-00 mhoyes Upgraded caching.
16 115.3 15-Nov-00 mhoyes - Initialized record in exceptions on a get
17 to cover when no rows exist.
18 115.4 03-Jul-01 tmathers 9i complaince removed unused nulls
19 from c_instance.
20 115.5 11-Dec-01 mhoyes - Added get_pilplnpep_dets.
21 115.6 17-Apr-02 pbodla - 2327832 : Modified the cursor
22 write_pilepo_cache to join epo rec to pil
23 rec to avoid fetching voided and backed out
24 epo rows.
25 115.7 17-Apr-02 pbodla - Added lines for GSCC compliance.
26 115.8 05-Jul-02 mhoyes - SQL tuning in get_pilplnpep_dets.
27 115.9 12-Jul-02 mhoyes - Added get_curroiplippep_dets and
28 get_currplnpep_dets.
29 115.10 16-Jul-02 mhoyes - Fixed oipl electability problem introduced
30 in 115.8.
31 115.11 28-Jul-02 mhoyes - Added back in join to ben_per_in_ler for
32 voided and backed out life events in
33 get_pilplnpep_dets.
34 115.12 20-Aug-02 mhoyes - Added caching into get_currpepepo_dets based
35 on comp object list row values.
36 - Fixed compliance error on defaulted
37 parameters.
38 115.13 17-Mar-03 vsethi - Bug 2650247 added inelg_rsn_cd to get_currpepepo_dets
39 115.14 15-Feb-04 mhoyes - Revamped write_pilpep_cache and
40 write_pilepo_cache to use bulk collects.
41 - Spilt cursor in write_pilepo_cache.
42 115.14 18-Feb-04 mhoyes - Bug 3412822. Revamp of eligibility cache.
43 115.15 06-Apr-04 mhoyes - Bug 3412822. Revamp of eligibility cache.
44 115.17 14-Apr-04 mhoyes - Bug 3506360. Scaleability tuning of EPO
45 cache.
46 115.18 20-Apr-04 rpgupta - Bug 3575396. Cache is not written if per in ler
47 is in started status.
48 115.19 27-Apr-04 mhoyes - Bug 3506360. More scaleability tuning of EPO
49 cache. Added get_peppil_list.
50 115.20 28-Apr-04 ikasire - Bug 3550789 creating duplicate EPO rows
51 115.20.1 13-Oct-04 mhoyes - Bug 3950924. Added get_pilepo_dets11521.
52 - Backed out functional change in 115.20.
53 - Applied bind peeking tuning.
54 115.22 08-Nov-04 mhoyes - Bug 3967078. Made 115.20.11591.2 version
55 115.22.
56 - Backed out functional change 3550789
57 made in 115.20.
58 - Applied bind peeking tuning.
59 115.23 02-May-05 mhoyes - Bug 4345064. Tuned cursor c_pilpepexists
60 by adding rownum=1 to minimize excessive
61 logical reads.
62 115.24 04-May-05 mhoyes - Bug 4350303. Backed out nocopy due to
63 performance regression.
64 115.25 06-May-05 mhoyes - Bug 4350303. Bypassed call to hash function
65 ben_hash_utility.get_hashed_index.
66 - Removed obsolete procedures.
67 115.26 30-May-05 mhoyes - Bug 4400538. Moved local procedures out to
68 ben_pep_cache2.
69 115.27 12-jun-05 mhoyes - Bug 4425771. Defined package locals as
70 globals.
71 115.28 20-Oct-05 abparekh - Bug 4646361 : Added NOCOPY hint to out parameters
72 -----------------------------------------------------------------------------
73 */
74 --
75 procedure get_pilpep_dets
76 (p_person_id in number
77 ,p_business_group_id in number
78 ,p_effective_date in date
79 ,p_pgm_id in number default null
80 ,p_ptip_id in number default null
81 ,p_pl_id in number default null
82 ,p_plip_id in number default null
83 ,p_date_sync in boolean default false
84 -- ,p_inst_row in out NOCOPY ben_derive_part_and_rate_facts.g_cache_structure
85 ,p_inst_row out nocopy ben_derive_part_and_rate_facts.g_cache_structure
86 )
87 is
88 --
89 l_proc varchar2(72) := 'get_pilpep_dets';
90 --
91 l_hv pls_integer;
92 l_reset ben_derive_part_and_rate_facts.g_cache_structure;
93 --
94 begin
95 --
96 if p_date_sync
97 then
98 --
99 -- Check if the passed in effective date matches the cached effective date
100 --
101 if nvl(g_pilpep_effdt,hr_api.g_sot) = p_effective_date
102 and nvl(g_pilpep_personid,-9999999) = p_person_id
103 then
104 --
105 null;
106 --
107 else
108 --
109 ben_pep_cache.clear_down_pepcache;
110 g_pilpep_cached := false;
111 --
112 end if;
113 --
114 end if;
115 --
116 if not g_pilpep_cached
117 then
118 --
119 -- Build the cache
120 --
121 ben_pep_cache2.write_pilpep_cache
122 (p_person_id => p_person_id
123 ,p_business_group_id => p_business_group_id
124 ,p_effective_date => p_effective_date
125 );
126 --
127 g_pilpep_cached := TRUE;
128 g_pilpep_effdt := p_effective_date;
129 g_pilpep_personid := p_person_id;
130 --
131 end if;
132 --
133 -- Get the hashed value
134 -- Bug 4350303
135 l_hv := mod(nvl(p_pgm_id,1)+nvl(p_pl_id,2)+nvl(p_plip_id,3)
136 +nvl(p_ptip_id,4),g_hash_key);
137 -- l_hv := ben_hash_utility.get_hashed_index(p_id => nvl(p_pgm_id,1)+nvl(p_pl_id,2)
138 -- +nvl(p_plip_id,3)+nvl(p_ptip_id,4));
139 -- Bug 4350303
140 --
141 -- Check the pgm and pl combination is correct
142 --
143 if nvl(g_pilpep_instance(l_hv).pgm_id,-1) = nvl(p_pgm_id,-1)
144 and nvl(g_pilpep_instance(l_hv).pl_id,-1) = nvl(p_pl_id,-1)
145 and nvl(g_pilpep_instance(l_hv).plip_id,-1) = nvl(p_plip_id,-1)
146 and nvl(g_pilpep_instance(l_hv).ptip_id,-1) = nvl(p_ptip_id,-1)
147 then
148 --
149 null;
150 --
151 else
152 --
153 l_hv := l_hv+g_hash_jump;
154 --
155 loop
156 --
157 if nvl(g_pilpep_instance(l_hv).pgm_id,-1) = nvl(p_pgm_id,-1)
158 and nvl(g_pilpep_instance(l_hv).pl_id,-1) = nvl(p_pl_id,-1)
159 and nvl(g_pilpep_instance(l_hv).plip_id,-1) = nvl(p_plip_id,-1)
160 and nvl(g_pilpep_instance(l_hv).ptip_id,-1) = nvl(p_ptip_id,-1)
161 then
162 --
163 exit;
164 --
165 else
166 --
167 l_hv := l_hv+g_hash_jump;
168 --
169 end if;
170 --
171 end loop;
172 --
173 end if;
174 --
175 p_inst_row := g_pilpep_instance(l_hv);
176 --
177 exception
178 --
179 when no_data_found then
180 --
181 -- p_inst_row := l_reset;
182 null;
183 --
184 end get_pilpep_dets;
185 --
186 procedure get_pilepo_dets
187 (p_person_id in number
188 ,p_business_group_id in number
189 ,p_effective_date in date
190 ,p_pgm_id in number default null
191 ,p_pl_id in number default null
192 ,p_opt_id in number default null
193 ,p_plip_id in number default null
194 ,p_date_sync in boolean default false
195 -- ,p_inst_row in out NOCOPY ben_derive_part_and_rate_facts.g_cache_structure
196 ,p_inst_row out nocopy ben_derive_part_and_rate_facts.g_cache_structure
197 )
198 is
199 --
200 l_proc varchar2(72) := 'get_pilepo_dets';
201 --
202 l_hv pls_integer;
203 l_reset ben_derive_part_and_rate_facts.g_cache_structure;
204 --
205 begin
206 --
207 if p_date_sync
208 then
209 --
210 -- Check if the passed in effective date matches the cached effective date
211 --
212 if nvl(g_optpilepo_effdt,hr_api.g_sot) = p_effective_date
213 and nvl(g_optpilepo_personid,-9999999) = p_person_id
214 then
215 --
216 null;
217 --
218 else
219 --
220 ben_pep_cache.clear_down_epocache;
221 g_optpilepo_cached := false;
222 --
223 end if;
224 --
225 end if;
226 --
227 if not g_optpilepo_cached
228 then
229 --
230 -- Build the cache
231 --
232 ben_pep_cache2.write_pilepo_cache
233 (p_person_id => p_person_id
234 ,p_business_group_id => p_business_group_id
235 ,p_effective_date => p_effective_date
236 );
237 --
238 g_optpilepo_cached := TRUE;
239 g_optpilepo_effdt := p_effective_date;
240 g_optpilepo_personid := p_person_id;
241 --
242 end if;
243 --
244 -- Get the instance details
245 --
246 -- Bug 4350303
247 l_hv := mod(nvl(p_opt_id,1)+nvl(p_pgm_id,2)+nvl(p_pl_id,3)
248 +nvl(p_plip_id,4),g_hash_key);
249 -- l_hv := ben_hash_utility.get_hashed_index(p_id => nvl(p_opt_id,1)+nvl(p_pgm_id,2)
250 -- +nvl(p_pl_id,3)+nvl(p_plip_id,4));
251 -- Bug 4350303
252 --
253 -- Check the pgm and pl combination is correct
254 --
255 if nvl(g_optpilepo_instance(l_hv).pgm_id,-1) = nvl(p_pgm_id,-1)
256 and nvl(g_optpilepo_instance(l_hv).pl_id,-1) = nvl(p_pl_id,-1)
257 and nvl(g_optpilepo_instance(l_hv).plip_id,-1) = nvl(p_plip_id,-1)
258 and nvl(g_optpilepo_instance(l_hv).opt_id,-1) = nvl(p_opt_id,-1)
259 then
260 --
261 null;
262 --
263 else
264 --
265 l_hv := l_hv+g_hash_jump;
266 --
267 loop
268 --
269 if nvl(g_optpilepo_instance(l_hv).pgm_id,-1) = nvl(p_pgm_id,-1)
270 and nvl(g_optpilepo_instance(l_hv).pl_id,-1) = nvl(p_pl_id,-1)
271 and nvl(g_optpilepo_instance(l_hv).plip_id,-1) = nvl(p_plip_id,-1)
272 and nvl(g_optpilepo_instance(l_hv).opt_id,-1) = nvl(p_opt_id,-1)
273 then
274 --
275 exit;
276 --
277 else
278 --
279 l_hv := l_hv+g_hash_jump;
280 --
281 end if;
282 --
283 end loop;
284 --
285 end if;
286 --
287 p_inst_row := g_optpilepo_instance(l_hv);
288 --
289 exception
290 --
291 when no_data_found then
292 --
293 -- p_inst_row := l_reset;
294 null;
295 --
296 end get_pilepo_dets;
297 --
298 procedure get_currpepepo_dets
299 (p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
300 ,p_per_in_ler_id in number
301 ,p_effective_date in date
302 ,p_pgm_id in number
303 ,p_pl_id in number
304 ,p_oipl_id in number
305 ,p_opt_id in number
306 --
307 ,p_inst_row in out NOCOPY g_pep_rec
308 )
309 is
310 --
311 l_proc varchar2(72) := 'get_currpepepo_dets';
312 --
313 l_inst_row g_pep_rec;
314 --
315 -- Determines the current eligibility for a plan
316 --
317 CURSOR c_current_elig_for_plan
318 (c_per_in_ler_id number
319 ,c_pl_id number
320 ,c_pgm_id number
321 ,c_effective_date date
322 )
323 is
324 SELECT /*+ benpepch.get_pilplnpep_dets.c_current_elig_for_plan */
325 pep.elig_per_id,
326 pep.elig_flag,
327 pep.must_enrl_anthr_pl_id,
328 pep.prtn_strt_dt,
329 pep.inelg_rsn_cd
330 FROM ben_elig_per_f pep,
331 ben_per_in_ler pil
332 WHERE pep.per_in_ler_id = c_per_in_ler_id
333 AND pep.pl_id = c_pl_id
334 AND pep.pgm_id = c_pgm_id
335 AND c_effective_date
336 BETWEEN pep.effective_start_date AND pep.effective_end_date
337 AND pil.per_in_ler_id (+) = pep.per_in_ler_id
338 AND pil.business_group_id (+) = pep.business_group_id
339 AND (
340 pil.per_in_ler_stat_cd NOT IN
341 (
342 'VOIDD',
343 'BCKDT')
344 OR pil.per_in_ler_stat_cd IS NULL);
345 --
346 CURSOR c_current_elig_for_plnip
347 (c_per_in_ler_id number
348 ,c_pl_id number
349 ,c_effective_date date
350 )
351 is
352 SELECT /*+ benpepch.get_pilplnpep_dets.c_current_elig_for_plnip */
353 pep.elig_per_id,
354 pep.elig_flag,
355 pep.must_enrl_anthr_pl_id,
356 pep.prtn_strt_dt,
357 pep.inelg_rsn_cd
358 FROM ben_elig_per_f pep,
359 ben_per_in_ler pil
360 WHERE pep.per_in_ler_id = c_per_in_ler_id
361 AND pep.pl_id = c_pl_id
362 AND pep.pgm_id IS NULL
363 AND c_effective_date
364 BETWEEN pep.effective_start_date AND pep.effective_end_date
365 AND pil.per_in_ler_id (+) = pep.per_in_ler_id
366 AND pil.business_group_id (+) = pep.business_group_id
367 AND (
368 pil.per_in_ler_stat_cd NOT IN
369 (
370 'VOIDD',
371 'BCKDT')
372 OR pil.per_in_ler_stat_cd IS NULL);
373 --
374 -- Determines the current eligibility for an option
375 --
376 CURSOR c_current_elig_for_option
377 (c_per_in_ler_id number
378 ,c_pl_id number
379 ,c_pgm_id number
380 ,c_opt_id number
381 ,c_effective_date date
382 )
383 IS
384 SELECT /*+ benpepch.get_pilplnpep_dets.c_current_elig_for_option */
385 ep.elig_per_id,
386 epo.elig_flag,
387 ep.must_enrl_anthr_pl_id,
388 epo.prtn_strt_dt,
389 epo.inelg_rsn_cd
390 FROM ben_elig_per_f ep,
391 ben_elig_per_opt_f epo,
392 ben_per_in_ler pil
393 WHERE ep.per_in_ler_id = c_per_in_ler_id
394 AND ep.pl_id = c_pl_id
395 AND ep.pgm_id = c_pgm_id
396 AND c_effective_date
397 BETWEEN ep.effective_start_date AND ep.effective_end_date
398 AND ep.elig_per_id = epo.elig_per_id
399 AND epo.opt_id = c_opt_id
400 AND c_effective_date
401 BETWEEN epo.effective_start_date AND epo.effective_end_date
402 AND pil.per_in_ler_id (+) = epo.per_in_ler_id
403 AND pil.business_group_id (+) = epo.business_group_id
404 AND (
405 pil.per_in_ler_stat_cd NOT IN
406 (
407 'VOIDD',
408 'BCKDT')
409 OR pil.per_in_ler_stat_cd IS NULL);
410 --
411 CURSOR c_current_elig_for_optnip
412 (c_per_in_ler_id number
413 ,c_pl_id number
414 ,c_opt_id number
415 ,c_effective_date date
416 )
417 IS
418 SELECT /*+ benpepch.get_pilplnpep_dets.c_current_elig_for_optnip */
419 ep.elig_per_id,
420 epo.elig_flag,
421 ep.must_enrl_anthr_pl_id,
422 epo.prtn_strt_dt,
423 epo.inelg_rsn_cd
424 FROM ben_elig_per_f ep,
425 ben_elig_per_opt_f epo,
426 ben_per_in_ler pil
427 WHERE ep.per_in_ler_id = c_per_in_ler_id
428 AND ep.pl_id = c_pl_id
429 AND ep.pgm_id IS NULL
430 AND c_effective_date
431 BETWEEN ep.effective_start_date AND ep.effective_end_date
432 AND ep.elig_per_id = epo.elig_per_id
433 AND epo.opt_id = c_opt_id
434 AND c_effective_date
435 BETWEEN epo.effective_start_date AND epo.effective_end_date
436 AND pil.per_in_ler_id (+) = epo.per_in_ler_id
437 AND pil.business_group_id (+) = epo.business_group_id
438 AND (
439 pil.per_in_ler_stat_cd NOT IN
440 (
441 'VOIDD',
442 'BCKDT')
443 OR pil.per_in_ler_stat_cd IS NULL);
444 --
445 begin
446 --
447 -- Get the current eligibility info from the comp
448 -- object list
449 --
450 if p_comp_obj_tree_row.elig_per_id is not null
451 then
452 --
453 p_inst_row.elig_per_id := p_comp_obj_tree_row.elig_per_id;
454 p_inst_row.elig_flag := p_comp_obj_tree_row.elig_flag;
455 p_inst_row.prtn_strt_dt := p_comp_obj_tree_row.prtn_strt_dt;
456 p_inst_row.inelg_rsn_cd := p_comp_obj_tree_row.inelg_rsn_cd; -- 2650247
457 --
458 return;
459 --
460 end if;
461 --
462 if p_oipl_id is null then
463 --
464 if p_pgm_id is not null
465 then
466 --
467 OPEN c_current_elig_for_plan
468 (c_per_in_ler_id => p_per_in_ler_id
469 ,c_effective_date => p_effective_date
470 ,c_pgm_id => p_pgm_id
471 ,c_pl_id => p_pl_id
472 );
473 FETCH c_current_elig_for_plan INTO l_inst_row.elig_per_id,
474 l_inst_row.elig_flag,
475 l_inst_row.must_enrl_anthr_pl_id,
476 l_inst_row.prtn_strt_dt,
477 l_inst_row.inelg_rsn_cd; -- 2650247
478 --
479 CLOSE c_current_elig_for_plan;
480 --
481 else
482 --
483 OPEN c_current_elig_for_plnip
484 (c_per_in_ler_id => p_per_in_ler_id
485 ,c_effective_date => p_effective_date
486 ,c_pl_id => p_pl_id
487 );
488 FETCH c_current_elig_for_plnip INTO l_inst_row.elig_per_id,
489 l_inst_row.elig_flag,
490 l_inst_row.must_enrl_anthr_pl_id,
491 l_inst_row.prtn_strt_dt,
492 l_inst_row.inelg_rsn_cd; -- 2650247
493 --
494 CLOSE c_current_elig_for_plnip;
495 --
496 end if;
497 --
498 else
499 --
500 if p_pgm_id is not null
501 then
502 --
503 OPEN c_current_elig_for_option
504 (c_per_in_ler_id => p_per_in_ler_id
505 ,c_effective_date => p_effective_date
506 ,c_pgm_id => p_pgm_id
507 ,c_pl_id => p_pl_id
508 ,c_opt_id => p_opt_id
509 );
510 FETCH c_current_elig_for_option INTO l_inst_row.elig_per_id,
511 l_inst_row.elig_flag,
512 l_inst_row.must_enrl_anthr_pl_id,
513 l_inst_row.prtn_strt_dt,
514 l_inst_row.inelg_rsn_cd; -- 2650247
515 --
516 CLOSE c_current_elig_for_option;
517 --
518 else
519 --
520 OPEN c_current_elig_for_optnip
521 (c_per_in_ler_id => p_per_in_ler_id
522 ,c_effective_date => p_effective_date
523 ,c_pl_id => p_pl_id
524 ,c_opt_id => p_opt_id
525 );
526 FETCH c_current_elig_for_optnip INTO l_inst_row.elig_per_id,
527 l_inst_row.elig_flag,
528 l_inst_row.must_enrl_anthr_pl_id,
529 l_inst_row.prtn_strt_dt,
530 l_inst_row.inelg_rsn_cd; -- 2650247
531 --
532 CLOSE c_current_elig_for_optnip;
533 --
534 end if;
535 --
536 end if;
537 --
538 p_inst_row := l_inst_row;
539 --
540 end get_currpepepo_dets;
541 --
542 procedure get_curroiplippep_dets
543 (p_comp_obj_tree_row in out NOCOPY ben_manage_life_events.g_cache_proc_objects_rec
544 ,p_person_id in number
545 ,p_effective_date in date
546 --
547 ,p_inst_row in out NOCOPY g_pep_rec
548 )
549 is
550 --
551 l_proc varchar2(72) := 'get_curroiplippep_dets';
552 --
553 l_inst_row g_pep_rec;
554 --
555 cursor c_oiplip_dets
556 (c_person_id in number
557 ,c_effective_date in date
558 ,c_pgm_id in number
559 ,c_plip_id in number
560 )
561 is
562 select /*+ benpepch.get_curroiplippep_dets.c_oiplip_dets */
563 pep.elig_per_id
564 from ben_elig_per_f pep,
565 ben_per_in_ler pil
566 where pep.person_id = c_person_id
567 and pep.pgm_id = c_pgm_id
568 and pep.plip_id = c_plip_id
569 and c_effective_date
570 between pep.effective_start_date
571 and pep.effective_end_date
572 and pil.per_in_ler_id(+)=pep.per_in_ler_id
573 and pil.business_group_id(+)=pep.business_group_id
574 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
575 or pil.per_in_ler_stat_cd is null);
576 --
577 cursor c_oiplipnip_dets
578 (c_person_id in number
579 ,c_effective_date in date
580 ,c_plip_id in number
581 )
582 is
583 select /*+ benpepch.get_curroiplippep_dets.c_oiplipnip_dets */
584 pep.elig_per_id
585 from ben_elig_per_f pep,
586 ben_per_in_ler pil
587 where pep.person_id = c_person_id
588 and pep.pgm_id is null
589 and pep.plip_id = c_plip_id
590 and c_effective_date
591 between pep.effective_start_date
592 and pep.effective_end_date
593 and pil.per_in_ler_id(+)=pep.per_in_ler_id
594 and pil.business_group_id(+)=pep.business_group_id
595 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
596 or pil.per_in_ler_stat_cd is null);
597 --
598 begin
599 --
600 if p_comp_obj_tree_row.par_pgm_id is not null
601 then
602 --
603 open c_oiplip_dets
604 (c_person_id => p_person_id
605 ,c_effective_date => p_effective_date
606 ,c_pgm_id => p_comp_obj_tree_row.par_pgm_id
607 ,c_plip_id => p_comp_obj_tree_row.par_plip_id
608 );
609 fetch c_oiplip_dets into l_inst_row.elig_per_id;
610 close c_oiplip_dets;
611 --
612 else
613 --
614 open c_oiplipnip_dets
615 (c_person_id => p_person_id
616 ,c_effective_date => p_effective_date
617 ,c_plip_id => p_comp_obj_tree_row.par_plip_id
618 );
619 fetch c_oiplipnip_dets into l_inst_row.elig_per_id;
620 close c_oiplipnip_dets;
621 --
622 end if;
623 --
624 p_inst_row := l_inst_row;
625 --
626 end get_curroiplippep_dets;
627 --
628 procedure get_currplnpep_dets
629 (p_comp_obj_tree_row in out NOCOPY ben_manage_life_events.g_cache_proc_objects_rec
630 ,p_person_id in number
631 ,p_effective_date in date
632 --
633 ,p_inst_row in out NOCOPY g_pep_rec
634 )
635 is
636 --
637 l_proc varchar2(72) := 'get_currplnpep_dets';
638 --
639 l_inst_row g_pep_rec;
640 --
641 -- Cursor to grab the PK of elig_per record to join the elig opt record to
642 -- for first time'rs only
643 --
644 cursor c_pln_dets
645 (c_person_id in number
646 ,c_effective_date in date
647 ,c_pgm_id in number
648 ,c_pl_id in number
649 )
650 is
651 select /*+ benpepch.get_currplnpep_dets.c_pln_dets */
652 pep.elig_per_id,
653 pep.prtn_strt_dt,
654 pep.prtn_end_dt
655 from ben_elig_per_f pep,
656 ben_per_in_ler pil
657 where pep.person_id = c_person_id
658 and pep.pgm_id = c_pgm_id
659 and pep.pl_id = c_pl_id
660 and c_effective_date
661 between pep.effective_start_date
662 and pep.effective_end_date
663 and pil.per_in_ler_id(+)=pep.per_in_ler_id
664 and pil.business_group_id(+)=pep.business_group_id
665 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
666 or pil.per_in_ler_stat_cd is null);
667 --
668 cursor c_plnip_dets
669 (c_person_id in number
670 ,c_effective_date in date
671 ,c_pl_id in number
672 )
673 is
674 select /*+ benpepch.get_currplnpep_dets.c_plnip_dets */
675 pep.elig_per_id,
676 pep.prtn_strt_dt,
677 pep.prtn_end_dt
678 from ben_elig_per_f pep,
679 ben_per_in_ler pil
680 where pep.person_id = c_person_id
681 and pep.pgm_id is null
682 and pep.pl_id = c_pl_id
683 and c_effective_date
684 between pep.effective_start_date
685 and pep.effective_end_date
686 and pil.per_in_ler_id(+)=pep.per_in_ler_id
687 and pil.business_group_id(+)=pep.business_group_id
688 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
689 or pil.per_in_ler_stat_cd is null);
690 --
691 begin
692 --
693 if p_comp_obj_tree_row.par_pgm_id is not null
694 then
695 --
696 open c_pln_dets
697 (c_person_id => p_person_id
698 ,c_effective_date => p_effective_date
699 ,c_pgm_id => p_comp_obj_tree_row.par_pgm_id
700 ,c_pl_id => p_comp_obj_tree_row.par_pl_id
701 );
702 fetch c_pln_dets into l_inst_row.elig_per_id,
703 l_inst_row.prtn_strt_dt,
704 l_inst_row.prtn_end_dt;
705 close c_pln_dets;
706 --
707 else
708 --
709 open c_plnip_dets
710 (c_person_id => p_person_id
711 ,c_effective_date => p_effective_date
712 ,c_pl_id => p_comp_obj_tree_row.par_pl_id
713 );
714 fetch c_plnip_dets into l_inst_row.elig_per_id,
715 l_inst_row.prtn_strt_dt,
716 l_inst_row.prtn_end_dt;
717 close c_plnip_dets;
718 --
719 end if;
720 --
721 p_inst_row := l_inst_row;
722 --
723 end get_currplnpep_dets;
724 --
725 ------------------------------------------------------------------------
726 -- DELETE ALL CACHED DATA
727 ------------------------------------------------------------------------
728 --
729 procedure clear_down_cache
730 is
731 begin
732 --
733 ben_pep_cache.clear_down_pepcache;
734 ben_pep_cache.clear_down_epocache;
735 --
736 end clear_down_cache;
737 --
738 procedure clear_down_pepcache
739 is
740 begin
741 --
742 g_pilpep_lookup.delete;
743 g_pilpep_instance.delete;
744 --
745 g_pilpep_cached := FALSE;
746 g_pilpep_effdt := null;
747 g_pilpep_personid := null;
748 --
749 end clear_down_pepcache;
750 --
751 procedure clear_down_epocache
752 is
753 begin
754 --
755 g_optpilepo_lookup.delete;
756 g_optpilepo_instance.delete;
757 --
758 g_optpilepo_cached := FALSE;
759 g_optpilepo_effdt := null;
760 g_optpilepo_personid := null;
761 --
762 end clear_down_epocache;
763 --
764 end ben_pep_cache;