[Home] [Help]
PACKAGE BODY: APPS.BEN_DET_IMPUTED_INCOME
Source
1 PACKAGE body ben_det_imputed_income as
2 /* $Header: bendeimp.pkb 120.10.12020000.2 2012/09/20 09:58:20 usaraswa ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_det_imputed_income.';
7 --
8 /*
9 8716870: Imputed Income Enhancement:
10 If we de-enroll from a plan subject to imputed income, then before re-evaluating the imputed income rate, any
11 rate starting before the earliest coverage start date of plan subject to imputed income should be deleted, because
12 it would be corresponding to the plan that got de-enrolled. procedure delete_past_imp is used for this purpose
13 */
14 procedure delete_past_imp(p_person_id number,
15 p_per_in_ler_id number,
16 p_business_group_id number,
17 p_effective_date date,
18 p_erlst_cvg_strt date,
19 p_imptd_incm_calc_cd varchar2)
20 is
21
22 --cursor to fetch imputed income plans
23 cursor c_imp_inc_plan(p_imptd_incm_calc_cd varchar2, p_business_group_id number, p_effective_date date)
24 is
25 select pln.pl_id
26 from ben_pl_f pln
27 where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
28 and pln.pl_stat_cd = 'A'
29 and pln.business_group_id = p_business_group_id
30 and p_effective_date between pln.effective_start_date and pln.effective_end_date;
31
32 l_pl_id number;
33
34 -- cursor to fetch rates starting before earliest coverage start date of plan subject to imputed income
35 cursor c_del_imp_inc_rt(p_per_in_ler_id number)
36 is
37 select prv.*
38 from ben_prtt_rt_val prv, ben_per_in_ler pil
39 where pil.person_id = p_person_id
40 and prv.per_in_ler_id = p_per_in_ler_id
41 and prv.per_in_ler_id = pil.per_in_ler_id
42 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
43 and prv.prtt_enrt_rslt_id in (select pen.prtt_enrt_rslt_id
44 from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
45 where pil.person_id = p_person_id
46 and pen.per_in_ler_id = pil.per_in_ler_id
47 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
48 and pen.pl_id = l_pl_id
49 and pen.per_in_ler_id = p_per_in_ler_id
50 and pen.prtt_enrt_rslt_stat_cd is NULL)
51 and prv.rt_strt_dt < p_erlst_cvg_strt
52 order by prv.rt_strt_dt desc ;
53
54 type l_imp_inc_rt_tab_type is table of c_del_imp_inc_rt%rowtype index by pls_integer;
55 l_imp_inc_rt_tab l_imp_inc_rt_tab_type;
56
57 l_proc varchar2(72) := g_package||'delete_past_imp';
58
59 BEGIN
60
61 hr_utility.set_location(' Entering: '||l_proc , 11);
62
63 -- get the imputed income plan
64 open c_imp_inc_plan(p_imptd_incm_calc_cd, p_business_group_id, p_effective_date);
65 fetch c_imp_inc_plan into l_pl_id;
66 close c_imp_inc_plan;
67
68 -- Now, we need to pick up the PRVs starting before the above earliest coverage start date of plan subject to imp inc
69 hr_utility.set_location (' l_pl_id '||l_pl_id, 2);
70 hr_utility.set_location (' p_per_in_ler_id '||p_per_in_ler_id, 2);
71
72 open c_del_imp_inc_rt (p_per_in_ler_id);
73 fetch c_del_imp_inc_rt bulk collect into l_imp_inc_rt_tab;
74 close c_del_imp_inc_rt;
75
76 hr_utility.set_location('l_imp_inc_rt_tab.count: '||l_imp_inc_rt_tab.count,6080);
77
78 if l_imp_inc_rt_tab.count > 0 then
79 for r in l_imp_inc_rt_tab.first..l_imp_inc_rt_tab.last
80 loop
81 hr_utility.set_location (' delete past rate prv', 2);
82 hr_utility.set_location (' l_imp_inc_rt_tab(r).prtt_rt_val_id '||l_imp_inc_rt_tab(r).prtt_rt_val_id, 2);
83
84 -- Nullify the prv_id on enrt_rt
85 update ben_enrt_rt set prtt_rt_val_id = NULL
86 where prtt_rt_val_id = nvl(l_imp_inc_rt_tab(r).prtt_rt_val_id,-1);
87
88 hr_utility.set_location (' l_imp_inc_rt_tab(r).rt_strt_dt '||l_imp_inc_rt_tab(r).rt_strt_dt, 2);
89 -- delete the rate
90 ben_prtt_rt_val_api.delete_prtt_rt_val
91 (p_validate => false
92 ,p_prtt_rt_val_id => l_imp_inc_rt_tab(r).prtt_rt_val_id
93 ,p_enrt_rt_id => NULL
94 ,p_person_id => p_person_id
95 ,p_business_group_id => p_business_group_id
96 ,p_object_version_number => l_imp_inc_rt_tab(r).object_version_number
97 ,p_effective_date => l_imp_inc_rt_tab(r).rt_strt_dt);
98 end loop;
99 end if;
100 hr_utility.set_location(' Leaving: '||l_proc , 20);
101 END delete_past_imp;
102 --
103 /* 8716870: procedure delete_imp_inc is addded to delete the future rate and enrollment for imputed shell plan */
104
105 procedure delete_imp_inc(p_person_id number,
106 p_per_in_ler_id number,
107 p_business_group_id number,
108 p_effective_date date,
109 p_imptd_incm_calc_cd varchar2)
110 is
111
112 l_proc varchar2(60) := g_package||'.delete_imp_inc';
113 l_zap boolean;
114 l_delete boolean;
115 l_future_change boolean;
116 l_delete_next_change boolean;
117 l_mode varchar2(20);
118 l_object_version_number number;
119 l_effective_start_date date;
120 l_effective_end_date date;
121 desc_idx pls_integer;
122 l_pl_id number;
123
124
125 --cursor to fetch imputed income plans
126 cursor c_imp_inc_plan(p_imptd_incm_calc_cd varchar2, p_business_group_id number, p_effective_date date)
127 is
128 select pln.pl_id
129 from ben_pl_f pln
130 where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
131 and pln.pl_stat_cd = 'A'
132 and pln.business_group_id = p_business_group_id
133 and p_effective_date between pln.effective_start_date and pln.effective_end_date;
134
135 -- cursor to fetch imputed income enrollments for deletion
136 cursor c_del_imp_inc_enrt(p_per_in_ler_id number)
137 is
138 select pen.*
139 from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
140 where pil.person_id = p_person_id
141 and pen.per_in_ler_id = p_per_in_ler_id
142 and pen.per_in_ler_id = pil.per_in_ler_id
143 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
144 and pen.pl_id = l_pl_id
145 and pen.enrt_cvg_strt_dt > p_effective_date
146 order by pen.effective_start_date, pen.enrt_cvg_strt_dt desc ;
147 --
148 -- Bug 14559387 If the coverage start date of the imputed come plan is after the rate start date then
149 -- Enrollment will be picked for deletion but rate record will not be picked. To make enrollment record and
150 -- rate record in ben_prtt_rt_val to be sinc following code changes are made.
151 cursor c_del_imp_inc_rt(p_per_in_ler_id number)
152 is
153 select prv.*
154 from ben_prtt_rt_val prv, ben_per_in_ler pil
155 where pil.person_id = p_person_id
156 and prv.per_in_ler_id = p_per_in_ler_id
157 and prv.per_in_ler_id = pil.per_in_ler_id
158 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
159 and prv.prtt_enrt_rslt_id in (select pen.prtt_enrt_rslt_id
160 from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
161 where pil.person_id = p_person_id
162 and pen.per_in_ler_id = pil.per_in_ler_id
163 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
164 and pen.pl_id = l_pl_id
165 and pen.per_in_ler_id = p_per_in_ler_id
166 and pen.enrt_cvg_strt_dt > p_effective_date --Bug 14559387
167 and pen.prtt_enrt_rslt_stat_cd is NULL)
168 -- and prv.rt_strt_dt > p_effective_date
169 and prv.rt_strt_dt > = p_effective_date --Bug 14559387
170 order by prv.rt_strt_dt desc ;
171
172 type l_imp_inc_rt_tab_type is table of c_del_imp_inc_rt%rowtype index by pls_integer;
173 l_imp_inc_rt_tab l_imp_inc_rt_tab_type;
174 --
175 cursor c_current_epe_pen (p_prtt_enrt_rslt_id number, p_per_in_ler_id number, p_effective_date date)
176 is
177 select 1
178 from ben_prtt_enrt_rslt_f pen
179 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
180 and pen.per_in_ler_id <> p_per_in_ler_id
181 and p_effective_date between pen.effective_start_date and pen.effective_end_date
182 and pen.enrt_cvg_thru_dt = hr_api.g_eot;
183
184 l_current_epe_pen c_current_epe_pen%rowtype;
185
186 --
187 cursor c_chk_rslt_status (p_prtt_enrt_rslt_id number, p_per_in_ler_id number)
188 is
189 select prtt_enrt_rslt_id,per_in_ler_id,pl_typ_id,pl_id
190 from ben_prtt_enrt_rslt_f
191 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
192 and per_in_ler_id = p_per_in_ler_id
193 and prtt_enrt_rslt_Stat_cd is NULL;
194
195 l_rslt_status c_chk_rslt_status%rowtype;
196
197
198 --
199 type l_imp_inc_enrt_tab_type is table of c_del_imp_inc_enrt%rowtype index by pls_integer;
200 l_imp_inc_enrt_tab l_imp_inc_enrt_tab_type;
201 l_imp_inc_enrt_tab_prior l_imp_inc_enrt_tab_type;
202 l_delrec c_del_imp_inc_enrt%rowtype;
203 --
204 cursor c_del_imp_inc_enrt_prior(p_prtt_enrt_rslt_id number,
205 p_effective_date date)
206 is
207 select pen.*
208 from ben_prtt_enrt_rslt_f pen
209 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
210 and p_effective_date between pen.effective_start_date and pen.effective_end_date
211 and pen.prtt_enrt_rslt_stat_cd is NULL
212 order by pen.prtt_enrt_rslt_id, pen.effective_start_date desc;
213 --
214 cursor c_corr_result_exist (l_per_in_ler_id number ,
215 l_prtt_enrt_rslt_id number ) is
216 select lcnr.*
217 from BEN_LE_CLSN_N_RSTR lcnr
218 where lcnr.bkup_tbl_id = l_prtt_enrt_rslt_id
219 and lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
220 and lcnr.enrt_cvg_thru_dt = hr_api.g_eot
221 and lcnr.Per_in_ler_ended_id = l_per_in_ler_id;
222
223 l_corr_pen_rec c_corr_result_exist%rowtype;
224 --
225 begin
226
227 hr_utility.set_location('Entering '||l_proc,6054);
228 hr_utility.set_location('p_effective_date: '||p_effective_date,6062);
229 hr_utility.set_location('p_imptd_incm_calc_cd: '||p_imptd_incm_calc_cd,6063);
230 hr_utility.set_location('p_per_in_ler_id: '||p_per_in_ler_id,6064);
231
232
233 -- get the imputed income plan
234 open c_imp_inc_plan(p_imptd_incm_calc_cd, p_business_group_id, p_effective_date);
235 fetch c_imp_inc_plan into l_pl_id;
236 close c_imp_inc_plan;
237
238 --delete rates first
239 hr_utility.set_location (' l_pl_id '||l_pl_id, 2);
240
241 open c_del_imp_inc_rt (p_per_in_ler_id);
242 fetch c_del_imp_inc_rt bulk collect into l_imp_inc_rt_tab;
243 close c_del_imp_inc_rt;
244
245 hr_utility.set_location('l_imp_inc_rt_tab.count: '||l_imp_inc_rt_tab.count,6080);
246
247 if l_imp_inc_rt_tab.count > 0 then
248 for r in l_imp_inc_rt_tab.first..l_imp_inc_rt_tab.last
249 loop
250 hr_utility.set_location (' delete future rate prv', 2);
251 hr_utility.set_location (' l_imp_inc_rt_tab(r).prtt_rt_val_id '||l_imp_inc_rt_tab(r).prtt_rt_val_id, 2);
252
253 -- Nullify the prv_id on enrt_rt
254 update ben_enrt_rt set prtt_rt_val_id = NULL
255 where prtt_rt_val_id = nvl(l_imp_inc_rt_tab(r).prtt_rt_val_id,-1);
256
257 hr_utility.set_location (' l_imp_inc_rt_tab(r).rt_strt_dt '||l_imp_inc_rt_tab(r).rt_strt_dt, 2);
258 -- delete the rate
259 ben_prtt_rt_val_api.delete_prtt_rt_val
260 (p_validate => false
261 ,p_prtt_rt_val_id => l_imp_inc_rt_tab(r).prtt_rt_val_id
262 ,p_enrt_rt_id => NULL
263 ,p_person_id => p_person_id
264 ,p_business_group_id => p_business_group_id
265 ,p_object_version_number => l_imp_inc_rt_tab(r).object_version_number
266 ,p_effective_date => l_imp_inc_rt_tab(r).rt_strt_dt);
267 end loop;
268 end if;
269
270
271 -- delete the existing imputed income enrollments
272 open c_del_imp_inc_enrt (p_per_in_ler_id);
273 fetch c_del_imp_inc_enrt bulk collect into l_imp_inc_enrt_tab;
274 close c_del_imp_inc_enrt;
275
276 hr_utility.set_location('l_imp_inc_enrt_tab.count: '||l_imp_inc_enrt_tab.count,6080);
277
278 if l_imp_inc_enrt_tab.count > 0 then
279 for r in l_imp_inc_enrt_tab.first..l_imp_inc_enrt_tab.last
280 loop
281 --
282 --Need to see if the imputed income enrollments existed 1 day prior, since we need the
283 --previous record in order to delete future records.
284 --
285 hr_utility.set_location (' delete imputed enrollment', 2);
286 hr_utility.set_location (' l_imp_inc_enrt_tab(r).prtt_enrt_rslt_id '||l_imp_inc_enrt_tab(r).prtt_enrt_rslt_id, 3);
287 hr_utility.set_location (' l_imp_inc_enrt_tab(r).effective_start_date '||l_imp_inc_enrt_tab(r).effective_start_date, 3);
288
289 open c_del_imp_inc_enrt_prior(p_prtt_enrt_rslt_id => l_imp_inc_enrt_tab(r).prtt_enrt_rslt_id,
290 p_effective_date => l_imp_inc_enrt_tab(r).effective_start_date - 1);
291 fetch c_del_imp_inc_enrt_prior bulk collect into l_imp_inc_enrt_tab_prior;
292 close c_del_imp_inc_enrt_prior;
293
294
295 hr_utility.set_location('l_imp_inc_enrt_tab.count '||l_imp_inc_enrt_tab.count,6685);
296
297
298 if l_imp_inc_enrt_tab_prior.count > 0 then
299 --
300 hr_utility.set_location('Prior enrollment exists: ',6685);
301 l_zap := null;
302 l_delete := null;
303 l_future_change := null;
304 l_delete_next_change := null;
305 l_mode := null;
306
307 dt_api.find_dt_del_modes
308 (p_effective_date => l_imp_inc_enrt_tab_prior(l_imp_inc_enrt_tab_prior.first).effective_start_date,
309 p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
310 p_base_key_column => 'PRTT_ENRT_RSLT_ID',
311 p_base_key_value => l_imp_inc_enrt_tab_prior(l_imp_inc_enrt_tab_prior.first).prtt_enrt_rslt_id,
312 p_zap => l_zap,
313 p_delete => l_delete,
314 p_future_change => l_future_change,
315 p_delete_next_change => l_delete_next_change);
316
317 if l_future_change then
318 l_mode := hr_api.g_future_change;
319 --
320 elsif l_delete_next_change then
321 --
322 l_mode := hr_api.g_delete_next_change;
323
324 end if;
325 hr_utility.set_location (' l_mode '||l_mode, 5);
326 l_delrec := l_imp_inc_enrt_tab_prior(l_imp_inc_enrt_tab_prior.first);
327 l_imp_inc_enrt_tab_prior.delete;
328
329 else
330 hr_utility.set_location('Else - no prior imputed enrollment exists ',6685);
331 l_delrec := l_imp_inc_enrt_tab(r);
332 l_mode := hr_api.g_zap;
333 end if;
334 --
335
336
337 hr_utility.set_location('----------------------------------------------------------',6690);
338 hr_utility.set_location('l_mode: '||l_mode,5363);
339 hr_utility.set_location('l_delrec.prtt_enrt_rslt_id: '||l_delrec.prtt_enrt_rslt_id,5363);
340 hr_utility.set_location('l_delrec.effective_start_date: '||l_delrec.effective_start_date,5363);
341 hr_utility.set_location('l_delrec.effective_end_date: '||l_delrec.effective_end_date,5363);
342
343
344 --
345 l_object_version_number := l_delrec.object_version_number;
346
347
348 if l_mode = hr_api.g_zap then
349 -- check if corr pen rec exists, if yes no zap, just update the corr rec pil_id and ler_id
350 open c_corr_result_exist (l_imp_inc_enrt_tab(r).per_in_ler_id, l_imp_inc_enrt_tab(r).prtt_enrt_rslt_id);
351 fetch c_corr_result_exist into l_corr_pen_rec;
352
353 if c_corr_result_exist%found then
354
355 hr_utility.set_location(' corr pen rec exists, no zap, upd prev pil_id on pen',6695);
356
357 -- as corr rec exists, upd the ler_id, pil_id of corr rec on the rslt
358 update ben_prtt_enrt_rslt_f
359 set ler_id = l_corr_pen_rec.ler_id,
360 per_in_ler_id = l_corr_pen_rec.per_in_ler_id
361 where prtt_enrt_rslt_id = l_corr_pen_rec.bkup_tbl_id
362 and effective_start_date = l_corr_pen_rec.effective_start_date
363 and exists (select 1
364 from ben_per_in_ler
365 where per_in_ler_id = l_corr_pen_rec.per_in_ler_id
366 and per_in_ler_stat_cd not in ('BCKDT','VOIDD'))
367 and business_group_id = p_business_group_id
368 and person_id = l_corr_pen_rec.person_id;
369
370 if sql%rowcount > 0 then
371 delete from BEN_LE_CLSN_N_RSTR
372 where bkup_tbl_id = nvl(l_corr_pen_rec.bkup_tbl_id,-1)
373 and Per_in_ler_ended_id = nvl(l_imp_inc_enrt_tab(r).per_in_ler_id,-1)
374 and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR';
375 end if;
376
377 close c_corr_result_exist;
378 else
379 close c_corr_result_exist;
380 -- no corr rec exists, so ZAP the result
381 hr_utility.set_location (' ZAP l_delrec.prtt_enrt_rslt_id '||l_delrec.prtt_enrt_rslt_id, 5);
382 ben_prtt_enrt_result_api.delete_prtt_enrt_result
383 (p_validate => false,
384 p_prtt_enrt_rslt_id => l_delrec.prtt_enrt_rslt_id,
385 p_effective_start_date => l_effective_start_date,
386 p_effective_end_date => l_effective_end_date,
387 p_object_version_number => l_object_version_number,
388 p_effective_date => l_delrec.effective_start_date,
389 p_datetrack_mode => l_mode,
390 p_multi_row_validate => FALSE);
391 end if;
392
393 else
394 hr_utility.set_location('Deleting enrollment: '||l_delrec.prtt_enrt_rslt_id,6695);
395
396 ben_prtt_enrt_result_api.delete_prtt_enrt_result
397 (p_validate => false,
398 p_prtt_enrt_rslt_id => l_delrec.prtt_enrt_rslt_id,
399 p_effective_start_date => l_effective_start_date,
400 p_effective_end_date => l_effective_end_date,
401 p_object_version_number => l_object_version_number,
402 p_effective_date => l_delrec.effective_start_date,
403 p_datetrack_mode => l_mode,
404 p_multi_row_validate => FALSE);
405 end if;
406 --
407 l_delrec := null;
408 end loop;
409 end if;
410
411 hr_utility.set_location('Leaving '||l_proc,6054);
412
413 end delete_imp_inc;
414
415
416 --
417 procedure p_comp_imp_inc_internal
418 (p_person_id in number
419 ,p_enrt_mthd_cd in varchar2
420 ,p_business_group_id in number
421 ,p_per_in_ler_id in number
422 ,p_effective_date in date
423 ,p_subj_to_imptd_incm_typ_cd in varchar2
424 ,p_imptd_incm_calc_cd in varchar2
425 ,p_validate in boolean default false
426 ,p_no_choice_flag in boolean default false
427 ,p_imp_cvg_strt_dt in date default NULL) -- 8716870
428 is
429 --
430 cursor c_pil is
431 select pil.lf_evt_ocrd_dt,
432 pil.ler_id
433 from ben_per_in_ler pil
434 where pil.per_in_ler_id = p_per_in_ler_id;
435 --
436 l_pil_rec c_pil%rowtype;
437 --
438 -- Select the total benefit amount to calculate the
439 -- imputed income.
440 l_max_le_eff_date date := null;
441 -- post tax employee contribution 2897063
442
443 cursor c_post_tax_contrib(p_eot in date) is
444 select prv.rt_val , prv.tx_typ_cd,prv.acty_typ_cd
445 from ben_prtt_enrt_rslt_f pen,
446 ben_prtt_rt_val prv,
447 ben_acty_base_rt_f abr,
448 ben_pl_f pln,
449 ben_per_in_ler pil
450 where pen.person_id = p_person_id
451 and pen.prtt_enrt_rslt_stat_cd is null
452 and pen.enrt_cvg_thru_dt = p_eot
453 and pen.business_group_id = p_business_group_id
454 and pen.pl_id = pln.pl_id
455 and pen.sspndd_flag = 'N'
456 and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
457 and pln.pl_stat_cd = 'A'
458 and pln.business_group_id = p_business_group_id
459 and prv.PRTT_RT_VAL_STAT_CD is null
460 --and prv.per_in_ler_id = pen.per_in_ler_id
461 and prv.Rt_end_dt = p_eot
462 and prv.per_in_ler_id = pil.per_in_ler_id
463 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
464 and p_effective_date between pln.effective_start_date
465 and pln.effective_end_date
466 and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
467 AND pen.effective_end_date=hr_api.g_eot
468 and l_max_le_eff_date >= least(pen.effective_start_date,pen.enrt_cvg_strt_dt )
469 and abr.acty_base_rt_id = prv.acty_base_rt_id
470 and abr.subj_to_imptd_incm_flag = 'Y'
471 and l_max_le_eff_date between abr.effective_start_date and abr.effective_end_date ;
472
473 l_post_tax_rec c_post_tax_contrib%rowtype ;
474 l_post_tax_err_found varchar2(1) := 'N' ;
475 l_post_tax_amount number := 0 ;
476 --
477 -- 8716870: commented existing c_ben_amt
478 /*
479 cursor c_ben_amt(p_highly_comp in varchar2,p_eot in date) is
480 select sum(pen.bnft_amt)
481 from ben_prtt_enrt_rslt_f pen,
482 ben_pl_f pln,
483 ben_per_in_ler pil
484 where pen.person_id = p_person_id
485 and pen.prtt_enrt_rslt_stat_cd is null
486 and pen.enrt_cvg_thru_dt = p_eot
487 and pen.business_group_id = p_business_group_id
488 and pen.pl_id = pln.pl_id
489 and pen.sspndd_flag = 'N'
490 and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
491 and pln.pl_stat_cd = 'A'
492 and pln.business_group_id = p_business_group_id
493 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
494 and p_effective_date between pln.effective_start_date
495 and pln.effective_end_date
496 -- Bug # - 1675410 - If the coverage start date is after life event occurred date,
497 -- the above condition does not select result row. As much as per_in_ler_id is
498 -- existing in pen, it is better to join pil with pen by per_in_ler_id
499 and pil.per_in_ler_id = pen.per_in_ler_id
500 AND pen.effective_end_date=hr_api.g_eot
501 -- Bug 1884964 to restrict pen records from different set
502 and l_max_le_eff_date >= least(pen.effective_start_date,pen.enrt_cvg_strt_dt )
503 and exists
504 (select 'x'
505 from ben_elig_per_f pep
506 where pep.pl_id=pen.pl_id
507 and nvl(pep.pgm_id,-1)=nvl(pen.pgm_id,-1)
508 and pep.person_id=pen.person_id
509 and pep.per_in_ler_id = pil.per_in_ler_id
510 and pep.pl_hghly_compd_flag = p_highly_comp
511 and pep.prtn_strt_dt <= greatest(pen.enrt_cvg_strt_dt,l_max_le_eff_date)
512 and pep.business_group_id = pen.business_group_id);
513 --
514 -- Bug 1312906 : check the person is highly compensated
515 -- on enrt_cvg_start date.
516 --
517 -- and ((pen.enrt_cvg_strt_dt between pep.PRTN_STRT_DT
518 -- and nvl(pep.PRTN_END_DT,p_eot)
519 -- and
520 -- pep.effective_end_date = p_eot)
521 -- or
522 -- (pep.PRTN_STRT_DT >= pen.enrt_cvg_strt_dt
523 -- and
524 -- pep.PRTN_STRT_DT =
525 -- (select min(pep2.PRTN_STRT_DT)
526 -- from ben_elig_per_f pep2
527 -- where pep.pl_id=pep2.pl_id
528 -- and nvl(pep.pgm_id,-1)=nvl(pep2.pgm_id,-1)
529 -- and pep.business_group_id = pep2.business_group_id)
530 -- and
531 -- pep.effective_start_date =
532 -- (select min(pep2.effective_start_date)
533 -- from ben_elig_per_f pep2
534 -- where pep.pl_id=pep2.pl_id
535 -- and pep.prtn_strt_dt=pep2.prtn_strt_dt
536 -- and nvl(pep.pgm_id,-1)=nvl(pep2.pgm_id,-1)
537 -- and pep.business_group_id = pep2.business_group_id)));
538
539 */
540
541 -- 8716870: Imp Inc Enh : Modified cursor c_ben_amt
542 cursor c_ben_amt(p_highly_comp in varchar2,p_cvg_strt_dt date) is
543 select sum(pen.bnft_amt)
544 from ben_prtt_enrt_rslt_f pen,
545 ben_pl_f pln,
546 ben_per_in_ler pil
547 where pen.person_id = p_person_id
548 and pen.prtt_enrt_rslt_stat_cd is null
549 -- and pen.enrt_cvg_thru_dt = p_eot
550 and pen.business_group_id = p_business_group_id
551 and pen.pl_id = pln.pl_id
552 and pen.sspndd_flag = 'N'
553 and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
554 and pln.pl_stat_cd = 'A'
555 and pln.business_group_id = p_business_group_id
556 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
557 and p_imp_cvg_strt_dt between pln.effective_start_date
558 and pln.effective_end_date -- Bug 9436910
559 -- Bug # - 1675410 - If the coverage start date is after life event occurred date,
560 -- the above condition does not select result row. As much as per_in_ler_id is
561 -- existing in pen, it is better to join pil with pen by per_in_ler_id
562 and pil.per_in_ler_id = pen.per_in_ler_id
563 AND pen.effective_end_date=hr_api.g_eot
564 -- Bug 1884964 to restrict pen records from different set
565 and l_max_le_eff_date >= least(pen.effective_start_date,pen.enrt_cvg_strt_dt )
566 and p_cvg_strt_dt between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
567 and exists
568 (select 'x'
569 from ben_elig_per_f pep
570 where pep.pl_id=pen.pl_id
571 and nvl(pep.pgm_id,-1)=nvl(pen.pgm_id,-1)
572 and pep.person_id=pen.person_id
573 and pep.per_in_ler_id = pil.per_in_ler_id
574 and pep.pl_hghly_compd_flag = p_highly_comp
575 and pep.prtn_strt_dt <= greatest(pen.enrt_cvg_strt_dt,l_max_le_eff_date)
576 and pep.business_group_id = pen.business_group_id);
577
578 --
579 -- Bug 1312906 : check the person is highly compensated
580 -- on enrt_cvg_start date.
581 --
582 -- and ((pen.enrt_cvg_strt_dt between pep.PRTN_STRT_DT
583 -- and nvl(pep.PRTN_END_DT,p_eot)
584 -- and
585 -- pep.effective_end_date = p_eot)
586 -- or
587 -- (pep.PRTN_STRT_DT >= pen.enrt_cvg_strt_dt
588 -- and
589 -- pep.PRTN_STRT_DT =
590 -- (select min(pep2.PRTN_STRT_DT)
591 -- from ben_elig_per_f pep2
592 -- where pep.pl_id=pep2.pl_id
593 -- and nvl(pep.pgm_id,-1)=nvl(pep2.pgm_id,-1)
594 -- and pep.business_group_id = pep2.business_group_id)
595 -- and
596 -- pep.effective_start_date =
597 -- (select min(pep2.effective_start_date)
598 -- from ben_elig_per_f pep2
599 -- where pep.pl_id=pep2.pl_id
600 -- and pep.prtn_strt_dt=pep2.prtn_strt_dt
601 -- and nvl(pep.pgm_id,-1)=nvl(pep2.pgm_id,-1)
602 -- and pep.business_group_id = pep2.business_group_id)));
603 --
604
605
606 cursor c_imp_inc_plan is
607 select pln.pl_id,
608 pln.pl_cd
609 from ben_pl_f pln
610 where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
611 and pln.pl_stat_cd = 'A'
612 and pln.business_group_id = p_business_group_id
613 and p_effective_date
614 between pln.effective_start_date
615 and pln.effective_end_date;
616 --
617 cursor c_enrt_rslt(p_pl_id in number) is
618 select pen.prtt_enrt_rslt_id,
619 pen.object_version_number,
620 pen.pl_id,
621 pen.oipl_id,
622 pen.pgm_id,
623 pen.effective_start_date, -- 8716870
624 pen.pl_typ_id
625 from ben_prtt_enrt_rslt_f pen
626 where pen.business_group_id = p_business_group_id and
627 pen.prtt_enrt_rslt_stat_cd is null and
628 pen.pl_id = p_pl_id and
629 pen.oipl_id is null and
630 pen.person_id = p_person_id
631 AND l_pil_rec.lf_evt_ocrd_dt between
632 pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
633 AND pen.effective_end_date=hr_api.g_eot;
634 --
635 cursor c_imp_inc_plan2 (p_enrt_cvg_strt_dt date) is -- 8716870
636 select pen.prtt_enrt_rslt_id,
637 pen.object_version_number,
638 pen.pl_id,
639 pen.oipl_id,
640 pen.pgm_id,
641 pen.effective_start_date, -- 8716870
642 pen.pl_typ_id
643 from ben_pl_f pln,
644 ben_prtt_enrt_rslt_f pen
645 where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
646 and pln.pl_stat_cd = 'A'
647 and p_effective_date
648 between pln.effective_start_date
649 and pln.effective_end_date
650 and pen.business_group_id = p_business_group_id
651 and pen.prtt_enrt_rslt_stat_cd is null
652 and pen.pl_id = pln.pl_id
653 and pen.oipl_id is null
654 and pen.person_id = p_person_id
655 /* 8716870 code changes */
656 -- and pen.effective_end_date = hr_api.g_eot
657 -- and pen.enrt_cvg_thru_dt = hr_api.g_eot;
658 and p_enrt_cvg_strt_dt between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
659 and pen.effective_end_date = hr_api.g_eot;
660 /* 8716870 code changes */
661 --
662 cursor c_elctbl_chc(p_pl_id in number) is
663 select epe.ELIG_PER_ELCTBL_CHC_ID,
664 epe.PL_ID,
665 epe.OIPL_ID,
666 epe.PGM_ID,
667 epe.PL_TYP_ID,
668 epe.PER_IN_LER_ID,
669 nvl(epe.prtt_enrt_rslt_id,-1) prtt_enrt_rslt_id
670 from ben_elig_per_elctbl_chc epe
671 where epe.per_in_ler_id = p_per_in_ler_id
672 and epe.business_group_id = p_business_group_id
673 and epe.pl_id = p_pl_id
674 and epe.oipl_id is null
675 order by prtt_enrt_rslt_id desc;
676 --
677 cursor c_ecr_prv(p_elig_per_elctbl_chc_id in number) is
678 select ecr.enrt_rt_id,
679 ecr.acty_base_rt_id,
680 ecr.prtt_rt_val_id
681 from ben_enrt_rt ecr
682 where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id and
683 ecr.business_group_id = p_business_group_id
684 and ecr.rt_usg_cd = 'IMPTDINC';
685 --
686 --
687 cursor c_prtt_rt(p_prtt_enrt_rslt_id in number) is
688 select prv.prtt_rt_val_id,
689 prv.acty_base_rt_id,
690 prv.rt_ovridn_flag, -- Bug 2200139 Override changes
691 prv.rt_ovridn_thru_dt -- Bug 2200139 Override changes
692 from ben_prtt_rt_val prv
693 where prv.business_group_id = p_business_group_id
694 and prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
695 and prv.prtt_rt_val_stat_cd is null
696 and prv.rt_end_dt = hr_api.g_eot;
697 --
698 cursor c_pgm_enrt_rslt_exists(p_pgm_id in number) is
699 select null
700 from ben_prtt_enrt_rslt_f pen,
701 ben_pl_f pln
702 where pen.business_group_id = p_business_group_id
703 and pen.prtt_enrt_rslt_stat_cd is null
704 and pen.pgm_id = p_pgm_id
705 and pen.person_id = p_person_id
706 and pen.enrt_cvg_thru_dt = hr_api.g_eot
707 and pen.effective_end_date= hr_api.g_eot
708 and pln.pl_id = pen.pl_id
709 and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
710 and pln.pl_stat_cd = 'A'
711 and pln.business_group_id = p_business_group_id
712 and p_effective_date between pln.effective_start_date
713 and pln.effective_end_date;
714
715 cursor c_chk_rate_avlbl(p_plan_id in number) is
716 select acty_base_rt_id
717 from ben_acty_base_rt_f abr
718 where abr.pl_id=p_plan_id
719 and p_effective_date between abr.effective_start_date and abr.effective_end_date
720 and abr.business_group_id = p_business_group_id ;
721
722
723 cursor c_chk_calc (p_abr_id number ) is
724 select b.mlt_cd from
725 ben_acty_vrbl_rt_f a ,
726 ben_vrbl_rt_prfl_f b
727 where
728 a.acty_base_rt_id = p_abr_id
729 and a.vrbl_rt_prfl_id = b.vrbl_rt_prfl_id
730 and a.business_group_id =p_business_group_id
731 and p_effective_date between a.effective_start_date and a.effective_end_date
732 and p_effective_date between b.effective_start_date and b.effective_end_date
733 and ( mlt_cd not in ('FLFX' , 'RL') or VRBL_RT_TRTMT_CD <> 'RPLC' )
734 ;
735
736 l_mlt_cd ben_vrbl_rt_prfl_f.mlt_cd%type ;
737
738 -- 8716870 code change begins
739 -- cursor used to determine l_cvg_strt_dt
740 cursor c_pil_popl
741 is
742 select pil.lf_evt_ocrd_dt, popl.elcns_made_dt, popl.dflt_asnd_dt, popl.dflt_enrt_dt
743 from ben_per_in_ler pil, ben_pil_elctbl_chc_popl popl
744 where popl.per_in_ler_id (+) = pil.per_in_ler_id
745 and pil.per_in_ler_id = p_per_in_ler_id;
746
747 l_pil_popl c_pil_popl%rowtype;
748
749 --
750 -- cursor c_pil_epe_imp_inc is called before calling determine dates
751 cursor c_pil_epe_imp_inc
752 is
753 select epe.ELIG_PER_ELCTBL_CHC_ID,
754 epe.pgm_id,
755 epe.pl_typ_id,
756 epe.pl_id,
757 epe.fonm_cvg_strt_dt
758 from ben_elig_per_elctbl_chc epe,
759 ben_per_in_ler pil,
760 ben_pl_f pl,
761 ben_pil_elctbl_chc_popl pel /* bug 10262697 */
762 where epe.PER_IN_LER_ID = p_per_in_ler_id
763 and pil.per_in_ler_id = p_per_in_ler_id
764 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
765 and epe.pl_id = pl.pl_id
766 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id /* bug 10262697 */
767 and p_effective_date between pl.effective_start_date and pl.effective_end_date
768 and pl.imptd_incm_calc_cd = p_imptd_incm_calc_cd;
769
770
771 l_pil_epe_imp_inc c_pil_epe_imp_inc%rowtype;
772
773 --
774 l_eff_dt date;
775 l_cvg_strt_dt date;
776 l_enrt_cvg_strt_dt_cd VARCHAR2(30);
777 l_enrt_cvg_strt_dt_rl NUMBER;
778 l_enrt_cvg_end_dt DATE;
779 l_enrt_cvg_end_dt_cd VARCHAR2(30);
780 l_enrt_cvg_end_dt_rl NUMBER;
781 l_rt_end_dt DATE;
782 l_rt_end_dt_cd VARCHAR2(30);
783 l_rt_end_dt_rl NUMBER;
784 l_enrt_cvg_thru_dt DATE;
785
786 -- 8716870 code change ends
787
788 --
789 l_rt_ovridn_rec c_prtt_rt%rowtype;
790 l_plan_rec c_imp_inc_plan%rowtype;
791 l_enrt_rslt_rec c_enrt_rslt%rowtype;
792 l_elctbl_chc_rec c_elctbl_chc%rowtype;
793 l_elctbl_chc_next_rec c_elctbl_chc%rowtype;
794 l_ecr_prv_rec c_ecr_prv%rowtype;
795 l_prtt_rt_rec c_prtt_rt%rowtype;
796 l_tot_ben_amt number;
797 l_hgh_cmp_amt number;
798 l_nor_cmp_amt number;
799 l_tot_sub_to_imp_inc number := 0;
800 l_imp_inc number;
801 l_std_imp_inc_ded number;
802 --
803 l_mn_elcn_value number;
804 l_mx_elcn_value number;
805 l_ann_val ben_enrt_rt.ann_val%TYPE;
806 l_ann_mn_elcn_val ben_enrt_rt.ann_mn_elcn_val%TYPE;
807 l_ann_mx_elcn_val ben_enrt_rt.ann_mx_elcn_val%TYPE;
808 l_cmcd_val ben_enrt_rt.cmcd_val%TYPE;
809 l_cmcd_mn_elcn_val ben_enrt_rt.cmcd_mn_elcn_val%TYPE;
810 l_cmcd_mx_elcn_val ben_enrt_rt.cmcd_mx_elcn_val%TYPE;
811 l_cmcd_acty_ref_perd_cd ben_enrt_rt.cmcd_acty_ref_perd_cd%TYPE;
812 l_actl_prem_id ben_enrt_rt.actl_prem_id%TYPE;
813 l_cvg_calc_amt_mthd_id ben_enrt_rt.CVG_AMT_CALC_MTHD_ID%TYPE;
814 l_bnft_rt_typ_cd ben_enrt_rt.bnft_rt_typ_cd%TYPE;
815 l_rt_typ_cd ben_enrt_rt.rt_typ_cd%TYPE;
816 l_rt_mlt_cd ben_enrt_rt.rt_mlt_cd%TYPE;
817 l_comp_lvl_fctr_id ben_enrt_rt.comp_lvl_fctr_id%TYPE;
818 l_entr_ann_val_flag ben_enrt_rt.entr_ann_val_flag%TYPE;
819 l_ptd_comp_lvl_fctr_id ben_enrt_rt.ptd_comp_lvl_fctr_id%TYPE;
820 l_clm_comp_lvl_fctr_id ben_enrt_rt.clm_comp_lvl_fctr_id%TYPE;
821 l_ann_dflt_val ben_enrt_rt.ann_dflt_val%TYPE;
822 l_rt_strt_dt ben_enrt_rt.rt_strt_dt%TYPE;
823 l_rt_strt_dt_rl ben_enrt_rt.rt_strt_dt_rl%TYPE;
824 l_rt_strt_dt_cd ben_enrt_rt.rt_strt_dt_cd%TYPE;
825 l_dsply_mn_elcn_val ben_enrt_rt.dsply_mn_elcn_val%TYPE;
826 l_dsply_mx_elcn_val ben_enrt_rt.dsply_mx_elcn_val%TYPE;
827 l_incrt_val number;
828 l_dflt_elcn_val number;
829 l_acty_ref_perd_cd varchar2(100);
830 l_tx_typ_cd varchar2(100);
831 l_acty_typ_cd varchar2(100);
832 l_asgn_on_enrt_flag varchar2(100);
833 l_use_to_calc_net_flx_cr_flag varchar2(100);
834 l_uom varchar2(100);
835 --
836 l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
837 l_prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%TYPE;
838 l_prtt_rt_val_id1 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
839 l_prtt_rt_val_id2 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
840 l_prtt_rt_val_id3 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
841 l_prtt_rt_val_id4 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
842 l_prtt_rt_val_id5 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
843 l_prtt_rt_val_id6 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
844 l_prtt_rt_val_id7 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
845 l_prtt_rt_val_id8 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
846 l_prtt_rt_val_id9 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
847 l_prtt_rt_val_id10 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
848 l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
849 l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
850 l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
851 l_nnmntry_uom varchar2(100);
852 l_entr_val_at_enrt_flag varchar2(30);
853 l_dsply_on_enrt_flag varchar2(30);
854 l_dpnt_actn_warning boolean;
855 l_ctfn_actn_warning boolean;
856 l_bnf_actn_warning boolean;
857 l_prtt_enrt_interim_id number;
858 L_SUSPEND_FLAG varchar2(30);
859 l_datetrack_mode varchar2(30) := 'INSERT';
860 l_RT_USG_CD VARCHAR2(30);
861 l_BNFT_PRVDR_POOL_ID NUMBER;
862 --
863 l_proc varchar2(72) := g_package||'p_comp_imp_inc_internal';
864 --
865 Type rate_id_type is table of ben_enrt_rt.enrt_rt_id%type index by BINARY_INTEGER;
866 Type rate_val_type is table of ben_enrt_rt.val%type index by BINARY_INTEGER;
867 --
868 rate_id_list rate_id_type;
869 rate_val_list rate_val_type;
870 l_count number;
871 l_result_exists_flag boolean := FALSE;
872 l_choice_exists_flag boolean := false;
873 l_effective_date date null ;
874 l_pp_in_yr_used_num number;
875 l_ordr_num number;
876 l_iss_val number;
877 l_pgm_enrt_rslt_exists varchar2(1);
878 l_prflvalue varchar2(4000) ;
879 l_string varchar2(4000) ;
880 l_acty_base_rt_id number;
881 --
882 /* 8716870: Imputed Income Enhancement */
883 --if the Imputed income enrollment row is in future compared to the effective date use
884 --effective date associated with the enrollment row rather than one being passed to the api.
885 l_eff_date_for_enrt date:=null;
886 p_global_env_rec ben_env_object.g_global_env_rec_type;
887 /* 8716870: Imputed Income Enhancement */
888
889 begin
890 --
891 hr_utility.set_location(' Entering: '||l_proc , 10);
892 --
893 open c_pil;
894 --
895 fetch c_pil into l_pil_rec;
896 --
897 close c_pil;
898 --
899 l_max_le_eff_date := greatest(l_pil_rec.lf_evt_ocrd_dt ,p_imp_cvg_strt_dt); -- Bug 9436910
900 hr_utility.set_location (' l_max_le_eff_date '||l_max_le_eff_date , 8.0);
901 --
902 l_eff_dt:= NULL;
903 l_cvg_strt_dt := NULL;
904 open c_pil_epe_imp_inc;
905 fetch c_pil_epe_imp_inc into l_pil_epe_imp_inc;
906 if c_pil_epe_imp_inc%found then
907 close c_pil_epe_imp_inc;
908 -- determine the enrt_cvg_strt_dt in the context of the life event
909 ben_determine_date.rate_and_coverage_dates
910 (p_which_dates_cd => 'B'
911 ,p_date_mandatory_flag => 'N'
912 ,p_compute_dates_flag => 'Y'
913 ,p_elig_per_elctbl_chc_id => l_pil_epe_imp_inc.elig_per_elctbl_chc_id
914 ,p_business_group_id => p_business_group_id
915 ,P_PER_IN_LER_ID => p_per_in_ler_id
916 ,P_PERSON_ID => p_person_id
917 ,P_PGM_ID => l_pil_epe_imp_inc.pgm_id
918 ,P_PL_ID => l_pil_epe_imp_inc.pl_id
919 -- ,P_OIPL_ID => p_oipl_id
920 ,p_enrt_cvg_strt_dt => l_cvg_strt_dt
921 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd
922 ,p_enrt_cvg_strt_dt_rl => l_enrt_cvg_strt_dt_rl
923 ,p_rt_strt_dt => l_rt_strt_dt
924 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
925 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
926 ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt
927 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd
928 ,p_enrt_cvg_end_dt_rl => l_enrt_cvg_end_dt_rl
929 ,p_rt_end_dt => l_rt_end_dt
930 ,p_rt_end_dt_cd => l_rt_end_dt_cd
931 ,p_rt_end_dt_rl => l_rt_end_dt_rl
932 ,p_effective_date => p_effective_date
933 );
934 else
935 close c_pil_epe_imp_inc;
936 end if;
937
938 -- 8716870
939 hr_utility.set_location (' p_imp_cvg_strt_dt '||p_imp_cvg_strt_dt, 2);
940 hr_utility.set_location (' l_cvg_strt_dt '||l_cvg_strt_dt, 2);
941
942 if p_imp_cvg_strt_dt is not null and p_imp_cvg_strt_dt > l_cvg_strt_dt then
943 hr_utility.set_location (' re-setting l_cvg_strt_dt ', 2);
944 l_cvg_strt_dt := p_imp_cvg_strt_dt;
945 l_rt_strt_dt := p_imp_cvg_strt_dt;
946 end if;
947
948 ben_manage_life_events.fonm := 'Y';
949 ben_manage_life_events.g_fonm_cvg_strt_dt := l_cvg_strt_dt;
950 ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt;
951
952 -- 8716870
953 hr_utility.set_location (' calling delete_imp_inc with effective date as l_rt_strt_dt ', 2);
954 delete_imp_inc(p_person_id => p_person_id,
955 p_per_in_ler_id => p_per_in_ler_id,
956 p_business_group_id => p_business_group_id,
957 p_effective_date => nvl(l_rt_strt_dt,l_cvg_strt_dt),
958 p_imptd_incm_calc_cd => p_subj_to_imptd_incm_typ_cd);
959
960 open c_pil_popl;
961 fetch c_pil_popl into l_pil_popl;
962
963 if c_pil_popl%found then
964 l_eff_dt := nvl(nvl(l_pil_popl.elcns_made_dt, nvl(l_pil_popl.dflt_asnd_dt,l_pil_popl.dflt_enrt_dt)),l_pil_popl.lf_evt_ocrd_dt);
965 close c_pil_popl;
966 else
967 close c_pil_popl;
968 end if;
969
970 l_cvg_strt_dt := nvl(nvl(l_cvg_strt_dt, l_pil_epe_imp_inc.fonm_cvg_strt_dt),l_eff_dt);
971
972 open c_ben_amt(p_highly_comp => 'Y', p_cvg_strt_dt => l_cvg_strt_dt);
973 fetch c_ben_amt into l_hgh_cmp_amt;
974 close c_ben_amt;
975
976 hr_utility.set_location (' l_hgh_cmp_amt '||l_hgh_cmp_amt ,8.1);
977 --
978 open c_ben_amt(p_highly_comp => 'N',p_cvg_strt_dt => l_cvg_strt_dt);
979 fetch c_ben_amt into l_nor_cmp_amt;
980 close c_ben_amt;
981
982 hr_utility.set_location (' l_nor_cmp_amt '||l_nor_cmp_amt , 8.2);
983 --
984 if p_subj_to_imptd_incm_typ_cd = 'PRTT' then
985 --
986 l_std_imp_inc_ded := 50000;
987 --
988 else
989 --
990 l_std_imp_inc_ded := 2000;
991 --
992 end if;
993 --
994 --Bug 2043374 don't deduct for spouse and dependents if the l_nor_cmp_amt amount is
995 -- more than 2000.
996 if p_subj_to_imptd_incm_typ_cd = 'PRTT' then
997 --
998 l_tot_ben_amt := nvl(l_nor_cmp_amt,0) - l_std_imp_inc_ded;
999 --
1000 else
1001 --
1002 if nvl(l_nor_cmp_amt,0) > 2000 then
1003 --
1004 l_tot_ben_amt := nvl(l_nor_cmp_amt,0) ;
1005 --
1006 else
1007 --
1008 l_tot_ben_amt := 0 ;
1009 --
1010 end if;
1011 --
1012 end if;
1013
1014 --
1015 if l_tot_ben_amt < 0 then
1016 --
1017 l_tot_ben_amt := 0;
1018 --
1019 end if;
1020 --
1021 l_tot_sub_to_imp_inc := l_tot_ben_amt + nvl(l_hgh_cmp_amt,0);
1022 --
1023 hr_utility.set_location('l_tot_sub_to_imp_inc: '||l_tot_sub_to_imp_inc, 10);
1024
1025 -- if the profile set to 'Y' and eployee contributed pre-tax , deduct the contribution
1026 --- post tax calcualtion 2897063
1027
1028 l_prflvalue := fnd_profile.value('BEN_IMPTD_INCM_POST_TAX');
1029 hr_utility.set_location('Profile:'||l_prflvalue, 99 );
1030 if l_prflvalue = 'Y' then
1031 open c_post_tax_contrib(hr_api.g_eot) ;
1032 Loop
1033 fetch c_post_tax_contrib into l_post_tax_rec ;
1034 exit when c_post_tax_contrib%notfound ;
1035 if l_post_tax_rec.tx_typ_cd = 'AFTERTAX'
1036 and substr(l_post_tax_rec.acty_typ_cd,1,2) = 'EE' then
1037 l_post_tax_amount := l_post_tax_amount + nvl(l_post_tax_rec.rt_val,0) ;
1038 else
1039 l_post_tax_err_found := 'Y' ;
1040 end if ;
1041 hr_utility.set_location('pdv amount : '||l_post_tax_rec.rt_val , 10);
1042 end loop ;
1043 close c_post_tax_contrib ;
1044 hr_utility.set_location('post tax : '||l_post_tax_amount || ' found ' || l_post_tax_err_found , 10);
1045
1046 if l_post_tax_err_found = 'Y' then
1047
1048 if fnd_global.conc_request_id in ( 0,-1) then
1049 -- Issue a warning to the user. These will display on the enrt forms.
1050 ben_warnings.load_warning
1051 (p_application_short_name => 'BEN',
1052 p_message_name => 'BEN_93397_SUBJ_IMPTD_INCOM_FLG',
1053 p_person_id => p_person_id);
1054 else
1055 --
1056 fnd_message.set_name('BEN','BEN_93397_SUBJ_IMPTD_INCOM_FLG');
1057 l_string := fnd_message.get;
1058 benutils.write(p_text => l_string);
1059 end if ;
1060 --
1061 end if;
1062 end if;
1063 ---- Post tax asmount is deducted after multiplying with factor
1064
1065
1066
1067 if l_tot_sub_to_imp_inc > 0 then
1068 --
1069 open c_imp_inc_plan;
1070 --
1071 fetch c_imp_inc_plan into l_plan_rec;
1072 if c_imp_inc_plan%notfound then
1073 --
1074 close c_imp_inc_plan;
1075 fnd_message.set_name('BEN','BEN_91487_NO_IMP_INC_PLN');
1076 fnd_message.set_token('PROC',l_proc);
1077 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1078 fnd_message.set_token('IMPTD_INCM_CALC_CD',p_imptd_incm_calc_cd);
1079 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
1080 fnd_message.raise_error;
1081 --
1082 end if;
1083 --
1084 close c_imp_inc_plan;
1085 --
1086 open c_elctbl_chc(l_plan_rec.pl_id);
1087 --
1088 fetch c_elctbl_chc into l_elctbl_chc_rec;
1089 --
1090 -- Bug 3153375 : As part of eligibility process if deenrollment is
1091 -- called and choice to imputed income is created then it will not
1092 -- rate row. If no choice flag is set do not use newly created choice
1093 -- data.
1094 --
1095 if c_elctbl_chc%notfound or p_no_choice_flag then
1096 --
1097 -- Bug 1950602 - If a enrollment result exists and electable choice not there
1098 -- for a given per_in_ler, still proceed with imputed income computation.
1099 --
1100 open c_enrt_rslt(l_plan_rec.pl_id);
1101 --
1102 fetch c_enrt_rslt into l_enrt_rslt_rec;
1103 hr_utility.set_location( ' Prtt_enrt_rslt_id '||l_enrt_rslt_rec.Prtt_enrt_rslt_id , 8.5);
1104 --
1105 close c_enrt_rslt;
1106 if p_no_choice_flag or l_enrt_rslt_rec.pl_id is not null then
1107 --
1108 l_elctbl_chc_rec.pl_id := l_enrt_rslt_rec.pl_id;
1109 l_elctbl_chc_rec.pgm_id := l_enrt_rslt_rec.pgm_id;
1110 l_elctbl_chc_rec.oipl_id := l_enrt_rslt_rec.oipl_id;
1111 l_elctbl_chc_rec.pl_typ_id := l_enrt_rslt_rec.pl_typ_id;
1112 l_elctbl_chc_rec.per_in_ler_id := p_per_in_ler_id;
1113 --
1114 else
1115 --
1116 fnd_message.set_name('BEN','BEN_91489_NO_ELCTBL_CHC');
1117 fnd_message.set_token('PROC',l_proc);
1118 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1119 fnd_message.set_token('PL_ID',to_char(l_plan_rec.pl_id));
1120 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
1121 fnd_message.raise_error;
1122 --
1123 end if;
1124 --
1125 else
1126 --
1127 l_choice_exists_flag := true;
1128 hr_utility.set_location( ' l_choice_exists_flag ' , 8.6);
1129 --
1130 if l_plan_rec.pl_cd = 'MSTBPGM' then
1131 loop
1132 --
1133 open c_pgm_enrt_rslt_exists(l_elctbl_chc_rec.pgm_id);
1134 fetch c_pgm_enrt_rslt_exists into l_pgm_enrt_rslt_exists;
1135 if c_pgm_enrt_rslt_exists%found then
1136 close c_pgm_enrt_rslt_exists;
1137 exit;
1138 end if;
1139 close c_pgm_enrt_rslt_exists;
1140
1141 fetch c_elctbl_chc into l_elctbl_chc_next_rec;
1142 exit when c_elctbl_chc%notfound;
1143 l_elctbl_chc_rec := l_elctbl_chc_next_rec;
1144 --
1145 end loop;
1146 --
1147 end if;
1148 --
1149 end if;
1150 --
1151 close c_elctbl_chc;
1152 --
1153 --
1154 -- Get enrollment result for imputed income plan.
1155 --
1156 -- If already fetched, do not re-fetch the record.
1157 --
1158 if l_enrt_rslt_rec.pl_id is not null then
1159 --
1160 l_result_exists_flag := TRUE;
1161 hr_utility.set_location( ' l_result_exists_flag ' , 8.7);
1162 --
1163 else
1164 --
1165 open c_enrt_rslt(l_plan_rec.pl_id);
1166 --
1167 fetch c_enrt_rslt into l_enrt_rslt_rec;
1168 if c_enrt_rslt%found then
1169 --
1170 l_result_exists_flag := TRUE;
1171 hr_utility.set_location( ' l_result_exists_flag ' ,8.8);
1172 --
1173 end if;
1174 --
1175 close c_enrt_rslt;
1176 --
1177 end if;
1178 --
1179 -- Bug 2200139 Override Changes
1180 open c_prtt_rt(l_enrt_rslt_rec.prtt_enrt_rslt_id);
1181 fetch c_prtt_rt into l_rt_ovridn_rec ;
1182 if c_prtt_rt%found then
1183 -- We don't want to recalculate the imputed income rate if it is
1184 -- Overriden by the user from the Override enrollment form.
1185 -- If they want this to be recalculated, they can change the
1186 -- rt_ovridn_thru_dt from the Override enrollment form and
1187 -- save the enrollment, which will recalculate the rate.
1188 --
1189 if -- l_rt_ovridn_rec.rt_ovridn_flag = 'Y' and
1190 p_enrt_mthd_cd = 'O' and
1191 p_effective_date <= nvl(l_rt_ovridn_rec.rt_ovridn_thru_dt, p_effective_date-1 ) then
1192 --
1193 close c_prtt_rt;
1194 return;
1195 --
1196 end if;
1197 --
1198 end if;
1199 close c_prtt_rt ;
1200 --
1201 -- End Bug 2200139 Override changes
1202 --
1203 if not(l_result_exists_flag) and
1204 not(l_choice_exists_flag) and
1205 p_no_choice_flag then
1206 -- *** When p_no_choice_flag is ON. ***
1207 -- No existing results and no imputed income choice, so
1208 -- no recomputation needs to be done. Just return back.
1209 return;
1210 --
1211 end if;
1212
1213 /* 8716870 begins */
1214 --check if the effective start date is greater than effective date being passed to module.
1215 if l_result_exists_flag then
1216 if l_enrt_rslt_rec.effective_start_date > p_effective_date then
1217 l_eff_date_for_enrt := l_enrt_rslt_rec.effective_start_date;
1218 else
1219 l_eff_date_for_enrt := p_effective_date;
1220 end if;
1221 end if;
1222
1223 hr_utility.set_location('l_eff_date_for_enrt :'||l_eff_date_for_enrt,1234);
1224 hr_utility.set_location('p_effective_date :'||p_effective_date,1234);
1225 /* 8716870 ends */
1226
1227
1228 --
1229 -- Dual procesing for rates, based on whether choice exists.
1230 -- If choice exists, processing done through enrollment rate,
1231 -- otherwise through prtt rate val table.
1232 --
1233 if l_choice_exists_flag then
1234 --
1235 open c_ecr_prv(l_elctbl_chc_rec.elig_per_elctbl_chc_id);
1236 --
1237 else
1238 --
1239 open c_prtt_rt(l_enrt_rslt_rec.prtt_enrt_rslt_id);
1240 --
1241 end if;
1242 --
1243 l_count := 1;
1244 --
1245 loop
1246 --
1247 if l_choice_exists_flag then
1248 --
1249 fetch c_ecr_prv into l_ecr_prv_rec;
1250 if c_ecr_prv%notfound then
1251 -- Minimum one rate required.
1252 if l_count > 1 then
1253 --
1254 exit;
1255 --
1256 end if;
1257 --
1258 close c_ecr_prv;
1259 -- Start of Bug fix 3027365
1260 hr_utility.set_location( ' pl id ' || l_plan_rec.pl_id, 99 ) ;
1261 open c_chk_rate_avlbl(l_plan_rec.pl_id);
1262 fetch c_chk_rate_avlbl into l_acty_base_rt_id;
1263
1264 if c_chk_rate_avlbl%found then
1265 close c_chk_rate_avlbl;
1266 open c_chk_calc (l_acty_base_rt_id ) ;
1267 fetch c_chk_calc into l_mlt_cd ;
1268 if c_chk_calc%found then
1269 close c_chk_calc ;
1270 fnd_message.set_name('BEN','BEN_93477_VAPRO_IMPUT_FLAT');
1271 fnd_message.set_token('PROC',l_proc);
1272 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1273 fnd_message.set_token('PL_ID',to_char(l_plan_rec.pl_id));
1274 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(l_acty_base_rt_id));
1275 fnd_message.raise_error;
1276 end if ;
1277 close c_chk_calc ;
1278 else
1279 close c_chk_rate_avlbl;
1280 fnd_message.set_name('BEN','BEN_91488_NO_IMP_ABR');
1281 fnd_message.set_token('PROC',l_proc);
1282 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1283 fnd_message.set_token('PL_ID',to_char(l_plan_rec.pl_id));
1284 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
1285 fnd_message.raise_error;
1286 end if;
1287 -- End of Bug fix 3027365
1288 --
1289 end if;
1290 --
1291 else
1292 --
1293 fetch c_prtt_rt into l_prtt_rt_rec;
1294 --
1295 if c_prtt_rt%notfound then
1296 --
1297 exit;
1298 --
1299 end if;
1300 --
1301 l_ecr_prv_rec.prtt_rt_val_id := l_prtt_rt_rec.prtt_rt_val_id;
1302 l_ecr_prv_rec.acty_base_rt_id := l_prtt_rt_rec.acty_base_rt_id;
1303 --
1304 end if;
1305 --
1306 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1307 fnd_message.set_token('PROC','ben_determine_activity_base_rt');
1308 --
1309 ben_determine_activity_base_rt.main
1310 (P_PERSON_ID => p_person_id
1311 ,P_ELIG_PER_ELCTBL_CHC_ID => l_elctbl_chc_rec.elig_per_elctbl_chc_id --9774358
1312 ,P_ENRT_BNFT_ID => NULL
1313 ,P_ACTY_BASE_RT_ID => l_ecr_prv_rec.acty_base_rt_id
1314 ,P_EFFECTIVE_DATE => p_effective_date
1315 ,p_lf_evt_ocrd_dt => l_pil_rec.lf_evt_ocrd_dt
1316 ,P_PERFORM_ROUNDING_FLG => FALSE
1317 ,p_calc_only_rt_val_flag => true
1318 ,p_pgm_id => l_elctbl_chc_rec.pgm_id
1319 ,p_pl_id => l_elctbl_chc_rec.pl_id
1320 ,p_oipl_id => l_elctbl_chc_rec.oipl_id
1321 ,p_pl_typ_id => l_elctbl_chc_rec.pl_typ_id
1322 ,p_per_in_ler_id => l_elctbl_chc_rec.per_in_ler_id
1323 ,p_ler_id => l_pil_rec.ler_id
1324 ,p_bnft_amt => l_tot_sub_to_imp_inc
1325 ,p_business_group_id => p_business_group_id
1326 ,P_VAL => rate_val_list(l_count)
1327 ,P_MN_ELCN_VAL => l_mn_elcn_value
1328 ,P_MX_ELCN_VAL => l_mx_elcn_value
1329 ,P_ANN_VAL => l_ann_val
1330 ,P_ANN_MN_ELCN_VAL => l_ann_mn_elcn_val
1331 ,P_ANN_MX_ELCN_VAL => l_ann_mx_elcn_val
1332 ,P_CMCD_VAL => l_cmcd_val
1333 ,P_CMCD_MN_ELCN_VAL => l_cmcd_mn_elcn_val
1334 ,P_CMCD_MX_ELCN_VAL => l_cmcd_mx_elcn_val
1335 ,P_CMCD_ACTY_REF_PERD_CD => l_cmcd_acty_ref_perd_cd
1336 ,P_INCRMT_ELCN_VAL => l_incrt_val
1337 ,P_DFLT_VAL => l_dflt_elcn_val
1338 ,P_TX_TYP_CD => l_tx_typ_cd
1339 ,P_ACTY_TYP_CD => l_acty_typ_cd
1340 ,P_NNMNTRY_UOM => l_nnmntry_uom
1341 ,P_ENTR_VAL_AT_ENRT_FLAG => l_entr_val_at_enrt_flag
1342 ,P_DSPLY_ON_ENRT_FLAG => l_dsply_on_enrt_flag
1343 ,P_USE_TO_CALC_NET_FLX_CR_FLAG => l_USE_TO_CALC_NET_FLX_CR_FLAG
1344 ,P_RT_USG_CD => l_RT_USG_CD
1345 ,P_BNFT_PRVDR_POOL_ID => l_BNFT_PRVDR_POOL_ID
1346 ,P_ACTL_PREM_ID => l_actl_prem_id
1347 ,P_CVG_CALC_AMT_MTHD_ID => l_cvg_calc_amt_mthd_id
1348 ,P_BNFT_RT_TYP_CD => l_bnft_rt_typ_cd
1349 ,P_RT_TYP_CD => l_rt_typ_cd
1350 ,P_RT_MLT_CD => l_rt_mlt_cd
1351 ,P_COMP_LVL_FCTR_ID => l_comp_lvl_fctr_id
1352 ,P_ENTR_ANN_VAL_FLAG => l_entr_ann_val_flag
1353 ,P_PTD_COMP_LVL_FCTR_ID => l_ptd_comp_lvl_fctr_id
1354 ,P_CLM_COMP_LVL_FCTR_ID => l_clm_comp_lvl_fctr_id
1355 ,P_ANN_DFLT_VAL => l_ann_dflt_val
1356 ,P_RT_STRT_DT => l_rt_strt_dt
1357 ,P_RT_STRT_DT_CD => l_rt_strt_dt_cd
1358 ,P_RT_STRT_DT_RL => l_rt_strt_dt_rl
1359 ,P_PRTT_RT_VAL_ID => l_prtt_rt_val_id
1360 ,p_dsply_mn_elcn_val => l_dsply_mn_elcn_val
1361 ,p_dsply_mx_elcn_val => l_dsply_mx_elcn_val
1362 ,p_pp_in_yr_used_num => l_pp_in_yr_used_num
1363 ,p_ordr_num => l_ordr_num
1364 ,p_iss_val => l_iss_val);
1365 --
1366 -- Imputed income will always be rate * total subject to imputed income
1367 --
1368 hr_utility.set_location('factor before : '||rate_val_list(l_count) , 10);
1369 rate_val_list(l_count) := rate_val_list(l_count)*l_tot_sub_to_imp_inc;
1370 rate_id_list(l_count) := l_ecr_prv_rec.enrt_rt_id;
1371
1372 hr_utility.set_location('factor after : '||rate_val_list(l_count) , 10);
1373 --- Post tax contribution deducted here 2897063
1374 if l_post_tax_amount > 0 then
1375 if l_post_tax_amount > rate_val_list(l_count) then
1376 rate_val_list(l_count) := 0 ;
1377 else
1378 rate_val_list(l_count) := rate_val_list(l_count) - l_post_tax_amount ;
1379 end if ;
1380 end if ;
1381 --
1382 hr_utility.set_location('post deduction : '||rate_val_list(l_count) , 10);
1383
1384 if l_result_exists_flag then
1385 --
1386 if l_choice_exists_flag then
1387 --
1388 -- result and choice exist, update both in call to
1389 -- election_information
1390 -- outside loop. Bug 1295277
1391 null;
1392 --
1393 else
1394 -- result exists but no choice exists, so just update the rate,
1395 -- by end-dating the previous rate.
1396 ben_provider_pools.update_rate
1397 (p_prtt_rt_val_id => l_ecr_prv_rec.prtt_rt_val_id,
1398 p_val => rate_val_list(l_count),
1399 p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id,
1400 p_business_group_id => p_business_group_id,
1401 p_ended_per_in_ler_id => p_per_in_ler_id,
1402 p_effective_date => p_effective_date);
1403 --
1404 end if;
1405 --
1406 end if;
1407 --
1408 l_count := l_count + 1;
1409 --
1410 end loop;
1411 --
1412 if l_choice_exists_flag then
1413 --
1414 close c_ecr_prv;
1415 --
1416 else
1417 --
1418 close c_prtt_rt;
1419 --
1420 end if;
1421 --
1422 for l_fill in l_count..10 loop
1423 --
1424 rate_id_list(l_fill) := null;
1425 rate_val_list(l_fill) := null;
1426 --
1427 end loop;
1428 --
1429 if l_result_exists_flag and
1430 l_choice_exists_flag then
1431 --
1432 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1433 fnd_message.set_token('PROC','ben_election_information -update');
1434 -- do update Bug 1295277
1435 hr_utility.set_location( ' Case 1 l_result_exists_flag l_choice_exists_flag ' , 9.0);
1436 ben_election_information.election_information
1437 (p_validate => FALSE
1438 ,p_elig_per_elctbl_chc_id => l_elctbl_chc_rec.elig_per_elctbl_chc_id
1439 ,p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id
1440 ,p_effective_date => l_eff_date_for_enrt --8716870
1441 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1442 ,p_enrt_bnft_id => null
1443 ,p_bnft_val => null
1444 ,p_enrt_rt_id1 => rate_id_list(1)
1445 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
1446 ,p_rt_val1 => rate_val_list(1)
1447 ,p_enrt_rt_id2 => rate_id_list(2)
1448 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
1449 ,p_rt_val2 => rate_val_list(2)
1450 ,p_enrt_rt_id3 => rate_id_list(3)
1451 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
1452 ,p_rt_val3 => rate_val_list(3)
1453 ,p_enrt_rt_id4 => rate_id_list(4)
1454 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
1455 ,p_rt_val4 => rate_val_list(4)
1456 ,p_enrt_rt_id5 => rate_id_list(5)
1457 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
1458 ,p_rt_val5 => rate_val_list(5)
1459 ,p_enrt_rt_id6 => rate_id_list(6)
1460 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
1461 ,p_rt_val6 => rate_val_list(6)
1462 ,p_enrt_rt_id7 => rate_id_list(7)
1463 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
1464 ,p_rt_val7 => rate_val_list(7)
1465 ,p_enrt_rt_id8 => rate_id_list(8)
1466 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
1467 ,p_rt_val8 => rate_val_list(8)
1468 ,p_enrt_rt_id9 => rate_id_list(9)
1469 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
1470 ,p_rt_val9 => rate_val_list(9)
1471 ,p_enrt_rt_id10 => rate_id_list(10)
1472 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
1473 ,p_rt_val10 => rate_val_list(10)
1474 ,p_suspend_flag => l_suspend_flag
1475 ,p_called_from_sspnd => 'N' -- 8716870
1476 ,p_effective_start_date => l_effective_start_date
1477 ,p_effective_end_date => l_effective_end_date
1478 ,p_object_version_number => l_object_version_number
1479 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
1480 ,p_business_group_id => p_business_group_id
1481 ,p_datetrack_mode => l_datetrack_mode
1482 ,p_dpnt_actn_warning => l_dpnt_actn_warning
1483 ,p_bnf_actn_warning => l_bnf_actn_warning
1484 ,p_ctfn_actn_warning => l_ctfn_actn_warning
1485 ,p_imp_cvg_strt_dt => p_imp_cvg_strt_dt); -- 8716870
1486 --
1487 elsif NOT(l_result_exists_flag) then
1488 --
1489 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
1490 fnd_message.set_token('PROC','ben_election_information-insert');
1491 -- do insert.
1492 hr_utility.set_location('Effective Date '||p_effective_date , 1399);
1493 hr_utility.set_location('p_enrt_mthd_cd ' ||p_enrt_mthd_cd , 1399 ) ;
1494
1495 hr_utility.set_location('Befor plan_id'||to_char(l_elctbl_chc_rec.pl_id) , 1399);
1496 hr_utility.set_location('Conc Req Id '||fnd_global.conc_request_id , 1399);
1497 hr_utility.set_location('lf date'|| l_pil_rec.lf_evt_ocrd_dt,1399);
1498 hr_utility.set_location('p_enrt_mthd_cd '||p_enrt_mthd_cd , 1399 );
1499 -- Bug 1561138 When automatic enrollment is run for an imputted income
1500 -- plan take the effective date as the minimum of
1501 -- life event occured date and effective date passed.
1502 -- This is done only in case of batch process from a
1503 -- concurrent request sumbission.
1504 l_effective_date := p_effective_date ;
1505
1506 if p_enrt_mthd_cd = 'A'
1507 AND fnd_global.conc_request_id <> -1
1508 then
1509 --
1510 hr_utility.set_location('Automatic enrollmant case' , 1399);
1511 if p_effective_date > l_pil_rec.lf_evt_ocrd_dt
1512 then
1513 l_effective_date := l_pil_rec.lf_evt_ocrd_dt ;
1514 hr_utility.set_location('Automatic enrollment eff date altered', 1399);
1515 hr_utility.set_location('Date passed is :'||l_effective_date, 1399) ;
1516 end if;
1517 --
1518 end if;
1519
1520 --
1521 ben_election_information.election_information
1522 (p_validate => FALSE
1523 ,p_elig_per_elctbl_chc_id => l_elctbl_chc_rec.elig_per_elctbl_chc_id
1524 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1525 ,p_effective_date => p_effective_date
1526 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1527 ,p_enrt_bnft_id => null
1528 ,p_bnft_val => null
1529 ,p_enrt_rt_id1 => rate_id_list(1)
1530 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
1531 ,p_rt_val1 => rate_val_list(1)
1532 ,p_enrt_rt_id2 => rate_id_list(2)
1533 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
1534 ,p_rt_val2 => rate_val_list(2)
1535 ,p_enrt_rt_id3 => rate_id_list(3)
1536 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
1537 ,p_rt_val3 => rate_val_list(3)
1538 ,p_enrt_rt_id4 => rate_id_list(4)
1539 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
1540 ,p_rt_val4 => rate_val_list(4)
1541 ,p_enrt_rt_id5 => rate_id_list(5)
1542 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
1543 ,p_rt_val5 => rate_val_list(5)
1544 ,p_enrt_rt_id6 => rate_id_list(6)
1545 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
1546 ,p_rt_val6 => rate_val_list(6)
1547 ,p_enrt_rt_id7 => rate_id_list(7)
1548 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
1549 ,p_rt_val7 => rate_val_list(7)
1550 ,p_enrt_rt_id8 => rate_id_list(8)
1551 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
1552 ,p_rt_val8 => rate_val_list(8)
1553 ,p_enrt_rt_id9 => rate_id_list(9)
1554 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
1555 ,p_rt_val9 => rate_val_list(9)
1556 ,p_enrt_rt_id10 => rate_id_list(10)
1557 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
1558 ,p_rt_val10 => rate_val_list(10)
1559 ,p_suspend_flag => l_suspend_flag
1560 ,p_called_from_sspnd => 'N' -- 8716870
1561 ,p_effective_start_date => l_effective_start_date
1562 ,p_effective_end_date => l_effective_end_date
1563 ,p_object_version_number => l_object_version_number
1564 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
1565 ,p_business_group_id => p_business_group_id
1566 ,p_datetrack_mode => l_datetrack_mode
1567 ,p_dpnt_actn_warning => l_dpnt_actn_warning
1568 ,p_bnf_actn_warning => l_bnf_actn_warning
1569 ,p_ctfn_actn_warning => l_ctfn_actn_warning
1570 ,p_imp_cvg_strt_dt => p_imp_cvg_strt_dt); -- 8716870
1571 --
1572 end if;
1573 --
1574 else
1575 --
1576 hr_utility.set_location( ' Case 3 ' , 9.2);
1577 open c_imp_inc_plan2(l_cvg_strt_dt); -- 8716870
1578 --
1579 fetch c_imp_inc_plan2 into l_enrt_rslt_rec;
1580 if c_imp_inc_plan2%found then
1581 --
1582 /* 8716870 */
1583 --check if the effective start date is greater than effective date being passed to module.
1584 if l_enrt_rslt_rec.effective_start_date > p_effective_date then
1585 l_eff_date_for_enrt := l_enrt_rslt_rec.effective_start_date;
1586 else
1587 l_eff_date_for_enrt := p_effective_date;
1588 end if;
1589
1590 hr_utility.set_location('l_eff_date_for_enrt :'||l_eff_date_for_enrt,1234);
1591 hr_utility.set_location('p_effective_date :'||p_effective_date,1234);
1592
1593 /* 8716870 */
1594 ben_prtt_enrt_result_api.delete_enrollment
1595 (p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id,
1596 p_per_in_ler_id => p_per_in_ler_id,
1597 p_object_version_number => l_enrt_rslt_rec.object_version_number,
1598 p_effective_start_date => l_effective_start_date,
1599 p_effective_end_date => l_effective_end_date,
1600 p_effective_date => l_eff_date_for_enrt, --8716870
1601 p_datetrack_mode => hr_api.g_delete,
1602 p_business_group_id => p_business_group_id,
1603 p_source => 'bendeimp',
1604 p_multi_row_validate => FALSE);
1605 --
1606 end if;
1607 --
1608 close c_imp_inc_plan2;
1609 --
1610 end if;
1611 --
1612 /* 8716870 begins */
1613 if nvl(l_eff_date_for_enrt,p_effective_date)<>p_effective_date then
1614 --if we used a different effective date than p_effective_date then store it again in
1615 --Environment objects. We have just called a heavy duty api (ben_election_information/ben_prtt_enrt_result_api)
1616 --with an effective date different from what was supposed to be used. This may have stored this date in the
1617 --env objects. This date is not required for any other modules so we query to see if this value was written and
1618 --if so we will overwrite it with the original effective date.
1619 ben_env_object.get(p_rec => p_global_env_rec);
1620
1621 hr_utility.set_location('l_eff_date_for_enrt:'||l_eff_date_for_enrt,1234);
1622 hr_utility.set_location('p_effective_date:'||p_effective_date,1234);
1623 hr_utility.set_location('p_global_env_rec.effective_date:'||p_global_env_rec.effective_date,1234);
1624
1625
1626 if (p_global_env_rec.effective_date = l_eff_date_for_enrt) then
1627 ben_env_object.init
1628 (p_business_group_id => p_global_env_rec.business_group_id
1629 ,p_effective_date => p_effective_date
1630 ,p_thread_id => p_global_env_rec.thread_id
1631 ,p_chunk_size => p_global_env_rec.chunk_size
1632 ,p_threads => p_global_env_rec.threads
1633 ,p_max_errors => p_global_env_rec.max_errors
1634 ,p_benefit_action_id => p_global_env_rec.benefit_action_id
1635 ,p_audit_log_flag => p_global_env_rec.audit_log_flag
1636 );
1637 end if;
1638 end if;
1639 /* 8716870 ends */
1640
1641 hr_utility.set_location('Leaving: '||l_proc , 10);
1642 --
1643 end p_comp_imp_inc_internal;
1644 --
1645
1646 /* 8716870 : Added procedure redo_imp_inc
1647 */
1648 procedure redo_imp_inc(p_person_id number,
1649 p_enrt_mthd_cd varchar2,
1650 p_business_group_id number,
1651 p_per_in_ler_id number,
1652 p_effective_date date,
1653 p_validate in boolean default false,
1654 p_no_choice_flag boolean default false)
1655 is
1656 l_imp_cvg_strt_dt date;
1657
1658 -- cursor to pick all enrollments subject to imputed income
1659 cursor c_enrt_subj_to_imp
1660 is
1661 select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
1662 from ben_prtt_enrt_rslt_f pen,
1663 ben_per_in_ler pil,
1664 ben_pl_f pl
1665 where pen.pl_id = pl.pl_id
1666 and pen.sspndd_flag = 'N'
1667 and pen.per_in_ler_id = pil.per_in_ler_id
1668 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1669 and pen.person_id = pil.person_id
1670 and pen.prtt_enrt_rslt_stat_cd is NULL
1671 and p_effective_date between pl.effective_start_date and pl.effective_end_date
1672 /* Bug 13399004 : Commented the below two coniditons*/
1673 --and pen.enrt_cvg_thru_dt = hr_api.g_eot
1674 --and pen.effective_end_date = hr_api.g_eot
1675 -- and p_effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
1676 and pen.person_id = p_person_id
1677 --and pil.person_id = p_person_id
1678 and pil.per_in_ler_id = p_per_in_ler_id
1679 and subj_to_imptd_incm_typ_cd is not null
1680 union
1681 select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
1682 from ben_prtt_enrt_rslt_f pen,
1683 ben_elig_per_elctbl_chc epe,
1684 ben_pl_f pl,
1685 ben_per_in_ler pil
1686 where epe.per_in_ler_id <> pen.per_in_ler_id
1687 and pen.pl_id = pl.pl_id
1688 and epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1689 and epe.per_in_ler_id = pil.per_in_ler_id
1690 and nvl(epe.pl_id,-1) = nvl(pen.pl_id,-1)
1691 and nvl(epe.oipl_id,-1) = nvl(pen.oipl_id,-1)
1692 and epe.elctbl_flag = 'N'
1693 and epe.crntly_enrd_flag = 'Y'
1694 and pen.sspndd_flag = 'N'
1695 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1696 and pen.prtt_enrt_rslt_stat_cd is NULL
1697 and p_effective_date between pl.effective_start_date and pl.effective_end_date
1698 -- and p_effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
1699 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1700 and pen.effective_end_date = hr_api.g_eot
1701 and pen.person_id = pil.person_id
1702 and pen.person_id = p_person_id
1703 and epe.per_in_ler_id = p_per_in_ler_id
1704 and pl.subj_to_imptd_incm_typ_cd is not null
1705 order by 2 ;
1706
1707 l_enrt_subj_to_imp c_enrt_subj_to_imp%rowtype;
1708 l_effective_date date;
1709
1710
1711
1712 -- This is similar to cursor c_enrt_subj_to_imp used for participant. When not opened in loop, it will fetch the earliest
1713 -- enrollment in terms of coverage start date
1714 cursor c_erlst_cvg_strt(p_imptd_incm_typ_cd varchar2)
1715 is
1716 select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
1717 from ben_prtt_enrt_rslt_f pen,
1718 ben_per_in_ler pil,
1719 ben_pl_f pl
1720 where pen.pl_id = pl.pl_id
1721 and pen.sspndd_flag = 'N'
1722 and pen.per_in_ler_id = pil.per_in_ler_id
1723 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1724 and pen.person_id = pil.person_id
1725 and pen.prtt_enrt_rslt_stat_cd is NULL
1726 and p_effective_date between pl.effective_start_date and pl.effective_end_date
1727 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1728 and pen.effective_end_date = hr_api.g_eot
1729 and pen.person_id = p_person_id
1730 and pil.person_id = p_person_id
1731 and pil.per_in_ler_id = p_per_in_ler_id
1732 and pl.subj_to_imptd_incm_typ_cd = p_imptd_incm_typ_cd
1733 order by 2;
1734
1735 -- Bug 10014628
1736
1737 cursor c_ler_type
1738 is
1739 select ler.typ_cd
1740 from ben_per_in_ler pil , ben_ler_f ler
1741 where per_in_ler_id = p_per_in_ler_id
1742 and ler.ler_id = pil.ler_id
1743 and lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date;
1744
1745 l_ler_type c_ler_type%rowtype;
1746
1747 -- Bug 10014628
1748
1749 -- Bug 9981379 : cursor to check if active enrollment is there in imputed shell plan
1750 -- Further changes against bug 12354818
1751
1752 cursor c_imp_shell_enrt(c_imp_inc_cd varchar2)
1753 is
1754 select pen.prtt_enrt_rslt_id,
1755 pen.object_version_number,
1756 pen.pl_id,
1757 pen.oipl_id,
1758 pen.pgm_id,
1759 pen.effective_start_date,
1760 pen.pl_typ_id,
1761 pen.enrt_cvg_strt_dt
1762 from ben_pl_f pln,
1763 ben_prtt_enrt_rslt_f pen
1764 where pln.imptd_incm_calc_cd = c_imp_inc_cd -- bug 12354818
1765 and pln.pl_stat_cd = 'A'
1766 and p_effective_date between pln.effective_start_date and pln.effective_end_date
1767 and pen.business_group_id = p_business_group_id
1768 and pen.prtt_enrt_rslt_stat_cd is null
1769 and pen.pl_id = pln.pl_id
1770 and pen.oipl_id is null
1771 and pen.person_id = p_person_id
1772 and pen.effective_end_date = hr_api.g_eot
1773 and pen.enrt_cvg_thru_dt = hr_api.g_eot;
1774 /*
1775 and p_enrt_cvg_strt_dt between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
1776 */
1777
1778 l_imp_shell_enrt c_imp_shell_enrt%rowtype;
1779
1780 -- Bug 9981379
1781
1782 -- Bug 10082909: cursor to check if any active enrollment is there that is subjected to imputed income.
1783 -- This cursor does not use the per_in_ler_id criteria.
1784
1785 cursor c_enrt_subj_to_imp_2(c_imp_inc_cd varchar2) /* bug 12354818 */
1786 is
1787 select pen.prtt_enrt_rslt_id,
1788 pen.enrt_cvg_strt_Dt,
1789 pl.subj_to_imptd_incm_typ_cd
1790 from ben_prtt_enrt_rslt_f pen,
1791 ben_per_in_ler pil,
1792 ben_pl_f pl
1793 where pen.pl_id = pl.pl_id
1794 and pen.sspndd_flag = 'N'
1795 and pen.per_in_ler_id = pil.per_in_ler_id
1796 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1797 and pen.person_id = pil.person_id
1798 and pen.prtt_enrt_rslt_stat_cd is NULL
1799 and p_effective_date between pl.effective_start_date and pl.effective_end_date
1800 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1801 and pen.effective_end_date = hr_api.g_eot
1802 -- and p_effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
1803 and pen.person_id = p_person_id
1804 -- and pil.per_in_ler_id = p_per_in_ler_id
1805 and subj_to_imptd_incm_typ_cd = c_imp_inc_cd ; /* bug 12354818 */
1806
1807 l_enrt_subj_to_imp_2 c_enrt_subj_to_imp_2%rowtype;
1808
1809 -- Bug 10082909
1810
1811
1812 l_erlst_cvg_strt c_erlst_cvg_strt%rowtype;
1813 l_found_prtt boolean := FALSE;
1814 l_found_dpnt boolean := FALSE;
1815 l_found_sps boolean := FALSE;
1816 l_effective_start_date date;
1817 l_effective_end_date date;
1818
1819
1820 l_proc varchar2(72) := g_package||'redo_imp_inc';
1821
1822 begin
1823 hr_utility.set_location(' Entering ' || l_proc , 11);
1824 hr_utility.set_location(' p_per_in_ler_id '||p_per_in_ler_id, 10);
1825 hr_utility.set_location(' p_person_id '||p_person_id, 10);
1826
1827 open c_ler_type ;
1828 fetch c_ler_type into l_ler_type ;
1829 close c_ler_type;
1830
1831 hr_utility.set_location('l_ler_type.typ_cd '||l_ler_type.typ_cd, 11);
1832
1833 -- Bug 10014628 : bypass delete_past_imp call for unrestricted life events, because for unrestricted life event,
1834 -- the per_in_ler_id will be same for all unrestricted runs. So, the enrollment will always be end dated, and we need
1835 -- the past prv record.
1836
1837 if l_ler_type.typ_cd <> 'SCHEDDU' then
1838
1839 -- Open cursor c_erlst_cvg_strt for participant, spouse and dependent imputed income. We need to delete any imputed
1840 -- income rate starting before the earliest coverage start date. Such a rate would correspond to a de-enrolled plan
1841 -- subject to imputed income
1842
1843 open c_erlst_cvg_strt(p_imptd_incm_typ_cd => 'PRTT');
1844 fetch c_erlst_cvg_strt into l_erlst_cvg_strt;
1845 if c_erlst_cvg_strt%found then
1846 l_found_prtt := TRUE;
1847 end if;
1848 close c_erlst_cvg_strt;
1849
1850 hr_utility.set_location('l_erlst_cvg_strt.enrt_cvg_strt_dt '||l_erlst_cvg_strt.enrt_cvg_strt_dt, 20);
1851 hr_utility.set_location('l_erlst_cvg_strt.subj_to_imptd_incm_typ_cd '||l_erlst_cvg_strt.subj_to_imptd_incm_typ_cd, 20);
1852
1853 if l_found_prtt then
1854 delete_past_imp(p_person_id => p_person_id,
1855 p_per_in_ler_id => p_per_in_ler_id,
1856 p_business_group_id => p_business_group_id,
1857 p_effective_date => p_effective_date,
1858 p_erlst_cvg_strt => l_erlst_cvg_strt.enrt_cvg_strt_dt,
1859 p_imptd_incm_calc_cd => 'PRTT');
1860 end if;
1861
1862 l_erlst_cvg_strt := NULL;
1863
1864 open c_erlst_cvg_strt(p_imptd_incm_typ_cd => 'DPNT');
1865 fetch c_erlst_cvg_strt into l_erlst_cvg_strt;
1866 if c_erlst_cvg_strt%found then
1867 l_found_dpnt := TRUE;
1868 end if;
1869 close c_erlst_cvg_strt;
1870
1871 hr_utility.set_location('dpnt l_erlst_cvg_strt.enrt_cvg_strt_dt '||l_erlst_cvg_strt.enrt_cvg_strt_dt, 20);
1872 hr_utility.set_location('dpnt l_erlst_cvg_strt.subj_to_imptd_incm_typ_cd '||l_erlst_cvg_strt.subj_to_imptd_incm_typ_cd, 20);
1873
1874 if l_found_dpnt then
1875 delete_past_imp(p_person_id => p_person_id,
1876 p_per_in_ler_id => p_per_in_ler_id,
1877 p_business_group_id => p_business_group_id,
1878 p_effective_date => p_effective_date,
1879 p_erlst_cvg_strt => l_erlst_cvg_strt.enrt_cvg_strt_dt,
1880 p_imptd_incm_calc_cd => 'DPNT');
1881 end if;
1882
1883 l_erlst_cvg_strt := NULL;
1884
1885 open c_erlst_cvg_strt(p_imptd_incm_typ_cd => 'SPS');
1886 fetch c_erlst_cvg_strt into l_erlst_cvg_strt;
1887 if c_erlst_cvg_strt%found then
1888 l_found_sps := TRUE;
1889 end if;
1890 close c_erlst_cvg_strt;
1891
1892 hr_utility.set_location('sps l_erlst_cvg_strt.enrt_cvg_strt_dt '||l_erlst_cvg_strt.enrt_cvg_strt_dt, 20);
1893 hr_utility.set_location('sps l_erlst_cvg_strt.subj_to_imptd_incm_typ_cd '||l_erlst_cvg_strt.subj_to_imptd_incm_typ_cd, 20);
1894
1895 if l_found_sps then
1896 delete_past_imp(p_person_id => p_person_id,
1897 p_per_in_ler_id => p_per_in_ler_id,
1898 p_business_group_id => p_business_group_id,
1899 p_effective_date => p_effective_date,
1900 p_erlst_cvg_strt => l_erlst_cvg_strt.enrt_cvg_strt_dt,
1901 p_imptd_incm_calc_cd => 'SPS');
1902 end if;
1903 end if;
1904
1905 -- pick up all enrollments subject to imputed income and call p_comp_imp_inc_internal for each such enrollment
1906 -- by passing effective_date as the coverage start date
1907
1908 open c_enrt_subj_to_imp;
1909 loop
1910 fetch c_enrt_subj_to_imp into l_enrt_subj_to_imp;
1911 exit when c_enrt_subj_to_imp%notfound;
1912 l_imp_cvg_strt_dt := l_enrt_subj_to_imp.enrt_cvg_strt_dt ;
1913
1914 l_effective_date := p_effective_date; -- Bug 9436910
1915
1916 hr_utility.set_location('l_imp_cvg_strt_dt '||l_imp_cvg_strt_dt, 10);
1917 hr_utility.set_location('l_effective_date '||l_effective_date, 10);
1918 hr_utility.set_location('l_enrt_subj_to_imp.prtt_enrt_rslt_id '||l_enrt_subj_to_imp.prtt_enrt_rslt_id, 10);
1919
1920 if l_enrt_subj_to_imp.subj_to_imptd_incm_typ_cd ='PRTT' then
1921 hr_utility.set_location('recalc imputed income PRTT ', 1635);
1922
1923 p_comp_imp_inc_internal
1924 (p_person_id => p_person_id
1925 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1926 ,p_business_group_id => p_business_group_id
1927 ,p_per_in_ler_id => p_per_in_ler_id
1928 ,p_effective_date => l_effective_date
1929 ,p_subj_to_imptd_incm_typ_cd => 'PRTT'
1930 ,p_imptd_incm_calc_cd => 'PRTT'
1931 ,p_validate => p_validate
1932 ,p_no_choice_flag => p_no_choice_flag
1933 ,p_imp_cvg_strt_dt => l_imp_cvg_strt_dt
1934 );
1935
1936 elsif l_enrt_subj_to_imp.subj_to_imptd_incm_typ_cd ='SPS' then
1937
1938 hr_utility.set_location('recalc imputed income sps', 1675);
1939
1940 p_comp_imp_inc_internal
1941 (p_person_id => p_person_id
1942 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1943 ,p_business_group_id => p_business_group_id
1944 ,p_per_in_ler_id => p_per_in_ler_id
1945 ,p_effective_date => l_effective_date
1946 ,p_subj_to_imptd_incm_typ_cd => 'SPS'
1947 ,p_imptd_incm_calc_cd => 'SPS'
1948 ,p_validate => p_validate
1949 ,p_no_choice_flag => p_no_choice_flag
1950 ,p_imp_cvg_strt_dt => l_imp_cvg_strt_dt
1951 );
1952
1953 elsif l_enrt_subj_to_imp.subj_to_imptd_incm_typ_cd ='DPNT' then
1954
1955 hr_utility.set_location('recalc imputed income dpnt', 1690);
1956
1957 p_comp_imp_inc_internal
1958 (p_person_id => p_person_id
1959 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1960 ,p_business_group_id => p_business_group_id
1961 ,p_per_in_ler_id => p_per_in_ler_id
1962 ,p_effective_date => l_effective_date
1963 ,p_subj_to_imptd_incm_typ_cd => 'DPNT'
1964 ,p_imptd_incm_calc_cd => 'DPNT'
1965 ,p_validate => p_validate
1966 ,p_no_choice_flag => p_no_choice_flag
1967 ,p_imp_cvg_strt_dt => l_imp_cvg_strt_dt
1968 );
1969 end if;
1970 end loop;
1971
1972 hr_utility.set_location(' Leaving ' || l_proc , 11);
1973 close c_enrt_subj_to_imp;
1974
1975 -- Bug 9981379: if c_enrt_subj_to_imp not found, which means that there are no enrollments subjected to
1976 -- imputed income currently, then check if you there is you are still enrolled in the imputed shell plan.
1977 -- If yes, you need to deenroll from the shell plan.
1978
1979 -- fix for Bug 12354818 starts. Need to check the existance for enrollment subjected to specific imputed income
1980 -- type (prtt, sps, dpnt), and if no enrollment is found, need to de-enroll from the specific shell plan.
1981
1982 -- 10082909 : changed cursor from c_enrt_subj_to_imp to c_enrt_subj_to_imp_2
1983
1984 open c_enrt_subj_to_imp_2('PRTT');
1985 fetch c_enrt_subj_to_imp_2 into l_enrt_subj_to_imp_2;
1986 if c_enrt_subj_to_imp_2%notfound then
1987 -- check if the person is enrolled in the imputed shell plan.
1988 hr_utility.set_location('PRTT c_enrt_subj_to_imp_2 is not found ', 200);
1989 open c_imp_shell_enrt('PRTT');
1990 fetch c_imp_shell_enrt into l_imp_shell_enrt;
1991 if c_imp_shell_enrt%found then
1992
1993 hr_utility.set_location('PRTT l_imp_shell_enrt.prtt_enrt_rslt_id '||l_imp_shell_enrt.prtt_enrt_rslt_id, 200);
1994 hr_utility.set_location('kalpesh p_per_in_ler_id '||p_per_in_ler_id, 200);
1995
1996 ben_prtt_enrt_result_api.delete_enrollment
1997 (p_prtt_enrt_rslt_id => l_imp_shell_enrt.prtt_enrt_rslt_id,
1998 p_per_in_ler_id => p_per_in_ler_id,
1999 p_object_version_number => l_imp_shell_enrt.object_version_number,
2000 p_effective_start_date => l_effective_start_date,
2001 p_effective_end_date => l_effective_end_date,
2002 p_effective_date => p_effective_date,
2003 p_datetrack_mode => hr_api.g_delete,
2004 p_business_group_id => p_business_group_id,
2005 p_source => 'bendeimp',
2006 p_multi_row_validate => FALSE);
2007 --
2008 end if;
2009 close c_imp_shell_enrt;
2010 end if;
2011 close c_enrt_subj_to_imp_2;
2012
2013 open c_enrt_subj_to_imp_2('SPS');
2014 fetch c_enrt_subj_to_imp_2 into l_enrt_subj_to_imp_2;
2015 if c_enrt_subj_to_imp_2%notfound then
2016 -- check if the person is enrolled in the imputed shell plan.
2017 hr_utility.set_location('SPS c_enrt_subj_to_imp_2 is not found ', 200);
2018 open c_imp_shell_enrt('SPS');
2019 fetch c_imp_shell_enrt into l_imp_shell_enrt;
2020 if c_imp_shell_enrt%found then
2021 hr_utility.set_location('SPS l_imp_shell_enrt.prtt_enrt_rslt_id '||l_imp_shell_enrt.prtt_enrt_rslt_id, 200);
2022 hr_utility.set_location('kalpesh p_per_in_ler_id '||p_per_in_ler_id, 200);
2023
2024 ben_prtt_enrt_result_api.delete_enrollment
2025 (p_prtt_enrt_rslt_id => l_imp_shell_enrt.prtt_enrt_rslt_id,
2026 p_per_in_ler_id => p_per_in_ler_id,
2027 p_object_version_number => l_imp_shell_enrt.object_version_number,
2028 p_effective_start_date => l_effective_start_date,
2029 p_effective_end_date => l_effective_end_date,
2030 p_effective_date => p_effective_date,
2031 p_datetrack_mode => hr_api.g_delete,
2032 p_business_group_id => p_business_group_id,
2033 p_source => 'bendeimp',
2034 p_multi_row_validate => FALSE);
2035
2036 end if;
2037 close c_imp_shell_enrt;
2038 end if;
2039 close c_enrt_subj_to_imp_2;
2040
2041
2042 open c_enrt_subj_to_imp_2('DPNT');
2043 fetch c_enrt_subj_to_imp_2 into l_enrt_subj_to_imp_2;
2044 if c_enrt_subj_to_imp_2%notfound then
2045 -- check if the person is enrolled in the imputed shell plan.
2046 hr_utility.set_location('DPNT c_enrt_subj_to_imp_2 is not found ', 200);
2047 open c_imp_shell_enrt('DPNT');
2048 fetch c_imp_shell_enrt into l_imp_shell_enrt;
2049 if c_imp_shell_enrt%found then
2050 hr_utility.set_location('DPNT l_imp_shell_enrt.prtt_enrt_rslt_id '||l_imp_shell_enrt.prtt_enrt_rslt_id, 200);
2051
2052 ben_prtt_enrt_result_api.delete_enrollment
2053 (p_prtt_enrt_rslt_id => l_imp_shell_enrt.prtt_enrt_rslt_id,
2054 p_per_in_ler_id => p_per_in_ler_id,
2055 p_object_version_number => l_imp_shell_enrt.object_version_number,
2056 p_effective_start_date => l_effective_start_date,
2057 p_effective_end_date => l_effective_end_date,
2058 p_effective_date => p_effective_date,
2059 p_datetrack_mode => hr_api.g_delete,
2060 p_business_group_id => p_business_group_id,
2061 p_source => 'bendeimp',
2062 p_multi_row_validate => FALSE);
2063
2064 end if;
2065 close c_imp_shell_enrt;
2066 end if;
2067 close c_enrt_subj_to_imp_2;
2068
2069 -- fix for Bug 12354818 ends
2070
2071 end redo_imp_inc;
2072
2073 --
2074 procedure p_comp_imputed_income
2075 (p_person_id in number
2076 ,p_enrt_mthd_cd in varchar2
2077 ,p_business_group_id in number
2078 ,p_per_in_ler_id in number
2079 ,p_effective_date in date
2080 -- Always supply this parameter as its a FIDO only param. Set to false.
2081 ,p_ctrlm_fido_call in boolean default true
2082 ,p_validate in boolean default false
2083 ,p_no_choice_flag in boolean default false
2084 ) is
2085 --
2086 l_proc varchar2(80) := 'ben_det_imputed_income.p_comp_imputed_income';
2087 l_commit number;
2088 l_SES_DATE date;
2089 l_SES_YESTERDAY_DATE date;
2090 l_START_OF_TIME date;
2091 l_END_OF_TIME date;
2092 l_SYS_DATE date;
2093 --
2094 /*
2095 cursor c_imp_plan_exists is
2096 select 'Y'
2097 from ben_pl_f pln
2098 where pln.imptd_incm_calc_cd is not null
2099 and pln.pl_stat_cd = 'A'
2100 and pln.business_group_id = p_business_group_id
2101 and p_effective_date
2102 between pln.effective_start_date
2103 and pln.effective_end_date;
2104 */
2105 --
2106 -- Bug 1950602 : If no electable choices which are subject to imputed income
2107 -- exist for the per_in_ler do not do the imputed income computation.
2108 --
2109 cursor c_imp_plan_exists is
2110 select 'Y'
2111 from ben_pl_f pln,
2112 ben_elig_per_elctbl_chc epe
2113 where pln.subj_to_imptd_incm_typ_cd is not null
2114 and pln.pl_id = epe.pl_id
2115 and epe.per_in_ler_id = p_per_in_ler_id
2116 and pln.pl_stat_cd = 'A'
2117 and pln.business_group_id = p_business_group_id
2118 and p_effective_date
2119 between pln.effective_start_date
2120 and pln.effective_end_date;
2121 --
2122 cursor c_imp_shell is
2123 select 'Y'
2124 from ben_prtt_enrt_rslt_f pen
2125 where pen.person_id = p_person_id
2126 and pen.comp_lvl_cd = 'PLANIMP'
2127 and pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
2128 and pen.prtt_enrt_rslt_stat_cd is null
2129 and pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy')
2130 and exists (select null from ben_elig_per_elctbl_chc epe
2131 where pen.pl_id = epe.pl_id
2132 and epe.per_in_ler_id = p_per_in_ler_id);
2133 --
2134 l_exists varchar2(30) := 'N';
2135 --
2136 /* 8716870 begins*/
2137
2138 l_no_choice_flag boolean := FALSE;
2139
2140 function get_no_choice_flag(p_per_in_ler_id number, p_imptd_incm_calc_cd varchar2)
2141 return boolean
2142 is
2143
2144 cursor c_imp_inc_no_chc(p_per_in_ler_id number, p_imptd_incm_calc_cd varchar2)
2145 is
2146 select 'Y'
2147 from ben_prtt_enrt_rslt_f pen, ben_pl_f pl
2148 where pen.person_id = p_person_id
2149 and pen.comp_lvl_cd = 'PLANIMP'
2150 and pen.effective_end_date = hr_api.g_eot
2151 and pen.prtt_enrt_rslt_stat_cd is null
2152 and pen.enrt_cvg_thru_dt = hr_api.g_eot
2153 and pen.pl_id = pl.pl_id
2154 and pl.imptd_incm_calc_cd = p_imptd_incm_calc_cd
2155 and pl.pl_stat_cd = 'A'
2156 and pen.enrt_cvg_strt_dt between pl.effective_start_date and pl.effective_end_date
2157 and not exists (select 1
2158 from ben_elig_per_elctbl_chc epe
2159 where epe.pl_id = pen.pl_id
2160 and epe.pgm_id = pen.pgm_id
2161 and epe.per_in_ler_id = p_per_in_ler_id);
2162
2163 l_imp_inc_chc_exists c_imp_inc_no_chc%rowtype;
2164 l_no_chc_flg boolean := FALSE;
2165 begin
2166 open c_imp_inc_no_chc(p_per_in_ler_id, p_imptd_incm_calc_cd);
2167 fetch c_imp_inc_no_chc into l_imp_inc_chc_exists;
2168
2169 if c_imp_inc_no_chc%found then
2170 l_no_chc_flg := TRUE;
2171 end if;
2172
2173 close c_imp_inc_no_chc;
2174 return l_no_chc_flg;
2175 end;
2176 /* 8716870 ends*/
2177 --
2178 begin
2179 --
2180 --
2181 hr_utility.set_location(' Entering ' || l_proc , 11);
2182 hr_utility.set_location(' Calculate participant imputed income ', 10);
2183
2184 if p_ctrlm_fido_call then
2185 --
2186 dt_fndate.get_dates(
2187 P_SES_DATE =>l_SES_DATE,
2188 P_SES_YESTERDAY_DATE =>l_SES_YESTERDAY_DATE,
2189 P_START_OF_TIME =>l_START_OF_TIME,
2190 P_END_OF_TIME =>l_END_OF_TIME,
2191 P_SYS_DATE =>l_SYS_DATE,
2192 P_COMMIT =>l_COMMIT
2193 );
2194 --
2195 -- Put row in fnd_sessions
2196 --
2197 dt_fndate.change_ses_date
2198 (p_ses_date => p_effective_date,
2199 p_commit => l_commit);
2200 --
2201 --
2202 -- Fidelity calls this routine from CTRL M which runs it as a concurrent
2203 -- program. In this case they need the environment setup for them.
2204 --
2205 ben_env_object.init(p_business_group_id => p_business_group_id,
2206 p_effective_date => p_effective_date,
2207 p_thread_id => 1,
2208 p_chunk_size => 1,
2209 p_threads => 1,
2210 p_max_errors => 1,
2211 p_benefit_action_id => null);
2212 --
2213 end if;
2214 --
2215 -- Check whether any imputed income plan or subject to income plan
2216 -- exist for the business group.
2217 -- If none, no processing is required.
2218 --
2219 open c_imp_plan_exists;
2220 fetch c_imp_plan_exists into l_exists;
2221 close c_imp_plan_exists;
2222 --
2223 --bug#4435255 - check to see whether the imputed shell plan needs to be deenrolled
2224 if l_exists = 'N' then
2225 --
2226 open c_imp_shell;
2227 fetch c_imp_shell into l_exists;
2228 close c_imp_shell;
2229 --
2230 end if;
2231 -- bug 1950602 : Do not recompute imputed income if there
2232 -- are no electable choices which are subjected to imputed income
2233 -- for this per in ler. or compute if it is denrollment indicated
2234 -- by p_no_choice_flag.
2235 --
2236 -- 8716870
2237 l_no_choice_flag := get_no_choice_flag(p_per_in_ler_id => p_per_in_ler_id,
2238 p_imptd_incm_calc_cd => 'PRTT');
2239
2240 /*
2241
2242 if l_exists = 'Y' or p_no_choice_flag then
2243 --
2244 p_comp_imp_inc_internal
2245 (p_person_id => p_person_id
2246 ,p_enrt_mthd_cd => p_enrt_mthd_cd
2247 ,p_business_group_id => p_business_group_id
2248 ,p_per_in_ler_id => p_per_in_ler_id
2249 ,p_effective_date => p_effective_date
2250 ,p_subj_to_imptd_incm_typ_cd => 'PRTT'
2251 ,p_imptd_incm_calc_cd => 'PRTT'
2252 ,p_validate => p_validate
2253 ,p_no_choice_flag => p_no_choice_flag
2254 );
2255
2256 hr_utility.set_location(' Calculate spouse imputed income ', 10);
2257
2258 p_comp_imp_inc_internal
2259 (p_person_id => p_person_id
2260 ,p_enrt_mthd_cd => p_enrt_mthd_cd
2261 ,p_business_group_id => p_business_group_id
2262 ,p_per_in_ler_id => p_per_in_ler_id
2263 ,p_effective_date => p_effective_date
2264 ,p_subj_to_imptd_incm_typ_cd => 'SPS'
2265 ,p_imptd_incm_calc_cd => 'SPS'
2266 ,p_validate => p_validate
2267 ,p_no_choice_flag => p_no_choice_flag
2268 );
2269
2270 hr_utility.set_location(' Calculate dependent imputed income ', 10);
2271
2272 p_comp_imp_inc_internal
2273 (p_person_id => p_person_id
2274 ,p_enrt_mthd_cd => p_enrt_mthd_cd
2275 ,p_business_group_id => p_business_group_id
2276 ,p_per_in_ler_id => p_per_in_ler_id
2277 ,p_effective_date => p_effective_date
2278 ,p_subj_to_imptd_incm_typ_cd => 'DPNT'
2279 ,p_imptd_incm_calc_cd => 'DPNT'
2280 ,p_validate => p_validate
2281 ,p_no_choice_flag => p_no_choice_flag
2282 );
2283 --
2284 end if;
2285 */
2286
2287 redo_imp_inc(p_person_id => p_person_id,
2288 p_enrt_mthd_cd => p_enrt_mthd_cd,
2289 p_business_group_id => p_business_group_id,
2290 p_per_in_ler_id => p_per_in_ler_id,
2291 p_effective_date => p_effective_date,
2292 p_validate => p_validate,
2293 p_no_choice_flag => p_no_choice_flag); /* bug 10262697 */
2294
2295 if p_ctrlm_fido_call then
2296 --
2297 -- Put back fnd_sessions
2298 --
2299 dt_fndate.change_ses_date
2300 (p_ses_date => l_ses_date,
2301 p_commit => l_commit);
2302 --
2303 end if;
2304 --
2305 hr_utility.set_location(' Leaving ' || l_proc , 10);
2306
2307 end p_comp_imputed_income;
2308 --
2309 END ben_det_imputed_income;