[Home] [Help]
PACKAGE BODY: APPS.BEN_CVG_CACHE
Source
1 package body ben_cvg_cache as
2 /* $Header: bencvgch.pkb 120.3 2006/01/27 01:56:28 rtagarra noship $ */
3 --
4 g_package varchar2(50) := 'ben_cvg_cache.';
5 --
6 procedure epeplncvg_getdets
7 (p_epe_id in number
8 ,p_epe_pl_id in number
9 ,p_epe_plip_id in number
10 ,p_epe_oipl_id in number
11 ,p_effective_date in date
12 ,p_cvgtype_code in varchar2
13 --
14 ,p_inst_set out nocopy ben_cvg_cache.g_epeplncvg_cache
15 ,p_inst_count out nocopy number
16 )
17 is
18 --
19 l_proc varchar2(72) := g_package||'epeplncvg_getdets';
20 --
21 l_inst_rec g_epeplncvg_cache_rec;
22 l_row_num pls_integer;
23 --
24 l_pl_id number;
25 l_plip_id number;
26 l_oipl_id number;
27 --
28 begin
29 --
30 if p_cvgtype_code = 'CPP' then
31 --
32 ben_cvg_cache.cppplncvg_getdets
33 (p_plip_id => p_epe_plip_id
34 ,p_effective_date => p_effective_date
35 --
36 ,p_inst_set => p_inst_set
37 ,p_inst_count => p_inst_count
38 );
39 --
40 elsif p_cvgtype_code = 'PLN' then
41 --
42 -- Get the PLNCVG details
43 --
44 ben_cvg_cache.plnplncvg_getdets
45 (p_pln_id => p_epe_pl_id
46 ,p_effective_date => p_effective_date
47 --
48 ,p_inst_set => p_inst_set
49 ,p_inst_count => p_inst_count
50 );
51 --
52 elsif p_cvgtype_code = 'COP' then
53 --
54 ben_cvg_cache.copplncvg_getdets
55 (p_oipl_id => p_epe_oipl_id
56 ,p_effective_date => p_effective_date
57 --
58 ,p_inst_set => p_inst_set
59 ,p_inst_count => p_inst_count
60 );
61 --
62 end if;
63 --
64 end epeplncvg_getdets;
65 --
66 procedure plnplncvg_getdets
67 (p_pln_id in number
68 ,p_effective_date in date
69 --
70 ,p_inst_set out nocopy ben_cvg_cache.g_epeplncvg_cache
71 ,p_inst_count out nocopy number
72 )
73 is
74 --
75 l_proc varchar2(72) := g_package||'plnplncvg_getdets';
76 --
77 l_inst_rec g_epeplncvg_cache_rec;
78 l_row_num pls_integer;
79 --
80 cursor c_cursor
81 (c_pln_id in number
82 ,c_effective_date in date
83 )
84 is
85 select cvg.cvg_amt_calc_mthd_id,
86 cvg.comp_lvl_fctr_id,
87 cvg.cvg_mlt_cd,
88 cvg.bndry_perd_cd,
89 cvg.bnft_typ_cd,
90 cvg.val,
91 cvg.nnmntry_uom,
92 cvg.mx_val,
93 cvg.mn_val,
94 cvg.incrmt_val,
95 cvg.rt_typ_cd,
96 cvg.business_group_id,
97 cvg.rndg_cd,
98 cvg.rndg_rl,
99 cvg.val_calc_rl,
100 cvg.dflt_val,
101 cvg.entr_val_at_enrt_flag,
102 cvg.lwr_lmt_val,
103 cvg.lwr_lmt_calc_rl,
104 cvg.upr_lmt_val,
105 cvg.upr_lmt_calc_rl,
106 pln.cvg_incr_r_decr_only_cd,
107 pln.bnft_or_option_rstrctn_cd,
108 pln.mx_cvg_rl,
109 pln.mn_cvg_rl
110 from ben_cvg_amt_calc_mthd_f cvg,
111 ben_pl_f pln
112 where pln.pl_id = c_pln_id
113 and cvg.pl_id = pln.pl_id
114 and c_effective_date
115 between pln.effective_start_date
116 and pln.effective_end_date
117 and c_effective_date
118 between cvg.effective_start_date
119 and cvg.effective_end_date;
120 --
121 begin
122 --
123 -- Get the PLNCVG details
124 --
125 open c_cursor
126 (c_pln_id => p_pln_id
127 ,c_effective_date => p_effective_date
128 );
129 fetch c_cursor into l_inst_rec;
130 if c_cursor%found then
131 --
132 p_inst_set(0) := l_inst_rec;
133 --
134 end if;
135 close c_cursor;
136 --
137 end plnplncvg_getdets;
138 --
139 procedure cppplncvg_getdets
140 (p_plip_id in number
141 ,p_effective_date in date
142 --
143 ,p_inst_set out nocopy ben_cvg_cache.g_epeplncvg_cache
144 ,p_inst_count out nocopy number
145 )
146 is
147 --
148 l_proc varchar2(72) := g_package||'cppplncvg_getdets ';
149 --
150 l_inst_rec g_epeplncvg_cache_rec;
151 l_row_num pls_integer;
152 --
153 cursor c_cursor
154 (c_plip_id in number
155 ,c_effective_date in date
156 )
157 is
158 select cvg.cvg_amt_calc_mthd_id,
159 cvg.comp_lvl_fctr_id,
160 cvg.cvg_mlt_cd,
161 cvg.bndry_perd_cd,
162 cvg.bnft_typ_cd,
163 cvg.val,
164 cvg.nnmntry_uom,
165 cvg.mx_val,
166 cvg.mn_val,
167 cvg.incrmt_val,
168 cvg.rt_typ_cd,
169 cvg.business_group_id,
170 cvg.rndg_cd,
171 cvg.rndg_rl,
172 cvg.val_calc_rl,
173 cvg.dflt_val,
174 cvg.entr_val_at_enrt_flag,
175 cvg.lwr_lmt_val,
176 cvg.lwr_lmt_calc_rl,
177 cvg.upr_lmt_val,
178 cvg.upr_lmt_calc_rl,
179 pln.cvg_incr_r_decr_only_cd,
180 pln.bnft_or_option_rstrctn_cd,
181 pln.mx_cvg_rl,
182 pln.mn_cvg_rl
183 from ben_cvg_amt_calc_mthd_f cvg,
184 ben_plip_f cpp,
185 ben_pl_f pln
186 where cpp.plip_id = c_plip_id
187 and cvg.plip_id = cpp.plip_id
188 and cpp.pl_id = pln.pl_id
189 and c_effective_date
190 between cpp.effective_start_date
191 and cpp.effective_end_date
192 and c_effective_date
193 between pln.effective_start_date
194 and pln.effective_end_date
195 and c_effective_date
196 between cvg.effective_start_date
197 and cvg.effective_end_date;
198 --
199 begin
200 --
201 -- Get the PLNCVG details
202 --
203 open c_cursor
204 (c_plip_id => p_plip_id
205 ,c_effective_date => p_effective_date
206 );
207 fetch c_cursor into l_inst_rec;
208 if c_cursor%found then
209 --
210 p_inst_set(0) := l_inst_rec;
211 --
212 end if;
213 --
214 close c_cursor;
215 --
216 end cppplncvg_getdets ;
217 --
218 procedure copplncvg_getdets
219 (p_oipl_id in number
220 ,p_effective_date in date
221 --
222 ,p_inst_set out nocopy ben_cvg_cache.g_epeplncvg_cache
223 ,p_inst_count out nocopy number
224 )
225 is
226 --
227 l_proc varchar2(72) := g_package||'copplncvg_getdets';
228 --
229 l_inst_rec g_epeplncvg_cache_rec;
230 l_row_num pls_integer;
231 --
232 cursor c_cursor
233 (c_oipl_id in number
234 ,c_effective_date in date
235 )
236 is
237 select cvg.cvg_amt_calc_mthd_id,
238 cvg.comp_lvl_fctr_id,
239 cvg.cvg_mlt_cd,
240 cvg.bndry_perd_cd,
241 cvg.bnft_typ_cd,
242 cvg.val,
243 cvg.nnmntry_uom,
244 cvg.mx_val,
245 cvg.mn_val,
246 cvg.incrmt_val,
247 cvg.rt_typ_cd,
248 cvg.business_group_id,
249 cvg.rndg_cd,
250 cvg.rndg_rl,
251 cvg.val_calc_rl,
252 cvg.dflt_val,
253 cvg.entr_val_at_enrt_flag,
254 cvg.lwr_lmt_val,
255 cvg.lwr_lmt_calc_rl,
256 cvg.upr_lmt_val,
257 cvg.upr_lmt_calc_rl,
258 pln.cvg_incr_r_decr_only_cd,
259 pln.bnft_or_option_rstrctn_cd,
260 pln.mx_cvg_rl,
261 pln.mn_cvg_rl
262 from ben_cvg_amt_calc_mthd_f cvg,
263 ben_oipl_f cop,
264 ben_pl_f pln
265 where cop.oipl_id = c_oipl_id
266 and cvg.oipl_id = cop.oipl_id
267 and cop.pl_id = pln.pl_id
268 and c_effective_date
269 between cop.effective_start_date
270 and cop.effective_end_date
271 and c_effective_date
272 between pln.effective_start_date
273 and pln.effective_end_date
274 and c_effective_date
275 between cvg.effective_start_date
276 and cvg.effective_end_date;
277 --
278 begin
279 --
280 -- Get the PLNCVG details
281 --
282 open c_cursor
283 (c_oipl_id => p_oipl_id
284 ,c_effective_date => p_effective_date
285 );
286 fetch c_cursor into l_inst_rec;
287 if c_cursor%found then
288 --
289 p_inst_set(0) := l_inst_rec;
290 --
291 end if;
292 close c_cursor;
293 --
294 end copplncvg_getdets;
295 --
296 procedure epecobjtree_getcvgdets
297 (p_epe_id in number
298 ,p_epe_pl_id in number
299 ,p_epe_plip_id in number
300 ,p_epe_oipl_id in number
301 ,p_effective_date in date
302 --
303 ,p_cvg_set out nocopy ben_cvg_cache.g_epeplncvg_cache
304 )
305 is
306 --
307 l_proc varchar2(72) := g_package||'epecobjtree_getcvgdets';
308 --
309 l_cvg ben_cvg_cache.g_epeplncvg_cache;
310 l_inst_count pls_integer;
311 --
312 begin
313 --
314
315 -- Bug 4968171 - Query coverage details in this order : OIPL, PLN, PLIP
316 --
317 ben_cvg_cache.epeplncvg_getdets
318 (p_epe_id => p_epe_id
319 ,p_epe_pl_id => p_epe_pl_id
320 ,p_epe_plip_id => p_epe_plip_id
321 ,p_epe_oipl_id => p_epe_oipl_id
322 ,p_effective_date => p_effective_date
323 ,p_cvgtype_code => 'COP'
324 --
325 ,p_inst_set => l_cvg
326 ,p_inst_count => l_inst_count
327 );
328 --
329 if l_cvg.count = 0
330 then
331 --
332 ben_cvg_cache.epeplncvg_getdets
333 (p_epe_id => p_epe_id
334 ,p_epe_pl_id => p_epe_pl_id
335 ,p_epe_plip_id => p_epe_plip_id
336 ,p_epe_oipl_id => p_epe_oipl_id
337 ,p_effective_date => p_effective_date
338 ,p_cvgtype_code => 'PLN'
339 --
340 ,p_inst_set => l_cvg
341 ,p_inst_count => l_inst_count
342 );
343 --
344 if l_cvg.count = 0
345 then
346 --
347 ben_cvg_cache.epeplncvg_getdets
348 (p_epe_id => p_epe_id
349 ,p_epe_pl_id => p_epe_pl_id
350 ,p_epe_plip_id => p_epe_plip_id
351 ,p_epe_oipl_id => p_epe_oipl_id
352 ,p_effective_date => p_effective_date
353 ,p_cvgtype_code => 'CPP'
354 --
355 ,p_inst_set => l_cvg
356 ,p_inst_count => l_inst_count
357 );
358 --
359 end if;
360 --
361 end if;
362 --
363 -- Set OUT parameters
364 --
365 p_cvg_set := l_cvg;
366 --
367 end epecobjtree_getcvgdets;
368 --
369 end ben_cvg_cache;