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