[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_CHC_CTFN
Source
4 /*
1 Package Body BEN_DETERMINE_CHC_CTFN as
2 /* $Header: benchctf.pkb 120.1.12010000.2 2009/05/29 05:32:30 sallumwa ship $ */
3 --------------------------------------------------------------------------------
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Determine choice certifications.
12 Purpose:
13 This process determines what certifications are necessary for an election and
14 then writes them to elctbl_chc_ctfn.
15 History:
16 Date Who Version What?
17 ---- --- ------- -----
18 10 Feb 99 T Guy 115.0 Created.
19 26 Feb 99 T Guy 115.1 Removed control m's
20 29 Mar 99 T Guy 115.2 Fixed cursors to use
21 local var's instead of referencing
22 for loop cursor
23 31 Mar 99 T Guy 115.3 Added checking for crntly enrolled
24 if so then do not create ctfn's
25 changed ben_per_in_ler_f to a non
26 date tracked table and fixed approp.
27 cursors
28 13 Apr 99 T Guy 115.4 Fixed ben_elig_per_elctbl_chc api
29 call
30 28 Apr 99 Shdas 115.5 Added more contexts to rule calls.
31 30 Apr 99 lmcdonal 115.6 Add check for per-in-ler status.
32 04 May 99 Shdas 115.7 Added jurisdiction code.
33 14 May 99 T Guy 115.8 is to as
34 15 Jul 99 mhoyes 115.9 - Added new trace messages.
35 - Replaced all +0s
36 20-JUL-99 Gperry 115.10 genutils -> benutils package
37 rename.
38 20-JUL-99 mhoyes 115.11 Added new trace messages.
39 30-AUG-99 tguy 115.12 fixed choice object version
40 number problem in get_ecf_ctfn
41 01-Sep-99 tguy 115.13 fixed choice object version
42 number for get_ler_ctfns
43 07-Sep-99 tguy 115.14 fixed call to pay_mag_utils.
44 lookup_jurisdiction_code
45 02-Nov-99 maagrawa 115.15 Modified to write enrt ctfns
46 for level jumping restrictions.
50 18-Nov-99 mhoyes 115.17 - Added new trace messages.
47 Major changes in the package
48 structure.
49 15-Nov-99 mhoyes 115.16 - Added new trace messages.
51 18-Nov-99 gperry 115.18 Fixed error messages.
52 18-Nov-99 gperry 115.19 p_elig_per_elctbl_chc_id passed to
53 formula.
54 24-Jan-00 maagrawa 115.20 Create certification defined at
55 life event level and the comp.
56 object level. Do not use exclude
57 flag at life event level.
58 31-Mar-00 mmogel 115.21 Changed the message number in the
59 message name BEN_91382_PACKAGE_PARAM_
60 NULL from 91382 to 91832
61 06-APR-00 pbodla 115.22 - Bug 3294/1096790 When formula called
62 in write_ctfn enrt_ctfn_typ_cd passed
63 as context. To access DBI's on
64 ben_elctbl_chc_ctfn.
65 09-May-00 lmcdonal 115.23 If a choice already has the ctfn type
66 attached, don't write another one.
67 14-May-00 gperry 115.24 Replaced header wiped by previous
68 version.
69 05-Jun-00 stee 115.25 Change to process one electable
70 choice at a time. Previously, it
71 was called after all choices were
72 created. WWBug 1308629.
73 07-AUG-00 Tmathers 115.26 moved header 1 line wwbug 1374473.
74 30-AUG-00 stee 115.27 Backport of 115.24 with wwbug
75 1374473 fix. wwbug 1391217.
76 30-AUG-00 stee 115.28 Leapfrog of 115.26. wwbug 1391217.
77 24-OCT-00 gperry 115.29 Write certficications for all
78 coverages that break max wout cert
79 value. Fixes WWBUG 1427477.
80 07-Nov-00 mhoyes 115.30 - Phased out main.c_epe.
81 - Referenced comp object loop.
82 21-NOV-00 jcarpent 115.31 - Close cursor missing.
83 27-Aug-01 pbodla 115.32 - bug:1949361 jurisdiction code is
84 derived inside benutils.formula.
85 30-Apr-02 kmahendr 115.33 - Added token to message 91832.
86 08-Jun-02 pabodla 115.34 Do not select the contingent worker
87 assignment when assignment data is
88 fetched.
89 19-AUg-04 kmahendr 115.35 Optional certification changes
90 15-nov-04 kmahendr 115.36 Unrest. enh changes
91 21-feb-05 kmahendr 115.37 Bug#4198774 - mode checked for ctfn
92 28-Feb-05 kmahendr 115.38 Bug#4175303 - certification is written only
93 for one level
94 12 Sep 05 ikasire 115.40 Added new procedure update_susp_if_ctfn_flag
95 29-May-09 sallumwa 115.41 Bug 7701140 : Initialized the dummy variables
96 in the electbl choice cert loop.
97
98 */
99 -----------------------------------------------------------------------------------
100 --
101 -- Globals
102 --
103 g_package varchar2(80) := 'ben_determine_chc_ctfn';
104 --
105 g_ctfn_created boolean := false;
106 g_mode varchar2(1);
107 --
108 ----------------------------------------------------------------
109 --
110 -- Write ELCTBL_CHC_CTFN records
111 --
112 ----------------------------------------------------------------
113 procedure write_ctfn(p_elig_per_elctbl_chc_id in number,
114 p_enrt_bnft_id in number default null,
115 p_enrt_ctfn_typ_cd in varchar2,
116 p_rqd_flag in varchar2,
117 p_ctfn_rqd_when_rl in number,
118 p_business_group_id in number,
119 p_effective_date in date,
120 p_assignment_id in number,
121 p_organization_id in number,
122 p_jurisdiction_code in varchar2,
123 p_pgm_id in number,
124 p_pl_id in number,
125 p_pl_typ_id in number,
126 p_opt_id in number,
127 p_ler_id in number,
128 p_susp_if_ctfn_not_prvd_flag in varchar2 default 'Y',
129 p_ctfn_determine_cd in varchar2 default null,
130 p_mode in varchar2 ) is
131 --
132 l_package varchar2(80) := g_package||'.write_ctfn ';
133 l_ler_ctfn_rqd ff_exec.outputs_t;
134 l_elctbl_chc_ctfn_id number;
135 l_object_version_number number;
136 l_write_ctfn boolean := false;
137
138 cursor c1 is
139 select 'x'
140 from ben_elctbl_chc_ctfn
141 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
142 and enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd
143 and nvl(enrt_bnft_id,-1) = nvl(p_enrt_bnft_id,-1);
144 l_dummy varchar2(1);
145
146 Begin
147
148 hr_utility.set_location ('Entering '||l_package,5);
149 hr_utility.set_location ('p_elig_per_elctbl_chc_id '||
153 hr_utility.set_location ('p_enrt_ctfn_typ_cd '||
150 to_char(p_elig_per_elctbl_chc_id),5);
151 hr_utility.set_location ('p_enrt_bnft_id '||
152 to_char(p_enrt_bnft_id),5);
154 p_enrt_ctfn_typ_cd,5);
155
156 -- if this certificaion type cd has already been attached to the choice,
157 -- don't attach another one. This prevents problems with bad plan
158 -- design setup.
159 open c1;
160 fetch c1 into l_dummy;
161 if c1%FOUND and p_mode not in ('U','R') then
162 hr_utility.set_location ('found ctfn ',5);
163 close c1;
164 else
165 close c1;
166 l_write_ctfn := false;
167
168 if p_ctfn_rqd_when_rl is not null then
169
170 l_ler_ctfn_rqd := benutils.formula
171 (p_formula_id => p_ctfn_rqd_when_rl,
172 p_effective_date => p_effective_date,
173 p_business_group_id => p_business_group_id,
174 p_assignment_id => p_assignment_id,
175 p_organization_id => p_organization_id,
176 p_pgm_id => p_pgm_id,
177 p_pl_id => p_pl_id,
178 p_pl_typ_id => p_pl_typ_id,
179 p_opt_id => p_opt_id,
180 p_ler_id => p_ler_id,
181 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
182 p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd,
183 p_jurisdiction_code => p_jurisdiction_code);
184
185 if l_ler_ctfn_rqd.exists(1) and
186 l_ler_ctfn_rqd(l_ler_ctfn_rqd.first).value = 'Y' then
187 l_write_ctfn := true;
188 end if;
189
190 else
191 l_write_ctfn := true;
192 end if;
193
194 if l_write_ctfn then
195
196 -- This global variable used to determine whether to update the
197 -- ctfn_rqd_flag on the choice.
198 --
199 g_ctfn_created := true;
200 --
201 if p_mode in ('U','R') then
202 --
203 l_elctbl_chc_ctfn_id := ben_manage_unres_life_events.ecc_exists
204 ( p_ELIG_PER_ELCTBL_CHC_ID =>p_elig_per_elctbl_chc_id
205 ,p_enrt_bnft_id =>p_enrt_bnft_id
206 ,p_ENRT_CTFN_TYP_CD =>p_enrt_ctfn_typ_cd
207 );
208 end if;
209 if l_elctbl_chc_ctfn_id is not null then
210 --
211 ben_manage_unres_life_events.update_enrt_ctfn
212 (p_elctbl_chc_ctfn_id => l_elctbl_chc_ctfn_id,
213 p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd,
214 p_rqd_flag => p_rqd_flag,
215 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
216 p_enrt_bnft_id => p_enrt_bnft_id,
217 p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag,
218 p_ctfn_determine_cd => p_ctfn_determine_cd,
219 p_business_group_id => p_business_group_id,
220 p_object_version_number => l_object_version_number,
221 p_effective_date => p_effective_date,
222 p_request_id => fnd_global.conc_request_id,
223 p_program_application_id => fnd_global.prog_appl_id,
224 p_program_id => fnd_global.conc_program_id,
225 p_program_update_date => sysdate);
226 --
227 else
228 --
229 ben_eltbl_chc_ctfn_api.create_eltbl_chc_ctfn(
230 p_elctbl_chc_ctfn_id => l_elctbl_chc_ctfn_id,
231 p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd,
232 p_rqd_flag => p_rqd_flag,
233 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
234 p_enrt_bnft_id => p_enrt_bnft_id,
235 p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag,
236 p_ctfn_determine_cd => p_ctfn_determine_cd,
237 p_business_group_id => p_business_group_id,
238 p_object_version_number => l_object_version_number,
239 p_effective_date => p_effective_date,
240 p_request_id => fnd_global.conc_request_id,
241 p_program_application_id => fnd_global.prog_appl_id,
242 p_program_id => fnd_global.conc_program_id,
243 p_program_update_date => sysdate);
244 --
245 end if;
246 --
247 end if;
248 end if;
249
250 hr_utility.set_location ('Leaving '||l_package,10);
251
252 end write_ctfn;
253 --
254 --
255 ----------------------------------------------------------------
256 --
257 -- Create get_ler_ctfns records
258 --
259 ----------------------------------------------------------------
260 procedure get_ler_ctfns(p_ler_rqrs_enrt_ctfn_id in number,
261 p_elig_per_elctbl_chc_id in number,
262 p_business_group_id in number,
263 p_effective_date in date,
264 p_ctfn_rqd_when_rl in number,
265 p_assignment_id in number,
266 p_organization_id in number,
267 p_jurisdiction_code in varchar2,
268 p_pgm_id in number,
269 p_pl_id in number,
270 p_pl_typ_id in number,
271 p_opt_id in number,
275 l_ctfn_rqd ff_exec.outputs_t;
272 p_ler_id in number) is
273 --
274 l_package varchar2(80) := g_package||'.get_ler_ctfns ';
276 l_create_ctfn boolean := false;
277 --
278 cursor c_ctfn is
279 select ctfn.rqd_flag,
280 ctfn.enrt_ctfn_typ_cd,
281 ctfn.ctfn_rqd_when_rl,
282 lre.susp_if_ctfn_not_prvd_flag,
283 lre.ctfn_determine_cd
284 from ben_ler_enrt_ctfn_f ctfn,
285 ben_ler_rqrs_enrt_ctfn_f lre
286 where ctfn.ler_rqrs_enrt_ctfn_id = p_ler_rqrs_enrt_ctfn_id
287 and lre.ler_rqrs_enrt_ctfn_id = ctfn.ler_rqrs_enrt_ctfn_id
288 and p_effective_date between
289 lre.effective_start_date and lre.effective_end_date
290 and ctfn.business_group_id = p_business_group_id
291 and p_effective_date between
292 ctfn.effective_start_date and ctfn.effective_end_date;
293 --
294 begin
295 --
296 hr_utility.set_location ('Entering '||l_package,10);
297 --
298 l_create_ctfn := false;
299 --
300 if p_ctfn_rqd_when_rl is not null then
301 --
302 l_ctfn_rqd := benutils.formula
303 (p_formula_id => p_ctfn_rqd_when_rl,
304 p_effective_date => p_effective_date,
305 p_business_group_id => p_business_group_id,
306 p_assignment_id => p_assignment_id,
307 p_organization_id => p_organization_id,
308 p_pgm_id => p_pgm_id,
309 p_pl_id => p_pl_id,
310 p_pl_typ_id => p_pl_typ_id,
311 p_opt_id => p_opt_id,
312 p_ler_id => p_ler_id,
313 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
314 p_jurisdiction_code => p_jurisdiction_code);
315 --
316 if l_ctfn_rqd(l_ctfn_rqd.first).value = 'Y' then
317 --
318 l_create_ctfn := true;
319 --
320 end if;
321 --
322 else
323 --
324 l_create_ctfn := true;
325 --
326 end if;
327 --
328 if l_create_ctfn then
329 --
330 for l_ctfn in c_ctfn loop
331 --
332 write_ctfn(p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
333 p_enrt_ctfn_typ_cd => l_ctfn.enrt_ctfn_typ_cd,
334 p_rqd_flag => l_ctfn.rqd_flag,
335 p_ctfn_rqd_when_rl => l_ctfn.ctfn_rqd_when_rl,
336 p_business_group_id => p_business_group_id,
337 p_effective_date => p_effective_date,
338 p_assignment_id => p_assignment_id,
339 p_organization_id => p_organization_id,
340 p_jurisdiction_code => p_jurisdiction_code,
341 p_pgm_id => p_pgm_id,
342 p_pl_id => p_pl_id,
343 p_pl_typ_id => p_pl_typ_id,
344 p_opt_id => p_opt_id,
345 p_ler_id => p_ler_id,
346 p_susp_if_ctfn_not_prvd_flag => l_ctfn.susp_if_ctfn_not_prvd_flag,
347 p_ctfn_determine_cd => l_ctfn.ctfn_determine_cd,
348 p_mode => g_mode);
349 --
350 end loop;
351 --
352 end if;
353 --
354 hr_utility.set_location ('Leaving '||l_package,10);
355 --
356 end get_ler_ctfns;
357 --
358 --
359 procedure write_bnft_rstrn_ctfn(p_elig_per_elctbl_chc_id in number,
360 p_pgm_id in number,
361 p_pl_id in number,
362 p_pl_typ_id in number,
363 p_opt_id in number,
364 p_ler_id in number,
365 p_assignment_id in number,
366 p_organization_id in number,
367 p_jurisdiction_code in varchar2,
368 p_business_group_id in number,
369 p_effective_date in date) is
370 --
371 l_package varchar2(80) := g_package||'.write_bnft_rstrn_ctfn ';
372 l_ler_bnft_rstrn_id number := null;
373 l_rstrn_found boolean := false;
374 --
375 cursor c_ler_rstrn is
376 select rstrn.ler_bnft_rstrn_id
377 from ben_ler_bnft_rstrn_f rstrn,
378 ben_pl_f pln
379 where rstrn.pl_id = p_pl_id
380 and rstrn.ler_id = p_ler_id
381 and rstrn.pl_id = pln.pl_id
382 and pln.bnft_or_option_rstrctn_cd = 'OPT'
383 and rstrn.business_group_id = p_business_group_id
384 and p_effective_date between
385 rstrn.effective_start_date and rstrn.effective_end_date
386 and p_effective_date between
387 pln.effective_start_date and pln.effective_end_date;
388 --
389 cursor c_ler_rstrn_ctfn is
390 select ctfn.rqd_flag,
391 ctfn.enrt_ctfn_typ_cd,
392 ctfn.ctfn_rqd_when_rl,
393 lbr.susp_if_ctfn_not_prvd_flag,
394 lbr.ctfn_determine_cd
395 from ben_ler_bnft_rstrn_ctfn_f ctfn,
396 ben_ler_bnft_rstrn_f lbr
397 where ctfn.ler_bnft_rstrn_id = l_ler_bnft_rstrn_id
403 ctfn.effective_start_date and ctfn.effective_end_date;
398 and lbr.ler_bnft_rstrn_id = ctfn.ler_bnft_rstrn_id
399 and p_effective_date between
400 lbr.effective_start_date and lbr.effective_end_date
401 and ctfn.business_group_id = p_business_group_id
402 and p_effective_date between
404 --
405 cursor c_pl_rstrn_ctfn is
406 select ctfn.rqd_flag,
407 ctfn.enrt_ctfn_typ_cd,
408 ctfn.ctfn_rqd_when_rl,
409 pln.susp_if_ctfn_not_prvd_flag,
410 pln.ctfn_determine_cd
411 from ben_bnft_rstrn_ctfn_f ctfn,
412 ben_pl_f pln
413 where pln.pl_id = p_pl_id
414 and pln.bnft_or_option_rstrctn_cd = 'OPT'
415 and pln.business_group_id = p_business_group_id
416 and pln.pl_id = ctfn.pl_id
417 and p_effective_date between
418 pln.effective_start_date and pln.effective_end_date
419 and p_effective_date between
420 ctfn.effective_start_date and ctfn.effective_end_date;
421 --
422 begin
423 --
424 hr_utility.set_location ('Entering '||l_package,10);
425 --
426 open c_ler_rstrn;
427 fetch c_ler_rstrn into l_ler_bnft_rstrn_id;
428 --
429 if c_ler_rstrn%found then
430 --
431 l_rstrn_found := true;
432 --
433 for l_ctfn in c_ler_rstrn_ctfn loop
434 --
435 -- Life Event Level option jumping certifications.
436 --
437 write_ctfn(p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
438 p_enrt_ctfn_typ_cd => l_ctfn.enrt_ctfn_typ_cd,
439 p_rqd_flag => l_ctfn.rqd_flag,
440 p_ctfn_rqd_when_rl => l_ctfn.ctfn_rqd_when_rl,
441 p_business_group_id => p_business_group_id,
442 p_effective_date => p_effective_date,
443 p_assignment_id => p_assignment_id,
444 p_organization_id => p_organization_id,
445 p_jurisdiction_code => p_jurisdiction_code,
446 p_pgm_id => p_pgm_id,
447 p_pl_id => p_pl_id,
448 p_pl_typ_id => p_pl_typ_id,
449 p_opt_id => p_opt_id,
450 p_ler_id => p_ler_id,
451 p_susp_if_ctfn_not_prvd_flag => l_ctfn.susp_if_ctfn_not_prvd_flag,
452 p_ctfn_determine_cd => l_ctfn.ctfn_determine_cd,
453 p_mode => g_mode);
454 --
455 end loop;
456 --
457 end if;
458 --
459 close c_ler_rstrn;
460 --
461 if not l_rstrn_found then
462 --
463 -- Plan Level option jumping certifications.
464 --
465 for l_ctfn in c_pl_rstrn_ctfn loop
466 --
467 hr_utility.set_location ('l_ctfn.enrt_ctfn_typ_cd '||l_ctfn.enrt_ctfn_typ_cd,10);
468
469 write_ctfn(p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
470 p_enrt_ctfn_typ_cd => l_ctfn.enrt_ctfn_typ_cd,
471 p_rqd_flag => l_ctfn.rqd_flag,
472 p_ctfn_rqd_when_rl => l_ctfn.ctfn_rqd_when_rl,
473 p_business_group_id => p_business_group_id,
474 p_effective_date => p_effective_date,
475 p_assignment_id => p_assignment_id,
476 p_organization_id => p_organization_id,
477 p_jurisdiction_code => p_jurisdiction_code,
478 p_pgm_id => p_pgm_id,
479 p_pl_id => p_pl_id,
480 p_pl_typ_id => p_pl_typ_id,
484 p_ctfn_determine_cd => l_ctfn.ctfn_determine_cd,
481 p_opt_id => p_opt_id,
482 p_ler_id => p_ler_id,
483 p_susp_if_ctfn_not_prvd_flag => l_ctfn.susp_if_ctfn_not_prvd_flag,
485 p_mode => g_mode);
486 --
487 end loop;
488 --
489 end if;
490 --
491 hr_utility.set_location ('Leaving '||l_package,10);
492 --
493 end write_bnft_rstrn_ctfn;
494 --
495 --
496 procedure update_ctfn_rqd_flag(p_elig_per_elctbl_chc_id in number,
497 p_ctfn_rqd_flag in varchar2,
498 p_object_version_number in number,
499 p_business_group_id in number,
500 p_effective_date in date) is
501 --
502 l_package varchar2(80) := g_package||'.update_ctfn_rqd_flag ';
503 l_object_version_number number := p_object_version_number;
504 l_ctfn_rqd_flag varchar2(30) := 'N';
505 --
506 begin
507 --
508 hr_utility.set_location ('Entering '||l_package,10);
509 --
510 if g_ctfn_created then
511 --
512 l_ctfn_rqd_flag := 'Y';
513 --
514 end if;
515 --
516 if l_ctfn_rqd_flag <> p_ctfn_rqd_flag then
517 --
518 -- Update the flag, only if it has changed.
519 --
520 ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
521 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
522 p_ctfn_rqd_flag => l_ctfn_rqd_flag,
523 p_object_version_number => l_object_version_number,
524 p_business_group_id => p_business_group_id,
525 p_effective_date => p_effective_date);
526 --
527 end if;
528 --
529 hr_utility.set_location ('Leaving '||l_package,10);
530 --
531 end update_ctfn_rqd_flag;
532 --
533 -------------------------------------------------------------------------
534 --
535 -- driving procedure
536 --
537 -------------------------------------------------------------------------
538 PROCEDURE main(p_effective_date IN date,
539 p_person_id IN number,
540 p_elig_per_elctbl_chc_id IN number,
541 p_mode in varchar2) IS
542 --
543 l_package varchar2(80) := g_package||'.main ';
544 l_found boolean := false;
545 l_oipl_id varchar2(30);
546 l_business_group_id varchar2(30);
547 l_pl_id varchar2(30);
548 l_ler_id varchar2(30);
549 --
550 /*
551 cursor c_epe is
552 select epe.elig_per_elctbl_chc_id,
553 epe.object_version_number,
554 epe.comp_lvl_cd,
555 epe.pgm_id,
556 epe.oipl_id,
557 epe.pl_id,
558 epe.pl_typ_id,
559 oipl.opt_id,
560 epe.business_group_id,
561 epe.ctfn_rqd_flag,
562 pil.person_id,
563 pil.ler_id
564 from ben_elig_per_elctbl_chc epe,
565 ben_per_in_ler pil,
566 ben_oipl_f oipl
567 where pil.per_in_ler_id = epe.per_in_ler_id
568 and epe.crntly_enrd_flag = 'N'
569 and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
570 and epe.oipl_id = oipl.oipl_id(+)
571 and p_effective_date between
572 nvl(oipl.effective_start_date, p_effective_date) and
573 nvl(oipl.effective_end_date, p_effective_date);
574 --
575 l_epe c_epe%rowtype;
576 */
577 --
578 l_epe ben_epe_cache.g_pilepe_inst_row;
579 --
580 cursor c_lre_oipl is
581 select lre.ler_rqrs_enrt_ctfn_id,
582 lre.ctfn_rqd_when_rl
583 from ben_ler_rqrs_enrt_ctfn_f lre
584 where lre.oipl_id = l_oipl_id
585 and lre.ler_id = l_ler_id
586 and lre.business_group_id = l_business_group_id
587 and p_effective_date
588 between lre.effective_start_date
589 and lre.effective_end_date;
590 --
591 cursor c_lre_pl is
592 select lre.ler_rqrs_enrt_ctfn_id,
593 lre.ctfn_rqd_when_rl
594 from ben_ler_rqrs_enrt_ctfn_f lre
595 where lre.pl_id = l_pl_id
596 and lre.ler_id = l_ler_id
597 and lre.business_group_id = l_business_group_id
598 and p_effective_date
599 between lre.effective_start_date
600 and lre.effective_end_date;
601 --
602 l_lre c_lre_pl%rowtype;
603 --
604 cursor c_ecf_oipl is
605 select ecf.enrt_ctfn_typ_cd,
606 ecf.rqd_flag,
607 ecf.ctfn_rqd_when_rl,
608 cop.susp_if_ctfn_not_prvd_flag,
609 cop.ctfn_determine_cd
610 from ben_enrt_ctfn_f ecf,
611 ben_oipl_f cop
612 where ecf.oipl_id = l_oipl_id
613 and cop.oipl_id = ecf.oipl_id
614 and ecf.business_group_id = l_business_group_id
615 and p_effective_date
616 between cop.effective_start_date
617 and cop.effective_end_date
618 and p_effective_date
619 between ecf.effective_start_date
620 and ecf.effective_end_date;
621 --
622 cursor c_ecf_pl is
623 select ecf.enrt_ctfn_typ_cd,
624 ecf.rqd_flag,
625 ecf.ctfn_rqd_when_rl,
626 pln.susp_if_ctfn_not_prvd_flag,
627 pln.ctfn_determine_cd
628 from ben_enrt_ctfn_f ecf,
629 ben_pl_f pln
630 where ecf.pl_id = l_pl_id
631 and pln.pl_id = ecf.pl_id
632 and ecf.business_group_id = l_business_group_id
633 and p_effective_date
634 between pln.effective_start_date
638 and ecf.effective_end_date;
635 and pln.effective_end_date
636 and p_effective_date
637 between ecf.effective_start_date
639 --
640 l_ecf c_ecf_pl%rowtype;
641 --
642 cursor c_asg is
643 select asg.assignment_id,asg.organization_id,loc.region_2
644 from per_all_assignments_f asg,hr_locations_all loc
645 where asg.person_id = p_person_id
646 and asg.assignment_type <> 'C'
647 and asg.primary_flag = 'Y'
648 and asg.location_id = loc.location_id(+)
649 and p_effective_date
650 between asg.effective_start_date
651 and asg.effective_end_date;
652 --
653 l_asg c_asg%rowtype;
654 l_jurisdiction_code varchar2(30) := null;
655 --
656 BEGIN
657 --
658 hr_utility.set_location ('Entering '||l_package,10);
659 --
660 -- Edit to ensure that the input p_effective_date has a value
661 --
662 If p_effective_date is null then
663 --
664 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
665 fnd_message.set_token('PACKAGE',l_package);
666 fnd_message.set_token('PARAM','p_effective_date');
667 fnd_message.set_token('PROC','Certification requirement');
668 fnd_message.raise_error;
669 --
670 elsif p_person_id is null then
671 --
672 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
673 fnd_message.set_token('PACKAGE',l_package);
674 fnd_message.set_token('PARAM','p_person_id');
675 fnd_message.set_token('PROC','Certification requirement');
676 fnd_message.raise_error;
677 --
678 end if;
679 --
680 open c_asg;
681 fetch c_asg into l_asg; -- if not found, we don't care,
682 close c_asg; -- will pass null to formula
683 --
684 g_mode := p_mode;
685 /*
686 Bug 1949361 : Now the l_jurisdiction_code is derived inside benutils.formula.
687 if l_asg.region_2 is not null then
688 --
689 l_jurisdiction_code := pay_mag_utils.lookup_jurisdiction_code
690 (p_state => l_asg.region_2);
691 --
692 end if;
693 */
694 --
695 -- (maagrawa 1/24/2000. As discussed with dwollenb)
696 -- Certifications are now written in the following order.
697 -- 1) If OIPL, create life event level oipl certification.
698 -- 2) If OIPL, Create OIPL level certification.
699 -- 3) If OIPL, and no cert.created in 1) and 2) then go ahead with 4) and 5).
700 -- 4) Create life event level plan certification.
701 -- 5) Create Plan level certification.
702 --
703 if ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id is not null then
704 --
705 l_epe.elig_per_elctbl_chc_id := ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id;
706 l_epe.object_version_number := ben_epe_cache.g_currcobjepe_row.object_version_number;
707 l_epe.pl_id := ben_epe_cache.g_currcobjepe_row.pl_id;
708 l_epe.ler_id := ben_epe_cache.g_currcobjepe_row.ler_id;
709 l_epe.oipl_id := ben_epe_cache.g_currcobjepe_row.oipl_id;
710 l_epe.business_group_id := ben_epe_cache.g_currcobjepe_row.business_group_id;
711 l_epe.comp_lvl_cd := ben_epe_cache.g_currcobjepe_row.comp_lvl_cd;
712 l_epe.pgm_id := ben_epe_cache.g_currcobjepe_row.pgm_id;
713 l_epe.pl_typ_id := ben_epe_cache.g_currcobjepe_row.pl_typ_id;
714 l_epe.opt_id := ben_epe_cache.g_currcobjepe_row.opt_id;
715 l_epe.ctfn_rqd_flag := ben_epe_cache.g_currcobjepe_row.ctfn_rqd_flag;
716 --
717 /*
718 open c_epe;
719 fetch c_epe into l_epe;
720 if c_epe%found then
721 close c_epe;
722 */
723 hr_utility.set_location (l_package||' Start EPE loop ',10);
724 --
725 l_found := false;
726 g_ctfn_created := false;
727 --
728 hr_utility.set_location ('l_ler_id '||l_ler_id,55);
729 hr_utility.set_location ('l_pl_id '||l_pl_id,55);
730 hr_utility.set_location ('l_oipl_id '||l_oipl_id,55);
731 hr_utility.set_location ('l_epe.elig_per_elctbl_chc_id '||
732 l_epe.elig_per_elctbl_chc_id,55);
733 hr_utility.set_location ('l_epe.object_version_number '||
734 l_epe.object_version_number,55);
735 --
736 l_pl_id := l_epe.pl_id;
737 l_ler_id := l_epe.ler_id;
738 l_oipl_id := l_epe.oipl_id;
739 l_business_group_id := l_epe.business_group_id;
740 --
741 If l_epe.comp_lvl_cd = 'OIPL' then
742 --
743 -- process ler rqrd ctfn if found for oipl
744 --
745 hr_utility.set_location ('Entering oipl ',99);
746 --
747 for l_lre in c_lre_oipl loop
748 --
749 hr_utility.set_location ('Entering oipl lre ',99);
750 l_found := true;
751 --
752 get_ler_ctfns
753 (p_ler_rqrs_enrt_ctfn_id => l_lre.ler_rqrs_enrt_ctfn_id ,
754 p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
755 p_business_group_id => l_epe.business_group_id,
756 p_effective_date => p_effective_date,
757 p_ctfn_rqd_when_rl => l_lre.ctfn_rqd_when_rl,
758 p_assignment_id => l_asg.assignment_id,
759 p_organization_id => l_asg.organization_id,
760 p_jurisdiction_code => l_jurisdiction_code,
761 p_pgm_id => l_epe.pgm_id,
762 p_pl_id => l_epe.pl_id,
763 p_pl_typ_id => l_epe.pl_typ_id,
764 p_opt_id => l_epe.opt_id,
768 --
765 p_ler_id => l_epe.ler_id);
766 --
767 end loop;
769 --bug#4175303 - if ler ctfns are written oipl will not be called
770 --
771 if not l_found then
772 for l_ecf in c_ecf_oipl loop
773 --
774 hr_utility.set_location ('Entering oipl ecf',99);
775 l_found := true;
776 --
777 write_ctfn
778 (p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
779 p_enrt_ctfn_typ_cd => l_ecf.enrt_ctfn_typ_cd,
780 p_rqd_flag => l_ecf.rqd_flag,
781 p_ctfn_rqd_when_rl => l_ecf.ctfn_rqd_when_rl,
782 p_business_group_id => l_epe.business_group_id,
783 p_effective_date => p_effective_date,
784 p_assignment_id => l_asg.assignment_id,
785 p_organization_id => l_asg.organization_id,
786 p_jurisdiction_code => l_jurisdiction_code,
787 p_pgm_id => l_epe.pgm_id,
788 p_pl_id => l_epe.pl_id,
789 p_pl_typ_id => l_epe.pl_typ_id,
790 p_opt_id => l_epe.opt_id,
791 p_ler_id => l_epe.ler_id,
792 p_susp_if_ctfn_not_prvd_flag => l_ecf.susp_if_ctfn_not_prvd_flag,
793 p_ctfn_determine_cd => l_ecf.ctfn_determine_cd,
794 p_mode => g_mode);
795 --
796 end loop;
797 --
798 end if;
799 --
800 end if;
801 --
802 hr_utility.set_location (l_package||' EPE CLC CHK ',10);
803 if l_epe.comp_lvl_cd in ('OIPL','PLAN','PLANFC','PLANIMP') then
804 --
805 -- process plan ler ctfns
806 --
807 if not l_found then
808 --
809 for l_lre in c_lre_pl loop
810 --
811 hr_utility.set_location ('Entering pl lre',99);
812 l_found := true;
813 --
814 get_ler_ctfns
815 (p_ler_rqrs_enrt_ctfn_id => l_lre.ler_rqrs_enrt_ctfn_id ,
816 p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
817 p_business_group_id => l_epe.business_group_id,
818 p_effective_date => p_effective_date,
819 p_ctfn_rqd_when_rl => l_lre.ctfn_rqd_when_rl,
820 p_assignment_id => l_asg.assignment_id,
821 p_organization_id => l_asg.organization_id,
822 p_jurisdiction_code => l_jurisdiction_code,
823 p_pgm_id => l_epe.pgm_id,
824 p_pl_id => l_epe.pl_id,
825 p_pl_typ_id => l_epe.pl_typ_id,
826 p_opt_id => l_epe.opt_id,
827 p_ler_id => l_epe.ler_id);
828 --
829 hr_utility.set_location ('Done glerctfns '||l_package,10);
830 --
831 end loop;
832 --
833 --
834 if not l_found then
835 for l_ecf in c_ecf_pl loop
836 --
837 hr_utility.set_location ('Entering pl ecf',99);
838 l_found := true;
839 --
840 write_ctfn
841 (p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
842 p_enrt_ctfn_typ_cd => l_ecf.enrt_ctfn_typ_cd,
843 p_rqd_flag => l_ecf.rqd_flag,
844 p_ctfn_rqd_when_rl => l_ecf.ctfn_rqd_when_rl,
845 p_business_group_id => l_epe.business_group_id,
846 p_effective_date => p_effective_date,
847 p_assignment_id => l_asg.assignment_id,
848 p_organization_id => l_asg.organization_id,
849 p_jurisdiction_code => l_jurisdiction_code,
850 p_pgm_id => l_epe.pgm_id,
851 p_pl_id => l_epe.pl_id,
852 p_pl_typ_id => l_epe.pl_typ_id,
853 p_opt_id => l_epe.opt_id,
854 p_ler_id => l_epe.ler_id,
855 p_susp_if_ctfn_not_prvd_flag => l_ecf.susp_if_ctfn_not_prvd_flag,
856 p_ctfn_determine_cd => l_ecf.ctfn_determine_cd,
857 p_mode => g_mode);
858 --
859 hr_utility.set_location ('Done gecfctfns '||l_package,10);
860 --
861 end loop;
862 --
863 end if;
864 --
865 end if;
866 --
867 end if;
868 --
869 hr_utility.set_location (l_package||' EPE OIPL ID EPE CRF ',10);
870 if l_epe.oipl_id is not null and l_epe.ctfn_rqd_flag = 'Y' then
871 --
872 -- Write level jumping certifications for OIPL's only if
873 -- bendenrr has already found that certification is required
874 -- to jump to this level.
875 --
876 write_bnft_rstrn_ctfn
877 (p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
878 p_pgm_id => l_epe.pgm_id,
879 p_pl_id => l_epe.pl_id,
880 p_pl_typ_id => l_epe.pl_typ_id,
881 p_opt_id => l_epe.opt_id,
882 p_ler_id => l_epe.ler_id,
883 p_assignment_id => l_asg.assignment_id,
884 p_organization_id => l_asg.organization_id,
888 --
885 p_jurisdiction_code => l_jurisdiction_code,
886 p_business_group_id => l_epe.business_group_id,
887 p_effective_date => p_effective_date);
889 end if;
890 --
891 hr_utility.set_location (l_package||' UCRF ',10);
892 update_ctfn_rqd_flag
893 (p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
894 p_ctfn_rqd_flag => l_epe.ctfn_rqd_flag,
895 p_object_version_number => l_epe.object_version_number,
896 p_business_group_id => l_epe.business_group_id,
897 p_effective_date => p_effective_date);
898 --
899 hr_utility.set_location (l_package||' End EPE loop ',10);
900 /*
901 else
902 close c_epe;
903 */
904 end if;
905 --
906 hr_utility.set_location ('Leaving '||l_package,10);
907 --
908 end main;
909 --
910 procedure update_susp_if_ctfn_flag(
911 p_effective_date in date,
912 p_lf_evt_ocrd_dt in date,
913 p_person_id in number,
914 p_per_in_ler_id in number
915 ) IS
916 l_package varchar2(80) := g_package||'.update_susp_if_ctfn_flag ';
917 --
918 cursor c_ecc is
919 select distinct epe.pgm_id,
920 epe.pl_typ_id,
921 epe.pl_id,
922 ecc.enrt_ctfn_typ_cd
923 from ben_elctbl_chc_ctfn ecc,
924 ben_elig_per_elctbl_chc epe
925 where epe.per_in_ler_id = p_per_in_ler_id
926 and epe.elctbl_flag = 'Y'
927 and epe.ctfn_rqd_flag = 'Y'
928 and ecc.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
929 and ecc.enrt_bnft_id is null
930 and ecc.ctfn_determine_cd = 'ENRFT'
931 and ecc.susp_if_ctfn_not_prvd_flag = 'Y'
932 and ecc.rqd_flag = 'Y'
933 order by 1,2,3,4;
934 --
935 cursor c_pl_ctfn ( p_pl_id number,
936 p_per_in_ler_id number,
937 p_effective_date date,
938 p_enrt_ctfn_typ_cd varchar2 ) is
939 select 'Y'
940 from ben_ler_rqrs_enrt_ctfn_f lre,
941 ben_ler_enrt_ctfn_f lec,
942 ben_per_in_ler pil
943 where lre.pl_id = p_pl_id
944 and pil.per_in_ler_id = p_per_in_ler_id
945 and lre.ler_id = pil.ler_id
946 and p_effective_date between lre.effective_start_date
947 and lre.effective_end_date
948 and p_effective_date between lec.effective_start_date
949 and lec.effective_end_date
950 and lec.ler_rqrs_enrt_ctfn_id = lre.ler_rqrs_enrt_ctfn_id
951 and lec.enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd
952 union
953 select 'Y'
954 from ben_enrt_ctfn_f ec
955 where ec.pl_id = p_pl_id
956 and p_effective_date between ec.effective_start_date
957 and ec.effective_end_date
958 and ec.enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd ;
959 --
960 CURSOR c_plan_enrolment_info(p_cvg_dt date,
961 p_person_id number,
962 p_pgm_id number,
963 p_pl_id number) IS
964 SELECT 'Y'
965 FROM ben_prtt_enrt_rslt_f pen
966 WHERE pen.person_id = p_person_id
967 AND pen.sspndd_flag = 'N'
968 AND pen.prtt_enrt_rslt_stat_cd IS NULL
969 AND pen.effective_end_date = hr_api.g_eot
970 AND p_cvg_dt <= pen.enrt_cvg_thru_dt
971 AND pen.enrt_cvg_strt_dt < pen.effective_end_date
972 -- AND pen.oipl_id IS NULL
973 AND p_pl_id = pen.pl_id
974 AND (
975 ( pen.pgm_id = p_pgm_id
976 AND p_pgm_id IS NOT NULL)
977 OR ( pen.pgm_id IS NULL
978 AND p_pgm_id IS NULL))
979 ;
980 --
981 CURSOR c_oipl_enrolment_info(p_cvg_dt date,
982 p_person_id number,
983 p_pgm_id number,
984 p_oipl_id number ) IS
985 SELECT 'Y'
986 FROM ben_prtt_enrt_rslt_f pen
987 WHERE pen.person_id = p_person_id
988 AND pen.sspndd_flag = 'N'
989 AND pen.prtt_enrt_rslt_stat_cd IS NULL
990 AND pen.effective_end_date = hr_api.g_eot
991 AND p_cvg_dt <= pen.enrt_cvg_thru_dt
992 AND pen.enrt_cvg_strt_dt < pen.effective_end_date
993 AND p_oipl_id = pen.oipl_id
994 AND (
995 ( pen.pgm_id = p_pgm_id
996 AND p_pgm_id IS NOT NULL)
997 OR ( pen.pgm_id IS NULL
998 AND p_pgm_id IS NULL))
999 ;
1000 --
1001 cursor c_oipl_ecc(p_pl_id number) is
1002 select epe.elig_per_elctbl_chc_id,
1003 epe.pgm_id,
1004 epe.pl_typ_id,
1005 epe.pl_id,
1006 epe.oipl_id,
1007 ecc.enrt_ctfn_typ_cd,
1008 ecc.elctbl_chc_ctfn_id,
1009 ecc.object_version_number,
1010 ecc.business_group_id
1011 from ben_elctbl_chc_ctfn ecc,
1012 ben_elig_per_elctbl_chc epe
1013 where epe.per_in_ler_id = p_per_in_ler_id
1014 and epe.pl_id = p_pl_id
1015 and epe.elctbl_flag = 'Y'
1016 and epe.ctfn_rqd_flag = 'Y'
1017 and epe.oipl_id IS NOT NULL
1018 and ecc.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1019 and ecc.enrt_bnft_id is null
1020 and ecc.ctfn_determine_cd = 'ENRFT'
1021 and ecc.susp_if_ctfn_not_prvd_flag = 'Y'
1025 select epe.elig_per_elctbl_chc_id,
1022 and ecc.rqd_flag = 'Y' ;
1023 --
1024 cursor c_pl_or_oipl_ecc(p_pl_id number) is
1026 epe.pgm_id,
1027 epe.pl_typ_id,
1028 epe.pl_id,
1029 epe.oipl_id,
1030 ecc.enrt_ctfn_typ_cd,
1031 ecc.elctbl_chc_ctfn_id,
1032 ecc.object_version_number,
1033 ecc.business_group_id
1034 from ben_elctbl_chc_ctfn ecc,
1035 ben_elig_per_elctbl_chc epe
1036 where epe.per_in_ler_id = p_per_in_ler_id
1037 and epe.pl_id = p_pl_id
1038 and epe.elctbl_flag = 'Y'
1039 and epe.ctfn_rqd_flag = 'Y'
1040 and ecc.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1041 and ecc.enrt_bnft_id is null
1042 and ecc.ctfn_determine_cd = 'ENRFT'
1043 and ecc.susp_if_ctfn_not_prvd_flag = 'Y'
1044 and ecc.rqd_flag = 'Y' ;
1045 --
1046 l_oipl_rec c_oipl_ecc%ROWTYPE;
1047 l_ecc_ovn NUMBER(9);
1048 --
1049 l_dummy VARCHAR2(30) := 'N';
1050 l_dummy1 VARCHAR2(30) := 'N';
1051 l_dummy2 VARCHAR2(30) := 'N';
1052 begin
1053 hr_utility.set_location ('Entering '||l_package,10);
1054 FOR l_ecc IN c_ecc LOOP
1055 --
1056 l_dummy := 'N'; --Bug 7701140
1057 l_dummy1 := 'N'; --Bug 7701140
1058 hr_utility.set_location ('l_ecc.pgm_id'||l_ecc.pgm_id,20);
1059 hr_utility.set_location ('l_ecc.pl_id '||l_ecc.pl_id,20);
1060 hr_utility.set_location ('l_ecc.enrt_ctfn_typ_cd '||l_ecc.enrt_ctfn_typ_cd,20);
1061 --
1062 OPEN c_pl_ctfn(l_ecc.pl_id,p_per_in_ler_id,p_effective_date,l_ecc.enrt_ctfn_typ_cd) ;
1063 FETCH c_pl_ctfn INTO l_dummy ;
1064 CLOSE c_pl_ctfn ;
1065 hr_utility.set_location(' l_dummy '||l_dummy,30);
1066 IF l_dummy = 'Y' THEN
1067 -- Plan level certification is setup and now check to see current at the plan level
1068 OPEN c_plan_enrolment_info(p_lf_evt_ocrd_dt,
1069 p_person_id,
1070 l_ecc.pgm_id,
1071 l_ecc.pl_id) ;
1072 FETCH c_plan_enrolment_info INTO l_dummy1 ;
1073 CLOSE c_plan_enrolment_info ;
1074 --Currently enrolled at plan level
1075 hr_utility.set_location(' Plan Level Certs l_dummy1 '||l_dummy1,40);
1076 IF l_dummy1 = 'Y' THEN
1077 --Currently enrolled in this option, so we need to update the flag
1078 OPEN c_pl_or_oipl_ecc(l_ecc.pl_id) ;
1079 loop
1080 fetch c_pl_or_oipl_ecc INTO l_oipl_rec;
1081 if c_pl_or_oipl_ecc%notfound then
1082 close c_pl_or_oipl_ecc ;
1083 exit ;
1084 end if;
1085 --
1086 hr_utility.set_location(' l_oipl_rec.elctbl_chc_ctfn_id '||l_oipl_rec.elctbl_chc_ctfn_id,50);
1087 l_ecc_ovn := l_oipl_rec.object_version_number ;
1088 --
1089 ben_ELTBL_CHC_CTFN_api.update_ELTBL_CHC_CTFN
1090 (p_elctbl_chc_ctfn_id => l_oipl_rec.elctbl_chc_ctfn_id
1091 ,p_susp_if_ctfn_not_prvd_flag => 'N'
1092 ,p_object_version_number => l_ecc_ovn
1093 ,p_effective_date => trunc(p_effective_date)
1094 );
1095 end loop;
1096 --
1097 END IF;
1098 --
1099 ELSE
1100 -- Plan level certification is not setup, so need to see if current only at the option level
1101 hr_utility.set_location('Cert at Option Level',60);
1102 OPEN c_oipl_ecc(l_ecc.pl_id) ;
1103 loop
1104 fetch c_oipl_ecc INTO l_oipl_rec;
1105 if c_oipl_ecc%notfound then
1106 close c_oipl_ecc ;
1107 exit ;
1108 end if;
1109 OPEN c_oipl_enrolment_info(p_lf_evt_ocrd_dt,
1110 p_person_id,
1111 l_ecc.pgm_id,
1112 l_oipl_rec.oipl_id) ;
1113 FETCH c_oipl_enrolment_info INTO l_dummy2 ;
1114 CLOSE c_oipl_enrolment_info ;
1115 hr_utility.set_location(' l_dummy2 '||l_dummy2,70);
1116 IF l_dummy2 = 'Y' THEN
1117 --Currently enrolled in this option, so we need to update the flag
1118 l_ecc_ovn := l_oipl_rec.object_version_number ;
1119 --
1120 hr_utility.set_location(' l_oipl_rec.elctbl_chc_ctfn_id '||l_oipl_rec.elctbl_chc_ctfn_id,80);
1121 ben_ELTBL_CHC_CTFN_api.update_ELTBL_CHC_CTFN
1122 (p_elctbl_chc_ctfn_id => l_oipl_rec.elctbl_chc_ctfn_id
1123 ,p_susp_if_ctfn_not_prvd_flag => 'N'
1124 ,p_object_version_number => l_ecc_ovn
1125 ,p_effective_date => trunc(p_effective_date)
1126 );
1127 --
1128 END IF;
1129 end loop;
1130 --
1131 END IF;
1132 --
1133 END LOOP;
1134 hr_utility.set_location ('Leaving '||l_package,10);
1135 end update_susp_if_ctfn_flag ;
1136 --
1137 end BEN_DETERMINE_CHC_CTFN;