[Home] [Help]
PACKAGE BODY: APPS.BEN_DET_ENRT_RATES
Source
1 PACKAGE body ben_det_enrt_rates as
2 /* $Header: benraten.pkb 120.11.12020000.2 2012/12/03 17:55:58 pvelvano ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_det_enrt_rates.';
7 --
8 type t_enrt_rslt_tab is table of number index by binary_integer;
9 type t_enrt_rt_tab is table of number index by binary_integer;
10 --
11 g_enrt_rslt_tab t_enrt_rslt_tab;
12 g_enrt_rt_tab t_enrt_rt_tab;
13 g_enrt_rslt_count number default 0;
14 g_enrt_rt_count number default 0;
15 --
16 --
17 -- Global variable maintainance. Used to idetify rates, which needs to
18 -- be processed.
19 -- Currently used by self-service but will be extended for professional
20 -- interface also.
21 --
22 procedure set_global_enrt_rslt
23 (p_prtt_enrt_rslt_id in number) is
24 begin
25 g_enrt_rslt_count := g_enrt_rslt_count + 1;
26 g_enrt_rslt_tab(g_enrt_rslt_count) := p_prtt_enrt_rslt_id;
27 end set_global_enrt_rslt;
28 --
29 procedure set_global_enrt_rt
30 (p_enrt_rt_id in number) is
31 begin
32 g_enrt_rt_count := g_enrt_rt_count + 1;
33 g_enrt_rt_tab(g_enrt_rt_count) := p_enrt_rt_id;
34 end set_global_enrt_rt;
35 --
36 function enrt_rslt_exists(p_prtt_enrt_rslt_id in number) return boolean is
37 begin
38 if g_enrt_rslt_tab.count > 0 then
39 for i in g_enrt_rslt_tab.first..g_enrt_rslt_tab.last loop
40 if p_prtt_enrt_rslt_id = g_enrt_rslt_tab(i) then
41 return true;
42 end if;
43 end loop;
44 end if;
45 --
46 return false;
47 --
48 end enrt_rslt_exists;
49 --
50 function enrt_rt_exists(p_enrt_rt_id in number) return boolean is
51 begin
52 if g_enrt_rt_tab.count > 0 then
53 for i in g_enrt_rt_tab.first..g_enrt_rt_tab.last loop
54 if p_enrt_rt_id = g_enrt_rt_tab(i) then
55 return true;
56 end if;
57 end loop;
58 end if;
59 --
60 return false;
61 --
62 end enrt_rt_exists;
63 --
64 procedure clear_globals is
65 begin
66 g_enrt_rt_count := 0;
67 g_enrt_rslt_count := 0;
68 g_enrt_rslt_tab.delete;
69 g_enrt_rt_tab.delete;
70 end;
71 --
72 -- ----------------------------------------------------------------------------
73 -- |---------------------------< p_det_enrt_rates >---------------------------|
74 -- ----------------------------------------------------------------------------
75 --
76 procedure p_det_enrt_rates
77 (p_calculate_only_mode in boolean default false
78 ,p_person_id in number
79 ,p_per_in_ler_id in number
80 ,p_enrt_mthd_cd in varchar2
81 ,p_business_group_id in number
82 ,p_effective_date in date
83 ,p_validate in boolean
84 ,p_self_service_flag in boolean default false
85 --
86 ,p_prv_rtval_set out nocopy ben_det_enrt_rates.PRVRtVal_tab
87 )
88 is
89 --
90 -- Cursor to fetch the enrt rslt for the participant
91 --
92 cursor c_enrt_rslt
93 (c_person_id in number
94 ,c_enrt_mthd_cd in varchar2
95 ,c_per_in_ler_id in number
96 ,c_effective_date in date
97 )
98 is
99 select pen.prtt_enrt_rslt_id,
100 pen.pl_id,
101 pen.pgm_id,
102 pen.oipl_id,
103 pen.enrt_cvg_strt_dt,
104 pen.comp_lvl_cd
105 from ben_prtt_enrt_rslt_f pen
106 where pen.person_id = c_person_id
107 --
108 -- Bug 6445880
109 -- Changed enrt_mthd_cd checks, to allow Default Enrollment records to be picked
110 -- up when Benefit elections are made using spreadsheet from Configuration
111 -- workbench in which case c_enrt_mthd_cd value will be 'E'
112 --
113 -- and ( pen.enrt_mthd_cd = c_enrt_mthd_cd
114 -- or pen.enrt_mthd_cd = 'O' ) -- Bug 2200139 Override Enhancements
115 and (( pen.enrt_mthd_cd = c_enrt_mthd_cd or pen.enrt_mthd_cd = 'O' )
116 or (pen.enrt_mthd_cd <> c_enrt_mthd_cd
117 and c_enrt_mthd_cd = 'E'
118 and (pen.enrt_mthd_cd = 'D'
119 or pen.enrt_mthd_cd = 'A'))
120 --Bug 9775905
121 or (c_enrt_mthd_cd = 'D' and
122 pen.enrt_mthd_cd = 'E' and
123 exists (select null
124 from ben_bnft_pool_rlovr_rqmt_f rlovr,
125 ben_prtt_enrt_rslt_f pen1,
126 ben_bnft_prvdd_ldgr_f ldgr,
127 ben_acty_base_rt_f abr
128 where pen1.pgm_id = pen.pgm_id
129 and pen1.comp_lvl_cd = 'PLANFC'
130 and pen1.per_in_ler_id = pen.per_in_ler_id
131 and c_effective_date
132 between pen1.effective_start_date and pen1.effective_end_date
133 and pen1.prtt_enrt_rslt_stat_cd is null
134 and pen1.business_group_id = pen.business_group_id
135 and ldgr.prtt_enrt_rslt_id = pen1.prtt_enrt_rslt_id
136 and ldgr.business_group_id = pen1.business_group_id
137 and ldgr.per_in_ler_id = pen1.per_in_ler_id
138 and ldgr.bnft_prvdr_pool_id = rlovr.bnft_prvdr_pool_id
139 and rlovr.business_group_id = pen1.business_group_id
140 and c_effective_date
141 between rlovr.effective_start_date and rlovr.effective_end_date
142 and rlovr.acty_base_rt_id = abr.acty_base_rt_id
143 and c_effective_date
144 between abr.effective_start_date and abr.effective_end_date
145 and abr.business_group_id = pen1.business_group_id
146 and abr.context_pl_id = pen.pl_id
147 and ( ( pen.oipl_id is not null
148 and exists ( select null
149 from ben_oipl_f oipl
150 where oipl.oipl_id = pen.oipl_id
151 and oipl.business_group_id = pen.business_group_id
152 and c_effective_date
153 between oipl.effective_start_date and oipl.effective_end_date
154 and nvl(abr.context_opt_id,oipl.opt_id) = oipl.opt_id
155 )
156 )
157 or (pen.oipl_id is null)
158 )
159 and abr.rt_usg_cd = 'STD'
160 )
161 )
162 --Bug 9775905
163 )
164 and pen.prtt_enrt_rslt_stat_cd is null
165 and pen.per_in_ler_id = c_per_in_ler_id
166 and enrt_cvg_thru_dt = hr_api.g_eot
167 and pen.comp_lvl_cd <> 'PLANIMP'
168 and c_effective_date
169 between pen.effective_start_date and pen.effective_end_date
170 and pen.effective_end_date = hr_api.g_eot
171 and -- start 4354929
172 ( EXISTS ( select null
173 from ben_ler_f ler,
174 ben_elig_per_elctbl_chc epe
175 where ler.ler_id = pen.ler_id
176 and (( ler.typ_cd = 'SCHEDDU'
177 and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
178 and epe.per_in_ler_id = c_per_in_ler_id )
179 or
180 ( ler.typ_cd <> 'SCHEDDU'
181 and epe.per_in_ler_id = c_per_in_ler_id)
182 )
183 )--exists
184 ) -- end 4354929
185 order by pen.rplcs_sspndd_rslt_id;
186 --
187 l_enrt_rslt_rec c_enrt_rslt%rowtype;
188 --
189 cursor c_rslt_pgm
190 (c_person_id in number
191 ,c_enrt_mthd_cd in varchar2
192 ,c_per_in_ler_id in number
193 ,c_effective_date in date
194 )
195 is
196 select distinct pen.pgm_id
197 from ben_prtt_enrt_rslt_f pen
198 where pen.person_id = c_person_id
199 and ( pen.enrt_mthd_cd in ('A','D','E') -- Bug 15938962
200 or pen.enrt_mthd_cd = 'O' ) -- Bug 2200139 Override Enhancements
201 and pen.prtt_enrt_rslt_stat_cd is null
202 and pen.per_in_ler_id = c_per_in_ler_id
203 and enrt_cvg_thru_dt = hr_api.g_eot
204 and pen.comp_lvl_cd <> 'PLANIMP'
205 and pen.pgm_id is not null
206 and c_effective_date
207 between pen.effective_start_date and pen.effective_end_date
208 and pen.effective_end_date = hr_api.g_eot;
209 --
210 l_pgm_id number;
211
212 -- Cursor to check if the prtt is also enrolled in another pl/oipl that may
213 -- qualify for a special rate.
214 --
215 cursor c_spcl_enrt_rslt(v_pl_id in number, v_oipl_id in number)
216 is
217 select '1'
218 from ben_prtt_enrt_rslt_f pen
219 where pen.person_id = p_person_id
220 and pen.business_group_id = p_business_group_id
221 and (pen.pl_id = v_pl_id or
222 pen.oipl_id = v_oipl_id)
223 and ( pen.enrt_mthd_cd = p_enrt_mthd_cd -- Bug 2200139 for Override
224 or pen.enrt_mthd_cd = 'O' )
225 and pen.prtt_enrt_rslt_stat_cd is null
226 and pen.sspndd_flag = 'N'
227 and enrt_cvg_thru_dt = hr_api.g_eot
228 and p_effective_date between pen.effective_start_date
229 and pen.effective_end_date
230 and pen.effective_end_date = hr_api.g_eot;
231 --
232 -- Cursor to fetch the electable choice.
233 --
234 -- Added the union to get the choice when a person is enrolled in two
235 -- benefits for the same plan (One can be suspeded and other interim)
236 -- but different coverage amounts, then the choice record will have been
237 -- updated by interim result's result id. So the only way left to get
238 -- the choice for the suspended result is to go through the benefit record.
239 -- (maagrawa 2/5/00)
240 --
241 cursor c_elctbl_chc(v_enrt_rslt_id in number)
242 is
243 select epe.pl_id,
244 epe.oipl_id,
245 epe.elig_per_elctbl_chc_id,
246 epe.spcl_rt_pl_id,
247 epe.spcl_rt_oipl_id,
248 epe.fonm_cvg_strt_dt,
249 pel.acty_ref_perd_cd
250 from ben_elig_per_elctbl_chc epe,
251 ben_per_in_ler pil,
252 ben_pil_elctbl_chc_popl pel,
253 ben_prtt_enrt_rslt_f pen
254 where epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
255 and pil.per_in_ler_id=epe.per_in_ler_id
256 and pil.per_in_ler_id = p_per_in_ler_id
257 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
258 and pen.prtt_enrt_rslt_id=v_enrt_rslt_id
259 and nvl(pen.pgm_id,-1)=nvl(epe.pgm_id,-1)
260 and pen.pl_id=epe.pl_id
261 and nvl(pen.oipl_id,-1)=nvl(epe.oipl_id,-1)
262 -- added bnft prvdr pool id to fetch the electable choice related to the comp.object
263 -- and prevent the one meant for flex credit - Bug#2177187- If flex credit is defined
264 -- on combination plan type and option, the cursor returns two rows without prvdr pool
265 -- id join
266 and epe.bnft_prvdr_pool_id is null
267 and pen.prtt_enrt_rslt_stat_cd is null
268 and p_effective_date between
269 pen.effective_start_date and pen.effective_end_date
270 ;
271 --
272 l_epe_rec c_elctbl_chc%rowtype;
273 --
274 -- Cursor to fetch the enrt rate for an elecbl chc.
275 --
276 cursor c_enrt_rt
277 (c_elig_per_elctbl_chc_id in number
278 ,c_prtt_enrt_rslt_id in number
279 )
280 is
281 select ecr.prtt_rt_val_id,
282 ecr.enrt_rt_id,
283 ecr.val,
284 ecr.ann_val,
285 ecr.rt_mlt_cd,
286 ecr.acty_typ_cd,
287 ecr.rt_strt_dt,
288 ecr.acty_base_rt_id,
289 to_char(null) cvg_mlt_cd
290 from ben_enrt_rt ecr
291 where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
292 and ecr.SPCL_RT_ENRT_RT_ID is null
293 and ecr.entr_val_at_enrt_flag = 'N'
294 and nvl(ecr.rt_strt_dt_cd,'AED') <> 'ENTRBL' --Bug 3053267
295 and ecr.asn_on_enrt_flag = 'Y'
296 and ecr.rt_mlt_cd <> 'ERL' -- added for canon fix
297 UNION
298 select ecr.prtt_rt_val_id,
299 ecr.enrt_rt_id,
300 ecr.val,
301 ecr.ann_val,
302 ecr.rt_mlt_cd,
303 ecr.acty_typ_cd,
304 ecr.rt_strt_dt,
305 ecr.acty_base_rt_id,
306 enb.cvg_mlt_cd cvg_mlt_cd
307 from ben_enrt_bnft enb,
308 ben_enrt_rt ecr
309 where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
310 and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
311 and enb.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
312 and ecr.SPCL_RT_ENRT_RT_ID is null
313 and ecr.entr_val_at_enrt_flag = 'N'
314 and nvl(ecr.rt_strt_dt_cd,'AED') <> 'ENTRBL' --Bug 3053267
315 and ecr.asn_on_enrt_flag = 'Y'
316 and ecr.rt_mlt_cd <> 'ERL' ; -- added for canon fix
317 --
318 -- Cursor to fetch the special enrt rate for an enrt rate.
319 --
320 cursor c_spcl_enrt_rt(v_enrt_rt_id in number)
321 is
322 select ecr.prtt_rt_val_id,
323 ecr.enrt_rt_id,
324 ecr.val,
325 ecr.ann_val,
326 ecr.rt_mlt_cd,
327 ecr.acty_typ_cd,
328 ecr.rt_strt_dt,
329 ecr.acty_base_rt_id,
330 to_char(null) cvg_mlt_cd
331 from ben_enrt_rt ecr
332 where ecr.spcl_rt_enrt_rt_id = v_enrt_rt_id
333 and ecr.entr_val_at_enrt_flag = 'N'
334 and ecr.asn_on_enrt_flag = 'Y'
335 and ecr.business_group_id = p_business_group_id;
336 --
337 l_spcl_rt_rec c_spcl_enrt_rt%rowtype;
338 l_use_enrt_rec c_spcl_enrt_rt%rowtype;
339 --
340 -- Added this cursor to stop re-processing the flat-fixed (FLFX) rates,
341 -- once they have been written. The only cases when the non-enterable
342 -- rates should be re-written is when they have been deleted or voided.
343 -- The cursor below takes care of it. (maagrawa Mar 09, 2001)
344 --
345 cursor c_prv(v_prtt_enrt_rslt_id in number) is
346 select prv.prtt_rt_val_id
347 from ben_prtt_rt_val prv
348 where prv.prtt_rt_val_id = l_use_enrt_rec.prtt_rt_val_id
349 and prv.per_in_ler_id = p_per_in_ler_id
350 and prv.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
351 and prv.mlt_cd = 'FLFX'
352 and prv.prtt_rt_val_stat_cd is null
353 and prv.rt_strt_dt <= prv.rt_end_dt;
354 --
355 cursor c_prv2(p_prtt_enrt_rslt_id in number,
356 p_acty_base_rt_id in number) is
357 select prv.prtt_rt_val_id
358 from ben_prtt_rt_val prv
359 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
360 and acty_base_rt_id = p_acty_base_rt_id
361 and prtt_rt_val_stat_cd is null;
362 --
363 cursor c_unrestricted is
364 select 'Y'
365 from ben_per_in_ler pil,
366 ben_ler_f ler
367 where pil.per_in_ler_id = p_per_in_ler_id
368 and pil.ler_id = ler.ler_id
369 and ler.typ_cd = 'SCHEDDU'
370 and ler.business_group_id = p_business_group_id
371 and p_effective_date between ler.effective_start_date
372 and ler.effective_end_date;
373
374 --
375 cursor c_rollover_plan is
376 select decode(enb.enrt_bnft_id,
377 null, ecr2.enrt_rt_id,
378 ecr1.enrt_rt_id) enrt_rt_id,
379 decode(enb.enrt_bnft_id,
380 null, ecr2.rt_mlt_cd,
381 ecr1.rt_mlt_cd) rt_mlt_cd,
382 decode(enb.enrt_bnft_id,
383 null, ecr2.entr_val_at_enrt_flag,
384 ecr1.entr_val_at_enrt_flag) entr_val_at_enrt_flag, --bug 5608160
385 enb.enrt_bnft_id,
386 nvl(enb.val, enb.dflt_val) bnft_val,
387 epe.elig_per_elctbl_chc_id,
388 pel.acty_ref_perd_cd,
389 pen.prtt_enrt_rslt_id,
390 pen.bnft_amt,
391 pen.object_version_number,
392 pen.pgm_id,
393 pen.pl_id,
394 pen.oipl_id
395 from ben_per_in_ler pil,
396 ben_elig_per_elctbl_chc epe,
397 ben_pil_elctbl_chc_popl pel,
398 ben_enrt_rt ecr1,
399 ben_enrt_rt ecr2,
400 ben_enrt_bnft enb,
401 ben_prtt_enrt_rslt_f pen,
402 ben_bnft_prvdr_pool_f bpp -- join to get only current pgm_id - rgajula
403 where
404 pil.per_in_ler_id=p_per_in_ler_id and
405 pil.business_group_id=p_business_group_id and
406 pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
407 pil.per_in_ler_id=epe.per_in_ler_id and
408 pil.per_in_ler_id = pel.per_in_ler_id and
409 pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id and
410 epe.business_group_id=p_business_group_id and
411 epe.elig_per_elctbl_chc_id=ecr2.elig_per_elctbl_chc_id(+) and
412 bpp.bnft_prvdr_pool_id in (select bnft_prvdr_pool_id from ben_bnft_pool_rlovr_rqmt_f
413 where business_group_id=p_business_group_id
414 and p_effective_date between effective_start_date and effective_end_date) and
415 bpp.business_group_id = p_business_group_id and --
416 p_effective_date between bpp.effective_start_date and bpp.effective_end_date and --
417 bpp.pgm_id = epe.pgm_id and --
418 (ecr1.acty_base_rt_id in (select acty_base_rt_id from ben_bnft_pool_rlovr_rqmt_f
419 where business_group_id=p_business_group_id
420 and p_effective_date between effective_start_date and effective_end_date) or
421 ecr2.acty_base_rt_id in (select acty_base_rt_id from ben_bnft_pool_rlovr_rqmt_f
422 where business_group_id=p_business_group_id
423 and p_effective_date between effective_start_date and effective_end_date)) and
424 pen.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id and
425 epe.elig_per_elctbl_chc_id=enb.elig_per_elctbl_chc_id(+) and
426 enb.enrt_bnft_id = ecr1.enrt_bnft_id(+) and
427 pen.prtt_enrt_rslt_stat_cd is null and
428 p_effective_date between
429 pen.effective_start_date(+) and pen.effective_end_date(+) and
430 pen.business_group_id(+)=p_business_group_id ;
431 --
432 l_rollover_plan_rec c_rollover_plan%rowtype;
433 --
434 l_prv_rec c_prv%rowtype;
435 --
436 l_use_spcl_rates_flag varchar2(1) := 'N';
437 l_dummy varchar2(1);
438 l_rate_amount number;
439 l_dummy_number number;
440 --
441 l_prtt_enrt_rslt_id_pool number;
442 l_prtt_rt_val_id_pool number;
443 l_acty_ref_perd_cd_pool varchar2(30);
444 l_acty_base_rt_id_pool number;
445 l_rt_strt_dt_pool date;
446 l_rt_val_pool number;
447 l_element_type_id_pool number;
448 L_BNFT_PRVDD_LDGR_ID number;
449 --
450 l_prtt_rt_val_id number;
451 l_call_total_pools_flag boolean := FALSE;
452 --
453 l_proc varchar2(72) := g_package||'p_det_enrt_rates';
454 --
455 l_penecrloop_cnt number;
456 l_pgm_rec ben_cobj_cache.g_pgm_inst_row;
457 --
458 l_process_this_result boolean := true;
459 l_process_this_rate boolean := true;
460 l_net_credit_method boolean := false;
461 l_unrestricted varchar2(1) := 'N';
462 l_prtt_rt_val_id2 number;
463 --
464 begin
465 --
466 hr_utility.set_location(' Entering: ' ||l_proc , 10);
467 --
468 --
469 open c_unrestricted;
470 fetch c_unrestricted into l_unrestricted;
471 close c_unrestricted;
472
473 -- Loop through all the enrt rslt records for the person.
474 --
475 l_penecrloop_cnt := 0;
476 --
477 for l_enrt_rslt_rec in c_enrt_rslt
478 (c_person_id => p_person_id
479 ,c_enrt_mthd_cd => p_enrt_mthd_cd
480 ,c_per_in_ler_id => p_per_in_ler_id
481 ,c_effective_date => p_effective_date
482 )
483 loop
484 --
485 l_process_this_result := true;
486 --
487
488 if p_self_service_flag and l_enrt_rslt_rec.comp_lvl_cd <> 'PLANFC' then --Bug 2736036 for Flex Plan enrt_rslts are not available
489 --in global result table when an Update Enrt is done
490 --
491 -- Check if the result exists in the global result table.
492 -- If not, go to next record.
493 --
494 l_process_this_result := enrt_rslt_exists
495 (p_prtt_enrt_rslt_id =>
496 l_enrt_rslt_rec.prtt_enrt_rslt_id);
497 --
498 --9817172
499 if (not l_process_this_result) then
500 for l_rollover_plan_rec in c_rollover_plan
501 loop
502 hr_utility.set_location('l_enrt_rslt_rec.pgm_id' || l_enrt_rslt_rec.pgm_id, 1234);
503 hr_utility.set_location('l_enrt_rslt_rec.pl_id' || l_enrt_rslt_rec.pl_id, 1234);
504 hr_utility.set_location('l_enrt_rslt_rec.oipl_id' || l_enrt_rslt_rec.oipl_id, 1234);
505
506 hr_utility.set_location('l_rollover_plan_rec.pgm_id' || l_rollover_plan_rec.pgm_id, 987);
507 hr_utility.set_location('l_rollover_plan_rec.pl_id' || l_rollover_plan_rec.pl_id, 987);
508 hr_utility.set_location('l_rollover_plan_rec.oipl_id' || l_rollover_plan_rec.oipl_id, 987);
509
510 if (nvl(l_enrt_rslt_rec.pgm_id,-1) = nvl(l_rollover_plan_rec.pgm_id,-1) and
511 nvl(l_enrt_rslt_rec.pl_id,-1) = nvl(l_rollover_plan_rec.pl_id,-1) and
512 nvl(l_enrt_rslt_rec.oipl_id,-1) = nvl(l_rollover_plan_rec.oipl_id,-1))then
513 l_process_this_result := true;
514 exit;
515 end if;
516 end loop;
517 end if;
518 --9817172
519 end if;
520 --
521 if l_process_this_result then
522 --
523 -- Get the elctbl_chc id for the enrt rslt
524 --
525 open c_elctbl_chc(l_enrt_rslt_rec.prtt_enrt_rslt_id);
526 fetch c_elctbl_chc into l_epe_rec;
527 --
528
529 if c_elctbl_chc%notfound then
530 -- raise error
531 close c_elctbl_chc;
532 fnd_message.set_name('BEN','BEN_91491_NO_ELCTBL_CHC');
533 fnd_message.set_token('PROC',l_proc);
534 fnd_message.set_token('PERSON_ID',p_person_id);
535 fnd_message.set_token('PRTT_ENRT_RSLT_ID',
536 l_enrt_rslt_rec.prtt_enrt_rslt_id);
537 fnd_message.set_token('PER_IN_LER_ID',p_per_in_ler_id);
538 fnd_message.raise_error;
539 --
540 else
541 --
542 close c_elctbl_chc;
543 --
544 end if;
545
546 --
547 -- If the person is enrolled in a flex program, we'll need to call
548 -- total pools.
549 --
550 if not (l_call_total_pools_flag)
551 and l_enrt_rslt_rec.pgm_id is not null then
552 --
553 ben_cobj_cache.get_pgm_dets(p_business_group_id=> p_business_group_id
554 ,p_effective_date => p_effective_date
555 ,p_pgm_id => l_enrt_rslt_rec.pgm_id
556 ,p_inst_row => l_pgm_rec);
557 --
558 if l_pgm_rec.pgm_typ_cd in ('COBRAFLX','FLEX', 'FPC') then
559 l_call_total_pools_flag := TRUE;
560 end if;
561 --
562 end if;
563 --
564 -- Check for special rates
565 --
566 if l_epe_rec.spcl_rt_pl_id is not null
567 or l_epe_rec.spcl_rt_oipl_id is not null then
568 --
569 -- The elctbl chc has a special rate for another plan or oipl. Check
570 -- if the person is enrolled in that plan or oipl and if yes, we have
571 -- to use special rates... set the l_use_spl_rates_flag to 'Y'
572 --
573 open c_spcl_enrt_rslt(l_epe_rec.pl_id, l_epe_rec.oipl_id);
574 fetch c_spcl_enrt_rslt into l_dummy;
575 --
576 if c_spcl_enrt_rslt%found then
577 --
578 l_use_spcl_rates_flag := 'Y';
579 --
580 else
581 --
582 l_use_spcl_rates_flag := 'N';
583 --
584 end if;
585 --
586 close c_spcl_enrt_rslt;
587 --
588 hr_utility.set_location('spcl_rates_flag: ' || l_use_spcl_rates_flag
589 ||' ' || l_proc, 20);
590 --
591 end if;
592 --
593 -- Loop through the enrt rt records for the eltbl chc
594 --
595 -- only if net credit method then call election rate information for shell plan
596 if l_enrt_rslt_rec.comp_lvl_cd = 'PLANFC' then
597 for l_enrt_rt_rec in c_enrt_rt
598 (c_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id
599 ,c_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id
600 ) loop
601 if l_enrt_rt_rec.acty_typ_cd in ('NCRDSTR','NCRUDED') then
602 l_net_credit_method := TRUE;
603 exit;
604 end if;
605 end loop;
606 end if;
607 --
608 for l_enrt_rt_rec in c_enrt_rt
609 (c_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id
610 ,c_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id
611 ) loop
612 if l_enrt_rslt_rec.comp_lvl_cd = 'PLANFC' and not (l_net_credit_method) then
613 exit;
614 end if;
615 --
616 -- Initialize the record to be used to the normal enrt_rt record.
617 --
618 l_use_enrt_rec := l_enrt_rt_rec;
619 --
620 if l_epe_rec.fonm_cvg_strt_dt is not null then
621 ben_manage_life_events.fonm := 'Y';
622 ben_manage_life_events.g_fonm_rt_strt_dt := l_enrt_rt_rec.rt_strt_dt;
623 ben_manage_life_events.g_fonm_cvg_strt_dt := l_epe_rec.fonm_cvg_strt_dt;
624 else
625 ben_manage_life_events.fonm := 'N';
626 ben_manage_life_events.g_fonm_rt_strt_dt := null;
627 ben_manage_life_events.g_fonm_cvg_strt_dt := null;
628 end if;
629 --
630 -- Check if a special rate exists and try to use that.
631 --
632 if l_use_spcl_rates_flag = 'Y' then
633 --
634 open c_spcl_enrt_rt(l_enrt_rt_rec.enrt_rt_id);
635 fetch c_spcl_enrt_rt into l_spcl_rt_rec;
636 --
637 if c_spcl_enrt_rt%found then
638 --
639 -- Since a special rate record was found, we have to use this rate
640 -- record instead of the normal rate.
641 --
642 hr_utility.set_location('Using special rates : ' || l_proc, 20);
643 --
644 l_use_enrt_rec := l_spcl_rt_rec;
645
646 if ben_manage_life_events.fonm = 'Y' then
647 ben_manage_life_events.g_fonm_rt_strt_dt := l_spcl_rt_rec.rt_strt_dt;
648 end if;
649 --
650 end if;
651 --
652 close c_spcl_enrt_rt;
653 --
654 end if;
655 --
656 l_process_this_rate := true;
657 --
658 if p_self_service_flag and l_enrt_rslt_rec.comp_lvl_cd <> 'PLANFC' then -- Bug 2736036, Rates associated with Flex Plan need to be
659 -- -- recalculated even if they already exist in global rates table
660 --
661 -- Check if the enrollent rate exists in global rate table.
662 -- If yes, then do not re-process that rate again.
663 --
664 -- Bug 3254982, if the rate is based on any ERL calculated "coverage or parent rate or both" then re-process the rate
665 if (nvl(l_enrt_rt_rec.cvg_mlt_cd,'NULL') = 'ERL') and (l_enrt_rt_rec.rt_mlt_cd in ('CVG','PRNT','PRNTANDCVG')) then
666 l_process_this_rate := true;
667 else
668 l_process_this_rate := not enrt_rt_exists
669 (p_enrt_rt_id => l_use_enrt_rec.enrt_rt_id);
670 end if;
671
672 for l_rollover_plan_rec in c_rollover_plan
673 loop
674 hr_utility.set_location('l_enrt_rslt_rec.pgm_id' || l_enrt_rslt_rec.pgm_id, 1234);
675 hr_utility.set_location('l_enrt_rslt_rec.pl_id' || l_enrt_rslt_rec.pl_id, 1234);
676 hr_utility.set_location('l_enrt_rslt_rec.oipl_id' || l_enrt_rslt_rec.oipl_id, 1234);
677
678 hr_utility.set_location('l_rollover_plan_rec.pgm_id' || l_rollover_plan_rec.pgm_id, 987);
679 hr_utility.set_location('l_rollover_plan_rec.pl_id' || l_rollover_plan_rec.pl_id, 987);
680 hr_utility.set_location('l_rollover_plan_rec.oipl_id' || l_rollover_plan_rec.oipl_id, 987);
681
682 if (nvl(l_enrt_rslt_rec.pgm_id,-1) = nvl(l_rollover_plan_rec.pgm_id,-1) and
683 nvl(l_enrt_rslt_rec.pl_id,-1) = nvl(l_rollover_plan_rec.pl_id,-1) and
684 nvl(l_enrt_rslt_rec.oipl_id,-1) = nvl(l_rollover_plan_rec.oipl_id,-1))then
685 l_process_this_rate := true;
686 exit;
687 end if;
688 end loop;
689 --
690 end if;
691 --
692 if l_process_this_rate then
693 --
694 l_prv_rec.prtt_rt_val_id := null;
695 --
696 if l_use_enrt_rec.prtt_rt_val_id is not null then
697 open c_prv(v_prtt_enrt_rslt_id =>
698 l_enrt_rslt_rec.prtt_enrt_rslt_id);
699 fetch c_prv into l_prv_rec;
700 close c_prv;
701 end if;
702 --
703 -- Check for calculate only mode. Do not re-calculate flat amounts
704 -- rt_mlt_cd = FLFX
705 --
706 if p_calculate_only_mode
707 and nvl(l_enrt_rt_rec.rt_mlt_cd,'ZZZ') = 'FLFX' then
708 --
709 p_prv_rtval_set(l_penecrloop_cnt).rt_val := null;
710 p_prv_rtval_set(l_penecrloop_cnt).ann_rt_val := null;
711 p_prv_rtval_set(l_penecrloop_cnt).prtt_rt_val_id :=
712 l_enrt_rt_rec.prtt_rt_val_id;
713 --
714 else
715 --
716 -- Calculate Only Mode: Always call election_rate_information to
717 -- get the calulated value.
718 -- Not Calculate Mode: Call election_rate_information, only when
719 -- rate has not been already saved for the LE.
720 --
721 -- if p_calculate_only_mode or l_prv_rec.prtt_rt_val_id is null then
722 hr_utility.set_location('Rate Code'||l_enrt_rt_rec.rt_mlt_cd ,112);
723 if l_unrestricted = 'Y' and l_enrt_rt_rec.rt_mlt_cd = 'SAREC' then
724 l_prtt_rt_val_id2 := null;
725 open c_prv2 (l_enrt_rslt_rec.prtt_enrt_rslt_id, l_use_enrt_rec.acty_base_rt_id);
726 fetch c_prv2 into l_prtt_rt_val_id2;
727 close c_prv2;
728 end if;
729 hr_utility.set_location('Prtt rate val'||l_prtt_rt_val_id,111);
730 if not (l_prtt_rt_val_id2 is not null and
731 l_enrt_rt_rec.rt_mlt_cd = 'SAREC' and l_unrestricted = 'Y') then
732 ben_election_information.election_rate_information
733 (p_calculate_only_mode => p_calculate_only_mode
734 ,p_enrt_mthd_cd => p_enrt_mthd_cd
735 ,p_effective_date => p_effective_date
736 ,p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id
737 ,p_per_in_ler_id => p_per_in_ler_id
738 ,p_person_id => p_person_id
739 ,p_pgm_id => l_enrt_rslt_rec.pgm_id
740 ,p_pl_id => l_enrt_rslt_rec.pl_id
741 ,p_oipl_id => l_enrt_rslt_rec.oipl_id
742 ,p_enrt_rt_id => l_use_enrt_rec.enrt_rt_id
743 ,p_prtt_rt_val_id => l_prtt_rt_val_id
744 ,p_rt_val => l_use_enrt_rec.val
745 ,p_ann_rt_val => l_use_enrt_rec.ann_val
746 ,p_enrt_cvg_strt_dt => l_enrt_rslt_rec.enrt_cvg_strt_dt
747 ,p_acty_ref_perd_cd => l_epe_rec.acty_ref_perd_cd
748 ,p_datetrack_mode => null
749 ,p_business_group_id => p_business_group_id
750 --
751 ,p_prv_rt_val => p_prv_rtval_set(l_penecrloop_cnt).
752 rt_val
753 ,p_prv_ann_rt_val => p_prv_rtval_set(l_penecrloop_cnt).
754 ann_rt_val
755 );
756 --
757 p_prv_rtval_set(l_penecrloop_cnt).prtt_rt_val_id :=
758 l_prtt_rt_val_id;
759 end if;
760 --
761 end if;
762 --
763 p_prv_rtval_set(l_penecrloop_cnt).ecr_rt_mlt_cd :=
764 l_enrt_rt_rec.rt_mlt_cd;
765 --
766 l_penecrloop_cnt := l_penecrloop_cnt+1;
767 --
768 end if; -- if l_process_this_rate.
769 --
770 end loop;
771 --
772 end if; -- if l_process_this_result
773 --
774 end loop;
775 --
776 -- Clear the globals used by the procedure.
777 --
778 clear_globals;
779 --
780 -- write participant rates with rate multi code ERL
781 for l_enrt_rslt_rec in c_enrt_rslt
782 (c_person_id => p_person_id
783 ,c_enrt_mthd_cd => p_enrt_mthd_cd
784 ,c_per_in_ler_id => p_per_in_ler_id
785 ,c_effective_date => p_effective_date
786 )
787 loop
788 --
789 open c_elctbl_chc(l_enrt_rslt_rec.prtt_enrt_rslt_id);
790 fetch c_elctbl_chc into l_epe_rec;
791 --
792 if c_elctbl_chc%notfound then
793 -- raise error
794 close c_elctbl_chc;
795 fnd_message.set_name('BEN','BEN_91491_NO_ELCTBL_CHC');
796 fnd_message.set_token('PROC',l_proc);
797 fnd_message.set_token('PERSON_ID',p_person_id);
798 fnd_message.set_token('PRTT_ENRT_RSLT_ID',
799 l_enrt_rslt_rec.prtt_enrt_rslt_id);
800 fnd_message.set_token('PER_IN_LER_ID',p_per_in_ler_id);
801 fnd_message.raise_error;
802 --
803 else
804 --
805 close c_elctbl_chc;
806 --
807 end if;
808 --
809 --
810 det_enrt_rates_erl
811 (p_person_id => p_person_id
812 ,p_per_in_ler_id => p_per_in_ler_id
813 ,p_enrt_mthd_cd => p_enrt_mthd_cd
814 ,p_business_group_id => p_business_group_id
815 ,p_effective_date => p_effective_date
816 ,p_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id
817 ,p_fonm_cvg_strt_dt => l_epe_rec.fonm_cvg_strt_dt
818 ,p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id
819 ,p_pgm_id => l_enrt_rslt_rec.pgm_id
820 ,p_pl_id => l_enrt_rslt_rec.pl_id
821 ,p_oipl_id => l_enrt_rslt_rec.oipl_id
822 ,p_enrt_cvg_strt_dt => l_enrt_rslt_rec.enrt_cvg_strt_dt
823 ,p_acty_ref_perd_cd => l_epe_rec.acty_ref_perd_cd
824 );
825 end loop;
826 --
827 ben_det_enrt_rates.end_prtt_rt_val
828 (p_person_id => p_person_id
829 ,p_per_in_ler_id => p_per_in_ler_id
830 ,p_enrt_mthd_cd =>p_enrt_mthd_cd
831 ,p_business_group_id => p_business_group_id
832 ,p_effective_date => p_effective_date
833 );
834
835
836 -- Total credits.
837 --
838 if l_call_total_pools_flag
839 and not p_calculate_only_mode
840 then
841 for l_enrt_rslt_rec in c_rslt_pgm
842 (c_person_id => p_person_id
843 ,c_enrt_mthd_cd => p_enrt_mthd_cd
844 ,c_per_in_ler_id => p_per_in_ler_id
845 ,c_effective_date => p_effective_date
846 )
847 loop
848 --
849 ben_provider_pools.total_pools
850 (p_validate => FALSE
851 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id_pool
852 ,p_prtt_rt_val_id => l_prtt_rt_val_id_pool
853 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd_pool
854 ,p_acty_base_rt_id => l_acty_base_rt_id_pool
855 ,p_rt_strt_dt => l_rt_strt_dt_pool
856 ,p_rt_val => l_rt_val_pool
857 ,p_element_type_id => l_element_type_id_pool
858 ,p_person_id => p_person_id
859 ,p_per_in_ler_id => p_per_in_ler_id
860 ,p_enrt_mthd_cd => p_enrt_mthd_cd
861 ,p_effective_date => p_effective_date
862 ,p_business_group_id => p_business_group_id
863 ,p_pgm_id => l_enrt_rslt_rec.pgm_id
864 );
865 end loop;
866 --
867 end if;
868 --
869 hr_utility.set_location(' Leaving: ' ||l_proc , 10);
870 --
871 --
872 /*
873 ben_det_enrt_rates.end_prtt_rt_val
874 (p_person_id => p_person_id
875 ,p_per_in_ler_id => p_per_in_ler_id
876 ,p_enrt_mthd_cd =>p_enrt_mthd_cd
877 ,p_business_group_id => p_business_group_id
878 ,p_effective_date => p_effective_date
879 );
880 */
881 exception
882 --
883 when others then
884 hr_utility.set_location('Exception raised in ' || l_proc, 10);
885 raise;
886 --
887 end p_det_enrt_rates;
888 --
889 procedure end_prtt_rt_val
890 (p_person_id in number
891 ,p_per_in_ler_id in number
892 ,p_enrt_mthd_cd in varchar2
893 ,p_business_group_id in number
894 ,p_effective_date in date
895 )
896 is
897 --
898 cursor c_enrt_rslt
899 (c_person_id in number
900 ,c_enrt_mthd_cd in varchar2
901 ,c_per_in_ler_id in number
902 ,c_effective_date in date
903 )
904 is
905 select pen.prtt_enrt_rslt_id,
906 pen.pl_id,
907 pen.pgm_id,
908 pen.oipl_id,
909 pen.enrt_cvg_strt_dt,
910 pen.comp_lvl_cd
911 from ben_prtt_enrt_rslt_f pen
912 where pen.person_id = c_person_id
913 and pen.enrt_mthd_cd = c_enrt_mthd_cd
914 and pen.prtt_enrt_rslt_stat_cd is null
915 and pen.per_in_ler_id = c_per_in_ler_id
916 and enrt_cvg_thru_dt = hr_api.g_eot
917 and pen.comp_lvl_cd not in ('PLANIMP','PLANFC')
918 and c_effective_date
919 between pen.effective_start_date and pen.effective_end_date
920 and pen.effective_end_date = hr_api.g_eot
921 and -- start 4354929
922 ( EXISTS ( select null
923 from ben_ler_f ler,
924 ben_elig_per_elctbl_chc epe
925 where ler.ler_id = pen.ler_id
926 and (
927 ( ler.typ_cd = 'SCHEDDU'
928 and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
929 and epe.per_in_ler_id = c_per_in_ler_id )
930 or
931 ( ler.typ_cd <> 'SCHEDDU' and epe.per_in_ler_id = c_per_in_ler_id )
932 )--inner and
933 )--exists
934 ) -- end 4354929
935 order by pen.pgm_id;
936 --
937 cursor c_prtt_rt_val (p_prtt_enrt_rslt_id in number)
938 is
939 select prv.prtt_rt_val_id,
940 prv.acty_base_rt_id,
941 prv.rt_strt_dt,
942 prv.per_in_ler_id,
943 prv.object_version_number
944 from ben_prtt_rt_val prv
945 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
946 and prv.rt_end_dt = hr_api.g_eot
947 and prv.prtt_rt_val_stat_cd is null;
948 --
949 cursor c_elctbl_chc(v_enrt_rslt_id in number)
950 is
951 select epe.pl_id,
952 epe.oipl_id,
953 epe.elig_per_elctbl_chc_id,
954 epe.spcl_rt_pl_id,
955 epe.spcl_rt_oipl_id,
956 pel.acty_ref_perd_cd
957 from ben_elig_per_elctbl_chc epe,
958 ben_pil_elctbl_chc_popl pel,
959 ben_prtt_enrt_rslt_f pen
960 where epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
961 and epe.per_in_ler_id = p_per_in_ler_id
962 and pen.prtt_enrt_rslt_id=v_enrt_rslt_id
963 and nvl(pen.pgm_id,-1)=nvl(epe.pgm_id,-1)
964 and pen.pl_id=epe.pl_id
965 and nvl(pen.oipl_id,-1)=nvl(epe.oipl_id,-1)
966 and pen.prtt_enrt_rslt_stat_cd is null
967 and epe.bnft_prvdr_pool_id is null
968 and p_effective_date between
969 pen.effective_start_date and pen.effective_end_date;
970 --
971 --Bug#3272320 - modified join condition
972 cursor c_enrt_rt
973 (c_elig_per_elctbl_chc_id in number
974 ,c_acty_base_rt_id in number
975 )
976 is
977 select DECR_BNFT_PRVDR_POOL_ID
978 from ben_enrt_rt ecr
979 where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
980 and ecr.asn_on_enrt_flag = 'Y'
981 -- and ecr.prtt_rt_val_id = c_prtt_rt_val_id
982 and ecr.acty_base_rt_id = c_acty_base_rt_id
983 UNION
984 select DECR_BNFT_PRVDR_POOL_ID
985 from ben_enrt_bnft enb,
986 ben_enrt_rt ecr
987 where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
988 and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
989 --and ecr.prtt_rt_val_id = c_prtt_rt_val_id
990 and ecr.asn_on_enrt_flag = 'Y'
991 and ecr.acty_base_rt_id = c_acty_base_rt_id;
992 --
993 cursor c_prtt_enrt (p_pgm_id number) is
994 select prtt_enrt_rslt_id
995 from ben_elig_per_elctbl_chc epe
996 where epe.per_in_ler_id = p_per_in_ler_id
997 and epe.comp_lvl_cd = 'PLANFC'
998 and epe.pgm_id = p_pgm_id
999 and epe.business_group_id = p_business_group_id;
1000
1001 --
1002 cursor c_ldgr_exist(p_prtt_enrt_rslt_id number
1003 ,p_acty_base_rt_id number
1004 ,p_per_in_ler_id number) is
1005 select bpl.bnft_prvdd_ldgr_id,
1006 bpl.object_version_number
1007 from ben_bnft_prvdd_ldgr_f bpl,
1008 ben_per_in_ler pil
1009 where bpl.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1010 and bpl.acty_base_rt_id = p_acty_base_rt_id
1011 and bpl.used_val is not null
1012 and bpl.PRTT_RO_OF_UNUSD_AMT_FLAG = 'N'
1013 --and bpl.per_in_ler_id = p_per_in_ler_id
1014 and bpl.effective_end_date = hr_api.g_eot
1015 and bpl.per_in_ler_id = pil.per_in_ler_id
1016 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1017 and p_effective_date between
1018 bpl.effective_start_date and bpl.effective_end_date;
1019
1020 --
1021 cursor c_unrestricted is
1022 select 'Y'
1023 from ben_per_in_ler pil,
1024 ben_ler_f ler
1025 where pil.per_in_ler_id = p_per_in_ler_id
1026 and pil.ler_id = ler.ler_id
1027 and ler.typ_cd = 'SCHEDDU'
1028 and ler.business_group_id = p_business_group_id
1029 and p_effective_date between ler.effective_start_date
1030 and ler.effective_end_date;
1031 --
1032 l_unrestricted varchar2(30):= 'N';
1033 l_end_prtt_rt_val boolean;
1034 l_epe_rec c_elctbl_chc%rowtype;
1035 l_proc varchar2(2000) := g_package||'End_prtt_rt_val';
1036 l_enrt_rt varchar2(100);
1037 l_rt_end_dt date;
1038 l_dummy_date date;
1039 l_dummy_varchar varchar2(200);
1040 l_dummy_number number;
1041 l_DECR_BNFT_PRVDR_POOL_ID number;
1042 l_pgm_id number := 0;
1043 l_prtt_enrt_rslt_id number;
1044 l_ldgr_exist c_ldgr_exist%rowtype;
1045 l_effective_start_date date;
1046 l_effective_end_date date;
1047
1048 --
1049 begin
1050 --
1051 hr_utility.set_location('Entering'||l_proc,10);
1052 open c_unrestricted;
1053 fetch c_unrestricted into l_unrestricted;
1054 close c_unrestricted;
1055 --
1056 for l_enrt_rslt_rec in c_enrt_rslt
1057 (c_person_id => p_person_id
1058 ,c_enrt_mthd_cd => p_enrt_mthd_cd
1059 ,c_per_in_ler_id => p_per_in_ler_id
1060 ,c_effective_date => p_effective_date
1061 )
1062 loop
1063 --
1064 for l_prtt_rt_val in c_prtt_rt_val
1065 (l_enrt_rslt_rec.prtt_enrt_rslt_id)
1066 loop
1067 --
1068 l_end_prtt_rt_val := false;
1069 -- only in OSB per_in_ler_id will be same for more than one prtt_rt_val
1070 hr_utility.set_location('Inside Loop',11);
1071
1072
1073 --if l_unrestricted = 'Y' then
1074 open c_elctbl_chc(l_enrt_rslt_rec.prtt_enrt_rslt_id);
1075 fetch c_elctbl_chc into l_epe_rec;
1076 --
1077 if c_elctbl_chc%notfound then
1078 -- raise error
1079 close c_elctbl_chc;
1080 fnd_message.set_name('BEN','BEN_91491_NO_ELCTBL_CHC');
1081 fnd_message.set_token('PROC',l_proc);
1082 fnd_message.set_token('PERSON_ID',p_person_id);
1083 fnd_message.set_token('PRTT_ENRT_RSLT_ID',
1084 l_enrt_rslt_rec.prtt_enrt_rslt_id);
1085 fnd_message.set_token('PER_IN_LER_ID',p_per_in_ler_id);
1086 fnd_message.raise_error;
1087 --
1088 else
1089 --
1090 close c_elctbl_chc;
1091 --
1092 end if;
1093 --
1094 l_DECR_BNFT_PRVDR_POOL_ID := null;
1095 --Bug#3272320 - modified join condition
1096 open c_enrt_rt
1097 (c_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id
1098 ,c_acty_base_rt_id => l_prtt_rt_val.acty_base_rt_id
1099 );
1100 fetch c_enrt_rt into l_DECR_BNFT_PRVDR_POOL_ID;
1101 if c_enrt_rt%notfound then
1102 l_end_prtt_rt_val := TRUE;
1103 end if;
1104 close c_enrt_rt;
1105 --
1106 -- end if;
1107 /* else
1108 --
1109 open c_elctbl_chc(l_enrt_rslt_rec.prtt_enrt_rslt_id);
1110 fetch c_elctbl_chc into l_epe_rec;
1111 --
1112 if c_elctbl_chc%notfound then
1113 -- raise error
1114 close c_elctbl_chc;
1115 fnd_message.set_name('BEN','BEN_91491_NO_ELCTBL_CHC');
1116 fnd_message.set_token('PROC',l_proc);
1117 fnd_message.set_token('PERSON_ID',p_person_id);
1118 fnd_message.set_token('PRTT_ENRT_RSLT_ID',
1119 l_enrt_rslt_rec.prtt_enrt_rslt_id);
1120 fnd_message.set_token('PER_IN_LER_ID',p_per_in_ler_id);
1121 fnd_message.raise_error;
1122 --
1123 else
1124 --
1125 close c_elctbl_chc;
1126 --
1127 end if;
1128 l_end_prtt_rt_val := TRUE;
1129 end if;
1130 */
1131 --
1132 if l_pgm_id <> l_enrt_rslt_rec.pgm_id then
1133 --
1134 l_pgm_id := l_enrt_rslt_rec.pgm_id;
1135 --
1136 l_prtt_enrt_rslt_id := null;
1137 open c_prtt_enrt (l_enrt_rslt_rec.pgm_id);
1138 fetch c_prtt_enrt into l_prtt_enrt_rslt_id;
1139 close c_prtt_enrt;
1140 --
1141 -- hr_utility.set_location('Shell plan'||l_prtt_enrt_rslt_id,12);
1142
1143 end if;
1144 --
1145 -- hr_utility.set_location('Decr pool id'||l_DECR_BNFT_PRVDR_POOL_ID,13);
1146 -- check for flex program
1147 if l_prtt_enrt_rslt_id is not null and
1148 ((l_end_prtt_rt_val) or l_DECR_BNFT_PRVDR_POOL_ID is null) then
1149 -- delete the debit ledger entry as the application is end dated
1150 open c_ldgr_exist (l_prtt_enrt_rslt_id, l_prtt_rt_val.acty_base_rt_id,
1151 l_prtt_rt_val.per_in_ler_id);
1152 fetch c_ldgr_exist into l_ldgr_exist;
1153 if c_ldgr_exist%found then
1154 --
1155 ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
1156 p_bnft_prvdd_ldgr_id => l_ldgr_exist.bnft_prvdd_ldgr_id,
1157 p_effective_start_date => l_effective_start_date,
1158 p_effective_end_date => l_effective_end_date,
1159 p_object_version_number => l_ldgr_exist.object_version_number,
1160 p_effective_date => (p_effective_date - 1),
1161 p_datetrack_mode => hr_api.g_delete,
1162 p_business_group_id => p_business_group_id
1163 );
1164 --
1165 end if;
1166 close c_ldgr_exist;
1167 --
1168
1169 end if;
1170 --
1171
1172 if l_end_prtt_rt_val then
1173 -- end prtt_rt_val as the standard rate is not applicable any more
1174 ben_determine_date.rate_and_coverage_dates
1175 (p_which_dates_cd => 'R'
1176 ,p_business_group_id => p_business_group_id
1177 ,p_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id
1178 ,p_enrt_cvg_strt_dt => l_dummy_date
1179 ,p_enrt_cvg_strt_dt_cd => l_dummy_varchar
1180 ,p_enrt_cvg_strt_dt_rl => l_dummy_number
1181 ,p_rt_strt_dt => l_dummy_date
1182 ,p_rt_strt_dt_cd => l_dummy_varchar
1183 ,p_rt_strt_dt_rl => l_dummy_number
1184 ,p_enrt_cvg_end_dt => l_dummy_date
1185 ,p_enrt_cvg_end_dt_cd => l_dummy_varchar
1186 ,p_enrt_cvg_end_dt_rl => l_dummy_number
1187 ,p_rt_end_dt => l_rt_end_dt
1188 ,p_rt_end_dt_cd => l_dummy_varchar
1189 ,p_rt_end_dt_rl => l_dummy_number
1190 ,p_acty_base_rt_id => l_prtt_rt_val.acty_base_rt_id
1191 ,p_effective_date => p_effective_date);
1192 --
1193 hr_utility.set_location('prtt rt val id'||l_prtt_rt_val.prtt_rt_val_id,11);
1194 ben_prtt_rt_val_api.update_prtt_rt_val
1195 (p_prtt_rt_val_id => l_prtt_rt_val.prtt_rt_val_id
1196 ,p_rt_end_dt => l_rt_end_dt
1197 ,p_ended_per_in_ler_id => p_per_in_ler_id
1198 ,p_person_id => p_person_id
1199 ,p_business_group_id => p_business_group_id
1200 ,p_object_version_number => l_prtt_rt_val.object_version_number
1201 ,p_effective_date => p_effective_date
1202 );
1203
1204 end if;
1205 End loop; -- c_prtt_rt_val
1206 --
1207 End loop; -- c_prtt_enrt_rslt
1208 hr_utility.set_location('Leaving'||l_proc,10);
1209
1210 End ;
1211
1212 --
1213 procedure end_prtt_rt_val
1214 (p_prtt_enrt_rslt_id in number
1215 ,p_person_id in number
1216 ,p_per_in_ler_id in number
1217 ,p_business_group_id in number
1218 ,p_effective_date in date
1219 )
1220 is
1221 --
1222 cursor c_prtt_rt_val (p_prtt_enrt_rslt_id in number)
1223 is
1224 select prv.prtt_rt_val_id,
1225 prv.acty_base_rt_id,
1226 prv.rt_strt_dt,
1227 prv.per_in_ler_id,
1228 prv.object_version_number
1229 from ben_prtt_rt_val prv
1230 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1231 and prv.rt_end_dt = hr_api.g_eot
1232 and prv.prtt_rt_val_stat_cd is null;
1233 --
1234 cursor c_elctbl_chc(v_enrt_rslt_id in number)
1235 is
1236 select epe.pl_id,
1237 epe.oipl_id,
1238 epe.elig_per_elctbl_chc_id,
1239 epe.spcl_rt_pl_id,
1240 epe.spcl_rt_oipl_id,
1241 pel.acty_ref_perd_cd
1242 from ben_elig_per_elctbl_chc epe,
1243 ben_pil_elctbl_chc_popl pel,
1244 ben_prtt_enrt_rslt_f pen
1245 where epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
1246 and epe.per_in_ler_id = p_per_in_ler_id
1247 and pen.prtt_enrt_rslt_id=v_enrt_rslt_id
1248 and nvl(pen.pgm_id,-1)=nvl(epe.pgm_id,-1)
1249 and pen.pl_id=epe.pl_id
1250 and nvl(pen.oipl_id,-1)=nvl(epe.oipl_id,-1)
1251 and pen.prtt_enrt_rslt_stat_cd is null
1252 and epe.bnft_prvdr_pool_id is null
1253 and p_effective_date between
1254 pen.effective_start_date and pen.effective_end_date;
1255 --
1256 --Bug#3272320 - modified join condition
1257 cursor c_enrt_rt
1258 (c_elig_per_elctbl_chc_id in number
1259 ,c_acty_base_rt_id in number
1260 )
1261 is
1262 select DECR_BNFT_PRVDR_POOL_ID
1263 from ben_enrt_rt ecr
1264 where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
1265 and ecr.asn_on_enrt_flag = 'Y'
1266 and ecr.acty_base_rt_id = c_acty_base_rt_id
1267 UNION
1268 select DECR_BNFT_PRVDR_POOL_ID
1269 from ben_enrt_bnft enb,
1270 ben_enrt_rt ecr
1271 where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
1272 and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
1273 and ecr.acty_base_rt_id = c_acty_base_rt_id
1274 and ecr.asn_on_enrt_flag = 'Y';
1275 --
1276 cursor c_rslt_pgm is
1277 select pen.pgm_id,
1278 pen.enrt_mthd_cd
1279 from ben_prtt_enrt_rslt_f pen
1280 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1281 and pen.prtt_enrt_rslt_stat_cd is null;
1282 --
1283 cursor c_prtt_enrt (p_pgm_id number) is
1284 select prtt_enrt_rslt_id
1285 from ben_elig_per_elctbl_chc epe
1286 where epe.per_in_ler_id = p_per_in_ler_id
1287 and epe.comp_lvl_cd = 'PLANFC'
1288 and epe.pgm_id = p_pgm_id
1289 and epe.business_group_id = p_business_group_id;
1290
1291 --
1292 cursor c_ldgr_exist(p_prtt_enrt_rslt_id number
1293 ,p_acty_base_rt_id number
1294 ,p_per_in_ler_id number) is
1295 select bpl.bnft_prvdd_ldgr_id,
1296 bpl.object_version_number
1297 from ben_bnft_prvdd_ldgr_f bpl,
1298 ben_per_in_ler pil
1299 where bpl.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1300 and bpl.acty_base_rt_id = p_acty_base_rt_id
1301 and bpl.used_val is not null
1302 and bpl.PRTT_RO_OF_UNUSD_AMT_FLAG = 'N'
1303 --and bpl.per_in_ler_id = p_per_in_ler_id
1304 and bpl.effective_end_date = hr_api.g_eot
1305 and bpl.per_in_ler_id = pil.per_in_ler_id
1306 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1307 and p_effective_date between
1308 bpl.effective_start_date and bpl.effective_end_date;
1309 --
1310 cursor c_unrestricted is
1311 select 'Y'
1312 from ben_per_in_ler pil,
1313 ben_ler_f ler
1314 where pil.per_in_ler_id = p_per_in_ler_id
1315 and pil.ler_id = ler.ler_id
1316 and ler.typ_cd = 'SCHEDDU'
1317 and ler.business_group_id = p_business_group_id
1318 and p_effective_date between ler.effective_start_date
1319 and ler.effective_end_date;
1320 --
1321 l_unrestricted varchar2(30):= 'N';
1322 l_end_prtt_rt_val boolean;
1323 l_epe_rec c_elctbl_chc%rowtype;
1324 l_proc varchar2(2000) := g_package||'End_prtt_rt_val';
1325 l_enrt_rt varchar2(100);
1326 l_rt_end_dt date;
1327 l_dummy_date date;
1328 l_dummy_varchar varchar2(200);
1329 l_dummy_number number;
1330 l_DECR_BNFT_PRVDR_POOL_ID number;
1331 l_pgm_id number := 0;
1332 l_prtt_enrt_rslt_id number;
1333 l_ldgr_exist c_ldgr_exist%rowtype;
1334 l_effective_start_date date;
1335 l_effective_end_date date;
1336 --
1337 l_prtt_enrt_rslt_id_pool number;
1338 l_prtt_rt_val_id_pool number;
1339 l_acty_ref_perd_cd_pool varchar2(30);
1340 l_acty_base_rt_id_pool number;
1341 l_rt_strt_dt_pool date;
1342 l_rt_val_pool number;
1343 l_element_type_id_pool number;
1344 L_BNFT_PRVDD_LDGR_ID number;
1345 l_enrt_mthd_cd varchar2(100);
1346
1347 --
1348 begin
1349 --
1350 hr_utility.set_location('Entering'||l_proc,11);
1351 open c_unrestricted;
1352 fetch c_unrestricted into l_unrestricted;
1353 close c_unrestricted;
1354 --
1355 open c_rslt_pgm;
1356 fetch c_rslt_pgm into l_pgm_id, l_enrt_mthd_cd;
1357 close c_rslt_pgm;
1358 --
1359 open c_prtt_enrt (l_pgm_id);
1360 fetch c_prtt_enrt into l_prtt_enrt_rslt_id;
1361 close c_prtt_enrt;
1362 --
1363 for l_prtt_rt_val in c_prtt_rt_val
1364 (p_prtt_enrt_rslt_id)
1365 loop
1366 --
1367 l_end_prtt_rt_val := false;
1368
1369 open c_elctbl_chc(p_prtt_enrt_rslt_id);
1370 fetch c_elctbl_chc into l_epe_rec;
1371 --
1372 if c_elctbl_chc%notfound then
1373 -- raise error
1374 close c_elctbl_chc;
1375 fnd_message.set_name('BEN','BEN_91491_NO_ELCTBL_CHC');
1376 fnd_message.set_token('PROC',l_proc);
1377 fnd_message.set_token('PRTT_ENRT_RSLT_ID',
1378 p_prtt_enrt_rslt_id);
1379 fnd_message.set_token('PER_IN_LER_ID',p_per_in_ler_id);
1380 fnd_message.raise_error;
1381 --
1382 else
1383 --
1384 close c_elctbl_chc;
1385 --
1386 end if;
1387 --
1388 l_DECR_BNFT_PRVDR_POOL_ID := null;
1389 --Bug#3272320 - modified join condition
1390 open c_enrt_rt
1391 (c_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id
1392 ,c_acty_base_rt_id => l_prtt_rt_val.acty_base_rt_id
1393 );
1394 fetch c_enrt_rt into l_DECR_BNFT_PRVDR_POOL_ID;
1395 if c_enrt_rt%notfound then
1396 l_end_prtt_rt_val := TRUE;
1397 end if;
1398 close c_enrt_rt;
1399 --
1400 -- check for flex program
1401 if l_prtt_enrt_rslt_id is not null and
1402 ((l_end_prtt_rt_val) or l_DECR_BNFT_PRVDR_POOL_ID is null) then
1403 -- delete the debit ledger entry as the application is end dated
1404 open c_ldgr_exist (l_prtt_enrt_rslt_id, l_prtt_rt_val.acty_base_rt_id,
1405 l_prtt_rt_val.per_in_ler_id);
1406 fetch c_ldgr_exist into l_ldgr_exist;
1407 if c_ldgr_exist%found then
1408 --
1409 ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
1410 p_bnft_prvdd_ldgr_id => l_ldgr_exist.bnft_prvdd_ldgr_id,
1411 p_effective_start_date => l_effective_start_date,
1412 p_effective_end_date => l_effective_end_date,
1413 p_object_version_number => l_ldgr_exist.object_version_number,
1414 p_effective_date => (p_effective_date - 1),
1415 p_datetrack_mode => hr_api.g_delete,
1416 p_business_group_id => p_business_group_id
1417 );
1418 --
1419 ben_provider_pools.total_pools
1420 (p_validate => FALSE
1421 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id_pool
1422 ,p_prtt_rt_val_id => l_prtt_rt_val_id_pool
1423 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd_pool
1424 ,p_acty_base_rt_id => l_acty_base_rt_id_pool
1425 ,p_rt_strt_dt => l_rt_strt_dt_pool
1426 ,p_rt_val => l_rt_val_pool
1427 ,p_element_type_id => l_element_type_id_pool
1428 ,p_person_id => p_person_id
1429 ,p_per_in_ler_id => p_per_in_ler_id
1430 ,p_enrt_mthd_cd => l_enrt_mthd_cd
1431 ,p_effective_date => p_effective_date
1432 ,p_business_group_id => p_business_group_id
1433 ,p_pgm_id => l_pgm_id
1434 );
1435 --
1436 end if;
1437 close c_ldgr_exist;
1438 --
1439
1440 end if;
1441 --
1442
1443 if l_end_prtt_rt_val then
1444 -- end prtt_rt_val as the standard rate is not applicable any more
1445 ben_determine_date.rate_and_coverage_dates
1446 (p_which_dates_cd => 'R'
1447 ,p_business_group_id => p_business_group_id
1448 ,p_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id
1449 ,p_enrt_cvg_strt_dt => l_dummy_date
1450 ,p_enrt_cvg_strt_dt_cd => l_dummy_varchar
1451 ,p_enrt_cvg_strt_dt_rl => l_dummy_number
1452 ,p_rt_strt_dt => l_dummy_date
1453 ,p_rt_strt_dt_cd => l_dummy_varchar
1454 ,p_rt_strt_dt_rl => l_dummy_number
1455 ,p_enrt_cvg_end_dt => l_dummy_date
1456 ,p_enrt_cvg_end_dt_cd => l_dummy_varchar
1457 ,p_enrt_cvg_end_dt_rl => l_dummy_number
1458 ,p_rt_end_dt => l_rt_end_dt
1459 ,p_rt_end_dt_cd => l_dummy_varchar
1460 ,p_rt_end_dt_rl => l_dummy_number
1461 ,p_acty_base_rt_id => l_prtt_rt_val.acty_base_rt_id
1462 ,p_effective_date => p_effective_date);
1463 --
1464 ben_prtt_rt_val_api.update_prtt_rt_val
1465 (p_prtt_rt_val_id => l_prtt_rt_val.prtt_rt_val_id
1466 ,p_rt_end_dt => l_rt_end_dt
1467 ,p_ended_per_in_ler_id => p_per_in_ler_id
1468 ,p_person_id => p_person_id
1469 ,p_business_group_id => p_business_group_id
1470 ,p_object_version_number => l_prtt_rt_val.object_version_number
1471 ,p_effective_date => p_effective_date
1472 );
1473
1474 end if;
1475 End loop;
1476 hr_utility.set_location('Leaving'||l_proc,12);
1477
1478 end;
1479 --
1480 procedure det_enrt_rates_erl
1481 (p_person_id in number
1482 ,p_per_in_ler_id in number
1483 ,p_enrt_mthd_cd in varchar2
1484 ,p_business_group_id in number
1485 ,p_effective_date in date
1486 ,p_elig_per_elctbl_chc_id in number
1487 ,p_fonm_cvg_strt_dt in date default null
1488 ,p_prtt_enrt_rslt_id in number
1489 ,p_pgm_id in number
1490 ,p_pl_id in number
1491 ,p_oipl_id in number
1492 ,p_enrt_cvg_strt_dt in date
1493 ,p_acty_ref_perd_cd in varchar2
1494 )
1495 is
1496 --
1497
1498 cursor c_enrt_rt
1499 (c_elig_per_elctbl_chc_id in number
1500 ,c_prtt_enrt_rslt_id in number
1501 )
1502 is
1503 select ecr.prtt_rt_val_id,
1504 ecr.enrt_rt_id,
1505 ecr.val,
1506 ecr.ann_val,
1507 ecr.rt_mlt_cd,
1508 ecr.acty_typ_cd,
1509 ecr.rt_strt_dt,
1510 ecr.object_version_number, -----Bug 8214477
1511 ecr.acty_base_rt_id
1512 from ben_enrt_rt ecr
1513 where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
1514 and ecr.SPCL_RT_ENRT_RT_ID is null
1515 and ecr.entr_val_at_enrt_flag = 'N'
1516 and ecr.asn_on_enrt_flag = 'Y'
1517 and ecr.rt_mlt_cd = 'ERL'
1518 UNION
1519 select ecr.prtt_rt_val_id,
1520 ecr.enrt_rt_id,
1521 ecr.val,
1522 ecr.ann_val,
1523 ecr.rt_mlt_cd,
1524 ecr.acty_typ_cd,
1525 ecr.rt_strt_dt,
1526 ecr.object_version_number, -----Bug 8214477
1527 ecr.acty_base_rt_id
1528 from ben_enrt_bnft enb,
1529 ben_enrt_rt ecr
1530 where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
1531 and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
1532 and enb.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1533 and ecr.SPCL_RT_ENRT_RT_ID is null
1534 and ecr.entr_val_at_enrt_flag = 'N'
1535 and ecr.asn_on_enrt_flag = 'Y'
1536 and ecr.rt_mlt_cd = 'ERL';
1537 --
1538 l_use_enrt_rec c_enrt_rt%rowtype;
1539 l_prv_rt_val number;
1540 l_prv_ann_rt_val number;
1541 l_prtt_rt_val_id number;
1542 --
1543 -----Bug 8214477
1544 cursor c_prv(p_prtt_enrt_rslt_id number)
1545 is
1546 select prtt_rt_val_id, rt_strt_dt
1547 from ben_prtt_rt_val prv, ben_per_in_ler pil
1548 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1549 and prv.rt_end_dt = hr_api.g_eot
1550 and prv.per_in_ler_id = pil.per_in_ler_id
1551 and prv.per_in_ler_id = p_per_in_ler_id --------- Bug 8342612
1552 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD');
1553
1554 l_prv_rec c_prv%rowtype;
1555 -----Bug 8214477
1556 begin
1557 --
1558 for l_enrt_rt_rec in c_enrt_rt
1559 (c_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1560 ,c_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1561 ) loop
1562 --
1563 --
1564 l_use_enrt_rec := l_enrt_rt_rec;
1565 --
1566 if p_fonm_cvg_strt_dt is not null then
1567 -----Bug 8214477
1568 l_prv_rec := NULL;
1569 open c_prv(p_prtt_enrt_rslt_id);
1570 fetch c_prv into l_prv_rec;
1571 close c_prv;
1572
1573 if l_prv_rec.rt_strt_dt is not NULL
1574 and l_enrt_rt_rec.rt_strt_dt <> l_prv_rec.rt_strt_dt then
1575 ben_enrollment_rate_api.update_enrollment_rate(
1576 p_enrt_rt_id => l_enrt_rt_rec.enrt_rt_id,
1577 p_rt_strt_dt => l_prv_rec.rt_strt_dt,
1578 p_object_version_number => l_enrt_rt_rec.object_version_number,
1579 p_effective_date => p_effective_date
1580 );
1581
1582 l_enrt_rt_rec.rt_strt_dt := l_prv_rec.rt_strt_dt;
1583
1584 end if;
1585 ------Bug 8214477
1586 ben_manage_life_events.fonm := 'Y';
1587 ben_manage_life_events.g_fonm_rt_strt_dt := l_enrt_rt_rec.rt_strt_dt;
1588 ben_manage_life_events.g_fonm_cvg_strt_dt := p_fonm_cvg_strt_dt;
1589 else
1590 ben_manage_life_events.fonm := 'N';
1591 ben_manage_life_events.g_fonm_rt_strt_dt := null;
1592 ben_manage_life_events.g_fonm_cvg_strt_dt := null;
1593 end if;
1594
1595 ben_election_information.election_rate_information
1596 (p_enrt_mthd_cd => p_enrt_mthd_cd
1597 ,p_effective_date => p_effective_date
1598 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1599 ,p_per_in_ler_id => p_per_in_ler_id
1600 ,p_person_id => p_person_id
1601 ,p_pgm_id => p_pgm_id
1602 ,p_pl_id => p_pl_id
1603 ,p_oipl_id => p_oipl_id
1604 ,p_enrt_rt_id => l_use_enrt_rec.enrt_rt_id
1605 ,p_prtt_rt_val_id => l_prtt_rt_val_id
1606 ,p_rt_val => l_use_enrt_rec.val
1607 ,p_ann_rt_val => l_use_enrt_rec.ann_val
1608 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
1609 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
1610 ,p_datetrack_mode => null
1611 ,p_business_group_id => p_business_group_id
1612 --
1613 ,p_prv_rt_val => l_prv_rt_val
1614 ,p_prv_ann_rt_val => l_prv_ann_rt_val
1615 );
1616 --
1617 --
1618 end loop;
1619
1620 End;
1621 end ben_det_enrt_rates;