[Home] [Help]
PACKAGE BODY: APPS.BEN_DET_IMPUTED_INCOME
Source
1 PACKAGE body ben_det_imputed_income as
2 /* $Header: bendeimp.pkb 120.1 2005/07/15 15:42:54 kmahendr noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_det_imputed_income.';
7 --
8 procedure p_comp_imp_inc_internal
9 (p_person_id in number
10 ,p_enrt_mthd_cd in varchar2
11 ,p_business_group_id in number
12 ,p_per_in_ler_id in number
13 ,p_effective_date in date
14 ,p_subj_to_imptd_incm_typ_cd in varchar2
15 ,p_imptd_incm_calc_cd in varchar2
16 ,p_validate in boolean default false
17 ,p_no_choice_flag in boolean default false) is
18 --
19 cursor c_pil is
20 select pil.lf_evt_ocrd_dt,
21 pil.ler_id
22 from ben_per_in_ler pil
23 where pil.per_in_ler_id = p_per_in_ler_id;
24 --
25 l_pil_rec c_pil%rowtype;
26 --
27 -- Select the total benefit amount to calculate the
28 -- imputed income.
29 l_max_le_eff_date date := null;
30 -- post tax employee contribution 2897063
31
32 cursor c_post_tax_contrib(p_eot in date) is
33 select prv.rt_val , prv.tx_typ_cd,prv.acty_typ_cd
34 from ben_prtt_enrt_rslt_f pen,
35 ben_prtt_rt_val prv,
36 ben_acty_base_rt_f abr,
37 ben_pl_f pln,
38 ben_per_in_ler pil
39 where pen.person_id = p_person_id
40 and pen.prtt_enrt_rslt_stat_cd is null
41 and pen.enrt_cvg_thru_dt = p_eot
42 and pen.business_group_id = p_business_group_id
43 and pen.pl_id = pln.pl_id
44 and pen.sspndd_flag = 'N'
45 and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
46 and pln.pl_stat_cd = 'A'
47 and pln.business_group_id = p_business_group_id
48 and prv.PRTT_RT_VAL_STAT_CD is null
49 --and prv.per_in_ler_id = pen.per_in_ler_id
50 and prv.Rt_end_dt = p_eot
51 and prv.per_in_ler_id = pil.per_in_ler_id
52 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
53 and p_effective_date between pln.effective_start_date
54 and pln.effective_end_date
55 and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
56 AND pen.effective_end_date=hr_api.g_eot
57 and l_max_le_eff_date >= least(pen.effective_start_date,pen.enrt_cvg_strt_dt )
58 and abr.acty_base_rt_id = prv.acty_base_rt_id
59 and abr.subj_to_imptd_incm_flag = 'Y'
60 and l_max_le_eff_date between abr.effective_start_date and abr.effective_end_date ;
61
62 l_post_tax_rec c_post_tax_contrib%rowtype ;
63 l_post_tax_err_found varchar2(1) := 'N' ;
64 l_post_tax_amount number := 0 ;
65 --
66 cursor c_ben_amt(p_highly_comp in varchar2,p_eot in date) is
67 select sum(pen.bnft_amt)
68 from ben_prtt_enrt_rslt_f pen,
69 ben_pl_f pln,
70 ben_per_in_ler pil
71 where pen.person_id = p_person_id
72 and pen.prtt_enrt_rslt_stat_cd is null
73 and pen.enrt_cvg_thru_dt = p_eot
74 and pen.business_group_id = p_business_group_id
75 and pen.pl_id = pln.pl_id
76 and pen.sspndd_flag = 'N'
77 and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
78 and pln.pl_stat_cd = 'A'
79 and pln.business_group_id = p_business_group_id
80 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
81 and p_effective_date between pln.effective_start_date
82 and pln.effective_end_date
83 -- Bug # - 1675410 - If the coverage start date is after life event occurred date,
84 -- the above condition does not select result row. As much as per_in_ler_id is
85 -- existing in pen, it is better to join pil with pen by per_in_ler_id
86 and pil.per_in_ler_id = pen.per_in_ler_id
87 AND pen.effective_end_date=hr_api.g_eot
88 -- Bug 1884964 to restrict pen records from different set
89 and l_max_le_eff_date >= least(pen.effective_start_date,pen.enrt_cvg_strt_dt )
90 and exists
91 (select 'x'
92 from ben_elig_per_f pep
93 where pep.pl_id=pen.pl_id
94 and nvl(pep.pgm_id,-1)=nvl(pen.pgm_id,-1)
95 and pep.person_id=pen.person_id
96 and pep.per_in_ler_id = pil.per_in_ler_id
97 and pep.pl_hghly_compd_flag = p_highly_comp
98 and pep.prtn_strt_dt <= greatest(pen.enrt_cvg_strt_dt,l_max_le_eff_date)
99 and pep.business_group_id = pen.business_group_id);
100 --
101 -- Bug 1312906 : check the person is highly compensated
102 -- on enrt_cvg_start date.
103 --
104 -- and ((pen.enrt_cvg_strt_dt between pep.PRTN_STRT_DT
105 -- and nvl(pep.PRTN_END_DT,p_eot)
106 -- and
107 -- pep.effective_end_date = p_eot)
108 -- or
109 -- (pep.PRTN_STRT_DT >= pen.enrt_cvg_strt_dt
110 -- and
111 -- pep.PRTN_STRT_DT =
112 -- (select min(pep2.PRTN_STRT_DT)
113 -- from ben_elig_per_f pep2
114 -- where pep.pl_id=pep2.pl_id
115 -- and nvl(pep.pgm_id,-1)=nvl(pep2.pgm_id,-1)
116 -- and pep.business_group_id = pep2.business_group_id)
117 -- and
118 -- pep.effective_start_date =
119 -- (select min(pep2.effective_start_date)
120 -- from ben_elig_per_f pep2
121 -- where pep.pl_id=pep2.pl_id
122 -- and pep.prtn_strt_dt=pep2.prtn_strt_dt
123 -- and nvl(pep.pgm_id,-1)=nvl(pep2.pgm_id,-1)
124 -- and pep.business_group_id = pep2.business_group_id)));
125 --
126 cursor c_imp_inc_plan is
127 select pln.pl_id,
128 pln.pl_cd
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
134 between pln.effective_start_date
135 and pln.effective_end_date;
136 --
137 cursor c_enrt_rslt(p_pl_id in number) is
138 select pen.prtt_enrt_rslt_id,
139 pen.object_version_number,
140 pen.pl_id,
141 pen.oipl_id,
142 pen.pgm_id,
143 pen.pl_typ_id
144 from ben_prtt_enrt_rslt_f pen
145 where pen.business_group_id = p_business_group_id and
146 pen.prtt_enrt_rslt_stat_cd is null and
147 pen.pl_id = p_pl_id and
148 pen.oipl_id is null and
149 pen.person_id = p_person_id
150 AND l_pil_rec.lf_evt_ocrd_dt between
151 pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
152 AND pen.effective_end_date=hr_api.g_eot;
153 --
154 cursor c_imp_inc_plan2 is
155 select pen.prtt_enrt_rslt_id,
156 pen.object_version_number,
157 pen.pl_id,
158 pen.oipl_id,
159 pen.pgm_id,
160 pen.pl_typ_id
161 from ben_pl_f pln,
162 ben_prtt_enrt_rslt_f pen
163 where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
164 and pln.pl_stat_cd = 'A'
165 and p_effective_date
166 between pln.effective_start_date
167 and pln.effective_end_date
168 and pen.business_group_id = p_business_group_id
169 and pen.prtt_enrt_rslt_stat_cd is null
170 and pen.pl_id = pln.pl_id
171 and pen.oipl_id is null
172 and pen.person_id = p_person_id
173 and pen.effective_end_date = hr_api.g_eot
174 and pen.enrt_cvg_thru_dt = hr_api.g_eot;
175 --
176 cursor c_elctbl_chc(p_pl_id in number) is
177 select epe.ELIG_PER_ELCTBL_CHC_ID,
178 epe.PL_ID,
179 epe.OIPL_ID,
180 epe.PGM_ID,
181 epe.PL_TYP_ID,
182 epe.PER_IN_LER_ID,
183 nvl(epe.prtt_enrt_rslt_id,-1) prtt_enrt_rslt_id
184 from ben_elig_per_elctbl_chc epe
185 where epe.per_in_ler_id = p_per_in_ler_id
186 and epe.business_group_id = p_business_group_id
187 and epe.pl_id = p_pl_id
188 and epe.oipl_id is null
189 order by prtt_enrt_rslt_id desc;
190 --
191 cursor c_ecr_prv(p_elig_per_elctbl_chc_id in number) is
192 select ecr.enrt_rt_id,
193 ecr.acty_base_rt_id,
194 ecr.prtt_rt_val_id
195 from ben_enrt_rt ecr
196 where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id and
197 ecr.business_group_id = p_business_group_id
198 and ecr.rt_usg_cd = 'IMPTDINC';
199 --
200 --
201 cursor c_prtt_rt(p_prtt_enrt_rslt_id in number) is
202 select prv.prtt_rt_val_id,
203 prv.acty_base_rt_id,
204 prv.rt_ovridn_flag, -- Bug 2200139 Override changes
205 prv.rt_ovridn_thru_dt -- Bug 2200139 Override changes
206 from ben_prtt_rt_val prv
207 where prv.business_group_id = p_business_group_id
208 and prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
209 and prv.prtt_rt_val_stat_cd is null
210 and prv.rt_end_dt = hr_api.g_eot;
211 --
212 cursor c_pgm_enrt_rslt_exists(p_pgm_id in number) is
213 select null
214 from ben_prtt_enrt_rslt_f pen,
215 ben_pl_f pln
216 where pen.business_group_id = p_business_group_id
217 and pen.prtt_enrt_rslt_stat_cd is null
218 and pen.pgm_id = p_pgm_id
219 and pen.person_id = p_person_id
220 and pen.enrt_cvg_thru_dt = hr_api.g_eot
221 and pen.effective_end_date= hr_api.g_eot
222 and pln.pl_id = pen.pl_id
223 and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
224 and pln.pl_stat_cd = 'A'
225 and pln.business_group_id = p_business_group_id
226 and p_effective_date between pln.effective_start_date
227 and pln.effective_end_date;
228
229 cursor c_chk_rate_avlbl(p_plan_id in number) is
230 select acty_base_rt_id
231 from ben_acty_base_rt_f abr
232 where abr.pl_id=p_plan_id
233 and p_effective_date between abr.effective_start_date and abr.effective_end_date
234 and abr.business_group_id = p_business_group_id ;
235
236
237 cursor c_chk_calc (p_abr_id number ) is
238 select b.mlt_cd from
239 ben_acty_vrbl_rt_f a ,
240 ben_vrbl_rt_prfl_f b
241 where
242 a.acty_base_rt_id = p_abr_id
243 and a.vrbl_rt_prfl_id = b.vrbl_rt_prfl_id
244 and a.business_group_id =p_business_group_id
245 and p_effective_date between a.effective_start_date and a.effective_end_date
246 and p_effective_date between b.effective_start_date and b.effective_end_date
247 and ( mlt_cd not in ('FLFX' , 'RL') or VRBL_RT_TRTMT_CD <> 'RPLC' )
248 ;
249
250 l_mlt_cd ben_vrbl_rt_prfl_f.mlt_cd%type ;
251
252 --
253 l_rt_ovridn_rec c_prtt_rt%rowtype;
254 l_plan_rec c_imp_inc_plan%rowtype;
255 l_enrt_rslt_rec c_enrt_rslt%rowtype;
256 l_elctbl_chc_rec c_elctbl_chc%rowtype;
257 l_elctbl_chc_next_rec c_elctbl_chc%rowtype;
258 l_ecr_prv_rec c_ecr_prv%rowtype;
259 l_prtt_rt_rec c_prtt_rt%rowtype;
260 l_tot_ben_amt number;
261 l_hgh_cmp_amt number;
262 l_nor_cmp_amt number;
263 l_tot_sub_to_imp_inc number := 0;
264 l_imp_inc number;
265 l_std_imp_inc_ded number;
266 --
267 l_mn_elcn_value number;
268 l_mx_elcn_value number;
269 l_ann_val ben_enrt_rt.ann_val%TYPE;
270 l_ann_mn_elcn_val ben_enrt_rt.ann_mn_elcn_val%TYPE;
271 l_ann_mx_elcn_val ben_enrt_rt.ann_mx_elcn_val%TYPE;
272 l_cmcd_val ben_enrt_rt.cmcd_val%TYPE;
273 l_cmcd_mn_elcn_val ben_enrt_rt.cmcd_mn_elcn_val%TYPE;
274 l_cmcd_mx_elcn_val ben_enrt_rt.cmcd_mx_elcn_val%TYPE;
275 l_cmcd_acty_ref_perd_cd ben_enrt_rt.cmcd_acty_ref_perd_cd%TYPE;
276 l_actl_prem_id ben_enrt_rt.actl_prem_id%TYPE;
277 l_cvg_calc_amt_mthd_id ben_enrt_rt.CVG_AMT_CALC_MTHD_ID%TYPE;
278 l_bnft_rt_typ_cd ben_enrt_rt.bnft_rt_typ_cd%TYPE;
279 l_rt_typ_cd ben_enrt_rt.rt_typ_cd%TYPE;
280 l_rt_mlt_cd ben_enrt_rt.rt_mlt_cd%TYPE;
281 l_comp_lvl_fctr_id ben_enrt_rt.comp_lvl_fctr_id%TYPE;
282 l_entr_ann_val_flag ben_enrt_rt.entr_ann_val_flag%TYPE;
283 l_ptd_comp_lvl_fctr_id ben_enrt_rt.ptd_comp_lvl_fctr_id%TYPE;
284 l_clm_comp_lvl_fctr_id ben_enrt_rt.clm_comp_lvl_fctr_id%TYPE;
285 l_ann_dflt_val ben_enrt_rt.ann_dflt_val%TYPE;
286 l_rt_strt_dt ben_enrt_rt.rt_strt_dt%TYPE;
287 l_rt_strt_dt_rl ben_enrt_rt.rt_strt_dt_rl%TYPE;
288 l_rt_strt_dt_cd ben_enrt_rt.rt_strt_dt_cd%TYPE;
289 l_dsply_mn_elcn_val ben_enrt_rt.dsply_mn_elcn_val%TYPE;
290 l_dsply_mx_elcn_val ben_enrt_rt.dsply_mx_elcn_val%TYPE;
291 l_incrt_val number;
292 l_dflt_elcn_val number;
293 l_acty_ref_perd_cd varchar2(100);
294 l_tx_typ_cd varchar2(100);
295 l_acty_typ_cd varchar2(100);
296 l_asgn_on_enrt_flag varchar2(100);
297 l_use_to_calc_net_flx_cr_flag varchar2(100);
298 l_uom varchar2(100);
299 --
300 l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
301 l_prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%TYPE;
302 l_prtt_rt_val_id1 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
303 l_prtt_rt_val_id2 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
304 l_prtt_rt_val_id3 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
305 l_prtt_rt_val_id4 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
306 l_prtt_rt_val_id5 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
307 l_prtt_rt_val_id6 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
308 l_prtt_rt_val_id7 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
309 l_prtt_rt_val_id8 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
310 l_prtt_rt_val_id9 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
311 l_prtt_rt_val_id10 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
312 l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
313 l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
314 l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
315 l_nnmntry_uom varchar2(100);
316 l_entr_val_at_enrt_flag varchar2(30);
317 l_dsply_on_enrt_flag varchar2(30);
318 l_dpnt_actn_warning boolean;
319 l_ctfn_actn_warning boolean;
320 l_bnf_actn_warning boolean;
321 l_prtt_enrt_interim_id number;
322 L_SUSPEND_FLAG varchar2(30);
323 l_datetrack_mode varchar2(30) := 'INSERT';
324 l_RT_USG_CD VARCHAR2(30);
325 l_BNFT_PRVDR_POOL_ID NUMBER;
326 --
327 l_proc varchar2(72) := g_package||'p_comp_imputed_income';
328 --
329 Type rate_id_type is table of ben_enrt_rt.enrt_rt_id%type index by BINARY_INTEGER;
330 Type rate_val_type is table of ben_enrt_rt.val%type index by BINARY_INTEGER;
331 --
332 rate_id_list rate_id_type;
333 rate_val_list rate_val_type;
334 l_count number;
338 l_pp_in_yr_used_num number;
335 l_result_exists_flag boolean := FALSE;
336 l_choice_exists_flag boolean := false;
337 l_effective_date date null ;
339 l_ordr_num number;
340 l_iss_val number;
341 l_pgm_enrt_rslt_exists varchar2(1);
342 l_prflvalue varchar2(4000) ;
343 l_string varchar2(4000) ;
344 l_acty_base_rt_id number;
345 --
346 begin
347 --
348 hr_utility.set_location(' Entering: '||l_proc , 10);
349 --
350 open c_pil;
351 --
352 fetch c_pil into l_pil_rec;
353 --
354 close c_pil;
355 --
356 l_max_le_eff_date := greatest(l_pil_rec.lf_evt_ocrd_dt ,p_effective_date);
357 hr_utility.set_location (' l_max_le_eff_date '||l_max_le_eff_date , 8.0);
358 --
359 open c_ben_amt('Y',hr_api.g_eot);
360 --
361 fetch c_ben_amt into l_hgh_cmp_amt;
362 hr_utility.set_location (' l_hgh_cmp_amt '||l_hgh_cmp_amt ,8.1);
363 --
364 close c_ben_amt;
365 --
366 open c_ben_amt('N',hr_api.g_eot);
367 --
368 fetch c_ben_amt into l_nor_cmp_amt;
369 hr_utility.set_location (' l_nor_cmp_amt '||l_nor_cmp_amt , 8.2);
370 --
371 close c_ben_amt;
372 --
373 if p_subj_to_imptd_incm_typ_cd = 'PRTT' then
374 --
375 l_std_imp_inc_ded := 50000;
376 --
377 else
378 --
379 l_std_imp_inc_ded := 2000;
380 --
381 end if;
382 --
383 --Bug 2043374 don't deduct for spouse and dependents if the l_nor_cmp_amt amount is
384 -- more than 2000.
385 if p_subj_to_imptd_incm_typ_cd = 'PRTT' then
386 --
387 l_tot_ben_amt := nvl(l_nor_cmp_amt,0) - l_std_imp_inc_ded;
388 --
389 else
390 --
391 if nvl(l_nor_cmp_amt,0) > 2000 then
392 --
393 l_tot_ben_amt := nvl(l_nor_cmp_amt,0) ;
394 --
395 else
396 --
397 l_tot_ben_amt := 0 ;
398 --
399 end if;
400 --
401 end if;
402 --
403 if l_tot_ben_amt < 0 then
404 --
405 l_tot_ben_amt := 0;
406 --
407 end if;
408 --
409 l_tot_sub_to_imp_inc := l_tot_ben_amt + nvl(l_hgh_cmp_amt,0);
410 --
411 hr_utility.set_location('l_tot_sub_to_imp_inc: '||l_tot_sub_to_imp_inc, 10);
412 -- if the profile set to 'Y' and eployee contributed pre-tax , deduct the contribution
413 --- post tax calcualtion 2897063
414 l_prflvalue := fnd_profile.value('BEN_IMPTD_INCM_POST_TAX');
415 hr_utility.set_location('Profile:'||l_prflvalue, 99 );
416 if l_prflvalue = 'Y' then
417 open c_post_tax_contrib(hr_api.g_eot) ;
418 Loop
419 fetch c_post_tax_contrib into l_post_tax_rec ;
420 exit when c_post_tax_contrib%notfound ;
421 if l_post_tax_rec.tx_typ_cd = 'AFTERTAX'
422 and substr(l_post_tax_rec.acty_typ_cd,1,2) = 'EE' then
423 l_post_tax_amount := l_post_tax_amount + nvl(l_post_tax_rec.rt_val,0) ;
424 else
425 l_post_tax_err_found := 'Y' ;
426 end if ;
427 hr_utility.set_location('pdv amount : '||l_post_tax_rec.rt_val , 10);
428 end loop ;
429 close c_post_tax_contrib ;
430 hr_utility.set_location('post tax : '||l_post_tax_amount || ' found ' || l_post_tax_err_found , 10);
431
432 if l_post_tax_err_found = 'Y' then
433
434 if fnd_global.conc_request_id in ( 0,-1) then
435 -- Issue a warning to the user. These will display on the enrt forms.
436 ben_warnings.load_warning
437 (p_application_short_name => 'BEN',
438 p_message_name => 'BEN_93397_SUBJ_IMPTD_INCOM_FLG',
439 p_person_id => p_person_id);
440 else
441 --
442 fnd_message.set_name('BEN','BEN_93397_SUBJ_IMPTD_INCOM_FLG');
443 l_string := fnd_message.get;
444 benutils.write(p_text => l_string);
445 end if ;
446 --
447 end if;
448 end if;
449 ---- Post tax asmount is deducted after multiplying with factor
450
451
452
453 if l_tot_sub_to_imp_inc > 0 then
454 --
455 open c_imp_inc_plan;
456 --
457 fetch c_imp_inc_plan into l_plan_rec;
458 if c_imp_inc_plan%notfound then
459 --
460 close c_imp_inc_plan;
461 fnd_message.set_name('BEN','BEN_91487_NO_IMP_INC_PLN');
462 fnd_message.set_token('PROC',l_proc);
463 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
464 fnd_message.set_token('IMPTD_INCM_CALC_CD',p_imptd_incm_calc_cd);
465 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
466 fnd_message.raise_error;
467 --
468 end if;
469 --
470 close c_imp_inc_plan;
471 --
472 open c_elctbl_chc(l_plan_rec.pl_id);
473 --
474 fetch c_elctbl_chc into l_elctbl_chc_rec;
475 --
476 -- Bug 3153375 : As part of eligibility process if deenrollment is
477 -- called and choice to imputed income is created then it will not
478 -- rate row. If no choice flag is set do not use newly created choice
479 -- data.
480 --
481 if c_elctbl_chc%notfound or p_no_choice_flag then
485 --
482 --
483 -- Bug 1950602 - If a enrollment result exists and electable choice not there
484 -- for a given per_in_ler, still proceed with imputed income computation.
486 open c_enrt_rslt(l_plan_rec.pl_id);
487 --
488 fetch c_enrt_rslt into l_enrt_rslt_rec;
489 hr_utility.set_location( ' Prtt_enrt_rslt_id '||l_enrt_rslt_rec.Prtt_enrt_rslt_id , 8.5);
490 --
491 close c_enrt_rslt;
492 if p_no_choice_flag or l_enrt_rslt_rec.pl_id is not null then
493 --
494 l_elctbl_chc_rec.pl_id := l_enrt_rslt_rec.pl_id;
495 l_elctbl_chc_rec.pgm_id := l_enrt_rslt_rec.pgm_id;
496 l_elctbl_chc_rec.oipl_id := l_enrt_rslt_rec.oipl_id;
497 l_elctbl_chc_rec.pl_typ_id := l_enrt_rslt_rec.pl_typ_id;
498 l_elctbl_chc_rec.per_in_ler_id := p_per_in_ler_id;
499 --
500 else
501 --
502 fnd_message.set_name('BEN','BEN_91489_NO_ELCTBL_CHC');
503 fnd_message.set_token('PROC',l_proc);
504 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
505 fnd_message.set_token('PL_ID',to_char(l_plan_rec.pl_id));
506 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
507 fnd_message.raise_error;
508 --
509 end if;
510 --
511 else
512 --
513 l_choice_exists_flag := true;
514 hr_utility.set_location( ' l_choice_exists_flag ' , 8.6);
515 --
516 if l_plan_rec.pl_cd = 'MSTBPGM' then
517 loop
518 --
519 open c_pgm_enrt_rslt_exists(l_elctbl_chc_rec.pgm_id);
520 fetch c_pgm_enrt_rslt_exists into l_pgm_enrt_rslt_exists;
521 if c_pgm_enrt_rslt_exists%found then
522 close c_pgm_enrt_rslt_exists;
523 exit;
524 end if;
525 close c_pgm_enrt_rslt_exists;
526
527 fetch c_elctbl_chc into l_elctbl_chc_next_rec;
528 exit when c_elctbl_chc%notfound;
529 l_elctbl_chc_rec := l_elctbl_chc_next_rec;
530 --
531 end loop;
532 --
533 end if;
534 --
535 end if;
536 --
537 close c_elctbl_chc;
538 --
539 --
540 -- Get enrollment result for imputed income plan.
541 --
542 -- If already fetched, do not re-fetch the record.
543 --
544 if l_enrt_rslt_rec.pl_id is not null then
545 --
546 l_result_exists_flag := TRUE;
547 hr_utility.set_location( ' l_result_exists_flag ' , 8.7);
548 --
549 else
550 --
551 open c_enrt_rslt(l_plan_rec.pl_id);
552 --
553 fetch c_enrt_rslt into l_enrt_rslt_rec;
554 if c_enrt_rslt%found then
555 --
556 l_result_exists_flag := TRUE;
557 hr_utility.set_location( ' l_result_exists_flag ' ,8.8);
558 --
559 end if;
560 --
561 close c_enrt_rslt;
562 --
563 end if;
564 --
565 -- Bug 2200139 Override Changes
566 open c_prtt_rt(l_enrt_rslt_rec.prtt_enrt_rslt_id);
567 fetch c_prtt_rt into l_rt_ovridn_rec ;
568 if c_prtt_rt%found then
569 -- We don't want to recalculate the imputed income rate if it is
570 -- Overriden by the user from the Override enrollment form.
571 -- If they want this to be recalculated, they can change the
572 -- rt_ovridn_thru_dt from the Override enrollment form and
573 -- save the enrollment, which will recalculate the rate.
574 --
575 if -- l_rt_ovridn_rec.rt_ovridn_flag = 'Y' and
576 p_enrt_mthd_cd = 'O' and
577 p_effective_date <= nvl(l_rt_ovridn_rec.rt_ovridn_thru_dt, p_effective_date-1 ) then
578 --
579 close c_prtt_rt;
580 return;
581 --
582 end if;
583 --
584 end if;
585 close c_prtt_rt ;
586 --
587 -- End Bug 2200139 Override changes
588 --
589 if not(l_result_exists_flag) and
590 not(l_choice_exists_flag) and
591 p_no_choice_flag then
592 -- *** When p_no_choice_flag is ON. ***
593 -- No existing results and no imputed income choice, so
594 -- no recomputation needs to be done. Just return back.
595 return;
596 --
597 end if;
598 --
599 -- Dual procesing for rates, based on whether choice exists.
600 -- If choice exists, processing done through enrollment rate,
601 -- otherwise through prtt rate val table.
602 --
603 if l_choice_exists_flag then
604 --
605 open c_ecr_prv(l_elctbl_chc_rec.elig_per_elctbl_chc_id);
606 --
607 else
608 --
609 open c_prtt_rt(l_enrt_rslt_rec.prtt_enrt_rslt_id);
610 --
611 end if;
612 --
613 l_count := 1;
614 --
615 loop
616 --
617 if l_choice_exists_flag then
618 --
619 fetch c_ecr_prv into l_ecr_prv_rec;
620 if c_ecr_prv%notfound then
621 -- Minimum one rate required.
625 --
622 if l_count > 1 then
623 --
624 exit;
626 end if;
627 --
628 close c_ecr_prv;
629 -- Start of Bug fix 3027365
630 hr_utility.set_location( ' pl id ' || l_plan_rec.pl_id, 99 ) ;
631 open c_chk_rate_avlbl(l_plan_rec.pl_id);
632 fetch c_chk_rate_avlbl into l_acty_base_rt_id;
633
634 if c_chk_rate_avlbl%found then
635 close c_chk_rate_avlbl;
636 open c_chk_calc (l_acty_base_rt_id ) ;
637 fetch c_chk_calc into l_mlt_cd ;
638 if c_chk_calc%found then
639 close c_chk_calc ;
640 fnd_message.set_name('BEN','BEN_93477_VAPRO_IMPUT_FLAT');
641 fnd_message.set_token('PROC',l_proc);
642 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
643 fnd_message.set_token('PL_ID',to_char(l_plan_rec.pl_id));
644 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(l_acty_base_rt_id));
645 fnd_message.raise_error;
646 end if ;
647 close c_chk_calc ;
648 else
649 close c_chk_rate_avlbl;
650 fnd_message.set_name('BEN','BEN_91488_NO_IMP_ABR');
651 fnd_message.set_token('PROC',l_proc);
652 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
653 fnd_message.set_token('PL_ID',to_char(l_plan_rec.pl_id));
654 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
655 fnd_message.raise_error;
656 end if;
657 -- End of Bug fix 3027365
658 --
659 end if;
660 --
661 else
662 --
663 fetch c_prtt_rt into l_prtt_rt_rec;
664 --
665 if c_prtt_rt%notfound then
666 --
667 exit;
668 --
669 end if;
670 --
671 l_ecr_prv_rec.prtt_rt_val_id := l_prtt_rt_rec.prtt_rt_val_id;
672 l_ecr_prv_rec.acty_base_rt_id := l_prtt_rt_rec.acty_base_rt_id;
673 --
674 end if;
675 --
676 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
677 fnd_message.set_token('PROC','ben_determine_activity_base_rt');
678 --
679 ben_determine_activity_base_rt.main
680 (P_PERSON_ID => p_person_id
681 ,P_ELIG_PER_ELCTBL_CHC_ID => null
682 ,P_ENRT_BNFT_ID => NULL
683 ,P_ACTY_BASE_RT_ID => l_ecr_prv_rec.acty_base_rt_id
684 ,P_EFFECTIVE_DATE => p_effective_date
685 ,p_lf_evt_ocrd_dt => l_pil_rec.lf_evt_ocrd_dt
686 ,P_PERFORM_ROUNDING_FLG => FALSE
687 ,p_calc_only_rt_val_flag => true
688 ,p_pgm_id => l_elctbl_chc_rec.pgm_id
689 ,p_pl_id => l_elctbl_chc_rec.pl_id
690 ,p_oipl_id => l_elctbl_chc_rec.oipl_id
691 ,p_pl_typ_id => l_elctbl_chc_rec.pl_typ_id
692 ,p_per_in_ler_id => l_elctbl_chc_rec.per_in_ler_id
693 ,p_ler_id => l_pil_rec.ler_id
694 ,p_bnft_amt => l_tot_sub_to_imp_inc
695 ,p_business_group_id => p_business_group_id
696 ,P_VAL => rate_val_list(l_count)
697 ,P_MN_ELCN_VAL => l_mn_elcn_value
698 ,P_MX_ELCN_VAL => l_mx_elcn_value
699 ,P_ANN_VAL => l_ann_val
700 ,P_ANN_MN_ELCN_VAL => l_ann_mn_elcn_val
701 ,P_ANN_MX_ELCN_VAL => l_ann_mx_elcn_val
702 ,P_CMCD_VAL => l_cmcd_val
703 ,P_CMCD_MN_ELCN_VAL => l_cmcd_mn_elcn_val
704 ,P_CMCD_MX_ELCN_VAL => l_cmcd_mx_elcn_val
705 ,P_CMCD_ACTY_REF_PERD_CD => l_cmcd_acty_ref_perd_cd
706 ,P_INCRMT_ELCN_VAL => l_incrt_val
707 ,P_DFLT_VAL => l_dflt_elcn_val
708 ,P_TX_TYP_CD => l_tx_typ_cd
709 ,P_ACTY_TYP_CD => l_acty_typ_cd
710 ,P_NNMNTRY_UOM => l_nnmntry_uom
711 ,P_ENTR_VAL_AT_ENRT_FLAG => l_entr_val_at_enrt_flag
712 ,P_DSPLY_ON_ENRT_FLAG => l_dsply_on_enrt_flag
713 ,P_USE_TO_CALC_NET_FLX_CR_FLAG => l_USE_TO_CALC_NET_FLX_CR_FLAG
714 ,P_RT_USG_CD => l_RT_USG_CD
715 ,P_BNFT_PRVDR_POOL_ID => l_BNFT_PRVDR_POOL_ID
716 ,P_ACTL_PREM_ID => l_actl_prem_id
717 ,P_CVG_CALC_AMT_MTHD_ID => l_cvg_calc_amt_mthd_id
718 ,P_BNFT_RT_TYP_CD => l_bnft_rt_typ_cd
719 ,P_RT_TYP_CD => l_rt_typ_cd
720 ,P_RT_MLT_CD => l_rt_mlt_cd
721 ,P_COMP_LVL_FCTR_ID => l_comp_lvl_fctr_id
722 ,P_ENTR_ANN_VAL_FLAG => l_entr_ann_val_flag
723 ,P_PTD_COMP_LVL_FCTR_ID => l_ptd_comp_lvl_fctr_id
724 ,P_CLM_COMP_LVL_FCTR_ID => l_clm_comp_lvl_fctr_id
725 ,P_ANN_DFLT_VAL => l_ann_dflt_val
726 ,P_RT_STRT_DT => l_rt_strt_dt
727 ,P_RT_STRT_DT_CD => l_rt_strt_dt_cd
728 ,P_RT_STRT_DT_RL => l_rt_strt_dt_rl
729 ,P_PRTT_RT_VAL_ID => l_prtt_rt_val_id
730 ,p_dsply_mn_elcn_val => l_dsply_mn_elcn_val
731 ,p_dsply_mx_elcn_val => l_dsply_mx_elcn_val
735 --
732 ,p_pp_in_yr_used_num => l_pp_in_yr_used_num
733 ,p_ordr_num => l_ordr_num
734 ,p_iss_val => l_iss_val);
736 -- Imputed income will always be rate * total subject to imputed income
737 --
738 hr_utility.set_location('factor before : '||rate_val_list(l_count) , 10);
739 rate_val_list(l_count) := rate_val_list(l_count)*l_tot_sub_to_imp_inc;
740 rate_id_list(l_count) := l_ecr_prv_rec.enrt_rt_id;
741
742 hr_utility.set_location('factor after : '||rate_val_list(l_count) , 10);
743 --- Post tax contribution deducted here 2897063
744 if l_post_tax_amount > 0 then
745 if l_post_tax_amount > rate_val_list(l_count) then
746 rate_val_list(l_count) := 0 ;
747 else
748 rate_val_list(l_count) := rate_val_list(l_count) - l_post_tax_amount ;
749 end if ;
750 end if ;
751 --
752 hr_utility.set_location('post deduction : '||rate_val_list(l_count) , 10);
753
754 if l_result_exists_flag then
755 --
756 if l_choice_exists_flag then
757 --
758 -- result and choice exist, update both in call to
759 -- election_information
760 -- outside loop. Bug 1295277
761 null;
762 --
763 else
764 -- result exists but no choice exists, so just update the rate,
765 -- by end-dating the previous rate.
766 ben_provider_pools.update_rate
767 (p_prtt_rt_val_id => l_ecr_prv_rec.prtt_rt_val_id,
768 p_val => rate_val_list(l_count),
769 p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id,
770 p_business_group_id => p_business_group_id,
771 p_ended_per_in_ler_id => p_per_in_ler_id,
772 p_effective_date => p_effective_date);
773 --
774 end if;
775 --
776 end if;
777 --
778 l_count := l_count + 1;
779 --
780 end loop;
781 --
782 if l_choice_exists_flag then
783 --
784 close c_ecr_prv;
785 --
786 else
787 --
788 close c_prtt_rt;
789 --
790 end if;
791 --
792 for l_fill in l_count..10 loop
793 --
794 rate_id_list(l_fill) := null;
795 rate_val_list(l_fill) := null;
796 --
797 end loop;
798 --
799 if l_result_exists_flag and
800 l_choice_exists_flag then
801 --
802 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
803 fnd_message.set_token('PROC','ben_election_information -update');
804 -- do update Bug 1295277
805 hr_utility.set_location( ' Case 1 l_result_exists_flag l_choice_exists_flag ' , 9.0);
806 ben_election_information.election_information
807 (p_validate => FALSE
808 ,p_elig_per_elctbl_chc_id => l_elctbl_chc_rec.elig_per_elctbl_chc_id
809 ,p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id
810 ,p_effective_date => p_effective_date
811 ,p_enrt_mthd_cd => p_enrt_mthd_cd
812 ,p_enrt_bnft_id => null
813 ,p_bnft_val => null
814 ,p_enrt_rt_id1 => rate_id_list(1)
815 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
816 ,p_rt_val1 => rate_val_list(1)
817 ,p_enrt_rt_id2 => rate_id_list(2)
818 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
819 ,p_rt_val2 => rate_val_list(2)
820 ,p_enrt_rt_id3 => rate_id_list(3)
821 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
822 ,p_rt_val3 => rate_val_list(3)
823 ,p_enrt_rt_id4 => rate_id_list(4)
824 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
825 ,p_rt_val4 => rate_val_list(4)
826 ,p_enrt_rt_id5 => rate_id_list(5)
827 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
828 ,p_rt_val5 => rate_val_list(5)
829 ,p_enrt_rt_id6 => rate_id_list(6)
830 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
831 ,p_rt_val6 => rate_val_list(6)
832 ,p_enrt_rt_id7 => rate_id_list(7)
833 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
834 ,p_rt_val7 => rate_val_list(7)
835 ,p_enrt_rt_id8 => rate_id_list(8)
836 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
837 ,p_rt_val8 => rate_val_list(8)
838 ,p_enrt_rt_id9 => rate_id_list(9)
839 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
840 ,p_rt_val9 => rate_val_list(9)
841 ,p_enrt_rt_id10 => rate_id_list(10)
842 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
843 ,p_rt_val10 => rate_val_list(10)
844 ,p_suspend_flag => l_suspend_flag
845 ,p_effective_start_date => l_effective_start_date
846 ,p_effective_end_date => l_effective_end_date
847 ,p_object_version_number => l_object_version_number
848 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
849 ,p_business_group_id => p_business_group_id
850 ,p_datetrack_mode => l_datetrack_mode
851 ,p_dpnt_actn_warning => l_dpnt_actn_warning
855 elsif NOT(l_result_exists_flag) then
852 ,p_bnf_actn_warning => l_bnf_actn_warning
853 ,p_ctfn_actn_warning => l_ctfn_actn_warning);
854 --
856 --
857 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
858 fnd_message.set_token('PROC','ben_election_information-insert');
859 -- do insert.
860 hr_utility.set_location('Effective Date '||p_effective_date , 1399);
861 hr_utility.set_location('p_enrt_mthd_cd ' ||p_enrt_mthd_cd , 1399 ) ;
862
863 hr_utility.set_location('Befor plan_id'||to_char(l_elctbl_chc_rec.pl_id) , 1399);
864 hr_utility.set_location('Conc Req Id '||fnd_global.conc_request_id , 1399);
865 hr_utility.set_location('lf date'|| l_pil_rec.lf_evt_ocrd_dt,1399);
866 hr_utility.set_location('p_enrt_mthd_cd '||p_enrt_mthd_cd , 1399 );
867 -- Bug 1561138 When automatic enrollment is run for an imputted income
868 -- plan take the effective date as the minimum of
869 -- life event occured date and effective date passed.
870 -- This is done only in case of batch process from a
871 -- concurrent request sumbission.
872 l_effective_date := p_effective_date ;
873
874 if p_enrt_mthd_cd = 'A'
875 AND fnd_global.conc_request_id <> -1
876 then
877 --
878 hr_utility.set_location('Automatic enrollmant case' , 1399);
879 if p_effective_date > l_pil_rec.lf_evt_ocrd_dt
880 then
881 l_effective_date := l_pil_rec.lf_evt_ocrd_dt ;
882 hr_utility.set_location('Automatic enrollment eff date altered', 1399);
883 hr_utility.set_location('Date passed is :'||l_effective_date, 1399) ;
884 end if;
885 --
886 end if;
887
888 --
889 ben_election_information.election_information
890 (p_validate => FALSE
891 ,p_elig_per_elctbl_chc_id => l_elctbl_chc_rec.elig_per_elctbl_chc_id
892 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
893 ,p_effective_date => p_effective_date
894 ,p_enrt_mthd_cd => p_enrt_mthd_cd
895 ,p_enrt_bnft_id => null
896 ,p_bnft_val => null
897 ,p_enrt_rt_id1 => rate_id_list(1)
898 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
899 ,p_rt_val1 => rate_val_list(1)
900 ,p_enrt_rt_id2 => rate_id_list(2)
901 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
902 ,p_rt_val2 => rate_val_list(2)
903 ,p_enrt_rt_id3 => rate_id_list(3)
904 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
905 ,p_rt_val3 => rate_val_list(3)
906 ,p_enrt_rt_id4 => rate_id_list(4)
907 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
908 ,p_rt_val4 => rate_val_list(4)
909 ,p_enrt_rt_id5 => rate_id_list(5)
910 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
911 ,p_rt_val5 => rate_val_list(5)
912 ,p_enrt_rt_id6 => rate_id_list(6)
913 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
914 ,p_rt_val6 => rate_val_list(6)
915 ,p_enrt_rt_id7 => rate_id_list(7)
916 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
917 ,p_rt_val7 => rate_val_list(7)
918 ,p_enrt_rt_id8 => rate_id_list(8)
919 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
920 ,p_rt_val8 => rate_val_list(8)
921 ,p_enrt_rt_id9 => rate_id_list(9)
922 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
923 ,p_rt_val9 => rate_val_list(9)
924 ,p_enrt_rt_id10 => rate_id_list(10)
925 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
926 ,p_rt_val10 => rate_val_list(10)
927 ,p_suspend_flag => l_suspend_flag
928 ,p_effective_start_date => l_effective_start_date
929 ,p_effective_end_date => l_effective_end_date
930 ,p_object_version_number => l_object_version_number
931 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
932 ,p_business_group_id => p_business_group_id
933 ,p_datetrack_mode => l_datetrack_mode
934 ,p_dpnt_actn_warning => l_dpnt_actn_warning
935 ,p_bnf_actn_warning => l_bnf_actn_warning
936 ,p_ctfn_actn_warning => l_ctfn_actn_warning);
937 --
938 end if;
939 --
940 else
941 --
942 hr_utility.set_location( ' Case 3 ' , 9.2);
943 open c_imp_inc_plan2;
944 --
945 fetch c_imp_inc_plan2 into l_enrt_rslt_rec;
946 if c_imp_inc_plan2%found then
947 --
948 ben_prtt_enrt_result_api.delete_enrollment
949 (p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id,
950 p_per_in_ler_id => p_per_in_ler_id,
951 p_object_version_number => l_enrt_rslt_rec.object_version_number
952 ,
953 p_effective_start_date => l_effective_start_date,
954 p_effective_end_date => l_effective_end_date,
955 p_effective_date => p_effective_date,
956 p_datetrack_mode => hr_api.g_delete,
957 p_business_group_id => p_business_group_id,
961 end if;
958 p_source => 'bendeimp',
959 p_multi_row_validate => FALSE);
960 --
962 --
963 close c_imp_inc_plan2;
964 --
965 end if;
966 --
967 hr_utility.set_location('Leaving: '||l_proc , 10);
968 --
969 end p_comp_imp_inc_internal;
970 --
971 procedure p_comp_imputed_income
972 (p_person_id in number
973 ,p_enrt_mthd_cd in varchar2
974 ,p_business_group_id in number
975 ,p_per_in_ler_id in number
976 ,p_effective_date in date
977 -- Always supply this parameter as its a FIDO only param. Set to false.
978 ,p_ctrlm_fido_call in boolean default true
979 ,p_validate in boolean default false
980 ,p_no_choice_flag in boolean default false
981 ) is
982 --
983 l_proc varchar2(80) := 'ben_det_imputed_income.p_comp_imputed_income';
984 l_commit number;
985 l_SES_DATE date;
986 l_SES_YESTERDAY_DATE date;
987 l_START_OF_TIME date;
988 l_END_OF_TIME date;
989 l_SYS_DATE date;
990 --
991 /*
992 cursor c_imp_plan_exists is
993 select 'Y'
994 from ben_pl_f pln
995 where pln.imptd_incm_calc_cd is not null
996 and pln.pl_stat_cd = 'A'
997 and pln.business_group_id = p_business_group_id
998 and p_effective_date
999 between pln.effective_start_date
1000 and pln.effective_end_date;
1001 */
1002 --
1003 -- Bug 1950602 : If no electable choices which are subject to imputed income
1004 -- exist for the per_in_ler do not do the imputed income computation.
1005 --
1006 cursor c_imp_plan_exists is
1007 select 'Y'
1008 from ben_pl_f pln,
1009 ben_elig_per_elctbl_chc epe
1010 where pln.subj_to_imptd_incm_typ_cd is not null
1011 and pln.pl_id = epe.pl_id
1012 and epe.per_in_ler_id = p_per_in_ler_id
1013 and pln.pl_stat_cd = 'A'
1014 and pln.business_group_id = p_business_group_id
1015 and p_effective_date
1016 between pln.effective_start_date
1017 and pln.effective_end_date;
1018 --
1019 cursor c_imp_shell is
1020 select 'Y'
1021 from ben_prtt_enrt_rslt_f pen
1022 where pen.person_id = p_person_id
1023 and pen.comp_lvl_cd = 'PLANIMP'
1024 and pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
1025 and pen.prtt_enrt_rslt_stat_cd is null
1026 and pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy')
1027 and exists (select null from ben_elig_per_elctbl_chc epe
1028 where pen.pl_id = epe.pl_id
1029 and epe.per_in_ler_id = p_per_in_ler_id);
1030 --
1031 l_exists varchar2(30) := 'N';
1032 --
1033 begin
1034 --
1035 hr_utility.set_location(' Entering ' || l_proc , 10);
1036 hr_utility.set_location(' Calculate participant imputed income ', 10);
1037 if p_ctrlm_fido_call then
1038 --
1039 dt_fndate.get_dates(
1040 P_SES_DATE =>l_SES_DATE,
1041 P_SES_YESTERDAY_DATE =>l_SES_YESTERDAY_DATE,
1042 P_START_OF_TIME =>l_START_OF_TIME,
1043 P_END_OF_TIME =>l_END_OF_TIME,
1044 P_SYS_DATE =>l_SYS_DATE,
1045 P_COMMIT =>l_COMMIT
1046 );
1047 --
1048 -- Put row in fnd_sessions
1049 --
1050 dt_fndate.change_ses_date
1051 (p_ses_date => p_effective_date,
1052 p_commit => l_commit);
1053 --
1054 --
1055 -- Fidelity calls this routine from CTRL M which runs it as a concurrent
1056 -- program. In this case they need the environment setup for them.
1057 --
1058 ben_env_object.init(p_business_group_id => p_business_group_id,
1059 p_effective_date => p_effective_date,
1060 p_thread_id => 1,
1061 p_chunk_size => 1,
1062 p_threads => 1,
1063 p_max_errors => 1,
1064 p_benefit_action_id => null);
1065 --
1066 end if;
1067 --
1068 -- Check whether any imputed income plan or subject to income plan
1069 -- exist for the business group.
1070 -- If none, no processing is required.
1071 --
1072 open c_imp_plan_exists;
1073 fetch c_imp_plan_exists into l_exists;
1074 close c_imp_plan_exists;
1075 --
1076 --bug#4435255 - check to see whether the imputed shell plan needs to be deenrolled
1077 if l_exists = 'N' then
1078 --
1079 open c_imp_shell;
1080 fetch c_imp_shell into l_exists;
1081 close c_imp_shell;
1082 --
1083 end if;
1084 -- bug 1950602 : Do not recompute imputed income if there
1085 -- are no electable choices which are subjected to imputed income
1086 -- for this per in ler. or compute if it is denrollment indicated
1087 -- by p_no_choice_flag.
1088 --
1089 if l_exists = 'Y' or p_no_choice_flag then
1090 --
1091 p_comp_imp_inc_internal
1092 (p_person_id => p_person_id
1093 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1094 ,p_business_group_id => p_business_group_id
1095 ,p_per_in_ler_id => p_per_in_ler_id
1096 ,p_effective_date => p_effective_date
1097 ,p_subj_to_imptd_incm_typ_cd => 'PRTT'
1098 ,p_imptd_incm_calc_cd => 'PRTT'
1099 ,p_validate => p_validate
1100 ,p_no_choice_flag => p_no_choice_flag
1101 );
1102
1106 (p_person_id => p_person_id
1103 hr_utility.set_location(' Calculate spouse imputed income ', 10);
1104
1105 p_comp_imp_inc_internal
1107 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1108 ,p_business_group_id => p_business_group_id
1109 ,p_per_in_ler_id => p_per_in_ler_id
1110 ,p_effective_date => p_effective_date
1111 ,p_subj_to_imptd_incm_typ_cd => 'SPS'
1112 ,p_imptd_incm_calc_cd => 'SPS'
1113 ,p_validate => p_validate
1114 ,p_no_choice_flag => p_no_choice_flag
1115 );
1116
1117 hr_utility.set_location(' Calculate dependent imputed income ', 10);
1118
1119 p_comp_imp_inc_internal
1120 (p_person_id => p_person_id
1121 ,p_enrt_mthd_cd => p_enrt_mthd_cd
1122 ,p_business_group_id => p_business_group_id
1123 ,p_per_in_ler_id => p_per_in_ler_id
1124 ,p_effective_date => p_effective_date
1125 ,p_subj_to_imptd_incm_typ_cd => 'DPNT'
1126 ,p_imptd_incm_calc_cd => 'DPNT'
1127 ,p_validate => p_validate
1128 ,p_no_choice_flag => p_no_choice_flag
1129 );
1130 --
1131 end if;
1132
1133 if p_ctrlm_fido_call then
1134 --
1135 -- Put back fnd_sessions
1136 --
1137 dt_fndate.change_ses_date
1138 (p_ses_date => l_ses_date,
1139 p_commit => l_commit);
1140 --
1141 end if;
1142 --
1143 hr_utility.set_location(' Leaving ' || l_proc , 10);
1144
1145 end p_comp_imputed_income;
1146 --
1147 END ben_det_imputed_income;