[Home] [Help]
PACKAGE BODY: APPS.BEN_EPE_CACHE
Source
1 package body ben_epe_cache as
2 /* $Header: benepech.pkb 120.0 2005/05/28 08:59:01 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 07-Aug-00 mhoyes Created.
15 115.1 05-Jan-01 kmahendr Added parameter per_in_ler_id
16 115.2 31-Jan-01 mhoyes - Added new columns to cache for use with EFC
17 - Removed STRTD life event restriction.
18 115.3 03-Jul-01 tmathers 9i compliance fixes.
19 115.4 09-Jul-01 tmathers 9i compliance fixes after test db came back.
20 115.5 01-Aug-01 ikasire added in_pndg_wkflow_flag to epe
21 115.6 13-Aug-01 mhoyes - Added bnft_prvdr_pool_id to type.
22 - Added EPE_GetEPEDets.
23 115.7 13-Sep-01 mhoyes - EFC tuning.
24 115.8 11-Dec-01 mhoyes - Added get_pilcobjepe_dets.
25 115.9 12 Jun 02 mhoyes - Performance tuning. Split cursors in
26 get_pilcobjepe_dets.
27 115.10 12 Aug 03 pbodla - Bug 1240957 : Added "order by PTIP_ORDR_NUM,
28 PLIP_ORDR_NUM, OIPL_ORDR_NUM, PL_ORDR_NUM"
29 to electable choice selection cursors
30 (c_instance) - to enable rates with
31 post enrollment rule calculated properly
32 115.11 12-Apr-04 kmahendr - Added three columns to cache.
33 -----------------------------------------------------------------------------
34 */
35 --
36 -- Globals.
37 --
38 g_package varchar2(50) := 'ben_epe_cache.';
39 g_hash_key number := ben_hash_utility.get_hash_key;
40 g_hash_jump number := ben_hash_utility.get_hash_jump;
41 --
42 -- 0 - Always refresh
43 -- 1 - Initialise cache
44 -- 2 - Cache hit
45 --
46 g_pilepe_instance g_pilepe_inst_tbl;
47 g_pilepe_cached pls_integer := 0;
48 g_perepe_cached pls_integer := 0;
49 g_enbepe_cached pls_integer := 0;
50 g_epe_cached pls_integer := 0;
51 --
52 type g_current_row is record
53 (per_in_ler_id number
54 );
55 --
56 g_enbepe_current g_current_row;
57 g_epe_current g_current_row;
58 --
59 procedure write_pilepe_cache
60 (p_person_id in number
61 ,p_per_in_ler_id in number
62 )
63 is
64 --
65 l_elig_per_elctbl_chc_id_va benutils.g_number_table := benutils.g_number_table();
66 l_business_group_id_va benutils.g_number_table := benutils.g_number_table();
67 l_person_id_va benutils.g_number_table := benutils.g_number_table();
68 l_ler_id_va benutils.g_number_table := benutils.g_number_table();
69 l_LF_EVT_OCRD_DT_va benutils.g_date_table := benutils.g_date_table();
70 l_per_in_ler_stat_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
71 l_per_in_ler_id_va benutils.g_number_table := benutils.g_number_table();
72 l_pgm_id_va benutils.g_number_table := benutils.g_number_table();
73 l_pl_typ_id_va benutils.g_number_table := benutils.g_number_table();
74 l_ptip_id_va benutils.g_number_table := benutils.g_number_table();
75 l_plip_id_va benutils.g_number_table := benutils.g_number_table();
76 l_pl_id_va benutils.g_number_table := benutils.g_number_table();
77 l_oipl_id_va benutils.g_number_table := benutils.g_number_table();
78 l_oiplip_id_va benutils.g_number_table := benutils.g_number_table();
79 l_opt_id_va benutils.g_number_table := benutils.g_number_table();
80 l_enrt_perd_id_va benutils.g_number_table := benutils.g_number_table();
81 l_lee_rsn_id_va benutils.g_number_table := benutils.g_number_table();
82 l_enrt_perd_strt_dt_va benutils.g_date_table := benutils.g_date_table();
83 l_prtt_enrt_rslt_id_va benutils.g_number_table := benutils.g_number_table();
84 l_enrt_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
85 l_enrt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
86 l_enrt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
87 l_yr_perd_id_va benutils.g_number_table := benutils.g_number_table();
88 l_comp_lvl_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
89 l_cmbn_plip_id_va benutils.g_number_table := benutils.g_number_table();
90 l_cmbn_ptip_id_va benutils.g_number_table := benutils.g_number_table();
91 l_cmbn_ptip_opt_id_va benutils.g_number_table := benutils.g_number_table();
92 l_dflt_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
93 l_ctfn_rqd_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
94 l_enrt_bnft_id_va benutils.g_number_table := benutils.g_number_table();
95 l_val_va benutils.g_number_table := benutils.g_number_table();
96 l_acty_ref_perd_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
97 l_elctbl_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
98 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
99 l_alws_dpnt_dsgn_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
100 l_dpnt_dsgn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
101 l_ler_chg_dpnt_cvg_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
102 l_dpnt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
103 l_dpnt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
104 l_in_pndg_wkflow_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
105 l_bnft_prvdr_pool_id_va benutils.g_number_table := benutils.g_number_table();
106 l_elig_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
107 l_inelig_rsn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
108 l_fonm_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
109 --
110 l_ele_num pls_integer;
111 --
112 CURSOR c_instance
113 (c_person_id in number
114 ,c_per_in_ler_id in number
115 )
116 IS
117 SELECT epe.elig_per_elctbl_chc_id,
118 epe.business_group_id,
119 pil.person_id,
120 pil.ler_id,
121 pil.LF_EVT_OCRD_DT,
122 pil.per_in_ler_stat_cd,
123 epe.per_in_ler_id,
124 epe.pgm_id,
125 epe.pl_typ_id,
126 epe.ptip_id,
127 epe.plip_id,
128 epe.pl_id,
129 epe.oipl_id,
130 epe.oiplip_id,
131 null opt_id,
132 pel.enrt_perd_id,
133 pel.lee_rsn_id,
134 pel.enrt_perd_strt_dt,
135 epe.prtt_enrt_rslt_id,
136 epe.enrt_cvg_strt_dt,
137 epe.enrt_cvg_strt_dt_cd,
138 epe.enrt_cvg_strt_dt_rl,
139 epe.yr_perd_id,
140 epe.comp_lvl_cd,
141 epe.cmbn_plip_id,
142 epe.cmbn_ptip_id,
143 epe.cmbn_ptip_opt_id,
144 epe.dflt_flag,
145 epe.ctfn_rqd_flag,
146 enb.enrt_bnft_id,
147 enb.val,
148 pel.acty_ref_perd_cd,
149 epe.elctbl_flag,
150 epe.object_version_number,
151 epe.alws_dpnt_dsgn_flag,
152 epe.dpnt_dsgn_cd,
153 epe.ler_chg_dpnt_cvg_cd,
154 epe.dpnt_cvg_strt_dt_cd,
155 epe.dpnt_cvg_strt_dt_rl,
156 epe.in_pndg_wkflow_flag,
157 epe.bnft_prvdr_pool_id,
158 epe.elig_flag,
159 epe.inelig_rsn_cd,
160 epe.fonm_cvg_strt_dt
161 /* removed the following as they are explicitly nulled later
162 9i compliance.,
163 null,
164 null,
165 null,
166 null,
167 null,
168 null,
169 null */
170 FROM ben_elig_per_elctbl_chc epe,
171 ben_pil_elctbl_chc_popl pel,
172 ben_enrt_bnft enb,
173 ben_per_in_ler pil
174 WHERE epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+)
175 AND epe.per_in_ler_id = pil.per_in_ler_id
176 AND epe.per_in_ler_id = pel.per_in_ler_id
177 AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
178 --
179 -- Removed for EFC. This cache is intended to support all electable
180 -- choice information for all types of life event. It should not be
181 -- restricted to started life event electable choices only
182 --
183 /*
184 AND pil.per_in_ler_stat_cd = 'STRTD'
185 */
186 -- added for unrestricted enhancement
187 and pil.per_in_ler_id = c_per_in_ler_id
188 AND pil.person_id = c_person_id
189 order by epe.PTIP_ORDR_NUM, PLIP_ORDR_NUM,
190 decode(PL_ORDR_NUM, null, OIPL_ORDR_NUM, PL_ORDR_NUM),
191 PL_ORDR_NUM,
192 decode(PL_ORDR_NUM, null, null, OIPL_ORDR_NUM);
193 --
194 begin
195 --
196 l_ele_num := 0;
197 --
198 open c_instance
199 (c_person_id => p_person_id
200 ,c_per_in_ler_id => p_per_in_ler_id
201 );
202 fetch c_instance BULK COLLECT INTO l_elig_per_elctbl_chc_id_va,
203 l_business_group_id_va,
204 l_person_id_va,
205 l_ler_id_va,
206 l_LF_EVT_OCRD_DT_va,
207 l_per_in_ler_stat_cd_va,
208 l_per_in_ler_id_va,
209 l_pgm_id_va,
210 l_pl_typ_id_va,
211 l_ptip_id_va,
212 l_plip_id_va,
213 l_pl_id_va,
214 l_oipl_id_va,
215 l_oiplip_id_va,
216 l_opt_id_va,
217 l_enrt_perd_id_va,
218 l_lee_rsn_id_va,
219 l_enrt_perd_strt_dt_va,
220 l_prtt_enrt_rslt_id_va,
221 l_enrt_cvg_strt_dt_va,
222 l_enrt_cvg_strt_dt_cd_va,
223 l_enrt_cvg_strt_dt_rl_va,
224 l_yr_perd_id_va,
225 l_comp_lvl_cd_va,
226 l_cmbn_plip_id_va,
227 l_cmbn_ptip_id_va,
228 l_cmbn_ptip_opt_id_va,
229 l_dflt_flag_va,
230 l_ctfn_rqd_flag_va,
231 l_enrt_bnft_id_va,
232 l_val_va,
233 l_acty_ref_perd_cd_va,
234 l_elctbl_flag_va,
235 l_object_version_number_va,
236 l_alws_dpnt_dsgn_flag_va,
237 l_dpnt_dsgn_cd_va,
238 l_ler_chg_dpnt_cvg_cd_va,
239 l_dpnt_cvg_strt_dt_cd_va,
240 l_dpnt_cvg_strt_dt_rl_va,
241 l_in_pndg_wkflow_flag_va,
242 l_bnft_prvdr_pool_id_va,
243 l_elig_flag_va,
244 l_inelig_rsn_cd_va,
245 l_fonm_cvg_strt_dt_va;
246 close c_instance;
247 --
248 if l_elig_per_elctbl_chc_id_va.count > 0 then
249 --
250 for i in l_elig_per_elctbl_chc_id_va.first..l_elig_per_elctbl_chc_id_va.last
251 loop
252 --
256 g_pilepe_instance(l_ele_num).ler_id := l_ler_id_va(i);
253 g_pilepe_instance(l_ele_num).elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id_va(i);
254 g_pilepe_instance(l_ele_num).business_group_id := l_business_group_id_va(i);
255 g_pilepe_instance(l_ele_num).person_id := l_person_id_va(i);
257 g_pilepe_instance(l_ele_num).LF_EVT_OCRD_DT := l_LF_EVT_OCRD_DT_va(i);
258 g_pilepe_instance(l_ele_num).per_in_ler_stat_cd := l_per_in_ler_stat_cd_va(i);
259 g_pilepe_instance(l_ele_num).per_in_ler_id := l_per_in_ler_id_va(i);
260 g_pilepe_instance(l_ele_num).pgm_id := l_pgm_id_va(i);
261 g_pilepe_instance(l_ele_num).pl_typ_id := l_pl_typ_id_va(i);
262 g_pilepe_instance(l_ele_num).ptip_id := l_ptip_id_va(i);
263 g_pilepe_instance(l_ele_num).plip_id := l_plip_id_va(i);
264 g_pilepe_instance(l_ele_num).pl_id := l_pl_id_va(i);
265 g_pilepe_instance(l_ele_num).oipl_id := l_oipl_id_va(i);
266 g_pilepe_instance(l_ele_num).oiplip_id := l_oiplip_id_va(i);
267 g_pilepe_instance(l_ele_num).opt_id := null;
268 g_pilepe_instance(l_ele_num).enrt_perd_id := l_enrt_perd_id_va(i);
269 g_pilepe_instance(l_ele_num).lee_rsn_id := l_lee_rsn_id_va(i);
270 g_pilepe_instance(l_ele_num).enrt_perd_strt_dt := l_enrt_perd_strt_dt_va(i);
271 g_pilepe_instance(l_ele_num).prtt_enrt_rslt_id := l_prtt_enrt_rslt_id_va(i);
272 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt := l_enrt_cvg_strt_dt_va(i);
273 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt_cd := l_enrt_cvg_strt_dt_cd_va(i);
274 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt_rl := l_enrt_cvg_strt_dt_rl_va(i);
275 g_pilepe_instance(l_ele_num).yr_perd_id := l_yr_perd_id_va(i);
276 g_pilepe_instance(l_ele_num).comp_lvl_cd := l_comp_lvl_cd_va(i);
277 g_pilepe_instance(l_ele_num).cmbn_plip_id := l_cmbn_plip_id_va(i);
278 g_pilepe_instance(l_ele_num).cmbn_ptip_id := l_cmbn_ptip_id_va(i);
279 g_pilepe_instance(l_ele_num).cmbn_ptip_opt_id := l_cmbn_ptip_opt_id_va(i);
280 g_pilepe_instance(l_ele_num).dflt_flag := l_dflt_flag_va(i);
281 g_pilepe_instance(l_ele_num).ctfn_rqd_flag := l_ctfn_rqd_flag_va(i);
282 g_pilepe_instance(l_ele_num).enrt_bnft_id := l_enrt_bnft_id_va(i);
283 g_pilepe_instance(l_ele_num).val := l_val_va(i);
284 g_pilepe_instance(l_ele_num).acty_ref_perd_cd := l_acty_ref_perd_cd_va(i);
285 g_pilepe_instance(l_ele_num).elctbl_flag := l_elctbl_flag_va(i);
286 g_pilepe_instance(l_ele_num).object_version_number := l_object_version_number_va(i);
287 g_pilepe_instance(l_ele_num).alws_dpnt_dsgn_flag := l_alws_dpnt_dsgn_flag_va(i);
288 g_pilepe_instance(l_ele_num).dpnt_dsgn_cd := l_dpnt_dsgn_cd_va(i);
289 g_pilepe_instance(l_ele_num).ler_chg_dpnt_cvg_cd := l_ler_chg_dpnt_cvg_cd_va(i);
290 g_pilepe_instance(l_ele_num).dpnt_cvg_strt_dt_cd := l_dpnt_cvg_strt_dt_cd_va(i);
291 g_pilepe_instance(l_ele_num).dpnt_cvg_strt_dt_rl := l_dpnt_cvg_strt_dt_rl_va(i);
292 g_pilepe_instance(l_ele_num).in_pndg_wkflow_flag := l_in_pndg_wkflow_flag_va(i);
293 g_pilepe_instance(l_ele_num).bnft_prvdr_pool_id := l_bnft_prvdr_pool_id_va(i);
294 --
295 g_pilepe_instance(l_ele_num).elig_flag := l_elig_flag_va(i);
296 g_pilepe_instance(l_ele_num).inelig_rsn_cd := l_inelig_rsn_cd_va(i);
297 g_pilepe_instance(l_ele_num).fonm_cvg_strt_dt := l_fonm_cvg_strt_dt_va(i);
298 g_pilepe_instance(l_ele_num).prtn_strt_dt := null;
299 g_pilepe_instance(l_ele_num).prtn_ovridn_flag := null;
300 g_pilepe_instance(l_ele_num).prtn_ovridn_thru_dt := null;
301 g_pilepe_instance(l_ele_num).rt_age_val := null;
305 l_ele_num := l_ele_num+1;
302 g_pilepe_instance(l_ele_num).rt_los_val := null;
303 g_pilepe_instance(l_ele_num).rt_hrs_wkd_val := null;
304 g_pilepe_instance(l_ele_num).rt_cmbn_age_n_los_val := null;
306 --
307 end loop;
308 --
309 end if;
310 --
311 /*
312 for row in c_instance
313 (c_person_id => p_person_id
314 ,c_per_in_ler_id => p_per_in_ler_id
315 )
316 loop
317 --
318 g_pilepe_instance(l_ele_num).elig_per_elctbl_chc_id := row.elig_per_elctbl_chc_id;
319 g_pilepe_instance(l_ele_num).business_group_id := row.business_group_id;
320 g_pilepe_instance(l_ele_num).person_id := row.person_id;
321 g_pilepe_instance(l_ele_num).ler_id := row.ler_id;
322 g_pilepe_instance(l_ele_num).LF_EVT_OCRD_DT := row.LF_EVT_OCRD_DT;
323 g_pilepe_instance(l_ele_num).per_in_ler_stat_cd := row.per_in_ler_stat_cd;
324 g_pilepe_instance(l_ele_num).per_in_ler_id := row.per_in_ler_id;
325 g_pilepe_instance(l_ele_num).pgm_id := row.pgm_id;
326 g_pilepe_instance(l_ele_num).pl_typ_id := row.pl_typ_id;
327 g_pilepe_instance(l_ele_num).ptip_id := row.ptip_id;
328 g_pilepe_instance(l_ele_num).plip_id := row.plip_id;
329 g_pilepe_instance(l_ele_num).pl_id := row.pl_id;
330 g_pilepe_instance(l_ele_num).oipl_id := row.oipl_id;
331 g_pilepe_instance(l_ele_num).oiplip_id := row.oiplip_id;
332 g_pilepe_instance(l_ele_num).comp_lvl_cd := row.comp_lvl_cd;
333 g_pilepe_instance(l_ele_num).cmbn_plip_id := row.cmbn_plip_id;
334 g_pilepe_instance(l_ele_num).cmbn_ptip_id := row.cmbn_ptip_id;
335 g_pilepe_instance(l_ele_num).cmbn_ptip_opt_id := row.cmbn_ptip_opt_id;
336 g_pilepe_instance(l_ele_num).dflt_flag := row.dflt_flag;
337 g_pilepe_instance(l_ele_num).ctfn_rqd_flag := row.ctfn_rqd_flag;
338 g_pilepe_instance(l_ele_num).prtt_enrt_rslt_id := row.prtt_enrt_rslt_id;
339 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt := row.enrt_cvg_strt_dt;
340 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt_cd := row.enrt_cvg_strt_dt_cd;
341 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt_rl := row.enrt_cvg_strt_dt_rl;
342 g_pilepe_instance(l_ele_num).yr_perd_id := row.yr_perd_id;
343 g_pilepe_instance(l_ele_num).enrt_bnft_id := row.enrt_bnft_id;
344 g_pilepe_instance(l_ele_num).val := row.val;
345 g_pilepe_instance(l_ele_num).enrt_perd_strt_dt := row.enrt_perd_strt_dt;
346 g_pilepe_instance(l_ele_num).enrt_perd_id := row.enrt_perd_id;
347 g_pilepe_instance(l_ele_num).lee_rsn_id := row.lee_rsn_id;
348 g_pilepe_instance(l_ele_num).acty_ref_perd_cd := row.acty_ref_perd_cd;
349 g_pilepe_instance(l_ele_num).elctbl_flag := row.elctbl_flag;
350 g_pilepe_instance(l_ele_num).object_version_number := row.object_version_number;
351 g_pilepe_instance(l_ele_num).alws_dpnt_dsgn_flag := row.alws_dpnt_dsgn_flag;
352 g_pilepe_instance(l_ele_num).dpnt_dsgn_cd := row.dpnt_dsgn_cd;
353 g_pilepe_instance(l_ele_num).ler_chg_dpnt_cvg_cd := row.ler_chg_dpnt_cvg_cd;
354 g_pilepe_instance(l_ele_num).dpnt_cvg_strt_dt_cd := row.dpnt_cvg_strt_dt_cd;
355 g_pilepe_instance(l_ele_num).dpnt_cvg_strt_dt_rl := row.dpnt_cvg_strt_dt_rl;
356 g_pilepe_instance(l_ele_num).in_pndg_wkflow_flag := row.in_pndg_wkflow_flag;
357 g_pilepe_instance(l_ele_num).bnft_prvdr_pool_id := row.bnft_prvdr_pool_id;
358 --
359 g_pilepe_instance(l_ele_num).opt_id := null;
360 g_pilepe_instance(l_ele_num).prtn_strt_dt := null;
361 g_pilepe_instance(l_ele_num).prtn_ovridn_flag := null;
362 g_pilepe_instance(l_ele_num).prtn_ovridn_thru_dt := null;
363 g_pilepe_instance(l_ele_num).rt_age_val := null;
364 g_pilepe_instance(l_ele_num).rt_los_val := null;
365 g_pilepe_instance(l_ele_num).rt_hrs_wkd_val := null;
366 g_pilepe_instance(l_ele_num).rt_cmbn_age_n_los_val := null;
367 l_ele_num := l_ele_num+1;
368 --
369 end loop;
370 */
371 --
372 -- Check for no rows found
373 --
374 if l_ele_num = 0 then
375 --
376 g_pilepe_instance.delete;
377 --
378 end if;
379 --
380 end write_pilepe_cache;
381 --
382 procedure get_perpilepe_list
383 (p_person_id in number
384 ,p_per_in_ler_id in number
385 ,p_inst_set in out NOCOPY g_pilepe_inst_tbl
386 )
387 is
388 --
389 l_proc varchar2(72) := 'get_perpilepe_list';
390 --
391 begin
392 --
393 -- check comp object type
394 --
395 if g_pilepe_cached < 2
396 then
397 --
398 -- Write the cache
399 --
400 write_pilepe_cache
401 (p_person_id => p_person_id
402 ,p_per_in_ler_id =>p_per_in_ler_id
403 );
404 --
405 if g_pilepe_cached = 1
406 then
407 --
408 g_pilepe_cached := 2;
409 --
410 end if;
411 --
412 end if;
413 --
414 p_inst_set := g_pilepe_instance;
415 --
416 end get_perpilepe_list;
417 --
418 procedure write_ENBEPE_cache
419 (p_per_in_ler_id in number
420 )
421 is
422 --
423 l_proc varchar2(72) := 'write_ENBEPE_cache';
424 --
425 l_elig_per_elctbl_chc_id_va benutils.g_number_table := benutils.g_number_table();
426 l_business_group_id_va benutils.g_number_table := benutils.g_number_table();
427 l_person_id_va benutils.g_number_table := benutils.g_number_table();
428 l_ler_id_va benutils.g_number_table := benutils.g_number_table();
429 l_LF_EVT_OCRD_DT_va benutils.g_date_table := benutils.g_date_table();
430 l_per_in_ler_stat_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
431 l_per_in_ler_id_va benutils.g_number_table := benutils.g_number_table();
432 l_pgm_id_va benutils.g_number_table := benutils.g_number_table();
433 l_pl_typ_id_va benutils.g_number_table := benutils.g_number_table();
434 l_ptip_id_va benutils.g_number_table := benutils.g_number_table();
435 l_plip_id_va benutils.g_number_table := benutils.g_number_table();
436 l_pl_id_va benutils.g_number_table := benutils.g_number_table();
437 l_oipl_id_va benutils.g_number_table := benutils.g_number_table();
438 l_oiplip_id_va benutils.g_number_table := benutils.g_number_table();
439 l_opt_id_va benutils.g_number_table := benutils.g_number_table();
440 l_enrt_perd_id_va benutils.g_number_table := benutils.g_number_table();
441 l_lee_rsn_id_va benutils.g_number_table := benutils.g_number_table();
442 l_enrt_perd_strt_dt_va benutils.g_date_table := benutils.g_date_table();
443 l_prtt_enrt_rslt_id_va benutils.g_number_table := benutils.g_number_table();
444 l_enrt_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
445 l_enrt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
446 l_enrt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
447 l_yr_perd_id_va benutils.g_number_table := benutils.g_number_table();
448 l_comp_lvl_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
449 l_cmbn_plip_id_va benutils.g_number_table := benutils.g_number_table();
450 l_cmbn_ptip_id_va benutils.g_number_table := benutils.g_number_table();
451 l_cmbn_ptip_opt_id_va benutils.g_number_table := benutils.g_number_table();
452 l_dflt_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
453 l_ctfn_rqd_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
454 l_enrt_bnft_id_va benutils.g_number_table := benutils.g_number_table();
455 l_val_va benutils.g_number_table := benutils.g_number_table();
456 l_acty_ref_perd_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
457 l_elctbl_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
458 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
459 l_alws_dpnt_dsgn_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
460 l_dpnt_dsgn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
461 l_ler_chg_dpnt_cvg_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
462 l_dpnt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
463 l_dpnt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
464 l_in_pndg_wkflow_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
465 l_bnft_prvdr_pool_id_va benutils.g_number_table := benutils.g_number_table();
466 l_elig_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
467 l_inelig_rsn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
468 l_fonm_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
469
470 --
471 l_hv pls_integer;
472 --
473 CURSOR c_instance
474 (c_per_in_ler_id in number
478 epe.business_group_id,
475 )
476 IS
477 SELECT epe.elig_per_elctbl_chc_id,
479 pil.person_id,
480 pil.ler_id,
481 pil.LF_EVT_OCRD_DT,
482 pil.per_in_ler_stat_cd,
483 epe.per_in_ler_id,
484 epe.pgm_id,
485 epe.pl_typ_id,
486 epe.ptip_id,
487 epe.plip_id,
488 epe.pl_id,
489 epe.oipl_id,
490 epe.oiplip_id,
491 null opt_id,
492 pel.enrt_perd_id,
493 pel.lee_rsn_id,
494 pel.enrt_perd_strt_dt,
495 epe.prtt_enrt_rslt_id,
496 epe.enrt_cvg_strt_dt,
497 epe.enrt_cvg_strt_dt_cd,
498 epe.enrt_cvg_strt_dt_rl,
499 epe.yr_perd_id,
500 epe.comp_lvl_cd,
501 epe.cmbn_plip_id,
502 epe.cmbn_ptip_id,
503 epe.cmbn_ptip_opt_id,
504 epe.dflt_flag,
505 epe.ctfn_rqd_flag,
506 enb.enrt_bnft_id,
507 enb.val,
508 pel.acty_ref_perd_cd,
509 /* removed the following as they are explicitly nulled later
510 9i compliance.,
511 null,
512 null,
513 null,
514 null,
515 null,
516 null,
517 null,
518 */
519 epe.elctbl_flag,
520 epe.object_version_number,
521 epe.alws_dpnt_dsgn_flag,
522 epe.dpnt_dsgn_cd,
523 epe.ler_chg_dpnt_cvg_cd,
524 epe.dpnt_cvg_strt_dt_cd,
525 epe.dpnt_cvg_strt_dt_rl,
526 epe.in_pndg_wkflow_flag,
527 epe.bnft_prvdr_pool_id,
528 epe.elig_flag,
529 epe.inelig_rsn_cd,
530 epe.fonm_cvg_strt_dt
531 FROM ben_enrt_bnft enb,
532 ben_elig_per_elctbl_chc epe,
533 ben_pil_elctbl_chc_popl pel,
534 ben_per_in_ler pil
535 WHERE enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
536 AND epe.per_in_ler_id = pil.per_in_ler_id
537 AND epe.per_in_ler_id = pel.per_in_ler_id
538 AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
539 and pil.per_in_ler_id = c_per_in_ler_id
540 order by epe.PTIP_ORDR_NUM, PLIP_ORDR_NUM,
541 decode(PL_ORDR_NUM, null, OIPL_ORDR_NUM, PL_ORDR_NUM),
542 PL_ORDR_NUM,
543 decode(PL_ORDR_NUM, null, null, OIPL_ORDR_NUM);
544 --
545 begin
546 --
547 open c_instance
548 (c_per_in_ler_id => p_per_in_ler_id
549 );
550 fetch c_instance BULK COLLECT INTO l_elig_per_elctbl_chc_id_va,
551 l_business_group_id_va,
552 l_person_id_va,
553 l_ler_id_va,
554 l_LF_EVT_OCRD_DT_va,
555 l_per_in_ler_stat_cd_va,
556 l_per_in_ler_id_va,
557 l_pgm_id_va,
558 l_pl_typ_id_va,
559 l_ptip_id_va,
560 l_plip_id_va,
561 l_pl_id_va,
562 l_oipl_id_va,
563 l_oiplip_id_va,
564 l_opt_id_va,
565 l_enrt_perd_id_va,
566 l_lee_rsn_id_va,
570 l_enrt_cvg_strt_dt_cd_va,
567 l_enrt_perd_strt_dt_va,
568 l_prtt_enrt_rslt_id_va,
569 l_enrt_cvg_strt_dt_va,
571 l_enrt_cvg_strt_dt_rl_va,
572 l_yr_perd_id_va,
573 l_comp_lvl_cd_va,
574 l_cmbn_plip_id_va,
575 l_cmbn_ptip_id_va,
576 l_cmbn_ptip_opt_id_va,
577 l_dflt_flag_va,
578 l_ctfn_rqd_flag_va,
579 l_enrt_bnft_id_va,
580 l_val_va,
581 l_acty_ref_perd_cd_va,
582 l_elctbl_flag_va,
583 l_object_version_number_va,
584 l_alws_dpnt_dsgn_flag_va,
585 l_dpnt_dsgn_cd_va,
586 l_ler_chg_dpnt_cvg_cd_va,
587 l_dpnt_cvg_strt_dt_cd_va,
588 l_dpnt_cvg_strt_dt_rl_va,
589 l_in_pndg_wkflow_flag_va,
590 l_bnft_prvdr_pool_id_va,
591 l_elig_flag_va,
592 l_inelig_rsn_cd_va,
593 l_fonm_cvg_strt_dt_va;
594 close c_instance;
595 --
596 if l_enrt_bnft_id_va.count > 0 then
597 --
598 for i in l_enrt_bnft_id_va.first..l_enrt_bnft_id_va.last
599 loop
600 --
601 l_hv := mod(l_enrt_bnft_id_va(i),ben_hash_utility.get_hash_key);
602 --
603 while g_enbepe_instance.exists(l_hv)
604 loop
605 --
606 l_hv := l_hv+g_hash_jump;
607 --
608 end loop;
609 --
610 g_enbepe_instance(l_hv).elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id_va(i);
611 g_enbepe_instance(l_hv).business_group_id := l_business_group_id_va(i);
612 g_enbepe_instance(l_hv).person_id := l_person_id_va(i);
613 g_enbepe_instance(l_hv).ler_id := l_ler_id_va(i);
614 g_enbepe_instance(l_hv).LF_EVT_OCRD_DT := l_LF_EVT_OCRD_DT_va(i);
615 g_enbepe_instance(l_hv).per_in_ler_stat_cd := l_per_in_ler_stat_cd_va(i);
616 g_enbepe_instance(l_hv).per_in_ler_id := l_per_in_ler_id_va(i);
617 g_enbepe_instance(l_hv).pgm_id := l_pgm_id_va(i);
618 g_enbepe_instance(l_hv).pl_typ_id := l_pl_typ_id_va(i);
619 g_enbepe_instance(l_hv).ptip_id := l_ptip_id_va(i);
620 g_enbepe_instance(l_hv).plip_id := l_plip_id_va(i);
621 g_enbepe_instance(l_hv).pl_id := l_pl_id_va(i);
622 g_enbepe_instance(l_hv).oipl_id := l_oipl_id_va(i);
623 g_enbepe_instance(l_hv).oiplip_id := l_oiplip_id_va(i);
624 g_enbepe_instance(l_hv).opt_id := null;
625 g_enbepe_instance(l_hv).enrt_perd_id := l_enrt_perd_id_va(i);
626 g_enbepe_instance(l_hv).lee_rsn_id := l_lee_rsn_id_va(i);
627 g_enbepe_instance(l_hv).enrt_perd_strt_dt := l_enrt_perd_strt_dt_va(i);
628 g_enbepe_instance(l_hv).prtt_enrt_rslt_id := l_prtt_enrt_rslt_id_va(i);
629 g_enbepe_instance(l_hv).enrt_cvg_strt_dt := l_enrt_cvg_strt_dt_va(i);
630 g_enbepe_instance(l_hv).enrt_cvg_strt_dt_cd := l_enrt_cvg_strt_dt_cd_va(i);
631 g_enbepe_instance(l_hv).enrt_cvg_strt_dt_rl := l_enrt_cvg_strt_dt_rl_va(i);
632 g_enbepe_instance(l_hv).yr_perd_id := l_yr_perd_id_va(i);
633 g_enbepe_instance(l_hv).comp_lvl_cd := l_comp_lvl_cd_va(i);
634 g_enbepe_instance(l_hv).cmbn_plip_id := l_cmbn_plip_id_va(i);
635 g_enbepe_instance(l_hv).cmbn_ptip_id := l_cmbn_ptip_id_va(i);
636 g_enbepe_instance(l_hv).cmbn_ptip_opt_id := l_cmbn_ptip_opt_id_va(i);
637 g_enbepe_instance(l_hv).dflt_flag := l_dflt_flag_va(i);
638 g_enbepe_instance(l_hv).ctfn_rqd_flag := l_ctfn_rqd_flag_va(i);
639 g_enbepe_instance(l_hv).enrt_bnft_id := l_enrt_bnft_id_va(i);
640 g_enbepe_instance(l_hv).val := l_val_va(i);
641 g_enbepe_instance(l_hv).acty_ref_perd_cd := l_acty_ref_perd_cd_va(i);
642 g_enbepe_instance(l_hv).elctbl_flag := l_elctbl_flag_va(i);
643 g_enbepe_instance(l_hv).object_version_number := l_object_version_number_va(i);
644 g_enbepe_instance(l_hv).alws_dpnt_dsgn_flag := l_alws_dpnt_dsgn_flag_va(i);
645 g_enbepe_instance(l_hv).dpnt_dsgn_cd := l_dpnt_dsgn_cd_va(i);
646 g_enbepe_instance(l_hv).ler_chg_dpnt_cvg_cd := l_ler_chg_dpnt_cvg_cd_va(i);
647 g_enbepe_instance(l_hv).dpnt_cvg_strt_dt_cd := l_dpnt_cvg_strt_dt_cd_va(i);
648 g_enbepe_instance(l_hv).dpnt_cvg_strt_dt_rl := l_dpnt_cvg_strt_dt_rl_va(i);
649 g_enbepe_instance(l_hv).in_pndg_wkflow_flag := l_in_pndg_wkflow_flag_va(i);
650 g_enbepe_instance(l_hv).bnft_prvdr_pool_id := l_bnft_prvdr_pool_id_va(i);
651 g_enbepe_instance(l_hv).elig_flag := l_elig_flag_va(i);
652 g_enbepe_instance(l_hv).inelig_rsn_cd := l_inelig_rsn_cd_va(i);
653 g_enbepe_instance(l_hv).fonm_cvg_strt_dt := l_fonm_cvg_strt_dt_va(i);
654
655 --
656 g_enbepe_instance(l_hv).prtn_strt_dt := null;
660 g_enbepe_instance(l_hv).rt_los_val := null;
657 g_enbepe_instance(l_hv).prtn_ovridn_flag := null;
658 g_enbepe_instance(l_hv).prtn_ovridn_thru_dt := null;
659 g_enbepe_instance(l_hv).rt_age_val := null;
661 g_enbepe_instance(l_hv).rt_hrs_wkd_val := null;
662 g_enbepe_instance(l_hv).rt_cmbn_age_n_los_val := null;
663 --
664 end loop;
665 --
666 end if;
667 --
668 /*
669 for objinst in c_instance
670 (c_per_in_ler_id => p_per_in_ler_id
671 )
672 loop
673 --
674 l_hv := mod(objinst.enrt_bnft_id,ben_hash_utility.get_hash_key);
675 --
676 while g_enbepe_instance.exists(l_hv)
677 loop
678 --
679 l_hv := l_hv+g_hash_jump;
680 --
681 end loop;
682 --
683 g_enbepe_instance(l_hv).elig_per_elctbl_chc_id := objinst.elig_per_elctbl_chc_id;
684 g_enbepe_instance(l_hv).business_group_id := objinst.business_group_id;
685 g_enbepe_instance(l_hv).person_id := objinst.person_id;
686 g_enbepe_instance(l_hv).ler_id := objinst.ler_id;
687 g_enbepe_instance(l_hv).per_in_ler_id := objinst.per_in_ler_id;
688 g_enbepe_instance(l_hv).LF_EVT_OCRD_DT := objinst.LF_EVT_OCRD_DT;
689 g_enbepe_instance(l_hv).per_in_ler_stat_cd := objinst.per_in_ler_stat_cd;
690 g_enbepe_instance(l_hv).pgm_id := objinst.pgm_id;
691 g_enbepe_instance(l_hv).pl_typ_id := objinst.pl_typ_id;
692 g_enbepe_instance(l_hv).ptip_id := objinst.ptip_id;
693 g_enbepe_instance(l_hv).plip_id := objinst.plip_id;
694 g_enbepe_instance(l_hv).pl_id := objinst.pl_id;
695 g_enbepe_instance(l_hv).oipl_id := objinst.oipl_id;
696 g_enbepe_instance(l_hv).oiplip_id := objinst.oiplip_id;
697 g_enbepe_instance(l_hv).comp_lvl_cd := objinst.comp_lvl_cd;
698 g_enbepe_instance(l_hv).cmbn_plip_id := objinst.cmbn_plip_id;
699 g_enbepe_instance(l_hv).cmbn_ptip_id := objinst.cmbn_ptip_id;
700 g_enbepe_instance(l_hv).cmbn_ptip_opt_id := objinst.cmbn_ptip_opt_id;
701 g_enbepe_instance(l_hv).dflt_flag := objinst.dflt_flag;
702 g_enbepe_instance(l_hv).ctfn_rqd_flag := objinst.ctfn_rqd_flag;
703 g_enbepe_instance(l_hv).prtt_enrt_rslt_id := objinst.prtt_enrt_rslt_id;
704 g_enbepe_instance(l_hv).enrt_cvg_strt_dt := objinst.enrt_cvg_strt_dt;
705 g_enbepe_instance(l_hv).enrt_cvg_strt_dt_cd := objinst.enrt_cvg_strt_dt_cd;
706 g_enbepe_instance(l_hv).enrt_cvg_strt_dt_rl := objinst.enrt_cvg_strt_dt_rl;
707 g_enbepe_instance(l_hv).yr_perd_id := objinst.yr_perd_id;
708 g_enbepe_instance(l_hv).enrt_bnft_id := objinst.enrt_bnft_id;
709 g_enbepe_instance(l_hv).val := objinst.val;
710 g_enbepe_instance(l_hv).enrt_perd_strt_dt := objinst.enrt_perd_strt_dt;
711 g_enbepe_instance(l_hv).enrt_perd_id := objinst.enrt_perd_id;
712 g_enbepe_instance(l_hv).lee_rsn_id := objinst.lee_rsn_id;
713 g_enbepe_instance(l_hv).acty_ref_perd_cd := objinst.acty_ref_perd_cd;
714 g_enbepe_instance(l_hv).elctbl_flag := objinst.elctbl_flag;
715 g_enbepe_instance(l_hv).object_version_number := objinst.object_version_number;
716 g_enbepe_instance(l_hv).alws_dpnt_dsgn_flag := objinst.alws_dpnt_dsgn_flag;
717 g_enbepe_instance(l_hv).dpnt_dsgn_cd := objinst.dpnt_dsgn_cd;
718 g_enbepe_instance(l_hv).ler_chg_dpnt_cvg_cd := objinst.ler_chg_dpnt_cvg_cd;
719 g_enbepe_instance(l_hv).dpnt_cvg_strt_dt_cd := objinst.dpnt_cvg_strt_dt_cd;
720 g_enbepe_instance(l_hv).dpnt_cvg_strt_dt_rl := objinst.dpnt_cvg_strt_dt_rl;
721 g_enbepe_instance(l_hv).in_pndg_wkflow_flag := objinst.in_pndg_wkflow_flag;
722 --
723 g_enbepe_instance(l_hv).opt_id := null;
724 g_enbepe_instance(l_hv).prtn_strt_dt := null;
725 g_enbepe_instance(l_hv).prtn_ovridn_flag := null;
726 g_enbepe_instance(l_hv).prtn_ovridn_thru_dt := null;
727 g_enbepe_instance(l_hv).rt_age_val := null;
728 g_enbepe_instance(l_hv).rt_los_val := null;
729 g_enbepe_instance(l_hv).rt_hrs_wkd_val := null;
730 g_enbepe_instance(l_hv).rt_cmbn_age_n_los_val := null;
731 --
732 end loop;
733 */
734 --
735 -- Check for no rows found
736 --
737 if l_hv is null then
738 --
739 g_enbepe_instance.delete;
740 g_enbepe_current.per_in_ler_id := null;
741 --
742 else
743 --
744 g_enbepe_current.per_in_ler_id := p_per_in_ler_id;
745 --
746 end if;
747 --
748 end write_ENBEPE_cache;
749 --
750 procedure ENBEPE_GetEPEDets
751 (p_enrt_bnft_id in number
752 ,p_per_in_ler_id in number
753 ,p_inst_row in out NOCOPY g_pilepe_inst_row
754 )
755 is
756 --
757 l_proc varchar2(72) := 'ENBEPE_GetEPEDets';
758 --
759 l_hv pls_integer;
760 l_reset g_pilepe_inst_row;
761 --
762 begin
763 --
764 -- Check for already cached or a change in current PIL ID
765 --
766 if nvl(g_enbepe_current.per_in_ler_id,-9999) <> p_per_in_ler_id
767 or g_pilepe_cached < 2
768 then
769 --
770 -- Write the cache
771 --
772 write_ENBEPE_cache
773 (p_per_in_ler_id => p_per_in_ler_id
774 );
775 --
776 if g_pilepe_cached = 1
777 then
778 --
779 g_pilepe_cached := 2;
780 --
781 end if;
782 --
783 end if;
784 --
785 -- Get the instance details
786 --
787 l_hv := mod(p_enrt_bnft_id,ben_hash_utility.get_hash_key);
788 --
789 if g_enbepe_instance(l_hv).enrt_bnft_id = p_enrt_bnft_id
790 then
791 -- Matched row
792 null;
793 else
794 --
795 -- Loop through the hash using the jump routine to check further
796 -- indexes if none exists at current index the NO_DATA_FOUND expection
797 -- will fire
798 --
799 l_hv := l_hv+g_hash_jump;
800 while g_enbepe_instance(l_hv).enrt_bnft_id <> p_enrt_bnft_id loop
801 --
802 l_hv := l_hv+g_hash_jump;
803 --
804 end loop;
805 --
806 end if;
807 --
808 p_inst_row := g_enbepe_instance(l_hv);
809 --
810 exception
811 --
812 when no_data_found then
813 --
814 p_inst_row := l_reset;
815 --
816 end ENBEPE_GetEPEDets;
817 --
818 procedure write_EPE_cache
819 (p_per_in_ler_id in number
820 )
821 is
822 --
823 l_proc varchar2(72) := 'write_EPE_cache';
824 --
825 l_elig_per_elctbl_chc_id_va benutils.g_number_table := benutils.g_number_table();
826 l_business_group_id_va benutils.g_number_table := benutils.g_number_table();
827 l_person_id_va benutils.g_number_table := benutils.g_number_table();
828 l_ler_id_va benutils.g_number_table := benutils.g_number_table();
829 l_LF_EVT_OCRD_DT_va benutils.g_date_table := benutils.g_date_table();
830 l_per_in_ler_stat_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
831 l_per_in_ler_id_va benutils.g_number_table := benutils.g_number_table();
832 l_pgm_id_va benutils.g_number_table := benutils.g_number_table();
833 l_pl_typ_id_va benutils.g_number_table := benutils.g_number_table();
834 l_ptip_id_va benutils.g_number_table := benutils.g_number_table();
835 l_plip_id_va benutils.g_number_table := benutils.g_number_table();
836 l_pl_id_va benutils.g_number_table := benutils.g_number_table();
837 l_oipl_id_va benutils.g_number_table := benutils.g_number_table();
838 l_oiplip_id_va benutils.g_number_table := benutils.g_number_table();
839 l_opt_id_va benutils.g_number_table := benutils.g_number_table();
840 l_enrt_perd_id_va benutils.g_number_table := benutils.g_number_table();
841 l_lee_rsn_id_va benutils.g_number_table := benutils.g_number_table();
842 l_enrt_perd_strt_dt_va benutils.g_date_table := benutils.g_date_table();
843 l_prtt_enrt_rslt_id_va benutils.g_number_table := benutils.g_number_table();
844 l_enrt_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
845 l_enrt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
846 l_enrt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
847 l_yr_perd_id_va benutils.g_number_table := benutils.g_number_table();
848 l_comp_lvl_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
849 l_cmbn_plip_id_va benutils.g_number_table := benutils.g_number_table();
850 l_cmbn_ptip_id_va benutils.g_number_table := benutils.g_number_table();
851 l_cmbn_ptip_opt_id_va benutils.g_number_table := benutils.g_number_table();
852 l_dflt_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
853 l_ctfn_rqd_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
854 l_enrt_bnft_id_va benutils.g_number_table := benutils.g_number_table();
855 l_val_va benutils.g_number_table := benutils.g_number_table();
856 l_acty_ref_perd_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
857 l_elctbl_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
858 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
859 l_alws_dpnt_dsgn_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
860 l_dpnt_dsgn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
861 l_ler_chg_dpnt_cvg_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
862 l_dpnt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
863 l_dpnt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
864 l_in_pndg_wkflow_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
865 l_bnft_prvdr_pool_id_va benutils.g_number_table := benutils.g_number_table();
866 l_elig_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
867 l_inelig_rsn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
868 l_fonm_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
869
870 --
871 l_hv pls_integer;
872 --
873 CURSOR c_instance
877 SELECT epe.elig_per_elctbl_chc_id,
874 (c_per_in_ler_id in number
875 )
876 IS
878 epe.business_group_id,
879 pil.person_id,
880 pil.ler_id,
881 pil.LF_EVT_OCRD_DT,
882 pil.per_in_ler_stat_cd,
883 epe.per_in_ler_id,
884 epe.pgm_id,
885 epe.pl_typ_id,
886 epe.ptip_id,
887 epe.plip_id,
888 epe.pl_id,
889 epe.oipl_id,
890 epe.oiplip_id,
891 null opt_id,
892 pel.enrt_perd_id,
893 pel.lee_rsn_id,
894 pel.enrt_perd_strt_dt,
895 epe.prtt_enrt_rslt_id,
896 epe.enrt_cvg_strt_dt,
897 epe.enrt_cvg_strt_dt_cd,
898 epe.enrt_cvg_strt_dt_rl,
899 epe.yr_perd_id,
900 epe.comp_lvl_cd,
901 epe.cmbn_plip_id,
902 epe.cmbn_ptip_id,
903 epe.cmbn_ptip_opt_id,
904 epe.dflt_flag,
905 epe.ctfn_rqd_flag,
906 enb.enrt_bnft_id,
907 enb.val,
908 pel.acty_ref_perd_cd,
909 epe.elctbl_flag,
910 epe.object_version_number,
911 epe.alws_dpnt_dsgn_flag,
912 epe.dpnt_dsgn_cd,
913 epe.ler_chg_dpnt_cvg_cd,
914 epe.dpnt_cvg_strt_dt_cd,
915 epe.dpnt_cvg_strt_dt_rl,
916 epe.in_pndg_wkflow_flag,
917 epe.bnft_prvdr_pool_id,
918 epe.elig_flag,
919 epe.inelig_rsn_cd,
920 epe.fonm_cvg_strt_dt
921 FROM ben_elig_per_elctbl_chc epe,
922 ben_pil_elctbl_chc_popl pel,
923 ben_enrt_bnft enb,
924 ben_per_in_ler pil
925 WHERE epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+)
926 AND epe.per_in_ler_id = pil.per_in_ler_id
927 AND epe.per_in_ler_id = pel.per_in_ler_id
928 AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
929 and pil.per_in_ler_id = c_per_in_ler_id
930 order by epe.PTIP_ORDR_NUM, PLIP_ORDR_NUM,
931 decode(PL_ORDR_NUM, null, OIPL_ORDR_NUM, PL_ORDR_NUM),
932 PL_ORDR_NUM,
933 decode(PL_ORDR_NUM, null, null, OIPL_ORDR_NUM);
934 --
935 begin
936 --
937 open c_instance
938 (c_per_in_ler_id => p_per_in_ler_id
939 );
940 fetch c_instance BULK COLLECT INTO l_elig_per_elctbl_chc_id_va,
941 l_business_group_id_va,
942 l_person_id_va,
943 l_ler_id_va,
944 l_LF_EVT_OCRD_DT_va,
945 l_per_in_ler_stat_cd_va,
946 l_per_in_ler_id_va,
947 l_pgm_id_va,
948 l_pl_typ_id_va,
949 l_ptip_id_va,
950 l_plip_id_va,
951 l_pl_id_va,
952 l_oipl_id_va,
953 l_oiplip_id_va,
954 l_opt_id_va,
955 l_enrt_perd_id_va,
956 l_lee_rsn_id_va,
957 l_enrt_perd_strt_dt_va,
958 l_prtt_enrt_rslt_id_va,
959 l_enrt_cvg_strt_dt_va,
960 l_enrt_cvg_strt_dt_cd_va,
961 l_enrt_cvg_strt_dt_rl_va,
962 l_yr_perd_id_va,
963 l_comp_lvl_cd_va,
964 l_cmbn_plip_id_va,
965 l_cmbn_ptip_id_va,
966 l_cmbn_ptip_opt_id_va,
967 l_dflt_flag_va,
968 l_ctfn_rqd_flag_va,
969 l_enrt_bnft_id_va,
970 l_val_va,
971 l_acty_ref_perd_cd_va,
972 l_elctbl_flag_va,
973 l_object_version_number_va,
974 l_alws_dpnt_dsgn_flag_va,
975 l_dpnt_dsgn_cd_va,
976 l_ler_chg_dpnt_cvg_cd_va,
977 l_dpnt_cvg_strt_dt_cd_va,
978 l_dpnt_cvg_strt_dt_rl_va,
979 l_in_pndg_wkflow_flag_va,
980 l_bnft_prvdr_pool_id_va,
981 l_elig_flag_va,
982 l_inelig_rsn_cd_va,
983 l_fonm_cvg_strt_dt_va;
984 close c_instance;
985 --
986 if l_elig_per_elctbl_chc_id_va.count > 0 then
987 --
988 for i in l_elig_per_elctbl_chc_id_va.first..l_elig_per_elctbl_chc_id_va.last
989 loop
990 --
991 l_hv := mod(l_elig_per_elctbl_chc_id_va(i),ben_hash_utility.get_hash_key);
992 --
993 while g_epe_instance.exists(l_hv)
994 loop
995 --
996 l_hv := l_hv+g_hash_jump;
997 --
998 end loop;
999 --
1000 g_epe_instance(l_hv).elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id_va(i);
1001 g_epe_instance(l_hv).business_group_id := l_business_group_id_va(i);
1005 g_epe_instance(l_hv).per_in_ler_stat_cd := l_per_in_ler_stat_cd_va(i);
1002 g_epe_instance(l_hv).person_id := l_person_id_va(i);
1003 g_epe_instance(l_hv).ler_id := l_ler_id_va(i);
1004 g_epe_instance(l_hv).LF_EVT_OCRD_DT := l_LF_EVT_OCRD_DT_va(i);
1006 g_epe_instance(l_hv).per_in_ler_id := l_per_in_ler_id_va(i);
1007 g_epe_instance(l_hv).pgm_id := l_pgm_id_va(i);
1008 g_epe_instance(l_hv).pl_typ_id := l_pl_typ_id_va(i);
1009 g_epe_instance(l_hv).ptip_id := l_ptip_id_va(i);
1010 g_epe_instance(l_hv).plip_id := l_plip_id_va(i);
1011 g_epe_instance(l_hv).pl_id := l_pl_id_va(i);
1012 g_epe_instance(l_hv).oipl_id := l_oipl_id_va(i);
1013 g_epe_instance(l_hv).oiplip_id := l_oiplip_id_va(i);
1014 g_epe_instance(l_hv).opt_id := null;
1015 g_epe_instance(l_hv).enrt_perd_id := l_enrt_perd_id_va(i);
1016 g_epe_instance(l_hv).lee_rsn_id := l_lee_rsn_id_va(i);
1017 g_epe_instance(l_hv).enrt_perd_strt_dt := l_enrt_perd_strt_dt_va(i);
1018 g_epe_instance(l_hv).prtt_enrt_rslt_id := l_prtt_enrt_rslt_id_va(i);
1019 g_epe_instance(l_hv).enrt_cvg_strt_dt := l_enrt_cvg_strt_dt_va(i);
1020 g_epe_instance(l_hv).enrt_cvg_strt_dt_cd := l_enrt_cvg_strt_dt_cd_va(i);
1021 g_epe_instance(l_hv).enrt_cvg_strt_dt_rl := l_enrt_cvg_strt_dt_rl_va(i);
1022 g_epe_instance(l_hv).yr_perd_id := l_yr_perd_id_va(i);
1023 g_epe_instance(l_hv).comp_lvl_cd := l_comp_lvl_cd_va(i);
1024 g_epe_instance(l_hv).cmbn_plip_id := l_cmbn_plip_id_va(i);
1025 g_epe_instance(l_hv).cmbn_ptip_id := l_cmbn_ptip_id_va(i);
1026 g_epe_instance(l_hv).cmbn_ptip_opt_id := l_cmbn_ptip_opt_id_va(i);
1027 g_epe_instance(l_hv).dflt_flag := l_dflt_flag_va(i);
1028 g_epe_instance(l_hv).ctfn_rqd_flag := l_ctfn_rqd_flag_va(i);
1029 g_epe_instance(l_hv).enrt_bnft_id := l_enrt_bnft_id_va(i);
1030 g_epe_instance(l_hv).val := l_val_va(i);
1031 g_epe_instance(l_hv).acty_ref_perd_cd := l_acty_ref_perd_cd_va(i);
1032 g_epe_instance(l_hv).elctbl_flag := l_elctbl_flag_va(i);
1033 g_epe_instance(l_hv).object_version_number := l_object_version_number_va(i);
1034 g_epe_instance(l_hv).alws_dpnt_dsgn_flag := l_alws_dpnt_dsgn_flag_va(i);
1035 g_epe_instance(l_hv).dpnt_dsgn_cd := l_dpnt_dsgn_cd_va(i);
1036 g_epe_instance(l_hv).ler_chg_dpnt_cvg_cd := l_ler_chg_dpnt_cvg_cd_va(i);
1037 g_epe_instance(l_hv).dpnt_cvg_strt_dt_cd := l_dpnt_cvg_strt_dt_cd_va(i);
1038 g_epe_instance(l_hv).dpnt_cvg_strt_dt_rl := l_dpnt_cvg_strt_dt_rl_va(i);
1039 g_epe_instance(l_hv).in_pndg_wkflow_flag := l_in_pndg_wkflow_flag_va(i);
1040 g_epe_instance(l_hv).bnft_prvdr_pool_id := l_bnft_prvdr_pool_id_va(i);
1041 g_epe_instance(l_hv).elig_flag := l_elig_flag_va(i);
1042 g_epe_instance(l_hv).inelig_rsn_cd := l_inelig_rsn_cd_va(i);
1043 g_epe_instance(l_hv).fonm_cvg_strt_dt := l_fonm_cvg_strt_dt_va(i);
1044
1045 --
1046 g_epe_instance(l_hv).prtn_strt_dt := null;
1047 g_epe_instance(l_hv).prtn_ovridn_flag := null;
1048 g_epe_instance(l_hv).prtn_ovridn_thru_dt := null;
1049 g_epe_instance(l_hv).rt_age_val := null;
1050 g_epe_instance(l_hv).rt_los_val := null;
1051 g_epe_instance(l_hv).rt_hrs_wkd_val := null;
1052 g_epe_instance(l_hv).rt_cmbn_age_n_los_val := null;
1053 --
1054 end loop;
1055 --
1056 end if;
1057 --
1058 /*
1059 for objinst in c_instance
1060 (c_per_in_ler_id => p_per_in_ler_id
1061 )
1062 loop
1063 --
1064 l_hv := mod(objinst.elig_per_elctbl_chc_id,ben_hash_utility.get_hash_key);
1065 --
1066 while g_epe_instance.exists(l_hv)
1067 loop
1068 --
1069 l_hv := l_hv+g_hash_jump;
1070 --
1071 end loop;
1072 --
1073 g_epe_instance(l_hv).elig_per_elctbl_chc_id := objinst.elig_per_elctbl_chc_id;
1074 g_epe_instance(l_hv).business_group_id := objinst.business_group_id;
1075 g_epe_instance(l_hv).person_id := objinst.person_id;
1076 g_epe_instance(l_hv).ler_id := objinst.ler_id;
1077 g_epe_instance(l_hv).per_in_ler_id := objinst.per_in_ler_id;
1078 g_epe_instance(l_hv).LF_EVT_OCRD_DT := objinst.LF_EVT_OCRD_DT;
1079 g_epe_instance(l_hv).per_in_ler_stat_cd := objinst.per_in_ler_stat_cd;
1080 g_epe_instance(l_hv).pgm_id := objinst.pgm_id;
1081 g_epe_instance(l_hv).pl_typ_id := objinst.pl_typ_id;
1082 g_epe_instance(l_hv).ptip_id := objinst.ptip_id;
1083 g_epe_instance(l_hv).plip_id := objinst.plip_id;
1084 g_epe_instance(l_hv).pl_id := objinst.pl_id;
1085 g_epe_instance(l_hv).oipl_id := objinst.oipl_id;
1086 g_epe_instance(l_hv).oiplip_id := objinst.oiplip_id;
1087 g_epe_instance(l_hv).comp_lvl_cd := objinst.comp_lvl_cd;
1088 g_epe_instance(l_hv).cmbn_plip_id := objinst.cmbn_plip_id;
1089 g_epe_instance(l_hv).cmbn_ptip_id := objinst.cmbn_ptip_id;
1090 g_epe_instance(l_hv).cmbn_ptip_opt_id := objinst.cmbn_ptip_opt_id;
1091 g_epe_instance(l_hv).dflt_flag := objinst.dflt_flag;
1092 g_epe_instance(l_hv).ctfn_rqd_flag := objinst.ctfn_rqd_flag;
1093 g_epe_instance(l_hv).prtt_enrt_rslt_id := objinst.prtt_enrt_rslt_id;
1094 g_epe_instance(l_hv).enrt_cvg_strt_dt := objinst.enrt_cvg_strt_dt;
1095 g_epe_instance(l_hv).enrt_cvg_strt_dt_cd := objinst.enrt_cvg_strt_dt_cd;
1096 g_epe_instance(l_hv).enrt_cvg_strt_dt_rl := objinst.enrt_cvg_strt_dt_rl;
1100 g_epe_instance(l_hv).enrt_perd_strt_dt := objinst.enrt_perd_strt_dt;
1097 g_epe_instance(l_hv).yr_perd_id := objinst.yr_perd_id;
1098 g_epe_instance(l_hv).enrt_bnft_id := objinst.enrt_bnft_id;
1099 g_epe_instance(l_hv).val := objinst.val;
1101 g_epe_instance(l_hv).enrt_perd_id := objinst.enrt_perd_id;
1102 g_epe_instance(l_hv).lee_rsn_id := objinst.lee_rsn_id;
1103 g_epe_instance(l_hv).acty_ref_perd_cd := objinst.acty_ref_perd_cd;
1104 g_epe_instance(l_hv).elctbl_flag := objinst.elctbl_flag;
1105 g_epe_instance(l_hv).object_version_number := objinst.object_version_number;
1106 g_epe_instance(l_hv).alws_dpnt_dsgn_flag := objinst.alws_dpnt_dsgn_flag;
1107 g_epe_instance(l_hv).dpnt_dsgn_cd := objinst.dpnt_dsgn_cd;
1108 g_epe_instance(l_hv).ler_chg_dpnt_cvg_cd := objinst.ler_chg_dpnt_cvg_cd;
1109 g_epe_instance(l_hv).dpnt_cvg_strt_dt_cd := objinst.dpnt_cvg_strt_dt_cd;
1110 g_epe_instance(l_hv).dpnt_cvg_strt_dt_rl := objinst.dpnt_cvg_strt_dt_rl;
1111 g_epe_instance(l_hv).in_pndg_wkflow_flag := objinst.in_pndg_wkflow_flag;
1112 --
1113 g_epe_instance(l_hv).opt_id := null;
1114 g_epe_instance(l_hv).prtn_strt_dt := null;
1115 g_epe_instance(l_hv).prtn_ovridn_flag := null;
1116 g_epe_instance(l_hv).prtn_ovridn_thru_dt := null;
1117 g_epe_instance(l_hv).rt_age_val := null;
1118 g_epe_instance(l_hv).rt_los_val := null;
1119 g_epe_instance(l_hv).rt_hrs_wkd_val := null;
1120 g_epe_instance(l_hv).rt_cmbn_age_n_los_val := null;
1121 --
1122 end loop;
1123 */
1124 --
1125 -- Check for no rows found
1126 --
1127 if l_hv is null then
1128 --
1129 g_epe_instance.delete;
1130 g_epe_current.per_in_ler_id := null;
1131 --
1132 else
1133 --
1134 g_epe_current.per_in_ler_id := p_per_in_ler_id;
1135 --
1136 end if;
1137 --
1138 end write_EPE_cache;
1139 --
1140 procedure EPE_GetEPEDets
1141 (p_elig_per_elctbl_chc_id in number
1142 ,p_per_in_ler_id in number
1143 ,p_inst_row in out NOCOPY g_pilepe_inst_row
1144 )
1145 is
1146 --
1147 l_proc varchar2(72) := 'EPE_GetEPEDets';
1148 --
1149 l_hv pls_integer;
1150 l_reset g_pilepe_inst_row;
1151 --
1152 begin
1153 --
1154 -- Check for already cached or a change in current PIL ID
1155 --
1156 if nvl(g_epe_current.per_in_ler_id,-9999) <> p_per_in_ler_id
1157 or g_epe_cached < 2
1158 then
1159 --
1160 -- When PIL changes then flush current cache
1161 --
1162 if nvl(g_epe_current.per_in_ler_id,-9999) <> p_per_in_ler_id
1163 then
1164 --
1165 g_epe_instance.delete;
1166 g_epe_cached := 1;
1167 --
1168 end if;
1169 --
1170 -- Write the cache
1171 --
1172 write_EPE_cache
1173 (p_per_in_ler_id => p_per_in_ler_id
1174 );
1175 --
1176 if g_epe_cached = 1
1177 then
1178 --
1179 g_epe_cached := 2;
1180 --
1181 end if;
1182 --
1183 end if;
1184 --
1185 -- Get the instance details
1186 --
1187 l_hv := mod(p_elig_per_elctbl_chc_id,ben_hash_utility.get_hash_key);
1188 --
1189 if g_epe_instance(l_hv).elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1190 then
1191 -- Matched row
1192 null;
1193 else
1194 --
1195 -- Loop through the hash using the jump routine to check further
1196 -- indexes if none exists at current index the NO_DATA_FOUND expection
1197 -- will fire
1198 --
1199 l_hv := l_hv+g_hash_jump;
1200 while g_epe_instance(l_hv).elig_per_elctbl_chc_id <> p_elig_per_elctbl_chc_id
1201 loop
1202 --
1203 l_hv := l_hv+g_hash_jump;
1204 --
1205 end loop;
1206 --
1207 end if;
1208 --
1209 p_inst_row := g_epe_instance(l_hv);
1210 --
1211 exception
1212 --
1213 when no_data_found then
1214 --
1215 p_inst_row := l_reset;
1216 --
1217 end EPE_GetEPEDets;
1218 --
1219 procedure get_pilcobjepe_dets
1220 (p_per_in_ler_id in number
1221 ,p_pgm_id in number
1222 ,p_pl_id in number
1223 ,p_oipl_id in number
1224 --
1225 ,p_inst_row in out NOCOPY g_pilepe_inst_row
1226 )
1227 is
1228 --
1229 l_proc varchar2(72) := 'get_pilcobjepe_dets';
1230 --
1231 l_inst_row g_pilepe_inst_row;
1232 --
1233 CURSOR c_choice_exists_for_option
1234 (c_per_in_ler_id number
1235 ,c_pgm_id number
1236 ,c_oipl_id number
1237 )
1238 is
1239 SELECT epe.elig_per_elctbl_chc_id
1240 FROM ben_elig_per_elctbl_chc epe
1241 WHERE epe.oipl_id = c_oipl_id
1242 AND epe.pgm_id = c_pgm_id
1243 AND epe.per_in_ler_id = c_per_in_ler_id;
1244 --
1245 CURSOR c_chc_exists_for_plnip_option
1246 (c_per_in_ler_id number
1247 ,c_oipl_id number
1248 )
1249 is
1250 SELECT epe.elig_per_elctbl_chc_id
1251 FROM ben_elig_per_elctbl_chc epe
1252 WHERE epe.oipl_id = c_oipl_id
1253 AND epe.pgm_id IS NULL
1254 AND epe.per_in_ler_id = c_per_in_ler_id;
1255 --
1256 CURSOR c_choice_exists_for_plan
1257 (c_per_in_ler_id number
1258 ,c_pgm_id number
1259 ,c_pl_id number
1260 )
1261 is
1262 SELECT epe.elig_per_elctbl_chc_id
1263 FROM ben_elig_per_elctbl_chc epe
1264 WHERE epe.pl_id = c_pl_id
1265 AND epe.oipl_id IS NULL
1266 AND epe.pgm_id = c_pgm_id
1267 AND epe.per_in_ler_id = c_per_in_ler_id;
1268 --
1269 CURSOR c_choice_exists_for_plnip
1270 (c_per_in_ler_id number
1271 ,c_pl_id number
1272 )
1273 is
1274 SELECT epe.elig_per_elctbl_chc_id
1275 FROM ben_elig_per_elctbl_chc epe
1276 WHERE epe.pl_id = c_pl_id
1277 AND epe.oipl_id IS NULL
1278 AND epe.pgm_id IS NULL
1279 AND epe.per_in_ler_id = c_per_in_ler_id;
1280 --
1281 begin
1282 --
1283 if p_oipl_id is null
1284 then
1285 --
1286 if p_pgm_id is not null
1287 then
1288 --
1289 OPEN c_choice_exists_for_plan
1290 (c_per_in_ler_id => p_per_in_ler_id
1291 ,c_pgm_id => p_pgm_id
1292 ,c_pl_id => p_pl_id
1293 );
1294 FETCH c_choice_exists_for_plan INTO l_inst_row.elig_per_elctbl_chc_id;
1295 CLOSE c_choice_exists_for_plan;
1296 --
1297 else
1298 --
1299 OPEN c_choice_exists_for_plnip
1300 (c_per_in_ler_id => p_per_in_ler_id
1301 ,c_pl_id => p_pl_id
1302 );
1303 FETCH c_choice_exists_for_plnip INTO l_inst_row.elig_per_elctbl_chc_id;
1304 CLOSE c_choice_exists_for_plnip;
1305 --
1306 end if;
1307 --
1308 else
1309 --
1310 if p_pgm_id is not null
1311 then
1312 --
1313 OPEN c_choice_exists_for_option
1314 (c_per_in_ler_id => p_per_in_ler_id
1315 ,c_pgm_id => p_pgm_id
1316 ,c_oipl_id => p_oipl_id
1317 );
1318 FETCH c_choice_exists_for_option INTO l_inst_row.elig_per_elctbl_chc_id;
1319 CLOSE c_choice_exists_for_option;
1320 --
1321 else
1322 --
1323 OPEN c_chc_exists_for_plnip_option
1324 (c_per_in_ler_id => p_per_in_ler_id
1325 ,c_oipl_id => p_oipl_id
1326 );
1327 FETCH c_chc_exists_for_plnip_option INTO l_inst_row.elig_per_elctbl_chc_id;
1328 CLOSE c_chc_exists_for_plnip_option;
1329 --
1330 end if;
1331 --
1332 end if;
1333 --
1334 p_inst_row := l_inst_row;
1335 --
1336 end get_pilcobjepe_dets;
1337 --
1338 procedure init_context_pileperow
1339 is
1340
1341 l_currepe_row g_pilepe_inst_row;
1342
1343 begin
1344 --
1345 ben_epe_cache.g_currepe_row := l_currepe_row;
1346 --
1347 end init_context_pileperow;
1348 --
1349 procedure init_context_cobj_pileperow
1350 is
1351
1352 l_currepe_row g_pilepe_inst_row;
1353
1354 begin
1355 --
1356 ben_epe_cache.g_currcobjepe_row := l_currepe_row;
1357 --
1358 end init_context_cobj_pileperow;
1359 --
1360 procedure clear_down_cache
1361 is
1362
1363 begin
1364 --
1365 g_pilepe_instance.delete;
1366 g_pilepe_cached := 1;
1367 --
1368 g_enbepe_instance.delete;
1369 g_enbepe_cached := 1;
1370 g_enbepe_current.per_in_ler_id := null;
1371 --
1372 g_epe_instance.delete;
1373 g_epe_cached := 1;
1374 g_epe_current.per_in_ler_id := null;
1375 --
1376 init_context_pileperow;
1377 --
1378 end clear_down_cache;
1379 --
1380 end ben_epe_cache;