[Home] [Help]
PACKAGE BODY: APPS.BEN_NEWLY_INELIGIBLE
Source
1 PACKAGE BODY ben_newly_ineligible as
2 /* $Header: beninelg.pkb 120.6.12010000.7 2009/11/02 11:27:43 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 02-Nov-09 sallumwa 115.71 Bug 9030738 : Modified the procedure main so that the election is
185 de-enrolled based on the enrollment period start date.
186 */
187 -----------------------------------------------------------------------
188 procedure defer_delete_enrollment
189 ( p_per_in_ler_id in number
190 ,p_person_id in number
191 ,p_business_group_id in number
192 ,p_effective_date in date
193 ) is
194 --
195 l_proc varchar2(80) := g_package|| '.defer_delete_enrollment';
196 --
197 cursor c_pel_pgm_id is
198 --
199 select pel.*
200 from ben_pil_elctbl_chc_popl pel
201 where pel.per_in_ler_id = p_per_in_ler_id
202 and pel.defer_deenrol_flag = 'Y'
203 and pel.deenrol_made_dt is null
204 and pel.pil_elctbl_popl_stat_cd not in ('VOIDD','BCKDT');
205 --
206 l_pel_pgm_id c_pel_pgm_id%ROWTYPE;
207 --
208 cursor c_pep_pgm_defer(p_pgm_id number) is
209 --
210 select pen.*
211 from ben_prtt_enrt_rslt_f pen
212 ,ben_elig_per_f pep
213 where pen.pgm_id = p_pgm_id
214 ---Bug 7342283
215 and pen.pgm_id = pep.pgm_id
216 ----Bug 7342283
217 and pen.pl_id = pep.pl_id
218 and pep.elig_flag = 'N'
219 and pep.per_in_ler_id = p_per_in_ler_id
220 and pen.prtt_enrt_rslt_stat_cd is null
221 and pen.effective_end_date = hr_api.g_eot
222 and pen.enrt_cvg_thru_dt = hr_api.g_eot
223 and pen.oipl_id is null
224 and pen.person_id = p_person_id;
225 --
226 l_pep_pgm_defer c_pep_pgm_defer%ROWTYPE;
227 --
228 cursor c_pep_pl_defer(p_pl_id number) is
229 --
230 select pen.*
231 from ben_prtt_enrt_rslt_f pen
232 ,ben_elig_per_f pep
233 where pen.pl_id = p_pl_id
234 and pep.pl_id = pen.pl_id
235 and pep.elig_flag = 'N'
236 and pen.pgm_id is null
237 and pen.oipl_id is null
238 and pep.per_in_ler_id = p_per_in_ler_id
239 and pen.prtt_enrt_rslt_stat_cd is null
240 and pen.effective_end_date = hr_api.g_eot
241 and pen.enrt_cvg_thru_dt = hr_api.g_eot
242 and pen.person_id = p_person_id;
243 --
244 l_pep_pl_defer c_pep_pl_defer%ROWTYPE;
245 --
246 cursor c_epo_plip_defer(p_pgm_id number) is
247 --
248 select pen.*
249 from ben_elig_per_opt_f epo
250 ,ben_prtt_enrt_rslt_f pen
251 ,ben_oipl_f oipl
252 ,ben_elig_per_f pep
253 where pen.pgm_id = p_pgm_id
254 ---Bug 7301670
255 and pen.pgm_id = pep.pgm_id
256 ----Bug 7301670
257 and pep.pl_id = pen.pl_id
258 and pen.oipl_id = oipl.oipl_id
259 and oipl.opt_id = epo.opt_id
260 and epo.elig_per_id = pep.elig_per_id
261 and pep.per_in_ler_id = p_per_in_ler_id
262 and epo.elig_flag = 'N'
263 --Bug : 6881745
264 /* AND p_effective_date
265 BETWEEN epo.effective_start_date AND epo.effective_end_date */
266 --Bug : 7181958
267 and epo.effective_end_date = hr_api.g_eot
268 --Bug : 6881745
269 and pen.effective_end_date = hr_api.g_eot
270 and pen.enrt_cvg_thru_dt = hr_api.g_eot
271 and pen.prtt_enrt_rslt_stat_cd is null
272 and pen.person_id = p_person_id;
273 --
274 l_epo_plip_defer c_epo_plip_defer%ROWTYPE;
275 --
276 cursor c_epo_plnip_defer(p_pl_id number) is
277 --
278 select pen.*
279 from ben_elig_per_opt_f epo
280 ,ben_prtt_enrt_rslt_f pen
281 ,ben_oipl_f oipl
282 ,ben_elig_per_f pep
283 where pen.pl_id = p_pl_id
284 and pep.pl_id = pen.pl_id
285 and pen.oipl_id = oipl.oipl_id
286 and pep.pgm_id is null
287 and epo.elig_per_id = pep.elig_per_id
288 and pep.per_in_ler_id = p_per_in_ler_id
289 and oipl.opt_id = epo.opt_id
290 and epo.elig_flag = 'N'
291 and pen.effective_end_date = hr_api.g_eot
292 and pen.enrt_cvg_thru_dt = hr_api.g_eot
293 and pen.prtt_enrt_rslt_stat_cd is null
294 and pen.person_id = p_person_id;
295 --
296 l_epo_plnip_defer c_epo_plnip_defer%ROWTYPE;
297 --
298 l_effective_start_date date;
299 l_effective_end_date date;
300 l_effective_date date;
301 --
302 begin
303 --
304 hr_utility.set_location ('Entering '|| l_proc|| p_effective_date,10);
305 --
306 l_effective_date := p_effective_date;
307 --
308 open c_pel_pgm_id;
309 loop
310 fetch c_pel_pgm_id into l_pel_pgm_id;
311 exit when c_pel_pgm_id%NOTFOUND;
312 --
313 if l_pel_pgm_id.pgm_id is not null then
314 --
315 hr_utility.set_location('PGM'|| l_pel_pgm_id.pgm_id,24);
316 --
317 open c_pep_pgm_defer(l_pel_pgm_id.pgm_id);
318 loop
319 fetch c_pep_pgm_defer into l_pep_pgm_defer;
320 exit when c_pep_pgm_defer%NOTFOUND;
321 --
322 if c_pep_pgm_defer%found then
323 --
324 hr_utility.set_location ('PEN'|| l_pep_pgm_defer.prtt_enrt_rslt_id,46000);
325 --
326 if l_effective_date <= l_pep_pgm_defer.effective_start_date then
327 l_effective_date := l_pep_pgm_defer.effective_start_date + 1;
328 --
329 end if;
330 --
331 ben_PRTT_ENRT_RESULT_api.delete_enrollment(
332 p_validate => false,
333 p_per_in_ler_id => p_per_in_ler_id,
334 p_prtt_enrt_rslt_id => l_pep_pgm_defer.prtt_enrt_rslt_id,
335 p_effective_start_date => l_effective_start_date,
336 p_effective_end_date => l_effective_end_date,
337 p_object_version_number => l_pep_pgm_defer.object_version_number,
338 p_business_group_id => p_business_group_id,
339 p_effective_date => l_effective_date,
340 p_datetrack_mode => 'DELETE',
341 p_source => 'beninelg',
342 p_multi_row_validate => FALSE);
343 --
344 end if;
345 --
346 end loop;
347 --
348 close c_pep_pgm_defer;
349 --
350 open c_epo_plip_defer(l_pel_pgm_id.pgm_id);
351 loop
352 fetch c_epo_plip_defer into l_epo_plip_defer;
353 exit when c_epo_plip_defer%NOTFOUND;
354 --
355 hr_utility.set_location ('EPO PEN '|| l_epo_plip_defer.prtt_enrt_rslt_id||'DATE'||l_effective_date,10007);
356 --
357 if c_epo_plip_defer%found then
358 --
359 if l_effective_date <= l_epo_plip_defer.effective_start_date then
360 l_effective_date := l_epo_plip_defer.effective_start_date + 1;
361 --
362 end if;
363 --
364 ben_PRTT_ENRT_RESULT_api.delete_enrollment(
365 p_validate => FALSE,
366 p_per_in_ler_id => p_per_in_ler_id,
367 p_prtt_enrt_rslt_id => l_epo_plip_defer.prtt_enrt_rslt_id,
368 p_effective_start_date => l_effective_start_date,
369 p_effective_end_date => l_effective_end_date,
370 p_object_version_number => l_epo_plip_defer.object_version_number,
371 p_business_group_id => p_business_group_id,
372 p_effective_date => l_effective_date,
373 p_datetrack_mode => 'DELETE',
374 p_source => 'beninelg',
375 p_multi_row_validate => FALSE);
376 --
377 end if;
378 --
379 end loop;
380 --
381 close c_epo_plip_defer;
382 --
383 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
384 (p_validate => FALSE
385 ,p_pil_elctbl_chc_popl_id => l_pel_pgm_id.pil_elctbl_chc_popl_id
386 ,p_object_version_number => l_pel_pgm_id.object_version_number
387 ,p_effective_date => p_effective_date
388 ,p_defer_deenrol_flag => 'Y'
389 ,p_deenrol_made_dt => p_effective_date
390 );
391 --
392 elsif l_pel_pgm_id.pl_id is not null then
393 --
394 open c_pep_pl_defer(l_pel_pgm_id.pl_id);
395 loop
396 fetch c_pep_pl_defer into l_pep_pl_defer;
397 exit when c_pep_pl_defer%NOTFOUND;
398 --
399 if c_pep_pl_defer%found then
400 --
401 hr_utility.set_location ('PEN'|| l_pep_pl_defer.prtt_enrt_rslt_id,46000);
402 --
403 if l_effective_date <= l_pep_pl_defer.effective_start_date then
404 l_effective_date := l_pep_pl_defer.effective_start_date + 1;
405 --
406 end if;
407 --
408 ben_PRTT_ENRT_RESULT_api.delete_enrollment(
409 p_validate => false,
410 p_per_in_ler_id => p_per_in_ler_id,
411 p_prtt_enrt_rslt_id => l_pep_pl_defer.prtt_enrt_rslt_id,
412 p_effective_start_date => l_effective_start_date,
413 p_effective_end_date => l_effective_end_date,
414 p_object_version_number => l_pep_pl_defer.object_version_number,
415 p_business_group_id => p_business_group_id,
416 p_effective_date => l_effective_date,
417 p_datetrack_mode => 'DELETE',
418 p_source => 'beninelg',
419 p_multi_row_validate => FALSE);
420 --
421 end if;
422 --
423 end loop;
424 --
425 close c_pep_pl_defer;
426 --
427 open c_epo_plnip_defer(l_pel_pgm_id.pl_id);
428 loop
429 fetch c_epo_plnip_defer into l_epo_plnip_defer;
430 exit when c_epo_plnip_defer%NOTFOUND;
431 --
432 hr_utility.set_location ('EPO PEN '|| l_epo_plip_defer.prtt_enrt_rslt_id,10007);
433 --
434 if l_effective_date <= l_epo_plnip_defer.effective_start_date then
435 l_effective_date := l_epo_plnip_defer.effective_start_date + 1;
436 --
437 end if;
438 --
439 ben_PRTT_ENRT_RESULT_api.delete_enrollment(
440 p_validate => FALSE,
441 p_per_in_ler_id => p_per_in_ler_id,
442 p_prtt_enrt_rslt_id => l_epo_plnip_defer.prtt_enrt_rslt_id,
443 p_effective_start_date => l_effective_start_date,
444 p_effective_end_date => l_effective_end_date,
445 p_object_version_number => l_epo_plnip_defer.object_version_number,
446 p_business_group_id => p_business_group_id,
447 p_effective_date => l_effective_date,
448 p_datetrack_mode => 'DELETE',
449 p_source => 'beninelg',
450 p_multi_row_validate => FALSE);
451 --
452 end loop;
453 --
454 close c_epo_plnip_defer;
455 --
456 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
457 (p_validate => FALSE
458 ,p_pil_elctbl_chc_popl_id => l_pel_pgm_id.pil_elctbl_chc_popl_id
459 ,p_object_version_number => l_pel_pgm_id.object_version_number
460 ,p_effective_date => p_effective_date
461 ,p_defer_deenrol_flag => 'Y'
462 ,p_deenrol_made_dt => p_effective_date
463 );
464
465 end if;
466 --
467 end loop;
468 --
469 close c_pel_pgm_id;
470 --
471 hr_utility.set_location ('Leaving'||l_proc,10);
472 --
473 end defer_delete_enrollment;
474 --
475 --
476 procedure main(p_person_id in number,
477 p_pgm_id in number default null,
478 p_pl_id in number default null,
479 p_oipl_id in number default null,
480 p_business_group_id in number,
481 p_ler_id in number,
482 p_effective_date in date) is
483 --
484 l_proc varchar2(80) := g_package|| '.main';
485 l_level varchar2(30);
486 l_eff_strt date;
487 l_eff_end date;
488 l_per_in_ler_id number := null;
489 l_lee_rsn_id number := null;
490 --
491 l_enrt_cvg_end_dt_cd varchar2(30);
492 l_ovn number;
493 l_dummy_dt date;
494 l_dummy_num number;
495 l_dummy_varchar varchar2(30);
496 l_oipl_rec ben_oipl_f%rowtype; -- TM added for backport of 115.6
497 l_pgm_rec ben_pgm_f%rowtype;
498
499 l_effective_date date;
500 l_effective_date_1 date := p_effective_date - 1;
501 -- Bug 2200139 added effective_start_date, enrt_mthd_cd for Override Case
502 cursor c_pen_pl is
503 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,ptip_id,
504 pen.effective_start_date,pen.enrt_mthd_cd,
505 pen.enrt_cvg_strt_dt
506 from ben_prtt_enrt_rslt_f pen
507 where p_pl_id = pen.pl_id
508 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
509 and pen.person_id = p_person_id
510 and pen.business_group_id+0 = p_business_group_id
511 and pen.prtt_enrt_rslt_stat_cd is null
512 and pen.effective_end_date = hr_api.g_eot
513 and pen.enrt_cvg_thru_dt = hr_api.g_eot -- 9999
514 and l_effective_date_1 <= pen.enrt_cvg_thru_dt
515 and pen.enrt_cvg_strt_dt < pen.effective_end_date
516 order by effective_start_date desc;
517 /*
518 and pen.enrt_cvg_thru_dt > p_effective_date
519 and p_effective_date
520 between pen.effective_start_date
521 and pen.effective_end_date;
522 */
523 -- -- Bug 2200139 added effective_start_date, enrt_mthd_cd for Override Case
524 cursor c_pen_pgm is
525 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,ptip_id,
526 pen.effective_start_date,pen.enrt_mthd_cd,
527 pen.enrt_cvg_strt_dt
528 from ben_prtt_enrt_rslt_f pen
529 where p_pgm_id = pen.pgm_id
530 and pen.person_id = p_person_id
531 and pen.business_group_id+0 = p_business_group_id
532 and pen.prtt_enrt_rslt_stat_cd is null
533 and pen.effective_end_date = hr_api.g_eot
534 and pen.enrt_cvg_thru_dt = hr_api.g_eot -- 9999
535 and l_effective_date_1 <= pen.enrt_cvg_thru_dt
536 and pen.enrt_cvg_strt_dt < pen.effective_end_date
537 order by effective_start_date desc;
538 /*
539 and pen.enrt_cvg_thru_dt > p_effective_date
540 and p_effective_date
541 between pen.effective_start_date
542 and pen.effective_end_date;
543 */
544 -- -- Bug 2200139 added effective_start_date, enrt_mthd_cd for Override Case
545 cursor c_pen_oipl is
546 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,
547 pen.effective_start_date,pen.enrt_mthd_cd,
548 pen.enrt_cvg_strt_dt
549 from ben_prtt_enrt_rslt_f pen
550 where p_oipl_id = pen.oipl_id
551 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
552 and pen.person_id = p_person_id
553 and pen.business_group_id+0 = p_business_group_id
554 and pen.prtt_enrt_rslt_stat_cd is null
555 and pen.effective_end_date = hr_api.g_eot
556 and pen.enrt_cvg_thru_dt = hr_api.g_eot -- 9999
557 and l_effective_date_1 <= pen.enrt_cvg_thru_dt
558 and pen.enrt_cvg_strt_dt < pen.effective_end_date
559 order by effective_start_date desc;
560 /*
561 and pen.enrt_cvg_thru_dt > p_effective_date
562 and p_effective_date
563 between pen.effective_start_date
564 and pen.effective_end_date;
565 */
566 --
567 cursor c_lee_rsn_pl(p_pl_id number) is
568 select lee.lee_rsn_id,
569 lee.enrt_perd_strt_dt_cd,
570 lee.enrt_perd_strt_dt_rl,
571 lee.enrt_perd_strt_days
572 from ben_lee_rsn_f lee,
573 ben_popl_enrt_typ_cycl_f pop
574 where pop.pl_id = p_pl_id
575 and pop.business_group_id +0 = p_business_group_id
576 and p_effective_date
577 between pop.effective_start_date
578 and pop.effective_end_date
579 and pop.popl_enrt_typ_cycl_id = lee.popl_enrt_typ_cycl_id
580 and lee.business_group_id +0 = pop.business_group_id
581 and lee.ler_id = p_ler_id
582 and p_effective_date
583 between lee.effective_start_date
584 and lee.effective_end_date;
585 --
586 -- PB :5422
587 --
588 CURSOR c_sched_enrol_period_for_plan(p_pl_id number,
589 p_lf_evt_ocrd_dt date) IS
590 SELECT enrtp.enrt_perd_id,
591 enrtp.strt_dt
592 FROM ben_popl_enrt_typ_cycl_f petc,
593 ben_enrt_perd enrtp
594 WHERE petc.pl_id = p_pl_id
595 AND petc.business_group_id = p_business_group_id
596 AND p_lf_evt_ocrd_dt BETWEEN petc.effective_start_date
597 AND petc.effective_end_date
598 AND petc.enrt_typ_cycl_cd <> 'L'
599 AND enrtp.business_group_id = p_business_group_id
600 AND enrtp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
601 /* PB :5422 AND enrtp.strt_dt=enrtp1.strt_dt
602 AND enrtp1.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
603 AND enrtp1.business_group_id = p_business_group_id */
604 AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id;
605 --
606 -- This cursor gets the enrolment period for scheduled
607 -- elections for program level
608 --
609 CURSOR c_sched_enrol_period_for_pgm(p_pgm_id number,
610 p_lf_evt_ocrd_dt date) IS
611 SELECT enrtp.enrt_perd_id,
612 enrtp.strt_dt
613 FROM ben_popl_enrt_typ_cycl_f petc,
614 ben_enrt_perd enrtp
615 WHERE petc.pgm_id = p_pgm_id
616 AND petc.business_group_id = p_business_group_id
617 AND p_lf_evt_ocrd_dt BETWEEN petc.effective_start_date
618 AND petc.effective_end_date
619 AND petc.enrt_typ_cycl_cd <> 'L'
620 AND enrtp.business_group_id = p_business_group_id
621 AND enrtp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
622 /* PB :5422 AND enrtp1.business_group_id = p_business_group_id
623 AND enrtp.strt_dt= enrtp1.strt_dt
624 AND enrtp1.enrt_perd_id = p_popl_enrt_typ_cycl_id */
625 AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id;
626 --
627 cursor c_lee_rsn_pgm(p_pgm_id number) is
628 select lee.lee_rsn_id,
629 lee.enrt_perd_strt_dt_cd,
630 lee.enrt_perd_strt_dt_rl,
631 lee.enrt_perd_strt_days
632 from ben_lee_rsn_f lee,
633 ben_popl_enrt_typ_cycl_f pop
634 where pop.pgm_id = p_pgm_id
635 and pop.business_group_id +0 = p_business_group_id
636 and p_effective_date
637 between pop.effective_start_date
638 and pop.effective_end_date
639 and pop.popl_enrt_typ_cycl_id = lee.popl_enrt_typ_cycl_id
640 and lee.business_group_id +0 = pop.business_group_id
641 and lee.ler_id = p_ler_id
642 and p_effective_date
643 between lee.effective_start_date
644 and lee.effective_end_date;
645 --
646 cursor c_imptd(v_pl_id in number) is
647 select 'Y'
648 from ben_pl_f pl
649 where pl.pl_id = v_pl_id
650 and pl.business_group_id + 0 = p_business_group_id
651 and p_effective_date between
652 pl.effective_start_date and pl.effective_end_date
653 and pl.subj_to_imptd_incm_typ_cd = 'PRTT';
654 --
655 -- CWB Changes.
656 --
657 cursor c_per_in_ler is
658 select pil.lf_evt_ocrd_dt, ler.typ_cd
659 from ben_per_in_ler pil,
660 ben_ler_f ler
661 where pil.per_in_ler_id = l_per_in_ler_id
662 and pil.ler_id = ler.ler_id
663 and p_effective_date between ler.effective_start_date and
664 ler.effective_end_date;
665 --
666 -- 22-Jun-01 bwharton 115.31
667 --
668 -- CWB Changes.
669 --
670 cursor c_procd_dt_pil is
671 select pil.procd_dt
672 from ben_per_in_ler pil,
673 ben_ler_f ler
674 where pil.person_id = p_person_id
675 and pil.per_in_ler_id <> l_per_in_ler_id
676 and pil.per_in_ler_stat_cd = 'PROCD'
677 and pil.ler_id = ler.ler_id
678 and p_effective_date between
679 ler.effective_start_date and ler.effective_end_date
680 and ler.typ_cd not in ('COMP','GSP', 'IREC')
681 order by lf_evt_ocrd_dt desc;
682
683 cursor c_procd_dt_pil_cwb is
684 select pil.procd_dt
685 from ben_per_in_ler pil,
686 ben_ler_f ler
687 where pil.person_id = p_person_id
688 and pil.per_in_ler_id <> l_per_in_ler_id
689 and pil.per_in_ler_stat_cd = 'PROCD'
690 and pil.ler_id = ler.ler_id
691 and p_effective_date between
692 ler.effective_start_date and ler.effective_end_date
693 and ler.ler_id = p_ler_id
694 and ler.typ_cd = 'COMP'
695 order by lf_evt_ocrd_dt desc;
696 --
697 --Bug#3279350
698
699 cursor c_pen_pl_2 is
700 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,ptip_id,
701 pen.effective_start_date,pen.enrt_mthd_cd,
702 pen.enrt_cvg_strt_dt
703 from ben_prtt_enrt_rslt_f pen
704 where p_pl_id = pen.pl_id
705 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
706 and pen.person_id = p_person_id
707 and pen.business_group_id = p_business_group_id
708 and pen.prtt_enrt_rslt_stat_cd is null
709 and pen.effective_end_date = hr_api.g_eot
710 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
711 and l_effective_date_1 < pen.enrt_cvg_thru_dt;
712 --
713 cursor c_pen_pgm_2 is
714 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,ptip_id,
715 pen.effective_start_date,pen.enrt_mthd_cd,
716 pen.enrt_cvg_strt_dt
717 from ben_prtt_enrt_rslt_f pen
718 where p_pgm_id = pen.pgm_id
719 and pen.person_id = p_person_id
720 and pen.business_group_id = p_business_group_id
721 and pen.prtt_enrt_rslt_stat_cd is null
722 and pen.effective_end_date = hr_api.g_eot
723 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
724 and l_effective_date_1 < pen.enrt_cvg_thru_dt;
725 --
726 cursor c_pen_oipl_2 is
727 select pen.prtt_enrt_rslt_id, pgm_id, pl_id, oipl_id, pen.object_version_number,
728 pen.effective_start_date,pen.enrt_mthd_cd,
729 pen.enrt_cvg_strt_dt
730 from ben_prtt_enrt_rslt_f pen
731 where p_oipl_id = pen.oipl_id
732 and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
733 and pen.person_id = p_person_id
734 and pen.business_group_id = p_business_group_id
735 and pen.prtt_enrt_rslt_stat_cd is null
736 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
737 and pen.effective_end_date = hr_api.g_eot
738 and l_effective_date_1 < pen.enrt_cvg_thru_dt;
739 --
740 cursor c_pen_max_esd (v_prtt_enrt_rslt_id in number) is
741 select pen.effective_end_date,pen.object_version_number
742 from ben_prtt_enrt_rslt_f pen,
743 ben_per_in_ler pil
744 where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
745 and pen.business_group_id = p_business_group_id
746 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
747 and pen.per_in_ler_id = pil.per_in_ler_id
748 and pen.effective_end_date <> hr_api.g_eot
749 order by pen.effective_end_date desc;
750 --
751 l_correction boolean;
752 l_update boolean;
753 l_update_override boolean;
754 l_update_change_insert boolean;
755 l_imptd_incm_chg boolean := false;
756 l_imptd_incm_flag varchar2(30);
757 l_inelig_lvl_in_pgm varchar2(30):= 'N' ;
758 l_datetrack_mode varchar2(30);
759 l_env_rec ben_env_object.g_global_env_rec_type;
760 l_benmngle_parm_rec benutils.g_batch_param_rec;
761 l_param_lf_evt_ocrd_dt date;
762 l_enrt_perd_id number;
763 l_LF_EVT_OCRD_DT date;
764 l_typ_cd varchar2(30);
765 l_ENRT_PERD_STRT_DT date;
766 l_procd_dt date; -- 22-Jun-01 bwharton 115.31
767 l_enrt_perd_strt_dt_cd varchar2(100);
768 l_enrt_perd_strt_dt_rl varchar2(100);
769 l_enrt_perd_strt_days number ;
770 l_enrt_strt_dt date;
771 l_pen_max_esd c_pen_max_esd%rowtype;
772 l_effective_start_date date;
773 l_effective_end_date date;
774
775
776 --
777 begin
778 --
779 hr_utility.set_location ('Entering '||l_proc,10);
780 hr_utility.set_location ('p_ler_id '||p_ler_id,10);
781 hr_utility.set_location ('p_pgm_id '||p_pgm_id,10);
782 hr_utility.set_location ('p_pl_id '||p_pl_id,10);
783 hr_utility.set_location ('p_oipl_id '||p_oipl_id,10);
784 --
785 -- Bug 5642702
786 -- If person is de-enroling at Program level then set variable G_DENROLING_FROM_PGM
787 -- so that we can obviate calls that create/update records in BEN_BNFT_PRVDD_LDGR_F table
788 -- See bebplapi.pkb for use of this variable
789 --
790 if p_oipl_id is null and p_pl_id is null and p_pgm_id is not null
791 then
792 --
793 g_denroling_from_pgm := 'Y';
794 --
795 else
796 --
797 g_denroling_from_pgm := 'N';
798 --
799 end if;
800 --
801 hr_utility.set_location ('g_denroling_from_pgm = ' || g_denroling_from_pgm, 15);
802 --
803 --
804 -- Bug#3279350 - undelete the prtt_enrt_rslt deenrolled in the future for OSB
805 -- Bug# 3507652 - Added "p_ler_id = ben_manage_life_events.g_ler_id" to delete future end-dated enrollments
806 -- when running in 'Unrestricted Mode' as well.
807 if ((p_ler_id is null) or (p_ler_id = ben_manage_life_events.g_ler_id)) then
808 --
809 hr_utility.set_location ('undelete prtt_enrt_rslts',10);
810 if p_oipl_id is not null then
811 for l_pen in c_pen_oipl_2 loop
812 -- call undelete
813 open c_pen_max_esd(l_pen.prtt_enrt_rslt_id);
814 fetch c_pen_max_esd into l_pen_max_esd;
815 if c_pen_max_esd%found then
816 ben_prtt_enrt_result_api.delete_prtt_enrt_result
817 (p_validate => false,
818 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
819 p_effective_start_date => l_effective_start_date,
820 p_effective_end_date => l_effective_end_date,
821 p_object_version_number => l_pen_max_esd.object_version_number,
822 p_effective_date => l_pen_max_esd.effective_end_date,
823 p_datetrack_mode => hr_api.g_future_change,
824 p_multi_row_validate => FALSE);
825 end if;
826 close c_pen_max_esd;
827
828 end loop;
829 elsif p_pl_id is not null then
830 for l_pen in c_pen_pl_2 loop
831 -- call undelete
832 open c_pen_max_esd(l_pen.prtt_enrt_rslt_id);
833 fetch c_pen_max_esd into l_pen_max_esd;
834 if c_pen_max_esd%found then
835 ben_prtt_enrt_result_api.delete_prtt_enrt_result
836 (p_validate => false,
837 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
838 p_effective_start_date => l_effective_start_date,
839 p_effective_end_date => l_effective_end_date,
840 p_object_version_number => l_pen_max_esd.object_version_number,
841 p_effective_date => l_pen_max_esd.effective_end_date,
842 p_datetrack_mode => hr_api.g_future_change,
843 p_multi_row_validate => FALSE);
844 end if;
845 close c_pen_max_esd;
846 --
847 end loop;
848 elsif p_pgm_id is not null then
849 for l_pen in c_pen_pgm_2 loop
850 --
851 open c_pen_max_esd(l_pen.prtt_enrt_rslt_id);
852 fetch c_pen_max_esd into l_pen_max_esd;
853 if c_pen_max_esd%found then
854 ben_prtt_enrt_result_api.delete_prtt_enrt_result
855 (p_validate => false,
856 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
857 p_effective_start_date => l_effective_start_date,
858 p_effective_end_date => l_effective_end_date,
859 p_object_version_number => l_pen_max_esd.object_version_number,
860 p_effective_date => l_pen_max_esd.effective_end_date,
861 p_datetrack_mode => hr_api.g_future_change,
862 p_multi_row_validate => FALSE);
863 end if;
864 close c_pen_max_esd;
865 --
866 end loop;
867 end if;
868 --
869 end if;
870 -- Attempt to get the active per in ler id for the person
871 --
872 if p_ler_id is not null then
873 --
874 -- check whether ler_id is of type unrestricted
875 if p_ler_id = ben_manage_life_events.g_ler_id then
876 --
877 l_per_in_ler_id := benutils.get_per_in_ler_id
878 (p_person_id => p_person_id,
879 p_business_group_id => p_business_group_id,
880 p_ler_id => p_ler_id,
881 p_lf_event_mode => 'U',
882 p_effective_date => p_effective_date);
883 else
884 --
885 l_per_in_ler_id := benutils.get_per_in_ler_id
886 (p_person_id => p_person_id,
887 p_business_group_id => p_business_group_id,
888 p_ler_id => p_ler_id,
889 p_effective_date => p_effective_date);
890 hr_utility.set_location('l_typ_cd ='||l_typ_cd,10.5);
891 --
892 end if;
893 --
894 hr_utility.set_location('l_per_in_ler_id '||l_per_in_ler_id,10);
895 --
896 end if;
897 --
898 open c_per_in_ler;
899 fetch c_per_in_ler into l_lf_evt_ocrd_dt, l_typ_cd;
900 close c_per_in_ler;
901 hr_utility.set_location('l_typ_cd ='||l_typ_cd,11.5);
902 -- Get enrt_perd_id from benmngle's parm list, if it exists.
903 -- Enrt_perd_id is in benmngle's popl_enrt_typ_cycl_id field.
904 ben_env_object.get(p_rec => l_env_rec);
905 benutils.get_batch_parameters(p_benefit_action_id => l_env_rec.benefit_action_id
906 ,p_rec => l_benmngle_parm_rec);
907 --
908 --
909
910
911 if l_benmngle_parm_rec.lf_evt_ocrd_dt is not null then
912
913 /* l_param_lf_evt_ocrd_dt :=
914 to_date(l_benmngle_parm_rec.lf_evt_ocrd_dt,'YYYY/MM/DD HH24:MI:SS');
915 hr_utility.set_location('l_per_in_ler_id '||l_per_in_ler_id,11);
916 l_param_lf_evt_ocrd_dt :=
917 to_date(to_char(trunc(l_param_lf_evt_ocrd_dt),'DD/MM/RRRR'),'DD/MM/RRRR');
918 l_param_lf_evt_ocrd_dt := to_date(l_benmngle_parm_rec.lf_evt_ocrd_dt);
919 */
920 --BUG 4046914
921 l_param_lf_evt_ocrd_dt :=
922 fnd_date.canonical_to_date(fnd_date.date_to_canonical(l_benmngle_parm_rec.lf_evt_ocrd_dt));
923
924
925 end if;
926 --
927 hr_utility.set_location('l_per_in_ler_id '||l_per_in_ler_id,12);
928 if p_oipl_id is not null then
929 -- PB : 5422 :
930 -- if l_benmngle_parm_rec.popl_enrt_typ_cycl_id is null then
931 if l_param_lf_evt_ocrd_dt is null then
932 --
933 -- TM added for backport of 115.6
934 --
935 ben_comp_object.get_object(p_oipl_id => p_oipl_id,
936 p_rec => l_oipl_rec);
937 --
938 end if;
939
940 for l_pen in c_pen_oipl loop
941 --
942 -- PB : 5422 :
943 -- if l_benmngle_parm_rec.popl_enrt_typ_cycl_id is null then
944 --
945 l_enrt_perd_id := null;
946
947 -- bug : 4645272 : when Open/Administartive runs in Life event mode, populate enrt_perd_id
948 if l_param_lf_evt_ocrd_dt is null and l_typ_cd not in ('SCHEDDO','SCHEDDA') then
949 l_lee_rsn_id:=null;
950
951 open c_lee_rsn_pl(l_pen.pl_id);
952 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;
953 close c_lee_rsn_pl;
954 if l_lee_rsn_id is null then
955 open c_lee_rsn_pgm(l_pen.pgm_id);
956 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;
957 close c_lee_rsn_pgm;
958 end if;
959 if l_enrt_perd_strt_dt_cd is not null then
960 ben_determine_date.main(
961 p_date_cd => l_enrt_perd_strt_dt_cd,
962 p_person_id => p_person_id,
963 p_per_in_ler_id => l_per_in_ler_id,
964 p_pgm_id => p_pgm_id,
965 p_pl_id => p_pl_id,
966 p_oipl_id => p_oipl_id,
967 p_business_group_id => p_business_group_id,
968 p_formula_id => l_enrt_perd_strt_dt_rl,
969 p_effective_date => p_effective_date,
970 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
971 p_returned_date => l_enrt_perd_strt_dt);
972
973 if l_enrt_perd_strt_dt_cd in ( 'NUMDOE', 'NUMDON','NUMDOEN') then
974 l_enrt_perd_strt_dt := l_enrt_perd_strt_dt + nvl(l_enrt_perd_strt_days,0) ;
975 end if ;
976 end if;
977 --
978 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_PERD_STRT_DT,p_effective_date));-- 5257226 - 1;
979 -- Enrollments were getting end-dated 2 days prior if passed eff_dt-1, passing the eff_dt now.
980
981 hr_utility.set_location('l_effective_date '||l_effective_date,10);
982 else
983 --
984
985 -- PB : 5422 :
986 --
987 OPEN c_sched_enrol_period_for_plan(l_pen.pl_id,
988 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
989 FETCH c_sched_enrol_period_for_plan INTO l_enrt_perd_id,l_enrt_strt_dt;
990 --
991 IF c_sched_enrol_period_for_plan%NOTFOUND THEN
992 --
993 OPEN c_sched_enrol_period_for_pgm(l_pen.pgm_id,
994 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
995 FETCH c_sched_enrol_period_for_pgm INTO l_enrt_perd_id,l_enrt_strt_dt;
996 CLOSE c_sched_enrol_period_for_pgm;
997 --
998 END IF;
999 CLOSE c_sched_enrol_period_for_plan;
1000 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_STRT_DT,p_effective_date)); -- 5257226 - 1;
1001 --
1002 end if;
1003 --
1004 open c_imptd(l_pen.pl_id);
1005 fetch c_imptd into l_imptd_incm_flag;
1006 if c_imptd%found then
1007 --
1008 l_imptd_incm_chg := true;
1009 --
1010 end if;
1011 close c_imptd;
1012 --
1013 /*
1014 ben_provider_pools.recompute_flex_credits(
1015 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1016 p_pgm_id => l_pen.pgm_id,
1017 p_per_in_ler_id => l_per_in_ler_id,
1018 p_person_id => p_person_id,
1019 p_enrt_mthd_cd => 'E',
1020 p_business_group_id => p_business_group_id,
1021 p_effective_date => p_effective_date);
1022 --
1023 ben_determine_date.rate_and_coverage_dates
1024 (p_which_dates_cd => 'C',
1025 p_date_mandatory_flag => 'N',
1026 p_compute_dates_flag => 'N',
1027 p_per_in_ler_id => l_per_in_ler_id,
1028 p_person_id => p_person_id,
1029 p_pgm_id => l_pen.pgm_id,
1030 p_pl_id => l_pen.pl_id,
1031 p_oipl_id => l_pen.oipl_id,
1032 p_business_group_id => p_business_group_id,
1033 p_enrt_cvg_strt_dt => l_dummy_dt,
1034 p_enrt_cvg_strt_dt_cd => l_dummy_varchar,
1035 p_enrt_cvg_strt_dt_rl => l_dummy_num,
1036 p_rt_strt_dt => l_dummy_dt,
1037 p_rt_strt_dt_cd => l_dummy_varchar,
1038 p_rt_strt_dt_rl => l_dummy_num,
1039 p_enrt_cvg_end_dt => l_dummy_dt,
1040 p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd,
1041 p_enrt_cvg_end_dt_rl => l_dummy_num,
1042 p_rt_end_dt => l_dummy_dt,
1043 p_rt_end_dt_cd => l_dummy_varchar,
1044 p_rt_end_dt_rl => l_dummy_num,
1045 p_effective_date => p_effective_date,
1046 p_lf_evt_ocrd_dt => null,
1047 p_lee_rsn_id => l_lee_rsn_id
1048 );
1049 --
1050 if substr(nvl(l_enrt_cvg_end_dt_cd, '-1'), 1, 1) = 'W' then
1051 --
1052 l_ovn := l_pen.object_version_number;
1053 --
1054 dt_api.find_dt_upd_modes
1055 (p_effective_date => p_effective_date,
1056 p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
1057 p_base_key_column => 'prtt_enrt_rslt_id',
1058 p_base_key_value => l_pen.prtt_enrt_rslt_id,
1059 p_correction => l_correction,
1060 p_update => l_update,
1061 p_update_override => l_update_override,
1062 p_update_change_insert => l_update_change_insert);
1063 --
1064 if l_update_override then
1065 --
1066 l_datetrack_mode := hr_api.g_update_override;
1067 --
1068 elsif l_update then
1069 --
1070 l_datetrack_mode := hr_api.g_update;
1071 --
1072 else
1073 --
1074 l_datetrack_mode := hr_api.g_correction;
1075 --
1076 end if;
1077 --
1078 ben_prtt_enrt_result_api.update_PRTT_ENRT_RESULT
1079 (p_validate => false
1080 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1081 ,p_effective_start_date => l_eff_strt
1082 ,p_effective_end_date => l_eff_end
1083 ,p_ler_id => p_ler_id
1084 ,p_no_lngr_elig_flag => 'Y'
1085 ,p_object_version_number => l_ovn
1086 ,p_per_in_ler_id => l_per_in_ler_id
1087 ,p_effective_date => p_effective_date
1088 ,p_datetrack_mode => l_datetrack_mode
1089 ,p_multi_row_validate => false
1090 );
1091 --
1092 else
1093 --
1094 */
1095 --
1096 -- 22-Jun-01 bwharton 115.31
1097 l_procd_dt := l_effective_date;
1098 --
1099 -- CWB changes : First determine the typ_cd of current per in ler
1100 -- and look for only COMP typ per in ler's if current per in ler is
1101 -- Comp type.
1102 --
1103 if l_typ_cd = 'COMP' then
1104 --
1105 for procd_dt_rec in c_procd_dt_pil_cwb loop
1106 l_procd_dt := procd_dt_rec.procd_dt;
1107 exit;
1108 end loop;
1109 --
1110 else
1111 --
1112 for procd_dt_rec in c_procd_dt_pil loop
1113 l_procd_dt := procd_dt_rec.procd_dt;
1114 exit;
1115 end loop;
1116 --
1117 end if;
1118 -- Bug 2200139 for Override Case look at the pen esd also
1119 hr_utility.set_location('Effective Date'||l_effective_date,111);
1120 hr_utility.set_location('l_typ_cd ='||l_typ_cd,111);
1121 if l_pen.enrt_mthd_cd = 'O' then
1122 l_effective_date := greatest(l_effective_date,
1123 greatest(l_procd_dt,l_pen.effective_start_date + 1));
1124 else
1125 l_effective_date := greatest (l_effective_date, l_procd_dt);
1126 end if;
1127 -- if benmngle is in selection mode, no need to compute the effective date
1128 if l_benmngle_parm_rec.mode_cd = 'S' or l_benmngle_parm_rec.mode_cd = 'U' or
1129 l_typ_cd = 'ABS' or l_typ_cd = 'GSP' or
1130 l_effective_date < l_pen.enrt_cvg_strt_dt then
1131 hr_utility.set_location('l_typ_cd ='||l_typ_cd,123);
1132 l_effective_date := p_effective_date;
1133 end if;
1134 -- Bug#2641545 - not to lose the per in ler of enrollment the result record should be
1135 -- datetrack updated with per in ler of ineligible life event
1136 if l_effective_date <= l_pen.effective_start_date and p_ler_id is not null then
1137 l_effective_date := l_pen.effective_start_date + 1;
1138 --bug#3279350
1139 elsif l_effective_date < l_pen.effective_start_date then
1140 l_effective_date := l_pen.effective_start_date;
1141 end if;
1142
1143 hr_utility.set_location('Effective Date'||l_effective_date,111);
1144 --
1145 ben_provider_pools.recompute_flex_credits(
1146 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1147 p_pgm_id => l_pen.pgm_id,
1148 p_per_in_ler_id => l_per_in_ler_id,
1149 p_person_id => p_person_id,
1150 p_enrt_mthd_cd => 'E',
1151 p_business_group_id => p_business_group_id,
1152 p_effective_date => l_effective_date);
1153 --
1154 hr_utility.set_location(' BKKKK DELETING THE FUTURE CVG OIPL ',10);
1155
1156 --
1157 /*
1158 if (l_per_in_ler_id IS NOT NULL) then
1159
1160 ben_election_information.backout_future_coverage
1161 (p_per_in_ler_id => l_per_in_ler_id,
1162 p_business_group_id => p_business_group_id,
1163 p_person_id => p_person_id,
1164 p_pgm_id => NVL(l_pen.pgm_id,p_pgm_id),
1165 p_pl_id => p_pl_id,
1166 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1167 p_effective_date => p_effective_date,
1168 p_prtt_enrt_rslt_id => l_dummy_num ) ;
1169 end if;
1170 */
1171 --
1172
1173 ben_prtt_enrt_result_api.delete_enrollment
1174 (p_validate => false ,
1175 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1176 p_per_in_ler_id => l_per_in_ler_id,
1177 p_lee_rsn_id => l_lee_rsn_id,
1178 -- PB : 5422
1179 p_enrt_perd_id => l_enrt_perd_id, -- l_benmngle_parm_rec.popl_enrt_typ_cycl_id,
1180 p_business_group_id => p_business_group_id ,
1181 p_effective_start_date => l_eff_strt,
1182 p_effective_end_date => l_eff_end,
1183 p_object_version_number => l_pen.object_version_number,
1184 p_effective_date => l_effective_date,
1185 p_datetrack_mode => 'DELETE',
1186 p_multi_row_validate => false,
1187 p_source => 'beninelg');
1188 --
1189 ben_prtt_enrt_result_api.g_enrollment_change := TRUE;
1190 --
1191 -- end if;
1192 --
1193 end loop;
1194 --
1195 elsif p_pl_id is not null then
1196 for l_pen in c_pen_pl loop
1197 --
1198 -- PB : 5422
1199 l_enrt_perd_id := null; --bug 4645272
1200 -- bug : 4645272 : when Open/Administartive runs in Life event mode, populate enrt_perd_id
1201 if l_param_lf_evt_ocrd_dt is null and l_typ_cd not in ('SCHEDDO','SCHEDDA') then
1202 -- if l_benmngle_parm_rec.popl_enrt_typ_cycl_id is null then
1203 l_lee_rsn_id:=null;
1204 open c_lee_rsn_pl(l_pen.pl_id);
1205 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;
1206 close c_lee_rsn_pl;
1207 if l_lee_rsn_id is null then
1208 open c_lee_rsn_pgm(l_pen.pgm_id);
1209 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;
1210 close c_lee_rsn_pgm;
1211 end if;
1212 if l_enrt_perd_strt_dt_cd is not null then
1213 ben_determine_date.main(
1214 p_date_cd => l_enrt_perd_strt_dt_cd,
1215 p_person_id => p_person_id,
1216 p_per_in_ler_id => l_per_in_ler_id,
1217 p_pgm_id => p_pgm_id,
1218 p_pl_id => p_pl_id,
1219 p_oipl_id => p_oipl_id,
1220 p_business_group_id => p_business_group_id,
1221 p_formula_id => l_enrt_perd_strt_dt_rl,
1222 p_effective_date => p_effective_date,
1223 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1224 p_returned_date => l_enrt_perd_strt_dt);
1225
1226 if l_enrt_perd_strt_dt_cd in ( 'NUMDOE', 'NUMDON','NUMDOEN') then
1227 l_enrt_perd_strt_dt := l_enrt_perd_strt_dt + nvl(l_enrt_perd_strt_days,0) ;
1228 end if ;
1229
1230 end if;
1231 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_PERD_STRT_DT,p_effective_date)); -- 5257226 - 1;
1232
1233 else
1234 --
1235 -- PB : 5422 :
1236 --
1237 OPEN c_sched_enrol_period_for_plan(l_pen.pl_id,
1238 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
1239 FETCH c_sched_enrol_period_for_plan INTO l_enrt_perd_id,l_enrt_strt_dt;
1240 --
1241 IF c_sched_enrol_period_for_plan%NOTFOUND THEN
1242 --
1243 OPEN c_sched_enrol_period_for_pgm(l_pen.pgm_id,
1244 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
1245 FETCH c_sched_enrol_period_for_pgm INTO l_enrt_perd_id,l_enrt_strt_dt;
1246 CLOSE c_sched_enrol_period_for_pgm;
1247 --
1248 END IF;
1249 CLOSE c_sched_enrol_period_for_plan;
1250 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_STRT_DT,p_effective_date)); -- 5257226 - 1;
1251 --
1252 end if;
1253 --
1254 open c_imptd(l_pen.pl_id);
1255 fetch c_imptd into l_imptd_incm_flag;
1256 if c_imptd%found then
1257 --
1258 l_imptd_incm_chg := true;
1259 --
1260 end if;
1261 close c_imptd;
1262 --
1263 /*
1264 ben_provider_pools.recompute_flex_credits(
1265 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1266 p_pgm_id => l_pen.pgm_id,
1267 p_per_in_ler_id => l_per_in_ler_id,
1268 p_person_id => p_person_id,
1269 p_enrt_mthd_cd => 'E',
1270 p_business_group_id => p_business_group_id,
1271 p_effective_date => p_effective_date);
1272 --
1273 ben_determine_date.rate_and_coverage_dates
1274 (p_which_dates_cd => 'C',
1275 p_date_mandatory_flag => 'N',
1276 p_compute_dates_flag => 'N',
1277 p_per_in_ler_id => l_per_in_ler_id,
1278 p_person_id => p_person_id,
1279 p_pgm_id => l_pen.pgm_id,
1280 p_pl_id => l_pen.pl_id,
1281 p_oipl_id => l_pen.oipl_id,
1282 p_business_group_id => p_business_group_id,
1283 p_enrt_cvg_strt_dt => l_dummy_dt,
1284 p_enrt_cvg_strt_dt_cd => l_dummy_varchar,
1285 p_enrt_cvg_strt_dt_rl => l_dummy_num,
1286 p_rt_strt_dt => l_dummy_dt,
1287 p_rt_strt_dt_cd => l_dummy_varchar,
1288 p_rt_strt_dt_rl => l_dummy_num,
1289 p_enrt_cvg_end_dt => l_dummy_dt,
1290 p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd,
1291 p_enrt_cvg_end_dt_rl => l_dummy_num,
1292 p_rt_end_dt => l_dummy_dt,
1293 p_rt_end_dt_cd => l_dummy_varchar,
1294 p_rt_end_dt_rl => l_dummy_num,
1295 p_effective_date => p_effective_date,
1296 p_lf_evt_ocrd_dt => null,
1297 p_lee_rsn_id => l_lee_rsn_id
1298 );
1299 --
1300 hr_utility.set_location('Beninelg: cvg_end_dt_cd='||l_enrt_cvg_end_dt_cd,1067);
1301 if substr(nvl(l_enrt_cvg_end_dt_cd, '-1'), 1, 1) = 'W' then
1302 --
1303 l_ovn := l_pen.object_version_number;
1304 --
1305 hr_utility.set_location('Before updating enrt result ',10);
1306 hr_utility.set_location('p_effective_date '||p_effective_date,10);
1307 --
1308 dt_api.find_dt_upd_modes
1309 (p_effective_date => p_effective_date,
1310 p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
1311 p_base_key_column => 'prtt_enrt_rslt_id',
1312 p_base_key_value => l_pen.prtt_enrt_rslt_id,
1313 p_correction => l_correction,
1314 p_update => l_update,
1315 p_update_override => l_update_override,
1316 p_update_change_insert => l_update_change_insert);
1317 --
1318 if l_update_override then
1319 --
1320 l_datetrack_mode := hr_api.g_update_override;
1321 --
1322 elsif l_update then
1323 --
1324 l_datetrack_mode := hr_api.g_update;
1325 --
1326 else
1327 --
1328 l_datetrack_mode := hr_api.g_correction;
1329 --
1330 end if;
1331 --
1332 ben_prtt_enrt_result_api.update_PRTT_ENRT_RESULT
1333 (p_validate => false
1334 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1335 ,p_effective_start_date => l_eff_strt
1336 ,p_effective_end_date => l_eff_end
1337 ,p_ler_id => p_ler_id
1338 ,p_no_lngr_elig_flag => 'Y'
1339 ,p_object_version_number => l_ovn
1340 ,p_per_in_ler_id => l_per_in_ler_id
1341 ,p_effective_date => p_effective_date
1342 ,p_datetrack_mode => l_datetrack_mode
1343 ,p_multi_row_validate => false
1344 );
1345 --
1346 else
1347 */
1348 --
1349 --
1350 -- 22-Jun-01 bwharton 115.31
1351 l_procd_dt := l_effective_date;
1352 --
1353 -- CWB changes : First determine the typ_cd of current per in ler
1354 -- and look for only COMP typ per in ler's if current per in ler is
1355 -- Comp type.
1356 --
1357 if l_typ_cd = 'COMP' then
1358 --
1359 for procd_dt_rec in c_procd_dt_pil_cwb loop
1360 l_procd_dt := procd_dt_rec.procd_dt;
1361 exit;
1362 end loop;
1363 --
1364 else
1365 --
1366 for procd_dt_rec in c_procd_dt_pil loop
1367 l_procd_dt := procd_dt_rec.procd_dt;
1368 exit;
1369 end loop;
1370 --
1371 end if;
1372 --
1373 -- Bug 2200139 for Override Case look at the pen esd also
1374 if l_pen.enrt_mthd_cd = 'O' then
1375 l_effective_date := greatest(l_effective_date,
1376 greatest(l_procd_dt,l_pen.effective_start_date + 1));
1377 else
1378 l_effective_date := greatest (l_effective_date, l_procd_dt);
1379 end if;
1380 -- if benmngle is in selection mode, no need to compute the effective date
1381 -- for absence life event also the effective date is not computed
1382 if l_benmngle_parm_rec.mode_cd = 'S' or l_benmngle_parm_rec.mode_cd = 'U' or
1383 l_typ_cd = 'ABS' or l_typ_cd = 'GSP' /*or
1384 l_effective_date < l_pen.enrt_cvg_strt_dt */ then --Commented the condition for the bug 9030738
1385 l_effective_date := p_effective_date;
1386 end if;
1387 --
1388 --bug#3279350
1389 if l_effective_date <= l_pen.effective_start_date and p_ler_id is not null then
1390 l_effective_date := l_pen.effective_start_date + 1;
1391 elsif l_effective_date < l_pen.effective_start_date then
1392 l_effective_date := l_pen.effective_start_date;
1393 end if;
1394 --
1395 ben_provider_pools.recompute_flex_credits(
1396 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1397 p_pgm_id => l_pen.pgm_id,
1398 p_per_in_ler_id => l_per_in_ler_id,
1399 p_person_id => p_person_id,
1400 p_enrt_mthd_cd => 'E',
1401 p_business_group_id => p_business_group_id,
1402 p_effective_date => l_effective_date);
1403 --
1404 hr_utility.set_location('Effective Date'||l_effective_date,112);
1405 hr_utility.set_location(' BKKKK DELETING THE FUTURE CVG PLN ',10);
1406 --
1407 /*
1408 if (l_per_in_ler_id IS NOT NULL) then
1409 ben_election_information.backout_future_coverage
1410 (p_per_in_ler_id => l_per_in_ler_id,
1411 p_business_group_id => p_business_group_id,
1412 p_person_id => p_person_id,
1413 p_pgm_id => NVL(l_pen.pgm_id,p_pgm_id),
1414 p_pl_id => p_pl_id,
1415 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1416 p_effective_date => p_effective_date ,
1417 p_prtt_enrt_rslt_id => l_dummy_num ) ;
1418 end if;
1419 */
1420 --
1421
1422 ben_prtt_enrt_result_api.delete_enrollment
1423 (p_validate => false ,
1424 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1425 p_per_in_ler_id => l_per_in_ler_id,
1426 p_lee_rsn_id => l_lee_rsn_id,
1427 -- PB : 5422
1428 p_enrt_perd_id => l_enrt_perd_id, -- l_benmngle_parm_rec.popl_enrt_typ_cycl_id ,
1429 p_business_group_id => p_business_group_id ,
1430 p_effective_start_date => l_eff_strt,
1431 p_effective_end_date => l_eff_end,
1432 p_object_version_number => l_pen.object_version_number,
1433 p_effective_date => l_effective_date,
1434 p_datetrack_mode => 'DELETE',
1435 p_multi_row_validate => false,
1436 p_source => 'beninelg');
1437 --
1438 ben_prtt_enrt_result_api.g_enrollment_change := TRUE;
1439 --
1440 -- Check if we should terminate COBRA eligibility.
1441 --
1442 hr_utility.set_location('l_pen.pgm_id: '||l_pen.pgm_id,10);
1443 hr_utility.set_location('l_pen.ptip_id: '||l_pen.ptip_id,10);
1444 --
1445 if l_pen.pgm_id is not null
1446 then
1447 ben_comp_object.get_object(p_pgm_id => l_pen.pgm_id,
1448 p_rec => l_pgm_rec);
1449 hr_utility.set_location('pgm_typ: '||l_pgm_rec.pgm_typ_cd,10);
1450 if l_pgm_rec.pgm_typ_cd like 'COBRA%' then
1451 hr_utility.set_location('pgm_typ: '||l_pgm_rec.pgm_typ_cd,10);
1452 ben_cobra_requirements.g_cobra_enrollment_change := TRUE;
1453 end if;
1454 end if;
1455 --
1456 -- end if;
1457 --
1458 end loop;
1459 --
1460 elsif p_pgm_id is not null then
1461 for l_pen in c_pen_pgm loop
1462 --
1463 l_enrt_perd_id:= null; ---- bug : 4645272
1464 -- PB : 5422
1465 -- bug : 4645272 : when Open/Administartive runs in Life event mode, populate enrt_perd_id
1466
1467 if l_param_lf_evt_ocrd_dt is null and l_typ_cd not in ('SCHEDDO','SCHEDDA') then
1468
1469 -- if l_benmngle_parm_rec.popl_enrt_typ_cycl_id is null then
1470 l_lee_rsn_id:=null;
1471
1472 open c_lee_rsn_pl(l_pen.pl_id);
1473 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;
1474 close c_lee_rsn_pl;
1475 if l_lee_rsn_id is null then
1476 open c_lee_rsn_pgm(l_pen.pgm_id);
1477 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;
1478 close c_lee_rsn_pgm;
1479 end if;
1480 if l_enrt_perd_strt_dt_cd is not null then
1481 ben_determine_date.main(
1482 p_date_cd => l_enrt_perd_strt_dt_cd,
1483 p_person_id => p_person_id,
1484 p_per_in_ler_id => l_per_in_ler_id,
1485 p_pgm_id => p_pgm_id,
1486 p_pl_id => p_pl_id,
1487 p_oipl_id => p_oipl_id,
1488 p_business_group_id => p_business_group_id,
1489 p_formula_id => l_enrt_perd_strt_dt_rl,
1490 p_effective_date => p_effective_date,
1491 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1492 p_returned_date => l_enrt_perd_strt_dt);
1493
1494 if l_enrt_perd_strt_dt_cd in ( 'NUMDOE', 'NUMDON','NUMDOEN') then
1495 l_enrt_perd_strt_dt := l_enrt_perd_strt_dt + nvl(l_enrt_perd_strt_days,0) ;
1496 end if ;
1497 end if;
1498 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_PERD_STRT_DT,p_effective_date)); -- 5257226 - 1;
1499
1500 else
1501 --
1502 -- PB : 5422 :
1503 --
1504 OPEN c_sched_enrol_period_for_pgm(l_pen.pgm_id,
1505 nvl(l_param_lf_evt_ocrd_dt,l_lf_evt_ocrd_dt)); --bug 4645272
1506
1507 FETCH c_sched_enrol_period_for_pgm INTO l_enrt_perd_id,l_enrt_strt_dt;
1508 CLOSE c_sched_enrol_period_for_pgm;
1509 --
1510 l_effective_date := least(nvl(l_lf_evt_ocrd_dt,p_effective_date), nvl(l_ENRT_STRT_DT,p_effective_date)); --5257226 - 1;
1511 end if;
1512 --
1513 open c_imptd(l_pen.pl_id);
1514 fetch c_imptd into l_imptd_incm_flag;
1515 if c_imptd%found then
1516 --
1517 l_imptd_incm_chg := true;
1518 l_inelig_lvl_in_pgm := 'Y' ;
1519 --
1520 end if;
1521 close c_imptd;
1522 --
1523 -- MOVED to pass this date to ben_provider_pools.recompute_flex_credits aslo
1524 -- 22-Jun-01 bwharton 115.31
1525 --
1526 l_procd_dt := l_effective_date;
1527 --
1528 -- CWB changes : First determine the typ_cd of current per in ler
1529 -- and look for only COMP typ per in ler's if current per in ler is
1530 -- Comp type.
1531 --
1532 if l_typ_cd = 'COMP' then
1533 --
1534 for procd_dt_rec in c_procd_dt_pil_cwb loop
1535 l_procd_dt := procd_dt_rec.procd_dt;
1536 exit;
1537 end loop;
1538 --
1539 else
1540 --
1541 for procd_dt_rec in c_procd_dt_pil loop
1542 l_procd_dt := procd_dt_rec.procd_dt;
1543 exit;
1544 end loop;
1545 --
1546 end if;
1547 -- Bug 2200139 for Override Case look at the pen esd also
1548 if l_pen.enrt_mthd_cd = 'O' then
1549 l_effective_date := greatest(l_effective_date,
1550 greatest(l_procd_dt,l_pen.effective_start_date + 1));
1551 else
1552 l_effective_date := greatest (l_effective_date, l_procd_dt);
1553 end if;
1554
1555 -- if benmngle is in selection mode, no need to compute the effective date
1556 if l_benmngle_parm_rec.mode_cd = 'S' or l_benmngle_parm_rec.mode_cd = 'U' or
1557 l_typ_cd = 'ABS' or l_typ_cd = 'GSP' or
1558 l_effective_date < l_pen.enrt_cvg_strt_dt then
1559 l_effective_date := p_effective_date;
1560 end if;
1561 --
1562 --bug#3279350
1563 if l_effective_date <= l_pen.effective_start_date and p_ler_id is not null then
1564 l_effective_date := l_pen.effective_start_date + 1;
1565 elsif l_effective_date < l_pen.effective_start_date then
1566 l_effective_date := l_pen.effective_start_date;
1567 end if;
1568 --
1569 ben_provider_pools.recompute_flex_credits(
1570 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1571 p_pgm_id => l_pen.pgm_id,
1572 p_per_in_ler_id => l_per_in_ler_id,
1573 p_person_id => p_person_id,
1574 p_enrt_mthd_cd => 'E',
1575 p_business_group_id => p_business_group_id,
1576 p_effective_date => l_effective_date ); -- p_effective_date);
1577 --
1578 /*
1579 ben_determine_date.rate_and_coverage_dates
1580 (p_which_dates_cd => 'C',
1581 p_date_mandatory_flag => 'N',
1582 p_compute_dates_flag => 'N',
1583 p_per_in_ler_id => l_per_in_ler_id,
1584 p_person_id => p_person_id,
1585 p_pgm_id => l_pen.pgm_id,
1586 p_pl_id => l_pen.pl_id,
1587 p_oipl_id => l_pen.oipl_id,
1588 p_business_group_id => p_business_group_id,
1589 p_enrt_cvg_strt_dt => l_dummy_dt,
1590 p_enrt_cvg_strt_dt_cd => l_dummy_varchar,
1591 p_enrt_cvg_strt_dt_rl => l_dummy_num,
1592 p_rt_strt_dt => l_dummy_dt,
1593 p_rt_strt_dt_cd => l_dummy_varchar,
1594 p_rt_strt_dt_rl => l_dummy_num,
1595 p_enrt_cvg_end_dt => l_dummy_dt,
1596 p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd,
1597 p_enrt_cvg_end_dt_rl => l_dummy_num,
1598 p_rt_end_dt => l_dummy_dt,
1599 p_rt_end_dt_cd => l_dummy_varchar,
1600 p_rt_end_dt_rl => l_dummy_num,
1601 p_effective_date => p_effective_date,
1602 p_lf_evt_ocrd_dt => null,
1603 p_lee_rsn_id => l_lee_rsn_id
1604 );
1605 --
1606 hr_utility.set_location('Before updating enrt result ',10);
1607 hr_utility.set_location('p_effective_date '||p_effective_date,10);
1608 --
1609 if substr(nvl(l_enrt_cvg_end_dt_cd, '-1'), 1, 1) = 'W' then
1610 --
1611 l_ovn := l_pen.object_version_number;
1612 --
1613 dt_api.find_dt_upd_modes
1614 (p_effective_date => p_effective_date,
1615 p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
1616 p_base_key_column => 'prtt_enrt_rslt_id',
1617 p_base_key_value => l_pen.prtt_enrt_rslt_id,
1618 p_correction => l_correction,
1619 p_update => l_update,
1620 p_update_override => l_update_override,
1621 p_update_change_insert => l_update_change_insert);
1622 --
1623 if l_update_override then
1624 --
1625 l_datetrack_mode := hr_api.g_update_override;
1626 --
1627 elsif l_update then
1628 --
1629 l_datetrack_mode := hr_api.g_update;
1630 --
1631 else
1632 --
1633 l_datetrack_mode := hr_api.g_correction;
1634 --
1635 end if;
1636 --
1637 hr_utility.set_location('pen_id='||l_pen.prtt_enrt_rslt_id,1963);
1638 ben_prtt_enrt_result_api.update_PRTT_ENRT_RESULT
1639 (p_validate => false
1640 ,p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id
1641 ,p_effective_start_date => l_eff_strt
1642 ,p_effective_end_date => l_eff_end
1643 ,p_ler_id => p_ler_id
1644 ,p_no_lngr_elig_flag => 'Y'
1645 ,p_object_version_number => l_ovn
1646 ,p_per_in_ler_id => l_per_in_ler_id
1647 ,p_effective_date => p_effective_date
1648 ,p_datetrack_mode => l_datetrack_mode
1649 ,p_multi_row_validate => false
1650 );
1651 --
1652 else
1653 --
1654 */
1655 --
1656 /* Moved to up use this for ben_provider_pools.recompute_flex_credits also
1657 -- 22-Jun-01 bwharton 115.31
1658 l_procd_dt := l_effective_date;
1659 --
1660 -- CWB changes : First determine the typ_cd of current per in ler
1661 -- and look for only COMP typ per in ler's if current per in ler is
1662 -- Comp type.
1663 --
1664 if l_typ_cd = 'COMP' then
1665 --
1666 for procd_dt_rec in c_procd_dt_pil_cwb loop
1667 l_procd_dt := procd_dt_rec.procd_dt;
1668 exit;
1669 end loop;
1670 --
1671 else
1672 --
1673 for procd_dt_rec in c_procd_dt_pil loop
1674 l_procd_dt := procd_dt_rec.procd_dt;
1675 exit;
1676 end loop;
1677 --
1678 end if;
1679 --
1680 -- Bug 2200139 for Override Case look at the pen esd also
1681 if l_pen.enrt_mthd_cd = 'O' then
1682 l_effective_date := greatest(l_effective_date,
1683 greatest(l_procd_dt,l_pen.effective_start_date + 1 ));
1684 else
1685 l_effective_date := greatest (l_effective_date, l_procd_dt);
1686 end if;
1687
1688 -- if benmngle is in selection mode, no need to compute the effective date
1689 if l_benmngle_parm_rec.mode_cd = 'S' or l_benmngle_parm_rec.mode_cd = 'U' or
1690 l_typ_cd = 'ABS' or l_typ_cd = 'GSP' or
1691 l_effective_date < l_pen.enrt_cvg_strt_dt then
1692 l_effective_date := p_effective_date;
1693 end if;
1694 */
1695
1696
1697 hr_utility.set_location('Effective Date'||l_effective_date,113);
1698 --
1699 hr_utility.set_location(' BKKKK DELETING THE FUTURE CVG PGM ',10);
1700 --
1701 /*
1702 if (l_per_in_ler_id IS NOT NULL) then
1703 ben_election_information.backout_future_coverage
1704 (p_per_in_ler_id => l_per_in_ler_id,
1705 p_business_group_id => p_business_group_id,
1706 p_person_id => p_person_id,
1707 p_pgm_id => p_pgm_id,
1708 p_pl_id => p_pl_id,
1709 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
1710 p_effective_date => p_effective_date ,
1711 p_prtt_enrt_rslt_id => l_dummy_num ) ;
1712 end if;
1713 */
1714 --
1715
1716 ben_prtt_enrt_result_api.delete_enrollment
1717 (p_validate => false ,
1718 p_prtt_enrt_rslt_id => l_pen.prtt_enrt_rslt_id,
1719 p_per_in_ler_id => l_per_in_ler_id,
1720 p_lee_rsn_id => l_lee_rsn_id,
1721 -- PB : 5422
1722 p_enrt_perd_id => l_enrt_perd_id, -- l_benmngle_parm_rec.popl_enrt_typ_cycl_id
1723 p_business_group_id => p_business_group_id ,
1724 p_effective_start_date => l_eff_strt,
1725 p_effective_end_date => l_eff_end,
1726 p_object_version_number => l_pen.object_version_number,
1727 p_effective_date => l_effective_date,
1728 p_datetrack_mode => 'DELETE',
1729 p_multi_row_validate => FALSE,
1730 p_source => 'beninelg');
1731 --
1732 ben_prtt_enrt_result_api.g_enrollment_change := TRUE;
1733 --
1734 --
1735 -- Check if we should terminate COBRA eligibility.
1736 --
1737 hr_utility.set_location('p_pgm_id: '||p_pgm_id,10);
1738 hr_utility.set_location('l_pen.ptip_id: '||l_pen.ptip_id,10);
1739 --
1740 ben_comp_object.get_object(p_pgm_id => p_pgm_id,
1741 p_rec => l_pgm_rec);
1742 hr_utility.set_location('pgm_typ: '||l_pgm_rec.pgm_typ_cd,10);
1743 if l_pgm_rec.pgm_typ_cd like 'COBRA%' then
1744 hr_utility.set_location('pgm_typ: '||l_pgm_rec.pgm_typ_cd,20);
1745 ben_cobra_requirements.g_cobra_enrollment_change := TRUE;
1746 end if;
1747 --
1748 -- end if;
1749 --
1750 end loop;
1751 --
1752 end if;
1753 --
1754 if l_imptd_incm_chg then
1755 --
1756 if l_inelig_lvl_in_pgm <> 'Y' then
1757 ben_det_imputed_income.p_comp_imputed_income(
1758 p_person_id => p_person_id,
1759 p_enrt_mthd_cd => 'E',
1760 p_business_group_id => p_business_group_id,
1761 p_per_in_ler_id => l_per_in_ler_id,
1762 p_effective_date => p_effective_date,
1763 p_ctrlm_fido_call => false,
1764 p_validate => false,
1765 p_no_choice_flag => true);
1766 end if ;
1767 --
1768 end if;
1769 --
1770 hr_utility.set_location ('Leaving '||l_proc,10);
1771 --
1772 end main;
1773 --
1774 end ben_newly_ineligible;