[Home] [Help]
PACKAGE BODY: APPS.BEN_PEP_CACHE2
Source
4 /*
1 package body ben_pep_cache2 as
2 /* $Header: benpepc2.pkb 120.0.12000000.5 2007/07/02 21:19:36 mkommuri noship $ */
3 --
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 30-May-05 mhoyes Created.
15 115.1 23-May-07 gsehgal bug 5947031 All pil records are not being
16 fetched in get_peppil_list
17 115.2 02-Jul-07 mkommuri this version could be ignored
18 115.3 26-Jun-07 mkommuri bug6138732 updated cursor is at
19 ben_pep_cache2.write_pilepo_cache cursor
20 c_pilinstance
21 115.4 02-Jul-07 mkommuri same as 115.3. updated this hisotry
22 -----------------------------------------------------------------------------
23 */
24 --
25 -- Globals.
26 --
27 g_package varchar2(50) := 'ben_pep_cache2.';
28 --
29 g_hash_jump number := ben_hash_utility.get_hash_jump;
30 --
31 procedure get_peppil_list
32 (p_person_id in number
33 ,p_effective_date in date
34 --
35 ,p_peppil_va in out nocopy benutils.g_number_table
36 )
37 is
38 --
39 l_proc varchar2(72) := 'get_pep_list';
40 --
41 l_allpilid_va benutils.g_number_table := benutils.g_number_table();
42 l_outpilid_va benutils.g_number_table := benutils.g_number_table();
43 --
44 l_outpil_en pls_integer;
45 l_peppil_id number;
46 --
47 cursor c_perpillist
48 (c_person_id number
49 )
50 is
51 select pil.per_in_ler_id
52 from ben_per_in_ler pil
53 where pil.person_id = c_person_id
54 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
55 order by pil.per_in_ler_id desc;
56 --
57 cursor c_pilpepexists
58 (c_pil_id number
59 ,c_eff_date date
60 )
61 is
62 select pep.per_in_ler_id
63 from ben_elig_per_f pep
64 where pep.per_in_ler_id = c_pil_id
65 and c_eff_date
66 between pep.effective_start_date and pep.effective_end_date
67 and rownum=1;
68
69 cursor c_pilepoexists
70 (c_pil_id number
71 ,c_eff_date date
72 )
73 is
74 select epo.per_in_ler_id
75 from ben_elig_per_opt_f epo
76 where epo.per_in_ler_id = c_pil_id
77 and c_eff_date
78 between epo.effective_start_date and epo.effective_end_date
79 and rownum=1;
80 --
81 begin
82 --
83 open c_perpillist
84 (c_person_id => p_person_id
85 );
86 fetch c_perpillist BULK COLLECT INTO l_allpilid_va;
87 close c_perpillist;
88 --
89 if l_allpilid_va.count > 0
90 then
91 --
92 l_outpil_en := 1;
93 --
94 for pilvaen in l_allpilid_va.first..l_allpilid_va.last
95 loop
96 --
97 open c_pilpepexists
98 (c_pil_id => l_allpilid_va(pilvaen)
99 ,c_eff_date => p_effective_date
100 );
101 fetch c_pilpepexists into l_peppil_id;
102 if c_pilpepexists%found
103 then
104 --
105 l_outpilid_va.extend(1);
106 l_outpilid_va(l_outpil_en) := l_peppil_id;
107 l_outpil_en := l_outpil_en+1;
108 --
109 -- bug 5947031
110 else
111 OPEN c_pilepoexists (c_pil_id => l_allpilid_va (pilvaen),
112 c_eff_date => p_effective_date
113 );
114
115 FETCH c_pilepoexists
116 INTO l_peppil_id;
117
118 IF c_pilepoexists%FOUND
119 THEN
120 --
121 l_outpilid_va.EXTEND (1);
122 l_outpilid_va (l_outpil_en) := l_peppil_id;
123 l_outpil_en := l_outpil_en + 1;
124
125 END IF;
126
127 CLOSE c_pilepoexists;
128 -- END bug 5947031
129 end if;
130 close c_pilpepexists;
131 --
135 --
132 end loop;
133 --
134 end if;
136 p_peppil_va := l_outpilid_va;
137 --
138 end get_peppil_list;
139 --
140 procedure write_pilpep_cache
141 (p_person_id in number
142 ,p_business_group_id in number
143 ,p_effective_date in date
144 )
145 is
146 --
147 l_proc varchar2(72) := 'write_pilpep_cache';
148 --
149 l_perpilid_va benutils.g_number_table := benutils.g_number_table();
150 --
151 l_los_val_va benutils.g_number_table := benutils.g_number_table();
152 l_age_val_va benutils.g_number_table := benutils.g_number_table();
153 l_comp_ref_amt_va benutils.g_number_table := benutils.g_number_table();
154 l_hrs_wkd_val_va benutils.g_number_table := benutils.g_number_table();
155 l_pct_fl_tm_val_va benutils.g_number_table := benutils.g_number_table();
156 l_cmbn_age_n_los_val_va benutils.g_number_table := benutils.g_number_table();
157 l_age_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
158 l_los_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
159 l_comp_ref_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
160 l_hrs_wkd_bndry_perd_cd_va benutils.g_varchar2_table := benutils.g_varchar2_table();
161 l_frz_los_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
162 l_frz_age_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
163 l_frz_hrs_wkd_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
164 l_frz_cmp_lvl_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
165 l_frz_pct_fl_tm_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
166 l_frz_comb_age_and_los_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
167 l_rt_los_val_va benutils.g_number_table := benutils.g_number_table();
168 l_rt_age_val_va benutils.g_number_table := benutils.g_number_table();
169 l_rt_comp_ref_amt_va benutils.g_number_table := benutils.g_number_table();
170 l_rt_hrs_wkd_val_va benutils.g_number_table := benutils.g_number_table();
171 l_rt_pct_fl_tm_val_va benutils.g_number_table := benutils.g_number_table();
172 l_rt_cmbn_age_n_los_val_va benutils.g_number_table := benutils.g_number_table();
173 l_rt_age_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
174 l_rt_los_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
175 l_rt_comp_ref_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
176 l_rt_hrs_wkd_bndry_perd_cd_va benutils.g_varchar2_table := benutils.g_varchar2_table();
177 l_rt_frz_los_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
178 l_rt_frz_age_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
179 l_rt_frz_hrs_wkd_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
180 l_rt_frz_cmp_lvl_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
181 l_rt_frz_pct_fl_tm_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
182 l_rt_frz_cmb_age_los_flg_va benutils.g_varchar2_table := benutils.g_varchar2_table();
183 l_ovrid_svc_dt_va benutils.g_date_table := benutils.g_date_table();
184 l_prtn_ovridn_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
185 l_prtn_ovridn_thru_dt_va benutils.g_date_table := benutils.g_date_table();
186 l_once_r_cntug_cd_va benutils.g_varchar2_table := benutils.g_varchar2_table();
187 l_elig_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
188 l_pgm_id_va benutils.g_number_table := benutils.g_number_table();
189 l_ptip_id_va benutils.g_number_table := benutils.g_number_table();
190 l_pl_id_va benutils.g_number_table := benutils.g_number_table();
191 l_plip_id_va benutils.g_number_table := benutils.g_number_table();
192 l_prtn_strt_dt_va benutils.g_date_table := benutils.g_date_table();
193 l_prtn_end_dt_va benutils.g_date_table := benutils.g_date_table();
194 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
195 l_elig_per_id_va benutils.g_number_table := benutils.g_number_table();
196 l_per_in_ler_id_va benutils.g_number_table := benutils.g_number_table();
197 --
198 l_hv pls_integer;
199 l_not_hash_found boolean;
200 --
201 l_pil_cnt pls_integer;
202 --
203 cursor c_pilinstance
204 (c_effective_date DATE
205 ,c_pil_id NUMBER
206 )
207 is
208 SELECT tab1.los_val,
209 tab1.age_val,
210 tab1.comp_ref_amt,
211 tab1.hrs_wkd_val,
212 tab1.pct_fl_tm_val,
213 tab1.cmbn_age_n_los_val,
214 tab1.age_uom,
215 tab1.los_uom,
216 tab1.comp_ref_uom,
217 tab1.hrs_wkd_bndry_perd_cd,
218 tab1.frz_los_flag,
219 tab1.frz_age_flag,
220 tab1.frz_hrs_wkd_flag,
221 tab1.frz_cmp_lvl_flag,
222 tab1.frz_pct_fl_tm_flag,
223 tab1.frz_comb_age_and_los_flag,
224 tab1.rt_los_val,
225 tab1.rt_age_val,
226 tab1.rt_comp_ref_amt,
227 tab1.rt_hrs_wkd_val,
228 tab1.rt_pct_fl_tm_val,
229 tab1.rt_cmbn_age_n_los_val,
230 tab1.rt_age_uom,
231 tab1.rt_los_uom,
232 tab1.rt_comp_ref_uom,
236 tab1.rt_frz_hrs_wkd_flag,
233 tab1.rt_hrs_wkd_bndry_perd_cd,
234 tab1.rt_frz_los_flag,
235 tab1.rt_frz_age_flag,
237 tab1.rt_frz_cmp_lvl_flag,
238 tab1.rt_frz_pct_fl_tm_flag,
239 tab1.rt_frz_comb_age_and_los_flag,
240 tab1.ovrid_svc_dt,
241 tab1.prtn_ovridn_flag,
242 tab1.prtn_ovridn_thru_dt,
243 tab1.once_r_cntug_cd,
244 tab1.elig_flag,
245 tab1.pgm_id,
246 tab1.ptip_id,
247 tab1.pl_id,
248 tab1.plip_id,
249 tab1.prtn_strt_dt,
250 tab1.prtn_end_dt,
251 tab1.object_version_number,
252 tab1.elig_per_id,
253 tab1.per_in_ler_id
254 from ben_elig_per_f tab1
255 where tab1.per_in_ler_id = c_pil_id
256 and c_effective_date
257 between tab1.effective_start_date and tab1.effective_end_date
258 order by tab1.pgm_id, tab1.pl_id, tab1.plip_id;
259 --
260 cursor c_nopilinstance
261 (c_effective_date DATE
262 ,c_person_id NUMBER
263 )
264 is
265 SELECT tab1.los_val,
266 tab1.age_val,
267 tab1.comp_ref_amt,
268 tab1.hrs_wkd_val,
269 tab1.pct_fl_tm_val,
270 tab1.cmbn_age_n_los_val,
271 tab1.age_uom,
272 tab1.los_uom,
273 tab1.comp_ref_uom,
274 tab1.hrs_wkd_bndry_perd_cd,
275 tab1.frz_los_flag,
276 tab1.frz_age_flag,
277 tab1.frz_hrs_wkd_flag,
278 tab1.frz_cmp_lvl_flag,
279 tab1.frz_pct_fl_tm_flag,
280 tab1.frz_comb_age_and_los_flag,
281 tab1.rt_los_val,
282 tab1.rt_age_val,
283 tab1.rt_comp_ref_amt,
284 tab1.rt_hrs_wkd_val,
285 tab1.rt_pct_fl_tm_val,
286 tab1.rt_cmbn_age_n_los_val,
287 tab1.rt_age_uom,
288 tab1.rt_los_uom,
289 tab1.rt_comp_ref_uom,
290 tab1.rt_hrs_wkd_bndry_perd_cd,
291 tab1.rt_frz_los_flag,
292 tab1.rt_frz_age_flag,
293 tab1.rt_frz_hrs_wkd_flag,
294 tab1.rt_frz_cmp_lvl_flag,
295 tab1.rt_frz_pct_fl_tm_flag,
296 tab1.rt_frz_comb_age_and_los_flag,
297 tab1.ovrid_svc_dt,
298 tab1.prtn_ovridn_flag,
299 tab1.prtn_ovridn_thru_dt,
300 tab1.once_r_cntug_cd,
301 tab1.elig_flag,
302 tab1.pgm_id,
303 tab1.ptip_id,
304 tab1.pl_id,
305 tab1.plip_id,
306 tab1.prtn_strt_dt,
307 tab1.prtn_end_dt,
308 tab1.object_version_number,
309 tab1.elig_per_id,
310 tab1.per_in_ler_id
311 from ben_elig_per_f tab1
312 where tab1.per_in_ler_id is null
313 and tab1.person_id = c_person_id
314 and c_effective_date
315 between tab1.effective_start_date and tab1.effective_end_date
316 order by tab1.pgm_id, tab1.pl_id, tab1.plip_id;
317 --
318 begin
319 --
320 get_peppil_list
321 (p_person_id => p_person_id
325 );
322 ,p_effective_date => p_effective_date
323 --
324 ,p_peppil_va => l_perpilid_va
326 --
327 l_pil_cnt := 0;
328 --
329 if l_perpilid_va.count > 0
330 then
331 --
332 for pilvaen in l_perpilid_va.first..l_perpilid_va.last
333 loop
334 --
335 open c_pilinstance
336 (c_effective_date => p_effective_date
337 ,c_pil_id => l_perpilid_va(pilvaen)
338 );
339 fetch c_pilinstance BULK COLLECT INTO l_los_val_va,
340 l_age_val_va,
341 l_comp_ref_amt_va,
342 l_hrs_wkd_val_va,
343 l_pct_fl_tm_val_va,
344 l_cmbn_age_n_los_val_va,
345 l_age_uom_va,
346 l_los_uom_va,
347 l_comp_ref_uom_va,
348 l_hrs_wkd_bndry_perd_cd_va,
349 l_frz_los_flag_va,
350 l_frz_age_flag_va,
351 l_frz_hrs_wkd_flag_va,
355 l_rt_los_val_va,
352 l_frz_cmp_lvl_flag_va,
353 l_frz_pct_fl_tm_flag_va,
354 l_frz_comb_age_and_los_flag_va,
356 l_rt_age_val_va,
357 l_rt_comp_ref_amt_va,
358 l_rt_hrs_wkd_val_va,
359 l_rt_pct_fl_tm_val_va,
360 l_rt_cmbn_age_n_los_val_va,
361 l_rt_age_uom_va,
362 l_rt_los_uom_va,
363 l_rt_comp_ref_uom_va,
364 l_rt_hrs_wkd_bndry_perd_cd_va,
365 l_rt_frz_los_flag_va,
366 l_rt_frz_age_flag_va,
367 l_rt_frz_hrs_wkd_flag_va,
368 l_rt_frz_cmp_lvl_flag_va,
369 l_rt_frz_pct_fl_tm_flag_va,
370 l_rt_frz_cmb_age_los_flg_va,
371 l_ovrid_svc_dt_va,
372 l_prtn_ovridn_flag_va,
373 l_prtn_ovridn_thru_dt_va,
374 l_once_r_cntug_cd_va,
375 l_elig_flag_va,
376 l_pgm_id_va,
377 l_ptip_id_va,
378 l_pl_id_va,
379 l_plip_id_va,
380 l_prtn_strt_dt_va,
381 l_prtn_end_dt_va,
382 l_object_version_number_va,
383 l_elig_per_id_va,
384 l_per_in_ler_id_va;
385 close c_pilinstance;
386 --
387 if l_pgm_id_va.count > 0
388 then
389 --
390 for vaen in l_pgm_id_va.first..l_pgm_id_va.last
391 loop
392 --
393 l_hv := mod(nvl(l_pgm_id_va(vaen),1)
394 +nvl(l_pl_id_va(vaen),2)
395 +nvl(l_plip_id_va(vaen),3)
396 +nvl(l_ptip_id_va(vaen),4) ,ben_hash_utility.get_hash_key);
397 --
398 while ben_pep_cache.g_pilpep_instance.exists(l_hv)
399 loop
400 --
401 l_hv := l_hv+g_hash_jump;
402 --
403 end loop;
404 --
405 ben_pep_cache.g_pilpep_instance(l_hv).pgm_id := l_pgm_id_va(vaen);
406 ben_pep_cache.g_pilpep_instance(l_hv).pl_id := l_pl_id_va(vaen);
407 ben_pep_cache.g_pilpep_instance(l_hv).plip_id := l_plip_id_va(vaen);
408 ben_pep_cache.g_pilpep_instance(l_hv).ptip_id := l_ptip_id_va(vaen);
409 ben_pep_cache.g_pilpep_instance(l_hv).prtn_strt_dt := l_prtn_strt_dt_va(vaen);
410 ben_pep_cache.g_pilpep_instance(l_hv).los_val := l_los_val_va(vaen);
411 ben_pep_cache.g_pilpep_instance(l_hv).age_val := l_age_val_va(vaen);
412 ben_pep_cache.g_pilpep_instance(l_hv).comp_ref_amt := l_comp_ref_amt_va(vaen);
413 ben_pep_cache.g_pilpep_instance(l_hv).hrs_wkd_val := l_hrs_wkd_val_va(vaen);
414 ben_pep_cache.g_pilpep_instance(l_hv).pct_fl_tm_val := l_pct_fl_tm_val_va(vaen);
415 ben_pep_cache.g_pilpep_instance(l_hv).cmbn_age_n_los_val := l_cmbn_age_n_los_val_va(vaen);
416 ben_pep_cache.g_pilpep_instance(l_hv).age_uom := l_age_uom_va(vaen);
417 ben_pep_cache.g_pilpep_instance(l_hv).los_uom := l_los_uom_va(vaen);
418 ben_pep_cache.g_pilpep_instance(l_hv).comp_ref_uom := l_comp_ref_uom_va(vaen);
419 ben_pep_cache.g_pilpep_instance(l_hv).hrs_wkd_bndry_perd_cd := l_hrs_wkd_bndry_perd_cd_va(vaen);
420 ben_pep_cache.g_pilpep_instance(l_hv).frz_los_flag := l_frz_los_flag_va(vaen);
421 ben_pep_cache.g_pilpep_instance(l_hv).frz_age_flag := l_frz_age_flag_va(vaen);
422 ben_pep_cache.g_pilpep_instance(l_hv).frz_hrs_wkd_flag := l_frz_hrs_wkd_flag_va(vaen);
423 ben_pep_cache.g_pilpep_instance(l_hv).frz_cmp_lvl_flag := l_frz_cmp_lvl_flag_va(vaen);
424 ben_pep_cache.g_pilpep_instance(l_hv).frz_pct_fl_tm_flag := l_frz_pct_fl_tm_flag_va(vaen);
425 ben_pep_cache.g_pilpep_instance(l_hv).frz_comb_age_and_los_flag := l_frz_comb_age_and_los_flag_va(vaen);
426 ben_pep_cache.g_pilpep_instance(l_hv).rt_los_val := l_rt_los_val_va(vaen);
427 ben_pep_cache.g_pilpep_instance(l_hv).rt_age_val := l_rt_age_val_va(vaen);
428 ben_pep_cache.g_pilpep_instance(l_hv).rt_comp_ref_amt := l_rt_comp_ref_amt_va(vaen);
429 ben_pep_cache.g_pilpep_instance(l_hv).rt_hrs_wkd_val := l_rt_hrs_wkd_val_va(vaen);
430 ben_pep_cache.g_pilpep_instance(l_hv).rt_pct_fl_tm_val := l_rt_pct_fl_tm_val_va(vaen);
431 ben_pep_cache.g_pilpep_instance(l_hv).rt_cmbn_age_n_los_val := l_rt_cmbn_age_n_los_val_va(vaen);
432 ben_pep_cache.g_pilpep_instance(l_hv).rt_age_uom := l_rt_age_uom_va(vaen);
433 ben_pep_cache.g_pilpep_instance(l_hv).rt_los_uom := l_rt_los_uom_va(vaen);
434 ben_pep_cache.g_pilpep_instance(l_hv).rt_comp_ref_uom := l_rt_comp_ref_uom_va(vaen);
438 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_hrs_wkd_flag := l_rt_frz_hrs_wkd_flag_va(vaen);
435 ben_pep_cache.g_pilpep_instance(l_hv).rt_hrs_wkd_bndry_perd_cd := l_rt_hrs_wkd_bndry_perd_cd_va(vaen);
436 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_los_flag := l_rt_frz_los_flag_va(vaen);
437 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_age_flag := l_rt_frz_age_flag_va(vaen);
439 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_cmp_lvl_flag := l_rt_frz_cmp_lvl_flag_va(vaen);
440 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_pct_fl_tm_flag := l_rt_frz_pct_fl_tm_flag_va(vaen);
441 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_comb_age_and_los_flag := l_rt_frz_cmb_age_los_flg_va(vaen);
442 ben_pep_cache.g_pilpep_instance(l_hv).ovrid_svc_dt := l_ovrid_svc_dt_va(vaen);
443 ben_pep_cache.g_pilpep_instance(l_hv).prtn_ovridn_flag := l_prtn_ovridn_flag_va(vaen);
444 ben_pep_cache.g_pilpep_instance(l_hv).prtn_ovridn_thru_dt := l_prtn_ovridn_thru_dt_va(vaen);
445 ben_pep_cache.g_pilpep_instance(l_hv).once_r_cntug_cd := l_once_r_cntug_cd_va(vaen);
446 ben_pep_cache.g_pilpep_instance(l_hv).elig_flag := l_elig_flag_va(vaen);
447 ben_pep_cache.g_pilpep_instance(l_hv).elig_per_id := l_elig_per_id_va(vaen);
448 ben_pep_cache.g_pilpep_instance(l_hv).prtn_end_dt := l_prtn_end_dt_va(vaen);
449 ben_pep_cache.g_pilpep_instance(l_hv).per_in_ler_id := l_per_in_ler_id_va(vaen);
450 ben_pep_cache.g_pilpep_instance(l_hv).object_version_number := l_object_version_number_va(vaen);
451 --
452 end loop;
453 --
454 end if;
455 --
456 l_pil_cnt := l_pil_cnt+1;
457 --
458 end loop;
459 --
460 end if;
461 --
462 -- When no per in lers exist that are not backed out and voided then
463 -- look for eligibility with a null per in ler.
464 --
465 if l_pil_cnt = 0
466 then
467 --
468 open c_nopilinstance
469 (c_effective_date => p_effective_date
470 ,c_person_id => p_person_id
471 );
472 fetch c_nopilinstance BULK COLLECT INTO l_los_val_va,
473 l_age_val_va,
474 l_comp_ref_amt_va,
475 l_hrs_wkd_val_va,
476 l_pct_fl_tm_val_va,
477 l_cmbn_age_n_los_val_va,
478 l_age_uom_va,
479 l_los_uom_va,
480 l_comp_ref_uom_va,
481 l_hrs_wkd_bndry_perd_cd_va,
482 l_frz_los_flag_va,
483 l_frz_age_flag_va,
484 l_frz_hrs_wkd_flag_va,
485 l_frz_cmp_lvl_flag_va,
486 l_frz_pct_fl_tm_flag_va,
487 l_frz_comb_age_and_los_flag_va,
488 l_rt_los_val_va,
489 l_rt_age_val_va,
490 l_rt_comp_ref_amt_va,
491 l_rt_hrs_wkd_val_va,
492 l_rt_pct_fl_tm_val_va,
493 l_rt_cmbn_age_n_los_val_va,
494 l_rt_age_uom_va,
495 l_rt_los_uom_va,
496 l_rt_comp_ref_uom_va,
497 l_rt_hrs_wkd_bndry_perd_cd_va,
498 l_rt_frz_los_flag_va,
499 l_rt_frz_age_flag_va,
500 l_rt_frz_hrs_wkd_flag_va,
501 l_rt_frz_cmp_lvl_flag_va,
502 l_rt_frz_pct_fl_tm_flag_va,
503 l_rt_frz_cmb_age_los_flg_va,
504 l_ovrid_svc_dt_va,
505 l_prtn_ovridn_flag_va,
506 l_prtn_ovridn_thru_dt_va,
507 l_once_r_cntug_cd_va,
508 l_elig_flag_va,
509 l_pgm_id_va,
510 l_ptip_id_va,
511 l_pl_id_va,
512 l_plip_id_va,
513 l_prtn_strt_dt_va,
514 l_prtn_end_dt_va,
515 l_object_version_number_va,
516 l_elig_per_id_va,
517 l_per_in_ler_id_va;
518 close c_nopilinstance;
519 --
520 if l_pgm_id_va.count > 0
521 then
522 --
523 for vaen in l_pgm_id_va.first..l_pgm_id_va.last
524 loop
525 --
526 l_hv := mod(nvl(l_pgm_id_va(vaen),1)
527 +nvl(l_pl_id_va(vaen),2)
528 +nvl(l_plip_id_va(vaen),3)
529 +nvl(l_ptip_id_va(vaen),4) ,ben_hash_utility.get_hash_key);
530 --
531 while ben_pep_cache.g_pilpep_instance.exists(l_hv)
532 loop
533 --
534 l_hv := l_hv+g_hash_jump;
538 ben_pep_cache.g_pilpep_instance(l_hv).pgm_id := l_pgm_id_va(vaen);
535 --
536 end loop;
537 --
539 ben_pep_cache.g_pilpep_instance(l_hv).pl_id := l_pl_id_va(vaen);
540 ben_pep_cache.g_pilpep_instance(l_hv).plip_id := l_plip_id_va(vaen);
541 ben_pep_cache.g_pilpep_instance(l_hv).ptip_id := l_ptip_id_va(vaen);
542 ben_pep_cache.g_pilpep_instance(l_hv).prtn_strt_dt := l_prtn_strt_dt_va(vaen);
543 ben_pep_cache.g_pilpep_instance(l_hv).los_val := l_los_val_va(vaen);
544 ben_pep_cache.g_pilpep_instance(l_hv).age_val := l_age_val_va(vaen);
545 ben_pep_cache.g_pilpep_instance(l_hv).comp_ref_amt := l_comp_ref_amt_va(vaen);
546 ben_pep_cache.g_pilpep_instance(l_hv).hrs_wkd_val := l_hrs_wkd_val_va(vaen);
547 ben_pep_cache.g_pilpep_instance(l_hv).pct_fl_tm_val := l_pct_fl_tm_val_va(vaen);
548 ben_pep_cache.g_pilpep_instance(l_hv).cmbn_age_n_los_val := l_cmbn_age_n_los_val_va(vaen);
549 ben_pep_cache.g_pilpep_instance(l_hv).age_uom := l_age_uom_va(vaen);
550 ben_pep_cache.g_pilpep_instance(l_hv).los_uom := l_los_uom_va(vaen);
551 ben_pep_cache.g_pilpep_instance(l_hv).comp_ref_uom := l_comp_ref_uom_va(vaen);
552 ben_pep_cache.g_pilpep_instance(l_hv).hrs_wkd_bndry_perd_cd := l_hrs_wkd_bndry_perd_cd_va(vaen);
553 ben_pep_cache.g_pilpep_instance(l_hv).frz_los_flag := l_frz_los_flag_va(vaen);
554 ben_pep_cache.g_pilpep_instance(l_hv).frz_age_flag := l_frz_age_flag_va(vaen);
555 ben_pep_cache.g_pilpep_instance(l_hv).frz_hrs_wkd_flag := l_frz_hrs_wkd_flag_va(vaen);
556 ben_pep_cache.g_pilpep_instance(l_hv).frz_cmp_lvl_flag := l_frz_cmp_lvl_flag_va(vaen);
557 ben_pep_cache.g_pilpep_instance(l_hv).frz_pct_fl_tm_flag := l_frz_pct_fl_tm_flag_va(vaen);
558 ben_pep_cache.g_pilpep_instance(l_hv).frz_comb_age_and_los_flag := l_frz_comb_age_and_los_flag_va(vaen);
559 ben_pep_cache.g_pilpep_instance(l_hv).rt_los_val := l_rt_los_val_va(vaen);
560 ben_pep_cache.g_pilpep_instance(l_hv).rt_age_val := l_rt_age_val_va(vaen);
561 ben_pep_cache.g_pilpep_instance(l_hv).rt_comp_ref_amt := l_rt_comp_ref_amt_va(vaen);
562 ben_pep_cache.g_pilpep_instance(l_hv).rt_hrs_wkd_val := l_rt_hrs_wkd_val_va(vaen);
563 ben_pep_cache.g_pilpep_instance(l_hv).rt_pct_fl_tm_val := l_rt_pct_fl_tm_val_va(vaen);
564 ben_pep_cache.g_pilpep_instance(l_hv).rt_cmbn_age_n_los_val := l_rt_cmbn_age_n_los_val_va(vaen);
565 ben_pep_cache.g_pilpep_instance(l_hv).rt_age_uom := l_rt_age_uom_va(vaen);
566 ben_pep_cache.g_pilpep_instance(l_hv).rt_los_uom := l_rt_los_uom_va(vaen);
567 ben_pep_cache.g_pilpep_instance(l_hv).rt_comp_ref_uom := l_rt_comp_ref_uom_va(vaen);
568 ben_pep_cache.g_pilpep_instance(l_hv).rt_hrs_wkd_bndry_perd_cd := l_rt_hrs_wkd_bndry_perd_cd_va(vaen);
569 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_los_flag := l_rt_frz_los_flag_va(vaen);
570 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_age_flag := l_rt_frz_age_flag_va(vaen);
571 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_hrs_wkd_flag := l_rt_frz_hrs_wkd_flag_va(vaen);
572 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_cmp_lvl_flag := l_rt_frz_cmp_lvl_flag_va(vaen);
573 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_pct_fl_tm_flag := l_rt_frz_pct_fl_tm_flag_va(vaen);
574 ben_pep_cache.g_pilpep_instance(l_hv).rt_frz_comb_age_and_los_flag := l_rt_frz_cmb_age_los_flg_va(vaen);
575 ben_pep_cache.g_pilpep_instance(l_hv).ovrid_svc_dt := l_ovrid_svc_dt_va(vaen);
576 ben_pep_cache.g_pilpep_instance(l_hv).prtn_ovridn_flag := l_prtn_ovridn_flag_va(vaen);
577 ben_pep_cache.g_pilpep_instance(l_hv).prtn_ovridn_thru_dt := l_prtn_ovridn_thru_dt_va(vaen);
578 ben_pep_cache.g_pilpep_instance(l_hv).once_r_cntug_cd := l_once_r_cntug_cd_va(vaen);
579 ben_pep_cache.g_pilpep_instance(l_hv).elig_flag := l_elig_flag_va(vaen);
580 ben_pep_cache.g_pilpep_instance(l_hv).elig_per_id := l_elig_per_id_va(vaen);
581 ben_pep_cache.g_pilpep_instance(l_hv).prtn_end_dt := l_prtn_end_dt_va(vaen);
582 ben_pep_cache.g_pilpep_instance(l_hv).per_in_ler_id := l_per_in_ler_id_va(vaen);
583 ben_pep_cache.g_pilpep_instance(l_hv).object_version_number := l_object_version_number_va(vaen);
584 --
585 end loop;
586 --
587 end if;
588 --
589 end if;
590 --
591 end write_pilpep_cache;
592 --
593 procedure write_pilepo_cache
594 (p_person_id in number
595 ,p_business_group_id in number
596 ,p_effective_date in date
597 )
598 is
599 --
600 l_proc varchar2(72) := 'write_pilepo_cache';
601 --
602 l_allpilid_va benutils.g_number_table := benutils.g_number_table();
603 l_perpilid_va benutils.g_number_table := benutils.g_number_table();
604 --
605 l_los_val_va benutils.g_number_table := benutils.g_number_table();
606 l_age_val_va benutils.g_number_table := benutils.g_number_table();
607 l_comp_ref_amt_va benutils.g_number_table := benutils.g_number_table();
608 l_hrs_wkd_val_va benutils.g_number_table := benutils.g_number_table();
609 l_pct_fl_tm_val_va benutils.g_number_table := benutils.g_number_table();
610 l_cmbn_age_n_los_val_va benutils.g_number_table := benutils.g_number_table();
611 l_age_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
612 l_los_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
613 l_comp_ref_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
614 l_hrs_wkd_bndry_perd_cd_va benutils.g_varchar2_table := benutils.g_varchar2_table();
618 l_frz_cmp_lvl_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
615 l_frz_los_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
616 l_frz_age_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
617 l_frz_hrs_wkd_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
619 l_frz_pct_fl_tm_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
620 l_frz_comb_age_and_los_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
621 l_rt_los_val_va benutils.g_number_table := benutils.g_number_table();
622 l_rt_age_val_va benutils.g_number_table := benutils.g_number_table();
623 l_rt_comp_ref_amt_va benutils.g_number_table := benutils.g_number_table();
624 l_rt_hrs_wkd_val_va benutils.g_number_table := benutils.g_number_table();
625 l_rt_pct_fl_tm_val_va benutils.g_number_table := benutils.g_number_table();
626 l_rt_cmbn_age_n_los_val_va benutils.g_number_table := benutils.g_number_table();
627 l_rt_age_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
628 l_rt_los_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
629 l_rt_comp_ref_uom_va benutils.g_varchar2_table := benutils.g_varchar2_table();
630 l_rt_hrs_wkd_bndry_perd_cd_va benutils.g_varchar2_table := benutils.g_varchar2_table();
631 l_rt_frz_los_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
632 l_rt_frz_age_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
633 l_rt_frz_hrs_wkd_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
634 l_rt_frz_cmp_lvl_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
635 l_rt_frz_pct_fl_tm_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
636 l_rt_frz_cmb_age_los_flg_va benutils.g_varchar2_table := benutils.g_varchar2_table();
637 l_ovrid_svc_dt_va benutils.g_date_table := benutils.g_date_table();
638 l_prtn_ovridn_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
639 l_prtn_ovridn_thru_dt_va benutils.g_date_table := benutils.g_date_table();
640 l_once_r_cntug_cd_va benutils.g_varchar2_table := benutils.g_varchar2_table();
641 l_elig_flag_va benutils.g_varchar2_table := benutils.g_varchar2_table();
642 l_pgm_id_va benutils.g_number_table := benutils.g_number_table();
643 l_ptip_id_va benutils.g_number_table := benutils.g_number_table();
644 l_pl_id_va benutils.g_number_table := benutils.g_number_table();
645 l_plip_id_va benutils.g_number_table := benutils.g_number_table();
646 l_opt_id_va benutils.g_number_table := benutils.g_number_table();
647 l_prtn_strt_dt_va benutils.g_date_table := benutils.g_date_table();
648 l_prtn_end_dt_va benutils.g_date_table := benutils.g_date_table();
649 l_elig_per_opt_id_va benutils.g_number_table := benutils.g_number_table();
650 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
651 l_elig_per_id_va benutils.g_number_table := benutils.g_number_table();
652 l_per_in_ler_id_va benutils.g_number_table := benutils.g_number_table();
653 l_pep_psd_va benutils.g_date_table := benutils.g_date_table();
654 l_pep_ped_va benutils.g_date_table := benutils.g_date_table();
655 l_pil_stcd_va benutils.g_varchar2_table := benutils.g_varchar2_table();
656 --
657 l_hv pls_integer;
658 l_not_hash_found boolean;
659 l_exclude boolean;
660 --
661 l_pil_cnt pls_integer;
662 --
663 cursor c_pilinstance
664 (c_effective_date DATE
665 ,c_pil_id NUMBER
666 )
667 is
668 SELECT tab1.los_val
669 ,tab1.age_val
670 ,tab1.comp_ref_amt
671 ,tab1.hrs_wkd_val
672 ,tab1.pct_fl_tm_val
673 ,tab1.cmbn_age_n_los_val
674 ,tab1.age_uom
675 ,tab1.los_uom
676 ,tab1.comp_ref_uom
677 ,tab1.hrs_wkd_bndry_perd_cd
678 ,tab1.frz_los_flag
679 ,tab1.frz_age_flag
680 ,tab1.frz_hrs_wkd_flag
681 ,tab1.frz_cmp_lvl_flag
682 ,tab1.frz_pct_fl_tm_flag
683 ,tab1.frz_comb_age_and_los_flag
684 ,tab1.rt_los_val
685 ,tab1.rt_age_val
686 ,tab1.rt_comp_ref_amt
687 ,tab1.rt_hrs_wkd_val
688 ,tab1.rt_pct_fl_tm_val
689 ,tab1.rt_cmbn_age_n_los_val
690 ,tab1.rt_age_uom
691 ,tab1.rt_los_uom
692 ,tab1.rt_comp_ref_uom
693 ,tab1.rt_hrs_wkd_bndry_perd_cd
694 ,tab1.rt_frz_los_flag
695 ,tab1.rt_frz_age_flag
696 ,tab1.rt_frz_hrs_wkd_flag
697 ,tab1.rt_frz_cmp_lvl_flag
698 ,tab1.rt_frz_pct_fl_tm_flag
699 ,tab1.rt_frz_comb_age_and_los_flag
700 ,tab1.ovrid_svc_dt
701 ,tab1.prtn_ovridn_flag
702 ,tab1.prtn_ovridn_thru_dt
703 ,tab1.once_r_cntug_cd
704 ,tab1.elig_flag
705 ,tab2.pgm_id
706 ,tab2.ptip_id
707 ,tab2.pl_id
708 ,tab2.plip_id
709 ,tab1.opt_id
710 ,tab1.prtn_strt_dt
711 ,tab1.prtn_end_dt
712 ,tab1.elig_per_opt_id
713 ,tab1.object_version_number
714 ,tab2.elig_per_id
715 ,tab2.per_in_ler_id
716 ,tab2.prtn_strt_dt pep_psd
720 ben_elig_per_f tab2,
717 ,tab2.prtn_end_dt pep_ped
718 ,tab3.per_in_ler_stat_cd
719 from ben_elig_per_opt_f tab1,
721 ben_per_in_ler tab3
722 where tab1.opt_id is not null
723 and tab1.elig_per_id = tab2.elig_per_id
724 and tab3.per_in_ler_id = tab1.per_in_ler_id
725 and c_effective_date
726 between tab1.effective_start_date and tab1.effective_end_date
727 and c_effective_date
728 between tab2.effective_start_date and tab2.effective_end_date
729 and tab1.per_in_ler_id = c_pil_id
730 order by tab1.opt_id, tab2.pl_id, tab2.pgm_id;
731 --
732 cursor c_nopilinstance
733 (c_effective_date DATE
734 ,c_person_id NUMBER
735 )
736 is
737 SELECT tab1.los_val
738 ,tab1.age_val
739 ,tab1.comp_ref_amt
740 ,tab1.hrs_wkd_val
741 ,tab1.pct_fl_tm_val
742 ,tab1.cmbn_age_n_los_val
743 ,tab1.age_uom
744 ,tab1.los_uom
745 ,tab1.comp_ref_uom
746 ,tab1.hrs_wkd_bndry_perd_cd
747 ,tab1.frz_los_flag
748 ,tab1.frz_age_flag
749 ,tab1.frz_hrs_wkd_flag
750 ,tab1.frz_cmp_lvl_flag
751 ,tab1.frz_pct_fl_tm_flag
752 ,tab1.frz_comb_age_and_los_flag
753 ,tab1.rt_los_val
754 ,tab1.rt_age_val
755 ,tab1.rt_comp_ref_amt
756 ,tab1.rt_hrs_wkd_val
757 ,tab1.rt_pct_fl_tm_val
758 ,tab1.rt_cmbn_age_n_los_val
759 ,tab1.rt_age_uom
760 ,tab1.rt_los_uom
761 ,tab1.rt_comp_ref_uom
762 ,tab1.rt_hrs_wkd_bndry_perd_cd
763 ,tab1.rt_frz_los_flag
764 ,tab1.rt_frz_age_flag
765 ,tab1.rt_frz_hrs_wkd_flag
766 ,tab1.rt_frz_cmp_lvl_flag
767 ,tab1.rt_frz_pct_fl_tm_flag
768 ,tab1.rt_frz_comb_age_and_los_flag
769 ,tab1.ovrid_svc_dt
770 ,tab1.prtn_ovridn_flag
771 ,tab1.prtn_ovridn_thru_dt
772 ,tab1.once_r_cntug_cd
773 ,tab1.elig_flag
774 ,tab2.pgm_id
775 ,tab2.ptip_id
776 ,tab2.pl_id
777 ,tab2.plip_id
778 ,tab1.opt_id
779 ,tab1.prtn_strt_dt
780 ,tab1.prtn_end_dt
781 ,tab1.elig_per_opt_id
782 ,tab1.object_version_number
783 ,tab2.elig_per_id
787 from ben_elig_per_opt_f tab1,
784 ,tab2.per_in_ler_id
785 ,tab2.prtn_strt_dt pep_psd
786 ,tab2.prtn_end_dt pep_ped
788 ben_elig_per_f tab2
789 where tab2.per_in_ler_id is null
790 and tab2.person_id = c_person_id
791 and tab1.opt_id is not null
792 and tab1.elig_per_id = tab2.elig_per_id
793 and c_effective_date
794 between tab1.effective_start_date and tab1.effective_end_date
795 and c_effective_date
796 between tab2.effective_start_date and tab2.effective_end_date
797 order by tab1.opt_id, tab2.pl_id, tab2.pgm_id;
798 --
799 begin
800 --
801 get_peppil_list
802 (p_person_id => p_person_id
803 ,p_effective_date => p_effective_date
804 --
805 ,p_peppil_va => l_perpilid_va
806 );
807 --
808 if l_perpilid_va.count > 0
809 then
810 --
811 l_pil_cnt := 0;
812 --
813 for pilvaen in l_perpilid_va.first..l_perpilid_va.last
814 loop
815 --
816 open c_pilinstance
817 (c_effective_date => p_effective_date
818 ,c_pil_id => l_perpilid_va(pilvaen)
819 );
820 fetch c_pilinstance BULK COLLECT INTO l_los_val_va,
821 l_age_val_va,
822 l_comp_ref_amt_va,
823 l_hrs_wkd_val_va,
824 l_pct_fl_tm_val_va,
825 l_cmbn_age_n_los_val_va,
826 l_age_uom_va,
827 l_los_uom_va,
828 l_comp_ref_uom_va,
829 l_hrs_wkd_bndry_perd_cd_va,
830 l_frz_los_flag_va,
831 l_frz_age_flag_va,
835 l_frz_comb_age_and_los_flag_va,
832 l_frz_hrs_wkd_flag_va,
833 l_frz_cmp_lvl_flag_va,
834 l_frz_pct_fl_tm_flag_va,
836 l_rt_los_val_va,
837 l_rt_age_val_va,
838 l_rt_comp_ref_amt_va,
839 l_rt_hrs_wkd_val_va,
840 l_rt_pct_fl_tm_val_va,
841 l_rt_cmbn_age_n_los_val_va,
842 l_rt_age_uom_va,
843 l_rt_los_uom_va,
844 l_rt_comp_ref_uom_va,
845 l_rt_hrs_wkd_bndry_perd_cd_va,
846 l_rt_frz_los_flag_va,
847 l_rt_frz_age_flag_va,
848 l_rt_frz_hrs_wkd_flag_va,
849 l_rt_frz_cmp_lvl_flag_va,
850 l_rt_frz_pct_fl_tm_flag_va,
851 l_rt_frz_cmb_age_los_flg_va,
852 l_ovrid_svc_dt_va,
853 l_prtn_ovridn_flag_va,
854 l_prtn_ovridn_thru_dt_va,
855 l_once_r_cntug_cd_va,
856 l_elig_flag_va,
857 l_pgm_id_va,
858 l_ptip_id_va,
859 l_pl_id_va,
860 l_plip_id_va,
861 l_opt_id_va,
862 l_prtn_strt_dt_va,
863 l_prtn_end_dt_va,
864 l_elig_per_opt_id_va,
865 l_object_version_number_va,
866 l_elig_per_id_va,
867 l_per_in_ler_id_va,
868 l_pep_psd_va,
869 l_pep_ped_va,
870 l_pil_stcd_va;
871 close c_pilinstance;
872 --
873 if l_los_val_va.count > 0
874 then
875 --
876 for vaen in l_los_val_va.first..l_los_val_va.last
877 loop
878 --
879 l_exclude := false;
880 --
881 -- Exclude for PIL status codes
882 --
883 if l_pil_stcd_va(vaen) = 'VOIDD'
884 or l_pil_stcd_va(vaen) = 'BCKDT'
885 then
886 --
887 l_exclude := true;
888 --
889 end if;
890 --
891 if not l_exclude
892 then
893 --
894 l_hv := mod(nvl(l_opt_id_va(vaen),1)+nvl(l_pgm_id_va(vaen),2)
895 +nvl(l_pl_id_va(vaen),3) +nvl(l_plip_id_va(vaen),4),
896 ben_hash_utility.get_hash_key);
897 --
898 while ben_pep_cache.g_optpilepo_instance.exists(l_hv)
899 loop
900 --
901 l_hv := l_hv+g_hash_jump;
902 --
903 end loop;
904 --
905 ben_pep_cache.g_optpilepo_instance(l_hv).opt_id := l_opt_id_va(vaen);
906 ben_pep_cache.g_optpilepo_instance(l_hv).pgm_id := l_pgm_id_va(vaen);
907 ben_pep_cache.g_optpilepo_instance(l_hv).pl_id := l_pl_id_va(vaen);
908 ben_pep_cache.g_optpilepo_instance(l_hv).plip_id := l_plip_id_va(vaen);
909 ben_pep_cache.g_optpilepo_instance(l_hv).prtn_strt_dt := l_prtn_strt_dt_va(vaen);
910 ben_pep_cache.g_optpilepo_instance(l_hv).los_val := l_los_val_va(vaen);
911 ben_pep_cache.g_optpilepo_instance(l_hv).age_val := l_age_val_va(vaen);
912 ben_pep_cache.g_optpilepo_instance(l_hv).comp_ref_amt := l_comp_ref_amt_va(vaen);
913 ben_pep_cache.g_optpilepo_instance(l_hv).hrs_wkd_val := l_hrs_wkd_val_va(vaen);
914 ben_pep_cache.g_optpilepo_instance(l_hv).pct_fl_tm_val := l_pct_fl_tm_val_va(vaen);
915 ben_pep_cache.g_optpilepo_instance(l_hv).cmbn_age_n_los_val := l_cmbn_age_n_los_val_va(vaen);
916 ben_pep_cache.g_optpilepo_instance(l_hv).age_uom := l_age_uom_va(vaen);
917 ben_pep_cache.g_optpilepo_instance(l_hv).los_uom := l_los_uom_va(vaen);
918 ben_pep_cache.g_optpilepo_instance(l_hv).comp_ref_uom := l_comp_ref_uom_va(vaen);
922 ben_pep_cache.g_optpilepo_instance(l_hv).frz_hrs_wkd_flag := l_frz_hrs_wkd_flag_va(vaen);
919 ben_pep_cache.g_optpilepo_instance(l_hv).hrs_wkd_bndry_perd_cd := l_hrs_wkd_bndry_perd_cd_va(vaen);
920 ben_pep_cache.g_optpilepo_instance(l_hv).frz_los_flag := l_frz_los_flag_va(vaen);
921 ben_pep_cache.g_optpilepo_instance(l_hv).frz_age_flag := l_frz_age_flag_va(vaen);
923 ben_pep_cache.g_optpilepo_instance(l_hv).frz_cmp_lvl_flag := l_frz_cmp_lvl_flag_va(vaen);
924 ben_pep_cache.g_optpilepo_instance(l_hv).frz_pct_fl_tm_flag := l_frz_pct_fl_tm_flag_va(vaen);
925 ben_pep_cache.g_optpilepo_instance(l_hv).frz_comb_age_and_los_flag := l_frz_comb_age_and_los_flag_va(vaen);
926 ben_pep_cache.g_optpilepo_instance(l_hv).rt_los_val := l_rt_los_val_va(vaen);
927 ben_pep_cache.g_optpilepo_instance(l_hv).rt_age_val := l_rt_age_val_va(vaen);
928 ben_pep_cache.g_optpilepo_instance(l_hv).rt_comp_ref_amt := l_rt_comp_ref_amt_va(vaen);
929 ben_pep_cache.g_optpilepo_instance(l_hv).rt_hrs_wkd_val := l_rt_hrs_wkd_val_va(vaen);
930 ben_pep_cache.g_optpilepo_instance(l_hv).rt_pct_fl_tm_val := l_rt_pct_fl_tm_val_va(vaen);
931 ben_pep_cache.g_optpilepo_instance(l_hv).rt_cmbn_age_n_los_val := l_rt_cmbn_age_n_los_val_va(vaen);
932 ben_pep_cache.g_optpilepo_instance(l_hv).rt_age_uom := l_rt_age_uom_va(vaen);
933 ben_pep_cache.g_optpilepo_instance(l_hv).rt_los_uom := l_rt_los_uom_va(vaen);
934 ben_pep_cache.g_optpilepo_instance(l_hv).rt_comp_ref_uom := l_rt_comp_ref_uom_va(vaen);
935 ben_pep_cache.g_optpilepo_instance(l_hv).rt_hrs_wkd_bndry_perd_cd := l_rt_hrs_wkd_bndry_perd_cd_va(vaen);
936 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_los_flag := l_rt_frz_los_flag_va(vaen);
937 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_age_flag := l_rt_frz_age_flag_va(vaen);
938 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_hrs_wkd_flag := l_rt_frz_hrs_wkd_flag_va(vaen);
939 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_cmp_lvl_flag := l_rt_frz_cmp_lvl_flag_va(vaen);
940 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_pct_fl_tm_flag := l_rt_frz_pct_fl_tm_flag_va(vaen);
941 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_comb_age_and_los_flag := l_rt_frz_cmb_age_los_flg_va(vaen);
942 ben_pep_cache.g_optpilepo_instance(l_hv).ovrid_svc_dt := l_ovrid_svc_dt_va(vaen);
943 ben_pep_cache.g_optpilepo_instance(l_hv).prtn_ovridn_flag := l_prtn_ovridn_flag_va(vaen);
944 ben_pep_cache.g_optpilepo_instance(l_hv).prtn_ovridn_thru_dt := l_prtn_ovridn_thru_dt_va(vaen);
945 ben_pep_cache.g_optpilepo_instance(l_hv).once_r_cntug_cd := l_once_r_cntug_cd_va(vaen);
946 ben_pep_cache.g_optpilepo_instance(l_hv).elig_flag := l_elig_flag_va(vaen);
947 ben_pep_cache.g_optpilepo_instance(l_hv).prtn_end_dt := l_prtn_end_dt_va(vaen);
948 ben_pep_cache.g_optpilepo_instance(l_hv).elig_per_opt_id := l_elig_per_opt_id_va(vaen);
949 ben_pep_cache.g_optpilepo_instance(l_hv).object_version_number := l_object_version_number_va(vaen);
950 ben_pep_cache.g_optpilepo_instance(l_hv).elig_per_id := l_elig_per_id_va(vaen);
951 ben_pep_cache.g_optpilepo_instance(l_hv).per_in_ler_id := l_per_in_ler_id_va(vaen);
952 ben_pep_cache.g_optpilepo_instance(l_hv).pep_prtn_strt_dt := l_pep_psd_va(vaen);
953 ben_pep_cache.g_optpilepo_instance(l_hv).pep_prtn_end_dt := l_pep_ped_va(vaen);
954 --
955 end if;
956 --
957 end loop;
958 --
959 end if;
960 --
961 l_pil_cnt := l_pil_cnt+1;
962 --
963 end loop;
964 --
965 -- When no per in lers exist that are not backed out and voided then
966 -- look for eligibility with a null per in ler.
967 --
968 if l_pil_cnt = 0
969 then
973 ,c_person_id => p_person_id
970 --
971 open c_nopilinstance
972 (c_effective_date => p_effective_date
974 );
975 fetch c_nopilinstance BULK COLLECT INTO l_los_val_va,
976 l_age_val_va,
977 l_comp_ref_amt_va,
978 l_hrs_wkd_val_va,
979 l_pct_fl_tm_val_va,
980 l_cmbn_age_n_los_val_va,
981 l_age_uom_va,
982 l_los_uom_va,
983 l_comp_ref_uom_va,
984 l_hrs_wkd_bndry_perd_cd_va,
985 l_frz_los_flag_va,
986 l_frz_age_flag_va,
987 l_frz_hrs_wkd_flag_va,
988 l_frz_cmp_lvl_flag_va,
989 l_frz_pct_fl_tm_flag_va,
990 l_frz_comb_age_and_los_flag_va,
991 l_rt_los_val_va,
992 l_rt_age_val_va,
993 l_rt_comp_ref_amt_va,
994 l_rt_hrs_wkd_val_va,
995 l_rt_pct_fl_tm_val_va,
996 l_rt_cmbn_age_n_los_val_va,
997 l_rt_age_uom_va,
998 l_rt_los_uom_va,
999 l_rt_comp_ref_uom_va,
1000 l_rt_hrs_wkd_bndry_perd_cd_va,
1001 l_rt_frz_los_flag_va,
1002 l_rt_frz_age_flag_va,
1003 l_rt_frz_hrs_wkd_flag_va,
1004 l_rt_frz_cmp_lvl_flag_va,
1005 l_rt_frz_pct_fl_tm_flag_va,
1006 l_rt_frz_cmb_age_los_flg_va,
1007 l_ovrid_svc_dt_va,
1008 l_prtn_ovridn_flag_va,
1009 l_prtn_ovridn_thru_dt_va,
1010 l_once_r_cntug_cd_va,
1011 l_elig_flag_va,
1012 l_pgm_id_va,
1013 l_ptip_id_va,
1014 l_pl_id_va,
1015 l_plip_id_va,
1016 l_opt_id_va,
1017 l_prtn_strt_dt_va,
1018 l_prtn_end_dt_va,
1019 l_elig_per_opt_id_va,
1020 l_object_version_number_va,
1021 l_elig_per_id_va,
1022 l_per_in_ler_id_va,
1023 l_pep_psd_va,
1024 l_pep_ped_va;
1025 close c_nopilinstance;
1026 --
1027 if l_los_val_va.count > 0
1028 then
1029 --
1030 for vaen in l_los_val_va.first..l_los_val_va.last
1031 loop
1032 --
1033 l_hv := mod(nvl(l_opt_id_va(vaen),1)+nvl(l_pgm_id_va(vaen),2)
1034 +nvl(l_pl_id_va(vaen),3) +nvl(l_plip_id_va(vaen),4),
1035 ben_hash_utility.get_hash_key);
1036 --
1037 while ben_pep_cache.g_optpilepo_instance.exists(l_hv)
1038 loop
1039 --
1040 l_hv := l_hv+g_hash_jump;
1041 --
1042 end loop;
1043 --
1044 ben_pep_cache.g_optpilepo_instance(l_hv).opt_id := l_opt_id_va(vaen);
1045 ben_pep_cache.g_optpilepo_instance(l_hv).pgm_id := l_pgm_id_va(vaen);
1046 ben_pep_cache.g_optpilepo_instance(l_hv).pl_id := l_pl_id_va(vaen);
1047 ben_pep_cache.g_optpilepo_instance(l_hv).plip_id := l_plip_id_va(vaen);
1048 ben_pep_cache.g_optpilepo_instance(l_hv).prtn_strt_dt := l_prtn_strt_dt_va(vaen);
1049 ben_pep_cache.g_optpilepo_instance(l_hv).los_val := l_los_val_va(vaen);
1050 ben_pep_cache.g_optpilepo_instance(l_hv).age_val := l_age_val_va(vaen);
1051 ben_pep_cache.g_optpilepo_instance(l_hv).comp_ref_amt := l_comp_ref_amt_va(vaen);
1052 ben_pep_cache.g_optpilepo_instance(l_hv).hrs_wkd_val := l_hrs_wkd_val_va(vaen);
1053 ben_pep_cache.g_optpilepo_instance(l_hv).pct_fl_tm_val := l_pct_fl_tm_val_va(vaen);
1054 ben_pep_cache.g_optpilepo_instance(l_hv).cmbn_age_n_los_val := l_cmbn_age_n_los_val_va(vaen);
1055 ben_pep_cache.g_optpilepo_instance(l_hv).age_uom := l_age_uom_va(vaen);
1056 ben_pep_cache.g_optpilepo_instance(l_hv).los_uom := l_los_uom_va(vaen);
1057 ben_pep_cache.g_optpilepo_instance(l_hv).comp_ref_uom := l_comp_ref_uom_va(vaen);
1058 ben_pep_cache.g_optpilepo_instance(l_hv).hrs_wkd_bndry_perd_cd := l_hrs_wkd_bndry_perd_cd_va(vaen);
1059 ben_pep_cache.g_optpilepo_instance(l_hv).frz_los_flag := l_frz_los_flag_va(vaen);
1060 ben_pep_cache.g_optpilepo_instance(l_hv).frz_age_flag := l_frz_age_flag_va(vaen);
1061 ben_pep_cache.g_optpilepo_instance(l_hv).frz_hrs_wkd_flag := l_frz_hrs_wkd_flag_va(vaen);
1062 ben_pep_cache.g_optpilepo_instance(l_hv).frz_cmp_lvl_flag := l_frz_cmp_lvl_flag_va(vaen);
1063 ben_pep_cache.g_optpilepo_instance(l_hv).frz_pct_fl_tm_flag := l_frz_pct_fl_tm_flag_va(vaen);
1064 ben_pep_cache.g_optpilepo_instance(l_hv).frz_comb_age_and_los_flag := l_frz_comb_age_and_los_flag_va(vaen);
1068 ben_pep_cache.g_optpilepo_instance(l_hv).rt_hrs_wkd_val := l_rt_hrs_wkd_val_va(vaen);
1065 ben_pep_cache.g_optpilepo_instance(l_hv).rt_los_val := l_rt_los_val_va(vaen);
1066 ben_pep_cache.g_optpilepo_instance(l_hv).rt_age_val := l_rt_age_val_va(vaen);
1067 ben_pep_cache.g_optpilepo_instance(l_hv).rt_comp_ref_amt := l_rt_comp_ref_amt_va(vaen);
1069 ben_pep_cache.g_optpilepo_instance(l_hv).rt_pct_fl_tm_val := l_rt_pct_fl_tm_val_va(vaen);
1070 ben_pep_cache.g_optpilepo_instance(l_hv).rt_cmbn_age_n_los_val := l_rt_cmbn_age_n_los_val_va(vaen);
1071 ben_pep_cache.g_optpilepo_instance(l_hv).rt_age_uom := l_rt_age_uom_va(vaen);
1072 ben_pep_cache.g_optpilepo_instance(l_hv).rt_los_uom := l_rt_los_uom_va(vaen);
1073 ben_pep_cache.g_optpilepo_instance(l_hv).rt_comp_ref_uom := l_rt_comp_ref_uom_va(vaen);
1074 ben_pep_cache.g_optpilepo_instance(l_hv).rt_hrs_wkd_bndry_perd_cd := l_rt_hrs_wkd_bndry_perd_cd_va(vaen);
1075 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_los_flag := l_rt_frz_los_flag_va(vaen);
1076 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_age_flag := l_rt_frz_age_flag_va(vaen);
1077 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_hrs_wkd_flag := l_rt_frz_hrs_wkd_flag_va(vaen);
1078 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_cmp_lvl_flag := l_rt_frz_cmp_lvl_flag_va(vaen);
1079 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_pct_fl_tm_flag := l_rt_frz_pct_fl_tm_flag_va(vaen);
1080 ben_pep_cache.g_optpilepo_instance(l_hv).rt_frz_comb_age_and_los_flag := l_rt_frz_cmb_age_los_flg_va(vaen);
1081 ben_pep_cache.g_optpilepo_instance(l_hv).ovrid_svc_dt := l_ovrid_svc_dt_va(vaen);
1082 ben_pep_cache.g_optpilepo_instance(l_hv).prtn_ovridn_flag := l_prtn_ovridn_flag_va(vaen);
1083 ben_pep_cache.g_optpilepo_instance(l_hv).prtn_ovridn_thru_dt := l_prtn_ovridn_thru_dt_va(vaen);
1084 ben_pep_cache.g_optpilepo_instance(l_hv).once_r_cntug_cd := l_once_r_cntug_cd_va(vaen);
1085 ben_pep_cache.g_optpilepo_instance(l_hv).elig_flag := l_elig_flag_va(vaen);
1086 ben_pep_cache.g_optpilepo_instance(l_hv).prtn_end_dt := l_prtn_end_dt_va(vaen);
1087 ben_pep_cache.g_optpilepo_instance(l_hv).elig_per_opt_id := l_elig_per_opt_id_va(vaen);
1088 ben_pep_cache.g_optpilepo_instance(l_hv).object_version_number := l_object_version_number_va(vaen);
1089 ben_pep_cache.g_optpilepo_instance(l_hv).elig_per_id := l_elig_per_id_va(vaen);
1090 ben_pep_cache.g_optpilepo_instance(l_hv).per_in_ler_id := l_per_in_ler_id_va(vaen);
1091 ben_pep_cache.g_optpilepo_instance(l_hv).pep_prtn_strt_dt := l_pep_psd_va(vaen);
1092 ben_pep_cache.g_optpilepo_instance(l_hv).pep_prtn_end_dt := l_pep_ped_va(vaen);
1093 --
1094 end loop;
1095 --
1096 end if;
1097 --
1098 end if;
1099 --
1100 end if;
1101 --
1102 end write_pilepo_cache;
1103 --
1104 end ben_pep_cache2;