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