4 /*
1 Package Body BEN_DETERMINE_ELCT_CHC_FLX_IMP as
2 /* $Header: benflxii.pkb 120.0 2005/05/28 09:01:21 appldev noship $ */
3 --------------------------------------------------------------------------------
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Determine Electable Choices for Flex Credits and Imputed Income.
12
13 Purpose:
14 Determine Electable Choices for Flex Credits and Imputed Income.
15 Flex Credits are at the pgm, plip, ptip,cmbn_plip,cmbn_ptip,cmbn_ptip_opt,
16 oiplip levels. Imputed income
17 is at the plan level.
18 Called from benmngle.pkb once per person.
19
20 History:
21 Date Who Version What?
22 ---- --- ------- -----
23 7 May 98 Ty Hayden 110.0 Created.
24 16 Jun 98 T Guy 110.1 Removed other exception.
25 23 Jun 98 Ty Hayden 110.2 Changed everything!
26 30 Jun 98 Ty Hayden 110.3 Remove writing of Flex Cred Plan
27 and Imp Inc Plan. They are now
28 written in Enrt Rqmts.
29 09 Oct 98 G Perry 115.2 Fixed error messages and
30 formatted correctly.
31 24 Oct 98 G Perry 115.3 Fixed value of g_package for
32 debugging purposes.
33 29 Oct 98 lmcdonal 115.5 Pass yr-perd-id to create choice.
34 Add per-in-ler, enrt_perd_strt_dt,
35 lf-evt-ocrd-dt and BG as input parms.
36 18 Jan 99 G Perry 115.7 LED V ED
37 29 Jan 99 S Das 115.8 Added codes for cmbn comp objects.
38 09 Mar 99 G Perry 115.9 IS to AS.
39 23 Aug 99 mhoyes 115.10 - Added new trace messages.
40 - Replaced +0s.
41 23 Aug 99 shdas 115.11 Added codes for plip_ordr_num,ptip_ordr_num
42 07 Sep 99 shdas 115.12 Added codes for cmbn ptip opt and oiplip.
43 15 Nov 99 mhoyes 115.13 - Added new trace messages.
44 08 Mar 00 lmcdonal 115.14 Made some performance changes. Fixed
45 OIPLIP so that it would work. Add
46 use of new comp-lvl-cds.
47 31-Mar-00 mmogel 115.15 I changed the message number from
48 91382 to 91832 in the message name
49 BEN_91382_PACKAGE_PARAM_NULL
50 07-Nov-00 mhoyes 115.16 - Referenced electable choice
51 performance APIs.
52 12-Apr-01 ikasire 115.17 bug 1285336 changed the cursor c_bpp6
53 23-Jul-01 ikasire 115.18 bug 1832643 to handle the case where
54 the benefit pool is defined only at
55 program level and the flex credits
56 are defined at different levels
57 13-Aug-01 ikasire 115.19 Bug 1832643 also fixed the above
58 issue for cmbn_ptip and cmbn_plip
59 25-Jan-02 ikasire 115.20 Bug 2189693 Enhancement to allow
60 Flex credits and Benefit Pools at
61 different level see bug for more details.
62 04-Feb-02 pbodla 115.21 Bug 2200783 : c_bpp1 : Removed the
63 pgm_pool_flag check as it do
64 not have any significance and similar
65 check is not available at other levels.
66 05-mar-02 tjesumic 115.22 bug 2251364 pgm_id is stored for all levels
67 of benefit_pool. so in c_pp1 (pgm_level)
68 all other lelvel id are validated for null
69 30-Apr-02 kmahendr 115.23 Added token to message 91832.
70 01-May-02 ikasire 115.24 Bug 2200139 Override Enhancements. Added new parameter
71 p_called_from with 'B' for benmngle and 'O'
72 for override. If called from override, added
73 conditions not to create the duplicate electable
74 choices.added new cursors to determine this at all
75 levels.
76 15-nov-04 kmahendr 115.25 Added parameter p_mode and codes for Unrest.
77 enh
78 */
79 --------------------------------------------------------------------------------
80 g_package varchar2(80) := 'ben_determine_elct_chc_flx_imp';
81 --
82 PROCEDURE main
83 (p_person_id IN number,
84 p_effective_date IN date,
85 p_enrt_perd_strt_dt in date,
86 p_per_in_ler_id in number,
87 p_lf_evt_ocrd_dt in date,
88 p_business_group_id in number,
92 --
89 p_called_from in varchar2 default 'B', -- B for Benmngle, O for Override
90 p_mode in varchar2
91 ) IS
93 l_proc varchar2(80) := g_package||'.main';
94 l_pgm_id number;
95 l_plip_id number;
96 l_ptip_id number;
97 l_pl_id number;
98 l_oipl_id number;
99 l_oiplip_id number;
100 l_elig_per_elctbl_chc_id number;
101 l_object_version_number number;
102 l_bnft_prvdr_pool_id number;
103 l_save_pgm_id number;
104 l_found_pgm varchar2(1) := 'N';
105 l_cmbn_ptip_id number;
106 l_cmbn_plip_id number;
107 l_cmbn_ptip_opt_id number;
108
109 -- Used for PGM
110 cursor c_epe_pgm is
111 select distinct
112 epe.pgm_id
113 from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
114 where p_per_in_ler_id = epe.per_in_ler_id
115 and pgm.pgm_id = epe.pgm_id
116 and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
117 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
118 between pgm.effective_start_date
119 and pgm.effective_end_date
123 -- Used for PLIP and Combo PLIP
120 and nvl(epe.request_id,-1) = fnd_global.conc_request_id
121 order by 1;
122
124 cursor c_epe_plip is
125 select distinct epe.pgm_id, epe.plip_id, epe.plip_ordr_num,
126 epe.pl_id, epe.pl_typ_id, epe.ptip_id, epe.ptip_ordr_num
127 from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
128 where p_per_in_ler_id = epe.per_in_ler_id
129 and epe.plip_id is not null
130 and pgm.pgm_id = epe.pgm_id
131 and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
132 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
133 between pgm.effective_start_date
134 and pgm.effective_end_date
135 and nvl(epe.request_id,-1) = fnd_global.conc_request_id
136 order by 1, 2;
137
138 -- Used for PTIP and Combo PTIP
139 cursor c_epe_ptip is
140 select distinct epe.pgm_id, epe.ptip_id,epe.ptip_ordr_num, epe.pl_typ_id
141 from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
142 where p_per_in_ler_id = epe.per_in_ler_id
143 and epe.ptip_id is not null
144 and pgm.pgm_id = epe.pgm_id
145 and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
146 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
147 between pgm.effective_start_date
148 and pgm.effective_end_date
149 and nvl(epe.request_id,-1) = fnd_global.conc_request_id
150 order by 1, 2;
151
152 -- Used for OIPLIP and Combo PTIP Option
153 cursor c_epe_oipl is
154 select distinct epe.pgm_id,epe.plip_id, epe.ptip_id,epe.oipl_id,epe.oipl_ordr_num,
155 epe.oiplip_id, epe.pl_typ_id, epe.pl_id, epe.plip_ordr_num, epe.ptip_ordr_num
156 from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
157 where p_per_in_ler_id = epe.per_in_ler_id
158 and epe.oiplip_id is not null
159 and pgm.pgm_id = epe.pgm_id
160 and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
161 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
162 between pgm.effective_start_date
163 and pgm.effective_end_date
164 and nvl(epe.request_id,-1) = fnd_global.conc_request_id
165 order by 1, 2,3,4;
166
167 -- Get Flex Credits at PGM level
168 cursor c_abr1 is
169 select abr.acty_base_rt_id
170 from ben_acty_base_rt_f abr
171 where abr.acty_base_rt_stat_cd = 'A'
172 and abr.rt_usg_cd in ('FLXCR')
173 and abr.pgm_id = l_pgm_id
177 l_abr1 c_abr1%rowtype;
174 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
175 between abr.effective_start_date
176 and abr.effective_end_date;
178
179 -- Get Flex Credits at PLIP level
180 cursor c_abr2 is
181 select abr.acty_base_rt_id
182 from ben_acty_base_rt_f abr
183 where abr.acty_base_rt_stat_cd = 'A'
184 and abr.rt_usg_cd in ('FLXCR')
185 and abr.plip_id = l_plip_id
186 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
187 between abr.effective_start_date
188 and abr.effective_end_date
189 and abr.cmbn_plip_id is null;
190 l_abr2 c_abr2%rowtype;
191
192
193 -- Get Flex Credits at PTIP level
194 cursor c_abr3 is
195 select abr.acty_base_rt_id
196 from ben_acty_base_rt_f abr
197 where abr.acty_base_rt_stat_cd = 'A'
198 and abr.rt_usg_cd in ('FLXCR')
199 and abr.ptip_id = l_ptip_id
200 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
201 between abr.effective_start_date
202 and abr.effective_end_date
203 and abr.cmbn_ptip_id is null
204 and abr.cmbn_ptip_opt_id is null;
205
206 l_abr3 c_abr3%rowtype;
207
208
209 -- Get Flex Credits at COMBO PTIP level
210 /*
211 cursor c_abr4 is
212 select abr.acty_base_rt_id
213 from ben_acty_base_rt_f abr
214 where abr.acty_base_rt_stat_cd = 'A'
215 and abr.rt_usg_cd in ('FLXCR')
216 and abr.cmbn_ptip_id = l_cmbn_ptip_id
217 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
218 between abr.effective_start_date
219 and abr.effective_end_date
220 and abr.cmbn_ptip_opt_id is null
221 and abr.ptip_id is null;
222 */
223
224 cursor c_abr4 is
225 select abr.acty_base_rt_id, abr.cmbn_ptip_id
226 from ben_acty_base_rt_f abr,
227 ben_cmbn_ptip_f cptip,
228 ben_ptip_f ptip
229 where abr.acty_base_rt_stat_cd = 'A'
230 and abr.rt_usg_cd in ('FLXCR')
231 --and abr.cmbn_ptip_id = l_cmbn_ptip_id
232 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
233 between abr.effective_start_date
234 and abr.effective_end_date
235 and abr.cmbn_ptip_id = cptip.cmbn_ptip_id
236 and abr.cmbn_ptip_opt_id is null
237 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
238 between cptip.effective_start_date
239 and cptip.effective_end_date
240 and cptip.cmbn_ptip_id = ptip.cmbn_ptip_id
241 and cptip.pgm_id = ptip.pgm_id
242 and ptip.pgm_id = l_pgm_id
243 and ptip.ptip_id = l_ptip_id
244 and abr.ptip_id is null;
245
246 --
247 l_abr4 c_abr4%rowtype;
248
249 -- Get Flex Credits at COMBO PLIP level
250 /*
251 cursor c_abr5 is
252 select abr.acty_base_rt_id
253 from ben_acty_base_rt_f abr
254 where abr.acty_base_rt_stat_cd = 'A'
255 and abr.rt_usg_cd in ('FLXCR')
256 and abr.cmbn_plip_id = l_cmbn_plip_id
257 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
258 between abr.effective_start_date
259 and abr.effective_end_date;
260 */
261 cursor c_abr5 is
262 select abr.acty_base_rt_id, abr.cmbn_plip_id
263 from ben_acty_base_rt_f abr,
264 ben_plip_f plip,
265 ben_cmbn_plip_f cplip
266 where abr.acty_base_rt_stat_cd = 'A'
267 and abr.rt_usg_cd in ('FLXCR')
268 --and abr.cmbn_plip_id = l_cmbn_plip_id
269 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
270 between abr.effective_start_date
271 and abr.effective_end_date
272 and abr.cmbn_plip_id = cplip.cmbn_plip_id
273 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
274 between cplip.effective_start_date
275 and cplip.effective_end_date
276 and cplip.cmbn_plip_id = plip.cmbn_plip_id
277 and cplip.pgm_id = plip.pgm_id
278 and plip.plip_id = l_plip_id
279 and plip.pgm_id = l_pgm_id ;
280
281 --
282
283 l_abr5 c_abr5%rowtype;
284 /*
285 -- Get Flex Credits at COMBO PTIP OPT level
286 cursor c_abr6 is
287 select abr.acty_base_rt_id
288 from ben_acty_base_rt_f abr
289 where abr.acty_base_rt_stat_cd = 'A'
290 and abr.rt_usg_cd in ('FLXCR')
291 and abr.cmbn_ptip_opt_id = l_cmbn_ptip_opt_id
292 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
293 between abr.effective_start_date
294 and abr.effective_end_date
295 and abr.cmbn_ptip_id is null
296 and abr.ptip_id is null;
297 */
298 -- Bug 1832643 Commented the above code not to look at the
299 -- l_cmbn_ptip_opt_id derived from c_bpp6 since we may have
300 -- cases where benefit pool defined at program level and
301 -- the flex credits defined at different levels.
302 --
303 cursor c_abr6 is
304 select abr.acty_base_rt_id, abr.cmbn_ptip_opt_id
305 from ben_acty_base_rt_f abr,
306 ben_optip_f otp,
307 ben_oipl_f oipl
308 where abr.acty_base_rt_stat_cd = 'A'
309 and abr.rt_usg_cd in ('FLXCR')
310 and abr.cmbn_ptip_opt_id = otp.cmbn_ptip_opt_id
311 and otp.ptip_id = l_ptip_id
312 and otp.pgm_id = l_pgm_id
313 and oipl.oipl_id = l_oipl_id
314 and oipl.opt_id = otp.opt_id
315 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
316 between abr.effective_start_date
317 and abr.effective_end_date
318 and abr.cmbn_ptip_id is null
319 and abr.ptip_id is null;
320
321 l_abr6 c_abr6%rowtype;
322
326 from ben_acty_base_rt_f abr
323 -- Get Flex Credits at OIPLIP level
324 cursor c_abr7 is
325 select abr.acty_base_rt_id
327 where abr.acty_base_rt_stat_cd = 'A'
328 and abr.rt_usg_cd in ('FLXCR')
329 and abr.oiplip_id = l_oiplip_id
330 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
331 between abr.effective_start_date
332 and abr.effective_end_date;
333
334 l_abr7 c_abr7%rowtype;
335
336 -- Process Program Level Flex Credits
337 cursor c_bpp1 is
338 select bpp.bnft_prvdr_pool_id
339 from ben_bnft_prvdr_pool_f bpp
340 where bpp.pgm_id = l_pgm_id
341 --bug 2251364 if the other pool created first
342 --- this will create problem , pgm_id stored for
343 --- all the levels
344 and cmbn_ptip_id is null
345 and cmbn_ptip_opt_id is null
346 and oiplip_id is null
347 and cmbn_plip_id is null
348 and plip_id is null
349 and ptip_id is null
350 --
351 -- Bug 2200783 : Removed as the flag do not have any significance and
352 -- similar check is not available at other levels.
353 --
354 -- and bpp.pgm_pool_flag = 'Y'
355 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
356 between bpp.effective_start_date
357 and bpp.effective_end_date
358 and rownum = 1;
359
360 -- ?? these cursors should probably not look at rows where pgm-flag is on because
361 -- they would have been handled by previous cursor?
362
363 -- Process PLIP Level Flex Credits
364 cursor c_bpp2 is
365 select bpp.bnft_prvdr_pool_id
366 from ben_bnft_prvdr_pool_f bpp
367 where bpp.plip_id = l_plip_id
368 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
369 between bpp.effective_start_date
370 and bpp.effective_end_date
371 and rownum = 1
372 and bpp.oiplip_id is null
373 and bpp.cmbn_plip_id is null;
374
375 -- Process PTIP Level Flex Credits
376 cursor c_bpp3 is
377 select bpp.bnft_prvdr_pool_id
378 from ben_bnft_prvdr_pool_f bpp
379 where bpp.ptip_id = l_ptip_id
380 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
381 between bpp.effective_start_date
382 and bpp.effective_end_date
383 and rownum = 1
384 and bpp.cmbn_ptip_id is null
385 and bpp.cmbn_ptip_opt_id is null;
386
387
388 -- Process Combo PTIP Level Flex Credits
389 cursor c_bpp4 is
390 select bpp.bnft_prvdr_pool_id, bpp.cmbn_ptip_id
391 from ben_bnft_prvdr_pool_f bpp,ben_cmbn_ptip_f cbp,ben_ptip_f ctp
392 where ctp.ptip_id = l_ptip_id
393 and ctp.cmbn_ptip_id = cbp.cmbn_ptip_id
394 and bpp.cmbn_ptip_id = cbp.cmbn_ptip_id
395 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
396 between bpp.effective_start_date
397 and bpp.effective_end_date
398 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
399 between cbp.effective_start_date
400 and cbp.effective_end_date
401 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
402 between ctp.effective_start_date
403 and ctp.effective_end_date
404 and rownum = 1
405 and bpp.ptip_id is null
406 and bpp.cmbn_ptip_opt_id is null;
407
408 -- Process Combo PLIP Level Flex Credits
409 cursor c_bpp5 is
410 select bpp.bnft_prvdr_pool_id, bpp.cmbn_plip_id
411 from ben_bnft_prvdr_pool_f bpp,ben_cmbn_plip_f cpl, ben_plip_f cpp
412 where cpp.plip_id = l_plip_id
413 and cpp.cmbn_plip_id = cpl.cmbn_plip_id
414 and bpp.cmbn_plip_id = cpl.cmbn_plip_id
415 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
416 between bpp.effective_start_date
417 and bpp.effective_end_date
418 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
419 between cpl.effective_start_date
420 and cpl.effective_end_date
421 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
422 between cpp.effective_start_date
423 and cpp.effective_end_date
424 and rownum = 1
425 and bpp.oiplip_id is null
426 and bpp.plip_id is null;
427
428 -- Process Combo PTIP Option Level Flex Credits
429 /* bug 1285336
430 cursor c_bpp6 is
431 select bpp.bnft_prvdr_pool_id, bpp.cmbn_ptip_opt_id
432 from ben_bnft_prvdr_pool_f bpp,ben_cmbn_ptip_opt_f cpt, ben_opt_f opt,
433 ben_oipl_f oipl
434 where oipl.oipl_id = l_oipl_id
435 and opt.opt_id = oipl.opt_id
436 and cpt.ptip_id = l_ptip_id
437 and opt.cmbn_ptip_opt_id = cpt.cmbn_ptip_opt_id
438 and bpp.cmbn_ptip_opt_id = cpt.cmbn_ptip_opt_id
439 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
440 between bpp.effective_start_date
441 and bpp.effective_end_date
442 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
443 between cpt.effective_start_date
444 and cpt.effective_end_date
445 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
446 between opt.effective_start_date
447 and opt.effective_end_date
448 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
449 between oipl.effective_start_date
450 and oipl.effective_end_date
451 and rownum = 1
452 and bpp.ptip_id is null
453 and bpp.cmbn_ptip_id is null;
454 */
455
456 cursor c_bpp6 is
457 select bpp.bnft_prvdr_pool_id,
458 bpp.cmbn_ptip_opt_id
459 from ben_bnft_prvdr_pool_f bpp,
460 ben_cmbn_ptip_opt_f cpt,
461 ben_optip_f otp,
462 ben_oipl_f oipl
466 and otp.cmbn_ptip_opt_id = cpt.cmbn_ptip_opt_id
463 where oipl.oipl_id = l_oipl_id
464 and otp.opt_id = oipl.opt_id
465 and otp.ptip_id = l_ptip_id
467 and bpp.cmbn_ptip_opt_id = cpt.cmbn_ptip_opt_id
468 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
469 between bpp.effective_start_date
470 and bpp.effective_end_date
471 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
472 between cpt.effective_start_date
473 and cpt.effective_end_date
474 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
475 between otp.effective_start_date
476 and otp.effective_end_date
477 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
478 between oipl.effective_start_date
479 and oipl.effective_end_date
480 and rownum = 1
481 and bpp.ptip_id is null
482 and bpp.cmbn_ptip_id is null;
483
484 -- Process OIPLIP Level Flex Credits
485 cursor c_bpp7 is
486 select bpp.bnft_prvdr_pool_id
487 from ben_bnft_prvdr_pool_f bpp
488 where bpp.oiplip_id = l_oiplip_id
489 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
490 between bpp.effective_start_date
491 and bpp.effective_end_date
492 and rownum = 1
493 and bpp.plip_id is null
494 and bpp.cmbn_plip_id is null;
495
496
497 cursor yr_perd_for_pgm (p_pgm_id in number) is
498 select yp.yr_perd_id
499 from ben_yr_perd yp,
500 ben_popl_yr_perd pop
501 where pop.pgm_id = p_pgm_id
502 and pop.yr_perd_id = yp.yr_perd_id
503 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
504 between yp.start_date
505 and yp.end_date
506 and yp.business_group_id = p_business_group_id;
507 --
508 -- Override Cursors
509 cursor c_chk_pgm (c_pgm_id in number) is
510 select 'x'
511 from ben_elig_per_elctbl_chc epe
512 where epe.per_in_ler_id = p_per_in_ler_id
513 and epe.pgm_id = c_pgm_id
514 and epe.comp_lvl_cd = 'PGM'
515 and epe.bnft_prvdr_pool_id is not null ;
516 -- PLIP
517 cursor c_chk_plip (c_plip_id in number) is
518 select 'x'
519 from ben_elig_per_elctbl_chc epe
520 where epe.per_in_ler_id = p_per_in_ler_id
521 and epe.plip_id = c_plip_id
522 and epe.comp_lvl_cd = 'PLIP'
523 and epe.bnft_prvdr_pool_id is not null ;
524 -- 'CPLIP'
525 cursor c_chk_cplip (c_cmbn_plip_id in number) is
526 select 'x'
527 from ben_elig_per_elctbl_chc epe
528 where epe.per_in_ler_id = p_per_in_ler_id
529 and epe.cmbn_plip_id = c_cmbn_plip_id
530 and epe.comp_lvl_cd = 'CPLIP'
531 and epe.bnft_prvdr_pool_id is not null ;
532 -- 'PTIP'
533 cursor c_chk_ptip (c_ptip_id in number) is
534 select 'x'
535 from ben_elig_per_elctbl_chc epe
536 where epe.per_in_ler_id = p_per_in_ler_id
537 and epe.ptip_id = c_ptip_id
538 and epe.comp_lvl_cd = 'PTIP'
539 and epe.bnft_prvdr_pool_id is not null ;
540 -- 'CPTIP'
541 cursor c_chk_cptip (c_cmbn_ptip_id in number) is
542 select 'x'
543 from ben_elig_per_elctbl_chc epe
544 where epe.per_in_ler_id = p_per_in_ler_id
545 and epe.cmbn_ptip_id = c_cmbn_ptip_id
546 and epe.comp_lvl_cd = 'CPTIP'
547 and epe.bnft_prvdr_pool_id is not null ;
548 -- 'OIPLIP'
549 cursor c_chk_oiplip (c_oiplip_id in number) is
550 select 'x'
551 from ben_elig_per_elctbl_chc epe
552 where epe.per_in_ler_id = p_per_in_ler_id
553 and epe.oiplip_id = c_oiplip_id
554 and epe.comp_lvl_cd = 'OIPLIP'
555 and epe.bnft_prvdr_pool_id is not null ;
556 -- 'CPTIPOPT'
557 cursor c_chk_cptipopt (c_cmbn_ptip_opt_id in number) is
558 select 'x'
559 from ben_elig_per_elctbl_chc epe
560 where epe.per_in_ler_id = p_per_in_ler_id
561 and epe.cmbn_ptip_opt_id = c_cmbn_ptip_opt_id
562 and epe.comp_lvl_cd = 'CPTIPOPT'
563 and epe.bnft_prvdr_pool_id is not null ;
564
565 l_yr_perd_id number;
566 type l_cmbn_list is table of number index by binary_integer;
567 l_wrote_cmbn l_cmbn_list;
568 l_num_cmbn number := 0;
569 l_num_ptip_cmbn number := 0;
570 l_num_plip_cmbn number := 0;
571 l_num_optip_cmbn number := 0;
572 l_write_this_one varchar2(1);
573 l_dummy_id number ;
574 l_wrote_ptip_cmbn l_cmbn_list;
575 l_wrote_plip_cmbn l_cmbn_list;
576 l_wrote_optip_cmbn l_cmbn_list;
577 l_exists varchar2(30);
578 l_create_override varchar2(30) := 'N' ;
579 --
580 BEGIN
581 -- This module creates choices from which we want to hang flex credit rates
582 -- We hang flex credit rates of chcs with comp_lvls of:
583 -- PGM, PTIP, PLIP, OIPLIP, CPTIP (Combo ptip), CPLIP, CPTIPOPT (Combo ptip/opt)
584 -- Regular rates should not hang off these choices.
585 hr_utility.set_location('Entering: '||l_proc,10);
586 -- clear out table of combination records that were written.
587 l_wrote_cmbn.delete;
588 if p_effective_date is null then
589 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
590 fnd_message.set_token('PACKAGE',l_proc);
591 fnd_message.set_token('PROC','Flex Credit');
592 fnd_message.set_token('PARAM','p_effective_date');
593 fnd_message.raise_error;
594 elsif p_person_id is null then
595 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
596 fnd_message.set_token('PACKAGE',l_proc);
600 end if;
597 fnd_message.set_token('PROC','Flex Credit');
598 fnd_message.set_token('PARAM','p_person_id');
599 fnd_message.raise_error;
601 ---------------------------------------------------------------------
602 -- Process Program Level Flex Credits
603 ---------------------------------------------------------------------
604 for l_epe_pgm in c_epe_pgm loop
605 -- We found a choice with a flex program type on it.
606 l_found_pgm := 'Y';
607 l_pgm_id := l_epe_pgm.pgm_id;
608 --
609 --Override Changes
610 --
611 l_create_override := 'N' ;
612 if p_called_from = 'O' then
613 open c_chk_pgm (l_pgm_id);
614 fetch c_chk_pgm into l_exists ;
615 if c_chk_pgm%notfound then
616 l_create_override := 'Y' ;
617 end if ;
618 close c_chk_pgm;
619 end if ;
620 --
621 open yr_perd_for_pgm (l_epe_pgm.pgm_id);
622 fetch yr_perd_for_pgm into l_yr_perd_id;
623 close yr_perd_for_pgm;
624 --
625 if nvl(p_called_from,'B') <> 'O'
626 or ( p_called_from = 'O' and l_create_override = 'Y' ) then
627 --
628 -- End Override Changes
629 --
630 open c_bpp1;
631 fetch c_bpp1 into l_bnft_prvdr_pool_id;
632 open c_abr1;
633 fetch c_abr1 into l_abr1;
634 if c_bpp1%found and c_abr1%found then
635 --
636 if p_mode in ('U','R') then
637 --
638 l_elig_per_elctbl_chc_id := null;
639 l_elig_per_elctbl_chc_id := ben_manage_unres_life_events.epe_exists
640 (p_per_in_ler_id => p_per_in_ler_id,
641 p_pgm_id => l_epe_pgm.pgm_id,
642 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
643 p_comp_lvl_cd =>'PGM');
644 --
645 end if;
646 --
647 if l_elig_per_elctbl_chc_id is not null and p_mode in ('U','R') then
648 --
649 ben_manage_unres_life_events.update_elig_per_elctbl_choice
650 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
651 p_elctbl_flag => 'N',
652 p_comp_lvl_cd => 'PGM',
653 p_pgm_id => l_epe_pgm.pgm_id,
654 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
655 p_per_in_ler_id => p_per_in_ler_id,
656 p_yr_perd_id => l_yr_perd_id,
657 p_business_group_id => p_business_group_id,
658 p_effective_date => p_effective_date);
659 else
660 -- Write Program Level Flex Credit Distribution Record
661 --
662 hr_utility.set_location('Write Pgm Lvl Flx Cr Distr Rec ',20);
663 ben_elig_per_elc_chc_api.create_perf_elig_per_elc_chc
664 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
665 p_elctbl_flag => 'N',
666 p_comp_lvl_cd => 'PGM',
667 p_pgm_id => l_epe_pgm.pgm_id,
668 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
669 p_per_in_ler_id => p_per_in_ler_id,
670 p_yr_perd_id => l_yr_perd_id,
671 p_business_group_id => p_business_group_id,
672 p_program_application_id => fnd_global.prog_appl_id,
673 p_program_id => fnd_global.conc_program_id,
674 p_request_id => fnd_global.conc_request_id,
675 p_program_update_date => sysdate,
676 p_object_version_number => l_object_version_number,
677 p_effective_date => p_effective_date);
678 hr_utility.set_location('Dn BEPECAPI_CRE 1: '||l_proc,10);
679 end if;
680 --
681 end if;
682 close c_bpp1;
683 close c_abr1;
684 end if ;
685 end loop;
686
687 hr_utility.set_location('Dn c_epe_pgm ',10);
688
689 -- There is no reason to continue if there are no flex program choices.
690 if l_found_pgm = 'Y' then
691 ---------------------------------------------------------------------
692 -- Process PLIP Level Flex Credits
693 ---------------------------------------------------------------------
694 for l_epe_plip in c_epe_plip loop
695 l_plip_id := l_epe_plip.plip_id;
696 l_ptip_id := l_epe_plip.ptip_id;
697 l_bnft_prvdr_pool_id := null;
698 l_cmbn_plip_id := null;
699 --
700 if l_pgm_id <> l_epe_plip.pgm_id then
701 open yr_perd_for_pgm (l_epe_plip.pgm_id);
702 fetch yr_perd_for_pgm into l_yr_perd_id;
703 close yr_perd_for_pgm;
704 end if;
705 --
706 --Override Changes
707 --
708 l_create_override := 'N' ;
709 if p_called_from = 'O' then
710 open c_chk_plip (l_plip_id);
711 fetch c_chk_plip into l_exists ;
712 if c_chk_plip%notfound then
713 l_create_override := 'Y' ;
714 end if ;
715 close c_chk_plip;
716 end if ;
717 --
718 --
719 if nvl(p_called_from,'B') <> 'O'
720 or ( p_called_from = 'O' and l_create_override = 'Y' ) then
721 --
722 -- End Override Changes
723 -- save the pgm id for the next records compare.
724 l_pgm_id := l_epe_plip.pgm_id;
725 --
726 open c_abr2;
727 fetch c_abr2 into l_abr2;
728 if c_abr2%found then
729 --
730 open c_bpp2;
731 fetch c_bpp2 into l_bnft_prvdr_pool_id ;
732 close c_bpp2;
733 --
734 hr_utility.set_location('PLIP Dn c_bpp2 '||l_bnft_prvdr_pool_id,20);
735 --
736 -- Combination PLIP level
740 -- Look for the Benefit pool at Combination PLIP level
737 --
738 if l_bnft_prvdr_pool_id is null then
739 --
741 --
742 open c_bpp5;
743 fetch c_bpp5 into l_bnft_prvdr_pool_id, l_cmbn_plip_id ;
744 close c_bpp5;
745 --
746 end if;
747 --
748 hr_utility.set_location('PLIP Dn c_bpp5 '||l_bnft_prvdr_pool_id,20);
749 -- Now Find the pool at PTIP level
750 --
751 if l_bnft_prvdr_pool_id is null then
752 --
753 open c_bpp3;
754 fetch c_bpp3 into l_bnft_prvdr_pool_id ;
755 close c_bpp3;
756 --
757 end if;
758 --
759 hr_utility.set_location('PLIP Dn c_bpp3 '||l_bnft_prvdr_pool_id,20);
760 --
761 -- Combination PTIP level
762 --
763 if l_bnft_prvdr_pool_id is null then
764 --
765 -- Look for the Benefit pool at Combination PTIP level
766 --
767 open c_bpp4;
768 fetch c_bpp4 into l_bnft_prvdr_pool_id, l_cmbn_ptip_id ;
769 close c_bpp4;
770 --
771 end if;
772 --
773 hr_utility.set_location('PLIP Dn c_bpp6 '||l_bnft_prvdr_pool_id,20);
774 -- Now Find the pool at Program level
775 --
776 if l_bnft_prvdr_pool_id is null then
777 --
778 open c_bpp1;
779 fetch c_bpp1 into l_bnft_prvdr_pool_id ;
780 close c_bpp1;
781 --
782 end if;
783 --
784 if l_bnft_prvdr_pool_id is not null then
785 --
786 if p_mode in ('U','R') then
787 --
788 l_elig_per_elctbl_chc_id := null;
789 l_elig_per_elctbl_chc_id := ben_manage_unres_life_events.epe_exists
790 (p_per_in_ler_id => p_per_in_ler_id,
791 p_pgm_id => l_epe_plip.pgm_id,
792 p_plip_id => l_epe_plip.plip_id,
793 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
794 p_comp_lvl_cd =>'PLIP');
795 --
796 end if;
797 --
798 if l_elig_per_elctbl_chc_id is not null and p_mode in ('U','R') then
799 --
800 ben_manage_unres_life_events.update_elig_per_elctbl_choice
801 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
802 p_elctbl_flag => 'N',
803 p_comp_lvl_cd => 'PLIP',
804 p_pgm_id => l_epe_plip.pgm_id,
805 p_plip_id => l_epe_plip.plip_id,
806 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
807 p_per_in_ler_id => p_per_in_ler_id,
808 p_yr_perd_id => l_yr_perd_id,
809 p_business_group_id => p_business_group_id,
810 p_effective_date => p_effective_date);
811 else
812 -- Write Plip Level Flex Credit Distribution Record
813 --
814 hr_utility.set_location('Write Plip Lvl Flx Cr Distr Rec '||
815 to_char(l_epe_plip.plip_id),30);
816 ben_elig_per_elc_chc_api.create_perf_elig_per_elc_chc
817 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
818 p_elctbl_flag => 'N',
819 p_comp_lvl_cd => 'PLIP',
820 p_pl_id => l_epe_plip.pl_id,
821 p_pgm_id => l_epe_plip.pgm_id,
822 p_plip_id => l_epe_plip.plip_id,
823 p_ptip_id => l_epe_plip.ptip_id,
824 p_pl_typ_id => l_epe_plip.pl_typ_id,
825 p_plip_ordr_num => l_epe_plip.plip_ordr_num,
826 p_ptip_ordr_num => l_epe_plip.ptip_ordr_num,
827 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
828 p_per_in_ler_id => p_per_in_ler_id,
829 p_yr_perd_id => l_yr_perd_id,
830 p_business_group_id => p_business_group_id,
831 p_program_application_id => fnd_global.prog_appl_id,
832 p_program_id => fnd_global.conc_program_id,
833 p_request_id => fnd_global.conc_request_id,
834 p_program_update_date => sysdate,
835 p_object_version_number => l_object_version_number,
836 p_effective_date => p_effective_date);
837 hr_utility.set_location('Dn BEPECAPI_CRE 2: ',10);
838 end if;
839 --
840 end if;
841 --
842 end if; -- c_abr2 found
843 close c_abr2;
844 end if; -- Override loop
845 end loop;
846 hr_utility.set_location(' Dn PLIP ',10);
847 ---------------------------------------------------------------------
848 -- Process COMBO PLIP Level Flex Credits
849 ---------------------------------------------------------------------
850 l_num_cmbn:=0;
851 l_wrote_cmbn.delete;
852 --
853 for l_epe_plip in c_epe_plip loop
854 --
855 l_plip_id := l_epe_plip.plip_id;
856 l_ptip_id := l_epe_plip.ptip_id;
857 l_cmbn_plip_id := null;
858 l_bnft_prvdr_pool_id := null ;
859 --
860 hr_utility.set_location('l_plip_id '||to_char(l_plip_id),10);
861 --
862 if l_pgm_id <> l_epe_plip.pgm_id then
863 open yr_perd_for_pgm (l_epe_plip.pgm_id);
864 fetch yr_perd_for_pgm into l_yr_perd_id;
865 close yr_perd_for_pgm;
866 end if;
867 -- save the pgm id for the next records compare.
868 l_pgm_id := l_epe_plip.pgm_id;
869 -- Process COMBO PLIP
870 --
871 open c_abr5 ;
872 fetch c_abr5 into l_abr5;
873 --
877 -- Look for the Benefit pool at Combination PLIP level
874 if c_abr5%found then
875 --
876 l_cmbn_plip_id := l_abr5.cmbn_plip_id ;
878 open c_bpp5;
879 fetch c_bpp5 into l_bnft_prvdr_pool_id, l_dummy_id ;
880 close c_bpp5;
881 --
882 hr_utility.set_location('Cmbn PLIP Dn c_bpp5 '||l_bnft_prvdr_pool_id,20);
883 -- Now Find the pool at PTIP level
884 --
885 if l_bnft_prvdr_pool_id is null then
886 --
887 open c_bpp3;
888 fetch c_bpp3 into l_bnft_prvdr_pool_id ;
889 close c_bpp3;
890 --
891 end if;
892 --
893 hr_utility.set_location('Cmbn PLIP Dn c_bpp3 '||l_bnft_prvdr_pool_id,20);
894 --
895 -- Combination PTIP level
896 --
897 if l_bnft_prvdr_pool_id is null then
898 --
899 -- Look for the Benefit pool at Combination PTIP level
900 --
901 open c_bpp4;
902 fetch c_bpp4 into l_bnft_prvdr_pool_id, l_dummy_id ;
903 close c_bpp4;
904 --
905 end if;
906 --
907 hr_utility.set_location('Cmbn PLIP Dn c_bpp4 '||l_bnft_prvdr_pool_id,20);
908 -- Now Find the pool at Program level
909 --
910 if l_bnft_prvdr_pool_id is null then
911 --
912 open c_bpp1;
913 fetch c_bpp1 into l_bnft_prvdr_pool_id ;
914 close c_bpp1;
915 --
916 end if;
917 --
918 hr_utility.set_location('Cmbn PLIP Dn c_bpp1 '||l_bnft_prvdr_pool_id,20);
919 if l_bnft_prvdr_pool_id is not null then
920 --
921 -- Write Cmbn Plip Level Flex Credit Distribution Record
922 --
923 -- save cmbn id so we only write it once.
924 l_write_this_one := 'Y';
925 if l_wrote_cmbn.first is not null then
926 for j in l_wrote_cmbn.first..l_wrote_cmbn.last loop
927 hr_utility.set_location('loop cmbns plips written '||
928 to_char(l_wrote_cmbn(j)),30);
929 if l_wrote_cmbn(j) = l_cmbn_plip_id then
930 l_write_this_one := 'N';
931 end if;
932 end loop;
933 end if;
934
935 if l_write_this_one = 'Y' then
936 hr_utility.set_location('Write Cmbn Plip Lvl Flx Cr Distr Rec '||
937 to_char(l_cmbn_plip_id),30);
938 --Override Changes
939 --
940 l_create_override := 'N' ;
941 if p_called_from = 'O' then
942 open c_chk_cplip (l_cmbn_plip_id);
943 fetch c_chk_cplip into l_exists ;
944 if c_chk_cplip%notfound then
945 l_create_override := 'Y' ;
946 end if ;
947 close c_chk_cplip;
948 end if ;
949 --
950 --
951 if nvl(p_called_from,'B') <> 'O'
952 or ( p_called_from = 'O' and l_create_override = 'Y' ) then
953 --
954 -- End Override Changes
955 l_wrote_cmbn(l_num_cmbn) := l_cmbn_plip_id;
956 l_num_cmbn := l_num_cmbn+1;
957 if p_mode in ('U','R') then
958 --
959 l_elig_per_elctbl_chc_id := null;
960 l_elig_per_elctbl_chc_id := ben_manage_unres_life_events.epe_exists
961 (p_per_in_ler_id => p_per_in_ler_id,
962 p_cmbn_plip_id => l_cmbn_plip_id,
963 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
964 p_comp_lvl_cd =>'CPLIP');
965 --
966 end if;
967 --
968 if l_elig_per_elctbl_chc_id is not null and p_mode in ('U','R') then
969 --
970 ben_manage_unres_life_events.update_elig_per_elctbl_choice
971 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
972 p_elctbl_flag => 'N',
973 p_comp_lvl_cd => 'CPLIP',
974 p_pgm_id => l_epe_plip.pgm_id,
975 p_cmbn_plip_id => l_cmbn_plip_id,
976 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
977 p_per_in_ler_id => p_per_in_ler_id,
978 p_yr_perd_id => l_yr_perd_id,
979 p_business_group_id => p_business_group_id,
980 p_effective_date => p_effective_date);
981 --
982 else
983 ben_elig_per_elc_chc_api.create_perf_elig_per_elc_chc
984 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
985 p_elctbl_flag => 'N',
986 p_comp_lvl_cd => 'CPLIP',
987 p_pl_id => null, --l_abr5.pl_id,
988 p_pgm_id => l_epe_plip.pgm_id,
989 p_plip_id => null, --l_abr5.plip_id, -- all plips in combo
990 p_ptip_id => null, --l_abr5.ptip_id, -- do not have same
991 p_pl_typ_id => null, --l_abr5.pl_typ_id,-- pt_typ_id.
992 p_cmbn_plip_id => l_cmbn_plip_id,
993 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
994 p_per_in_ler_id => p_per_in_ler_id,
995 p_yr_perd_id => l_yr_perd_id,
996 p_business_group_id => p_business_group_id,
997 p_program_application_id => fnd_global.prog_appl_id,
998 p_program_id => fnd_global.conc_program_id,
1002 p_effective_date => p_effective_date);
999 p_request_id => fnd_global.conc_request_id,
1000 p_program_update_date => sysdate,
1001 p_object_version_number => l_object_version_number,
1003 hr_utility.set_location('Dn BEPECAPI_CRE 5: ',10);
1004 end if;
1005 --
1006 end if; -- Override
1007 end if;
1008 end if;
1009 end if;
1010 close c_abr5;
1011 end loop;
1012 hr_utility.set_location('Dn c_epe_plip ',10);
1013 -- clear out table of combination records that were written.
1014 ---------------------------------------------------------------------
1015 -- Process PTIP Level Flex Credits
1016 ---------------------------------------------------------------------
1017 for l_epe_ptip in c_epe_ptip loop
1018 l_ptip_id := l_epe_ptip.ptip_id;
1019 l_bnft_prvdr_pool_id := null;
1020 l_cmbn_ptip_id := null ;
1021 --hr_utility.set_location('pgm_id'||to_char(l_epe_ptip.pgm_id)||
1022 -- ' ptip_id'||to_char(l_epe_ptip.ptip_id),10);
1023 --hr_utility.set_location(' ptip_ordr_num'||to_char(l_epe_ptip.ptip_ordr_num)||
1024 -- ' pl_typ_id'||to_char(l_epe_ptip.pl_typ_id),10);
1025 --
1026 --Override Changes
1027 --
1028 l_create_override := 'N' ;
1029 if p_called_from = 'O' then
1030 open c_chk_ptip (l_ptip_id);
1031 fetch c_chk_ptip into l_exists ;
1032 if c_chk_ptip%notfound then
1033 l_create_override := 'Y' ;
1034 end if ;
1035 close c_chk_ptip;
1036 end if ;
1037 --
1038 if l_pgm_id <> l_epe_ptip.pgm_id then
1039 open yr_perd_for_pgm (l_epe_ptip.pgm_id);
1040 fetch yr_perd_for_pgm into l_yr_perd_id;
1041 close yr_perd_for_pgm;
1042 end if;
1043 --
1044 if nvl(p_called_from,'B') <> 'O'
1045 or ( p_called_from = 'O' and l_create_override = 'Y' ) then
1046 --
1047 -- End Override Changes
1048 l_pgm_id := l_epe_ptip.pgm_id;
1049 open c_abr3;
1050 fetch c_abr3 into l_abr3;
1051 -- If there is a Flex Credit record at this level, then look for the benefit pool at
1052 -- OIPLIP or higher level.A
1053 --
1054 if c_abr3%found then
1055 --
1056 hr_utility.set_location('PTIP abr7 found',20);
1057 -- Now Find the pool at PTIP level
1058 --
1059 --
1060 open c_bpp3;
1061 fetch c_bpp3 into l_bnft_prvdr_pool_id ;
1062 close c_bpp3;
1063 --
1064 hr_utility.set_location('PTIP Dn c_bpp3 '||l_bnft_prvdr_pool_id,20);
1065 --
1066 -- Combination PTIP level
1067 --
1068 if l_bnft_prvdr_pool_id is null then
1069 --
1070 -- Look for the Benefit pool at Combination PTIP level
1071 --
1072 open c_bpp4;
1073 fetch c_bpp4 into l_bnft_prvdr_pool_id, l_cmbn_ptip_id ;
1074 close c_bpp4;
1075 --
1076 end if;
1077 --
1078 hr_utility.set_location('PTIP Dn c_bpp4 '||l_bnft_prvdr_pool_id,20);
1079 -- Now Find the pool at Program level
1080 --
1081 if l_bnft_prvdr_pool_id is null then
1082 --
1083 open c_bpp1;
1084 fetch c_bpp1 into l_bnft_prvdr_pool_id ;
1085 close c_bpp1;
1086 --
1087 end if;
1088 --
1089 if l_bnft_prvdr_pool_id is not null then
1090 --
1091 if p_mode in ('U','R') then
1092 --
1093 l_elig_per_elctbl_chc_id := null;
1094 l_elig_per_elctbl_chc_id := ben_manage_unres_life_events.epe_exists
1095 (p_per_in_ler_id => p_per_in_ler_id,
1096 p_pgm_id => l_epe_ptip.pgm_id,
1097 p_ptip_id => l_epe_ptip.ptip_id,
1098 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1099 p_comp_lvl_cd =>'PTIP');
1100 --
1101 end if;
1102 --
1103 if l_elig_per_elctbl_chc_id is not null and p_mode in ('U','R') then
1104 --
1105 ben_manage_unres_life_events.update_elig_per_elctbl_choice
1106 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
1107 p_elctbl_flag => 'N',
1108 p_comp_lvl_cd => 'PTIP',
1109 p_pgm_id => l_epe_ptip.pgm_id,
1110 p_ptip_id => l_epe_ptip.ptip_id,
1111 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1112 p_per_in_ler_id => p_per_in_ler_id,
1113 p_yr_perd_id => l_yr_perd_id,
1114 p_business_group_id => p_business_group_id,
1115 p_effective_date => p_effective_date);
1116 else
1117 -- Write Ptip Level Flex Credit Distribution Record
1118 --
1119 hr_utility.set_location('Write Ptip Lvl Flx Cr Distr Rec '||
1120 to_char(l_epe_ptip.ptip_id),40);
1121 ben_elig_per_elc_chc_api.create_perf_elig_per_elc_chc
1122 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
1123 p_elctbl_flag => 'N',
1124 p_comp_lvl_cd => 'PTIP',
1125 p_pgm_id => l_epe_ptip.pgm_id,
1126 p_ptip_id => l_epe_ptip.ptip_id,
1127 p_pl_typ_id => l_epe_ptip.pl_typ_id,
1128 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1129 p_per_in_ler_id => p_per_in_ler_id,
1130 p_yr_perd_id => l_yr_perd_id,
1131 p_ptip_ordr_num => l_epe_ptip.ptip_ordr_num,
1132 p_business_group_id => p_business_group_id,
1136 p_program_update_date => sysdate,
1133 p_program_application_id => fnd_global.prog_appl_id,
1134 p_program_id => fnd_global.conc_program_id,
1135 p_request_id => fnd_global.conc_request_id,
1137 p_object_version_number => l_object_version_number,
1138 p_effective_date => p_effective_date);
1139 hr_utility.set_location('Dn BEPECAPI_CRE 3: ',10);
1140 --
1141 end if;
1142 --
1143 end if;
1144 --
1145 end if;
1146 close c_abr3;
1147 --
1148 end if ; -- Override
1149 end loop;
1150 ---------------------------------------------------------------------
1151 -- Process Combo PTIP Level Flex Credits
1152 ---------------------------------------------------------------------
1153 -- clear out table of combination records that were written.
1154 l_num_cmbn:=0;
1155 l_wrote_cmbn.delete;
1156 for l_epe_ptip in c_epe_ptip loop
1157 l_ptip_id := l_epe_ptip.ptip_id;
1158 l_bnft_prvdr_pool_id := null;
1159 l_cmbn_ptip_id := null ;
1160 --
1161 if l_pgm_id <> l_epe_ptip.pgm_id then
1162 open yr_perd_for_pgm (l_epe_ptip.pgm_id);
1163 fetch yr_perd_for_pgm into l_yr_perd_id;
1164 close yr_perd_for_pgm;
1165 end if;
1166 --
1167 open c_abr4;
1168 fetch c_abr4 into l_abr4;
1169 if c_abr4%found then
1170 --
1171 l_cmbn_ptip_id := l_abr4.cmbn_ptip_id ;
1172 -- Look for the Benefit pool at Combination PTIP level
1173 --
1174 --
1175 --Override Changes
1176 --
1177 l_create_override := 'N' ;
1178 if p_called_from = 'O' then
1179 open c_chk_cptip (l_cmbn_ptip_id);
1180 fetch c_chk_cptip into l_exists ;
1181 if c_chk_cptip%notfound then
1182 l_create_override := 'Y' ;
1183 end if ;
1184 close c_chk_cptip;
1185 end if ;
1186 --
1187 --
1188 if nvl(p_called_from,'B') <> 'O'
1189 or ( p_called_from = 'O' and l_create_override = 'Y' ) then
1190 --
1191 -- End Override Changes
1192 open c_bpp4;
1193 fetch c_bpp4 into l_bnft_prvdr_pool_id, l_dummy_id ;
1194 close c_bpp4;
1195 --
1196 hr_utility.set_location('PTIP Dn c_bpp6 '||l_bnft_prvdr_pool_id,20);
1197 -- Now Find the pool at Program level
1198 --
1199 if l_bnft_prvdr_pool_id is null then
1200 --
1201 open c_bpp1;
1202 fetch c_bpp1 into l_bnft_prvdr_pool_id ;
1203 close c_bpp1;
1204 --
1205 end if;
1206 --
1207 --
1208 if l_bnft_prvdr_pool_id is not null then
1209 --
1210 --
1211 -- Write Cmbn Ptip Level Flex Credit Distribution Record
1212 --
1213 -- save cmbn id so we only write it once.
1214 l_write_this_one := 'Y';
1215 if l_wrote_cmbn.first is not null then
1216 for j in l_wrote_cmbn.first..l_wrote_cmbn.last loop
1217 hr_utility.set_location('loop cmbns ptips written '||
1218 to_char(l_wrote_cmbn(j)),30);
1219 if l_wrote_cmbn(j) = l_cmbn_ptip_id then
1220 l_write_this_one := 'N';
1221 end if;
1222 end loop;
1223 end if;
1224
1225 if l_write_this_one = 'Y' then
1226 hr_utility.set_location('Write Cmbn Ptip Lvl Flx Cr Distr Rec '||
1227 to_char(l_cmbn_ptip_id),40);
1228 l_wrote_cmbn(l_num_cmbn) := l_cmbn_ptip_id;
1229 l_num_cmbn := l_num_cmbn+1;
1230 --
1231 if p_mode in ('U','R') then
1232 --
1233 l_elig_per_elctbl_chc_id := null;
1234 l_elig_per_elctbl_chc_id := ben_manage_unres_life_events.epe_exists
1235 (p_per_in_ler_id => p_per_in_ler_id,
1236 p_pgm_id => l_epe_ptip.pgm_id,
1237 p_cmbn_ptip_id => l_cmbn_ptip_id,
1238 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1239 p_comp_lvl_cd =>'CPTIP');
1240 --
1241 end if;
1242 --
1243 if l_elig_per_elctbl_chc_id is not null and p_mode in ('U','R') then
1244 --
1245 ben_manage_unres_life_events.update_elig_per_elctbl_choice
1246 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
1247 p_elctbl_flag => 'N',
1248 p_comp_lvl_cd => 'CPTIP',
1249 p_pgm_id => l_epe_ptip.pgm_id,
1250 p_cmbn_ptip_id => l_cmbn_ptip_id,
1251 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1252 p_per_in_ler_id => p_per_in_ler_id,
1253 p_yr_perd_id => l_yr_perd_id,
1254 p_business_group_id => p_business_group_id,
1255 p_effective_date => p_effective_date);
1256 else
1257 ben_elig_per_elc_chc_api.create_perf_elig_per_elc_chc
1258 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
1259 p_elctbl_flag => 'N',
1260 p_comp_lvl_cd => 'CPTIP',
1261 p_pgm_id => l_epe_ptip.pgm_id,
1262 p_ptip_id => null, --l_abr4.ptip_id,
1263 p_pl_typ_id => null, --l_abr4.pl_typ_id,
1264 p_cmbn_ptip_id => l_cmbn_ptip_id,
1265 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1266 p_per_in_ler_id => p_per_in_ler_id,
1270 p_program_id => fnd_global.conc_program_id,
1267 p_yr_perd_id => l_yr_perd_id,
1268 p_business_group_id => p_business_group_id,
1269 p_program_application_id => fnd_global.prog_appl_id,
1271 p_request_id => fnd_global.conc_request_id,
1272 p_program_update_date => sysdate,
1273 p_object_version_number => l_object_version_number,
1274 p_effective_date => p_effective_date);
1275 hr_utility.set_location('Dn BEPECAPI_CRE 4: ',10);
1276 end if;
1277 --
1278 end if;
1279 end if;
1280 --
1281 end if ; -- Override
1282 end if; -- c_abr4 found
1283 close c_abr4;
1284 --
1285 end loop;
1286 -- clear out table of combination records that were written.
1287 l_num_cmbn:=0;
1288 l_wrote_cmbn.delete;
1289 ---------------------------------------------------------------------
1290 -- Process OIPLIP Level Flex Credits
1291 ---------------------------------------------------------------------
1292 hr_utility.set_location('Start EPE_OIPL loop ',10);
1293 for l_epe_oipl in c_epe_oipl loop
1294 l_oipl_id := l_epe_oipl.oipl_id;
1295 l_ptip_id := l_epe_oipl.ptip_id;
1296 l_oiplip_id := l_epe_oipl.oiplip_id;
1297 l_plip_id := l_epe_oipl.plip_id;
1298 --
1299 l_dummy_id := null ;
1300 l_bnft_prvdr_pool_id := null;
1301 l_cmbn_plip_id := null;
1302 l_cmbn_ptip_id := null ;
1303 --
1304 --
1305 --Override Changes
1306 --
1307 l_create_override := 'N' ;
1308 if p_called_from = 'O' then
1309 open c_chk_oiplip (l_oiplip_id);
1310 fetch c_chk_oiplip into l_exists ;
1311 if c_chk_oiplip%notfound then
1312 l_create_override := 'Y' ;
1313 end if ;
1314 close c_chk_oiplip;
1315 end if ;
1316 --
1317 if l_pgm_id <> l_epe_oipl.pgm_id then
1318 open yr_perd_for_pgm (l_epe_oipl.pgm_id);
1319 fetch yr_perd_for_pgm into l_yr_perd_id;
1320 close yr_perd_for_pgm;
1321 end if;
1322 --
1323 if nvl(p_called_from,'B') <> 'O'
1324 or ( p_called_from = 'O' and l_create_override = 'Y' ) then
1325 --
1326 -- End Override Changes
1327 --
1328 -- save the pgm id for the next records compare.
1329 l_pgm_id := l_epe_oipl.pgm_id;
1330 --
1331 hr_utility.set_location('OIPLIP pgm_id'||to_char(l_pgm_id)||
1332 ' l_oiplip_id'||to_char(l_oiplip_id)||' l_ptip_id'||to_char(l_ptip_id),10);
1333 --
1334 open c_abr7;
1335 fetch c_abr7 into l_abr7;
1336 --
1337 -- If there is a Flex Credit record at this level, then look for the benefit pool at
1338 -- OIPLIP or higher level.A
1339 --
1340 if c_abr7%found then
1341 --
1342 hr_utility.set_location('OIPLIP abr7 found',20);
1343 -- Process OIPLIP
1344 open c_bpp7;
1345 fetch c_bpp7 into l_bnft_prvdr_pool_id; -- get required pool id.
1346 close c_bpp7;
1347 --
1348 -- Now Find the pool at Combination optip level
1349 hr_utility.set_location('OIPLIP Dn c_bpp7 '||l_bnft_prvdr_pool_id,20);
1350 --
1351 if l_bnft_prvdr_pool_id is null then
1352 --
1353 -- Look for the Benefit pool at Combination OPTIP level
1354 --
1355 open c_bpp6;
1356 fetch c_bpp6 into l_bnft_prvdr_pool_id, l_dummy_id ;
1357 close c_bpp6;
1358 --
1359 end if;
1360 hr_utility.set_location('OIPLIP Dn c_bpp6 '||l_bnft_prvdr_pool_id,20);
1361 --
1362 -- Now Find the pool at PLIP level
1363 --
1364 if l_bnft_prvdr_pool_id is null then
1365 --
1366 open c_bpp2;
1367 fetch c_bpp2 into l_bnft_prvdr_pool_id ;
1368 close c_bpp2;
1369 --
1370 end if;
1371 --
1372 hr_utility.set_location('OIPLIP Dn c_bpp2 '||l_bnft_prvdr_pool_id,20);
1373 --
1374 -- Combination PLIP level
1375 --
1376 if l_bnft_prvdr_pool_id is null then
1377 --
1378 -- Look for the Benefit pool at Combination PLIP level
1379 --
1380 open c_bpp5;
1381 fetch c_bpp5 into l_bnft_prvdr_pool_id, l_cmbn_plip_id ;
1382 close c_bpp5;
1383 --
1384 end if;
1385 --
1386 hr_utility.set_location('OIPLIP Dn c_bpp5 '||l_bnft_prvdr_pool_id,20);
1387 -- Now Find the pool at PTIP level
1388 --
1389 if l_bnft_prvdr_pool_id is null then
1390 --
1391 open c_bpp3;
1392 fetch c_bpp3 into l_bnft_prvdr_pool_id ;
1393 close c_bpp3;
1394 --
1395 end if;
1396 --
1397 hr_utility.set_location('OIPLIP Dn c_bpp3 '||l_bnft_prvdr_pool_id,20);
1398 --
1399 -- Combination PTIP level
1400 --
1401 if l_bnft_prvdr_pool_id is null then
1402 --
1403 -- Look for the Benefit pool at Combination PTIP level
1404 --
1405 open c_bpp4;
1406 fetch c_bpp4 into l_bnft_prvdr_pool_id, l_cmbn_ptip_id ;
1407 close c_bpp4;
1408 --
1409 end if;
1410 --
1411 hr_utility.set_location('OIPLIP Dn c_bpp6 '||l_bnft_prvdr_pool_id,20);
1412 -- Now Find the pool at Program level
1413 --
1414 if l_bnft_prvdr_pool_id is null then
1415 --
1416 open c_bpp1;
1420 end if;
1417 fetch c_bpp1 into l_bnft_prvdr_pool_id ;
1418 close c_bpp1;
1419 --
1421 --
1422 hr_utility.set_location('OIPLIP Dn c_bpp1 '||l_bnft_prvdr_pool_id,20);
1423 --
1424 if l_bnft_prvdr_pool_id is not null then
1425 --
1426 if p_mode in ('U','R') then
1427 --
1428 l_elig_per_elctbl_chc_id := null;
1429 l_elig_per_elctbl_chc_id := ben_manage_unres_life_events.epe_exists
1430 (p_per_in_ler_id => p_per_in_ler_id,
1431 p_pgm_id => l_epe_oipl.pgm_id,
1432 p_oiplip_id => l_epe_oipl.oiplip_id,
1433 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1434 p_comp_lvl_cd =>'OIPLIP');
1435 --
1436 end if;
1437 --
1438 if l_elig_per_elctbl_chc_id is not null and p_mode in ('U','R') then
1439 --
1440 ben_manage_unres_life_events.update_elig_per_elctbl_choice
1441 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
1442 p_elctbl_flag => 'N',
1443 p_comp_lvl_cd => 'OIPLIP',
1444 p_pgm_id => l_epe_oipl.pgm_id,
1445 p_oiplip_id => l_epe_oipl.oiplip_id,
1446 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1447 p_per_in_ler_id => p_per_in_ler_id,
1448 p_yr_perd_id => l_yr_perd_id,
1449 p_business_group_id => p_business_group_id,
1450 p_effective_date => p_effective_date);
1451 else
1452 hr_utility.set_location('BP fount at bpid '||l_bnft_prvdr_pool_id ,30);
1453 ben_elig_per_elc_chc_api.create_perf_elig_per_elc_chc
1454 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
1455 p_elctbl_flag => 'N',
1456 p_comp_lvl_cd => 'OIPLIP',
1457 p_pgm_id => l_epe_oipl.pgm_id,
1458 p_ptip_id => l_ptip_id,
1459 p_pl_typ_id => l_epe_oipl.pl_typ_id,
1460 p_oipl_id => l_oipl_id,
1461 p_plip_id => l_epe_oipl.plip_id,
1462 p_pl_id => l_epe_oipl.pl_id,
1463 p_oiplip_id => l_epe_oipl.oiplip_id,
1464 p_oipl_ordr_num => l_epe_oipl.oipl_ordr_num,
1465 p_plip_ordr_num => l_epe_oipl.plip_ordr_num,
1466 p_ptip_ordr_num => l_epe_oipl.ptip_ordr_num,
1467 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1468 p_per_in_ler_id => p_per_in_ler_id,
1469 p_yr_perd_id => l_yr_perd_id,
1470 p_business_group_id => p_business_group_id,
1471 p_program_application_id => fnd_global.prog_appl_id,
1472 p_program_id => fnd_global.conc_program_id,
1473 p_request_id => fnd_global.conc_request_id,
1474 p_program_update_date => sysdate,
1475 p_object_version_number => l_object_version_number,
1476 p_effective_date => p_effective_date);
1477 --
1478 end if;
1479 --
1480 --
1481 end if;
1482 end if; -- c_abr7 found
1483 --
1484 close c_abr7;
1485 --
1486 end if ; -- Override
1487 end loop;
1488 ---------------------------------------------------------------------
1489 -- Process Combo PTIP Option Level Flex Credits
1490 ---------------------------------------------------------------------
1491 hr_utility.set_location('Start EPE_COPTIP loop ',10);
1492 -- clear out table of combination records that were written.
1493 l_num_cmbn:=0;
1494 l_wrote_cmbn.delete;
1495 for l_epe_oipl in c_epe_oipl loop
1496 --
1497 l_oipl_id := l_epe_oipl.oipl_id;
1498 l_ptip_id := l_epe_oipl.ptip_id;
1499 l_oiplip_id := l_epe_oipl.oiplip_id;
1500 l_plip_id := l_epe_oipl.plip_id;
1501 --
1502 l_dummy_id := null ;
1503 l_cmbn_ptip_opt_id := null;
1504 l_bnft_prvdr_pool_id := null;
1505 l_cmbn_plip_id := null;
1506 l_cmbn_ptip_id := null ;
1507 --
1508 hr_utility.set_location('OIPLIP pgm_id'||to_char(l_pgm_id)||
1509 ' l_oiplip_id'||to_char(l_oiplip_id)||' l_ptip_id'||to_char(l_ptip_id),10);
1510 --
1511 if l_pgm_id <> l_epe_oipl.pgm_id then
1512 open yr_perd_for_pgm (l_epe_oipl.pgm_id);
1513 fetch yr_perd_for_pgm into l_yr_perd_id;
1514 close yr_perd_for_pgm;
1515 end if;
1516 -- save the pgm id for the next records compare.
1517 l_pgm_id := l_epe_oipl.pgm_id;
1518 --
1519 open c_abr6;
1520 fetch c_abr6 into l_abr6;
1521 --
1522 if c_abr6%found then
1523 --
1524 l_cmbn_ptip_opt_id := l_abr6.cmbn_ptip_opt_id ;
1525 hr_utility.set_location('COPTIP found c_abr6 cmbnptipoptid '||l_cmbn_ptip_opt_id,10);
1526 --
1527 --
1528 --Override Changes
1529 --
1530 l_create_override := 'N' ;
1531 if p_called_from = 'O' then
1532 open c_chk_cptipopt (l_cmbn_ptip_opt_id);
1533 fetch c_chk_cptipopt into l_exists ;
1534 if c_chk_cptipopt%notfound then
1535 l_create_override := 'Y' ;
1536 end if ;
1537 close c_chk_cptipopt;
1538 end if ;
1539 --
1540 --
1541 if nvl(p_called_from,'B') <> 'O'
1542 or ( p_called_from = 'O' and l_create_override = 'Y' ) then
1543 --
1544 -- End Override Changes
1545 --
1546 -- Look for the Benefit pool at Combination OPTIP level
1547 --
1548 open c_bpp6;
1552 hr_utility.set_location('COPTIP Dn c_bpp6 '||l_bnft_prvdr_pool_id,20);
1549 fetch c_bpp6 into l_bnft_prvdr_pool_id, l_dummy_id ;
1550 close c_bpp6;
1551 --
1553 -- Now Find the pool at PLIP level
1554 --
1555 if l_bnft_prvdr_pool_id is null then
1556 --
1557 open c_bpp2;
1558 fetch c_bpp2 into l_bnft_prvdr_pool_id ;
1559 close c_bpp2;
1560 --
1561 end if;
1562 hr_utility.set_location('COPTIP Dn c_bpp2 '||l_bnft_prvdr_pool_id,20);
1563 -- Combination PLIP level
1564 --
1565 if l_bnft_prvdr_pool_id is null then
1566 --
1567 -- Look for the Benefit pool at Combination PLIP level
1568 --
1569 open c_bpp5;
1570 fetch c_bpp5 into l_bnft_prvdr_pool_id, l_cmbn_plip_id ;
1571 close c_bpp5;
1572 --
1573 end if;
1574 hr_utility.set_location('COPTIP Dn c_bpp5 '||l_bnft_prvdr_pool_id,20);
1575 -- Now Find the pool at PTIP level
1576 --
1577 if l_bnft_prvdr_pool_id is null then
1578 --
1579 open c_bpp3;
1580 fetch c_bpp3 into l_bnft_prvdr_pool_id ;
1581 close c_bpp3;
1582 --
1583 end if;
1584 hr_utility.set_location('COPTIP Dn c_bpp3 '||l_bnft_prvdr_pool_id,20);
1585 --
1586 -- Combination PTIP level
1587 --
1588 if l_bnft_prvdr_pool_id is null then
1589 --
1590 -- Look for the Benefit pool at Combination PTIP level
1591 --
1592 open c_bpp4;
1593 fetch c_bpp4 into l_bnft_prvdr_pool_id, l_cmbn_ptip_id ;
1594 close c_bpp4;
1595 --
1596 end if;
1597 hr_utility.set_location('COPTIP Dn c_bpp4 '||l_bnft_prvdr_pool_id,20);
1598 --
1599 -- Now Find the pool at Program level
1600 --
1601 if l_bnft_prvdr_pool_id is null then
1602 --
1603 open c_bpp1;
1604 fetch c_bpp1 into l_bnft_prvdr_pool_id ;
1605 close c_bpp1;
1606 --
1607 end if;
1608 hr_utility.set_location('COPTIP Dn c_bpp1 '||l_bnft_prvdr_pool_id,20);
1609 -- Process COMBO PTIP Option
1610 if l_bnft_prvdr_pool_id is not null then
1611 --
1612 hr_utility.set_location('writing cmbnptipopt ',19 );
1613 -- save cmbn id so we only write it once.
1614 l_write_this_one := 'Y';
1615 if l_wrote_cmbn.first is not null then
1616 for j in l_wrote_cmbn.first..l_wrote_cmbn.last loop
1617 hr_utility.set_location('loop cmbns ptip/opts written '||
1618 to_char(l_wrote_cmbn(j)),30);
1619 if l_wrote_cmbn(j) = l_cmbn_ptip_opt_id then
1620 l_write_this_one := 'N';
1621 end if;
1622 end loop;
1623 end if;
1624 if l_write_this_one = 'Y' then
1625 hr_utility.set_location('Write Cmbn Ptip Opt Lvl Flx Cr Distr Rec '||
1626 to_char(l_cmbn_ptip_opt_id),30);
1627 l_wrote_cmbn(l_num_cmbn) := l_cmbn_ptip_opt_id;
1628 l_num_cmbn := l_num_cmbn+1;
1629 --
1630
1631 if p_mode in ('U','R') then
1632 --
1633 l_elig_per_elctbl_chc_id := null;
1634 l_elig_per_elctbl_chc_id := ben_manage_unres_life_events.epe_exists
1635 (p_per_in_ler_id => p_per_in_ler_id,
1636 p_pgm_id => l_epe_oipl.pgm_id,
1637 p_cmbn_ptip_opt_id => l_cmbn_ptip_opt_id,
1638 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1639 p_comp_lvl_cd =>'CPTIPOPT');
1640 --
1641 end if;
1642 --
1643 if l_elig_per_elctbl_chc_id is not null and p_mode in ('U','R') then
1644 --
1645 ben_manage_unres_life_events.update_elig_per_elctbl_choice
1646 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
1647 p_elctbl_flag => 'N',
1648 p_comp_lvl_cd => 'CPTIPOPT',
1649 p_pgm_id => l_epe_oipl.pgm_id,
1650 p_cmbn_ptip_opt_id => l_cmbn_ptip_opt_id,
1651 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1652 p_per_in_ler_id => p_per_in_ler_id,
1653 p_yr_perd_id => l_yr_perd_id,
1654 p_business_group_id => p_business_group_id,
1655 p_effective_date => p_effective_date);
1656 else
1657 ben_elig_per_elc_chc_api.create_perf_elig_per_elc_chc
1658 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
1659 p_elctbl_flag => 'N',
1660 p_comp_lvl_cd => 'CPTIPOPT',
1661 p_pgm_id => l_epe_oipl.pgm_id,
1662 p_ptip_id => l_ptip_id,
1663 p_pl_id => l_epe_oipl.pl_id,
1664 p_pl_typ_id => l_epe_oipl.pl_typ_id,
1665 p_oipl_id => l_epe_oipl.oipl_id,
1666 p_cmbn_ptip_opt_id => l_cmbn_ptip_opt_id,
1667 p_oipl_ordr_num => l_epe_oipl.oipl_ordr_num,
1668 p_plip_ordr_num => l_epe_oipl.plip_ordr_num,
1669 p_ptip_ordr_num => l_epe_oipl.ptip_ordr_num,
1670 p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
1671 p_per_in_ler_id => p_per_in_ler_id,
1672 p_yr_perd_id => l_yr_perd_id,
1673 p_business_group_id => p_business_group_id,
1674 p_program_application_id => fnd_global.prog_appl_id,
1678 p_object_version_number => l_object_version_number,
1675 p_program_id => fnd_global.conc_program_id,
1676 p_request_id => fnd_global.conc_request_id,
1677 p_program_update_date => sysdate,
1679 p_effective_date => p_effective_date);
1680 hr_utility.set_location('Dn BEPECAPI_CRE 6: ',10);
1681 --
1682 end if;
1683 --
1684 end if;
1685 --
1686 end if;
1687 --
1688 end if ; -- Override
1689 end if;
1690 close c_abr6;
1691 end loop;
1692 hr_utility.set_location('Dn c_epe_oipl ',10);
1693 end if;
1694
1695
1696 hr_utility.set_location('Leaving: '||l_proc,90);
1697 --
1698 end main;
1699 --
1700 end ben_determine_elct_chc_flx_imp;
[Home] [Help]