[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.3 2010/03/08 07:07:02 sagnanas 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 01-Oct-09 krupani 115.97 Bug 7481099: Corrected the fix done against bug 4287999
221 08-Mar-10 sagnanas 115.98 Bug 9443647 - Corrected the fix done against bug 4287999
222 */
223 --------------------------------------------------------------------------------
224 g_rec benutils.g_batch_dpnt_rec;
225 g_cvg_thru_dt date := null;
226 g_mnanrd_condition boolean:=false;
227 --
228 --
229 --
230 -- Task 131 (pbodla): Elig dependent records are created soon after
231 -- person is found eligible. This procedure is called from
232 -- ben_manage_life_events.process_comp_objects(benmngle.pkb).
233 -- Then electable choice record is created in bendenrr.pkb.
234 -- Essentially elig dependent rows are created before creating
235 -- the electable choice row.
236 -- All elig dependent rows which belongs to a electable choice
237 -- row are stored in g_egd_table, Also g_upd_epe_egd_rec record
238 -- holds the information required to update electable choice row.
239 --
240 procedure main
241 (p_pgm_id in number default null
242 ,p_pl_id in number default null
243 ,p_plip_id in number default null
244 ,p_ptip_id in number default null
245 ,p_oipl_id in number default null
246 ,p_pl_typ_id in number default null
247 ,p_business_group_id in number
248 ,p_person_id in number
249 ,p_effective_date in date
250 ,p_lf_evt_ocrd_dt in date
251 ,p_per_in_ler_id in number default null
252 ,p_elig_per_id in number default null
253 ,p_elig_per_opt_id in number default null
254 )
255 is
256 --
257 l_proc varchar2(80):= g_package||'.main2';
258 l_process_flag varchar2(30):= 'Y';
259 l_level varchar2(30):= 'PL';
260 l_code varchar2(30);
261 l_cvg_strt_cd varchar2(30);
262 l_cvg_strt_rl number(15);
263 l_cvg_end_cd varchar2(30);
264 l_cvg_end_rl number(15);
265 l_chc_id number(15);
266 l_elig_flag varchar2(30);
267 l_inelig_rsn_cd varchar2(30);
268 l_exists varchar2(1);
269 l_elctbl_flag ben_elig_per_elctbl_chc.elctbl_flag%TYPE;
270 l_cvg_strt_dt date;
271 l_datetrack_mode varchar2(30);
272 l_cvg_end_dt date;
273 l_ler_chg_dpnt_cvg_cd varchar2(30);
274 l_elig_cvrd_dpnt_id number(15);
275 l_pl_typ_id number ;
276 l_pdp_effective_start_date date;
277 l_pdp_effective_end_date date;
278 l_correction boolean;
279 l_update boolean;
280 l_update_override boolean;
281 l_update_change_insert boolean;
282 l_mnanrd_condition boolean:=false;
283 --
284 l_fonm_cvg_strt_dt date ;
285 l_lf_evt_ocrd_dt date ;
286 -- Formula stuff
287 --
288 l_outputs ff_exec.outputs_t;
289 --
290 l_return varchar2(30);
291 --
292 -- 9999 Can I get all comp objects from cache????
293 --
294 -- Define cursors
295 --
296 cursor c_opt is
297 select opt_id from ben_oipl_f oipl
298 where oipl.oipl_id = p_oipl_id
299 and oipl.business_group_id = p_business_group_id
300 and l_lf_evt_ocrd_dt
301 between oipl.effective_start_date
302 and oipl.effective_end_date;
303
304
305 l_opt c_opt%rowtype;
306 --- This cursor added to find the pl_typ id # 2508745
307 --- for the ptip comparison for different pgm pl_typ_id to be compared
308 cursor c_pl_typ is
309 select pl_typ_id
310 from ben_ptip_f ptip
311 where p_ptip_id = ptip.ptip_id
312 and l_lf_evt_ocrd_dt
313 between ptip.effective_start_date
314 and ptip.effective_end_date ;
315
316
317 cursor c_plan is
318 select pl.dpnt_dsgn_cd,
319 pl.dpnt_cvg_strt_dt_cd,
320 pl.dpnt_cvg_strt_dt_rl,
321 pl.dpnt_cvg_end_dt_cd,
322 pl.dpnt_cvg_end_dt_rl
323 from ben_pl_f pl
324 where pl.pl_id = p_pl_id
325 and pl.business_group_id = p_business_group_id
326 and l_lf_evt_ocrd_dt
327 between pl.effective_start_date
328 and pl.effective_end_date;
329 --
330 l_plan c_plan%rowtype;
331 --
332 cursor c_pgm is
333 select pgm.dpnt_dsgn_lvl_cd,
334 pgm.dpnt_dsgn_cd,
335 pgm.dpnt_cvg_strt_dt_cd,
336 pgm.dpnt_cvg_strt_dt_rl,
337 pgm.dpnt_cvg_end_dt_cd,
338 pgm.dpnt_cvg_end_dt_rl
339 from ben_pgm_f pgm
340 where pgm.pgm_id = p_pgm_id
341 and pgm.business_group_id = p_business_group_id
342 and l_lf_evt_ocrd_dt
343 between pgm.effective_start_date
344 and pgm.effective_end_date;
345 --
346 l_pgm c_pgm%rowtype;
347 --
348 cursor c_ptip is
349 select ptip.dpnt_dsgn_cd,
350 ptip.dpnt_cvg_strt_dt_cd,
351 ptip.dpnt_cvg_strt_dt_rl,
352 ptip.dpnt_cvg_end_dt_cd,
353 ptip.dpnt_cvg_end_dt_rl
354 from ben_ptip_f ptip
355 where ptip.ptip_id = p_ptip_id
356 and ptip.business_group_id = p_business_group_id
357 and l_lf_evt_ocrd_dt
358 between ptip.effective_start_date
359 and ptip.effective_end_date;
360 --
361 l_ptip c_ptip%rowtype;
362 --
363 -- Can I get it from cache????
364 -- iREC : Ideally it should be obtained from cache or should passed
365 -- from calling routine. For now avoid looking at iRec life events.
366 --
367 cursor c_per_in_ler is
368 select pil.ler_id,
369 pil.person_id,
370 pil.lf_evt_ocrd_dt,
371 pil.per_in_ler_id
372 from ben_per_in_ler pil,
373 ben_ler_f ler
374 where pil.person_id = p_person_id
375 --and pil.business_group_id = p_business_group_id
376 and pil.per_in_ler_stat_cd = 'STRTD'
377 ---cwb changes
378 and ler.ler_id = pil.ler_id
379 and ler.typ_cd not in ('COMP', 'GSP', 'IREC')
380 and l_lf_evt_ocrd_dt
381 between ler.effective_start_date
382 and ler.effective_end_date
383 -- unrestricted enhancement
384 and pil.per_in_ler_id = nvl(p_per_in_ler_id,pil.per_in_ler_id);
385 --
386
387 --
388 l_per_in_ler c_per_in_ler%rowtype;
389 --
390 l_loc_rec hr_locations_all%rowtype;
391 l_jurisdiction_code varchar2(30);
392
393 cursor c_ler_chg_dep(p_level varchar2) is
394 select chg.cvg_eff_strt_cd,
395 chg.cvg_eff_end_cd,
396 chg.cvg_eff_strt_rl,
397 chg.cvg_eff_end_rl,
398 chg.ler_chg_dpnt_cvg_cd,
399 chg.ler_chg_dpnt_cvg_rl
400 from ben_ler_chg_dpnt_cvg_f chg
401 where chg.ler_id = l_per_in_ler.ler_id
402 and chg.business_group_id = p_business_group_id
403 and decode(p_level,
404 'PL',p_pl_id,
405 'PTIP',p_ptip_id,
406 'PGM', p_pgm_id) =
407 decode(p_level,
408 'PL',chg.pl_id,
409 'PTIP',chg.ptip_id,
410 'PGM', chg.pgm_id)
411 and l_lf_evt_ocrd_dt
412 between chg.effective_start_date
413 and chg.effective_end_date;
414
415 /*
416 --
417 cursor c_ler_chg_dep(p_level varchar2) is
418 select chg.cvg_eff_strt_cd,
419 chg.cvg_eff_end_cd,
420 chg.cvg_eff_strt_rl,
421 chg.cvg_eff_end_rl,
422 chg.ler_chg_dpnt_cvg_cd,
423 chg.ler_chg_dpnt_cvg_rl
424 from ben_ler_chg_dpnt_cvg_f chg,
425 ben_ptip_f ptip
426 where chg.ler_id = l_per_in_ler.ler_id
427 and chg.business_group_id = p_business_group_id
428 and chg.Ptip_id = ptip.ptip_id (+)
429 and l_lf_evt_ocrd_dt
430 between ptip.effective_start_date (+)
431 and ptip.effective_end_date (+)
432 and decode(p_level,
433 'PL',p_pl_id,
434 'PTIP',l_pl_typ_id, -- 2508745 ptip validateated agains pl type id
435 'PGM', p_pgm_id) =
436 decode(p_level,
437 'PL',chg.pl_id,
438 'PTIP',ptip.pl_typ_id,
439 'PGM', chg.pgm_id)
440 and l_lf_evt_ocrd_dt
441 between chg.effective_start_date
442 and chg.effective_end_date;
443 */
444 --
445 l_ler_chg_dep c_ler_chg_dep%rowtype;
446 l_prtt_enrt_rslt_id number;
447 --
448 -- Gets the enrolment information for this plan
449 --
450 cursor c_plan_enrolment_info is
451 select prtt_enrt_rslt_id
452 from ben_prtt_enrt_rslt_f pen
453 where pen.person_id=p_person_id and
454 pen.business_group_id =p_business_group_id and
455 --pen.sspndd_flag='N' and --5127698
456 pen.prtt_enrt_rslt_stat_cd is null and
457 pen.effective_end_date = hr_api.g_eot and
458 nvl(l_lf_evt_ocrd_dt,p_effective_date)-1 <=
459 pen.enrt_cvg_thru_dt and
460 pen.enrt_cvg_strt_dt < pen.effective_end_date
461 and pen.pl_id = p_pl_id
462 and nvl(pen.pgm_id, -1) = nvl(p_pgm_id, -1);
463 --
464 -- Gets the enrolment information for this oipl
465 --
466 cursor c_oipl_enrolment_info is
467 select prtt_enrt_rslt_id
468 from ben_prtt_enrt_rslt_f pen
469 where pen.person_id=p_person_id and
470 pen.business_group_id =p_business_group_id and
471 --pen.sspndd_flag='N' and --5127698
472 pen.prtt_enrt_rslt_stat_cd is null and
473 pen.effective_end_date = hr_api.g_eot and
474 nvl(l_lf_evt_ocrd_dt,p_effective_date)-1 <=
475 pen.enrt_cvg_thru_dt and
476 pen.enrt_cvg_strt_dt < pen.effective_end_date and
477 pen.oipl_id=p_oipl_id
478 and nvl(pen.pgm_id, -1) = nvl(p_pgm_id, -1);
479 --
480 cursor c_contact is
481 select ctr.contact_person_id,
482 ctr.contact_relationship_id,
483 ctr.contact_type,
484 ctr.date_end, -- 4697057 Added this
485 'Y' contact_active_flag,
486 per.date_of_death /* Bug 7481099 */
487 from per_contact_relationships ctr,
488 per_all_people_f per
489 where per.person_id = ctr.contact_person_id
490 -- and l_lf_evt_ocrd_dt <= nvl(per.DATE_OF_DEATH, l_lf_evt_ocrd_dt) /* Bug 4287999 */
491 and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
492 and ctr.personal_flag = 'Y'
493 and ctr.contact_relationship_id =
494 ( select min(contact_relationship_id)
495 from per_contact_relationships ctr2
496 where ctr2.contact_person_id = ctr.contact_person_id
497 and ctr2.person_id = p_person_id /* Bug 4692782 */
498 and ctr2.personal_flag = 'Y'
499 and l_lf_evt_ocrd_dt between
500 nvl(ctr2.date_start,nvl(l_lf_evt_ocrd_dt,p_effective_date))
501 and nvl(ctr2.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
502 )
503 and ctr.business_group_id = p_business_group_id
504 and ctr.person_id = p_person_id
505 and l_lf_evt_ocrd_dt between
506 nvl(ctr.date_start,nvl(l_lf_evt_ocrd_dt,p_effective_date))
507 and nvl(ctr.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
508 union
509 --
510 -- this union is to provide rows for ended contacts.
511 -- these ended contacts will have contact_active_flag='N'.
512 -- these contacts elig_cvrd_dpnt rows should be ended.
513 --
514 select ctr.contact_person_id,
515 ctr.contact_relationship_id,
516 ctr.contact_type,
517 ctr.date_end, -- 4697057 Added this
518 'N' contact_active_flag,
519 per.date_of_death /* Bug 7481099 */
520 from per_contact_relationships ctr,
521 per_all_people_f per,
522 ben_elig_cvrd_dpnt_f pdp,
523 ben_per_in_ler pil
524 where per.person_id = ctr.contact_person_id
525 -- and l_lf_evt_ocrd_dt <= nvl(per.DATE_OF_DEATH, l_lf_evt_ocrd_dt) /* Bug 4287999 */
526 and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
527 and ctr.personal_flag = 'Y'
528 and ctr.business_group_id = p_business_group_id
529 and ctr.person_id = p_person_id
530 and l_lf_evt_ocrd_dt >= nvl(ctr.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
531 and pdp.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
532 and pdp.business_group_id = p_business_group_id
533 and pdp.dpnt_person_id = ctr.contact_person_id
534 and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
535 pdp.effective_start_date and pdp.effective_end_date
536 and l_lf_evt_ocrd_dt between pdp.cvg_strt_dt and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
537 and pil.per_in_ler_id=pdp.per_in_ler_id
538 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
539 and not exists (
540 select null
541 from per_contact_relationships ctr1,
542 per_all_people_f per1
543 where ctr1.contact_person_id=ctr.contact_person_id
544 and per1.person_id = ctr1.contact_person_id
545 and l_lf_evt_ocrd_dt between per1.effective_start_date and per1.effective_end_date
546 and ctr1.personal_flag = 'Y'
547 and ctr1.business_group_id = p_business_group_id
548 and ctr1.person_id = p_person_id
549 and nvl(l_lf_evt_ocrd_dt,p_effective_date) between
550 nvl(ctr1.date_start, nvl(l_lf_evt_ocrd_dt,p_effective_date))
551 and nvl(ctr1.date_end, nvl(l_lf_evt_ocrd_dt,p_effective_date)))
552 ;
553 --
554 l_contact c_contact%rowtype;
555 --
556 -- hierarchy
557 -- 1) oipl
558 -- 2) opt
559 -- 3) pl
560 --
561 cursor c_dsgn is
562 select a.lvl, -- 5152062 : Added the GROUPING..
563 SUM(NVL(a.mn_dpnts_rqd_num,0)) mn_dpnts_rqd_num,
564 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
565 MAX(NVL(a.no_mn_num_dfnd_flag,'N')) no_mn_num_dfnd_flag
566 from (
567 select decode(nvl(ddr.oipl_id,-1),
568 -1,
569 decode(nvl(ddr.opt_id,-1),
570 -1,
571 3,--pl level
572 2),--opt level
573 1) lvl, -- oipl level,
574 mn_dpnts_rqd_num,
575 mx_dpnts_alwd_num,
576 no_mn_num_dfnd_flag
577 from ben_dsgn_rqmt_f ddr
578 where (ddr.oipl_id = p_oipl_id
579 or ddr.pl_id = p_pl_id
580 or ddr.opt_id = (select oipl.opt_id
581 from ben_oipl_f oipl
582 where oipl.oipl_id = p_oipl_id
583 and oipl.business_group_id =
584 p_business_group_id
585 and l_lf_evt_ocrd_dt
586 between oipl.effective_start_date
587 and oipl.effective_end_date))
588 and ddr.dsgn_typ_cd = 'DPNT'
589 /*
590 Task 133 : when mn_dpnts_rqd_num is 0 or no_mn_num_dfnd_flag is Y
591 then make dpnt_dsgn_cd optional, otherwise no interim will be
592 assigned. These commented conditions are checked when the rows are
593 fetched.
594 and nvl(ddr.mn_dpnts_rqd_num,-1) = 0
595 and nvl(ddr.mx_dpnts_alwd_num,-1) = 0
596 */
597 and ddr.business_group_id = p_business_group_id
598 and l_lf_evt_ocrd_dt
599 between ddr.effective_start_date
600 and ddr.effective_end_date
601 ) a
602 group by a.lvl
603 order by 1
604 ;
605 --
606 l_mn_dpnts_rqd_num number;
607 l_mx_dpnts_alwd_num number;
608 l_no_mn_num_dfnd_flag varchar2(1);
609 cursor c_pdp is
610 select pdp.object_version_number,
611 pdp.elig_cvrd_dpnt_id,
612 pdp.effective_start_date,
613 pdp.cvg_strt_dt,
614 pdp.effective_end_date
615 from ben_elig_cvrd_dpnt_f pdp,
616 ben_per_in_ler pil
617 where pdp.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
618 and pdp.business_group_id = p_business_group_id
619 and pdp.dpnt_person_id = l_contact.contact_person_id
620 -- bug 3784375
621 --and nvl(p_lf_evt_ocrd_dt,p_effective_date)
622 -- between pdp.effective_start_date and pdp.effective_end_date
623 and pdp.effective_end_date = hr_api.g_eot
624 --
625 -- Bug 5127698 : Due to following date check, dependents do not get carry forwarded
626 -- when PDP.cvg_strt_dt is later than l_lf_evt_ocrd_dt.
627 -- Above date check(EOT) should be sufficient.
628 --and l_lf_evt_ocrd_dt
629 -- between pdp.cvg_strt_dt and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
630 -- Instead of above check, added following check which will make sure that
631 -- end dated dependents will not get carry forwarded
632 and nvl(pdp.cvg_thru_dt,hr_api.g_eot) >= l_lf_evt_ocrd_dt
633 and nvl(pdp.cvg_thru_dt,hr_api.g_eot) >= pdp.cvg_strt_dt
634 and pil.per_in_ler_id=pdp.per_in_ler_id
635 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
636 --
637 cursor c_dcln_pl_opt is
638 select decode(pln.invk_dcln_prtn_pl_flag,
639 'N', nvl(opt.invk_wv_opt_flag,'N'),
640 'Y') dcln_pl_opt_flag
641 from ben_pl_f pln,
642 ben_oipl_f oipl,
643 ben_opt_f opt
644 where pln.pl_id = p_pl_id
645 and oipl.oipl_id (+) = p_oipl_id
646 and oipl.pl_id (+) = pln.pl_id
647 and oipl.opt_id = opt.opt_id (+)
648 and l_lf_evt_ocrd_dt
649 between pln.effective_start_date and pln.effective_end_date
650 and l_lf_evt_ocrd_dt
651 between oipl.effective_start_date(+) and oipl.effective_end_date(+)
652 and l_lf_evt_ocrd_dt
653 between opt.effective_start_date(+) and opt.effective_end_date(+);
654 --
655 cursor c_dpnt_exist (p_per_in_ler_id number,
656 p_elig_per_id number,
657 p_elig_per_opt_id number,
658 p_dpnt_person_id number) is
659 select null
660 from ben_elig_dpnt egd
661 where egd.dpnt_person_id = p_dpnt_person_id
662 and egd.per_in_ler_id = p_per_in_ler_id
663 and egd.elig_per_id = p_elig_per_id
664 and (p_elig_per_opt_id is null or
665 egd.elig_per_opt_id = p_elig_per_opt_id);
666 --
667 l_pdp c_pdp%rowtype;
668 l_ass_rec per_all_assignments_f%rowtype;
669 l_egd_rec ben_elig_dpnt%rowtype;
670 l_egd_rec_found boolean := false;
671 l_pdp_rec_found boolean := false;
672 l_elig_dpnt_id number;
673 l_elig_per_id number;
674 l_elig_per_opt_id number;
675 l_egd_object_version_number number(9);
676 l_dummy varchar2(1);
677 l_dsgn_rqmt_level number;
678 l_dpnt_cvg_strt_dt date;
679 l_next_row number;
680 l_sysdate date;
681 l_decline_flag varchar2(30) := 'N';
682 --
683 l_elig_per_elctbl_chc_id number;
684 l_per_in_ler_id number;
685
686 l_env ben_env_object.g_global_env_rec_type;
687 l_benmngle_parm_rec benutils.g_batch_param_rec; /* Bug 5936849 */
688 l_effective_date date ;
689 l_contact_date_end date;
690 --
691 begin
692 --
693 g_debug := hr_utility.debug_enabled;
694 --
695 -- Set sysdate to a local
696 --
697 if g_debug then
698 hr_utility.set_location ('Entering '||l_proc,10);
699 end if;
700 --
701 if g_debug then
702 hr_utility.set_location ('Fonm '|| ben_manage_life_events.fonm ,10);
703 hr_utility.set_location ('Fonm '|| ben_manage_life_events.g_fonm_cvg_strt_dt ,10);
704 end if;
705 l_sysdate := sysdate;
706 -- fonm
707 l_lf_evt_ocrd_dt := nvl(p_lf_evt_ocrd_dt, p_effective_date);
708 if ben_manage_life_events.fonm = 'Y'
709 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
710 --
711 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
712 l_lf_evt_ocrd_dt := nvl(l_fonm_cvg_strt_dt,l_lf_evt_ocrd_dt ) ;
713
714 --
715 hr_utility.set_location ('Fonm Date '||l_lf_evt_ocrd_dt ,10);
716 end if;
717
718 --
719 if g_debug then
720 hr_utility.set_location ('pl '||p_pl_id,10);
721 hr_utility.set_location ('plip '||p_plip_id,10);
722 hr_utility.set_location ('pgm '||p_pgm_id,10);
723 end if;
724 --
725 g_egd_table.delete;
726 g_egd_table := g_egd_table_temp;
727 --
728 if p_oipl_id is not null then
729 open c_opt;
730 fetch c_opt into l_opt;
731 close c_opt;
732 end if;
733 -- Determine designation level
734 --
735 if g_debug then
736 hr_utility.set_location ('Determining designation level '||l_proc,30);
737 end if;
738 -- fix made for 1192368 reversed - bug#3238951
739 -- start fix 1192368
740 -- while finding out the dependency designation level we should first see
741 -- if it has been defined at plan level and then check the level at program
742 -- level to see if it is PGM / PTIP
743 -- Moved the cursor code of c_plan from below to here to check if
744 -- pl.dpnt_dsgn_cd is null or not
745 --
746 /*
747 open c_plan;
748 --
749 fetch c_plan into l_plan;
750 if c_plan%notfound then
751 --
752 close c_plan;
753 fnd_message.set_name('BEN','BEN_91472_PLAN_NOT_FOUND');
754 fnd_message.set_token('PROC',l_proc);
755 fnd_message.set_token('PL_ID',to_char(p_pl_id));
756 fnd_message.raise_error;
757 --
758 end if;
759 --
760 close c_plan;
761 */
762 --
763 -- end fix 1192368
764 -- modified the if condn below to check for l_plan.dpnt_dsgn_cd before
765 -- going into pgm level check.
766 --
767 if p_pgm_id is not null then
768 --
769 -- find the level from the program
770 --
771 open c_pgm;
772 --
773 fetch c_pgm into l_pgm;
774 --
775 if c_pgm%notfound then
776 --
777 close c_pgm;
778 fnd_message.set_name('BEN','BEN_91470_PGM_NOT_FOUND');
779 fnd_message.set_token('PROC',l_proc);
780 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
781 fnd_message.raise_error;
782 --
783 end if;
784 --
785 close c_pgm;
786 --
787 l_level := l_pgm.dpnt_dsgn_lvl_cd;
788 --
789 else
790 --
791 -- PLAN level
792 --
793 l_level := 'PL';
794 --
795 end if;
796 --
797 --Bug 2189561 in case of plan in program when the dpnt_dsgn_lvl_cd is null at plan level
798 -- and user defines ONLY in plan enrollment requirements, it fails.
799 --To resolve the issue, we are resetting to PL if it is null
800 --
801 if l_level is null then
802 --
803 l_level := 'PL';
804 --
805 end if;
806 --
807 -- Retrieve designation code
808 --
809 if g_debug then
810 hr_utility.set_location ('Level = '||l_level,40);
811 end if;
812 --
813 if l_level = 'PGM' then
814 --
815 l_code := l_pgm.dpnt_dsgn_cd;
816 --
817 elsif l_level = 'PTIP' then
818 --
819 open c_ptip;
820 --
821 fetch c_ptip into l_ptip;
822 --
823 if c_ptip%notfound then
824 --
825 close c_ptip;
826 fnd_message.set_name('BEN','BEN_91471_MISSING_PLAN_TYPE');
827 fnd_message.set_token('PROC',l_proc);
828 fnd_message.set_token('PTIP_ID',to_char(p_ptip_id));
829 fnd_message.raise_error;
830 --
831 end if;
832 --
833 close c_ptip;
834 --
835 l_code := l_ptip.dpnt_dsgn_cd;
836 --
837 elsif l_level = 'PL' then
838 --
839 open c_plan;
840 fetch c_plan into l_plan;
841 if c_plan%notfound then
842 --
843 close c_plan;
844 fnd_message.set_name('BEN','BEN_91472_PLAN_NOT_FOUND');
845 fnd_message.set_token('PROC',l_proc);
846 fnd_message.set_token('PL_ID',to_char(p_pl_id));
847 fnd_message.raise_error;
848 --
849 end if;
850 --
851 close c_plan;
852 --
853 l_code := l_plan.dpnt_dsgn_cd;
854 --
855 else
856 --
857 l_code := NULL;
858 l_process_flag := 'N';
859 --
860 end if;
861 --
862 if g_debug then
863 hr_utility.set_location ('l_code '||l_code,50);
864 hr_utility.set_location ('l_level '||l_level,50);
865 hr_utility.set_location( 'l_process_flag ' || l_process_flag , 50) ;
866 end if;
867 --
868 -- Check whether the choice is for Decline Plan/Option. If so,
869 -- no need to create eligible dependents for them.
870 --
871 open c_dcln_pl_opt;
872 fetch c_dcln_pl_opt into l_decline_flag;
873 close c_dcln_pl_opt;
874 --
875 if l_decline_flag = 'Y' then
876 l_process_flag := 'N';
877 --bug#3238951 - assign Optional to dependent designation
878 l_code := 'O';
879 end if;
880 --
881 -- Does this life event support dependent changes?
882 --
883 if g_debug then
884 hr_utility.set_location ('Determining life event support '||l_proc,50);
885 hr_utility.set_location ('Process Flag '||l_process_flag,50);
886 end if;
887 --
888 if l_process_flag = 'Y' then
889 --
890 open c_per_in_ler;
891 --
892 fetch c_per_in_ler into l_per_in_ler;
893 --
894 if c_per_in_ler%notfound then
895 --
896 close c_per_in_ler;
897 fnd_message.set_name('BEN','BEN_91473_PER_NOT_FOUND');
898 fnd_message.set_token('PROC',l_proc);
899 /* fnd_message.set_token('PER_IN_LER_ID',
900 to_char(l_elctbl_chc.per_in_ler_id)); */
901 fnd_message.raise_error;
902 --
903 end if;
904 --
905 close c_per_in_ler;
906 -- before ler change find out the pl type id # 2508745
907
908 open c_pl_typ ;
909 fetch c_pl_typ into l_pl_typ_id ;
910 close c_pl_typ ;
911 --
912 if g_debug then
913 hr_utility.set_location( ' l_pl_typ_id ' || l_pl_typ_id , 40) ;
914 hr_utility.set_location( ' l_process_flag ' || l_process_flag , 40) ;
915 end if;
916 --
917 open c_ler_chg_dep(l_level);
918 --
919 fetch c_ler_chg_dep into l_ler_chg_dep;
920 --
921 if c_ler_chg_dep%notfound then
922 --
923 l_process_flag := 'N';
924 --
925 if g_debug then
926 hr_utility.set_location( ' ler chagne l_process_flag ' || l_process_flag , 40) ;
927 end if;
928 else
929 --
930 l_ler_chg_dpnt_cvg_cd := l_ler_chg_dep.ler_chg_dpnt_cvg_cd;
931 --
932 if l_ler_chg_dpnt_cvg_cd = 'RL' then
933 --
934 ben_person_object.get_object(p_person_id => l_per_in_ler.person_id,
935 p_rec => l_ass_rec);
936 --
937 if l_ass_rec.assignment_id is null then
938 --
939 ben_person_object.get_benass_object
940 (p_person_id => l_per_in_ler.person_id,
941 p_rec => l_ass_rec);
942 --
943 end if;
944 --
945 if l_ass_rec.location_id is not null then
946 --
947 ben_location_object.get_object
948 (p_location_id => l_ass_rec.location_id,
949 p_rec => l_loc_rec);
950 --
951 --if l_loc_rec.region_2 is not null then
952 -- l_jurisdiction_code := pay_mag_utils.lookup_jurisdiction_code
953 -- (p_state => l_loc_rec.region_2);
954 --end if ;
955 --
956 end if;
957 --
958 l_outputs := benutils.formula
959 (p_formula_id => l_ler_chg_dep.ler_chg_dpnt_cvg_rl,
960 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
961 p_assignment_id => l_ass_rec.assignment_id,
962 p_organization_id => l_ass_rec.organization_id,
963 p_business_group_id => p_business_group_id,
964 p_pgm_id => p_pgm_id,
965 p_pl_id => p_pl_id,
966 p_pl_typ_id => p_pl_typ_id,
967 p_opt_id => l_opt.opt_id,
968 p_ler_id => l_per_in_ler.ler_id,
969 p_elig_per_elctbl_chc_id => null,
970 p_jurisdiction_code => l_jurisdiction_code,
971 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
972 p_param1_value => to_char(p_person_id));
973 --
974 l_ler_chg_dpnt_cvg_cd := l_outputs(l_outputs.first).value;
975 --
976 end if;
977 --
978 if l_ler_chg_dpnt_cvg_cd in ('MNANRD', 'N') then
979 --
980 l_process_flag := 'N';
981 --
982 end if;
983 hr_utility.set_location( ' ler chagne l_process_flag ' || l_process_flag , 50) ;
984
985 if l_ler_chg_dpnt_cvg_cd in ('MNANRD', 'N','MRD') then
986 --
987 -- If the below flag is true then process the dependents
988 -- as if process_flag='Y' but update choice to be not
989 -- designatable.
990 --
991 l_mnanrd_condition:=true;
992 hr_utility.set_location ('mnanrd set',59);
993 --
994 end if;
995 --
996 end if;
997 --
998 close c_ler_chg_dep;
999 --
1000 end if;
1001 --
1002 if g_debug then
1003 hr_utility.set_location ('Checking comp object dep allowed '||l_proc,60);
1004 hr_utility.set_location ('Process Flag '||l_process_flag,50);
1005 end if;
1006 --
1007 if l_process_flag = 'Y' then
1008 hr_utility.set_location ('in this if',50);
1009 --
1010 -- See if we can find designation requirements ben_dsgn_rqmt_f
1011 -- If designation requirments does not exist, than any number
1012 -- or type of dependents are allowed. If designation requirements
1013 -- are found, then the min and max cannot be 0(Employee Only option).
1014 --
1015 open c_dsgn;
1016 --
1017 fetch c_dsgn into l_dsgn_rqmt_level,
1018 l_mn_dpnts_rqd_num,
1019 l_mx_dpnts_alwd_num,
1020 l_no_mn_num_dfnd_flag;
1021 --
1022 if c_dsgn%found then
1023 --
1024 -- Task 133 : Interim coverage cannot be assigned when in
1025 -- insufficient number of dependents have been designated
1026 -- to a participant enrollment. In this case, the current
1027 -- product will end the participant's current coverage.
1028 -- To have above functionality, if no dependent designation
1029 -- is required if l_mn_dpnts_rqd_num is 0 or l_no_mn_num_dfnd_flag
1030 -- is Y.
1031 --
1032 if nvl(l_mn_dpnts_rqd_num, -1) = 0 and
1033 nvl(l_mx_dpnts_alwd_num, -1) = 0 and
1034 l_no_mn_num_dfnd_flag = 'N' then
1035 -- 5152062 : Set process_flag to 'N' , only if if explicitly set l_mn_dpnts_rqd_num to 'Y'.
1036 --
1037 hr_utility.set_location ('dsgn not found',50);
1038 l_process_flag := 'N';
1039 --
1040 end if;
1041 --
1042 if nvl(l_mn_dpnts_rqd_num, -1) = 0
1043 -- 5152062 : Do not need the below condition.
1044 -- OR l_no_mn_num_dfnd_flag = 'Y'
1045 then
1046 --
1047 l_code := 'O';
1048 --
1049 end if;
1050 end if;
1051 --
1052 close c_dsgn;
1053
1054 end if;
1055 --
1056 if g_debug then
1057 hr_utility.set_location ('Process Flag '||l_process_flag,50);
1058 end if;
1059 --
1060 -- Determine coverage dates
1061 --
1062 if l_process_flag = 'Y' or l_mnanrd_condition then
1063 --
1064 hr_utility.set_location ('Determining coverage dates '||l_proc,70);
1065 --
1066 l_cvg_strt_cd := l_ler_chg_dep.cvg_eff_strt_cd;
1067 l_cvg_strt_rl := l_ler_chg_dep.cvg_eff_strt_rl;
1068 l_cvg_end_cd := l_ler_chg_dep.cvg_eff_end_cd;
1069 l_cvg_end_rl := l_ler_chg_dep.cvg_eff_end_rl;
1070 --
1071 if l_cvg_strt_cd is null and l_cvg_strt_rl is null then
1072 --
1073 if l_level ='PL' then
1074 --
1075 l_cvg_strt_cd := l_plan.dpnt_cvg_strt_dt_cd;
1076 l_cvg_strt_rl := l_plan.dpnt_cvg_strt_dt_rl;
1077 l_cvg_end_cd := l_plan.dpnt_cvg_end_dt_cd;
1078 l_cvg_end_rl := l_plan.dpnt_cvg_end_dt_rl;
1079 --
1080 elsif l_level = 'PTIP' then
1081 --
1082 l_cvg_strt_cd := l_ptip.dpnt_cvg_strt_dt_cd;
1083 l_cvg_strt_rl := l_ptip.dpnt_cvg_strt_dt_rl;
1084 l_cvg_end_cd := l_ptip.dpnt_cvg_end_dt_cd;
1085 l_cvg_end_rl := l_ptip.dpnt_cvg_end_dt_rl;
1086 --
1087 elsif l_level ='PGM' then
1088 --
1089 -- Use program dates if available
1090 --
1091 l_cvg_strt_cd := l_pgm.dpnt_cvg_strt_dt_cd;
1092 l_cvg_strt_rl := l_pgm.dpnt_cvg_strt_dt_rl;
1093 l_cvg_end_cd := l_pgm.dpnt_cvg_end_dt_cd;
1094 l_cvg_end_rl := l_pgm.dpnt_cvg_end_dt_rl;
1095 --
1096 end if;
1097 --
1098 end if;
1099 --
1100 if l_cvg_strt_cd is null then
1101 --
1102 fnd_message.set_name('BEN','BEN_91475_DEPT_ST_DT_NULL');
1103 fnd_message.set_token('PROC',l_proc);
1104 fnd_message.set_token('LER_ID',to_char(l_per_in_ler.ler_id));
1105 fnd_message.raise_error;
1106 --
1107 else
1108 --
1109 IF l_cvg_strt_cd <> 'RL' THEN
1110 --
1111 hr_utility.set_location('cvg_strt_cd'||l_cvg_strt_cd,10);
1112 hr_utility.set_location('cvg_strt_rl'||l_cvg_strt_rl,10);
1113 ben_determine_date.main
1114 (P_DATE_CD => l_cvg_strt_cd,
1115 P_BUSINESS_GROUP_ID => p_business_group_id,
1116 P_PERSON_ID => p_person_id,
1117 P_PGM_ID => p_pgm_id,
1118 P_PL_ID => p_pl_id,
1119 P_OIPL_ID => p_oipl_id,
1120 P_PER_IN_LER_ID => l_per_in_ler.per_in_ler_id,
1121 P_ELIG_PER_ELCTBL_CHC_ID => null,
1122 P_FORMULA_ID => l_cvg_strt_rl,
1123 P_EFFECTIVE_DATE => p_effective_date,
1124 P_LF_EVT_OCRD_DT => p_lf_evt_ocrd_dt,
1125 P_RETURNED_DATE => l_cvg_strt_dt
1126 );
1127 --
1128 END IF;
1129 --
1130 end if;
1131 --
1132 end if;
1133 --
1134 if g_debug then
1135 hr_utility.set_location ('l_Process_flag '||l_process_flag,80);
1136 end if;
1137 --
1138 g_upd_epe_egd_rec.g_process_flag := l_Process_flag;
1139 g_mnanrd_condition:=l_mnanrd_condition;
1140 g_upd_epe_egd_rec.g_code := l_code;
1141 g_upd_epe_egd_rec.g_ler_chg_dpnt_cvg_cd := l_ler_chg_dpnt_cvg_cd;
1142 g_upd_epe_egd_rec.g_cvg_strt_cd := l_cvg_strt_cd;
1143 g_upd_epe_egd_rec.g_cvg_strt_rl := l_cvg_strt_rl;
1144 --
1145 if l_Process_flag = 'Y' or l_mnanrd_condition then
1146 --
1147 -- Loop through potential dependent pool
1148 --
1149 hr_utility.set_location ('open c_contact2 '||l_proc,90);
1150 --
1151 -- Get the enrollment result row.
1152 --
1153 if (p_oipl_id is null) then
1154 --
1155 open c_plan_enrolment_info;
1156 fetch c_plan_enrolment_info into l_prtt_enrt_rslt_id;
1157 close c_plan_enrolment_info;
1158 --
1159 else
1160 --
1161 open c_oipl_enrolment_info;
1162 fetch c_oipl_enrolment_info into l_prtt_enrt_rslt_id;
1163 close c_oipl_enrolment_info;
1164 --
1165 end if;
1166 --
1167 open c_contact;
1168 loop
1169 hr_utility.set_location ('again in loop',50);
1170
1171 --
1172 fetch c_contact into l_contact;
1173 exit when c_contact%notfound;
1174 --
1175 -- 9999 As enrollment result not available,
1176 -- How to find the pdp. Need to derive from the
1177 -- comp object combination. ????
1178 --
1179 -- Following condition is added to fix Bug 1531647 to add inpute value contact_person_id
1180
1181 IF l_cvg_strt_cd = 'RL' THEN
1182 --
1183 hr_utility.set_location('cvg_strt_cd'||l_cvg_strt_cd,10);
1184 hr_utility.set_location('cvg_strt_rl'||l_cvg_strt_rl,10);
1185 ben_determine_date.main
1186 (P_DATE_CD => l_cvg_strt_cd,
1187 P_BUSINESS_GROUP_ID => p_business_group_id,
1188 P_PERSON_ID => p_person_id,
1189 P_PGM_ID => p_pgm_id,
1190 P_PL_ID => p_pl_id,
1191 P_OIPL_ID => p_oipl_id,
1192 P_PER_IN_LER_ID => l_per_in_ler.per_in_ler_id,
1193 P_ELIG_PER_ELCTBL_CHC_ID => null,
1194 P_FORMULA_ID => l_cvg_strt_rl,
1195 P_EFFECTIVE_DATE => p_effective_date,
1196 P_LF_EVT_OCRD_DT => p_lf_evt_ocrd_dt,
1197 P_RETURNED_DATE => l_cvg_strt_dt,
1198 P_PARAM1 => 'CON_PERSON_ID',
1199 P_PARAM1_VALUE => to_char(l_contact.contact_person_id));
1200 --
1201 END IF;
1202 --
1203 open c_pdp;
1204 fetch c_pdp into l_pdp;
1205 if c_pdp%found then
1206 l_pdp_rec_found := true;
1207 l_elig_cvrd_dpnt_id := l_pdp.elig_cvrd_dpnt_id;
1208 l_dpnt_cvg_strt_dt := l_pdp.cvg_strt_dt;
1209 else
1210 l_pdp_rec_found := false;
1211 l_elig_cvrd_dpnt_id := null;
1212 l_dpnt_cvg_strt_dt := null;
1213 end if;
1214 close c_pdp;
1215 --
1216 if l_contact.contact_active_flag='Y' then
1217 --
1218 hr_utility.set_location ('BEDEP_MN '||l_proc,90);
1219 --fonm taken care inside of the code dont pass any date
1220 ben_evaluate_dpnt_elg_profiles.main
1221 (p_contact_relationship_id => l_contact.contact_relationship_id,
1222 p_contact_person_id => l_contact.contact_person_id,
1223 p_pgm_id => p_pgm_id,
1224 p_pl_id => p_pl_id,
1225 p_ptip_id => p_ptip_id,
1226 p_oipl_id => p_oipl_id,
1227 p_business_group_id => p_business_group_id,
1228 p_per_in_ler_id => l_per_in_ler.per_in_ler_id,
1229 p_effective_date => p_effective_date,
1230 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1231 p_dpnt_cvg_strt_dt => l_dpnt_cvg_strt_dt,
1232 p_level => l_level,
1233 p_dependent_eligible_flag => l_elig_flag,
1234 p_dpnt_inelig_rsn_cd => l_inelig_rsn_cd);
1235 hr_utility.set_location ('Dn BEDEP_MN '||l_proc,90);
1236 else
1237 hr_utility.set_location ('contact ended '||l_proc,90);
1238 l_elig_flag:='N';
1239 l_contact_date_end := l_contact.date_end;
1240 end if;
1241 --
1242 hr_utility.set_location ('l_contact.date_of_death '||l_contact.date_of_death,110);
1243 hr_utility.set_location ('l_lf_evt_ocrd_dt '||l_lf_evt_ocrd_dt,110);
1244
1245 -- Bug 7481099
1246 -- if dependent is not already enrolled and if he is dead, set elig flag to N
1247 if not l_pdp_rec_found and nvl(l_contact.date_of_death,hr_api.g_eot) <= l_lf_evt_ocrd_dt then
1248 l_elig_flag := 'N';
1249 hr_utility.set_location ('setting elig_flag to N ',110);
1250 end if;
1251
1252 -- Bug 7481099
1253
1254 if l_pdp_rec_found then
1255 --
1256 -- 9999 is it required? As it is based on pdp.
1257 --
1258 l_egd_rec_found := ben_ELIG_DPNT_api.get_elig_dpnt_rec(
1259 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
1260 p_effective_date => l_lf_evt_ocrd_dt,
1261 p_elig_dpnt_rec => l_egd_rec);
1262 --
1263 else
1264 --
1265 l_egd_rec_found := ben_ELIG_DPNT_api.get_elig_dpnt_rec
1266 (p_pl_id => p_pl_id
1267 ,p_pgm_id => p_pgm_id
1268 ,p_oipl_id => p_oipl_id
1269 ,p_dpnt_person_id => l_contact.contact_person_id
1270 ,p_effective_date => l_lf_evt_ocrd_dt
1271 --
1272 ,p_per_in_ler_id => p_per_in_ler_id
1273 ,p_elig_per_id => p_elig_per_id
1274 ,p_elig_per_opt_id => p_elig_per_opt_id
1275 ,p_opt_id => l_opt.opt_id
1276 --
1277 ,p_elig_dpnt_rec => l_egd_rec
1278 );
1279 --
1280 end if;
1281 hr_utility.set_location ('Dn BEDP_GEDR '||l_proc,90);
1282 --
1283 if l_elig_flag = 'Y' then
1284 --
1285 g_rec.person_id := l_per_in_ler.person_id;
1286 g_rec.pgm_id := p_pgm_id;
1287 g_rec.pl_id := p_pl_id;
1288 g_rec.oipl_id := p_oipl_id;
1289 g_rec.contact_typ_cd := l_contact.contact_type;
1290 g_rec.dpnt_person_id := l_contact.contact_person_id;
1291 g_rec.business_group_id := p_business_group_id;
1292 g_rec.effective_date := p_effective_date;
1293 --
1294 benutils.write(p_rec => g_rec);
1295 --
1296 hr_utility.set_location (' Elig Y BED_GEPID '||l_proc,90);
1297 if p_elig_per_id is not null
1298 then
1299 --
1300 l_elig_per_id := p_elig_per_id;
1301 l_elig_per_opt_id := p_elig_per_opt_id;
1302 --
1303 else
1304 --
1305 ben_ELIG_DPNT_api.get_elig_per_id
1306 (p_person_id => l_per_in_ler.person_id
1307 ,p_pgm_id => p_pgm_id
1308 ,p_pl_id => p_pl_id
1309 ,p_oipl_id => p_oipl_id
1310 ,p_business_group_id => p_business_group_id
1311 ,p_effective_date => l_lf_evt_ocrd_dt
1312 ,p_elig_per_id => l_elig_per_id
1313 ,p_elig_per_opt_id => l_elig_per_opt_id
1314 );
1315 --
1316 end if;
1317 --
1318 hr_utility.set_location (' Elig Y BED_CED '||l_proc,90);
1319 --
1320 -- 3685120 - Get the environment details and if it is U then create EGD
1321 --
1322 ben_env_object.get(p_rec => l_env);
1323 --
1324 -- Bug 5936849
1325 hr_utility.set_location('ACE l_env.mode_cd = ' || l_env.mode_cd, 9999);
1326 hr_utility.set_location('ACE l_env.benefit_action_id = ' || l_env.benefit_action_id, 9999);
1327 --
1328 if l_env.mode_cd is null
1329 then
1330 --
1331 benutils.get_batch_parameters(p_benefit_action_id => l_env.benefit_action_id,
1332 p_rec => l_benmngle_parm_rec
1333 );
1334 --
1335 hr_utility.set_location('ACE l_benmngle_parm_rec.mode_cd = ' || l_benmngle_parm_rec.mode_cd, 9999);
1336 --
1337 l_env.mode_cd := l_benmngle_parm_rec.mode_cd;
1338 --
1339 end if;
1340 --
1341 -- End Bug 5936849
1342
1343 hr_utility.set_location (' env mode cd '||l_env.mode_cd ,90);
1344 --bug#3495592 - cursor to check already created elig_dpnt record
1345 open c_dpnt_exist (p_per_in_ler_id =>l_per_in_ler.per_in_ler_id,
1346 p_elig_per_id => l_elig_per_id,
1347 p_elig_per_opt_id => l_elig_per_opt_id,
1348 p_dpnt_person_id => l_contact.contact_person_id);
1349 fetch c_dpnt_exist into l_dummy;
1350 if c_dpnt_exist%notfound or nvl(l_env.mode_cd,'~') in ('R', 'U') then
1351 if l_env.mode_cd in ('U','R') then
1352 --
1353 hr_utility.set_location ('inside ',11);
1354 /*j
1355 l_elig_dpnt_id := ben_manage_unres_life_events.egd_exists
1356 (p_PER_IN_LER_ID => 1 --l_per_in_ler.per_in_ler_id
1357 ,p_ELIG_PER_ID => 1 --l_elig_per_id
1358 ,p_ELIG_PER_OPT_ID => 1 --l_elig_per_opt_id
1359 ,p_DPNT_PERSON_ID =>1 --l_contact.contact_person_id
1360 );
1361 */
1362 l_elig_dpnt_id := ben_manage_unres_life_events.egd_exists
1363 (p_PER_IN_LER_ID => 1
1364 ,p_ELIG_PER_ID => 1
1365 ,p_ELIG_PER_OPT_ID => 1
1366 ,p_DPNT_PERSON_ID =>1
1367 );
1368 hr_utility.set_location ('after',12);
1369 end if;
1370 if l_elig_dpnt_id is not null then
1371 --
1372 ben_manage_unres_life_events.update_elig_dpnt
1373 (p_elig_dpnt_id => l_elig_dpnt_id
1374 ,p_create_dt => l_lf_evt_ocrd_dt
1375 ,p_business_group_id => p_business_group_id
1376 ,p_elig_per_elctbl_chc_id => null
1377 ,p_dpnt_person_id => l_contact.contact_person_id
1378 ,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
1379 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1380 ,p_elig_strt_dt => nvl(l_egd_rec.elig_strt_dt,
1381 nvl(p_lf_evt_ocrd_dt,
1382 p_effective_date))
1383 ,p_elig_thru_dt => hr_api.g_eot
1384 ,p_elig_per_id => l_elig_per_id
1385 ,p_elig_per_opt_id => l_elig_per_opt_id,
1386 p_ovrdn_flag => nvl(l_egd_rec.ovrdn_flag,'N')
1387 ,p_ovrdn_thru_dt => l_egd_rec.ovrdn_thru_dt
1388 ,p_object_version_number => l_egd_object_version_number
1389 ,p_effective_date => p_effective_date
1390 ,p_program_application_id => fnd_global.prog_appl_id
1391 ,p_program_id => fnd_global.conc_program_id
1392 ,p_request_id => fnd_global.conc_request_id
1393 ,p_program_update_date => l_sysdate
1394 );
1395 --
1396 else
1397 ben_elig_dpnt_api.create_perf_elig_dpnt
1398 (p_elig_dpnt_id => l_elig_dpnt_id
1399 ,p_create_dt => l_lf_evt_ocrd_dt
1400 ,p_business_group_id => p_business_group_id
1401 ,p_elig_per_elctbl_chc_id => null
1402 ,p_dpnt_person_id => l_contact.contact_person_id
1403 ,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
1404 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1405 ,p_elig_strt_dt => nvl(l_egd_rec.elig_strt_dt,
1406 nvl(p_lf_evt_ocrd_dt,
1407 p_effective_date))
1408 ,p_elig_thru_dt => hr_api.g_eot
1409 ,p_elig_per_id => l_elig_per_id
1410 ,p_elig_per_opt_id => l_elig_per_opt_id,
1411 p_ovrdn_flag => nvl(l_egd_rec.ovrdn_flag,'N')
1412 ,p_ovrdn_thru_dt => l_egd_rec.ovrdn_thru_dt
1413 ,p_object_version_number => l_egd_object_version_number
1414 ,p_effective_date => p_effective_date
1415 ,p_program_application_id => fnd_global.prog_appl_id
1416 ,p_program_id => fnd_global.conc_program_id
1417 ,p_request_id => fnd_global.conc_request_id
1418 ,p_program_update_date => l_sysdate
1419 );
1420 --
1421 end if;
1422
1423 hr_utility.set_location ('Dn Elig Y '||l_proc,90);
1424
1425 --
1426 l_next_row := nvl(g_egd_table.LAST, 0) + 1;
1427 g_egd_table(l_next_row).object_version_number := l_egd_object_version_number;
1428 g_egd_table(l_next_row).elig_dpnt_id := l_elig_dpnt_id;
1429 l_elig_dpnt_id := null ; -- 4051409 - regression
1430
1431 end if;
1432 close c_dpnt_exist;
1433 --
1434 else
1435 --
1436 -- Only update the coverage end date if dependent is currently
1437 -- ineligible.
1438 --
1439 -- jcarpent bug fix for bug 1524099 below.
1440 -- added nvl below.
1441 -- jcarpent bug fix for 2147682 , merged by tilak
1442 if nvl(l_egd_rec.dpnt_inelig_flag,'N') = 'N' and l_pdp_rec_found = true then
1443 --
1444 -- Get coverage end date
1445 --
1446 if l_cvg_end_cd is null then
1447 --
1448 fnd_message.set_name('BEN','BEN_91478_INVALID_DEP_ENDDT');
1449 fnd_message.set_token('PROC',l_proc);
1450 fnd_message.set_token('LER_ID',to_char(l_per_in_ler.ler_id));
1451 fnd_message.raise_error;
1452 --
1453 else
1454 --
1455 hr_utility.set_location('l_cvg_end_cd'||l_cvg_end_Cd,10);
1456 hr_utility.set_location('l_cvg_end_rl'||l_cvg_end_rl,10);
1457 hr_utility.set_location('l_contact.date_end '|| l_contact.date_end,10);
1458 hr_utility.set_location('l_lf_evt_ocrd_dt '|| l_lf_evt_ocrd_dt,10);
1459 hr_utility.set_location('get_elig_change_dt '|| ben_evaluate_dpnt_elg_profiles.get_elig_change_dt,10);
1460 --
1461 -- 4697057 : If relationship is end-dated, determine end-date as of
1462 -- relationship_end, rather than ben_evaluate_dpnt_elg_profiles.get_elig_change_dt,
1463 -- since ben_evaluate_dpnt_elg_profiles.set_elig_change_dt is never
1464 -- called in this case..
1465 --9443647
1466 if (l_contact.date_end < l_lf_evt_ocrd_dt) then
1467 l_contact_date_end := l_contact.date_end + 1;
1468 else
1469 l_contact_date_end := ben_evaluate_dpnt_elg_profiles.get_elig_change_dt + 1;
1470 end if;
1471 --
1472 --
1473 ben_determine_date.main
1474 (P_DATE_CD => l_cvg_end_cd,
1475 P_BUSINESS_GROUP_ID => p_business_group_id,
1476 P_PERSON_ID => p_person_id,
1477 P_PGM_ID => p_pgm_id,
1478 P_PL_ID => p_pl_id,
1479 P_OIPL_ID => p_oipl_id,
1480 P_PER_IN_LER_ID => l_per_in_ler.per_in_ler_id,
1481 P_ELIG_PER_ELCTBL_CHC_ID => null,
1482 P_FORMULA_ID => l_cvg_end_rl,
1483 P_EFFECTIVE_DATE => p_effective_date,
1484 P_LF_EVT_OCRD_DT => l_contact_date_end, -- 4697057
1485 P_RETURNED_DATE => l_cvg_end_dt,
1486 P_PARAM1 => 'CON_PERSON_ID',
1487 P_PARAM1_VALUE => to_char(l_contact.contact_person_id));
1488 --
1489 end if;
1490 hr_utility.set_location ('Dn Elig N BENDETDT '||l_proc,90);
1491 --
1492 -- According to the cursor, the coverage has started as of the
1493 -- effective date, so the coverage through date should atleast be
1494 -- the coverge start date. If the coverage through date is less
1495 -- than the coverage start date, we assign a value of effective
1496 -- date.
1497 --
1498 if l_cvg_end_dt < l_pdp.cvg_strt_dt then
1499 --
1500 l_cvg_end_dt := l_LF_EVT_OCRD_DT;
1501 --
1502 end if;
1503 --
1504 if l_pdp_rec_found then
1505 --
1506 --bug#4658173
1507 if nvl(p_lf_evt_ocrd_dt,p_effective_date) < l_pdp.effective_start_date then
1508 l_effective_date := l_pdp.effective_start_date;
1509 else
1510 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
1511 end if;
1512 -- Check datetrack mode.
1513 --
1514 dt_api.find_dt_upd_modes
1515 (p_effective_date => l_effective_date,
1516 p_base_table_name => 'BEN_ELIG_CVRD_DPNT_F',
1517 p_base_key_column => 'elig_cvrd_dpnt_id',
1518 p_base_key_value => l_elig_cvrd_dpnt_id,
1519 p_correction => l_correction,
1520 p_update => l_update,
1521 p_update_override => l_update_override,
1522 p_update_change_insert => l_update_change_insert);
1523 --
1524 if l_update_override then
1525 --
1526 l_datetrack_mode := hr_api.g_update_override;
1527 --
1528 elsif l_update then
1529 --
1530 l_datetrack_mode := hr_api.g_update;
1531 --
1532 else
1533 --
1534 l_datetrack_mode := hr_api.g_correction;
1535 --
1536 end if;
1537 hr_utility.set_location ('datetrack_mode '||l_datetrack_mode,10);
1538 --
1539 -- Update the eligible dependent record
1540 --
1541 hr_utility.set_location (' Elig N BECD_UECD '||l_proc,90);
1542 hr_utility.set_location (' l_cvg_thru_dt '||l_cvg_end_dt,90);
1543 --
1544 ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
1545 (p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
1546 ,p_effective_start_date => l_pdp_effective_start_date
1547 ,p_effective_end_date => l_pdp_effective_end_date
1548 ,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
1549 ,p_cvg_thru_dt => l_cvg_end_dt
1550 ,p_object_version_number => l_pdp.object_version_number
1551 ,p_effective_date => l_effective_date
1552 ,p_datetrack_mode => l_datetrack_mode
1553 ,p_program_application_id => fnd_global.prog_appl_id
1554 ,p_program_id => fnd_global.conc_program_id
1555 ,p_request_id => fnd_global.conc_request_id
1556 ,p_program_update_date => l_sysdate
1557 ,p_business_group_id => p_business_group_id
1558 ,p_multi_row_actn => FALSE
1559 );
1560 --
1561 -- RCHASE - update elig_dpnt row when coverage is lost due to ineligibility
1562 if l_egd_rec.dpnt_inelig_flag = 'N' then
1563 --
1564 hr_utility.set_location(' Set Dpnt InElig ' || (l_contact_date_end -1), 10);
1565 hr_utility.set_location(' l_egd_rec.object_version_number ' || l_egd_rec.object_version_number, 10);
1566 --
1567 ben_elig_dpnt_api.update_elig_dpnt(
1568 p_elig_dpnt_id => l_egd_rec.elig_dpnt_id
1569 ,p_object_version_number => l_egd_rec.object_version_number
1570 ,p_effective_date => nvl(l_lf_evt_ocrd_dt,p_effective_date)
1571 ,p_elig_thru_dt => (l_contact_date_end-1) --l_cvg_end_dt 5100008
1572 ,p_dpnt_inelig_flag => 'Y'
1573 ,p_inelg_rsn_cd => l_inelig_rsn_cd
1574 );
1575 --
1576 end if;
1577 --
1578 -- Update global variable so a benefit assignment can
1579 -- be written.
1580 --
1581 hr_utility.set_location ('dpnt ineligible ',90);
1582 g_dpnt_ineligible := true;
1583 --
1584 end if;
1585 --
1586 if l_egd_rec_found then
1587 --
1588 hr_utility.set_location (' Elig N BED_UED '||l_proc,90);
1589 --
1590 ben_elig_dpnt_api.update_perf_elig_dpnt
1591 (p_elig_dpnt_id => l_egd_rec.elig_dpnt_id
1592 ,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
1593 ,p_elig_thru_dt => (l_contact_date_end-1)--nvl(l_cvg_end_dt,p_effective_date) 5100008
1594 ,p_dpnt_inelig_flag => 'Y'
1595 ,p_inelg_rsn_cd => l_inelig_rsn_cd
1596 ,p_object_version_number => l_egd_rec.object_version_number
1597 ,p_effective_date => nvl(l_lf_evt_ocrd_dt,p_effective_date)
1598 ,p_program_application_id => fnd_global.prog_appl_id
1599 ,p_program_id => fnd_global.conc_program_id
1600 ,p_request_id => fnd_global.conc_request_id
1601 ,p_program_update_date => l_sysdate
1602 );
1603 --
1604 end if;
1605 --
1606 hr_utility.set_location ('Dn Elig N '||l_proc,90);
1607 end if;
1608 end if; -- elig_flag = 'Y'
1609 --
1610 hr_utility.set_location ('End Con Loop '||l_proc,90);
1611 end loop;
1612 --
1613 close c_contact;
1614 hr_utility.set_location ('close c_contact '||l_proc,90);
1615 --
1616 end if;
1617 --
1618 if g_debug then
1619 hr_utility.set_location ('Leaving '||l_proc,80);
1620 end if;
1621 end;
1622 --
1623 -- Task 131 (pbodla): Elig dependent records are created soon after
1624 -- person is found eligible(In benmngle.pkb). Then electable
1625 -- coice record is created in bendenrr.pkb. This procedure
1626 -- links all the elig dependent rows and electable choice row.
1627 -- All elig dependent rows which belongs to a electable choice
1628 -- row are stored in g_egd_table, Also g_upd_epe_egd_rec record
1629 -- holds the information required to update electable choice row.
1630 -- Both g_egd_table, g_upd_epe_egd_rec are populated by bendepen.pkb
1631 --
1632 procedure p_upd_egd_with_epe_id
1633 (p_elig_per_elctbl_chc_id in number
1634 ,p_person_id in number
1635 ,p_effective_date in date
1636 ,p_lf_evt_ocrd_dt in date
1637 )
1638 is
1639 --
1640 l_proc varchar2(80):= g_package||'.p_upd_egd_with_epe_id';
1641 l_chc_id number(15);
1642 l_elctbl_flag ben_elig_per_elctbl_chc.elctbl_flag%TYPE;
1643 --
1644 -- Formula stuff
1645 --
1646 l_outputs ff_exec.outputs_t;
1647 l_return varchar2(30);
1648 /*
1649 --
1650 -- Define cursors
1651 --
1652 cursor c_elctbl_chc is
1653 select chc.elig_per_elctbl_chc_id,
1654 chc.object_version_number,
1655 chc.business_group_id,
1656 chc.per_in_ler_id,
1657 chc.pgm_id,
1658 chc.pl_id,
1659 chc.pl_typ_id,
1660 chc.ptip_id,
1661 chc.oipl_id,
1662 chc.prtt_enrt_rslt_id,
1663 chc.elctbl_flag,
1664 pel.enrt_perd_id
1665 from ben_elig_per_elctbl_chc chc,
1666 ben_pil_elctbl_chc_popl pel
1667 where chc.elig_per_elctbl_chc_id = l_chc_id
1668 and chc.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id;
1669 --
1670 l_elctbl_chc c_elctbl_chc%rowtype;
1671 */
1672 --
1673 l_elctbl_chc ben_epe_cache.g_pilepe_inst_row;
1674 --
1675 cursor c_per_in_ler is
1676 select pil.ler_id,
1677 pil.person_id,
1678 pil.lf_evt_ocrd_dt
1679 from ben_per_in_ler pil
1680 where pil.per_in_ler_id = l_elctbl_chc.per_in_ler_id
1681 and pil.business_group_id = l_elctbl_chc.business_group_id
1682 and pil.per_in_ler_stat_cd = 'STRTD';
1683 --
1684 l_per_in_ler c_per_in_ler%rowtype;
1685 l_sysdate date;
1686 --
1687 begin
1688 --
1689 g_debug := hr_utility.debug_enabled;
1690 --
1691 hr_utility.set_location ('Entering '||l_proc,10);
1692 --
1693 -- Set sysdate to a local
1694 --
1695 l_sysdate := sysdate;
1696 --
1697 -- get the electable choice
1698 --
1699 hr_utility.set_location('Elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,10);
1700 l_chc_id := p_elig_per_elctbl_chc_id;
1701 --
1702 -- Check that current comp object loop EPE row is set
1703 --
1704 if ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id is not null then
1705 --
1706 l_elctbl_chc.elig_per_elctbl_chc_id := ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id;
1707 l_elctbl_chc.pl_id := ben_epe_cache.g_currcobjepe_row.pl_id;
1708 l_elctbl_chc.plip_id := ben_epe_cache.g_currcobjepe_row.plip_id;
1709 l_elctbl_chc.oipl_id := ben_epe_cache.g_currcobjepe_row.oipl_id;
1710 l_elctbl_chc.elctbl_flag := ben_epe_cache.g_currcobjepe_row.elctbl_flag;
1711 l_elctbl_chc.per_in_ler_id := ben_epe_cache.g_currcobjepe_row.per_in_ler_id;
1712 l_elctbl_chc.business_group_id := ben_epe_cache.g_currcobjepe_row.business_group_id;
1713 l_elctbl_chc.object_version_number := ben_epe_cache.g_currcobjepe_row.object_version_number;
1714 --
1715 else
1716 --
1717 return;
1718 --
1719 end if;
1720 --
1721 /*
1722 open c_elctbl_chc;
1723 --
1724 fetch c_elctbl_chc into l_elctbl_chc;
1725 if c_elctbl_chc%notfound then
1726 --
1727 -- If no choice found, no need to go ahead, just return back.
1728 --
1729 close c_elctbl_chc;
1730 return;
1731 --
1732 else
1733 --
1734 close c_elctbl_chc;
1735 --
1736 end if;
1737 --
1738 */
1739 l_elctbl_flag := l_elctbl_chc.elctbl_flag;
1740 --
1741 -- Does this life event support dependent changes?
1742 --
1743 open c_per_in_ler;
1744 --
1745 fetch c_per_in_ler into l_per_in_ler;
1746 --
1747 if c_per_in_ler%notfound then
1748 --
1749 close c_per_in_ler;
1750 fnd_message.set_name('BEN','BEN_91473_PER_NOT_FOUND');
1751 fnd_message.set_token('PROC',l_proc);
1752 fnd_message.set_token('PER_IN_LER_ID',
1753 to_char(l_elctbl_chc.per_in_ler_id));
1754 fnd_message.raise_error;
1755 --
1756 end if;
1757 --
1758 close c_per_in_ler;
1759 --
1760 hr_utility.set_location ('Updating electable choice '||l_proc,80);
1761 hr_utility.set_location ('g_Process_flag '||g_upd_epe_egd_rec.g_process_flag,80);
1762 --
1763 if g_upd_epe_egd_rec.g_process_flag <> 'Y' then
1764 --
1765 hr_utility.set_location ('l_elctbl_flag '||l_elctbl_flag,40);
1766 hr_utility.set_location ('pl_id '||l_elctbl_chc.pl_id,50);
1767 hr_utility.set_location ('oipl_id '||l_elctbl_chc.oipl_id,50);
1768 -- Also must update g_code
1769 -- p_allws_dpnt_dsgn_flag will go away and be replaced by
1770 -- 1. Designation code => g_code
1771 -- 2. LER chg dep cd => g_ler_chg_dpnt_cvg_cd
1772 -- 3. Allows dep cvg flag => g_process_flag
1773 --
1774 -- Update the allows_dpnt_dsgn_flag to 'N'
1775 --
1776 hr_utility.set_location ('BEEPEAPI_UPD '||l_proc,80);
1777 ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
1778 (p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
1779 p_alws_dpnt_dsgn_flag => 'N',
1780 p_object_version_number => l_elctbl_chc.object_version_number,
1781 p_effective_date => p_effective_date,
1782 p_dpnt_dsgn_cd => g_upd_epe_egd_rec.g_code,
1783 p_ler_chg_dpnt_cvg_cd => g_upd_epe_egd_rec.g_ler_chg_dpnt_cvg_cd,
1784 p_program_application_id => fnd_global.prog_appl_id,
1785 p_program_id => fnd_global.conc_program_id,
1786 p_request_id => fnd_global.conc_request_id,
1787 p_program_update_date => l_sysdate,
1788 p_dpnt_cvg_strt_dt_cd => g_upd_epe_egd_rec.g_cvg_strt_cd ,
1789 p_dpnt_cvg_strt_dt_rl => g_upd_epe_egd_rec.g_cvg_strt_rl );
1790 hr_utility.set_location ('Dn BEEPEAPI_UPD '||l_proc,80);
1791 --
1792 else
1793 --
1794 -- Update the allows_dpnt_dsgn_flag
1795 --
1796 -- Not sure at this time if we should be storing
1797 -- Codes, dates, rules, or other codes
1798 -- Now writing dsgn_cd and cvg_cd
1799 --
1800 hr_utility.set_location ('BEEPEAPI_UPD 1 '||l_proc,80);
1801 ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
1802 (p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
1803 p_alws_dpnt_dsgn_flag => 'Y',
1804 p_object_version_number => l_elctbl_chc.object_version_number,
1805 p_effective_date => p_effective_date,
1806 p_dpnt_dsgn_cd => g_upd_epe_egd_rec.g_code,
1807 p_ler_chg_dpnt_cvg_cd => g_upd_epe_egd_rec.g_ler_chg_dpnt_cvg_cd,
1808 p_program_application_id => fnd_global.prog_appl_id,
1809 p_program_id => fnd_global.conc_program_id,
1810 p_request_id => fnd_global.conc_request_id,
1811 p_program_update_date => l_sysdate,
1812 p_dpnt_cvg_strt_dt_cd => g_upd_epe_egd_rec.g_cvg_strt_cd ,
1813 p_dpnt_cvg_strt_dt_rl => g_upd_epe_egd_rec.g_cvg_strt_rl );
1814 --
1815 hr_utility.set_location ('Dn BEEPEAPI_UPD 1 '||l_proc,80);
1816 end if;
1817 if g_upd_epe_egd_rec.g_process_flag = 'Y' or g_mnanrd_condition then
1818 hr_utility.set_location ('BEEGDAPI_UPD Loop '||l_proc,80);
1819 --
1820 -- Now update all the elig dpnt rows with epe id.
1821 --
1822 if nvl(g_egd_table.last, 0) > 0 then
1823 for l_curr_count in g_egd_table.first..g_egd_table.last
1824 loop
1825 hr_utility.set_location ('St BEEGDAPI_UPD loop '||l_proc,80);
1826 --
1827 -- Update the egd row with electable choice id.
1828 --
1829 ben_elig_dpnt_api.update_perf_elig_dpnt
1830 (p_elig_dpnt_id => g_egd_table(l_curr_count).elig_dpnt_id,
1831 p_per_in_ler_id => l_elctbl_chc.per_in_ler_id,
1832 p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
1833 p_object_version_number =>
1834 g_egd_table(l_curr_count).object_version_number,
1835 p_effective_date => p_effective_date,
1836 p_program_update_date => l_sysdate);
1837 hr_utility.set_location ('End BEEGDAPI_UPD loop '||l_proc,80);
1838 --
1839 end loop;
1840 --
1841 hr_utility.set_location ('Dn BEEGDAPI_UPD Loop '||l_proc,80);
1842 end if;
1843
1844 end if;
1845 --
1846 if g_debug then
1847 hr_utility.set_location ('Leaving '||l_proc,80);
1848 end if;
1849 end;
1850 END;