[Home] [Help]
PACKAGE BODY: APPS.BEN_RTP_CACHE
Source
1 package body ben_rtp_cache as
2 /* $Header: benrtpch.pkb 120.0 2005/12/01 17:09:41 kmahendr noship $ */
3 --
4 g_package varchar2(50) := 'ben_rtp_cache.';
5 --
6 procedure write_abravr_odcache
7 (p_effective_date in date
8 ,p_acty_base_rt_id in number default hr_api.g_number
9 ,p_cvg_amt_calc_mthd_id in number default hr_api.g_number
10 ,p_actl_prem_id in number default hr_api.g_number
11 ,p_hv out nocopy pls_integer
12 )
13 is
14 --
15 l_proc varchar2(72) := 'write_abravr_odcache';
16 --
17 l_copcep_odlookup_rec ben_cache.g_cache_lookup;
18 --
19 l_hv pls_integer;
20 l_not_hash_found boolean;
21 l_torrwnum pls_integer;
22 l_starttorele_num pls_integer;
23 --
24 cursor c_profile_cvg
25 (c_cvg_amt_calc_mthd_id in number,
26 c_effective_date in date
27 )
28 is
29 select vrp.vrbl_rt_prfl_id,
30 vrp.rt_hrly_slrd_flag,
31 vrp.rt_pstl_cd_flag,
32 vrp.rt_lbr_mmbr_flag,
33 vrp.rt_lgl_enty_flag,
34 vrp.rt_benfts_grp_flag,
35 vrp.rt_wk_loc_flag,
36 vrp.rt_brgng_unit_flag,
37 vrp.rt_age_flag,
38 vrp.rt_los_flag,
39 vrp.rt_per_typ_flag,
40 vrp.rt_fl_tm_pt_tm_flag,
41 vrp.rt_ee_stat_flag,
42 vrp.rt_grd_flag,
43 vrp.rt_pct_fl_tm_flag,
44 vrp.rt_asnt_set_flag,
45 vrp.rt_hrs_wkd_flag,
46 vrp.rt_comp_lvl_flag,
47 vrp.rt_org_unit_flag,
48 vrp.rt_loa_rsn_flag,
49 vrp.rt_pyrl_flag,
50 vrp.rt_schedd_hrs_flag,
51 vrp.rt_py_bss_flag,
52 vrp.rt_prfl_rl_flag,
53 vrp.rt_cmbn_age_los_flag,
54 vrp.rt_prtt_pl_flag,
55 vrp.rt_svc_area_flag,
56 vrp.rt_ppl_grp_flag,
57 vrp.rt_dsbld_flag,
58 vrp.rt_hlth_cvg_flag,
59 vrp.rt_poe_flag,
60 vrp.rt_ttl_cvg_vol_flag,
61 vrp.rt_ttl_prtt_flag,
62 vrp.rt_gndr_flag,
63 vrp.rt_tbco_use_flag ,
64 vrp.rt_cntng_prtn_prfl_flag ,
65 vrp.rt_cbr_quald_bnf_flag,
66 vrp.rt_optd_mdcr_flag,
67 vrp.rt_lvg_rsn_flag ,
68 vrp.rt_pstn_flag ,
69 vrp.rt_comptncy_flag ,
70 vrp.rt_job_flag ,
71 vrp.rt_qual_titl_flag ,
72 vrp.rt_dpnt_cvrd_pl_flag,
73 vrp.rt_dpnt_cvrd_plip_flag,
74 vrp.rt_dpnt_cvrd_ptip_flag,
75 vrp.rt_dpnt_cvrd_pgm_flag,
76 vrp.rt_enrld_oipl_flag,
77 vrp.rt_enrld_pl_flag,
78 vrp.rt_enrld_plip_flag,
79 vrp.rt_enrld_ptip_flag,
80 vrp.rt_enrld_pgm_flag,
81 vrp.rt_prtt_anthr_pl_flag,
82 vrp.rt_othr_ptip_flag,
83 vrp.rt_no_othr_cvg_flag,
84 vrp.rt_dpnt_othr_ptip_flag,
85 vrp.rt_qua_in_gr_flag,
86 vrp.rt_perf_rtng_flag,
87 vrp.asmt_to_use_cd,
88 bvr.ordr_num,
89 vrp.rt_elig_prfl_flag
90 from ben_vrbl_rt_prfl_f vrp,
91 ben_bnft_vrbl_rt_f bvr
92 where vrp.vrbl_rt_prfl_stat_cd = 'A'
93 and c_effective_date
94 between vrp.effective_start_date
95 and vrp.effective_end_date
96 and bvr.cvg_amt_calc_mthd_id = c_cvg_amt_calc_mthd_id
97 and vrp.vrbl_rt_prfl_id = bvr.vrbl_rt_prfl_id
98 and c_effective_date
99 between bvr.effective_start_date
100 and bvr.effective_end_date
101 order by bvr.ordr_num;
102 --
103 cursor c_profile_abr
104 (c_acty_base_rt_id in number,
105 c_effective_date in date
106 )
107 is
108 select vrp.vrbl_rt_prfl_id,
109 vrp.rt_hrly_slrd_flag,
110 vrp.rt_pstl_cd_flag,
111 vrp.rt_lbr_mmbr_flag,
112 vrp.rt_lgl_enty_flag,
113 vrp.rt_benfts_grp_flag,
114 vrp.rt_wk_loc_flag,
115 vrp.rt_brgng_unit_flag,
116 vrp.rt_age_flag,
117 vrp.rt_los_flag,
118 vrp.rt_per_typ_flag,
119 vrp.rt_fl_tm_pt_tm_flag,
120 vrp.rt_ee_stat_flag,
121 vrp.rt_grd_flag,
122 vrp.rt_pct_fl_tm_flag,
123 vrp.rt_asnt_set_flag,
124 vrp.rt_hrs_wkd_flag,
125 vrp.rt_comp_lvl_flag,
126 vrp.rt_org_unit_flag,
127 vrp.rt_loa_rsn_flag,
128 vrp.rt_pyrl_flag,
129 vrp.rt_schedd_hrs_flag,
130 vrp.rt_py_bss_flag,
131 vrp.rt_prfl_rl_flag,
132 vrp.rt_cmbn_age_los_flag,
133 vrp.rt_prtt_pl_flag,
134 vrp.rt_svc_area_flag,
135 vrp.rt_ppl_grp_flag,
136 vrp.rt_dsbld_flag,
137 vrp.rt_hlth_cvg_flag,
138 vrp.rt_poe_flag,
139 vrp.rt_ttl_cvg_vol_flag,
140 vrp.rt_ttl_prtt_flag,
141 vrp.rt_gndr_flag,
142 vrp.rt_tbco_use_flag,
143 vrp.rt_cntng_prtn_prfl_flag ,
144 vrp.rt_cbr_quald_bnf_flag,
145 vrp.rt_optd_mdcr_flag,
146 vrp.rt_lvg_rsn_flag ,
147 vrp.rt_pstn_flag ,
148 vrp.rt_comptncy_flag ,
149 vrp.rt_job_flag ,
150 vrp.rt_qual_titl_flag ,
151 vrp.rt_dpnt_cvrd_pl_flag,
152 vrp.rt_dpnt_cvrd_plip_flag,
153 vrp.rt_dpnt_cvrd_ptip_flag,
154 vrp.rt_dpnt_cvrd_pgm_flag,
155 vrp.rt_enrld_oipl_flag,
156 vrp.rt_enrld_pl_flag,
157 vrp.rt_enrld_plip_flag,
158 vrp.rt_enrld_ptip_flag,
159 vrp.rt_enrld_pgm_flag,
160 vrp.rt_prtt_anthr_pl_flag,
161 vrp.rt_othr_ptip_flag,
162 vrp.rt_no_othr_cvg_flag,
163 vrp.rt_dpnt_othr_ptip_flag,
164 vrp.rt_qua_in_gr_flag,
165 vrp.rt_perf_rtng_flag,
166 vrp.asmt_to_use_cd,
167 avr.ordr_num,
168 vrp.rt_elig_prfl_flag
169 from ben_acty_vrbl_rt_f avr,
170 ben_vrbl_rt_prfl_f vrp
171 where vrp.vrbl_rt_prfl_stat_cd = 'A'
172 and c_effective_date
173 between vrp.effective_start_date
174 and vrp.effective_end_date
175 and avr.acty_base_rt_id = c_acty_base_rt_id
176 and vrp.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
177 and c_effective_date
178 between avr.effective_start_date
179 and avr.effective_end_date
180 order by avr.ordr_num;
181 --
182 l_instance c_profile_abr%rowtype;
183
184 cursor c_profile_apr
185 (c_actl_prem_id in number,
186 c_effective_date in date
187 )
188 is
189 select vrp.vrbl_rt_prfl_id,
190 vrp.rt_hrly_slrd_flag,
191 vrp.rt_pstl_cd_flag,
192 vrp.rt_lbr_mmbr_flag,
193 vrp.rt_lgl_enty_flag,
194 vrp.rt_benfts_grp_flag,
195 vrp.rt_wk_loc_flag,
196 vrp.rt_brgng_unit_flag,
197 vrp.rt_age_flag,
198 vrp.rt_los_flag,
199 vrp.rt_per_typ_flag,
200 vrp.rt_fl_tm_pt_tm_flag,
201 vrp.rt_ee_stat_flag,
202 vrp.rt_grd_flag,
203 vrp.rt_pct_fl_tm_flag,
204 vrp.rt_asnt_set_flag,
205 vrp.rt_hrs_wkd_flag,
206 vrp.rt_comp_lvl_flag,
207 vrp.rt_org_unit_flag,
208 vrp.rt_loa_rsn_flag,
209 vrp.rt_pyrl_flag,
210 vrp.rt_schedd_hrs_flag,
211 vrp.rt_py_bss_flag,
212 vrp.rt_prfl_rl_flag,
213 vrp.rt_cmbn_age_los_flag,
214 vrp.rt_prtt_pl_flag,
215 vrp.rt_svc_area_flag,
216 vrp.rt_ppl_grp_flag,
217 vrp.rt_dsbld_flag,
218 vrp.rt_hlth_cvg_flag,
219 vrp.rt_poe_flag,
220 vrp.rt_ttl_cvg_vol_flag,
221 vrp.rt_ttl_prtt_flag,
222 vrp.rt_gndr_flag,
223 vrp.rt_tbco_use_flag,
224 vrp.rt_cntng_prtn_prfl_flag ,
225 vrp.rt_cbr_quald_bnf_flag,
226 vrp.rt_optd_mdcr_flag,
227 vrp.rt_lvg_rsn_flag ,
228 vrp.rt_pstn_flag ,
229 vrp.rt_comptncy_flag ,
230 vrp.rt_job_flag ,
231 vrp.rt_qual_titl_flag ,
232 vrp.rt_dpnt_cvrd_pl_flag,
233 vrp.rt_dpnt_cvrd_plip_flag,
234 vrp.rt_dpnt_cvrd_ptip_flag,
235 vrp.rt_dpnt_cvrd_pgm_flag,
236 vrp.rt_enrld_oipl_flag,
237 vrp.rt_enrld_pl_flag,
238 vrp.rt_enrld_plip_flag,
239 vrp.rt_enrld_ptip_flag,
240 vrp.rt_enrld_pgm_flag,
241 vrp.rt_prtt_anthr_pl_flag,
242 vrp.rt_othr_ptip_flag,
243 vrp.rt_no_othr_cvg_flag,
244 vrp.rt_dpnt_othr_ptip_flag,
245 vrp.rt_qua_in_gr_flag,
246 vrp.rt_perf_rtng_flag,
247 vrp.asmt_to_use_cd,
248 apr.ordr_num,
249 vrp.rt_elig_prfl_flag
250 from ben_actl_prem_vrbl_rt_f apr,
251 ben_vrbl_rt_prfl_f vrp
252 where vrp.vrbl_rt_prfl_stat_cd = 'A'
253 and c_effective_date
254 between vrp.effective_start_date
255 and vrp.effective_end_date
256 and apr.actl_prem_id = c_actl_prem_id
257 and vrp.vrbl_rt_prfl_id = apr.vrbl_rt_prfl_id
258 and c_effective_date
259 between apr.effective_start_date
260 and apr.effective_end_date
261 order by apr.ordr_num;
262 --
263 begin
264 --
265 hr_utility.set_location(' Entering '||l_proc,10);
266 hr_utility.set_location(' p_acty_base_rt_id '||p_acty_base_rt_id,10);
267 hr_utility.set_location(' p_cvg_amt_calc_mthd_id '||p_cvg_amt_calc_mthd_id,10);
268 hr_utility.set_location(' p_actl_prem_id '||p_actl_prem_id,10);
269 --
270 -- Get the instance details
271 --
272 l_hv := mod(nvl(p_acty_base_rt_id,1)+
273 nvl(p_cvg_amt_calc_mthd_id,2)+
274 nvl(p_actl_prem_id,3) ,g_hash_key);
275 --
276 -- Get a unique hash value
277 --
278 if g_copcep_odlookup.exists(l_hv) then
279 --
280 if nvl(g_copcep_odlookup(l_hv).id,-1) = nvl(p_acty_base_rt_id,-1)
281 and nvl(g_copcep_odlookup(l_hv).fk_id,-1) = nvl(p_cvg_amt_calc_mthd_id,-1)
282 and nvl(g_copcep_odlookup(l_hv).fk1_id,-1) = nvl(p_actl_prem_id,-1)
283 then
284 --
285 null;
286 --
287 else
288 --
289 l_not_hash_found := false;
290 --
291 -- Loop until un-allocated has value is derived
292 --
293 while not l_not_hash_found loop
294 --
295 l_hv := l_hv+g_hash_jump;
296 --
297 -- Check if the hash index exists, and compare the values
298 --
299 if g_copcep_odlookup.exists(l_hv) then
300 --
301 if nvl(g_copcep_odlookup(l_hv).id,-1) = nvl(p_acty_base_rt_id,-1)
302 and nvl(g_copcep_odlookup(l_hv).fk_id,-1) = nvl(p_cvg_amt_calc_mthd_id,-1)
303 and nvl(g_copcep_odlookup(l_hv).fk1_id,-1) = nvl(p_actl_prem_id,-1)
304 then
305 --
306 l_not_hash_found := true;
307 exit;
308 --
309 else
310 --
311 l_not_hash_found := false;
312 --
313 end if;
314 --
315 else
316 --
317 exit;
318 --
319 end if;
320 --
321 end loop;
322 --
323 end if;
324 --
325 end if;
326 --
327 g_copcep_odlookup(l_hv).id := p_acty_base_rt_id;
328 g_copcep_odlookup(l_hv).fk_id := p_cvg_amt_calc_mthd_id;
329 g_copcep_odlookup(l_hv).fk1_id := p_actl_prem_id;
330 --
331 hr_utility.set_location(' Dn Look '||l_proc,10);
332 --
333 l_starttorele_num := nvl(g_copcep_nxelenum,1);
334 l_torrwnum := l_starttorele_num;
335 --
336 hr_utility.set_location(' Bef inst loop '||l_proc,10);
337 --
338 if p_acty_base_rt_id is not null then
339 --
340 open c_profile_abr
341 (c_acty_base_rt_id => p_acty_base_rt_id
342 ,c_effective_date => p_effective_date
343 );
344 --
345 elsif p_cvg_amt_calc_mthd_id is not null then
346 --
347 open c_profile_cvg
348 (c_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
349 ,c_effective_date => p_effective_date
350 );
351 --
352 elsif p_actl_prem_id is not null then
353 --
354 open c_profile_apr
355 (c_actl_prem_id => p_actl_prem_id
356 ,c_effective_date => p_effective_date
357 );
358 --
359 end if;
360 --
361 loop
362 --
363 if p_acty_base_rt_id is not null then
364 --
365 fetch c_profile_abr into l_instance;
366 exit when c_profile_abr%NOTFOUND;
367 --
368 elsif p_cvg_amt_calc_mthd_id is not null then
369 --
370 fetch c_profile_cvg into l_instance;
371 exit when c_profile_cvg%NOTFOUND;
372 --
373 elsif p_actl_prem_id is not null then
374 --
375 fetch c_profile_apr into l_instance;
376 exit when c_profile_apr%NOTFOUND;
377 --
378 end if;
379 --
380 hr_utility.set_location(' Assign inst '||l_proc,10);
381 --
382 g_copcep_odinst.extend(1);
383 g_copcep_odinst(l_torrwnum).acty_base_rt_id := p_acty_base_rt_id;
384 g_copcep_odinst(l_torrwnum).cvg_amt_calc_mthd_id := p_cvg_amt_calc_mthd_id;
385 g_copcep_odinst(l_torrwnum).actl_prem_id := p_actl_prem_id;
386 g_copcep_odinst(l_torrwnum).vrbl_rt_prfl_id := l_instance.vrbl_rt_prfl_id;
387 g_copcep_odinst(l_torrwnum).rt_hrly_slrd_flag := l_instance.rt_hrly_slrd_flag;
388 g_copcep_odinst(l_torrwnum).rt_pstl_cd_flag := l_instance.rt_pstl_cd_flag;
389 g_copcep_odinst(l_torrwnum).rt_lbr_mmbr_flag := l_instance.rt_lbr_mmbr_flag;
390 g_copcep_odinst(l_torrwnum).rt_lgl_enty_flag := l_instance.rt_lgl_enty_flag;
391 g_copcep_odinst(l_torrwnum).rt_benfts_grp_flag := l_instance.rt_benfts_grp_flag;
392 g_copcep_odinst(l_torrwnum).rt_wk_loc_flag := l_instance.rt_wk_loc_flag;
393 g_copcep_odinst(l_torrwnum).rt_brgng_unit_flag := l_instance.rt_brgng_unit_flag;
394 g_copcep_odinst(l_torrwnum).rt_age_flag := l_instance.rt_age_flag;
395 g_copcep_odinst(l_torrwnum).rt_los_flag := l_instance.rt_los_flag;
396 g_copcep_odinst(l_torrwnum).rt_per_typ_flag := l_instance.rt_per_typ_flag;
397 g_copcep_odinst(l_torrwnum).rt_fl_tm_pt_tm_flag := l_instance.rt_fl_tm_pt_tm_flag;
398 g_copcep_odinst(l_torrwnum).rt_ee_stat_flag := l_instance.rt_ee_stat_flag;
399 g_copcep_odinst(l_torrwnum).rt_grd_flag := l_instance.rt_grd_flag;
400 g_copcep_odinst(l_torrwnum).rt_pct_fl_tm_flag := l_instance.rt_pct_fl_tm_flag;
401 g_copcep_odinst(l_torrwnum).rt_asnt_set_flag := l_instance.rt_asnt_set_flag;
402 g_copcep_odinst(l_torrwnum).rt_hrs_wkd_flag := l_instance.rt_hrs_wkd_flag;
403 g_copcep_odinst(l_torrwnum).rt_comp_lvl_flag := l_instance.rt_comp_lvl_flag;
404 g_copcep_odinst(l_torrwnum).rt_org_unit_flag := l_instance.rt_org_unit_flag;
405 g_copcep_odinst(l_torrwnum).rt_loa_rsn_flag := l_instance.rt_loa_rsn_flag;
406 g_copcep_odinst(l_torrwnum).rt_pyrl_flag := l_instance.rt_pyrl_flag;
407 g_copcep_odinst(l_torrwnum).rt_schedd_hrs_flag := l_instance.rt_schedd_hrs_flag;
408 g_copcep_odinst(l_torrwnum).rt_py_bss_flag := l_instance.rt_py_bss_flag;
409 g_copcep_odinst(l_torrwnum).rt_prfl_rl_flag := l_instance.rt_prfl_rl_flag;
410 g_copcep_odinst(l_torrwnum).rt_cmbn_age_los_flag := l_instance.rt_cmbn_age_los_flag;
411 g_copcep_odinst(l_torrwnum).rt_prtt_pl_flag := l_instance.rt_prtt_pl_flag;
412 g_copcep_odinst(l_torrwnum).rt_svc_area_flag := l_instance.rt_svc_area_flag;
413 g_copcep_odinst(l_torrwnum).rt_ppl_grp_flag := l_instance.rt_ppl_grp_flag;
414 g_copcep_odinst(l_torrwnum).rt_dsbld_flag := l_instance.rt_dsbld_flag;
415 g_copcep_odinst(l_torrwnum).rt_hlth_cvg_flag := l_instance.rt_hlth_cvg_flag;
416 g_copcep_odinst(l_torrwnum).rt_poe_flag := l_instance.rt_poe_flag;
417 g_copcep_odinst(l_torrwnum).rt_ttl_cvg_vol_flag := l_instance.rt_ttl_cvg_vol_flag;
418 g_copcep_odinst(l_torrwnum).rt_ttl_prtt_flag := l_instance.rt_ttl_prtt_flag;
419 g_copcep_odinst(l_torrwnum).rt_gndr_flag := l_instance.rt_gndr_flag;
420 g_copcep_odinst(l_torrwnum).rt_tbco_use_flag := l_instance.rt_tbco_use_flag;
421 g_copcep_odinst(l_torrwnum).rt_cntng_prtn_prfl_flag := l_instance.rt_cntng_prtn_prfl_flag;
422 g_copcep_odinst(l_torrwnum).rt_cbr_quald_bnf_flag := l_instance.rt_cbr_quald_bnf_flag;
423 g_copcep_odinst(l_torrwnum).rt_optd_mdcr_flag := l_instance.rt_optd_mdcr_flag;
424 g_copcep_odinst(l_torrwnum).rt_lvg_rsn_flag := l_instance.rt_lvg_rsn_flag;
425 g_copcep_odinst(l_torrwnum).rt_pstn_flag := l_instance.rt_pstn_flag;
426 g_copcep_odinst(l_torrwnum).rt_comptncy_flag := l_instance.rt_comptncy_flag;
427 g_copcep_odinst(l_torrwnum).rt_job_flag := l_instance.rt_job_flag;
428 g_copcep_odinst(l_torrwnum).rt_qual_titl_flag := l_instance.rt_qual_titl_flag;
429 g_copcep_odinst(l_torrwnum).rt_dpnt_cvrd_pl_flag := l_instance.rt_dpnt_cvrd_pl_flag;
430 g_copcep_odinst(l_torrwnum).rt_dpnt_cvrd_plip_flag := l_instance.rt_dpnt_cvrd_plip_flag;
431 g_copcep_odinst(l_torrwnum).rt_dpnt_cvrd_ptip_flag := l_instance.rt_dpnt_cvrd_ptip_flag;
432 g_copcep_odinst(l_torrwnum).rt_dpnt_cvrd_pgm_flag := l_instance.rt_dpnt_cvrd_pgm_flag;
433 g_copcep_odinst(l_torrwnum).rt_enrld_oipl_flag := l_instance.rt_enrld_oipl_flag;
434 g_copcep_odinst(l_torrwnum).rt_enrld_pl_flag := l_instance.rt_enrld_pl_flag;
435 g_copcep_odinst(l_torrwnum).rt_enrld_plip_flag := l_instance.rt_enrld_plip_flag;
436 g_copcep_odinst(l_torrwnum).rt_enrld_ptip_flag := l_instance.rt_enrld_ptip_flag;
437 g_copcep_odinst(l_torrwnum).rt_enrld_pgm_flag := l_instance.rt_enrld_pgm_flag;
438 g_copcep_odinst(l_torrwnum).rt_prtt_anthr_pl_flag := l_instance.rt_prtt_anthr_pl_flag;
439 g_copcep_odinst(l_torrwnum).rt_othr_ptip_flag := l_instance.rt_othr_ptip_flag;
440 g_copcep_odinst(l_torrwnum).rt_no_othr_cvg_flag := l_instance.rt_no_othr_cvg_flag;
441 g_copcep_odinst(l_torrwnum).rt_dpnt_othr_ptip_flag := l_instance.rt_dpnt_othr_ptip_flag;
442 g_copcep_odinst(l_torrwnum).rt_qua_in_gr_flag := l_instance.rt_qua_in_gr_flag;
443 g_copcep_odinst(l_torrwnum).rt_perf_rtng_flag := l_instance.rt_perf_rtng_flag;
444 g_copcep_odinst(l_torrwnum).asmt_to_use_cd := l_instance.asmt_to_use_cd;
445 g_copcep_odinst(l_torrwnum).ordr_num := l_instance.ordr_num;
446 g_copcep_odinst(l_torrwnum).rt_elig_prfl_flag := l_instance.rt_elig_prfl_flag;
447 hr_utility.set_location(' Dn Assign inst '||l_proc,10);
448 --
449 l_torrwnum := l_torrwnum+1;
450 --
451 end loop;
452 --
453 if p_acty_base_rt_id is not null then
454 --
455 close c_profile_abr;
456 --
457 elsif p_cvg_amt_calc_mthd_id is not null then
458 --
459 close c_profile_cvg;
460 --
461 elsif p_actl_prem_id is not null then
462 --
463 close c_profile_apr;
464 --
465 end if;
466 --
467 -- Check if any rows were found
468 --
469 if l_torrwnum > nvl(g_copcep_nxelenum,1)
470 then
471 --
472 g_copcep_odlookup(l_hv).starttorele_num := l_starttorele_num;
473 g_copcep_odlookup(l_hv).endtorele_num := l_torrwnum-1;
474 g_copcep_nxelenum := l_torrwnum;
475 --
476 p_hv := l_hv;
477 --
478 else
479 --
480 -- Delete and free PGA with assignment
481 --
482 g_copcep_odlookup.delete(l_hv);
483 g_copcep_odlookup(l_hv) := l_copcep_odlookup_rec;
484 --
485 p_hv := null;
486 --
487 end if;
488 --
489 hr_utility.set_location(' Leaving '||l_proc,10);
490 end write_abravr_odcache;
491 --
492 procedure clear_down_cache
493 is
494 --
495 l_copcep_odlookup ben_cache.g_cache_lookup_table;
496 l_copcep_odinst g_cobcep_odcache := g_cobcep_odcache();
497 --
498 begin
499 --
500 -- On demand cache structures
501 --
502 g_copcep_odlookup := l_copcep_odlookup;
503 g_copcep_odinst := l_copcep_odinst;
504 g_copcep_odcached := 0;
505 g_copcep_nxelenum := null;
506 --
507 -- Grab back memory
508 --
509 begin
510 --
511 dbms_session.free_unused_user_memory;
512 --
513 end;
514 --
515 end clear_down_cache;
516 --
517 procedure abravr_odgetdets
518 (p_effective_date in date
519 ,p_acty_base_rt_id in number default hr_api.g_number
520 ,p_cvg_amt_calc_mthd_id in number default hr_api.g_number
521 ,p_actl_prem_id in number default hr_api.g_number
522 ,p_inst_set in out nocopy g_cobcep_odcache
523 )
524 is
525 l_proc varchar2(72) := 'abravr_odgetdets';
526 --
527 l_inst_set g_cobcep_odcache := g_cobcep_odcache();
528 --
529 l_hv pls_integer;
530 l_hash_found boolean;
531 l_insttorrw_num pls_integer;
532 l_torrwnum pls_integer;
533 --
534 l_clash_count pls_integer;
535 --
536 begin
537 --
538 hr_utility.set_location(' Entering '||l_proc,10);
539 --
540 if g_copcep_odcached = 0
541 then
542 --
543 -- Build the cache
544 --
545 clear_down_cache;
546 --
547 g_copcep_odcached := 1;
548 --
549 end if;
550 --
551 -- Get the instance details
552 --
553 l_hv := mod(nvl(p_acty_base_rt_id,1)+
554 nvl(p_cvg_amt_calc_mthd_id,2)+
555 nvl(p_actl_prem_id,3) ,g_hash_key);
556 --
557 -- Check if hashed value is already allocated
558 --
559 l_hash_found := false;
560 --
561 if g_copcep_odlookup.exists(l_hv) then
562 --
563 if nvl(g_copcep_odlookup(l_hv).id,-1) = nvl(p_acty_base_rt_id,-1)
564 and nvl(g_copcep_odlookup(l_hv).fk_id,-1) = nvl(p_cvg_amt_calc_mthd_id,-1)
565 and nvl(g_copcep_odlookup(l_hv).fk1_id,-1) = nvl(p_actl_prem_id,-1)
566 then
567 --
568 null;
569 --
570 else
571 --
572 l_hash_found := false;
573 --
574 -- Loop until un-allocated has value is derived
575 --
576 l_clash_count := 0;
577 --
578 while not l_hash_found loop
579 --
580 l_hv := l_hv+g_hash_jump;
581 --
582 if g_copcep_odlookup.exists(l_hv) then
583 --
584 -- Check if the hash index exists, and compare the values
585 --
586 if nvl(g_copcep_odlookup(l_hv).id,-1) = nvl(p_acty_base_rt_id,-1)
587 and nvl(g_copcep_odlookup(l_hv).fk_id,-1) = nvl(p_cvg_amt_calc_mthd_id,-1)
588 and nvl(g_copcep_odlookup(l_hv).fk1_id,-1) = nvl(p_actl_prem_id,-1)
589 then
590 --
591 l_hash_found := true;
592 exit;
593 --
594 else
595 --
596 l_clash_count := l_clash_count+1;
597 l_hash_found := false;
598 --
599 end if;
600 --
601 -- Check for high clash counts and defrag
602 --
603 if l_clash_count > 50
604 then
605 --
606 l_hv := null;
607 clear_down_cache;
608 exit;
609 --
610 end if;
611 --
612 else
613 --
614 l_hv := null;
615 exit;
616 --
617 end if;
618 --
619 end loop;
620 --
621 end if;
622 --
623 else
624 --
625 l_hv := null;
626 --
627 end if;
628
629 if l_hv is null
630 then
631 --
632 write_abravr_odcache
633 (p_effective_date => p_effective_date
634 ,p_acty_base_rt_id => p_acty_base_rt_id
635 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
636 ,p_actl_prem_id => p_actl_prem_id
637 ,p_hv => l_hv
638 );
639 --
640 end if;
641 --
642 if l_hv is not null then
643 --
644 l_torrwnum := 1;
645 --
646 hr_utility.set_location(' Get loop '||l_proc,10);
647 for l_insttorrw_num in g_copcep_odlookup(l_hv).starttorele_num ..
648 g_copcep_odlookup(l_hv).endtorele_num
649 loop
650 --
651 l_inst_set.extend(1);
652 l_inst_set(l_torrwnum) := g_copcep_odinst(l_insttorrw_num);
653 l_torrwnum := l_torrwnum+1;
654 --
655 end loop;
656 --
657 end if;
658 --
659 p_inst_set := l_inst_set;
660 --
661 hr_utility.set_location(' Leaving '||l_proc,10);
662 exception
663 --
664 when no_data_found then
665 --
666 p_inst_set := l_inst_set;
667 --
668 end abravr_odgetdets;
669 --
670 end ben_rtp_cache;