[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_DPNT_ELIGIBILITY
Source
1 PACKAGE BODY ben_determine_dpnt_eligibility as
2 /* $Header: bendepen.pkb 120.13.12010000.1 2008/07/29 12:09:27 appldev ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Dependent Eligibility
13 Purpose
14 This package is used to determine the dependents who may be eligible for
15 an electable choice for a specific participant. It also determines
16 if the electable choice may or may not actually be electable.
17 History
18 Date Who Version What?
19 ---- --- ------- -----
20 09 Apr 98 M Rosen/JM 110.0 Created.
21 03 Jun 98 J Mohapatra Replaced the date calculation
22 with a new procedure call.
23 13 Jun 98 Ty Hayden Added batch Who columns.
24 17 Jun 98 J Mohapatra Added parameters(dpnt_cvg_strt
25 dt_cd/rl) in the call to update
26 api on elec_chc table. Make
27 changes in the hirerchy of
28 picking of cvg strt date/rl.
29 11 Sep 98 maagrawa 115.4 Eligibility to be checked for
30 non-electable choices also.
31 30-OCT-98 G PERRY 115.5 Removed Control m
32 10-DEC-98 lmcdonal 115.6 Do not load dpnt cvg_strt_dt
33 when creating dpnt records that are
34 tied only to a choice and not to a
35 result. Bug 1536.
36 15-DEC-98 stee 115.7 Remove dsgn rqmt check as it
37 is checked in ben_evaluate_dpnt
38 -elg_profiles.
39 20-DEC-98 G PERRY 115.8 Added in info to cache about the
40 covered dependent.
41 22-DEC-98 stee 115.9 If participant has a qmcso, set
42 electable_flag to 'N' for choices
43 that do not allow dependents.
44 28-DEC-98 stee 115.10 Fix c_contact cursor to select
45 the contacts within a date range.
46 02-JAN-99 stee 115.11 Added p_multi_row_actn of false
47 to update_elig_cvrd_dpnt api.
48 18 Jan 99 G Perry 115.12 LED V ED
49 05 Apr 99 mhoyes 115.13 - Un-datetrack of per_in_ler_f changes.
50 - Removed DT restriction from
51 - main/c_per_in_ler
52 30 Apr 99 shdas 115.15 Added contexts to rule calls(genutils.formula)
53 Added a rule call for enrt_cvg_strt_dt_rl.
54 08 May 99 jcarpent 115.16 Check ('VOIDD', 'BCKDT') for pil stat cd
55 06 Jun 99 stee 115.17 Check for datetrack mode before
56 updating dependent.
57 28 Jul 99 tmathers 115.18 genutils to benutils.
58 06 Aug 99 mhoyes 115.19 - Added new trace messages.
59 10 Aug 99 gperry 115.20 Removed global references to
60 g_cache_person.
61 24 Aug 99 maagrawa 115.21 Changes related to breaking of
62 dependents table.
63 26 Aug 99 gperry 115.22 Added benefits assignment call for
64 when employee assignment is null.
65 30 Aug 99 maagrawa 115.23 Added p_dpnt_inelig_rsn_cd to
66 Dependent eligibility process to
67 return ineligible reason.
68 02 Sep 99 maagrawa 115.24 Added HIPAA communication.
69 07 Sep 99 jcarpent 115.25 Removed qmcso check.
70 11 Oct 99 jcarpent 115.26 Added bg_id to update_elig_cvrd_dpn
71 19 Nov 99 pbodla 115.27 Passed p_elig_per_elctbl_chc_id
72 to formula
73 30 Nov 99 lmcdonal 115.28 add per_all_people_f to c_contact
74 cursor to avoid error in bendpelg.
75 03 Jan 00 maagrawa 115.29 Added pl_typ_id to comm. process.
76 07 Jan 00 pbodla 115.30 oabrules.xls says the ler_chg_dpnt_cvg_rl
77 should return Y/N.
78 18 Jan 00 maagrawa 115.31 Removed HIPAA comm. Now called
79 from benmngle.
80 12 Feb 00 maagrawa 115.32 Use the elig_change_dt to
81 calculate cvg_end_dt (1193065).
82 17 Feb 00 jcarpent 115.33 change c_contact cursor to also
83 include covered even if ended.
84 22 Feb 00 jcarpent 115.34 Added hierarchy for ben_dsgn_rqmt
85 10 Mar 00 jcarpent 115.35 Pass cvg_end_dt to update_elig_dpnt
86 31 mar 00 maagrawa 115.36 Pass the cvg_strt_dt to dpnt.
87 eligibilty process (4929).
88 01 Apr 00 stee 115.36 Set a global variable when
89 dependent is found ineligible
90 so a benefit assignment can
91 be written.
92 05 Apr 00 mmogel 115.38 Added tokens to message calls to
93 make the messages more meaningful
94 11 Apr 00 jcarpent 115.39 Handle the case when bendsgel has
95 already end dated the elig covered
96 dependent in the future (5076)
97 25 Apr 00 mhoyes 115.40 - Added trace messages for profiling.
98 01 May 00 pbodla 115.41 - Task 131 : Elig dependent rows are
99 created before creating the electable
100 choice rows. Added procedures main() -
101 created the elig dependent rows,
102 p_upd_egd_with_epe_id()- updates elig
103 dependent rows with electable choice
104 rows.
105 13 May 00 mhoyes 115.42 - Added trace messages for profiling.
106 14 May 00 lmcdonal 115.43 Bug 1193065. Use life event occurred
107 date for elig_strt_dt.
108 15 May 00 mhoyes 115.44 - Called performance API.
109 22 May 00 jcarpent 115.45 - Create elig_dpnts when mnanrd so
110 that they can be carried over by
111 the system. (4981).
112 23 May 00 stee 115.46 - Do not update coverage end date
113 if the dependent was previously
114 ineligible.
115 24 May 00 jcarpent 115.47 - Fix for bug introduced in 115.45
116 29 May 00 mhoyes 115.48 - Called update performance covers
117 for EPEs and EGDs.
118 15 Jun 00 pbodla 115.49 - Removed old main(). as Martin looked
119 at it for performance reasons.
120 - Task 133 : Interim coverage cannot
121 be assigned when in insufficient number
122 of dependents have been designated
123 to a participant enrollment. In this case,
124 the current product will end the
125 participant's current coverage.
126 To have above functionality, if no
127 dependent designation is required if
128 l_mn_dpnts_rqd_num is 0 or
129 l_no_mn_num_dfnd_flag is Y.
130 28 Jun 00 mhoyes 115.50 - Called update performance covers
131 for EPE.
132 - Stored sysdate to local to reduce
133 sysdate references.
134 09 Aug 00 maagrawa 115.51 - If plan/option has decline flag ON,
135 do not create elig_dpnt records.
136 14 Aug 00 gperry 115.52 Fixed WWBUG 1375474.
137 Passed in more contexts to
138 ben_determine_date call.
139 30 Aug 00 tilak 115.53 bug:1390107 region_2 is validate before calling pay_mag_util.
140 lokup_jurisdiction_code
141 07 Nov 00 mhoyes 115.54 - Phased out c_elctbl_chc. Referenced
142 comp object loop electable choice context
143 global.
144 21 NOV 00 rchase 115.55 - Bug 1500945. Update elig_dpnt row
145 when coverage is lost
146 due to ineligibility. Leapfrog
147 based on ver 115.53.
148 21 NOV 00 jcarpent 115.56 - Merged version of 115.54 and 115.55.
149 12 DEC 00 jcarpent 115.57 - Bug 1524099, When elig_dpnt row was
150 not found was assuming eligible.
151 Added nvl(..dpnt_inelig_flag,'N').
152 26 DEC 00 Ikasire 115.58 Bug 1531647 added input values for contact_person_id
153 in the call to ben_determine_date.main procedure
154 05 Jan 01 kmahendr 115.59 Added per_in_ler_id column in the cursor to fetch active
155 life event
156 27 Aug 01 tilak 115.61 bug:1949361 jurisdiction code is
157 derived inside benutils.formula.
158 18 nov 01 tjesumic 115.62 cwb changes
159 20 dec 01 ikasire 115.63 added dbdrv lines
160 22 jan 02 mhoyes 115.64 - Added p_per_in_ler_id and p_opt_id to
161 call to get_elig_dpnt_rec.
162 23 Jan 02 ikasire 115.65 Bug 2189561 dependent eligility profile
163 not recognised when attached at plan level
164 24 jan 02 tjesumic 115.66 2147682 - fixed by jc
165 11 mar 02 mhoyes 115.67 - Tuned get_elig_dpnt_rec.
166 - Modified c_per_in_ler changed alias from
167 pil. to ler.
168 12 jun 02 mhoyes 115.68 - Performance tuning. Bypassed call to
169 get_elig_per_id.
170 31 jul 02 hnarayan 115.69 - bug 1192368 - modified designation level determination
171 in procedure main to first check if it is defined at
172 plan level, then check at program level.
173 03 Oct 02 tjesumic 115.70 # 2508745 To validate the PTIP level the plan type id
174 is validated instead of PTIP id , this allow to validate
175 across the porgram
176 25 Nov-02 tjesumic 115.71 fix of the # 2508745 reverted
177 10 Feb-03 pbodla 115.72 Filter GRADE/STEP life events.
178 18-Mar-04 kmahendr 115.73 bug#3495592 - cursor c_dpnt_exist added to
179 check already created elig_dpnt record
180 27-jul-04 nhunur 115.74 3685120-create elig_dpnt records for unrestricted.
181 03-Aug-04 tjesumic 115.75 3784375 - cursor c_pdp changed to validate the eot
182 03-Aug-04 tjesumic 115.76 fonm changes
183 16-Aug-04 kmahendr 115.77 Bug#3238951 - assign O to dpnt_dsgn_cd if the
184 plan is waive and reversed the fix made for
185 bug#1192368
186 17-Sep-04 pbodla 115.78 iREC : Avoid fetching the iRecruitement
187 life events.
188 15-Nov-04 kmahendr 115.79 Unrest. enh changes
189 14-dec-04 nhunur 115.80 4051409 - Reset variable l_elig_dpnt_id to allow creation
190 of elig_dpnt rows for multiple contacts.
191 07-Apr-05 abparekh 115. 81 Bug 4287999 : Fixed cursor c_contact not to select
192 deceased persons
193 12-May-05 nhunur 115. 82 Bug 4366892 : Fixed cursor c_contact not to select
194 one contact more than once if he has more than 1
195 personal relationship.
196
197 03-Jun-05 kmahendr 115.84 Added additional conditions to cursor c_contact
198 to avoid regression
199 03-Oct-05 kmahendr 115.85 Added mode_cd R to create elig_dpnt rows
200 24-Oct-05 kmahendr 115.86 Bug#4658173 - effective_date is arrived
201 based on pdp row
202 26-Oct-05 bmanyam 115.87 Bug 4692782 - changed c_contact cursor.
203 16-Dec-05 bmanyam 115.88 4697057 - Relationship End-date should
204 be end-date of dependent coverage.
205 04-Feb-06 mhoyes 115.89 - bug4966769 - hr_utility tuning.
206 04-Apr-06 vborkar 115.90 Bug 5127698 : Changes in cursors c_pdp,
207 c_plan_enrolment_info and c_oipl_enrolment_info.
208 17-Apr-06 bmanyam 115.91 5152062 - Changed c_dsgn, such that
209 all desgn.requirement are summed up
210 at a particular level (PL/OIPL/OPT).
211 26-May-06 bmanyam 115.92 5100008 - EGD elig_thru_dt is the
212 date eligibility is lost. Previously the elig_thru_dt
213 was updated with PDP cvg_thru_dt.
214 28-Jun-06 swjain 115.94 5331889 - Added person_id as param in call to
215 benutils.formula for ler_chg_dpnt_cvg_rl in procedure main
216 15-Sep-06 rgajula 115.95 Bug 5529902: In the cursor c_dsgn
217 if mx_dpnts_alwd_num is NULL, consider unlimited designees can be assigned.
218 02-Apr-07 swjain 115.96 Bug 5936849 : Set L_ENV.MODE_CD using benutils
219 if not available through benenvir
220 */
221 --------------------------------------------------------------------------------
222 g_rec benutils.g_batch_dpnt_rec;
223 g_cvg_thru_dt date := null;
224 g_mnanrd_condition boolean:=false;
225 --
226 --
227 --
228 -- Task 131 (pbodla): Elig dependent records are created soon after
229 -- person is found eligible. This procedure is called from
230 -- ben_manage_life_events.process_comp_objects(benmngle.pkb).
231 -- Then electable choice record is created in bendenrr.pkb.
232 -- Essentially elig dependent rows are created before creating
233 -- the electable choice row.
234 -- All elig dependent rows which belongs to a electable choice
235 -- row are stored in g_egd_table, Also g_upd_epe_egd_rec record
236 -- holds the information required to update electable choice row.
237 --
238 procedure main
239 (p_pgm_id in number default null
240 ,p_pl_id in number default null
244 ,p_pl_typ_id in number default null
241 ,p_plip_id in number default null
242 ,p_ptip_id in number default null
243 ,p_oipl_id in number default null
245 ,p_business_group_id in number
246 ,p_person_id in number
247 ,p_effective_date in date
248 ,p_lf_evt_ocrd_dt in date
249 ,p_per_in_ler_id in number default null
250 ,p_elig_per_id in number default null
251 ,p_elig_per_opt_id in number default null
252 )
253 is
254 --
255 l_proc varchar2(80):= g_package||'.main2';
256 l_process_flag varchar2(30):= 'Y';
257 l_level varchar2(30):= 'PL';
258 l_code varchar2(30);
259 l_cvg_strt_cd varchar2(30);
260 l_cvg_strt_rl number(15);
261 l_cvg_end_cd varchar2(30);
262 l_cvg_end_rl number(15);
263 l_chc_id number(15);
264 l_elig_flag varchar2(30);
265 l_inelig_rsn_cd varchar2(30);
266 l_exists varchar2(1);
267 l_elctbl_flag ben_elig_per_elctbl_chc.elctbl_flag%TYPE;
268 l_cvg_strt_dt date;
269 l_datetrack_mode varchar2(30);
270 l_cvg_end_dt date;
271 l_ler_chg_dpnt_cvg_cd varchar2(30);
272 l_elig_cvrd_dpnt_id number(15);
273 l_pl_typ_id number ;
274 l_pdp_effective_start_date date;
275 l_pdp_effective_end_date date;
276 l_correction boolean;
277 l_update boolean;
278 l_update_override boolean;
279 l_update_change_insert boolean;
280 l_mnanrd_condition boolean:=false;
281 --
282 l_fonm_cvg_strt_dt date ;
283 l_lf_evt_ocrd_dt date ;
284 -- Formula stuff
285 --
286 l_outputs ff_exec.outputs_t;
287 --
288 l_return varchar2(30);
289 --
290 -- 9999 Can I get all comp objects from cache????
291 --
292 -- Define cursors
293 --
294 cursor c_opt is
295 select opt_id from ben_oipl_f oipl
296 where oipl.oipl_id = p_oipl_id
297 and oipl.business_group_id = p_business_group_id
298 and l_lf_evt_ocrd_dt
299 between oipl.effective_start_date
300 and oipl.effective_end_date;
301
302
303 l_opt c_opt%rowtype;
304 --- This cursor added to find the pl_typ id # 2508745
305 --- for the ptip comparison for different pgm pl_typ_id to be compared
306 cursor c_pl_typ is
307 select pl_typ_id
308 from ben_ptip_f ptip
309 where p_ptip_id = ptip.ptip_id
310 and l_lf_evt_ocrd_dt
311 between ptip.effective_start_date
312 and ptip.effective_end_date ;
313
314
315 cursor c_plan is
316 select pl.dpnt_dsgn_cd,
317 pl.dpnt_cvg_strt_dt_cd,
318 pl.dpnt_cvg_strt_dt_rl,
319 pl.dpnt_cvg_end_dt_cd,
320 pl.dpnt_cvg_end_dt_rl
321 from ben_pl_f pl
322 where pl.pl_id = p_pl_id
323 and pl.business_group_id = p_business_group_id
324 and l_lf_evt_ocrd_dt
325 between pl.effective_start_date
326 and pl.effective_end_date;
327 --
328 l_plan c_plan%rowtype;
329 --
330 cursor c_pgm is
331 select pgm.dpnt_dsgn_lvl_cd,
332 pgm.dpnt_dsgn_cd,
333 pgm.dpnt_cvg_strt_dt_cd,
334 pgm.dpnt_cvg_strt_dt_rl,
335 pgm.dpnt_cvg_end_dt_cd,
336 pgm.dpnt_cvg_end_dt_rl
337 from ben_pgm_f pgm
338 where pgm.pgm_id = p_pgm_id
339 and pgm.business_group_id = p_business_group_id
340 and l_lf_evt_ocrd_dt
341 between pgm.effective_start_date
342 and pgm.effective_end_date;
343 --
344 l_pgm c_pgm%rowtype;
345 --
346 cursor c_ptip is
347 select ptip.dpnt_dsgn_cd,
348 ptip.dpnt_cvg_strt_dt_cd,
349 ptip.dpnt_cvg_strt_dt_rl,
350 ptip.dpnt_cvg_end_dt_cd,
351 ptip.dpnt_cvg_end_dt_rl
352 from ben_ptip_f ptip
353 where ptip.ptip_id = p_ptip_id
354 and ptip.business_group_id = p_business_group_id
355 and l_lf_evt_ocrd_dt
356 between ptip.effective_start_date
357 and ptip.effective_end_date;
358 --
359 l_ptip c_ptip%rowtype;
360 --
361 -- Can I get it from cache????
362 -- iREC : Ideally it should be obtained from cache or should passed
363 -- from calling routine. For now avoid looking at iRec life events.
364 --
365 cursor c_per_in_ler is
366 select pil.ler_id,
367 pil.person_id,
368 pil.lf_evt_ocrd_dt,
369 pil.per_in_ler_id
370 from ben_per_in_ler pil,
371 ben_ler_f ler
372 where pil.person_id = p_person_id
373 --and pil.business_group_id = p_business_group_id
374 and pil.per_in_ler_stat_cd = 'STRTD'
375 ---cwb changes
376 and ler.ler_id = pil.ler_id
377 and ler.typ_cd not in ('COMP', 'GSP', 'IREC')
378 and l_lf_evt_ocrd_dt
379 between ler.effective_start_date
380 and ler.effective_end_date
381 -- unrestricted enhancement
382 and pil.per_in_ler_id = nvl(p_per_in_ler_id,pil.per_in_ler_id);
383 --
384
385 --
386 l_per_in_ler c_per_in_ler%rowtype;
387 --
388 l_loc_rec hr_locations_all%rowtype;
389 l_jurisdiction_code varchar2(30);
393 chg.cvg_eff_end_cd,
390
391 cursor c_ler_chg_dep(p_level varchar2) is
392 select chg.cvg_eff_strt_cd,
394 chg.cvg_eff_strt_rl,
395 chg.cvg_eff_end_rl,
396 chg.ler_chg_dpnt_cvg_cd,
397 chg.ler_chg_dpnt_cvg_rl
398 from ben_ler_chg_dpnt_cvg_f chg
399 where chg.ler_id = l_per_in_ler.ler_id
400 and chg.business_group_id = p_business_group_id
401 and decode(p_level,
402 'PL',p_pl_id,
403 'PTIP',p_ptip_id,
404 'PGM', p_pgm_id) =
405 decode(p_level,
406 'PL',chg.pl_id,
407 'PTIP',chg.ptip_id,
408 'PGM', chg.pgm_id)
409 and l_lf_evt_ocrd_dt
410 between chg.effective_start_date
411 and chg.effective_end_date;
412
413 /*
414 --
415 cursor c_ler_chg_dep(p_level varchar2) is
416 select chg.cvg_eff_strt_cd,
417 chg.cvg_eff_end_cd,
418 chg.cvg_eff_strt_rl,
419 chg.cvg_eff_end_rl,
420 chg.ler_chg_dpnt_cvg_cd,
421 chg.ler_chg_dpnt_cvg_rl
422 from ben_ler_chg_dpnt_cvg_f chg,
423 ben_ptip_f ptip
424 where chg.ler_id = l_per_in_ler.ler_id
425 and chg.business_group_id = p_business_group_id
426 and chg.Ptip_id = ptip.ptip_id (+)
427 and l_lf_evt_ocrd_dt
428 between ptip.effective_start_date (+)
429 and ptip.effective_end_date (+)
430 and decode(p_level,
431 'PL',p_pl_id,
432 'PTIP',l_pl_typ_id, -- 2508745 ptip validateated agains pl type id
433 'PGM', p_pgm_id) =
434 decode(p_level,
435 'PL',chg.pl_id,
436 'PTIP',ptip.pl_typ_id,
437 'PGM', chg.pgm_id)
438 and l_lf_evt_ocrd_dt
439 between chg.effective_start_date
440 and chg.effective_end_date;
441 */
442 --
443 l_ler_chg_dep c_ler_chg_dep%rowtype;
444 l_prtt_enrt_rslt_id number;
445 --
446 -- Gets the enrolment information for this plan
447 --
448 cursor c_plan_enrolment_info is
449 select prtt_enrt_rslt_id
450 from ben_prtt_enrt_rslt_f pen
451 where pen.person_id=p_person_id and
452 pen.business_group_id =p_business_group_id and
453 --pen.sspndd_flag='N' and --5127698
454 pen.prtt_enrt_rslt_stat_cd is null and
455 pen.effective_end_date = hr_api.g_eot and
456 nvl(l_lf_evt_ocrd_dt,p_effective_date)-1 <=
457 pen.enrt_cvg_thru_dt and
458 pen.enrt_cvg_strt_dt < pen.effective_end_date
459 and pen.pl_id = p_pl_id
460 and nvl(pen.pgm_id, -1) = nvl(p_pgm_id, -1);
461 --
462 -- Gets the enrolment information for this oipl
463 --
464 cursor c_oipl_enrolment_info is
465 select prtt_enrt_rslt_id
466 from ben_prtt_enrt_rslt_f pen
467 where pen.person_id=p_person_id and
468 pen.business_group_id =p_business_group_id and
469 --pen.sspndd_flag='N' and --5127698
470 pen.prtt_enrt_rslt_stat_cd is null and
471 pen.effective_end_date = hr_api.g_eot and
472 nvl(l_lf_evt_ocrd_dt,p_effective_date)-1 <=
473 pen.enrt_cvg_thru_dt and
474 pen.enrt_cvg_strt_dt < pen.effective_end_date and
475 pen.oipl_id=p_oipl_id
476 and nvl(pen.pgm_id, -1) = nvl(p_pgm_id, -1);
477 --
478 cursor c_contact is
479 select ctr.contact_person_id,
480 ctr.contact_relationship_id,
481 ctr.contact_type,
482 ctr.date_end, -- 4697057 Added this
483 'Y' contact_active_flag
484 from per_contact_relationships ctr,
485 per_all_people_f per
486 where per.person_id = ctr.contact_person_id
487 and l_lf_evt_ocrd_dt <= nvl(per.DATE_OF_DEATH, l_lf_evt_ocrd_dt) /* Bug 4287999 */
488 and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
489 and ctr.personal_flag = 'Y'
490 and ctr.contact_relationship_id =
491 ( select min(contact_relationship_id)
492 from per_contact_relationships ctr2
493 where ctr2.contact_person_id = ctr.contact_person_id
494 and ctr2.person_id = p_person_id /* Bug 4692782 */
495 and ctr2.personal_flag = 'Y'
496 and l_lf_evt_ocrd_dt between
497 nvl(ctr2.date_start,nvl(l_lf_evt_ocrd_dt,p_effective_date))
498 and nvl(ctr2.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
499 )
500 and ctr.business_group_id = p_business_group_id
501 and ctr.person_id = p_person_id
502 and l_lf_evt_ocrd_dt between
503 nvl(ctr.date_start,nvl(l_lf_evt_ocrd_dt,p_effective_date))
504 and nvl(ctr.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
505 union
506 --
507 -- this union is to provide rows for ended contacts.
508 -- these ended contacts will have contact_active_flag='N'.
509 -- these contacts elig_cvrd_dpnt rows should be ended.
510 --
511 select ctr.contact_person_id,
512 ctr.contact_relationship_id,
513 ctr.contact_type,
514 ctr.date_end, -- 4697057 Added this
515 'N' contact_active_flag
516 from per_contact_relationships ctr,
520 where per.person_id = ctr.contact_person_id
517 per_all_people_f per,
518 ben_elig_cvrd_dpnt_f pdp,
519 ben_per_in_ler pil
521 and l_lf_evt_ocrd_dt <= nvl(per.DATE_OF_DEATH, l_lf_evt_ocrd_dt) /* Bug 4287999 */
522 and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
523 and ctr.personal_flag = 'Y'
524 and ctr.business_group_id = p_business_group_id
525 and ctr.person_id = p_person_id
526 and l_lf_evt_ocrd_dt >= nvl(ctr.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
527 and pdp.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
528 and pdp.business_group_id = p_business_group_id
529 and pdp.dpnt_person_id = ctr.contact_person_id
530 and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
531 pdp.effective_start_date and pdp.effective_end_date
532 and l_lf_evt_ocrd_dt between pdp.cvg_strt_dt and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
533 and pil.per_in_ler_id=pdp.per_in_ler_id
534 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
535 and not exists (
536 select null
537 from per_contact_relationships ctr1,
538 per_all_people_f per1
539 where ctr1.contact_person_id=ctr.contact_person_id
540 and per1.person_id = ctr1.contact_person_id
541 and l_lf_evt_ocrd_dt between per1.effective_start_date and per1.effective_end_date
542 and ctr1.personal_flag = 'Y'
543 and ctr1.business_group_id = p_business_group_id
544 and ctr1.person_id = p_person_id
545 and nvl(l_lf_evt_ocrd_dt,p_effective_date) between
546 nvl(ctr1.date_start, nvl(l_lf_evt_ocrd_dt,p_effective_date))
547 and nvl(ctr1.date_end, nvl(l_lf_evt_ocrd_dt,p_effective_date)))
548 ;
549 --
550 l_contact c_contact%rowtype;
551 --
552 -- hierarchy
553 -- 1) oipl
554 -- 2) opt
555 -- 3) pl
556 --
557 cursor c_dsgn is
558 select a.lvl, -- 5152062 : Added the GROUPING..
559 SUM(NVL(a.mn_dpnts_rqd_num,0)) mn_dpnts_rqd_num,
560 SUM(NVL(a.mx_dpnts_alwd_num,9999999)) mx_dpnts_alwd_num, --Bug 5529902: if mx_dpnts_alwd_num is NULL consider unlimited designees can be assigned
561 MAX(NVL(a.no_mn_num_dfnd_flag,'N')) no_mn_num_dfnd_flag
562 from (
563 select decode(nvl(ddr.oipl_id,-1),
564 -1,
565 decode(nvl(ddr.opt_id,-1),
566 -1,
567 3,--pl level
568 2),--opt level
569 1) lvl, -- oipl level,
570 mn_dpnts_rqd_num,
571 mx_dpnts_alwd_num,
572 no_mn_num_dfnd_flag
573 from ben_dsgn_rqmt_f ddr
574 where (ddr.oipl_id = p_oipl_id
575 or ddr.pl_id = p_pl_id
576 or ddr.opt_id = (select oipl.opt_id
577 from ben_oipl_f oipl
578 where oipl.oipl_id = p_oipl_id
579 and oipl.business_group_id =
580 p_business_group_id
581 and l_lf_evt_ocrd_dt
582 between oipl.effective_start_date
583 and oipl.effective_end_date))
584 and ddr.dsgn_typ_cd = 'DPNT'
585 /*
586 Task 133 : when mn_dpnts_rqd_num is 0 or no_mn_num_dfnd_flag is Y
587 then make dpnt_dsgn_cd optional, otherwise no interim will be
588 assigned. These commented conditions are checked when the rows are
589 fetched.
590 and nvl(ddr.mn_dpnts_rqd_num,-1) = 0
591 and nvl(ddr.mx_dpnts_alwd_num,-1) = 0
592 */
593 and ddr.business_group_id = p_business_group_id
594 and l_lf_evt_ocrd_dt
595 between ddr.effective_start_date
596 and ddr.effective_end_date
597 ) a
598 group by a.lvl
599 order by 1
600 ;
601 --
602 l_mn_dpnts_rqd_num number;
603 l_mx_dpnts_alwd_num number;
604 l_no_mn_num_dfnd_flag varchar2(1);
605 cursor c_pdp is
606 select pdp.object_version_number,
607 pdp.elig_cvrd_dpnt_id,
608 pdp.effective_start_date,
609 pdp.cvg_strt_dt,
610 pdp.effective_end_date
611 from ben_elig_cvrd_dpnt_f pdp,
612 ben_per_in_ler pil
613 where pdp.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
614 and pdp.business_group_id = p_business_group_id
615 and pdp.dpnt_person_id = l_contact.contact_person_id
616 -- bug 3784375
617 --and nvl(p_lf_evt_ocrd_dt,p_effective_date)
618 -- between pdp.effective_start_date and pdp.effective_end_date
619 and pdp.effective_end_date = hr_api.g_eot
620 --
621 -- Bug 5127698 : Due to following date check, dependents do not get carry forwarded
622 -- when PDP.cvg_strt_dt is later than l_lf_evt_ocrd_dt.
623 -- Above date check(EOT) should be sufficient.
624 --and l_lf_evt_ocrd_dt
625 -- between pdp.cvg_strt_dt and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
626 -- Instead of above check, added following check which will make sure that
627 -- end dated dependents will not get carry forwarded
628 and nvl(pdp.cvg_thru_dt,hr_api.g_eot) >= l_lf_evt_ocrd_dt
629 and nvl(pdp.cvg_thru_dt,hr_api.g_eot) >= pdp.cvg_strt_dt
633 cursor c_dcln_pl_opt is
630 and pil.per_in_ler_id=pdp.per_in_ler_id
631 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
632 --
634 select decode(pln.invk_dcln_prtn_pl_flag,
635 'N', nvl(opt.invk_wv_opt_flag,'N'),
636 'Y') dcln_pl_opt_flag
637 from ben_pl_f pln,
638 ben_oipl_f oipl,
639 ben_opt_f opt
640 where pln.pl_id = p_pl_id
641 and oipl.oipl_id (+) = p_oipl_id
642 and oipl.pl_id (+) = pln.pl_id
643 and oipl.opt_id = opt.opt_id (+)
644 and l_lf_evt_ocrd_dt
645 between pln.effective_start_date and pln.effective_end_date
646 and l_lf_evt_ocrd_dt
647 between oipl.effective_start_date(+) and oipl.effective_end_date(+)
648 and l_lf_evt_ocrd_dt
649 between opt.effective_start_date(+) and opt.effective_end_date(+);
650 --
651 cursor c_dpnt_exist (p_per_in_ler_id number,
652 p_elig_per_id number,
653 p_elig_per_opt_id number,
654 p_dpnt_person_id number) is
655 select null
656 from ben_elig_dpnt egd
657 where egd.dpnt_person_id = p_dpnt_person_id
658 and egd.per_in_ler_id = p_per_in_ler_id
659 and egd.elig_per_id = p_elig_per_id
660 and (p_elig_per_opt_id is null or
661 egd.elig_per_opt_id = p_elig_per_opt_id);
662 --
663 l_pdp c_pdp%rowtype;
664 l_ass_rec per_all_assignments_f%rowtype;
665 l_egd_rec ben_elig_dpnt%rowtype;
666 l_egd_rec_found boolean := false;
667 l_pdp_rec_found boolean := false;
668 l_elig_dpnt_id number;
669 l_elig_per_id number;
670 l_elig_per_opt_id number;
671 l_egd_object_version_number number(9);
672 l_dummy varchar2(1);
673 l_dsgn_rqmt_level number;
674 l_dpnt_cvg_strt_dt date;
675 l_next_row number;
676 l_sysdate date;
677 l_decline_flag varchar2(30) := 'N';
678 --
679 l_elig_per_elctbl_chc_id number;
680 l_per_in_ler_id number;
681
682 l_env ben_env_object.g_global_env_rec_type;
683 l_benmngle_parm_rec benutils.g_batch_param_rec; /* Bug 5936849 */
684 l_effective_date date ;
685 l_contact_date_end date;
686 --
687 begin
688 --
689 g_debug := hr_utility.debug_enabled;
690 --
691 -- Set sysdate to a local
692 --
693 if g_debug then
694 hr_utility.set_location ('Entering '||l_proc,10);
695 end if;
696 --
697 if g_debug then
698 hr_utility.set_location ('Fonm '|| ben_manage_life_events.fonm ,10);
699 hr_utility.set_location ('Fonm '|| ben_manage_life_events.g_fonm_cvg_strt_dt ,10);
700 end if;
701 l_sysdate := sysdate;
702 -- fonm
703 l_lf_evt_ocrd_dt := nvl(p_lf_evt_ocrd_dt, p_effective_date);
704 if ben_manage_life_events.fonm = 'Y'
705 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
706 --
707 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
708 l_lf_evt_ocrd_dt := nvl(l_fonm_cvg_strt_dt,l_lf_evt_ocrd_dt ) ;
709
710 --
711 hr_utility.set_location ('Fonm Date '||l_lf_evt_ocrd_dt ,10);
712 end if;
713
714 --
715 if g_debug then
716 hr_utility.set_location ('pl '||p_pl_id,10);
717 hr_utility.set_location ('plip '||p_plip_id,10);
718 hr_utility.set_location ('pgm '||p_pgm_id,10);
719 end if;
720 --
721 g_egd_table.delete;
722 g_egd_table := g_egd_table_temp;
723 --
724 if p_oipl_id is not null then
725 open c_opt;
726 fetch c_opt into l_opt;
727 close c_opt;
728 end if;
729 -- Determine designation level
730 --
731 if g_debug then
732 hr_utility.set_location ('Determining designation level '||l_proc,30);
733 end if;
734 -- fix made for 1192368 reversed - bug#3238951
735 -- start fix 1192368
736 -- while finding out the dependency designation level we should first see
737 -- if it has been defined at plan level and then check the level at program
738 -- level to see if it is PGM / PTIP
739 -- Moved the cursor code of c_plan from below to here to check if
740 -- pl.dpnt_dsgn_cd is null or not
741 --
742 /*
743 open c_plan;
744 --
745 fetch c_plan into l_plan;
746 if c_plan%notfound then
747 --
748 close c_plan;
749 fnd_message.set_name('BEN','BEN_91472_PLAN_NOT_FOUND');
750 fnd_message.set_token('PROC',l_proc);
751 fnd_message.set_token('PL_ID',to_char(p_pl_id));
752 fnd_message.raise_error;
753 --
754 end if;
755 --
756 close c_plan;
757 */
758 --
759 -- end fix 1192368
760 -- modified the if condn below to check for l_plan.dpnt_dsgn_cd before
761 -- going into pgm level check.
762 --
763 if p_pgm_id is not null then
764 --
765 -- find the level from the program
766 --
767 open c_pgm;
768 --
769 fetch c_pgm into l_pgm;
770 --
771 if c_pgm%notfound then
772 --
773 close c_pgm;
774 fnd_message.set_name('BEN','BEN_91470_PGM_NOT_FOUND');
778 --
775 fnd_message.set_token('PROC',l_proc);
776 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
777 fnd_message.raise_error;
779 end if;
780 --
781 close c_pgm;
782 --
783 l_level := l_pgm.dpnt_dsgn_lvl_cd;
784 --
785 else
786 --
787 -- PLAN level
788 --
789 l_level := 'PL';
790 --
791 end if;
792 --
793 --Bug 2189561 in case of plan in program when the dpnt_dsgn_lvl_cd is null at plan level
794 -- and user defines ONLY in plan enrollment requirements, it fails.
795 --To resolve the issue, we are resetting to PL if it is null
796 --
797 if l_level is null then
798 --
799 l_level := 'PL';
800 --
801 end if;
802 --
803 -- Retrieve designation code
804 --
805 if g_debug then
806 hr_utility.set_location ('Level = '||l_level,40);
807 end if;
808 --
809 if l_level = 'PGM' then
810 --
811 l_code := l_pgm.dpnt_dsgn_cd;
812 --
813 elsif l_level = 'PTIP' then
814 --
815 open c_ptip;
816 --
817 fetch c_ptip into l_ptip;
818 --
819 if c_ptip%notfound then
820 --
821 close c_ptip;
822 fnd_message.set_name('BEN','BEN_91471_MISSING_PLAN_TYPE');
823 fnd_message.set_token('PROC',l_proc);
824 fnd_message.set_token('PTIP_ID',to_char(p_ptip_id));
825 fnd_message.raise_error;
826 --
827 end if;
828 --
829 close c_ptip;
830 --
831 l_code := l_ptip.dpnt_dsgn_cd;
832 --
833 elsif l_level = 'PL' then
834 --
835 open c_plan;
836 fetch c_plan into l_plan;
837 if c_plan%notfound then
838 --
839 close c_plan;
840 fnd_message.set_name('BEN','BEN_91472_PLAN_NOT_FOUND');
841 fnd_message.set_token('PROC',l_proc);
842 fnd_message.set_token('PL_ID',to_char(p_pl_id));
843 fnd_message.raise_error;
844 --
845 end if;
846 --
847 close c_plan;
848 --
849 l_code := l_plan.dpnt_dsgn_cd;
850 --
851 else
852 --
853 l_code := NULL;
854 l_process_flag := 'N';
855 --
856 end if;
857 --
858 if g_debug then
859 hr_utility.set_location ('l_code '||l_code,50);
860 hr_utility.set_location ('l_level '||l_level,50);
861 hr_utility.set_location( 'l_process_flag ' || l_process_flag , 50) ;
862 end if;
863 --
864 -- Check whether the choice is for Decline Plan/Option. If so,
865 -- no need to create eligible dependents for them.
866 --
867 open c_dcln_pl_opt;
868 fetch c_dcln_pl_opt into l_decline_flag;
869 close c_dcln_pl_opt;
870 --
871 if l_decline_flag = 'Y' then
872 l_process_flag := 'N';
873 --bug#3238951 - assign Optional to dependent designation
874 l_code := 'O';
875 end if;
876 --
877 -- Does this life event support dependent changes?
878 --
879 if g_debug then
880 hr_utility.set_location ('Determining life event support '||l_proc,50);
881 hr_utility.set_location ('Process Flag '||l_process_flag,50);
882 end if;
883 --
884 if l_process_flag = 'Y' then
885 --
886 open c_per_in_ler;
887 --
888 fetch c_per_in_ler into l_per_in_ler;
889 --
890 if c_per_in_ler%notfound then
891 --
892 close c_per_in_ler;
893 fnd_message.set_name('BEN','BEN_91473_PER_NOT_FOUND');
894 fnd_message.set_token('PROC',l_proc);
895 /* fnd_message.set_token('PER_IN_LER_ID',
896 to_char(l_elctbl_chc.per_in_ler_id)); */
897 fnd_message.raise_error;
898 --
899 end if;
900 --
901 close c_per_in_ler;
902 -- before ler change find out the pl type id # 2508745
903
904 open c_pl_typ ;
905 fetch c_pl_typ into l_pl_typ_id ;
906 close c_pl_typ ;
907 --
908 if g_debug then
909 hr_utility.set_location( ' l_pl_typ_id ' || l_pl_typ_id , 40) ;
910 hr_utility.set_location( ' l_process_flag ' || l_process_flag , 40) ;
911 end if;
912 --
913 open c_ler_chg_dep(l_level);
914 --
915 fetch c_ler_chg_dep into l_ler_chg_dep;
916 --
917 if c_ler_chg_dep%notfound then
918 --
919 l_process_flag := 'N';
920 --
921 if g_debug then
922 hr_utility.set_location( ' ler chagne l_process_flag ' || l_process_flag , 40) ;
923 end if;
924 else
925 --
926 l_ler_chg_dpnt_cvg_cd := l_ler_chg_dep.ler_chg_dpnt_cvg_cd;
927 --
928 if l_ler_chg_dpnt_cvg_cd = 'RL' then
929 --
930 ben_person_object.get_object(p_person_id => l_per_in_ler.person_id,
931 p_rec => l_ass_rec);
932 --
933 if l_ass_rec.assignment_id is null then
934 --
935 ben_person_object.get_benass_object
939 end if;
936 (p_person_id => l_per_in_ler.person_id,
937 p_rec => l_ass_rec);
938 --
940 --
941 if l_ass_rec.location_id is not null then
942 --
943 ben_location_object.get_object
944 (p_location_id => l_ass_rec.location_id,
945 p_rec => l_loc_rec);
946 --
947 --if l_loc_rec.region_2 is not null then
948 -- l_jurisdiction_code := pay_mag_utils.lookup_jurisdiction_code
949 -- (p_state => l_loc_rec.region_2);
950 --end if ;
951 --
952 end if;
953 --
954 l_outputs := benutils.formula
955 (p_formula_id => l_ler_chg_dep.ler_chg_dpnt_cvg_rl,
956 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
957 p_assignment_id => l_ass_rec.assignment_id,
958 p_organization_id => l_ass_rec.organization_id,
959 p_business_group_id => p_business_group_id,
960 p_pgm_id => p_pgm_id,
961 p_pl_id => p_pl_id,
962 p_pl_typ_id => p_pl_typ_id,
963 p_opt_id => l_opt.opt_id,
964 p_ler_id => l_per_in_ler.ler_id,
965 p_elig_per_elctbl_chc_id => null,
966 p_jurisdiction_code => l_jurisdiction_code,
967 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
968 p_param1_value => to_char(p_person_id));
969 --
970 l_ler_chg_dpnt_cvg_cd := l_outputs(l_outputs.first).value;
971 --
972 end if;
973 --
974 if l_ler_chg_dpnt_cvg_cd in ('MNANRD', 'N') then
975 --
976 l_process_flag := 'N';
977 --
978 end if;
979 hr_utility.set_location( ' ler chagne l_process_flag ' || l_process_flag , 50) ;
980
981 if l_ler_chg_dpnt_cvg_cd in ('MNANRD', 'N','MRD') then
982 --
983 -- If the below flag is true then process the dependents
984 -- as if process_flag='Y' but update choice to be not
985 -- designatable.
986 --
987 l_mnanrd_condition:=true;
988 hr_utility.set_location ('mnanrd set',59);
989 --
990 end if;
991 --
992 end if;
993 --
994 close c_ler_chg_dep;
995 --
996 end if;
997 --
998 if g_debug then
999 hr_utility.set_location ('Checking comp object dep allowed '||l_proc,60);
1000 hr_utility.set_location ('Process Flag '||l_process_flag,50);
1001 end if;
1002 --
1003 if l_process_flag = 'Y' then
1004 hr_utility.set_location ('in this if',50);
1005 --
1006 -- See if we can find designation requirements ben_dsgn_rqmt_f
1007 -- If designation requirments does not exist, than any number
1008 -- or type of dependents are allowed. If designation requirements
1009 -- are found, then the min and max cannot be 0(Employee Only option).
1010 --
1011 open c_dsgn;
1012 --
1013 fetch c_dsgn into l_dsgn_rqmt_level,
1014 l_mn_dpnts_rqd_num,
1015 l_mx_dpnts_alwd_num,
1016 l_no_mn_num_dfnd_flag;
1017 --
1018 if c_dsgn%found then
1019 --
1020 -- Task 133 : Interim coverage cannot be assigned when in
1021 -- insufficient number of dependents have been designated
1022 -- to a participant enrollment. In this case, the current
1023 -- product will end the participant's current coverage.
1024 -- To have above functionality, if no dependent designation
1025 -- is required if l_mn_dpnts_rqd_num is 0 or l_no_mn_num_dfnd_flag
1026 -- is Y.
1027 --
1028 if nvl(l_mn_dpnts_rqd_num, -1) = 0 and
1029 nvl(l_mx_dpnts_alwd_num, -1) = 0 and
1030 l_no_mn_num_dfnd_flag = 'N' then
1031 -- 5152062 : Set process_flag to 'N' , only if if explicitly set l_mn_dpnts_rqd_num to 'Y'.
1032 --
1033 hr_utility.set_location ('dsgn not found',50);
1034 l_process_flag := 'N';
1035 --
1036 end if;
1037 --
1038 if nvl(l_mn_dpnts_rqd_num, -1) = 0
1039 -- 5152062 : Do not need the below condition.
1040 -- OR l_no_mn_num_dfnd_flag = 'Y'
1041 then
1042 --
1043 l_code := 'O';
1044 --
1045 end if;
1046 end if;
1047 --
1048 close c_dsgn;
1049
1050 end if;
1051 --
1052 if g_debug then
1053 hr_utility.set_location ('Process Flag '||l_process_flag,50);
1054 end if;
1055 --
1056 -- Determine coverage dates
1057 --
1058 if l_process_flag = 'Y' or l_mnanrd_condition then
1059 --
1060 hr_utility.set_location ('Determining coverage dates '||l_proc,70);
1061 --
1062 l_cvg_strt_cd := l_ler_chg_dep.cvg_eff_strt_cd;
1063 l_cvg_strt_rl := l_ler_chg_dep.cvg_eff_strt_rl;
1064 l_cvg_end_cd := l_ler_chg_dep.cvg_eff_end_cd;
1068 --
1065 l_cvg_end_rl := l_ler_chg_dep.cvg_eff_end_rl;
1066 --
1067 if l_cvg_strt_cd is null and l_cvg_strt_rl is null then
1069 if l_level ='PL' then
1070 --
1071 l_cvg_strt_cd := l_plan.dpnt_cvg_strt_dt_cd;
1072 l_cvg_strt_rl := l_plan.dpnt_cvg_strt_dt_rl;
1073 l_cvg_end_cd := l_plan.dpnt_cvg_end_dt_cd;
1074 l_cvg_end_rl := l_plan.dpnt_cvg_end_dt_rl;
1075 --
1076 elsif l_level = 'PTIP' then
1077 --
1078 l_cvg_strt_cd := l_ptip.dpnt_cvg_strt_dt_cd;
1079 l_cvg_strt_rl := l_ptip.dpnt_cvg_strt_dt_rl;
1080 l_cvg_end_cd := l_ptip.dpnt_cvg_end_dt_cd;
1081 l_cvg_end_rl := l_ptip.dpnt_cvg_end_dt_rl;
1082 --
1083 elsif l_level ='PGM' then
1084 --
1085 -- Use program dates if available
1086 --
1087 l_cvg_strt_cd := l_pgm.dpnt_cvg_strt_dt_cd;
1088 l_cvg_strt_rl := l_pgm.dpnt_cvg_strt_dt_rl;
1089 l_cvg_end_cd := l_pgm.dpnt_cvg_end_dt_cd;
1090 l_cvg_end_rl := l_pgm.dpnt_cvg_end_dt_rl;
1091 --
1092 end if;
1093 --
1094 end if;
1095 --
1096 if l_cvg_strt_cd is null then
1097 --
1098 fnd_message.set_name('BEN','BEN_91475_DEPT_ST_DT_NULL');
1099 fnd_message.set_token('PROC',l_proc);
1100 fnd_message.set_token('LER_ID',to_char(l_per_in_ler.ler_id));
1101 fnd_message.raise_error;
1102 --
1103 else
1104 --
1105 IF l_cvg_strt_cd <> 'RL' THEN
1106 --
1107 hr_utility.set_location('cvg_strt_cd'||l_cvg_strt_cd,10);
1108 hr_utility.set_location('cvg_strt_rl'||l_cvg_strt_rl,10);
1109 ben_determine_date.main
1110 (P_DATE_CD => l_cvg_strt_cd,
1111 P_BUSINESS_GROUP_ID => p_business_group_id,
1112 P_PERSON_ID => p_person_id,
1113 P_PGM_ID => p_pgm_id,
1114 P_PL_ID => p_pl_id,
1115 P_OIPL_ID => p_oipl_id,
1116 P_PER_IN_LER_ID => l_per_in_ler.per_in_ler_id,
1117 P_ELIG_PER_ELCTBL_CHC_ID => null,
1118 P_FORMULA_ID => l_cvg_strt_rl,
1119 P_EFFECTIVE_DATE => p_effective_date,
1120 P_LF_EVT_OCRD_DT => p_lf_evt_ocrd_dt,
1121 P_RETURNED_DATE => l_cvg_strt_dt
1122 );
1123 --
1124 END IF;
1125 --
1126 end if;
1127 --
1128 end if;
1129 --
1130 if g_debug then
1131 hr_utility.set_location ('l_Process_flag '||l_process_flag,80);
1132 end if;
1133 --
1134 g_upd_epe_egd_rec.g_process_flag := l_Process_flag;
1135 g_mnanrd_condition:=l_mnanrd_condition;
1136 g_upd_epe_egd_rec.g_code := l_code;
1137 g_upd_epe_egd_rec.g_ler_chg_dpnt_cvg_cd := l_ler_chg_dpnt_cvg_cd;
1138 g_upd_epe_egd_rec.g_cvg_strt_cd := l_cvg_strt_cd;
1139 g_upd_epe_egd_rec.g_cvg_strt_rl := l_cvg_strt_rl;
1140 --
1141 if l_Process_flag = 'Y' or l_mnanrd_condition then
1142 --
1143 -- Loop through potential dependent pool
1144 --
1145 hr_utility.set_location ('open c_contact2 '||l_proc,90);
1146 --
1147 -- Get the enrollment result row.
1148 --
1149 if (p_oipl_id is null) then
1150 --
1151 open c_plan_enrolment_info;
1152 fetch c_plan_enrolment_info into l_prtt_enrt_rslt_id;
1153 close c_plan_enrolment_info;
1154 --
1155 else
1156 --
1157 open c_oipl_enrolment_info;
1158 fetch c_oipl_enrolment_info into l_prtt_enrt_rslt_id;
1159 close c_oipl_enrolment_info;
1160 --
1161 end if;
1162 --
1163 open c_contact;
1164 loop
1165 hr_utility.set_location ('again in loop',50);
1166
1167 --
1168 fetch c_contact into l_contact;
1169 exit when c_contact%notfound;
1170 --
1171 -- 9999 As enrollment result not available,
1172 -- How to find the pdp. Need to derive from the
1173 -- comp object combination. ????
1174 --
1175 -- Following condition is added to fix Bug 1531647 to add inpute value contact_person_id
1176
1177 IF l_cvg_strt_cd = 'RL' THEN
1178 --
1179 hr_utility.set_location('cvg_strt_cd'||l_cvg_strt_cd,10);
1180 hr_utility.set_location('cvg_strt_rl'||l_cvg_strt_rl,10);
1181 ben_determine_date.main
1182 (P_DATE_CD => l_cvg_strt_cd,
1183 P_BUSINESS_GROUP_ID => p_business_group_id,
1184 P_PERSON_ID => p_person_id,
1185 P_PGM_ID => p_pgm_id,
1186 P_PL_ID => p_pl_id,
1187 P_OIPL_ID => p_oipl_id,
1188 P_PER_IN_LER_ID => l_per_in_ler.per_in_ler_id,
1189 P_ELIG_PER_ELCTBL_CHC_ID => null,
1190 P_FORMULA_ID => l_cvg_strt_rl,
1191 P_EFFECTIVE_DATE => p_effective_date,
1192 P_LF_EVT_OCRD_DT => p_lf_evt_ocrd_dt,
1193 P_RETURNED_DATE => l_cvg_strt_dt,
1194 P_PARAM1 => 'CON_PERSON_ID',
1195 P_PARAM1_VALUE => to_char(l_contact.contact_person_id));
1196 --
1197 END IF;
1198 --
1199 open c_pdp;
1200 fetch c_pdp into l_pdp;
1201 if c_pdp%found then
1202 l_pdp_rec_found := true;
1206 l_pdp_rec_found := false;
1203 l_elig_cvrd_dpnt_id := l_pdp.elig_cvrd_dpnt_id;
1204 l_dpnt_cvg_strt_dt := l_pdp.cvg_strt_dt;
1205 else
1207 l_elig_cvrd_dpnt_id := null;
1208 l_dpnt_cvg_strt_dt := null;
1209 end if;
1210 close c_pdp;
1211 --
1212 if l_contact.contact_active_flag='Y' then
1213 --
1214 hr_utility.set_location ('BEDEP_MN '||l_proc,90);
1215 --fonm taken care inside of the code dont pass any date
1216 ben_evaluate_dpnt_elg_profiles.main
1217 (p_contact_relationship_id => l_contact.contact_relationship_id,
1218 p_contact_person_id => l_contact.contact_person_id,
1219 p_pgm_id => p_pgm_id,
1220 p_pl_id => p_pl_id,
1221 p_ptip_id => p_ptip_id,
1222 p_oipl_id => p_oipl_id,
1223 p_business_group_id => p_business_group_id,
1224 p_per_in_ler_id => l_per_in_ler.per_in_ler_id,
1225 p_effective_date => p_effective_date,
1226 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1227 p_dpnt_cvg_strt_dt => l_dpnt_cvg_strt_dt,
1228 p_level => l_level,
1229 p_dependent_eligible_flag => l_elig_flag,
1230 p_dpnt_inelig_rsn_cd => l_inelig_rsn_cd);
1231 hr_utility.set_location ('Dn BEDEP_MN '||l_proc,90);
1232 else
1233 hr_utility.set_location ('contact ended '||l_proc,90);
1234 l_elig_flag:='N';
1235 l_contact_date_end := l_contact.date_end;
1236 end if;
1237 --
1238 if l_pdp_rec_found then
1239 --
1240 -- 9999 is it required? As it is based on pdp.
1241 --
1242 l_egd_rec_found := ben_ELIG_DPNT_api.get_elig_dpnt_rec(
1243 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
1244 p_effective_date => l_lf_evt_ocrd_dt,
1245 p_elig_dpnt_rec => l_egd_rec);
1246 --
1247 else
1248 --
1249 l_egd_rec_found := ben_ELIG_DPNT_api.get_elig_dpnt_rec
1250 (p_pl_id => p_pl_id
1251 ,p_pgm_id => p_pgm_id
1252 ,p_oipl_id => p_oipl_id
1253 ,p_dpnt_person_id => l_contact.contact_person_id
1254 ,p_effective_date => l_lf_evt_ocrd_dt
1255 --
1256 ,p_per_in_ler_id => p_per_in_ler_id
1257 ,p_elig_per_id => p_elig_per_id
1258 ,p_elig_per_opt_id => p_elig_per_opt_id
1259 ,p_opt_id => l_opt.opt_id
1260 --
1261 ,p_elig_dpnt_rec => l_egd_rec
1262 );
1263 --
1264 end if;
1265 hr_utility.set_location ('Dn BEDP_GEDR '||l_proc,90);
1266 --
1267 if l_elig_flag = 'Y' then
1268 --
1269 g_rec.person_id := l_per_in_ler.person_id;
1270 g_rec.pgm_id := p_pgm_id;
1271 g_rec.pl_id := p_pl_id;
1272 g_rec.oipl_id := p_oipl_id;
1273 g_rec.contact_typ_cd := l_contact.contact_type;
1274 g_rec.dpnt_person_id := l_contact.contact_person_id;
1275 g_rec.business_group_id := p_business_group_id;
1276 g_rec.effective_date := p_effective_date;
1277 --
1278 benutils.write(p_rec => g_rec);
1279 --
1280 hr_utility.set_location (' Elig Y BED_GEPID '||l_proc,90);
1281 if p_elig_per_id is not null
1282 then
1283 --
1284 l_elig_per_id := p_elig_per_id;
1285 l_elig_per_opt_id := p_elig_per_opt_id;
1286 --
1287 else
1288 --
1289 ben_ELIG_DPNT_api.get_elig_per_id
1290 (p_person_id => l_per_in_ler.person_id
1291 ,p_pgm_id => p_pgm_id
1292 ,p_pl_id => p_pl_id
1293 ,p_oipl_id => p_oipl_id
1294 ,p_business_group_id => p_business_group_id
1295 ,p_effective_date => l_lf_evt_ocrd_dt
1296 ,p_elig_per_id => l_elig_per_id
1297 ,p_elig_per_opt_id => l_elig_per_opt_id
1298 );
1299 --
1300 end if;
1301 --
1302 hr_utility.set_location (' Elig Y BED_CED '||l_proc,90);
1303 --
1304 -- 3685120 - Get the environment details and if it is U then create EGD
1305 --
1306 ben_env_object.get(p_rec => l_env);
1307 --
1308 -- Bug 5936849
1309 hr_utility.set_location('ACE l_env.mode_cd = ' || l_env.mode_cd, 9999);
1310 hr_utility.set_location('ACE l_env.benefit_action_id = ' || l_env.benefit_action_id, 9999);
1311 --
1312 if l_env.mode_cd is null
1313 then
1314 --
1315 benutils.get_batch_parameters(p_benefit_action_id => l_env.benefit_action_id,
1316 p_rec => l_benmngle_parm_rec
1317 );
1318 --
1322 --
1319 hr_utility.set_location('ACE l_benmngle_parm_rec.mode_cd = ' || l_benmngle_parm_rec.mode_cd, 9999);
1320 --
1321 l_env.mode_cd := l_benmngle_parm_rec.mode_cd;
1323 end if;
1324 --
1325 -- End Bug 5936849
1326
1327 hr_utility.set_location (' env mode cd '||l_env.mode_cd ,90);
1328 --bug#3495592 - cursor to check already created elig_dpnt record
1329 open c_dpnt_exist (p_per_in_ler_id =>l_per_in_ler.per_in_ler_id,
1330 p_elig_per_id => l_elig_per_id,
1331 p_elig_per_opt_id => l_elig_per_opt_id,
1332 p_dpnt_person_id => l_contact.contact_person_id);
1333 fetch c_dpnt_exist into l_dummy;
1334 if c_dpnt_exist%notfound or nvl(l_env.mode_cd,'~') in ('R', 'U') then
1335 if l_env.mode_cd in ('U','R') then
1336 --
1337 hr_utility.set_location ('inside ',11);
1338 /*j
1339 l_elig_dpnt_id := ben_manage_unres_life_events.egd_exists
1340 (p_PER_IN_LER_ID => 1 --l_per_in_ler.per_in_ler_id
1341 ,p_ELIG_PER_ID => 1 --l_elig_per_id
1342 ,p_ELIG_PER_OPT_ID => 1 --l_elig_per_opt_id
1343 ,p_DPNT_PERSON_ID =>1 --l_contact.contact_person_id
1344 );
1345 */
1346 l_elig_dpnt_id := ben_manage_unres_life_events.egd_exists
1347 (p_PER_IN_LER_ID => 1
1348 ,p_ELIG_PER_ID => 1
1349 ,p_ELIG_PER_OPT_ID => 1
1350 ,p_DPNT_PERSON_ID =>1
1351 );
1352 hr_utility.set_location ('after',12);
1353 end if;
1354 if l_elig_dpnt_id is not null then
1355 --
1356 ben_manage_unres_life_events.update_elig_dpnt
1357 (p_elig_dpnt_id => l_elig_dpnt_id
1358 ,p_create_dt => l_lf_evt_ocrd_dt
1359 ,p_business_group_id => p_business_group_id
1360 ,p_elig_per_elctbl_chc_id => null
1361 ,p_dpnt_person_id => l_contact.contact_person_id
1362 ,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
1363 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1364 ,p_elig_strt_dt => nvl(l_egd_rec.elig_strt_dt,
1365 nvl(p_lf_evt_ocrd_dt,
1366 p_effective_date))
1367 ,p_elig_thru_dt => hr_api.g_eot
1368 ,p_elig_per_id => l_elig_per_id
1369 ,p_elig_per_opt_id => l_elig_per_opt_id,
1370 p_ovrdn_flag => nvl(l_egd_rec.ovrdn_flag,'N')
1371 ,p_ovrdn_thru_dt => l_egd_rec.ovrdn_thru_dt
1372 ,p_object_version_number => l_egd_object_version_number
1373 ,p_effective_date => p_effective_date
1374 ,p_program_application_id => fnd_global.prog_appl_id
1375 ,p_program_id => fnd_global.conc_program_id
1376 ,p_request_id => fnd_global.conc_request_id
1377 ,p_program_update_date => l_sysdate
1378 );
1379 --
1380 else
1381 ben_elig_dpnt_api.create_perf_elig_dpnt
1382 (p_elig_dpnt_id => l_elig_dpnt_id
1383 ,p_create_dt => l_lf_evt_ocrd_dt
1384 ,p_business_group_id => p_business_group_id
1385 ,p_elig_per_elctbl_chc_id => null
1386 ,p_dpnt_person_id => l_contact.contact_person_id
1387 ,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
1388 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1389 ,p_elig_strt_dt => nvl(l_egd_rec.elig_strt_dt,
1390 nvl(p_lf_evt_ocrd_dt,
1391 p_effective_date))
1392 ,p_elig_thru_dt => hr_api.g_eot
1393 ,p_elig_per_id => l_elig_per_id
1394 ,p_elig_per_opt_id => l_elig_per_opt_id,
1395 p_ovrdn_flag => nvl(l_egd_rec.ovrdn_flag,'N')
1396 ,p_ovrdn_thru_dt => l_egd_rec.ovrdn_thru_dt
1397 ,p_object_version_number => l_egd_object_version_number
1398 ,p_effective_date => p_effective_date
1399 ,p_program_application_id => fnd_global.prog_appl_id
1400 ,p_program_id => fnd_global.conc_program_id
1401 ,p_request_id => fnd_global.conc_request_id
1402 ,p_program_update_date => l_sysdate
1403 );
1404 --
1405 end if;
1406
1407 hr_utility.set_location ('Dn Elig Y '||l_proc,90);
1408
1409 --
1410 l_next_row := nvl(g_egd_table.LAST, 0) + 1;
1411 g_egd_table(l_next_row).object_version_number := l_egd_object_version_number;
1412 g_egd_table(l_next_row).elig_dpnt_id := l_elig_dpnt_id;
1416 close c_dpnt_exist;
1413 l_elig_dpnt_id := null ; -- 4051409 - regression
1414
1415 end if;
1417 --
1418 else
1419 --
1420 -- Only update the coverage end date if dependent is currently
1421 -- eligible.
1422 --
1423 -- jcarpent bug fix for bug 1524099 below.
1424 -- added nvl below.
1425 -- jcarpent bug fix for 2147682 , merged by tilak
1426 if nvl(l_egd_rec.dpnt_inelig_flag,'N') = 'N' and l_pdp_rec_found = true then
1427 --
1428 -- Get coverage end date
1429 --
1430 if l_cvg_end_cd is null then
1431 --
1432 fnd_message.set_name('BEN','BEN_91478_INVALID_DEP_ENDDT');
1433 fnd_message.set_token('PROC',l_proc);
1434 fnd_message.set_token('LER_ID',to_char(l_per_in_ler.ler_id));
1435 fnd_message.raise_error;
1436 --
1437 else
1438 --
1439 hr_utility.set_location('l_cvg_end_cd'||l_cvg_end_Cd,10);
1440 hr_utility.set_location('l_cvg_end_rl'||l_cvg_end_rl,10);
1441 hr_utility.set_location('l_contact.date_end '|| l_contact.date_end,10);
1442 hr_utility.set_location('l_lf_evt_ocrd_dt '|| l_lf_evt_ocrd_dt,10);
1443 hr_utility.set_location('get_elig_change_dt '|| ben_evaluate_dpnt_elg_profiles.get_elig_change_dt,10);
1444 --
1445 -- 4697057 : If relationship is end-dated, determine end-date as of
1446 -- relationship_end, rather than ben_evaluate_dpnt_elg_profiles.get_elig_change_dt,
1447 -- since ben_evaluate_dpnt_elg_profiles.set_elig_change_dt is never
1448 -- called in this case..
1449 --
1450 if (l_contact.date_end <= l_lf_evt_ocrd_dt) then
1451 l_contact_date_end := l_contact.date_end + 1;
1452 else
1453 l_contact_date_end := ben_evaluate_dpnt_elg_profiles.get_elig_change_dt + 1;
1454 end if;
1455 --
1456 --
1457 ben_determine_date.main
1458 (P_DATE_CD => l_cvg_end_cd,
1459 P_BUSINESS_GROUP_ID => p_business_group_id,
1460 P_PERSON_ID => p_person_id,
1461 P_PGM_ID => p_pgm_id,
1462 P_PL_ID => p_pl_id,
1463 P_OIPL_ID => p_oipl_id,
1464 P_PER_IN_LER_ID => l_per_in_ler.per_in_ler_id,
1465 P_ELIG_PER_ELCTBL_CHC_ID => null,
1466 P_FORMULA_ID => l_cvg_end_rl,
1467 P_EFFECTIVE_DATE => p_effective_date,
1468 P_LF_EVT_OCRD_DT => l_contact_date_end, -- 4697057
1469 P_RETURNED_DATE => l_cvg_end_dt,
1470 P_PARAM1 => 'CON_PERSON_ID',
1471 P_PARAM1_VALUE => to_char(l_contact.contact_person_id));
1472 --
1473 end if;
1474 hr_utility.set_location ('Dn Elig N BENDETDT '||l_proc,90);
1475 --
1476 -- According to the cursor, the coverage has started as of the
1477 -- effective date, so the coverage through date should atleast be
1478 -- the coverge start date. If the coverage through date is less
1479 -- than the coverage start date, we assign a value of effective
1480 -- date.
1481 --
1482 if l_cvg_end_dt < l_pdp.cvg_strt_dt then
1483 --
1484 l_cvg_end_dt := l_LF_EVT_OCRD_DT;
1485 --
1486 end if;
1487 --
1488 if l_pdp_rec_found then
1489 --
1490 --bug#4658173
1491 if nvl(p_lf_evt_ocrd_dt,p_effective_date) < l_pdp.effective_start_date then
1492 l_effective_date := l_pdp.effective_start_date;
1493 else
1494 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
1495 end if;
1496 -- Check datetrack mode.
1497 --
1498 dt_api.find_dt_upd_modes
1499 (p_effective_date => l_effective_date,
1500 p_base_table_name => 'BEN_ELIG_CVRD_DPNT_F',
1501 p_base_key_column => 'elig_cvrd_dpnt_id',
1502 p_base_key_value => l_elig_cvrd_dpnt_id,
1503 p_correction => l_correction,
1504 p_update => l_update,
1505 p_update_override => l_update_override,
1506 p_update_change_insert => l_update_change_insert);
1507 --
1508 if l_update_override then
1509 --
1510 l_datetrack_mode := hr_api.g_update_override;
1511 --
1512 elsif l_update then
1513 --
1514 l_datetrack_mode := hr_api.g_update;
1515 --
1516 else
1517 --
1518 l_datetrack_mode := hr_api.g_correction;
1519 --
1520 end if;
1521 hr_utility.set_location ('datetrack_mode '||l_datetrack_mode,10);
1522 --
1523 -- Update the eligible dependent record
1524 --
1528 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
1525 hr_utility.set_location (' Elig N BECD_UECD '||l_proc,90);
1526 hr_utility.set_location (' l_cvg_thru_dt '||l_cvg_end_dt,90);
1527 --
1529 (p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1530 ,p_effective_start_date => l_pdp_effective_start_date
1531 ,p_effective_end_date => l_pdp_effective_end_date
1532 ,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
1533 ,p_cvg_thru_dt => l_cvg_end_dt
1534 ,p_object_version_number => l_pdp.object_version_number
1535 ,p_effective_date => l_effective_date
1536 ,p_datetrack_mode => l_datetrack_mode
1537 ,p_program_application_id => fnd_global.prog_appl_id
1538 ,p_program_id => fnd_global.conc_program_id
1539 ,p_request_id => fnd_global.conc_request_id
1540 ,p_program_update_date => l_sysdate
1541 ,p_business_group_id => p_business_group_id
1542 ,p_multi_row_actn => FALSE
1543 );
1544 --
1545 -- RCHASE - update elig_dpnt row when coverage is lost due to ineligibility
1546 if l_egd_rec.dpnt_inelig_flag = 'N' then
1547 --
1548 hr_utility.set_location(' Set Dpnt InElig ' || (l_contact_date_end -1), 10);
1549 hr_utility.set_location(' l_egd_rec.object_version_number ' || l_egd_rec.object_version_number, 10);
1550 --
1551 ben_elig_dpnt_api.update_elig_dpnt(
1552 p_elig_dpnt_id => l_egd_rec.elig_dpnt_id
1553 ,p_object_version_number => l_egd_rec.object_version_number
1554 ,p_effective_date => nvl(l_lf_evt_ocrd_dt,p_effective_date)
1555 ,p_elig_thru_dt => (l_contact_date_end-1) --l_cvg_end_dt 5100008
1556 ,p_dpnt_inelig_flag => 'Y'
1557 ,p_inelg_rsn_cd => l_inelig_rsn_cd
1558 );
1559 --
1560 end if;
1561 --
1562 -- Update global variable so a benefit assignment can
1563 -- be written.
1564 --
1565 hr_utility.set_location ('dpnt ineligible ',90);
1566 g_dpnt_ineligible := true;
1567 --
1568 end if;
1569 --
1570 if l_egd_rec_found then
1571 --
1572 hr_utility.set_location (' Elig N BED_UED '||l_proc,90);
1573 --
1574 ben_elig_dpnt_api.update_perf_elig_dpnt
1575 (p_elig_dpnt_id => l_egd_rec.elig_dpnt_id
1576 ,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
1577 ,p_elig_thru_dt => (l_contact_date_end-1)--nvl(l_cvg_end_dt,p_effective_date) 5100008
1578 ,p_dpnt_inelig_flag => 'Y'
1579 ,p_inelg_rsn_cd => l_inelig_rsn_cd
1580 ,p_object_version_number => l_egd_rec.object_version_number
1581 ,p_effective_date => nvl(l_lf_evt_ocrd_dt,p_effective_date)
1582 ,p_program_application_id => fnd_global.prog_appl_id
1583 ,p_program_id => fnd_global.conc_program_id
1584 ,p_request_id => fnd_global.conc_request_id
1585 ,p_program_update_date => l_sysdate
1586 );
1587 --
1588 end if;
1589 --
1590 hr_utility.set_location ('Dn Elig N '||l_proc,90);
1591 end if;
1592 end if; -- elig_flag = 'Y'
1593 --
1594 hr_utility.set_location ('End Con Loop '||l_proc,90);
1595 end loop;
1596 --
1597 close c_contact;
1598 hr_utility.set_location ('close c_contact '||l_proc,90);
1599 --
1600 end if;
1601 --
1602 if g_debug then
1603 hr_utility.set_location ('Leaving '||l_proc,80);
1604 end if;
1605 end;
1606 --
1607 -- Task 131 (pbodla): Elig dependent records are created soon after
1608 -- person is found eligible(In benmngle.pkb). Then electable
1609 -- coice record is created in bendenrr.pkb. This procedure
1610 -- links all the elig dependent rows and electable choice row.
1611 -- All elig dependent rows which belongs to a electable choice
1612 -- row are stored in g_egd_table, Also g_upd_epe_egd_rec record
1613 -- holds the information required to update electable choice row.
1614 -- Both g_egd_table, g_upd_epe_egd_rec are populated by bendepen.pkb
1615 --
1616 procedure p_upd_egd_with_epe_id
1617 (p_elig_per_elctbl_chc_id in number
1618 ,p_person_id in number
1619 ,p_effective_date in date
1620 ,p_lf_evt_ocrd_dt in date
1621 )
1622 is
1623 --
1624 l_proc varchar2(80):= g_package||'.p_upd_egd_with_epe_id';
1625 l_chc_id number(15);
1626 l_elctbl_flag ben_elig_per_elctbl_chc.elctbl_flag%TYPE;
1627 --
1628 -- Formula stuff
1629 --
1630 l_outputs ff_exec.outputs_t;
1631 l_return varchar2(30);
1632 /*
1633 --
1634 -- Define cursors
1635 --
1636 cursor c_elctbl_chc is
1637 select chc.elig_per_elctbl_chc_id,
1638 chc.object_version_number,
1639 chc.business_group_id,
1640 chc.per_in_ler_id,
1641 chc.pgm_id,
1645 chc.oipl_id,
1642 chc.pl_id,
1643 chc.pl_typ_id,
1644 chc.ptip_id,
1646 chc.prtt_enrt_rslt_id,
1647 chc.elctbl_flag,
1648 pel.enrt_perd_id
1649 from ben_elig_per_elctbl_chc chc,
1650 ben_pil_elctbl_chc_popl pel
1651 where chc.elig_per_elctbl_chc_id = l_chc_id
1652 and chc.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id;
1653 --
1654 l_elctbl_chc c_elctbl_chc%rowtype;
1655 */
1656 --
1657 l_elctbl_chc ben_epe_cache.g_pilepe_inst_row;
1658 --
1659 cursor c_per_in_ler is
1660 select pil.ler_id,
1661 pil.person_id,
1662 pil.lf_evt_ocrd_dt
1663 from ben_per_in_ler pil
1664 where pil.per_in_ler_id = l_elctbl_chc.per_in_ler_id
1665 and pil.business_group_id = l_elctbl_chc.business_group_id
1666 and pil.per_in_ler_stat_cd = 'STRTD';
1667 --
1668 l_per_in_ler c_per_in_ler%rowtype;
1669 l_sysdate date;
1670 --
1671 begin
1672 --
1673 g_debug := hr_utility.debug_enabled;
1674 --
1675 hr_utility.set_location ('Entering '||l_proc,10);
1676 --
1677 -- Set sysdate to a local
1678 --
1679 l_sysdate := sysdate;
1680 --
1681 -- get the electable choice
1682 --
1683 hr_utility.set_location('Elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,10);
1684 l_chc_id := p_elig_per_elctbl_chc_id;
1685 --
1686 -- Check that current comp object loop EPE row is set
1687 --
1688 if ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id is not null then
1689 --
1690 l_elctbl_chc.elig_per_elctbl_chc_id := ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id;
1691 l_elctbl_chc.pl_id := ben_epe_cache.g_currcobjepe_row.pl_id;
1692 l_elctbl_chc.plip_id := ben_epe_cache.g_currcobjepe_row.plip_id;
1693 l_elctbl_chc.oipl_id := ben_epe_cache.g_currcobjepe_row.oipl_id;
1694 l_elctbl_chc.elctbl_flag := ben_epe_cache.g_currcobjepe_row.elctbl_flag;
1695 l_elctbl_chc.per_in_ler_id := ben_epe_cache.g_currcobjepe_row.per_in_ler_id;
1696 l_elctbl_chc.business_group_id := ben_epe_cache.g_currcobjepe_row.business_group_id;
1697 l_elctbl_chc.object_version_number := ben_epe_cache.g_currcobjepe_row.object_version_number;
1698 --
1699 else
1700 --
1701 return;
1702 --
1703 end if;
1704 --
1705 /*
1706 open c_elctbl_chc;
1707 --
1708 fetch c_elctbl_chc into l_elctbl_chc;
1709 if c_elctbl_chc%notfound then
1710 --
1711 -- If no choice found, no need to go ahead, just return back.
1712 --
1713 close c_elctbl_chc;
1714 return;
1715 --
1716 else
1717 --
1718 close c_elctbl_chc;
1719 --
1720 end if;
1721 --
1722 */
1723 l_elctbl_flag := l_elctbl_chc.elctbl_flag;
1724 --
1725 -- Does this life event support dependent changes?
1726 --
1727 open c_per_in_ler;
1728 --
1729 fetch c_per_in_ler into l_per_in_ler;
1730 --
1731 if c_per_in_ler%notfound then
1732 --
1733 close c_per_in_ler;
1734 fnd_message.set_name('BEN','BEN_91473_PER_NOT_FOUND');
1735 fnd_message.set_token('PROC',l_proc);
1736 fnd_message.set_token('PER_IN_LER_ID',
1737 to_char(l_elctbl_chc.per_in_ler_id));
1738 fnd_message.raise_error;
1739 --
1740 end if;
1741 --
1742 close c_per_in_ler;
1743 --
1744 hr_utility.set_location ('Updating electable choice '||l_proc,80);
1745 hr_utility.set_location ('g_Process_flag '||g_upd_epe_egd_rec.g_process_flag,80);
1746 --
1747 if g_upd_epe_egd_rec.g_process_flag <> 'Y' then
1748 --
1749 hr_utility.set_location ('l_elctbl_flag '||l_elctbl_flag,40);
1750 hr_utility.set_location ('pl_id '||l_elctbl_chc.pl_id,50);
1751 hr_utility.set_location ('oipl_id '||l_elctbl_chc.oipl_id,50);
1752 -- Also must update g_code
1753 -- p_allws_dpnt_dsgn_flag will go away and be replaced by
1754 -- 1. Designation code => g_code
1755 -- 2. LER chg dep cd => g_ler_chg_dpnt_cvg_cd
1756 -- 3. Allows dep cvg flag => g_process_flag
1757 --
1758 -- Update the allows_dpnt_dsgn_flag to 'N'
1759 --
1760 hr_utility.set_location ('BEEPEAPI_UPD '||l_proc,80);
1761 ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
1762 (p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
1763 p_alws_dpnt_dsgn_flag => 'N',
1764 p_object_version_number => l_elctbl_chc.object_version_number,
1765 p_effective_date => p_effective_date,
1766 p_dpnt_dsgn_cd => g_upd_epe_egd_rec.g_code,
1767 p_ler_chg_dpnt_cvg_cd => g_upd_epe_egd_rec.g_ler_chg_dpnt_cvg_cd,
1768 p_program_application_id => fnd_global.prog_appl_id,
1769 p_program_id => fnd_global.conc_program_id,
1770 p_request_id => fnd_global.conc_request_id,
1771 p_program_update_date => l_sysdate,
1772 p_dpnt_cvg_strt_dt_cd => g_upd_epe_egd_rec.g_cvg_strt_cd ,
1773 p_dpnt_cvg_strt_dt_rl => g_upd_epe_egd_rec.g_cvg_strt_rl );
1774 hr_utility.set_location ('Dn BEEPEAPI_UPD '||l_proc,80);
1775 --
1776 else
1777 --
1778 -- Update the allows_dpnt_dsgn_flag
1779 --
1780 -- Not sure at this time if we should be storing
1781 -- Codes, dates, rules, or other codes
1782 -- Now writing dsgn_cd and cvg_cd
1783 --
1784 hr_utility.set_location ('BEEPEAPI_UPD 1 '||l_proc,80);
1785 ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
1786 (p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
1787 p_alws_dpnt_dsgn_flag => 'Y',
1788 p_object_version_number => l_elctbl_chc.object_version_number,
1789 p_effective_date => p_effective_date,
1790 p_dpnt_dsgn_cd => g_upd_epe_egd_rec.g_code,
1791 p_ler_chg_dpnt_cvg_cd => g_upd_epe_egd_rec.g_ler_chg_dpnt_cvg_cd,
1792 p_program_application_id => fnd_global.prog_appl_id,
1793 p_program_id => fnd_global.conc_program_id,
1794 p_request_id => fnd_global.conc_request_id,
1795 p_program_update_date => l_sysdate,
1796 p_dpnt_cvg_strt_dt_cd => g_upd_epe_egd_rec.g_cvg_strt_cd ,
1797 p_dpnt_cvg_strt_dt_rl => g_upd_epe_egd_rec.g_cvg_strt_rl );
1798 --
1799 hr_utility.set_location ('Dn BEEPEAPI_UPD 1 '||l_proc,80);
1800 end if;
1801 if g_upd_epe_egd_rec.g_process_flag = 'Y' or g_mnanrd_condition then
1802 hr_utility.set_location ('BEEGDAPI_UPD Loop '||l_proc,80);
1803 --
1804 -- Now update all the elig dpnt rows with epe id.
1805 --
1806 if nvl(g_egd_table.last, 0) > 0 then
1807 for l_curr_count in g_egd_table.first..g_egd_table.last
1808 loop
1809 hr_utility.set_location ('St BEEGDAPI_UPD loop '||l_proc,80);
1810 --
1811 -- Update the egd row with electable choice id.
1812 --
1813 ben_elig_dpnt_api.update_perf_elig_dpnt
1814 (p_elig_dpnt_id => g_egd_table(l_curr_count).elig_dpnt_id,
1815 p_per_in_ler_id => l_elctbl_chc.per_in_ler_id,
1816 p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
1817 p_object_version_number =>
1818 g_egd_table(l_curr_count).object_version_number,
1819 p_effective_date => p_effective_date,
1820 p_program_update_date => l_sysdate);
1821 hr_utility.set_location ('End BEEGDAPI_UPD loop '||l_proc,80);
1822 --
1823 end loop;
1824 --
1825 hr_utility.set_location ('Dn BEEGDAPI_UPD Loop '||l_proc,80);
1826 end if;
1827
1828 end if;
1829 --
1830 if g_debug then
1831 hr_utility.set_location ('Leaving '||l_proc,80);
1832 end if;
1833 end;
1834 END;