[Home] [Help]
494: 115.213 29-Dec-03 rpgupta Bug#3327224 - Make dpnt cvg end dt independent of prtt cvg end dt
495: 115.214 08-Jan-04 vvprabhu Bug 1620171 - Changes to date format in message 91902
496: 115.215 05-Feb-04 ikasire Bug 3412562 void_rate
497: 115.216 11-Mar-04 bmanyam BUG: 3398353 - END-DATE/VOID Dependent Designation records
498: (BEN_ELIG_CVRD_DPNT_F) in void_enrollment procedure
499: for Unrestricted Enrollment.
500: Bug: 1620171 - For msg 91902, used fnd_date.date_to_chardate
501: instead of a fixed format-mask.
502: 115.217 07=Apr-04 tjesumic FONM changes added
723: No Coverage plans against the new pen_id.. Only completed action items will be copied and check will be made whether to copy the
724: completed actions items or not.
725: 115.341 25-Aug-09 sallumwa Bug 8688513 : Modified the fix done for 8222481 to handle the coverage gaps when no electability exists for the
726: previous life event.
727: 115.343 07-Oct-09 sallumwa Bug 8919376 : Update the elig cvrd dpnt ID in the ben_elig_cvrd_dpnt_f table correponding to the
728: enrollment result if the participant is currently enrolled in the plan.
729: 115.344 14-Oct-09 stee Bug 8972844 : Changed cursor c_bckup_tbl_restore.
730: 115.345 13-Nov-09 stee Bug 9114147 : Use life event occurred date to check if an enrollment is
731: overriden when determining if an enrollment should be terminated
730: 115.345 13-Nov-09 stee Bug 9114147 : Use life event occurred date to check if an enrollment is
731: overriden when determining if an enrollment should be terminated
732: in multi_rows_edit.
733: 115.346 08-Jan-09 velvanop Bug 9256641 : Do not generate communications while creating the action items
734: 115.348 16-Apr-10 sallumwa Bug 9534364 : Modified the IF condition to update the row in ben_elig_cvrd_dpnt_f table.
735: 115.349 20-Apr-10 sagnanas Bug 9501588 : Modified cursor c_old_dpnt in procedure unhook_dpnt
736: 115.350 08-Jun-10 velvanop Bug 9748558 : If a person move out of the result and re-enroll into the same result
737: on a different day in the same enrollment period,then re-cover the dependents.
738: 115.351 30-Aug-10 velvanop Bug 10048313 : For a plan having Required Period of enrollment defined and participant changes the option with in the same plan
1691: l_ppe c_ppe%rowtype;
1692: --
1693: cursor c_check_cvrd_in_plan is
1694: select null
1695: from ben_elig_cvrd_dpnt_f pdp
1696: ,ben_prtt_enrt_rslt_f pen
1697: ,ben_pgm_f pgm
1698: where pdp.dpnt_person_id = p_person_id
1699: and pdp.business_group_id = p_business_group_id
3116: pdp.object_version_number,
3117: pdp.cvg_thru_dt --9881657
3118: from ben_elig_per_elctbl_chc epe,
3119: ben_elig_dpnt egd,
3120: ben_elig_cvrd_dpnt_f pdp,
3121: ben_per_in_ler egd_pil,
3122: ben_per_in_ler pdp_pil
3123: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3124: and epe.per_in_ler_id = p_per_in_ler_id
3143: /* Bug 9748558:-If a person move out of the result and re-enroll into the same result
3144: on a different day in the same enrollment period,then re-cover the dependents.*/
3145: cursor c_check_dup_dep(p_pdp_id number) is
3146: select pdp.*
3147: from ben_elig_cvrd_dpnt_f pdp,
3148: ben_prtt_enrt_rslt_f pen
3149: where pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3150: and pdp.elig_cvrd_dpnt_id = p_pdp_id
3151: and pdp.per_in_ler_id = p_per_in_ler_id
3177: l_get_last_pil c_get_last_pil%rowtype;
3178:
3179: cursor c_last_pil_pdp(p_pil_id number,p_pdp_id number) is
3180: SELECT pdp.*
3181: FROM ben_elig_cvrd_dpnt_f pdp
3182: WHERE pdp.per_in_ler_id = p_pil_id
3183: AND pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3184: AND pdp.elig_cvrd_dpnt_id = p_pdp_id;
3185:
3186: l_last_pil_pdp c_last_pil_pdp%rowtype;
3187:
3188: cursor c_pdp_rec(p_pdp_id number) is
3189: select pdp.*
3190: from ben_elig_cvrd_dpnt_f pdp
3191: where pdp.elig_cvrd_dpnt_id = p_pdp_id;
3192: /* Bug 9748558 */
3193:
3194: ------Check if the elig_cvrd_dpnt_id is set in ben_elig_dpnt table,Bug 8919376
3195: cursor c_check_cvrd_dpnt(p_dpnt_cvg_thru_dt date) is --9881657
3196: select egd.*,pdp.elig_cvrd_dpnt_id cvrd_dpnt_id
3197: from ben_elig_per_elctbl_chc epe,
3198: ben_elig_dpnt egd,
3199: ben_elig_cvrd_dpnt_f pdp,
3200: ben_per_in_ler egd_pil,
3201: ben_per_in_ler pdp_pil
3202: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3203: and epe.per_in_ler_id = p_per_in_ler_id
4292: -- Find the valid datetrack modes.
4293: --
4294: dt_api.find_dt_upd_modes
4295: (p_effective_date => p_effective_date,
4296: p_base_table_name => 'BEN_ELIG_CVRD_DPNT_F',
4297: p_base_key_column => 'elig_cvrd_dpnt_id',
4298: p_base_key_value => l_egd.elig_cvrd_dpnt_id,
4299: p_correction => l_correction,
4300: p_update => l_update,
8097: hr_utility.set_location('l_env_rec.mode_cd '|| l_env_rec.mode_cd , 100);
8098: hr_utility.set_location('p_source '|| p_source , 100);
8099: -- 3398353
8100: /* In case of Unrestricted Enrollment running on the same-day,
8101: the dependent designation records (ben_elig_cvrd_dpnt_f) also
8102: needs to be end-dated / voided, when the enrollment is voided.
8103: */
8104: -- Also call the below code for 3797391 when source is benelinf
8105: --
10990: ,ecd.CVG_PNDG_FLAG
10991: ,ecd.cvg_thru_dt
10992: ,ecd.object_version_number
10993: ,ecd.per_in_ler_id
10994: from ben_elig_cvrd_dpnt_f ecd,
10995: ben_per_in_ler pil
10996: where ecd.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
10997: and ecd.cvg_strt_dt is not null
10998: -- and ecd.cvg_thru_dt = hr_api.g_eot
11009: pil.per_in_ler_stat_cd is null)
11010: /* Bug 4520785 Pick only those cvrd dependent records where either cvg thru date hasn't
11011: been end dated yet or cvg thru date is more than effective date */
11012: and ecd.elig_cvrd_dpnt_id not in (select ecd1.elig_cvrd_dpnt_id
11013: from ben_elig_cvrd_dpnt_f ecd1
11014: where ecd1.elig_cvrd_dpnt_id = ecd.elig_cvrd_dpnt_id
11015: and ecd1.cvg_thru_dt <= p_cvg_thru_dt --9501588
11016: and p_effective_date <= ecd1.effective_end_date ) -- Bug 4710937
11017: /* End Bug 4520785 */
11120: -- thru date.
11121: --
11122: dt_api.find_dt_upd_modes
11123: (p_effective_date => p_effective_date,
11124: p_base_table_name => 'BEN_ELIG_CVRD_DPNT_F',
11125: p_base_key_column => 'ELIG_CVRD_DPNT_ID',
11126: p_base_key_value => orec.elig_cvrd_dpnt_id,
11127: p_correction => l_correction,
11128: p_update => l_update,
11921: and pl.pl_id = r.pl_id
11922: and p_effective_date between
11923: pl.effective_start_date and pl.effective_end_date
11924: and ((c.alws_dpnt_dsgn_flag = 'Y'
11925: and not exists (select 'x' from ben_elig_cvrd_dpnt_f pdp
11926: where pdp.prtt_enrt_rslt_id = r.prtt_enrt_rslt_id
11927: and p_effective_date between
11928: pdp.effective_start_date and pdp.effective_end_date))
11929: OR