[Home] [Help]
PACKAGE BODY: APPS.BEN_NEWLY_INELIGIBLE
Source
1 PACKAGE BODY ben_newly_ineligible as
2 /* $Header: beninelg.pkb 120.6.12010000.6 2008/08/22 07:59:10 sallumwa ship $ */
3 -----------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name
11 Manage Newly Ineligible Persons
12 Purpose
13 This package is used to find out whether the person is covered under the
14 Program/Plan or OIPL for which he is newly ineligible. And if covered,
15 it calls the deenrollment API to deenroll the person.
16
17 History
18 Date Who Version What?
19 ---- --- ------- -----
20 28 May 98 J Mohapatra 110.0 Created.
21 07 Jun 98 J Mohapatra 110.1 added 'ben_' to package Name.
22 24 Oct 98 G Perry 115.3 Corrected Query removed
23 dbms_output.put_line calls.
24 05 Nov 98 G Perry 115.4 Added lee_rsn_id, made
25 cursors more performant.
26 01 Feb 99 Y Rathman 115.5 Made modification to allow
27 flag enrollments as no longer
28 eligible
29 28 Apr 99 lmcdonal 115.6 prtt_enrt_rslt now has stat_cd
30 25 May 99 lmcdonal 115.7 To de-enroll from a comp object
31 when running in scheduled mode,
32 we should not look for a lee_rsn
33 to pass to delete enrt, but instead
34 pass the enrt_perd_id from the
35 benmngle parm list.
36 27 May 99 maagrawa 115.8 Added calls to imputed income
37 and flex credits recomputing
38 procedures when de-enrollment
39 takes place.
40 03 Jun 99 Tmathers 115.9 Backport of 115.7 with
41 cacheing changes.
42 03 Jun 99 Tmathers 115.10 leapfrog of 115.8 with
43 cacheing changes.
44 24 Jun 99 maagrawa 115.11 subj_to_imput_inc_flag changed
45 to subj_to_imptd_incm_typ_cd.
46 20-JUL-99 Gperry 115.12 genutils -> benutils package
47 rename.
48 04-OCT-99 Stee 115.13 If terminating all
49 coverage for COBRA,
50 also end date Qualified bnf.
51 29-Oct-99 maagrawa 115.14 Call the delete_enrollment
52 process with parameter
53 p_source => 'beninelg'.
54 12-Nov-99 lmcdonal 115.15 Pass enrt_mthd_cd to
55 recompute_flex_credits.
56 31-Jan-00 jcarpent 115.16 Use dt mode globals.
57 09-Feb-00 jcarpent 115.17 Don't call multi_rows_edit.
58 11-Feb-00 jcarpent 115.18 Accept p_pgm_id for plips.
59 04-Mar-00 stee 115.19 Add ptip_id to
60 end_prtt_cobra_eligibility.
61 16-Mar-00 jcarpent 115.20 Fixed lee_rsn logic to use
62 plan level then pgm level.
63 Also removed oipl query.
64 Also query into pen loop
65 since could be different
66 29-Mar-00 jcarpent 115.21 Reset l_lee_rsn_id for each
67 plan so if not at pgm level
68 will use each plan.
69 11-Apr-00 gperry 115.22 Added extra param for fido
70 ctrl m call.
71 24-jun-00 jcarpent 115.23 Pass lee_rsn_id to
72 ben_determine_date. (5182,
73 1304658,1311768)
74 19-Jul-00 jcarpent 115.24 5241, 1343362. Process
75 comp objects in order.
76 05-Sep-00 pbodla 115.25 - Bug 5422 : Allow different enrollment
77 periods for programs for a scheduled
78 enrollment. p_popl_enrt_typ_cycl_id
79 is removed.
80 08-Nov-00 vputtiga 115.26 - Bug Fix 1485814. Set global g_enrollment_change
81 to TRUE after each call to delete_enrollment
82 05-Jan-01 kmahendr 115.27 - Check for type of life event to get the correct
83 life event
84 21-Feb-01 ikasire 115.28 remove the special treatment given
85 to the 'W' (1 prior) cases, as we need to
86 call delete_enrollment for these
87 cases also. Right now when a person
88 becomes ineligibile for a life event under
89 process, benmngle is closing the life event
90 09-May-01 kmahendr 115.29 Bug#1646404 - ENrollment result rows end dated
91 with min(lf_evt_ocrd_dt,enrl_perd_strt_dt) -1
92 06-Jun-01 kmahendr 115.30 Bug#1819106 - l_effective date is assigned the
93 value even if enrollment period start dt cd is
94 null
95 22-Jun-01 bwharton 115.31 Bug 1646404. Sometimes the effective date
96 is computed to be before the esd of the pen.
97 Move it ahead to the processed date of
98 the previous pil if greater.
99 15-Jul-01 kmahendr 115.32 Bug#1871614- in selection mode the effective
100 date is passed as one day less than benmngle
101 run date and there is no lfevt_ocrd date which
102 caused coverage to end 2 days before eff.date
103 04-Sep-01 kmahendr 115.33 Bug#1950044-Cursors for fetching enrt_rlst
104 modified so that if effective_start_date is
105 after lf_event_ocrd_dt of subsequent lf event
106 the results are pulled-similar to condition in
107 bendenrr
108 19-dec-01 pbodla 115.34 CWB Changes : fetch procd_dt's
109 of relevant per in ler's
110 26-dec-01 kmahendr 115.35 In unrestricted mode, effective_date is passed
111 for delete_enrollment
112 07-Jan-02 Rpillay 115.36 Added dbdrv and checkfile commands.
113 19-MAy-02 ikasire 115.37 Bug 2200139 Override Enrollment issues.
114 We may have the enrollments started in future
115 for Override Case.
116 23-May-02 ikasire 115.38 Bug 2200139 More Override changes
117 11-Jul-02 kmahendr 115.39 ABSENCES - Effective date is not
118 computed for absence life
119 event as start and end can occur on the same day
120 15-Jul-02 pbodla 115.40 ABSENCES - fixed cursor which
121 fetches the typ_cd
122 07-Aug-02 tjesumic 115.41 If the Ineligibility level define in program
123 all the plan in the pgm became ineligble so
124 There is no need to call the inputed income
125 calcualtion
126 14-Aug-02 stee 115.43 COBRA: Set a global variable if
127 enrollment in COBRA is terminated.
128 Bug 1794808.
129 19-Aug-02 mmudigon 115.44 call ben_comp_object only if pgm_id is not null
130 12-Sep-02 kmahendr 115.45 Bug#2508822 - Effective date is not changed
131 if coverage start date is same as effective
132 date
133 19-Nov-02 kmahendr 115.46 Bug#2641545 - effective date is changed based
134 on effective start date of pen row.
135 11-Dec-02 tjesumic 115.47 lf_evt_ocrd_dt from the table ben_benefit_actions
136 coverted into date format 'DD/MM/RRR'. the system errors
137 because the date is stored in YYYY/MM/DD format
138 This is fixed by removing the format mask in the to_date
139 this will conver the string to char in the same format
140 bug # 2688628
141 16-Sep-03 kmahendr 115.48 GSP changes
142 19-Sep-03 rpillay 115.49 GSCC warning fix
143 25-Nov-03 kmahendr 115.50 Bug#3279350- added cursors to find the deenrolled
144 results after the event date and reopen the result
145 02-jun-04 nhunur 115.51 corrected join betweeen pil and ler to avoid MJC
146 05-Jul-04 bmanyam 115.52 Bug# 3507652 - The fix 115.50 (3279350) needs to
147 run for the unrestricted le also. Enabled this.
148 15-Sep-04 pbodla 115.52 iRec : avoid using the iRec life events
149 in cursor c_procd_dt_pil and
150 Modified query in c_pen_max_esd to join
151 between pil and pen.
152 02-dec-04 ikasire 115.54 Bug 4046914
153 03-dec-04 ikasire/bala 115.55 Bug 4031416 need to call backout proces
154 23-dec-04 tjesumic 115.56 p_prt_enrt_rslt_id is passed to backout_future_coverage
155 07-Feb-05 tjesumic 115.57 call for backout_future_cvg is removed. future result is
156 taken care in delete_enrollment # 4118315
157 18-Apr-05 tjesumic 115.58 GHR enhancement to add number of days in enrt perd codeds
158 30-Jun-05 kmahendr 115.59 Bug#4463829 - added person_id parameter to
159 ben_determine_date calls
160 07-Oct-05 ssarkar 115.60 Bug 4645272 : when Open/Administartive runs in Life event mode,
161 populate enrt_perd_id and not lee_rsn_id
162 06-Jun-06 rbingi 115.61 Bug 5257226: passing effective_date to delete_enrollment as
163 delete_enrollment will end-date enrollment 1 day prior.
164 16-Nov-06 abparekh 115.62 Bug 5642702 : Defined and initialized global
165 variable G_DENROLING_FROM_PGM
166 05-Apr-07 rtagarra 115.63 Bug 6000303 : Defer Deenrollment ENH.Added
167 Procedure defer_delete_enrollment.
168 16-May-07 rtagarra 115.64 -- DO --
169 17-Nov-07 rtagarra 115.65 Bug 6634074 :
170 25-Mar-08 sallumwa 115.67 Bug 6881745 : Modified cursor c_epo_plip_defer in Procedure
171 defer_delete_enrollment,to pick-up ineligible rows with respect
172 to p_effective_date.
173 24-Jun-08 sallumwa 115.68 Bug 7181958 : Modified cursor c_epo_plip_defer in Procedure
174 defer_delete_enrollment,to pick-up ineligible rows with respect
175 to latest Life event.Reverted back the changes of 6881745.
176 21-Aug-08 sallumwa 115.69 Bug 7301670 : Modified cursor c_epo_plip_defer in Procedure
177 defer_delete_enrollment,to pick-up ineligible rows with respect
178 Active Program and not Cobra pgm when person has dual
179 program eligibility.
180 22-Aug-08 sallumwa 115.70 Bug 7342283 : Modified cursor c_pep_pgm_defer in Procedure
181 defer_delete_enrollment,to pick-up ineligible rows with respect
182 Active Program and not Cobra pgm when person has dual
183 program eligibility(for plip with no options).
184 */
185 -----------------------------------------------------------------------
186 procedure defer_delete_enrollment
187 ( p_per_in_ler_id in number
188 ,p_person_id in number
189 ,p_business_group_id in number
190 ,p_effective_date in date
191 ) is
192 --
193 l_proc varchar2(80) := g_package|| '.defer_delete_enrollment';
194 --
195 cursor c_pel_pgm_id is
196 --
197 select pel.*
198 from ben_pil_elctbl_chc_popl pel
199 where pel.per_in_ler_id = p_per_in_ler_id
200 and pel.defer_deenrol_flag = 'Y'
201 and pel.deenrol_made_dt is null
202 and pel.pil_elctbl_popl_stat_cd not in ('VOIDD','BCKDT');
203 --
204 l_pel_pgm_id c_pel_pgm_id%ROWTYPE;
205 --
206 cursor c_pep_pgm_defer(p_pgm_id number) is
207 --
208 select pen.*
209 from ben_prtt_enrt_rslt_f pen
210 ,ben_elig_per_f pep
211 where pen.pgm_id = p_pgm_id
212 ---Bug 7342283
213 and pen.pgm_id = pep.pgm_id
214 ----Bug 7342283
215 and pen.pl_id = pep.pl_id
216 and pep.elig_flag = 'N'
217 and pep.per_in_ler_id = p_per_in_ler_id
218 and pen.prtt_enrt_rslt_stat_cd is null
219 and pen.effective_end_date = hr_api.g_eot
220 and pen.enrt_cvg_thru_dt = hr_api.g_eot
221 and pen.oipl_id is null
222 and pen.person_id = p_person_id;
223 --
224 l_pep_pgm_defer c_pep_pgm_defer%ROWTYPE;
225 --
226 cursor c_pep_pl_defer(p_pl_id number) is
227 --
228 select pen.*
229 from ben_prtt_enrt_rslt_f pen
230 ,ben_elig_per_f pep
231 where pen.pl_id = p_pl_id
232 and pep.pl_id = pen.pl_id
233 and pep.elig_flag = 'N'
234 and pen.pgm_id is null
235 and pen.oipl_id is null
236 and pep.per_in_ler_id = p_per_in_ler_id
237 and pen.prtt_enrt_rslt_stat_cd is null
238 and pen.effective_end_date = hr_api.g_eot
239 and pen.enrt_cvg_thru_dt = hr_api.g_eot
240 and pen.person_id = p_person_id;
241 --
242 l_pep_pl_defer c_pep_pl_defer%ROWTYPE;
243 --
244 cursor c_epo_plip_defer(p_pgm_id number) is
245 --
246 select pen.*
247 from ben_elig_per_opt_f epo
248 ,ben_prtt_enrt_rslt_f pen
249 ,ben_oipl_f oipl
250 ,ben_elig_per_f pep
251 where pen.pgm_id = p_pgm_id
252 ---Bug 7301670
253 and pen.pgm_id = pep.pgm_id
254 ----Bug 7301670
255 and pep.pl_id = pen.pl_id
256 and pen.oipl_id = oipl.oipl_id
257 and oipl.opt_id = epo.opt_id
258 and epo.elig_per_id = pep.elig_per_id
259 and pep.per_in_ler_id = p_per_in_ler_id
260 and epo.elig_flag = 'N'
261 --Bug : 6881745
262 /* AND p_effective_date
263 BETWEEN epo.effective_start_date AND epo.effective_end_date */
264 --Bug : 7181958
265 and epo.effective_end_date = hr_api.g_eot
266 --Bug : 6881745
267 and pen.effective_end_date = hr_api.g_eot
268 and pen.enrt_cvg_thru_dt = hr_api.g_eot
269 and pen.prtt_enrt_rslt_stat_cd is null
270 and pen.person_id = p_person_id;
271 --
272 l_epo_plip_defer c_epo_plip_defer%ROWTYPE;
273 --
274 cursor c_epo_plnip_defer(p_pl_id number) is
275 --
276 select pen.*
277 from ben_elig_per_opt_f epo
278 ,ben_prtt_enrt_rslt_f pen
279 ,ben_oipl_f oipl
280 ,ben_elig_per_f pep
281 where pen.pl_id = p_pl_id
282 and pep.pl_id = pen.pl_id
283 and pen.oipl_id = oipl.oipl_id
284 and pep.pgm_id is null
285 and epo.elig_per_id = pep.elig_per_id
286 and pep.per_in_ler_id = p_per_in_ler_id
287 and oipl.opt_id = epo.opt_id
288 and epo.elig_flag = 'N'
289 and pen.effective_end_date = hr_api.g_eot
290 and pen.enrt_cvg_thru_dt = hr_api.g_eot
291 and pen.prtt_enrt_rslt_stat_cd is null
292 and pen.person_id = p_person_id;
293 --
294 l_epo_plnip_defer c_epo_plnip_defer%ROWTYPE;
295 --
296 l_effective_start_date date;
297 l_effective_end_date date;
298 l_effective_date date;
299 --
300 begin
301 --
302 hr_utility.set_location ('Entering '|| l_proc|| p_effective_date,10);
303 --
304 l_effective_date := p_effective_date;
305 --
306 open c_pel_pgm_id;
307 loop
308 fetch c_pel_pgm_id into l_pel_pgm_id;
309 exit when c_pel_pgm_id%NOTFOUND;
310 --
311 if l_pel_pgm_id.pgm_id is not null then
312 --
313 hr_utility.set_location('PGM'|| l_pel_pgm_id.pgm_id,24);
314 --
315 open c_pep_pgm_defer(l_pel_pgm_id.pgm_id);
316 loop
317 fetch c_pep_pgm_defer into l_pep_pgm_defer;
318 exit when c_pep_pgm_defer%NOTFOUND;
319 --
320 if c_pep_pgm_defer%found then
321 --
322 hr_utility.set_location ('PEN'|| l_pep_pgm_defer.prtt_enrt_rslt_id,46000);
323 --
324 if l_effective_date <= l_pep_pgm_defer.effective_start_date then
325 l_effective_date := l_pep_pgm_defer.effective_start_date + 1;
326 --
327 end if;
328 --
329 ben_PRTT_ENRT_RESULT_api.delete_enrollment(
330 p_validate => false,
331 p_per_in_ler_id => p_per_in_ler_id,
332 p_prtt_enrt_rslt_id => l_pep_pgm_defer.prtt_enrt_rslt_id,
333 p_effective_start_date => l_effective_start_date,
334 p_effective_end_date => l_effective_end_date,
335 p_object_version_number => l_pep_pgm_defer.object_version_number,
336 p_business_group_id => p_business_group_id,
337 p_effective_date => l_effective_date,
338 p_datetrack_mode => 'DELETE',
339 p_source => 'beninelg',
340 p_multi_row_validate => FALSE);
341 --
342 end if;
343 --
344 end loop;
345 --
346 close c_pep_pgm_defer;
347 --
348 open c_epo_plip_defer(l_pel_pgm_id.pgm_id);
349 loop
350 fetch c_epo_plip_defer into l_epo_plip_defer;
351 exit when c_epo_plip_defer%NOTFOUND;
352 --
353 hr_utility.set_location ('EPO PEN '|| l_epo_plip_defer.prtt_enrt_rslt_id||'DATE'||l_effective_date,10007);
354 --
355 if c_epo_plip_defer%found then
356 --
357 if l_effective_date <= l_epo_plip_defer.effective_start_date then
358 l_effective_date := l_epo_plip_defer.effective_start_date + 1;
359 --
360 end if;
361 --
362 ben_PRTT_ENRT_RESULT_api.delete_enrollment(
363 p_validate => FALSE,
364 p_per_in_ler_id => p_per_in_ler_id,
365 p_prtt_enrt_rslt_id => l_epo_plip_defer.prtt_enrt_rslt_id,
366 p_effective_start_date => l_effective_start_date,
367 p_effective_end_date => l_effective_end_date,
368 p_object_version_number => l_epo_plip_defer.object_version_number,
369 p_business_group_id => p_business_group_id,
370 p_effective_date => l_effective_date,
371 p_datetrack_mode => 'DELETE',
372 p_source => 'beninelg',
373 p_multi_row_validate => FALSE);
374 --
375 end if;
376 --
377 end loop;
378 --
379 close c_epo_plip_defer;
380 --
381 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
382 (p_validate => FALSE
383 ,p_pil_elctbl_chc_popl_id => l_pel_pgm_id.pil_elctbl_chc_popl_id
384 ,p_object_version_number => l_pel_pgm_id.object_version_number
385 ,p_effective_date => p_effective_date
386 ,p_defer_deenrol_flag => 'Y'
387 ,p_deenrol_made_dt => p_effective_date
388 );
389 --
390 elsif l_pel_pgm_id.pl_id is not null then
391 --
392 open c_pep_pl_defer(l_pel_pgm_id.pl_id);
393 loop
394 fetch c_pep_pl_defer into l_pep_pl_defer;
395 exit when c_pep_pl_defer%NOTFOUND;
396 --
397 if c_pep_pl_defer%found then
398 --
399 hr_utility.set_location ('PEN'|| l_pep_pl_defer.prtt_enrt_rslt_id,46000);
400 --
401 if l_effective_date <= l_pep_pl_defer.effective_start_date then
402 l_effective_date := l_pep_pl_defer.effective_start_date + 1;
403 --
404 end if;
405 --
406 ben_PRTT_ENRT_RESULT_api.delete_enrollment(
407 p_validate => false,
408 p_per_in_ler_id => p_per_in_ler_id,
409 p_prtt_enrt_rslt_id => l_pep_pl_defer.prtt_enrt_rslt_id,
410 p_effective_start_date => l_effective_start_date,
411 p_effective_end_date => l_effective_end_date,
412 p_object_version_number => l_pep_pl_defer.object_version_number,
413 p_business_group_id => p_business_group_id,
414 p_effective_date => l_effective_date,
415 p_datetrack_mode => 'DELETE',
416 p_source => 'beninelg',
417 p_multi_row_validate => FALSE);
418 --
419 end if;
420 --
421 end loop;
422 --
423 close c_pep_pl_defer;
424 --
425 open c_epo_plnip_defer(l_pel_pgm_id.pl_id);
426 loop
427 fetch c_epo_plnip_defer into l_epo_plnip_defer;
428 exit when c_epo_plnip_defer%NOTFOUND;
429 --
430 hr_utility.set_location ('EPO PEN '|| l_epo_plip_defer.prtt_enrt_rslt_id,10007);
431 --
432 if l_effective_date <= l_epo_plnip_defer.effective_start_date then
433 l_effective_date := l_epo_plnip_defer.effective_start_date + 1;
434 --
435 end if;
436 --
437 ben_PRTT_ENRT_RESULT_api.delete_enrollment(
438 p_validate => FALSE,
439 p_per_in_ler_id => p_per_in_ler_id,
440 p_prtt_enrt_rslt_id => l_epo_plnip_defer.prtt_enrt_rslt_id,
441 p_effective_start_date => l_effective_start_date,
442 p_effective_end_date => l_effective_end_date,
443 p_object_version_number => l_epo_plnip_defer.object_version_number,
444 p_business_group_id => p_business_group_id,
445 p_effective_date => l_effective_date,
446 p_datetrack_mode => 'DELETE',
447 p_source => 'beninelg',
448 p_multi_row_validate => FALSE);
449 --
450 end loop;
451 --
452 close c_epo_plnip_defer;
453 --
454 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
455 (p_validate => FALSE
456 ,p_pil_elctbl_chc_popl_id => l_pel_pgm_id.pil_elctbl_chc_popl_id
457 ,p_object_version_number => l_pel_pgm_id.object_version_number
458 ,p_effective_date => p_effective_date
459 ,p_defer_deenrol_flag => 'Y'
460 ,p_deenrol_made_dt => p_effective_date
461 );
462
463 end if;
464 --
465 end loop;
466 --
467 close c_pel_pgm_id;
468 --
469 hr_utility.set_location ('Leaving'||l_proc,10);
470 --
471 end defer_delete_enrollment;
472 --
473 --
474 procedure main(p_person_id in number,
475 p_pgm_id in number default null,
476 p_pl_id in number default null,
477 p_oipl_id in number default null,
478 p_business_group_id in number,
479 p_ler_id in number,
480 p_effective_date in date) is
481 --
482 l_proc varchar2(80) := g_package|| '.main';
483 l_level varchar2(30);
484 l_eff_strt date;
485 l_eff_end date;
486 l_per_in_ler_id number := null;
487 l_lee_rsn_id number := null;
488 --
489 l_enrt_cvg_end_dt_cd varchar2(30);
490 l_ovn number;
491 l_dummy_dt date;
492 l_dummy_num number;
493 l_dummy_varchar varchar2(30);
494 l_oipl_rec ben_oipl_f%rowtype; -- TM added for backport of 115.6
495 l_pgm_rec ben_pgm_f%rowtype;
496
497 l_effective_date date;
498 l_effective_date_1 date := p_effective_date - 1;
499 -- Bug 2200139 added effective_start_date, enrt_mthd_cd for Override Case
500 cursor c_pen_pl is
501 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,ptip_id,
502 pen.effective_start_date,pen.enrt_mthd_cd,
503 pen.enrt_cvg_strt_dt
504 from ben_prtt_enrt_rslt_f pen
505 where p_pl_id = pen.pl_id
506 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
507 and pen.person_id = p_person_id
508 and pen.business_group_id+0 = p_business_group_id
509 and pen.prtt_enrt_rslt_stat_cd is null
510 and pen.effective_end_date = hr_api.g_eot
511 and pen.enrt_cvg_thru_dt = hr_api.g_eot -- 9999
512 and l_effective_date_1 <= pen.enrt_cvg_thru_dt
513 and pen.enrt_cvg_strt_dt < pen.effective_end_date
514 order by effective_start_date desc;
515 /*
516 and pen.enrt_cvg_thru_dt > p_effective_date
517 and p_effective_date
518 between pen.effective_start_date
519 and pen.effective_end_date;
520 */
521 -- -- Bug 2200139 added effective_start_date, enrt_mthd_cd for Override Case
522 cursor c_pen_pgm is
523 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,ptip_id,
524 pen.effective_start_date,pen.enrt_mthd_cd,
525 pen.enrt_cvg_strt_dt
526 from ben_prtt_enrt_rslt_f pen
527 where p_pgm_id = pen.pgm_id
528 and pen.person_id = p_person_id
529 and pen.business_group_id+0 = p_business_group_id
530 and pen.prtt_enrt_rslt_stat_cd is null
531 and pen.effective_end_date = hr_api.g_eot
532 and pen.enrt_cvg_thru_dt = hr_api.g_eot -- 9999
533 and l_effective_date_1 <= pen.enrt_cvg_thru_dt
534 and pen.enrt_cvg_strt_dt < pen.effective_end_date
535 order by effective_start_date desc;
536 /*
537 and pen.enrt_cvg_thru_dt > p_effective_date
538 and p_effective_date
539 between pen.effective_start_date
540 and pen.effective_end_date;
541 */
542 -- -- Bug 2200139 added effective_start_date, enrt_mthd_cd for Override Case
543 cursor c_pen_oipl is
544 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,
545 pen.effective_start_date,pen.enrt_mthd_cd,
546 pen.enrt_cvg_strt_dt
547 from ben_prtt_enrt_rslt_f pen
548 where p_oipl_id = pen.oipl_id
549 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
550 and pen.person_id = p_person_id
551 and pen.business_group_id+0 = p_business_group_id
552 and pen.prtt_enrt_rslt_stat_cd is null
553 and pen.effective_end_date = hr_api.g_eot
554 and pen.enrt_cvg_thru_dt = hr_api.g_eot -- 9999
555 and l_effective_date_1 <= pen.enrt_cvg_thru_dt
556 and pen.enrt_cvg_strt_dt < pen.effective_end_date
557 order by effective_start_date desc;
558 /*
559 and pen.enrt_cvg_thru_dt > p_effective_date
560 and p_effective_date
561 between pen.effective_start_date
562 and pen.effective_end_date;
563 */
564 --
565 cursor c_lee_rsn_pl(p_pl_id number) is
566 select lee.lee_rsn_id,
567 lee.enrt_perd_strt_dt_cd,
568 lee.enrt_perd_strt_dt_rl,
569 lee.enrt_perd_strt_days
570 from ben_lee_rsn_f lee,
571 ben_popl_enrt_typ_cycl_f pop
572 where pop.pl_id = p_pl_id
573 and pop.business_group_id +0 = p_business_group_id
574 and p_effective_date
575 between pop.effective_start_date
576 and pop.effective_end_date
577 and pop.popl_enrt_typ_cycl_id = lee.popl_enrt_typ_cycl_id
578 and lee.business_group_id +0 = pop.business_group_id
579 and lee.ler_id = p_ler_id
580 and p_effective_date
581 between lee.effective_start_date
582 and lee.effective_end_date;
583 --
584 -- PB :5422
585 --
586 CURSOR c_sched_enrol_period_for_plan(p_pl_id number,
587 p_lf_evt_ocrd_dt date) IS
588 SELECT enrtp.enrt_perd_id,
589 enrtp.strt_dt
590 FROM ben_popl_enrt_typ_cycl_f petc,
591 ben_enrt_perd enrtp
592 WHERE petc.pl_id = p_pl_id
593 AND petc.business_group_id = p_business_group_id
594 AND p_lf_evt_ocrd_dt BETWEEN petc.effective_start_date
595 AND petc.effective_end_date
596 AND petc.enrt_typ_cycl_cd <> 'L'
597 AND enrtp.business_group_id = p_business_group_id
598 AND enrtp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
599 /* PB :5422 AND enrtp.strt_dt=enrtp1.strt_dt
600 AND enrtp1.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
601 AND enrtp1.business_group_id = p_business_group_id */
602 AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id;
603 --
604 -- This cursor gets the enrolment period for scheduled
605 -- elections for program level
606 --
607 CURSOR c_sched_enrol_period_for_pgm(p_pgm_id number,
608 p_lf_evt_ocrd_dt date) IS
609 SELECT enrtp.enrt_perd_id,
610 enrtp.strt_dt
611 FROM ben_popl_enrt_typ_cycl_f petc,
612 ben_enrt_perd enrtp
613 WHERE petc.pgm_id = p_pgm_id
614 AND petc.business_group_id = p_business_group_id
615 AND p_lf_evt_ocrd_dt BETWEEN petc.effective_start_date
616 AND petc.effective_end_date
617 AND petc.enrt_typ_cycl_cd <> 'L'
618 AND enrtp.business_group_id = p_business_group_id
619 AND enrtp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
620 /* PB :5422 AND enrtp1.business_group_id = p_business_group_id
621 AND enrtp.strt_dt= enrtp1.strt_dt
622 AND enrtp1.enrt_perd_id = p_popl_enrt_typ_cycl_id */
623 AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id;
624 --
625 cursor c_lee_rsn_pgm(p_pgm_id number) is
626 select lee.lee_rsn_id,
627 lee.enrt_perd_strt_dt_cd,
628 lee.enrt_perd_strt_dt_rl,
629 lee.enrt_perd_strt_days
630 from ben_lee_rsn_f lee,
631 ben_popl_enrt_typ_cycl_f pop
632 where pop.pgm_id = p_pgm_id
633 and pop.business_group_id +0 = p_business_group_id
634 and p_effective_date
635 between pop.effective_start_date
636 and pop.effective_end_date
637 and pop.popl_enrt_typ_cycl_id = lee.popl_enrt_typ_cycl_id
638 and lee.business_group_id +0 = pop.business_group_id
639 and lee.ler_id = p_ler_id
640 and p_effective_date
641 between lee.effective_start_date
642 and lee.effective_end_date;
643 --
644 cursor c_imptd(v_pl_id in number) is
645 select 'Y'
646 from ben_pl_f pl
647 where pl.pl_id = v_pl_id
648 and pl.business_group_id + 0 = p_business_group_id
649 and p_effective_date between
650 pl.effective_start_date and pl.effective_end_date
651 and pl.subj_to_imptd_incm_typ_cd = 'PRTT';
652 --
653 -- CWB Changes.
654 --
655 cursor c_per_in_ler is
656 select pil.lf_evt_ocrd_dt, ler.typ_cd
657 from ben_per_in_ler pil,
658 ben_ler_f ler
659 where pil.per_in_ler_id = l_per_in_ler_id
660 and pil.ler_id = ler.ler_id
661 and p_effective_date between ler.effective_start_date and
662 ler.effective_end_date;
663 --
664 -- 22-Jun-01 bwharton 115.31
665 --
666 -- CWB Changes.
667 --
668 cursor c_procd_dt_pil is
669 select pil.procd_dt
670 from ben_per_in_ler pil,
671 ben_ler_f ler
672 where pil.person_id = p_person_id
673 and pil.per_in_ler_id <> l_per_in_ler_id
674 and pil.per_in_ler_stat_cd = 'PROCD'
675 and pil.ler_id = ler.ler_id
676 and p_effective_date between
677 ler.effective_start_date and ler.effective_end_date
678 and ler.typ_cd not in ('COMP','GSP', 'IREC')
679 order by lf_evt_ocrd_dt desc;
680
681 cursor c_procd_dt_pil_cwb is
682 select pil.procd_dt
683 from ben_per_in_ler pil,
684 ben_ler_f ler
685 where pil.person_id = p_person_id
686 and pil.per_in_ler_id <> l_per_in_ler_id
687 and pil.per_in_ler_stat_cd = 'PROCD'
688 and pil.ler_id = ler.ler_id
689 and p_effective_date between
690 ler.effective_start_date and ler.effective_end_date
691 and ler.ler_id = p_ler_id
692 and ler.typ_cd = 'COMP'
693 order by lf_evt_ocrd_dt desc;
694 --
695 --Bug#3279350
696
697 cursor c_pen_pl_2 is
698 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,ptip_id,
699 pen.effective_start_date,pen.enrt_mthd_cd,
700 pen.enrt_cvg_strt_dt
701 from ben_prtt_enrt_rslt_f pen
702 where p_pl_id = pen.pl_id
703 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
704 and pen.person_id = p_person_id
705 and pen.business_group_id = p_business_group_id
706 and pen.prtt_enrt_rslt_stat_cd is null
707 and pen.effective_end_date = hr_api.g_eot
708 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
709 and l_effective_date_1 < pen.enrt_cvg_thru_dt;
710 --
711 cursor c_pen_pgm_2 is
712 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,ptip_id,
713 pen.effective_start_date,pen.enrt_mthd_cd,
714 pen.enrt_cvg_strt_dt
715 from ben_prtt_enrt_rslt_f pen
716 where p_pgm_id = pen.pgm_id
717 and pen.person_id = p_person_id
718 and pen.business_group_id = p_business_group_id
719 and pen.prtt_enrt_rslt_stat_cd is null
720 and pen.effective_end_date = hr_api.g_eot
721 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
722 and l_effective_date_1 < pen.enrt_cvg_thru_dt;
723 --
724 cursor c_pen_oipl_2 is
725 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,
726 pen.effective_start_date,pen.enrt_mthd_cd,
727 pen.enrt_cvg_strt_dt
728 from ben_prtt_enrt_rslt_f pen
729 where p_oipl_id = pen.oipl_id
730 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
731 and pen.person_id = p_person_id
732 and pen.business_group_id = p_business_group_id
733 and pen.prtt_enrt_rslt_stat_cd is null
734 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
735 and pen.effective_end_date = hr_api.g_eot
736 and l_effective_date_1 < pen.enrt_cvg_thru_dt;
737 --
738 cursor c_pen_max_esd (v_prtt_enrt_rslt_id in number) is
739 select pen.effective_end_date,pen.object_version_number
740 from ben_prtt_enrt_rslt_f pen,
741 ben_per_in_ler pil
742 where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
743 and pen.business_group_id = p_business_group_id
744 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
745 and pen.per_in_ler_id = pil.per_in_ler_id
746 and pen.effective_end_date <> hr_api.g_eot
747 order by pen.effective_end_date desc;
748 --
749 l_correction boolean;
750 l_update boolean;
751 l_update_override boolean;
752 l_update_change_insert boolean;
753 l_imptd_incm_chg boolean := false;
754 l_imptd_incm_flag varchar2(30);
755 l_inelig_lvl_in_pgm varchar2(30):= 'N' ;
756 l_datetrack_mode varchar2(30);
757 l_env_rec ben_env_object.g_global_env_rec_type;
758 l_benmngle_parm_rec benutils.g_batch_param_rec;
759 l_param_lf_evt_ocrd_dt date;
760 l_enrt_perd_id number;
761 l_LF_EVT_OCRD_DT date;
762 l_typ_cd varchar2(30);
763 l_ENRT_PERD_STRT_DT date;
764 l_procd_dt date; -- 22-Jun-01 bwharton 115.31
765 l_enrt_perd_strt_dt_cd varchar2(100);
766 l_enrt_perd_strt_dt_rl varchar2(100);
767 l_enrt_perd_strt_days number ;
768 l_enrt_strt_dt date;
769 l_pen_max_esd c_pen_max_esd%rowtype;
770 l_effective_start_date date;
771 l_effective_end_date date;
772
773
774 --
775 begin
776 --
777 hr_utility.set_location ('Entering '||l_proc,10);
778 hr_utility.set_location ('p_ler_id '||p_ler_id,10);
779 hr_utility.set_location ('p_pgm_id '||p_pgm_id,10);
780 hr_utility.set_location ('p_pl_id '||p_pl_id,10);
781 hr_utility.set_location ('p_oipl_id '||p_oipl_id,10);
782 --
783 -- Bug 5642702
784 -- If person is de-enroling at Program level then set variable G_DENROLING_FROM_PGM
785 -- so that we can obviate calls that create/update records in BEN_BNFT_PRVDD_LDGR_F table
786 -- See bebplapi.pkb for use of this variable
787 --
788 if p_oipl_id is null and p_pl_id is null and p_pgm_id is not null
789 then
790 --
791 g_denroling_from_pgm := 'Y';
792 --
793 else
794 --
795 g_denroling_from_pgm := 'N';
796 --
797 end if;
798 --
799 hr_utility.set_location ('g_denroling_from_pgm = ' || g_denroling_from_pgm, 15);
800 --
801 --
802 -- Bug#3279350 - undelete the prtt_enrt_rslt deenrolled in the future for OSB
803 -- Bug# 3507652 - Added "p_ler_id = ben_manage_life_events.g_ler_id" to delete future end-dated enrollments
804 -- when running in 'Unrestricted Mode' as well.
805 if ((p_ler_id is null) or (p_ler_id = ben_manage_life_events.g_ler_id)) then
806 --
807 hr_utility.set_location ('undelete prtt_enrt_rslts',10);
808 if p_oipl_id is not null then
809 for l_pen in c_pen_oipl_2 loop
810 -- call undelete
811 open c_pen_max_esd(l_pen.prtt_enrt_rslt_id);
812 fetch c_pen_max_esd into l_pen_max_esd;
813 if c_pen_max_esd%found then
814 ben_prtt_enrt_result_api.delete_prtt_enrt_result
815 (p_validate => false,
816 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
817 p_effective_start_date => l_effective_start_date,
818 p_effective_end_date => l_effective_end_date,
819 p_object_version_number => l_pen_max_esd.object_version_number,
820 p_effective_date => l_pen_max_esd.effective_end_date,
821 p_datetrack_mode => hr_api.g_future_change,
822 p_multi_row_validate => FALSE);
823 end if;
824 close c_pen_max_esd;
825
826 end loop;
827 elsif p_pl_id is not null then
828 for l_pen in c_pen_pl_2 loop
829 -- call undelete
830 open c_pen_max_esd(l_pen.prtt_enrt_rslt_id);
831 fetch c_pen_max_esd into l_pen_max_esd;
832 if c_pen_max_esd%found then
833 ben_prtt_enrt_result_api.delete_prtt_enrt_result
834 (p_validate => false,
835 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
836 p_effective_start_date => l_effective_start_date,
837 p_effective_end_date => l_effective_end_date,
838 p_object_version_number => l_pen_max_esd.object_version_number,
839 p_effective_date => l_pen_max_esd.effective_end_date,
840 p_datetrack_mode => hr_api.g_future_change,
841 p_multi_row_validate => FALSE);
842 end if;
843 close c_pen_max_esd;
844 --
845 end loop;
846 elsif p_pgm_id is not null then
847 for l_pen in c_pen_pgm_2 loop
848 --
849 open c_pen_max_esd(l_pen.prtt_enrt_rslt_id);
850 fetch c_pen_max_esd into l_pen_max_esd;
851 if c_pen_max_esd%found then
852 ben_prtt_enrt_result_api.delete_prtt_enrt_result
853 (p_validate => false,
854 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
855 p_effective_start_date => l_effective_start_date,
856 p_effective_end_date => l_effective_end_date,
857 p_object_version_number => l_pen_max_esd.object_version_number,
858 p_effective_date => l_pen_max_esd.effective_end_date,
859 p_datetrack_mode => hr_api.g_future_change,
860 p_multi_row_validate => FALSE);
861 end if;
862 close c_pen_max_esd;
863 --
864 end loop;
865 end if;
866 --
867 end if;
868 -- Attempt to get the active per in ler id for the person
869 --
870 if p_ler_id is not null then
871 --
872 -- check whether ler_id is of type unrestricted
873 if p_ler_id = ben_manage_life_events.g_ler_id then
874 --
875 l_per_in_ler_id := benutils.get_per_in_ler_id
876 (p_person_id => p_person_id,
877 p_business_group_id => p_business_group_id,
878 p_ler_id => p_ler_id,
879 p_lf_event_mode => 'U',
880 p_effective_date => p_effective_date);
881 else
882 --
883 l_per_in_ler_id := benutils.get_per_in_ler_id
884 (p_person_id => p_person_id,
885 p_business_group_id => p_business_group_id,
886 p_ler_id => p_ler_id,
887 p_effective_date => p_effective_date);
888 hr_utility.set_location('l_typ_cd ='||l_typ_cd,10.5);
889 --
890 end if;
891 --
892 hr_utility.set_location('l_per_in_ler_id '||l_per_in_ler_id,10);
893 --
894 end if;
895 --
896 open c_per_in_ler;
897 fetch c_per_in_ler into l_lf_evt_ocrd_dt, l_typ_cd;
898 close c_per_in_ler;
899 hr_utility.set_location('l_typ_cd ='||l_typ_cd,11.5);
900 -- Get enrt_perd_id from benmngle's parm list, if it exists.
901 -- Enrt_perd_id is in benmngle's popl_enrt_typ_cycl_id field.
902 ben_env_object.get(p_rec => l_env_rec);
903 benutils.get_batch_parameters(p_benefit_action_id => l_env_rec.benefit_action_id
904 ,p_rec => l_benmngle_parm_rec);
905 --
906 --
907
908
909 if l_benmngle_parm_rec.lf_evt_ocrd_dt is not null then
910
911 /* l_param_lf_evt_ocrd_dt :=
912 to_date(l_benmngle_parm_rec.lf_evt_ocrd_dt,'YYYY/MM/DD HH24:MI:SS');
913 hr_utility.set_location('l_per_in_ler_id '||l_per_in_ler_id,11);
914 l_param_lf_evt_ocrd_dt :=
915 to_date(to_char(trunc(l_param_lf_evt_ocrd_dt),'DD/MM/RRRR'),'DD/MM/RRRR');
916 l_param_lf_evt_ocrd_dt := to_date(l_benmngle_parm_rec.lf_evt_ocrd_dt);
917 */
918 --BUG 4046914
919 l_param_lf_evt_ocrd_dt :=
920 fnd_date.canonical_to_date(fnd_date.date_to_canonical(l_benmngle_parm_rec.lf_evt_ocrd_dt));
921
922
923 end if;
924 --
925 hr_utility.set_location('l_per_in_ler_id '||l_per_in_ler_id,12);
926 if p_oipl_id is not null then
927 -- PB : 5422 :
928 -- if l_benmngle_parm_rec.popl_enrt_typ_cycl_id is null then
929 if l_param_lf_evt_ocrd_dt is null then
930 --
931 -- TM added for backport of 115.6
932 --
933 ben_comp_object.get_object(p_oipl_id => p_oipl_id,
934 p_rec => l_oipl_rec);
935 --
936 end if;
937
938 for l_pen in c_pen_oipl loop
939 --
940 -- PB : 5422 :
941 -- if l_benmngle_parm_rec.popl_enrt_typ_cycl_id is null then
942 --
943 l_enrt_perd_id := null;
944
945 -- bug : 4645272 : when Open/Administartive runs in Life event mode, populate enrt_perd_id
946 if l_param_lf_evt_ocrd_dt is null and l_typ_cd not in ('SCHEDDO','SCHEDDA') then
947 l_lee_rsn_id:=null;
948
949 open c_lee_rsn_pl(l_pen.pl_id);
950 fetch c_lee_rsn_pl into l_lee_rsn_id,l_enrt_perd_strt_dt_cd,l_enrt_perd_strt_dt_rl, l_enrt_perd_strt_days;
951 close c_lee_rsn_pl;
952 if l_lee_rsn_id is null then
953 open c_lee_rsn_pgm(l_pen.pgm_id);
954 fetch c_lee_rsn_pgm into l_lee_rsn_id,l_enrt_perd_strt_dt_cd,l_enrt_perd_strt_dt_rl, l_enrt_perd_strt_days;
955 close c_lee_rsn_pgm;
956 end if;
957 if l_enrt_perd_strt_dt_cd is not null then
958 ben_determine_date.main(
959 p_date_cd => l_enrt_perd_strt_dt_cd,
960 p_person_id => p_person_id,
961 p_per_in_ler_id => l_per_in_ler_id,
962 p_pgm_id => p_pgm_id,
963 p_pl_id => p_pl_id,
964 p_oipl_id => p_oipl_id,
965 p_business_group_id => p_business_group_id,
966 p_formula_id => l_enrt_perd_strt_dt_rl,
967 p_effective_date => p_effective_date,
968 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
969 p_returned_date => l_enrt_perd_strt_dt);
970
971 if l_enrt_perd_strt_dt_cd in ( 'NUMDOE', 'NUMDON','NUMDOEN') then
972 l_enrt_perd_strt_dt := l_enrt_perd_strt_dt + nvl(l_enrt_perd_strt_days,0) ;
973 end if ;
974 end if;
975 --
976 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_PERD_STRT_DT,p_effective_date));-- 5257226 - 1;
977 -- Enrollments were getting end-dated 2 days prior if passed eff_dt-1, passing the eff_dt now.
978
979 hr_utility.set_location('l_effective_date '||l_effective_date,10);
980 else
981 --
982
983 -- PB : 5422 :
984 --
985 OPEN c_sched_enrol_period_for_plan(l_pen.pl_id,
986 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
987 FETCH c_sched_enrol_period_for_plan INTO l_enrt_perd_id,l_enrt_strt_dt;
988 --
989 IF c_sched_enrol_period_for_plan%NOTFOUND THEN
990 --
991 OPEN c_sched_enrol_period_for_pgm(l_pen.pgm_id,
992 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
993 FETCH c_sched_enrol_period_for_pgm INTO l_enrt_perd_id,l_enrt_strt_dt;
994 CLOSE c_sched_enrol_period_for_pgm;
995 --
996 END IF;
997 CLOSE c_sched_enrol_period_for_plan;
998 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_STRT_DT,p_effective_date)); -- 5257226 - 1;
999 --
1000 end if;
1001 --
1002 open c_imptd(l_pen.pl_id);
1003 fetch c_imptd into l_imptd_incm_flag;
1004 if c_imptd%found then
1005 --
1006 l_imptd_incm_chg := true;
1007 --
1008 end if;
1009 close c_imptd;
1010 --
1011 /*
1012 ben_provider_pools.recompute_flex_credits(
1013 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1014 p_pgm_id => l_pen.pgm_id,
1015 p_per_in_ler_id => l_per_in_ler_id,
1016 p_person_id => p_person_id,
1017 p_enrt_mthd_cd => 'E',
1018 p_business_group_id => p_business_group_id,
1019 p_effective_date => p_effective_date);
1020 --
1021 ben_determine_date.rate_and_coverage_dates
1022 (p_which_dates_cd => 'C',
1023 p_date_mandatory_flag => 'N',
1024 p_compute_dates_flag => 'N',
1025 p_per_in_ler_id => l_per_in_ler_id,
1026 p_person_id => p_person_id,
1027 p_pgm_id => l_pen.pgm_id,
1028 p_pl_id => l_pen.pl_id,
1029 p_oipl_id => l_pen.oipl_id,
1030 p_business_group_id => p_business_group_id,
1031 p_enrt_cvg_strt_dt => l_dummy_dt,
1032 p_enrt_cvg_strt_dt_cd => l_dummy_varchar,
1033 p_enrt_cvg_strt_dt_rl => l_dummy_num,
1034 p_rt_strt_dt => l_dummy_dt,
1035 p_rt_strt_dt_cd => l_dummy_varchar,
1036 p_rt_strt_dt_rl => l_dummy_num,
1037 p_enrt_cvg_end_dt => l_dummy_dt,
1038 p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd,
1039 p_enrt_cvg_end_dt_rl => l_dummy_num,
1040 p_rt_end_dt => l_dummy_dt,
1041 p_rt_end_dt_cd => l_dummy_varchar,
1042 p_rt_end_dt_rl => l_dummy_num,
1043 p_effective_date => p_effective_date,
1044 p_lf_evt_ocrd_dt => null,
1045 p_lee_rsn_id => l_lee_rsn_id
1046 );
1047 --
1048 if substr(nvl(l_enrt_cvg_end_dt_cd, '-1'), 1, 1) = 'W' then
1049 --
1050 l_ovn := l_pen.object_version_number;
1051 --
1052 dt_api.find_dt_upd_modes
1053 (p_effective_date => p_effective_date,
1054 p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
1055 p_base_key_column => 'prtt_enrt_rslt_id',
1056 p_base_key_value => l_pen.prtt_enrt_rslt_id,
1057 p_correction => l_correction,
1058 p_update => l_update,
1059 p_update_override => l_update_override,
1060 p_update_change_insert => l_update_change_insert);
1061 --
1062 if l_update_override then
1063 --
1064 l_datetrack_mode := hr_api.g_update_override;
1065 --
1066 elsif l_update then
1067 --
1068 l_datetrack_mode := hr_api.g_update;
1069 --
1070 else
1071 --
1072 l_datetrack_mode := hr_api.g_correction;
1073 --
1074 end if;
1075 --
1076 ben_prtt_enrt_result_api.update_PRTT_ENRT_RESULT
1077 (p_validate => false
1078 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1079 ,p_effective_start_date => l_eff_strt
1080 ,p_effective_end_date => l_eff_end
1081 ,p_ler_id => p_ler_id
1082 ,p_no_lngr_elig_flag => 'Y'
1083 ,p_object_version_number => l_ovn
1084 ,p_per_in_ler_id => l_per_in_ler_id
1085 ,p_effective_date => p_effective_date
1086 ,p_datetrack_mode => l_datetrack_mode
1087 ,p_multi_row_validate => false
1088 );
1089 --
1090 else
1091 --
1092 */
1093 --
1094 -- 22-Jun-01 bwharton 115.31
1095 l_procd_dt := l_effective_date;
1096 --
1097 -- CWB changes : First determine the typ_cd of current per in ler
1098 -- and look for only COMP typ per in ler's if current per in ler is
1099 -- Comp type.
1100 --
1101 if l_typ_cd = 'COMP' then
1102 --
1103 for procd_dt_rec in c_procd_dt_pil_cwb loop
1104 l_procd_dt := procd_dt_rec.procd_dt;
1105 exit;
1106 end loop;
1107 --
1108 else
1109 --
1110 for procd_dt_rec in c_procd_dt_pil loop
1111 l_procd_dt := procd_dt_rec.procd_dt;
1112 exit;
1113 end loop;
1114 --
1115 end if;
1116 -- Bug 2200139 for Override Case look at the pen esd also
1117 hr_utility.set_location('Effective Date'||l_effective_date,111);
1118 hr_utility.set_location('l_typ_cd ='||l_typ_cd,111);
1119 if l_pen.enrt_mthd_cd = 'O' then
1120 l_effective_date := greatest(l_effective_date,
1121 greatest(l_procd_dt,l_pen.effective_start_date + 1));
1122 else
1123 l_effective_date := greatest (l_effective_date, l_procd_dt);
1124 end if;
1125 -- if benmngle is in selection mode, no need to compute the effective date
1126 if l_benmngle_parm_rec.mode_cd = 'S' or l_benmngle_parm_rec.mode_cd = 'U' or
1127 l_typ_cd = 'ABS' or l_typ_cd = 'GSP' or
1128 l_effective_date < l_pen.enrt_cvg_strt_dt then
1129 hr_utility.set_location('l_typ_cd ='||l_typ_cd,123);
1130 l_effective_date := p_effective_date;
1131 end if;
1132 -- Bug#2641545 - not to lose the per in ler of enrollment the result record should be
1133 -- datetrack updated with per in ler of ineligible life event
1134 if l_effective_date <= l_pen.effective_start_date and p_ler_id is not null then
1135 l_effective_date := l_pen.effective_start_date + 1;
1136 --bug#3279350
1137 elsif l_effective_date < l_pen.effective_start_date then
1138 l_effective_date := l_pen.effective_start_date;
1139 end if;
1140
1141 hr_utility.set_location('Effective Date'||l_effective_date,111);
1142 --
1143 ben_provider_pools.recompute_flex_credits(
1144 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1145 p_pgm_id => l_pen.pgm_id,
1146 p_per_in_ler_id => l_per_in_ler_id,
1147 p_person_id => p_person_id,
1148 p_enrt_mthd_cd => 'E',
1149 p_business_group_id => p_business_group_id,
1150 p_effective_date => l_effective_date);
1151 --
1152 hr_utility.set_location(' BKKKK DELETING THE FUTURE CVG OIPL ',10);
1153
1154 --
1155 /*
1156 if (l_per_in_ler_id IS NOT NULL) then
1157
1158 ben_election_information.backout_future_coverage
1159 (p_per_in_ler_id => l_per_in_ler_id,
1160 p_business_group_id => p_business_group_id,
1161 p_person_id => p_person_id,
1162 p_pgm_id => NVL(l_pen.pgm_id,p_pgm_id),
1163 p_pl_id => p_pl_id,
1164 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1165 p_effective_date => p_effective_date,
1166 p_prtt_enrt_rslt_id => l_dummy_num ) ;
1167 end if;
1168 */
1169 --
1170
1171 ben_prtt_enrt_result_api.delete_enrollment
1172 (p_validate => false ,
1173 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1174 p_per_in_ler_id => l_per_in_ler_id,
1175 p_lee_rsn_id => l_lee_rsn_id,
1176 -- PB : 5422
1177 p_enrt_perd_id => l_enrt_perd_id, -- l_benmngle_parm_rec.popl_enrt_typ_cycl_id,
1178 p_business_group_id => p_business_group_id ,
1179 p_effective_start_date => l_eff_strt,
1180 p_effective_end_date => l_eff_end,
1181 p_object_version_number => l_pen.object_version_number,
1182 p_effective_date => l_effective_date,
1183 p_datetrack_mode => 'DELETE',
1184 p_multi_row_validate => false,
1185 p_source => 'beninelg');
1186 --
1187 ben_prtt_enrt_result_api.g_enrollment_change := TRUE;
1188 --
1189 -- end if;
1190 --
1191 end loop;
1192 --
1193 elsif p_pl_id is not null then
1194 for l_pen in c_pen_pl loop
1195 --
1196 -- PB : 5422
1197 l_enrt_perd_id := null; --bug 4645272
1198 -- bug : 4645272 : when Open/Administartive runs in Life event mode, populate enrt_perd_id
1199 if l_param_lf_evt_ocrd_dt is null and l_typ_cd not in ('SCHEDDO','SCHEDDA') then
1200 -- if l_benmngle_parm_rec.popl_enrt_typ_cycl_id is null then
1201 l_lee_rsn_id:=null;
1202 open c_lee_rsn_pl(l_pen.pl_id);
1203 fetch c_lee_rsn_pl into l_lee_rsn_id,l_enrt_perd_strt_dt_cd,l_enrt_perd_strt_dt_rl,l_enrt_perd_strt_days;
1204 close c_lee_rsn_pl;
1205 if l_lee_rsn_id is null then
1206 open c_lee_rsn_pgm(l_pen.pgm_id);
1207 fetch c_lee_rsn_pgm into l_lee_rsn_id,l_enrt_perd_strt_dt_cd,l_enrt_perd_strt_dt_rl,l_enrt_perd_strt_days;
1208 close c_lee_rsn_pgm;
1209 end if;
1210 if l_enrt_perd_strt_dt_cd is not null then
1211 ben_determine_date.main(
1212 p_date_cd => l_enrt_perd_strt_dt_cd,
1213 p_person_id => p_person_id,
1214 p_per_in_ler_id => l_per_in_ler_id,
1215 p_pgm_id => p_pgm_id,
1216 p_pl_id => p_pl_id,
1217 p_oipl_id => p_oipl_id,
1218 p_business_group_id => p_business_group_id,
1219 p_formula_id => l_enrt_perd_strt_dt_rl,
1220 p_effective_date => p_effective_date,
1221 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1222 p_returned_date => l_enrt_perd_strt_dt);
1223
1224 if l_enrt_perd_strt_dt_cd in ( 'NUMDOE', 'NUMDON','NUMDOEN') then
1225 l_enrt_perd_strt_dt := l_enrt_perd_strt_dt + nvl(l_enrt_perd_strt_days,0) ;
1226 end if ;
1227
1228 end if;
1229 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_PERD_STRT_DT,p_effective_date)); -- 5257226 - 1;
1230
1231 else
1232 --
1233 -- PB : 5422 :
1234 --
1235 OPEN c_sched_enrol_period_for_plan(l_pen.pl_id,
1236 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
1237 FETCH c_sched_enrol_period_for_plan INTO l_enrt_perd_id,l_enrt_strt_dt;
1238 --
1239 IF c_sched_enrol_period_for_plan%NOTFOUND THEN
1240 --
1241 OPEN c_sched_enrol_period_for_pgm(l_pen.pgm_id,
1242 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
1243 FETCH c_sched_enrol_period_for_pgm INTO l_enrt_perd_id,l_enrt_strt_dt;
1244 CLOSE c_sched_enrol_period_for_pgm;
1245 --
1246 END IF;
1247 CLOSE c_sched_enrol_period_for_plan;
1248 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_STRT_DT,p_effective_date)); -- 5257226 - 1;
1249 --
1250 end if;
1251 --
1252 open c_imptd(l_pen.pl_id);
1253 fetch c_imptd into l_imptd_incm_flag;
1254 if c_imptd%found then
1255 --
1256 l_imptd_incm_chg := true;
1257 --
1258 end if;
1259 close c_imptd;
1260 --
1261 /*
1262 ben_provider_pools.recompute_flex_credits(
1263 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1264 p_pgm_id => l_pen.pgm_id,
1265 p_per_in_ler_id => l_per_in_ler_id,
1266 p_person_id => p_person_id,
1267 p_enrt_mthd_cd => 'E',
1268 p_business_group_id => p_business_group_id,
1269 p_effective_date => p_effective_date);
1270 --
1271 ben_determine_date.rate_and_coverage_dates
1272 (p_which_dates_cd => 'C',
1273 p_date_mandatory_flag => 'N',
1274 p_compute_dates_flag => 'N',
1275 p_per_in_ler_id => l_per_in_ler_id,
1276 p_person_id => p_person_id,
1277 p_pgm_id => l_pen.pgm_id,
1278 p_pl_id => l_pen.pl_id,
1279 p_oipl_id => l_pen.oipl_id,
1280 p_business_group_id => p_business_group_id,
1281 p_enrt_cvg_strt_dt => l_dummy_dt,
1282 p_enrt_cvg_strt_dt_cd => l_dummy_varchar,
1283 p_enrt_cvg_strt_dt_rl => l_dummy_num,
1284 p_rt_strt_dt => l_dummy_dt,
1285 p_rt_strt_dt_cd => l_dummy_varchar,
1286 p_rt_strt_dt_rl => l_dummy_num,
1287 p_enrt_cvg_end_dt => l_dummy_dt,
1288 p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd,
1289 p_enrt_cvg_end_dt_rl => l_dummy_num,
1290 p_rt_end_dt => l_dummy_dt,
1291 p_rt_end_dt_cd => l_dummy_varchar,
1292 p_rt_end_dt_rl => l_dummy_num,
1293 p_effective_date => p_effective_date,
1294 p_lf_evt_ocrd_dt => null,
1295 p_lee_rsn_id => l_lee_rsn_id
1296 );
1297 --
1298 hr_utility.set_location('Beninelg: cvg_end_dt_cd='||l_enrt_cvg_end_dt_cd,1067);
1299 if substr(nvl(l_enrt_cvg_end_dt_cd, '-1'), 1, 1) = 'W' then
1300 --
1301 l_ovn := l_pen.object_version_number;
1302 --
1303 hr_utility.set_location('Before updating enrt result ',10);
1304 hr_utility.set_location('p_effective_date '||p_effective_date,10);
1305 --
1306 dt_api.find_dt_upd_modes
1307 (p_effective_date => p_effective_date,
1308 p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
1309 p_base_key_column => 'prtt_enrt_rslt_id',
1310 p_base_key_value => l_pen.prtt_enrt_rslt_id,
1311 p_correction => l_correction,
1312 p_update => l_update,
1313 p_update_override => l_update_override,
1314 p_update_change_insert => l_update_change_insert);
1315 --
1316 if l_update_override then
1317 --
1318 l_datetrack_mode := hr_api.g_update_override;
1319 --
1320 elsif l_update then
1321 --
1322 l_datetrack_mode := hr_api.g_update;
1323 --
1324 else
1325 --
1326 l_datetrack_mode := hr_api.g_correction;
1327 --
1328 end if;
1329 --
1330 ben_prtt_enrt_result_api.update_PRTT_ENRT_RESULT
1331 (p_validate => false
1332 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1333 ,p_effective_start_date => l_eff_strt
1334 ,p_effective_end_date => l_eff_end
1335 ,p_ler_id => p_ler_id
1336 ,p_no_lngr_elig_flag => 'Y'
1337 ,p_object_version_number => l_ovn
1338 ,p_per_in_ler_id => l_per_in_ler_id
1339 ,p_effective_date => p_effective_date
1340 ,p_datetrack_mode => l_datetrack_mode
1341 ,p_multi_row_validate => false
1342 );
1343 --
1344 else
1345 */
1346 --
1347 --
1348 -- 22-Jun-01 bwharton 115.31
1349 l_procd_dt := l_effective_date;
1350 --
1351 -- CWB changes : First determine the typ_cd of current per in ler
1352 -- and look for only COMP typ per in ler's if current per in ler is
1353 -- Comp type.
1354 --
1355 if l_typ_cd = 'COMP' then
1356 --
1357 for procd_dt_rec in c_procd_dt_pil_cwb loop
1358 l_procd_dt := procd_dt_rec.procd_dt;
1359 exit;
1360 end loop;
1361 --
1362 else
1363 --
1364 for procd_dt_rec in c_procd_dt_pil loop
1365 l_procd_dt := procd_dt_rec.procd_dt;
1366 exit;
1367 end loop;
1368 --
1369 end if;
1370 --
1371 -- Bug 2200139 for Override Case look at the pen esd also
1372 if l_pen.enrt_mthd_cd = 'O' then
1373 l_effective_date := greatest(l_effective_date,
1374 greatest(l_procd_dt,l_pen.effective_start_date + 1));
1375 else
1376 l_effective_date := greatest (l_effective_date, l_procd_dt);
1377 end if;
1378 -- if benmngle is in selection mode, no need to compute the effective date
1379 -- for absence life event also the effective date is not computed
1380 if l_benmngle_parm_rec.mode_cd = 'S' or l_benmngle_parm_rec.mode_cd = 'U' or
1381 l_typ_cd = 'ABS' or l_typ_cd = 'GSP' or
1382 l_effective_date < l_pen.enrt_cvg_strt_dt then
1383 l_effective_date := p_effective_date;
1384 end if;
1385 --
1386 --bug#3279350
1387 if l_effective_date <= l_pen.effective_start_date and p_ler_id is not null then
1388 l_effective_date := l_pen.effective_start_date + 1;
1389 elsif l_effective_date < l_pen.effective_start_date then
1390 l_effective_date := l_pen.effective_start_date;
1391 end if;
1392 --
1393 ben_provider_pools.recompute_flex_credits(
1394 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1395 p_pgm_id => l_pen.pgm_id,
1396 p_per_in_ler_id => l_per_in_ler_id,
1397 p_person_id => p_person_id,
1398 p_enrt_mthd_cd => 'E',
1399 p_business_group_id => p_business_group_id,
1400 p_effective_date => l_effective_date);
1401 --
1402 hr_utility.set_location('Effective Date'||l_effective_date,112);
1403 hr_utility.set_location(' BKKKK DELETING THE FUTURE CVG PLN ',10);
1404 --
1405 /*
1406 if (l_per_in_ler_id IS NOT NULL) then
1407 ben_election_information.backout_future_coverage
1408 (p_per_in_ler_id => l_per_in_ler_id,
1409 p_business_group_id => p_business_group_id,
1410 p_person_id => p_person_id,
1411 p_pgm_id => NVL(l_pen.pgm_id,p_pgm_id),
1412 p_pl_id => p_pl_id,
1413 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1414 p_effective_date => p_effective_date ,
1415 p_prtt_enrt_rslt_id => l_dummy_num ) ;
1416 end if;
1417 */
1418 --
1419
1420 ben_prtt_enrt_result_api.delete_enrollment
1421 (p_validate => false ,
1422 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1423 p_per_in_ler_id => l_per_in_ler_id,
1424 p_lee_rsn_id => l_lee_rsn_id,
1425 -- PB : 5422
1426 p_enrt_perd_id => l_enrt_perd_id, -- l_benmngle_parm_rec.popl_enrt_typ_cycl_id ,
1427 p_business_group_id => p_business_group_id ,
1428 p_effective_start_date => l_eff_strt,
1429 p_effective_end_date => l_eff_end,
1430 p_object_version_number => l_pen.object_version_number,
1431 p_effective_date => l_effective_date,
1432 p_datetrack_mode => 'DELETE',
1433 p_multi_row_validate => false,
1434 p_source => 'beninelg');
1435 --
1436 ben_prtt_enrt_result_api.g_enrollment_change := TRUE;
1437 --
1438 -- Check if we should terminate COBRA eligibility.
1439 --
1440 hr_utility.set_location('l_pen.pgm_id: '||l_pen.pgm_id,10);
1441 hr_utility.set_location('l_pen.ptip_id: '||l_pen.ptip_id,10);
1442 --
1443 if l_pen.pgm_id is not null
1444 then
1445 ben_comp_object.get_object(p_pgm_id => l_pen.pgm_id,
1446 p_rec => l_pgm_rec);
1447 hr_utility.set_location('pgm_typ: '||l_pgm_rec.pgm_typ_cd,10);
1448 if l_pgm_rec.pgm_typ_cd like 'COBRA%' then
1449 hr_utility.set_location('pgm_typ: '||l_pgm_rec.pgm_typ_cd,10);
1450 ben_cobra_requirements.g_cobra_enrollment_change := TRUE;
1451 end if;
1452 end if;
1453 --
1454 -- end if;
1455 --
1456 end loop;
1457 --
1458 elsif p_pgm_id is not null then
1459 for l_pen in c_pen_pgm loop
1460 --
1461 l_enrt_perd_id:= null; ---- bug : 4645272
1462 -- PB : 5422
1463 -- bug : 4645272 : when Open/Administartive runs in Life event mode, populate enrt_perd_id
1464
1465 if l_param_lf_evt_ocrd_dt is null and l_typ_cd not in ('SCHEDDO','SCHEDDA') then
1466
1467 -- if l_benmngle_parm_rec.popl_enrt_typ_cycl_id is null then
1468 l_lee_rsn_id:=null;
1469
1470 open c_lee_rsn_pl(l_pen.pl_id);
1471 fetch c_lee_rsn_pl into l_lee_rsn_id,l_enrt_perd_strt_dt_cd,l_enrt_perd_strt_dt_rl,l_enrt_perd_strt_days;
1472 close c_lee_rsn_pl;
1473 if l_lee_rsn_id is null then
1474 open c_lee_rsn_pgm(l_pen.pgm_id);
1475 fetch c_lee_rsn_pgm into l_lee_rsn_id,l_enrt_perd_strt_dt_cd,l_enrt_perd_strt_dt_rl,l_enrt_perd_strt_days;
1476 close c_lee_rsn_pgm;
1477 end if;
1478 if l_enrt_perd_strt_dt_cd is not null then
1479 ben_determine_date.main(
1480 p_date_cd => l_enrt_perd_strt_dt_cd,
1481 p_person_id => p_person_id,
1482 p_per_in_ler_id => l_per_in_ler_id,
1483 p_pgm_id => p_pgm_id,
1484 p_pl_id => p_pl_id,
1485 p_oipl_id => p_oipl_id,
1486 p_business_group_id => p_business_group_id,
1487 p_formula_id => l_enrt_perd_strt_dt_rl,
1488 p_effective_date => p_effective_date,
1489 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1490 p_returned_date => l_enrt_perd_strt_dt);
1491
1492 if l_enrt_perd_strt_dt_cd in ( 'NUMDOE', 'NUMDON','NUMDOEN') then
1493 l_enrt_perd_strt_dt := l_enrt_perd_strt_dt + nvl(l_enrt_perd_strt_days,0) ;
1494 end if ;
1495 end if;
1496 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_PERD_STRT_DT,p_effective_date)); -- 5257226 - 1;
1497
1498 else
1499 --
1500 -- PB : 5422 :
1501 --
1502 OPEN c_sched_enrol_period_for_pgm(l_pen.pgm_id,
1503 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
1504
1505 FETCH c_sched_enrol_period_for_pgm INTO l_enrt_perd_id,l_enrt_strt_dt;
1506 CLOSE c_sched_enrol_period_for_pgm;
1507 --
1508 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_STRT_DT,p_effective_date)); --5257226 - 1;
1509 end if;
1510 --
1511 open c_imptd(l_pen.pl_id);
1512 fetch c_imptd into l_imptd_incm_flag;
1513 if c_imptd%found then
1514 --
1515 l_imptd_incm_chg := true;
1516 l_inelig_lvl_in_pgm := 'Y' ;
1517 --
1518 end if;
1519 close c_imptd;
1520 --
1521 -- MOVED to pass this date to ben_provider_pools.recompute_flex_credits aslo
1522 -- 22-Jun-01 bwharton 115.31
1523 --
1524 l_procd_dt := l_effective_date;
1525 --
1526 -- CWB changes : First determine the typ_cd of current per in ler
1527 -- and look for only COMP typ per in ler's if current per in ler is
1528 -- Comp type.
1529 --
1530 if l_typ_cd = 'COMP' then
1531 --
1532 for procd_dt_rec in c_procd_dt_pil_cwb loop
1533 l_procd_dt := procd_dt_rec.procd_dt;
1534 exit;
1535 end loop;
1536 --
1537 else
1538 --
1539 for procd_dt_rec in c_procd_dt_pil loop
1540 l_procd_dt := procd_dt_rec.procd_dt;
1541 exit;
1542 end loop;
1543 --
1544 end if;
1545 -- Bug 2200139 for Override Case look at the pen esd also
1546 if l_pen.enrt_mthd_cd = 'O' then
1547 l_effective_date := greatest(l_effective_date,
1548 greatest(l_procd_dt,l_pen.effective_start_date + 1));
1549 else
1550 l_effective_date := greatest (l_effective_date, l_procd_dt);
1551 end if;
1552
1553 -- if benmngle is in selection mode, no need to compute the effective date
1554 if l_benmngle_parm_rec.mode_cd = 'S' or l_benmngle_parm_rec.mode_cd = 'U' or
1555 l_typ_cd = 'ABS' or l_typ_cd = 'GSP' or
1556 l_effective_date < l_pen.enrt_cvg_strt_dt then
1557 l_effective_date := p_effective_date;
1558 end if;
1559 --
1560 --bug#3279350
1561 if l_effective_date <= l_pen.effective_start_date and p_ler_id is not null then
1562 l_effective_date := l_pen.effective_start_date + 1;
1563 elsif l_effective_date < l_pen.effective_start_date then
1564 l_effective_date := l_pen.effective_start_date;
1565 end if;
1566 --
1567 ben_provider_pools.recompute_flex_credits(
1568 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1569 p_pgm_id => l_pen.pgm_id,
1570 p_per_in_ler_id => l_per_in_ler_id,
1571 p_person_id => p_person_id,
1572 p_enrt_mthd_cd => 'E',
1573 p_business_group_id => p_business_group_id,
1574 p_effective_date => l_effective_date ); -- p_effective_date);
1575 --
1576 /*
1577 ben_determine_date.rate_and_coverage_dates
1578 (p_which_dates_cd => 'C',
1579 p_date_mandatory_flag => 'N',
1580 p_compute_dates_flag => 'N',
1581 p_per_in_ler_id => l_per_in_ler_id,
1582 p_person_id => p_person_id,
1583 p_pgm_id => l_pen.pgm_id,
1584 p_pl_id => l_pen.pl_id,
1585 p_oipl_id => l_pen.oipl_id,
1586 p_business_group_id => p_business_group_id,
1587 p_enrt_cvg_strt_dt => l_dummy_dt,
1588 p_enrt_cvg_strt_dt_cd => l_dummy_varchar,
1589 p_enrt_cvg_strt_dt_rl => l_dummy_num,
1590 p_rt_strt_dt => l_dummy_dt,
1591 p_rt_strt_dt_cd => l_dummy_varchar,
1592 p_rt_strt_dt_rl => l_dummy_num,
1593 p_enrt_cvg_end_dt => l_dummy_dt,
1594 p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd,
1595 p_enrt_cvg_end_dt_rl => l_dummy_num,
1596 p_rt_end_dt => l_dummy_dt,
1597 p_rt_end_dt_cd => l_dummy_varchar,
1598 p_rt_end_dt_rl => l_dummy_num,
1599 p_effective_date => p_effective_date,
1600 p_lf_evt_ocrd_dt => null,
1601 p_lee_rsn_id => l_lee_rsn_id
1602 );
1603 --
1604 hr_utility.set_location('Before updating enrt result ',10);
1605 hr_utility.set_location('p_effective_date '||p_effective_date,10);
1606 --
1607 if substr(nvl(l_enrt_cvg_end_dt_cd, '-1'), 1, 1) = 'W' then
1608 --
1609 l_ovn := l_pen.object_version_number;
1610 --
1611 dt_api.find_dt_upd_modes
1612 (p_effective_date => p_effective_date,
1613 p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
1614 p_base_key_column => 'prtt_enrt_rslt_id',
1615 p_base_key_value => l_pen.prtt_enrt_rslt_id,
1616 p_correction => l_correction,
1617 p_update => l_update,
1618 p_update_override => l_update_override,
1619 p_update_change_insert => l_update_change_insert);
1620 --
1621 if l_update_override then
1622 --
1623 l_datetrack_mode := hr_api.g_update_override;
1624 --
1625 elsif l_update then
1626 --
1627 l_datetrack_mode := hr_api.g_update;
1628 --
1629 else
1630 --
1631 l_datetrack_mode := hr_api.g_correction;
1632 --
1633 end if;
1634 --
1635 hr_utility.set_location('pen_id='||l_pen.prtt_enrt_rslt_id,1963);
1636 ben_prtt_enrt_result_api.update_PRTT_ENRT_RESULT
1637 (p_validate => false
1638 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1639 ,p_effective_start_date => l_eff_strt
1640 ,p_effective_end_date => l_eff_end
1641 ,p_ler_id => p_ler_id
1642 ,p_no_lngr_elig_flag => 'Y'
1643 ,p_object_version_number => l_ovn
1644 ,p_per_in_ler_id => l_per_in_ler_id
1645 ,p_effective_date => p_effective_date
1646 ,p_datetrack_mode => l_datetrack_mode
1647 ,p_multi_row_validate => false
1648 );
1649 --
1650 else
1651 --
1652 */
1653 --
1654 /* Moved to up use this for ben_provider_pools.recompute_flex_credits also
1655 -- 22-Jun-01 bwharton 115.31
1656 l_procd_dt := l_effective_date;
1657 --
1658 -- CWB changes : First determine the typ_cd of current per in ler
1659 -- and look for only COMP typ per in ler's if current per in ler is
1660 -- Comp type.
1661 --
1662 if l_typ_cd = 'COMP' then
1663 --
1664 for procd_dt_rec in c_procd_dt_pil_cwb loop
1665 l_procd_dt := procd_dt_rec.procd_dt;
1666 exit;
1667 end loop;
1668 --
1669 else
1670 --
1671 for procd_dt_rec in c_procd_dt_pil loop
1672 l_procd_dt := procd_dt_rec.procd_dt;
1673 exit;
1674 end loop;
1675 --
1676 end if;
1677 --
1678 -- Bug 2200139 for Override Case look at the pen esd also
1679 if l_pen.enrt_mthd_cd = 'O' then
1680 l_effective_date := greatest(l_effective_date,
1681 greatest(l_procd_dt,l_pen.effective_start_date + 1 ));
1682 else
1683 l_effective_date := greatest (l_effective_date, l_procd_dt);
1684 end if;
1685
1686 -- if benmngle is in selection mode, no need to compute the effective date
1687 if l_benmngle_parm_rec.mode_cd = 'S' or l_benmngle_parm_rec.mode_cd = 'U' or
1688 l_typ_cd = 'ABS' or l_typ_cd = 'GSP' or
1689 l_effective_date < l_pen.enrt_cvg_strt_dt then
1690 l_effective_date := p_effective_date;
1691 end if;
1692 */
1693
1694
1695 hr_utility.set_location('Effective Date'||l_effective_date,113);
1696 --
1697 hr_utility.set_location(' BKKKK DELETING THE FUTURE CVG PGM ',10);
1698 --
1699 /*
1700 if (l_per_in_ler_id IS NOT NULL) then
1701 ben_election_information.backout_future_coverage
1702 (p_per_in_ler_id => l_per_in_ler_id,
1703 p_business_group_id => p_business_group_id,
1704 p_person_id => p_person_id,
1705 p_pgm_id => p_pgm_id,
1706 p_pl_id => p_pl_id,
1707 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1708 p_effective_date => p_effective_date ,
1709 p_prtt_enrt_rslt_id => l_dummy_num ) ;
1710 end if;
1711 */
1712 --
1713
1714 ben_prtt_enrt_result_api.delete_enrollment
1715 (p_validate => false ,
1716 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1717 p_per_in_ler_id => l_per_in_ler_id,
1718 p_lee_rsn_id => l_lee_rsn_id,
1719 -- PB : 5422
1720 p_enrt_perd_id => l_enrt_perd_id, -- l_benmngle_parm_rec.popl_enrt_typ_cycl_id
1721 p_business_group_id => p_business_group_id ,
1722 p_effective_start_date => l_eff_strt,
1723 p_effective_end_date => l_eff_end,
1724 p_object_version_number => l_pen.object_version_number,
1725 p_effective_date => l_effective_date,
1726 p_datetrack_mode => 'DELETE',
1727 p_multi_row_validate => FALSE,
1728 p_source => 'beninelg');
1729 --
1730 ben_prtt_enrt_result_api.g_enrollment_change := TRUE;
1731 --
1732 --
1733 -- Check if we should terminate COBRA eligibility.
1734 --
1735 hr_utility.set_location('p_pgm_id: '||p_pgm_id,10);
1736 hr_utility.set_location('l_pen.ptip_id: '||l_pen.ptip_id,10);
1737 --
1738 ben_comp_object.get_object(p_pgm_id => p_pgm_id,
1739 p_rec => l_pgm_rec);
1740 hr_utility.set_location('pgm_typ: '||l_pgm_rec.pgm_typ_cd,10);
1741 if l_pgm_rec.pgm_typ_cd like 'COBRA%' then
1742 hr_utility.set_location('pgm_typ: '||l_pgm_rec.pgm_typ_cd,20);
1743 ben_cobra_requirements.g_cobra_enrollment_change := TRUE;
1744 end if;
1745 --
1746 -- end if;
1747 --
1748 end loop;
1749 --
1750 end if;
1751 --
1752 if l_imptd_incm_chg then
1753 --
1754 if l_inelig_lvl_in_pgm <> 'Y' then
1755 ben_det_imputed_income.p_comp_imputed_income(
1756 p_person_id => p_person_id,
1757 p_enrt_mthd_cd => 'E',
1758 p_business_group_id => p_business_group_id,
1759 p_per_in_ler_id => l_per_in_ler_id,
1760 p_effective_date => p_effective_date,
1761 p_ctrlm_fido_call => false,
1762 p_validate => false,
1763 p_no_choice_flag => true);
1764 end if ;
1765 --
1766 end if;
1767 --
1768 hr_utility.set_location ('Leaving '||l_proc,10);
1769 --
1770 end main;
1771 --
1772 end ben_newly_ineligible;