[Home] [Help]
PACKAGE BODY: APPS.BEN_REINSTATE_EPE_CACHE
Source
1 package body ben_reinstate_epe_cache as
2 /* $Header: berepech.pkb 120.0 2005/05/28 11:38:39 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 01-Apr-05 ikasire Created.
15 -----------------------------------------------------------------------------
16 */
17 --
18 -- Globals.
19 --
20 g_package varchar2(50) := 'ben_reinstate_epe_cache.';
21 g_hash_key number := ben_hash_utility.get_hash_key;
22 g_hash_jump number := ben_hash_utility.get_hash_jump;
23 --
24 -- 0 - Always refresh
25 -- 1 - Initialise cache
26 -- 2 - Cache hit
27 --
28 g_pilepe_instance g_pilepe_inst_tbl;
29 g_pilepe_cached pls_integer := 0;
30 g_perepe_cached pls_integer := 0;
31 g_epe_cached pls_integer := 0;
32 --
33 type g_current_row is record
34 (per_in_ler_id number
35 );
36 --
37 g_epe_current g_current_row;
38 --
39 procedure write_pilepe_cache
40 (p_per_in_ler_id in number
41 )
42 is
43 --
44 l_alws_dpnt_dsgn_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
45 l_approval_status_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
46 l_assignment_id_va benutils.g_number_table := benutils.g_number_table();
47 l_auto_enrt_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
48 l_bnft_prvdr_pool_id_va benutils.g_number_table := benutils.g_number_table();
49 l_business_group_id_va benutils.g_number_table := benutils.g_number_table();
50 l_cmbn_plip_id_va benutils.g_number_table := benutils.g_number_table();
51 l_cmbn_ptip_id_va benutils.g_number_table := benutils.g_number_table();
52 l_cmbn_ptip_opt_id_va benutils.g_number_table := benutils.g_number_table();
53 l_comp_lvl_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
54 l_crntly_enrd_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
55 l_cryfwd_elig_dpnt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
56 l_ctfn_rqd_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
57 l_dflt_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
58 l_dpnt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
59 l_dpnt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
60 l_dpnt_dsgn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
61 l_elctbl_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
62 l_elig_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
63 l_elig_ovrid_dt_va benutils.g_date_table := benutils.g_date_table();
64 l_elig_ovrid_person_id_va benutils.g_number_table := benutils.g_number_table();
65 l_elig_per_elctbl_chc_id_va benutils.g_number_table := benutils.g_number_table();
66 l_enrt_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
67 l_enrt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
68 l_enrt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
69 l_erlst_deenrt_dt_va benutils.g_date_table := benutils.g_date_table();
70 l_fonm_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
71 l_inelig_rsn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
72 l_interim_epe_id_va benutils.g_number_table := benutils.g_number_table();
73 l_in_pndg_wkflow_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
74 l_ler_chg_dpnt_cvg_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
75 l_mgr_ovrid_dt_va benutils.g_date_table := benutils.g_date_table();
76 l_mgr_ovrid_person_id_va benutils.g_number_table := benutils.g_number_table();
77 l_mndtry_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
78 l_must_enrl_anthr_pl_id_va benutils.g_number_table := benutils.g_number_table();
79 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
80 l_oiplip_id_va benutils.g_number_table := benutils.g_number_table();
81 l_oipl_id_va benutils.g_number_table := benutils.g_number_table();
82 l_oipl_ordr_num_va benutils.g_number_table := benutils.g_number_table();
83 l_per_in_ler_id_va benutils.g_number_table := benutils.g_number_table();
84 l_pgm_id_va benutils.g_number_table := benutils.g_number_table();
85 l_pil_elctbl_chc_popl_id_va benutils.g_number_table := benutils.g_number_table();
86 l_plip_id_va benutils.g_number_table := benutils.g_number_table();
87 l_plip_ordr_num_va benutils.g_number_table := benutils.g_number_table();
88 l_pl_id_va benutils.g_number_table := benutils.g_number_table();
89 l_pl_ordr_num_va benutils.g_number_table := benutils.g_number_table();
90 l_pl_typ_id_va benutils.g_number_table := benutils.g_number_table();
91 l_procg_end_dt_va benutils.g_date_table := benutils.g_date_table();
92 l_prtt_enrt_rslt_id_va benutils.g_number_table := benutils.g_number_table();
93 l_ptip_id_va benutils.g_number_table := benutils.g_number_table();
94 l_ptip_ordr_num_va benutils.g_number_table := benutils.g_number_table();
95 l_roll_crs_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
96 l_spcl_rt_oipl_id_va benutils.g_number_table := benutils.g_number_table();
97 l_spcl_rt_pl_id_va benutils.g_number_table := benutils.g_number_table();
98 l_ws_mgr_id_va benutils.g_number_table := benutils.g_number_table();
99 l_yr_perd_id_va benutils.g_number_table := benutils.g_number_table();
100 --
101 l_ele_num pls_integer;
102 --
103 CURSOR c_instance
104 ( c_per_in_ler_id in number)
105 IS
106 SELECT
107 epe.alws_dpnt_dsgn_flag
108 ,epe.approval_status_cd
109 ,epe.assignment_id
110 ,epe.auto_enrt_flag
111 ,epe.bnft_prvdr_pool_id
112 ,epe.business_group_id
113 ,epe.cmbn_plip_id
114 ,epe.cmbn_ptip_id
115 ,epe.cmbn_ptip_opt_id
116 ,epe.comp_lvl_cd
117 ,epe.crntly_enrd_flag
118 ,epe.cryfwd_elig_dpnt_cd
119 ,epe.ctfn_rqd_flag
120 ,epe.dflt_flag
121 ,epe.dpnt_cvg_strt_dt_cd
122 ,epe.dpnt_cvg_strt_dt_rl
123 ,epe.dpnt_dsgn_cd
124 ,epe.elctbl_flag
125 ,epe.elig_flag
126 ,epe.elig_ovrid_dt
127 ,epe.elig_ovrid_person_id
128 ,epe.elig_per_elctbl_chc_id
129 ,epe.enrt_cvg_strt_dt
130 ,epe.enrt_cvg_strt_dt_cd
131 ,epe.enrt_cvg_strt_dt_rl
132 ,epe.erlst_deenrt_dt
133 ,epe.fonm_cvg_strt_dt
134 ,epe.inelig_rsn_cd
135 ,epe.interim_elig_per_elctbl_chc_id
136 ,epe.in_pndg_wkflow_flag
137 ,epe.ler_chg_dpnt_cvg_cd
138 ,epe.mgr_ovrid_dt
139 ,epe.mgr_ovrid_person_id
140 ,epe.mndtry_flag
141 ,epe.must_enrl_anthr_pl_id
142 ,epe.object_version_number
143 ,epe.oiplip_id
144 ,epe.oipl_id
145 ,epe.oipl_ordr_num
146 ,epe.per_in_ler_id
147 ,epe.pgm_id
148 ,epe.pil_elctbl_chc_popl_id
149 ,epe.plip_id
150 ,epe.plip_ordr_num
151 ,epe.pl_id
152 ,epe.pl_ordr_num
153 ,epe.pl_typ_id
154 ,epe.procg_end_dt
155 ,epe.prtt_enrt_rslt_id
156 ,epe.ptip_id
157 ,epe.ptip_ordr_num
158 ,epe.roll_crs_flag
159 ,epe.spcl_rt_oipl_id
160 ,epe.spcl_rt_pl_id
161 ,epe.ws_mgr_id
162 ,epe.yr_perd_id
163 FROM ben_elig_per_elctbl_chc epe
164 WHERE epe.per_in_ler_id = c_per_in_ler_id
165 order by epe.PTIP_ORDR_NUM,
166 epe.PLIP_ORDR_NUM,
167 decode(PL_ORDR_NUM, null, OIPL_ORDR_NUM, PL_ORDR_NUM),
168 PL_ORDR_NUM,
169 decode(PL_ORDR_NUM, null, null, OIPL_ORDR_NUM);
170 --
171 begin
172 --
173 l_ele_num := 0;
174 --
175 open c_instance
176 ( c_per_in_ler_id => p_per_in_ler_id
177 );
178 fetch c_instance BULK COLLECT INTO
179 l_alws_dpnt_dsgn_flag_va
180 ,l_approval_status_cd_va
181 ,l_assignment_id_va
182 ,l_auto_enrt_flag_va
183 ,l_bnft_prvdr_pool_id_va
184 ,l_business_group_id_va
185 ,l_cmbn_plip_id_va
186 ,l_cmbn_ptip_id_va
187 ,l_cmbn_ptip_opt_id_va
188 ,l_comp_lvl_cd_va
189 ,l_crntly_enrd_flag_va
190 ,l_cryfwd_elig_dpnt_cd_va
191 ,l_ctfn_rqd_flag_va
192 ,l_dflt_flag_va
193 ,l_dpnt_cvg_strt_dt_cd_va
194 ,l_dpnt_cvg_strt_dt_rl_va
195 ,l_dpnt_dsgn_cd_va
196 ,l_elctbl_flag_va
197 ,l_elig_flag_va
198 ,l_elig_ovrid_dt_va
199 ,l_elig_ovrid_person_id_va
200 ,l_elig_per_elctbl_chc_id_va
201 ,l_enrt_cvg_strt_dt_va
202 ,l_enrt_cvg_strt_dt_cd_va
203 ,l_enrt_cvg_strt_dt_rl_va
204 ,l_erlst_deenrt_dt_va
205 ,l_fonm_cvg_strt_dt_va
206 ,l_inelig_rsn_cd_va
207 ,l_interim_epe_id_va
208 ,l_in_pndg_wkflow_flag_va
209 ,l_ler_chg_dpnt_cvg_cd_va
210 ,l_mgr_ovrid_dt_va
211 ,l_mgr_ovrid_person_id_va
212 ,l_mndtry_flag_va
213 ,l_must_enrl_anthr_pl_id_va
214 ,l_object_version_number_va
215 ,l_oiplip_id_va
216 ,l_oipl_id_va
217 ,l_oipl_ordr_num_va
218 ,l_per_in_ler_id_va
219 ,l_pgm_id_va
220 ,l_pil_elctbl_chc_popl_id_va
221 ,l_plip_id_va
222 ,l_plip_ordr_num_va
223 ,l_pl_id_va
224 ,l_pl_ordr_num_va
225 ,l_pl_typ_id_va
226 ,l_procg_end_dt_va
227 ,l_prtt_enrt_rslt_id_va
228 ,l_ptip_id_va
229 ,l_ptip_ordr_num_va
230 ,l_roll_crs_flag_va
231 ,l_spcl_rt_oipl_id_va
232 ,l_spcl_rt_pl_id_va
233 ,l_ws_mgr_id_va
234 ,l_yr_perd_id_va;
235 close c_instance;
236 --
237 if l_elig_per_elctbl_chc_id_va.count > 0 then
238 --
239 for i in l_elig_per_elctbl_chc_id_va.first..l_elig_per_elctbl_chc_id_va.last
240 loop
241 --
242 g_pilepe_instance(l_ele_num).alws_dpnt_dsgn_flag := l_alws_dpnt_dsgn_flag_va(i);
243 g_pilepe_instance(l_ele_num).approval_status_cd := l_approval_status_cd_va(i);
244 g_pilepe_instance(l_ele_num).assignment_id := l_assignment_id_va(i);
245 g_pilepe_instance(l_ele_num).auto_enrt_flag := l_auto_enrt_flag_va(i);
246 g_pilepe_instance(l_ele_num).bnft_prvdr_pool_id := l_bnft_prvdr_pool_id_va(i);
247 g_pilepe_instance(l_ele_num).business_group_id := l_business_group_id_va(i);
248 g_pilepe_instance(l_ele_num).cmbn_plip_id := l_cmbn_plip_id_va(i);
249 g_pilepe_instance(l_ele_num).cmbn_ptip_id := l_cmbn_ptip_id_va(i);
250 g_pilepe_instance(l_ele_num).cmbn_ptip_opt_id := l_cmbn_ptip_opt_id_va(i);
251 g_pilepe_instance(l_ele_num).comp_lvl_cd := l_comp_lvl_cd_va(i);
252 g_pilepe_instance(l_ele_num).crntly_enrd_flag := l_crntly_enrd_flag_va(i);
253 g_pilepe_instance(l_ele_num).cryfwd_elig_dpnt_cd := l_cryfwd_elig_dpnt_cd_va(i);
254 g_pilepe_instance(l_ele_num).ctfn_rqd_flag := l_ctfn_rqd_flag_va(i);
255 g_pilepe_instance(l_ele_num).dflt_flag := l_dflt_flag_va(i);
256 g_pilepe_instance(l_ele_num).dpnt_cvg_strt_dt_cd := l_dpnt_cvg_strt_dt_cd_va(i);
257 g_pilepe_instance(l_ele_num).dpnt_cvg_strt_dt_rl := l_dpnt_cvg_strt_dt_rl_va(i);
258 g_pilepe_instance(l_ele_num).dpnt_dsgn_cd := l_dpnt_dsgn_cd_va(i);
259 g_pilepe_instance(l_ele_num).elctbl_flag := l_elctbl_flag_va(i);
260 g_pilepe_instance(l_ele_num).elig_flag := l_elig_flag_va(i);
261 g_pilepe_instance(l_ele_num).elig_ovrid_dt := l_elig_ovrid_dt_va(i);
262 g_pilepe_instance(l_ele_num).elig_ovrid_person_id := l_elig_ovrid_person_id_va(i);
263 g_pilepe_instance(l_ele_num).elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id_va(i);
264 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt := l_enrt_cvg_strt_dt_va(i);
265 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt_cd := l_enrt_cvg_strt_dt_cd_va(i);
266 g_pilepe_instance(l_ele_num).enrt_cvg_strt_dt_rl := l_enrt_cvg_strt_dt_rl_va(i);
267 g_pilepe_instance(l_ele_num).erlst_deenrt_dt := l_erlst_deenrt_dt_va(i);
268 g_pilepe_instance(l_ele_num).fonm_cvg_strt_dt := l_fonm_cvg_strt_dt_va(i);
269 g_pilepe_instance(l_ele_num).inelig_rsn_cd := l_inelig_rsn_cd_va(i);
270 g_pilepe_instance(l_ele_num).interim_elig_per_elctbl_chc_id := l_interim_epe_id_va(i);
271 g_pilepe_instance(l_ele_num).in_pndg_wkflow_flag := l_in_pndg_wkflow_flag_va(i);
272 g_pilepe_instance(l_ele_num).ler_chg_dpnt_cvg_cd := l_ler_chg_dpnt_cvg_cd_va(i);
273 g_pilepe_instance(l_ele_num).mgr_ovrid_dt := l_mgr_ovrid_dt_va(i);
274 g_pilepe_instance(l_ele_num).mgr_ovrid_person_id := l_mgr_ovrid_person_id_va(i);
275 g_pilepe_instance(l_ele_num).mndtry_flag := l_mndtry_flag_va(i);
276 g_pilepe_instance(l_ele_num).must_enrl_anthr_pl_id := l_must_enrl_anthr_pl_id_va(i);
277 g_pilepe_instance(l_ele_num).object_version_number := l_object_version_number_va(i);
278 g_pilepe_instance(l_ele_num).oiplip_id := l_oiplip_id_va(i);
279 g_pilepe_instance(l_ele_num).oipl_id := l_oipl_id_va(i);
280 g_pilepe_instance(l_ele_num).oipl_ordr_num := l_oipl_ordr_num_va(i);
281 g_pilepe_instance(l_ele_num).per_in_ler_id := l_per_in_ler_id_va(i);
282 g_pilepe_instance(l_ele_num).pgm_id := l_pgm_id_va(i);
283 g_pilepe_instance(l_ele_num).pil_elctbl_chc_popl_id := l_pil_elctbl_chc_popl_id_va(i);
284 g_pilepe_instance(l_ele_num).plip_id := l_plip_id_va(i);
285 g_pilepe_instance(l_ele_num).plip_ordr_num := l_plip_ordr_num_va(i);
286 g_pilepe_instance(l_ele_num).pl_id := l_pl_id_va(i);
287 g_pilepe_instance(l_ele_num).pl_ordr_num := l_pl_ordr_num_va(i);
288 g_pilepe_instance(l_ele_num).pl_typ_id := l_pl_typ_id_va(i);
289 g_pilepe_instance(l_ele_num).procg_end_dt := l_procg_end_dt_va(i);
290 g_pilepe_instance(l_ele_num).prtt_enrt_rslt_id := l_prtt_enrt_rslt_id_va(i);
291 g_pilepe_instance(l_ele_num).ptip_id := l_ptip_id_va(i);
292 g_pilepe_instance(l_ele_num).ptip_ordr_num := l_ptip_ordr_num_va(i);
293 g_pilepe_instance(l_ele_num).roll_crs_flag := l_roll_crs_flag_va(i);
294 g_pilepe_instance(l_ele_num).spcl_rt_oipl_id := l_spcl_rt_oipl_id_va(i);
295 g_pilepe_instance(l_ele_num).spcl_rt_pl_id := l_spcl_rt_pl_id_va(i);
296 g_pilepe_instance(l_ele_num).ws_mgr_id := l_ws_mgr_id_va(i);
297 g_pilepe_instance(l_ele_num).yr_perd_id := l_yr_perd_id_va(i);
298 --
299 l_ele_num := l_ele_num+1;
300 --
301 end loop;
302 --
303 end if;
304 --
305 -- Check for no rows found
306 --
307 if l_ele_num = 0 then
308 --
309 g_pilepe_instance.delete;
310 --
311 end if;
312 --
313 end write_pilepe_cache;
314 --
315 procedure get_perpilepe_list
316 (p_per_in_ler_id in number
317 ,p_inst_set in out NOCOPY g_pilepe_inst_tbl
318 )
319 is
320 --
321 l_proc varchar2(72) := 'get_perpilepe_list';
322 --
323 begin
324 --
325 -- check comp object type
326 --
327 if g_pilepe_cached < 2
328 then
329 --
330 -- Write the cache
331 --
332 write_pilepe_cache
333 (p_per_in_ler_id =>p_per_in_ler_id
334 );
335 --
336 if g_pilepe_cached = 1
337 then
338 --
339 g_pilepe_cached := 2;
340 --
341 end if;
342 --
343 end if;
344 --
345 p_inst_set := g_pilepe_instance;
346 --
347 end get_perpilepe_list;
348 --
349 procedure write_EPE_cache
350 (p_per_in_ler_id in number
351 )
352 is
353 --
354 l_proc varchar2(72) := 'write_EPE_cache';
355 --
356 l_alws_dpnt_dsgn_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
357 l_approval_status_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
358 l_assignment_id_va benutils.g_number_table := benutils.g_number_table();
359 l_auto_enrt_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
360 l_bnft_prvdr_pool_id_va benutils.g_number_table := benutils.g_number_table();
361 l_business_group_id_va benutils.g_number_table := benutils.g_number_table();
362 l_cmbn_plip_id_va benutils.g_number_table := benutils.g_number_table();
363 l_cmbn_ptip_id_va benutils.g_number_table := benutils.g_number_table();
364 l_cmbn_ptip_opt_id_va benutils.g_number_table := benutils.g_number_table();
365 l_comp_lvl_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
369 l_dflt_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
366 l_crntly_enrd_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
367 l_cryfwd_elig_dpnt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
368 l_ctfn_rqd_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
370 l_dpnt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
371 l_dpnt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
372 l_dpnt_dsgn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
373 l_elctbl_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
374 l_elig_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
375 l_elig_ovrid_dt_va benutils.g_date_table := benutils.g_date_table();
376 l_elig_ovrid_person_id_va benutils.g_number_table := benutils.g_number_table();
377 l_elig_per_elctbl_chc_id_va benutils.g_number_table := benutils.g_number_table();
378 l_enrt_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
379 l_enrt_cvg_strt_dt_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
380 l_enrt_cvg_strt_dt_rl_va benutils.g_number_table := benutils.g_number_table();
381 l_erlst_deenrt_dt_va benutils.g_date_table := benutils.g_date_table();
382 l_fonm_cvg_strt_dt_va benutils.g_date_table := benutils.g_date_table();
383 l_inelig_rsn_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
384 l_interim_epe_id_va benutils.g_number_table := benutils.g_number_table();
385 l_in_pndg_wkflow_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
386 l_ler_chg_dpnt_cvg_cd_va benutils.g_v2_30_table := benutils.g_v2_30_table();
387 l_mgr_ovrid_dt_va benutils.g_date_table := benutils.g_date_table();
388 l_mgr_ovrid_person_id_va benutils.g_number_table := benutils.g_number_table();
389 l_mndtry_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
390 l_must_enrl_anthr_pl_id_va benutils.g_number_table := benutils.g_number_table();
391 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
392 l_oiplip_id_va benutils.g_number_table := benutils.g_number_table();
393 l_oipl_id_va benutils.g_number_table := benutils.g_number_table();
394 l_oipl_ordr_num_va benutils.g_number_table := benutils.g_number_table();
395 l_per_in_ler_id_va benutils.g_number_table := benutils.g_number_table();
396 l_pgm_id_va benutils.g_number_table := benutils.g_number_table();
397 l_pil_elctbl_chc_popl_id_va benutils.g_number_table := benutils.g_number_table();
398 l_plip_id_va benutils.g_number_table := benutils.g_number_table();
399 l_plip_ordr_num_va benutils.g_number_table := benutils.g_number_table();
400 l_pl_id_va benutils.g_number_table := benutils.g_number_table();
401 l_pl_ordr_num_va benutils.g_number_table := benutils.g_number_table();
402 l_pl_typ_id_va benutils.g_number_table := benutils.g_number_table();
403 l_procg_end_dt_va benutils.g_date_table := benutils.g_date_table();
404 l_prtt_enrt_rslt_id_va benutils.g_number_table := benutils.g_number_table();
405 l_ptip_id_va benutils.g_number_table := benutils.g_number_table();
406 l_ptip_ordr_num_va benutils.g_number_table := benutils.g_number_table();
407 l_roll_crs_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
408 l_spcl_rt_oipl_id_va benutils.g_number_table := benutils.g_number_table();
409 l_spcl_rt_pl_id_va benutils.g_number_table := benutils.g_number_table();
410 l_ws_mgr_id_va benutils.g_number_table := benutils.g_number_table();
411 l_yr_perd_id_va benutils.g_number_table := benutils.g_number_table();
412 --
413 l_hv pls_integer;
414 --
415 CURSOR c_instance
416 (c_per_in_ler_id in number
417 )
418 IS
419 SELECT
420 epe.alws_dpnt_dsgn_flag
421 ,epe.approval_status_cd
422 ,epe.assignment_id
423 ,epe.auto_enrt_flag
424 ,epe.bnft_prvdr_pool_id
425 ,epe.business_group_id
426 ,epe.cmbn_plip_id
427 ,epe.cmbn_ptip_id
428 ,epe.cmbn_ptip_opt_id
429 ,epe.comp_lvl_cd
430 ,epe.crntly_enrd_flag
431 ,epe.cryfwd_elig_dpnt_cd
432 ,epe.ctfn_rqd_flag
433 ,epe.dflt_flag
434 ,epe.dpnt_cvg_strt_dt_cd
435 ,epe.dpnt_cvg_strt_dt_rl
436 ,epe.dpnt_dsgn_cd
437 ,epe.elctbl_flag
438 ,epe.elig_flag
439 ,epe.elig_ovrid_dt
440 ,epe.elig_ovrid_person_id
441 ,epe.elig_per_elctbl_chc_id
442 ,epe.enrt_cvg_strt_dt
443 ,epe.enrt_cvg_strt_dt_cd
444 ,epe.enrt_cvg_strt_dt_rl
445 ,epe.erlst_deenrt_dt
446 ,epe.fonm_cvg_strt_dt
447 ,epe.inelig_rsn_cd
448 ,epe.interim_elig_per_elctbl_chc_id
449 ,epe.in_pndg_wkflow_flag
450 ,epe.ler_chg_dpnt_cvg_cd
451 ,epe.mgr_ovrid_dt
452 ,epe.mgr_ovrid_person_id
453 ,epe.mndtry_flag
454 ,epe.must_enrl_anthr_pl_id
455 ,epe.object_version_number
456 ,epe.oiplip_id
457 ,epe.oipl_id
458 ,epe.oipl_ordr_num
459 ,epe.per_in_ler_id
460 ,epe.pgm_id
461 ,epe.pil_elctbl_chc_popl_id
462 ,epe.plip_id
463 ,epe.plip_ordr_num
464 ,epe.pl_id
465 ,epe.pl_ordr_num
466 ,epe.pl_typ_id
467 ,epe.procg_end_dt
468 ,epe.prtt_enrt_rslt_id
469 ,epe.ptip_id
470 ,epe.ptip_ordr_num
471 ,epe.roll_crs_flag
472 ,epe.spcl_rt_oipl_id
473 ,epe.spcl_rt_pl_id
474 ,epe.ws_mgr_id
475 ,epe.yr_perd_id
476 FROM ben_elig_per_elctbl_chc epe
477 WHERE epe.per_in_ler_id = c_per_in_ler_id
478 order by epe.PTIP_ORDR_NUM,
479 epe.PLIP_ORDR_NUM,
480 decode(epe.PL_ORDR_NUM, null, epe.OIPL_ORDR_NUM, epe.PL_ORDR_NUM),
481 epe.PL_ORDR_NUM,
482 decode(epe.PL_ORDR_NUM, null, null, epe.OIPL_ORDR_NUM);
483 --
484 begin
485 --
486 open c_instance
487 (c_per_in_ler_id => p_per_in_ler_id
488 );
489 fetch c_instance BULK COLLECT INTO
490 l_alws_dpnt_dsgn_flag_va
491 ,l_approval_status_cd_va
492 ,l_assignment_id_va
493 ,l_auto_enrt_flag_va
497 ,l_cmbn_ptip_id_va
494 ,l_bnft_prvdr_pool_id_va
495 ,l_business_group_id_va
496 ,l_cmbn_plip_id_va
498 ,l_cmbn_ptip_opt_id_va
499 ,l_comp_lvl_cd_va
500 ,l_crntly_enrd_flag_va
501 ,l_cryfwd_elig_dpnt_cd_va
502 ,l_ctfn_rqd_flag_va
503 ,l_dflt_flag_va
504 ,l_dpnt_cvg_strt_dt_cd_va
505 ,l_dpnt_cvg_strt_dt_rl_va
506 ,l_dpnt_dsgn_cd_va
507 ,l_elctbl_flag_va
508 ,l_elig_flag_va
509 ,l_elig_ovrid_dt_va
510 ,l_elig_ovrid_person_id_va
511 ,l_elig_per_elctbl_chc_id_va
512 ,l_enrt_cvg_strt_dt_va
513 ,l_enrt_cvg_strt_dt_cd_va
514 ,l_enrt_cvg_strt_dt_rl_va
515 ,l_erlst_deenrt_dt_va
516 ,l_fonm_cvg_strt_dt_va
517 ,l_inelig_rsn_cd_va
518 ,l_interim_epe_id_va
519 ,l_in_pndg_wkflow_flag_va
520 ,l_ler_chg_dpnt_cvg_cd_va
521 ,l_mgr_ovrid_dt_va
522 ,l_mgr_ovrid_person_id_va
523 ,l_mndtry_flag_va
524 ,l_must_enrl_anthr_pl_id_va
525 ,l_object_version_number_va
526 ,l_oiplip_id_va
527 ,l_oipl_id_va
528 ,l_oipl_ordr_num_va
529 ,l_per_in_ler_id_va
530 ,l_pgm_id_va
531 ,l_pil_elctbl_chc_popl_id_va
532 ,l_plip_id_va
533 ,l_plip_ordr_num_va
534 ,l_pl_id_va
535 ,l_pl_ordr_num_va
536 ,l_pl_typ_id_va
537 ,l_procg_end_dt_va
538 ,l_prtt_enrt_rslt_id_va
539 ,l_ptip_id_va
540 ,l_ptip_ordr_num_va
541 ,l_roll_crs_flag_va
542 ,l_spcl_rt_oipl_id_va
543 ,l_spcl_rt_pl_id_va
544 ,l_ws_mgr_id_va
545 ,l_yr_perd_id_va;
546 --
547 close c_instance;
548 --
549 if l_elig_per_elctbl_chc_id_va.count > 0 then
550 --
551 for i in l_elig_per_elctbl_chc_id_va.first..l_elig_per_elctbl_chc_id_va.last
552 loop
553 --
554 l_hv := mod(l_elig_per_elctbl_chc_id_va(i),ben_hash_utility.get_hash_key);
555 --
556 while g_epe_instance.exists(l_hv)
557 loop
558 --
559 l_hv := l_hv+g_hash_jump;
560 --
561 end loop;
562 --
563 g_epe_instance(l_hv).alws_dpnt_dsgn_flag := l_alws_dpnt_dsgn_flag_va(i);
564 g_epe_instance(l_hv).approval_status_cd := l_approval_status_cd_va(i);
565 g_epe_instance(l_hv).assignment_id := l_assignment_id_va(i);
566 g_epe_instance(l_hv).auto_enrt_flag := l_auto_enrt_flag_va(i);
567 g_epe_instance(l_hv).bnft_prvdr_pool_id := l_bnft_prvdr_pool_id_va(i);
568 g_epe_instance(l_hv).business_group_id := l_business_group_id_va(i);
569 g_epe_instance(l_hv).cmbn_plip_id := l_cmbn_plip_id_va(i);
570 g_epe_instance(l_hv).cmbn_ptip_id := l_cmbn_ptip_id_va(i);
571 g_epe_instance(l_hv).cmbn_ptip_opt_id := l_cmbn_ptip_opt_id_va(i);
572 g_epe_instance(l_hv).comp_lvl_cd := l_comp_lvl_cd_va(i);
573 g_epe_instance(l_hv).crntly_enrd_flag := l_crntly_enrd_flag_va(i);
574 g_epe_instance(l_hv).cryfwd_elig_dpnt_cd := l_cryfwd_elig_dpnt_cd_va(i);
575 g_epe_instance(l_hv).ctfn_rqd_flag := l_ctfn_rqd_flag_va(i);
576 g_epe_instance(l_hv).dflt_flag := l_dflt_flag_va(i);
577 g_epe_instance(l_hv).dpnt_cvg_strt_dt_cd := l_dpnt_cvg_strt_dt_cd_va(i);
578 g_epe_instance(l_hv).dpnt_cvg_strt_dt_rl := l_dpnt_cvg_strt_dt_rl_va(i);
579 g_epe_instance(l_hv).dpnt_dsgn_cd := l_dpnt_dsgn_cd_va(i);
580 g_epe_instance(l_hv).elctbl_flag := l_elctbl_flag_va(i);
581 g_epe_instance(l_hv).elig_flag := l_elig_flag_va(i);
582 g_epe_instance(l_hv).elig_ovrid_dt := l_elig_ovrid_dt_va(i);
583 g_epe_instance(l_hv).elig_ovrid_person_id := l_elig_ovrid_person_id_va(i);
584 g_epe_instance(l_hv).elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id_va(i);
585 g_epe_instance(l_hv).enrt_cvg_strt_dt := l_enrt_cvg_strt_dt_va(i);
586 g_epe_instance(l_hv).enrt_cvg_strt_dt_cd := l_enrt_cvg_strt_dt_cd_va(i);
587 g_epe_instance(l_hv).enrt_cvg_strt_dt_rl := l_enrt_cvg_strt_dt_rl_va(i);
588 g_epe_instance(l_hv).erlst_deenrt_dt := l_erlst_deenrt_dt_va(i);
589 g_epe_instance(l_hv).fonm_cvg_strt_dt := l_fonm_cvg_strt_dt_va(i);
590 g_epe_instance(l_hv).inelig_rsn_cd := l_inelig_rsn_cd_va(i);
591 g_epe_instance(l_hv).interim_elig_per_elctbl_chc_id := l_interim_epe_id_va(i);
595 g_epe_instance(l_hv).mgr_ovrid_person_id := l_mgr_ovrid_person_id_va(i);
592 g_epe_instance(l_hv).in_pndg_wkflow_flag := l_in_pndg_wkflow_flag_va(i);
593 g_epe_instance(l_hv).ler_chg_dpnt_cvg_cd := l_ler_chg_dpnt_cvg_cd_va(i);
594 g_epe_instance(l_hv).mgr_ovrid_dt := l_mgr_ovrid_dt_va(i);
596 g_epe_instance(l_hv).mndtry_flag := l_mndtry_flag_va(i);
597 g_epe_instance(l_hv).must_enrl_anthr_pl_id := l_must_enrl_anthr_pl_id_va(i);
598 g_epe_instance(l_hv).object_version_number := l_object_version_number_va(i);
599 g_epe_instance(l_hv).oiplip_id := l_oiplip_id_va(i);
600 g_epe_instance(l_hv).oipl_id := l_oipl_id_va(i);
601 g_epe_instance(l_hv).oipl_ordr_num := l_oipl_ordr_num_va(i);
602 g_epe_instance(l_hv).per_in_ler_id := l_per_in_ler_id_va(i);
603 g_epe_instance(l_hv).pgm_id := l_pgm_id_va(i);
604 g_epe_instance(l_hv).pil_elctbl_chc_popl_id := l_pil_elctbl_chc_popl_id_va(i);
605 g_epe_instance(l_hv).plip_id := l_plip_id_va(i);
606 g_epe_instance(l_hv).plip_ordr_num := l_plip_ordr_num_va(i);
607 g_epe_instance(l_hv).pl_id := l_pl_id_va(i);
608 g_epe_instance(l_hv).pl_ordr_num := l_pl_ordr_num_va(i);
609 g_epe_instance(l_hv).pl_typ_id := l_pl_typ_id_va(i);
610 g_epe_instance(l_hv).procg_end_dt := l_procg_end_dt_va(i);
611 g_epe_instance(l_hv).prtt_enrt_rslt_id := l_prtt_enrt_rslt_id_va(i);
612 g_epe_instance(l_hv).ptip_id := l_ptip_id_va(i);
613 g_epe_instance(l_hv).ptip_ordr_num := l_ptip_ordr_num_va(i);
614 g_epe_instance(l_hv).roll_crs_flag := l_roll_crs_flag_va(i);
615 g_epe_instance(l_hv).spcl_rt_oipl_id := l_spcl_rt_oipl_id_va(i);
616 g_epe_instance(l_hv).spcl_rt_pl_id := l_spcl_rt_pl_id_va(i);
617 g_epe_instance(l_hv).ws_mgr_id := l_ws_mgr_id_va(i);
618 g_epe_instance(l_hv).yr_perd_id := l_yr_perd_id_va(i);
619 --
620 end loop;
621 --
622 end if;
623 --
624 -- Check for no rows found
625 --
626 if l_hv is null then
627 --
628 g_epe_instance.delete;
629 g_epe_current.per_in_ler_id := null;
630 --
631 else
632 --
633 g_epe_current.per_in_ler_id := p_per_in_ler_id;
634 --
635 end if;
636 --
637 end write_EPE_cache;
638 --
639 procedure EPE_GetEPEDets
640 (p_elig_per_elctbl_chc_id in number
641 ,p_per_in_ler_id in number
642 ,p_inst_row in out NOCOPY g_pilepe_inst_row
643 )
644 is
645 --
646 l_proc varchar2(72) := 'EPE_GetEPEDets';
647 --
648 l_hv pls_integer;
649 l_reset g_pilepe_inst_row;
650 --
651 begin
652 --
653 -- Check for already cached or a change in current PIL ID
654 --
655 if nvl(g_epe_current.per_in_ler_id,-9999) <> p_per_in_ler_id
656 or g_epe_cached < 2
657 then
658 --
659 -- When PIL changes then flush current cache
660 --
661 if nvl(g_epe_current.per_in_ler_id,-9999) <> p_per_in_ler_id
662 then
663 --
664 g_epe_instance.delete;
665 g_epe_cached := 1;
666 --
667 end if;
668 --
669 -- Write the cache
670 --
671 write_EPE_cache
672 (p_per_in_ler_id => p_per_in_ler_id
673 );
674 --
675 if g_epe_cached = 1
676 then
677 --
678 g_epe_cached := 2;
679 --
680 end if;
681 --
682 end if;
683 --
684 -- Get the instance details
685 --
686 l_hv := mod(p_elig_per_elctbl_chc_id,ben_hash_utility.get_hash_key);
687 --
688 if g_epe_instance(l_hv).elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
689 then
690 -- Matched row
691 null;
692 else
693 --
694 -- Loop through the hash using the jump routine to check further
695 -- indexes if none exists at current index the NO_DATA_FOUND expection
696 -- will fire
697 --
698 l_hv := l_hv+g_hash_jump;
699 while g_epe_instance(l_hv).elig_per_elctbl_chc_id <> p_elig_per_elctbl_chc_id
700 loop
701 --
702 l_hv := l_hv+g_hash_jump;
703 --
704 end loop;
705 --
706 end if;
707 --
708 p_inst_row := g_epe_instance(l_hv);
709 --
710 exception
711 --
712 when no_data_found then
713 --
714 p_inst_row := l_reset;
715 --
716 end EPE_GetEPEDets;
717 --
718 procedure init_context_pileperow
719 is
720
721 l_currepe_row g_pilepe_inst_row;
722
723 begin
724 --
725 ben_reinstate_epe_cache.g_currepe_row := l_currepe_row;
726 --
727 end init_context_pileperow;
728 --
729 procedure get_pilcobjepe_dets
730 (p_per_in_ler_id in number
731 ,p_pgm_id in number
732 ,p_pl_id in number
733 ,p_oipl_id in number
734 --
735 ,p_inst_row in out NOCOPY g_pilepe_inst_row
736 )
737 is
738 --
739 l_proc varchar2(72) := 'get_pilcobjepe_dets';
740 --
741 l_inst_row g_pilepe_inst_row;
742 l_elig_per_elctbl_chc_id number ;
743 --
744 CURSOR c_choice_exists_for_option
745 (c_per_in_ler_id number
746 ,c_pgm_id number
747 ,c_oipl_id number
751 FROM ben_elig_per_elctbl_chc epe
748 )
749 is
750 SELECT epe.elig_per_elctbl_chc_id
752 WHERE epe.oipl_id = c_oipl_id
753 AND epe.pgm_id = c_pgm_id
754 AND epe.per_in_ler_id = c_per_in_ler_id;
755 --
756 CURSOR c_chc_exists_for_plnip_option
757 (c_per_in_ler_id number
758 ,c_oipl_id number
759 )
760 is
761 SELECT epe.elig_per_elctbl_chc_id
762 FROM ben_elig_per_elctbl_chc epe
763 WHERE epe.oipl_id = c_oipl_id
764 AND epe.pgm_id IS NULL
765 AND epe.per_in_ler_id = c_per_in_ler_id;
766 --
767 CURSOR c_choice_exists_for_plan
768 (c_per_in_ler_id number
769 ,c_pgm_id number
770 ,c_pl_id number
771 )
772 is
773 SELECT epe.elig_per_elctbl_chc_id
774 FROM ben_elig_per_elctbl_chc epe
775 WHERE epe.pl_id = c_pl_id
776 AND epe.oipl_id IS NULL
777 AND epe.pgm_id = c_pgm_id
778 AND epe.per_in_ler_id = c_per_in_ler_id;
779 --
780 CURSOR c_choice_exists_for_plnip
781 (c_per_in_ler_id number
782 ,c_pl_id number
783 )
784 is
785 SELECT epe.elig_per_elctbl_chc_id
786 FROM ben_elig_per_elctbl_chc epe
787 WHERE epe.pl_id = c_pl_id
788 AND epe.oipl_id IS NULL
789 AND epe.pgm_id IS NULL
790 AND epe.per_in_ler_id = c_per_in_ler_id;
791 --
792 begin
793 --
794 if p_oipl_id is null
795 then
796 --
797 if p_pgm_id is not null
798 then
799 --
800 OPEN c_choice_exists_for_plan
801 (c_per_in_ler_id => p_per_in_ler_id
802 ,c_pgm_id => p_pgm_id
803 ,c_pl_id => p_pl_id
804 );
805 FETCH c_choice_exists_for_plan INTO l_elig_per_elctbl_chc_id;
806 CLOSE c_choice_exists_for_plan;
807 --
808 else
809 --
810 OPEN c_choice_exists_for_plnip
811 (c_per_in_ler_id => p_per_in_ler_id
812 ,c_pl_id => p_pl_id
813 );
814 FETCH c_choice_exists_for_plnip INTO l_elig_per_elctbl_chc_id;
815 CLOSE c_choice_exists_for_plnip;
816 --
817 end if;
818 --
819 else
820 --
821 if p_pgm_id is not null
822 then
823 --
824 OPEN c_choice_exists_for_option
825 (c_per_in_ler_id => p_per_in_ler_id
826 ,c_pgm_id => p_pgm_id
827 ,c_oipl_id => p_oipl_id
828 );
829 FETCH c_choice_exists_for_option INTO l_elig_per_elctbl_chc_id;
830 CLOSE c_choice_exists_for_option;
831 --
832 else
833 --
834 OPEN c_chc_exists_for_plnip_option
835 (c_per_in_ler_id => p_per_in_ler_id
836 ,c_oipl_id => p_oipl_id
837 );
838 FETCH c_chc_exists_for_plnip_option INTO l_elig_per_elctbl_chc_id;
839 CLOSE c_chc_exists_for_plnip_option;
840 --
841 end if;
842 --
843 end if;
844 --
845 if l_elig_per_elctbl_chc_id is not null then
846 --
847 EPE_GetEPEDets
848 (p_elig_per_elctbl_chc_id =>l_elig_per_elctbl_chc_id
849 ,p_per_in_ler_id =>p_per_in_ler_id
850 ,p_inst_row =>l_inst_row
851 );
852 --
853 end if;
854 --
855 p_inst_row := l_inst_row;
856 --
857 end get_pilcobjepe_dets;
858 --
859 procedure init_context_cobj_pileperow
860 is
861
862 l_currepe_row g_pilepe_inst_row;
863
864 begin
865 --
866 ben_reinstate_epe_cache.g_currcobjepe_row := l_currepe_row;
867 --
868 end init_context_cobj_pileperow;
869 --
870 procedure clear_down_cache
871 is
872
873 begin
874 --
875 g_pilepe_instance.delete;
876 g_pilepe_cached := 1;
877 --
878 g_epe_instance.delete;
879 g_epe_cached := 1;
880 g_epe_current.per_in_ler_id := null;
881 --
882 init_context_pileperow;
883 --
884 end clear_down_cache;
885 --
886 end ben_reinstate_epe_cache;