1 package body ben_pln_cache as
2 /* $Header: benplnch.pkb 120.1 2007/03/27 15:52:41 rtagarra noship $ */
3 --
4 g_package varchar2(50) := 'ben_pln_cache.';
5 --
6 procedure bgpcpp_getdets
7 (p_business_group_id in number
8 ,p_effective_date in date
9 ,p_mode in varchar2
10 ,p_pgm_id in number default null
11 ,p_pl_id in number default null
12 ,p_opt_id in number default null
13 ,p_rptg_grp_id in number default null
14 ,p_vrbl_rt_prfl_id in number default null
15 ,p_eligy_prfl_id in number default null
16 -- PB : 5422 :
17 -- ,p_popl_enrt_typ_cycl_id in number default null
18 ,p_asnd_lf_evt_dt in date default null
19 --
20 ,p_inst_set out nocopy ben_pln_cache.g_bgppln_cache
21 )
22 is
23 --
24 l_proc varchar2(72) := g_package||'bgpcpp_getdets';
25 --
26 l_instcolnm_set ben_cache.InstColNmType;
27 l_curparm_set ben_cache.CurParmType;
28 --
29 l_cppinst_set ben_pln_cache.g_bgpcpp_cache;
30 --
31 l_row_num pls_integer;
32 l_parhv pls_integer;
33 l_torrwnum pls_integer;
34 l_hv pls_integer;
35 l_not_hash_found boolean;
36 --
37 l_lkup_query long;
38 l_inst_query long;
39 l_coninst_query long;
40 --
41 cursor c_pln
42 (c_business_group_id number
43 ,c_effective_date date
44 ,c_mode varchar2
45 ,c_pgm_id number
46 ,c_pl_id number
47 ,c_opt_id number
48 ,c_rptg_grp_id number
49 ,c_vrbl_rt_prfl_id number
50 ,c_eligy_prfl_id number
51 -- PB : 5422 :
52 -- ,c_popl_enrt_typ_cycl_id number
53 )
54 is
55 select pln.pl_id,
56 pln.pl_typ_id,
57 ptp.opt_typ_cd,
58 pln.drvbl_fctr_prtn_elig_flag,
59 pln.drvbl_fctr_apls_rts_flag,
60 pln.trk_inelig_per_flag
61 from ben_ptip_f ctp,
62 ben_pl_f pln,
63 ben_pl_typ_f ptp,
64 ben_plip_f plp
65 -- ben_popl_yr_perd cpy,
66 -- ben_yr_perd yrp
67 where
68 /* Hint joins */
69 plp.pgm_id = c_pgm_id
70 and pln.pl_id = plp.pl_id
71 and c_effective_date
72 between pln.effective_start_date
73 and pln.effective_end_date
74 and pln.pl_typ_id = ptp.pl_typ_id
75 and c_effective_date
76 between ptp.effective_start_date
77 and ptp.effective_end_date
78 and (p_mode IN ('P','G','D') or
79 exists (select null
80 from ben_popl_yr_perd cpy,
81 ben_yr_perd yrp
82 where cpy.pl_id = pln.pl_id
83 and cpy.yr_perd_id = yrp.yr_perd_id
84 and c_effective_date
85 between yrp.start_date
86 and yrp.end_date))
87 and ctp.pgm_id = c_pgm_id
88 /* Histograms */
89 and plp.plip_stat_cd = 'A'
90 and pln.pl_stat_cd = 'A'
91 and plp.alws_unrstrctd_enrt_flag = decode(c_mode,
92 'U',
93 'Y',
94 'D',
95 'Y',
96 plp.alws_unrstrctd_enrt_flag)
97 and ctp.ptip_stat_cd = 'A'
98 /* Other joins */
99 and ctp.pl_typ_id = pln.pl_typ_id
100 and pln.pl_id = nvl(c_pl_id,pln.pl_id)
101 and c_effective_date
102 between plp.effective_start_date
103 and plp.effective_end_date
104 and c_effective_date
105 between ctp.effective_start_date
106 and ctp.effective_end_date
107 /* and c_effective_date
108 between yrp.start_date
109 and yrp.end_date
110 */
111 /* Make sure that plan being linked to covers all the options that may
112 or may not have been stated by the user. */
113 and (exists (select null
114 from ben_oipl_f cop
115 where cop.opt_id = c_opt_id
116 and cop.pl_id = pln.pl_id
117 and cop.business_group_id = pln.business_group_id
118 and cop.oipl_stat_cd = 'A'
119 and c_effective_date
120 between cop.effective_start_date
121 and cop.effective_end_date)
122 or c_opt_id is null)
123 /* Make sure that plan being linked to covers all the programs that may
124 or may not have been stated by the user. Also link in the benefits
125 reporting group. */
126 and (exists (select null
127 from ben_plip_f cpp,
128 ben_rptg_grp bnr,
129 ben_popl_rptg_grp rgr
130 where cpp.pgm_id = c_pgm_id
131 and cpp.pl_id = pln.pl_id
132 and cpp.plip_stat_cd = 'A'
133 and cpp.business_group_id = pln.business_group_id
134 and c_effective_date
135 between cpp.effective_start_date
136 and cpp.effective_end_date
137 and bnr.rptg_grp_id = c_rptg_grp_id
138 and bnr.business_group_id = pln.business_group_id
139 and rgr.rptg_grp_id = bnr.rptg_grp_id
140 and rgr.business_group_id = bnr.business_group_id
141 and rgr.pl_id = pln.pl_id)
142 or c_rptg_grp_id is null)
143 /* Make sure that plan being linked to is of the variable rate profile
144 that has been specified by the user. */
145 and (exists (select null
146 from ben_acty_base_rt_f abr,
147 ben_acty_vrbl_rt_f avr,
148 ben_vrbl_rt_prfl_f vpf
149 where abr.pl_id = pln.pl_id
150 and abr.business_group_id = pln.business_group_id
151 and c_effective_date
152 between abr.effective_start_date
153 and abr.effective_end_date
154 and avr.acty_base_rt_id = abr.acty_base_rt_id
155 and avr.business_group_id = abr.business_group_id
156 and c_effective_date
157 between avr.effective_start_date
158 and avr.effective_end_date
159 and vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
160 and vpf.business_group_id = avr.business_group_id
161 and vpf.vrbl_rt_prfl_id = c_vrbl_rt_prfl_id
162 and c_effective_date
163 between vpf.effective_start_date
164 and vpf.effective_end_date)
165 or c_vrbl_rt_prfl_id is null)
166 /* Make sure that plan being linked to is of the enrt perd of the
167 program that has been specified by the user. */
168 /* Bug#3694695 - removed the condition below as I believe this one is used
169 for defining coverage/rate codes for plip at life event level
170 and (exists (select null
171 from ben_enrt_perd_for_pl_f erp,
172 ben_enrt_perd enp,
173 ben_popl_enrt_typ_cycl_f pop
174 where erp.pl_id = pln.pl_id
175 and c_effective_date
176 between erp.effective_start_date
177 and erp.effective_end_date
178 and erp.enrt_perd_id = enp.enrt_perd_id
179 and enp.asnd_lf_evt_dt = p_asnd_lf_evt_dt
180 PB : 5422 : and enp.strt_dt = (select enp1.strt_dt
181 from ben_enrt_perd enp1
182 where enp1.enrt_perd_id =
183 c_popl_enrt_typ_cycl_id)
184 and enp.business_group_id =
185 erp.business_group_id
186 and pop.popl_enrt_typ_cycl_id=enp.popl_enrt_typ_cycl_id
187 and c_effective_date between
188 pop.effective_start_date and pop.effective_end_date
189 and pop.business_group_id=enp.business_group_id
190 and pop.pgm_id=c_pgm_id
191 )
192 or not exists
193 (select null
194 from ben_enrt_perd_for_pl_f erp,
195 ben_enrt_perd enp,
196 ben_popl_enrt_typ_cycl_f pop
197 where c_effective_date between
198 erp.effective_start_date and erp.effective_end_date
199 and erp.enrt_perd_id = enp.enrt_perd_id
200 and enp.asnd_lf_evt_dt = p_asnd_lf_evt_dt
201 /* PB : 5422 :
202 and enp.strt_dt = (select enp2.strt_dt
203 from ben_enrt_perd enp2
204 where enp2.enrt_perd_id =
205 c_popl_enrt_typ_cycl_id)
206 and enp.business_group_id = erp.business_group_id
207 and enp.business_group_id = pln.business_group_id
208 and pop.popl_enrt_typ_cycl_id=enp.popl_enrt_typ_cycl_id
209 and c_effective_date between
210 pop.effective_start_date and pop.effective_end_date
211 and pop.business_group_id=enp.business_group_id
212 and pop.pgm_id=c_pgm_id
213 )
214
215 -- PB : 5422 :
216 -- or c_popl_enrt_typ_cycl_id is null
217 or p_asnd_lf_evt_dt is null
218 or pln.invk_flx_cr_pl_flag ='Y'
219 or pln.imptd_incm_calc_cd = 'PRTT')
220 */
221 /* Make sure that plan being linked to is of the eligibility profile
222 that has been specified by the user. */
223 and (exists
224 (select null
225 from ben_prtn_elig_f epa2,
226 ben_prtn_elig_prfl_f cep,
227 ben_eligy_prfl_f elp
228 where epa2.pl_id = pln.pl_id
229 and epa2.business_group_id = pln.business_group_id
230 and c_effective_date
231 between epa2.effective_start_date
232 and epa2.effective_end_date
233 and cep.prtn_elig_id = epa2.prtn_elig_id
234 and cep.business_group_id = epa2.business_group_id
235 and c_effective_date
236 between cep.effective_start_date
237 and cep.effective_end_date
238 and elp.eligy_prfl_id = cep.eligy_prfl_id
239 and elp.business_group_id = cep.business_group_id
240 and elp.eligy_prfl_id = c_eligy_prfl_id
241 and c_effective_date
242 between elp.effective_start_date
243 and elp.effective_end_date)
244 or c_eligy_prfl_id is null)
245 order by ctp.ordr_num ,plp.ordr_num ;
246 --
247 cursor c_noparms
248 (c_pgm_id number
249 ,c_effective_date date
250 ,c_mode varchar2
251 )
252 is
253 select pln.pl_id,
254 pln.pl_typ_id,
255 ptp.opt_typ_cd,
256 pln.drvbl_fctr_prtn_elig_flag,
257 pln.drvbl_fctr_apls_rts_flag,
258 pln.trk_inelig_per_flag
259 from ben_ptip_f ctp,
260 ben_pl_f pln,
261 ben_pl_typ_f ptp,
262 ben_plip_f plp
263 -- ben_popl_yr_perd cpy,
264 -- ben_yr_perd yrp
265 where
266 /* Hint joins */
267 plp.pgm_id = c_pgm_id
268 and pln.pl_id = plp.pl_id
269 and c_effective_date
270 between pln.effective_start_date
271 and pln.effective_end_date
272 and pln.pl_typ_id = ptp.pl_typ_id
273 and c_effective_date
274 between ptp.effective_start_date
275 and ptp.effective_end_date
276 and ctp.pgm_id = c_pgm_id
277
278 and (p_mode in ('G','D') or
279 exists (select null
280 from ben_popl_yr_perd cpy,
281 ben_yr_perd yrp
282 where cpy.pl_id = pln.pl_id
283 and cpy.yr_perd_id = yrp.yr_perd_id
284 and c_effective_date
285 between yrp.start_date
286 and yrp.end_date))
287
288 /* Histograms */
289 and plp.plip_stat_cd = 'A'
290 and pln.pl_stat_cd = 'A'
291 and plp.alws_unrstrctd_enrt_flag = decode(c_mode,
292 'U',
293 'Y',
294 'D',
295 'Y',
296 plp.alws_unrstrctd_enrt_flag)
297 and ctp.ptip_stat_cd = 'A'
298 /* Other joins */
299 and ctp.pl_typ_id = pln.pl_typ_id
300 and c_effective_date
301 between plp.effective_start_date
302 and plp.effective_end_date
303 and c_effective_date
304 between ctp.effective_start_date
305 and ctp.effective_end_date
306 order by ctp.ordr_num ,plp.ordr_num;
307 --
308 begin
309 --
310 -- hr_utility.set_location (l_proc||' Entering ',10);
311 --
312 l_row_num := 0;
313 --
314 if p_pl_id is null
315 and p_opt_id is null
316 and p_rptg_grp_id is null
317 and p_vrbl_rt_prfl_id is null
318 and p_eligy_prfl_id is null
319 -- PB : 5422 :
320 -- and p_popl_enrt_typ_cycl_id is null
321 and p_asnd_lf_evt_dt is null
322 then
323 --
324 for obj in c_noparms
325 (c_pgm_id => p_pgm_id
326 ,c_effective_date => p_effective_date
327 ,c_mode => p_mode
328 )
329 loop
330 --
331 p_inst_set(l_row_num) := obj;
332 l_row_num := l_row_num+1;
333 --
334 end loop;
335 --
336 else
337 --
338 for obj in c_pln
339 (c_business_group_id => p_business_group_id
340 ,c_effective_date => p_effective_date
341 ,c_mode => p_mode
342 ,c_pgm_id => p_pgm_id
343 ,c_pl_id => p_pl_id
344 ,c_opt_id => p_opt_id
345 ,c_rptg_grp_id => p_rptg_grp_id
346 ,c_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
347 ,c_eligy_prfl_id => p_eligy_prfl_id
348 -- PB : 5422 :
349 -- ,c_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
350 )
351 loop
352 --
353 p_inst_set(l_row_num) := obj;
354 l_row_num := l_row_num+1;
355 --
356 end loop;
357 --
358 end if;
359 --
360 -- hr_utility.set_location (l_proc||' Leaving ',10);
361 exception
362 when others then
363 hr_utility.set_location (l_proc||' Leaving Others Exc ',100);
364 raise;
365 end bgpcpp_getdets;
366 --
367 procedure clear_down_cache is
368 --
369 l_proc varchar2(72) := g_package||'clear_down_cache';
370 --
371 begin
372 --
373 g_eedcpp_parlookup.delete;
374 g_eedcpp_lookup.delete;
375 g_eedcpp_inst.delete;
376 --
377 begin
378 --
379 dbms_session.free_unused_user_memory;
380 --
381 end;
382 --
383 end clear_down_cache;
384 --
385 end ben_pln_cache;
[Home] [Help]