[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
1618: l_ppe c_ppe%rowtype;
1619: --
1620: cursor c_check_cvrd_in_plan is
1621: select null
1622: from ben_elig_cvrd_dpnt_f pdp
1623: ,ben_prtt_enrt_rslt_f pen
1624: ,ben_pgm_f pgm
1625: where pdp.dpnt_person_id = p_person_id
1626: and pdp.business_group_id = p_business_group_id
3005: pdp.effective_start_date,
3006: pdp.object_version_number
3007: from ben_elig_per_elctbl_chc epe,
3008: ben_elig_dpnt egd,
3009: ben_elig_cvrd_dpnt_f pdp,
3010: ben_per_in_ler egd_pil,
3011: ben_per_in_ler pdp_pil
3012: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3013: and epe.per_in_ler_id = p_per_in_ler_id
4012: -- Find the valid datetrack modes.
4013: --
4014: dt_api.find_dt_upd_modes
4015: (p_effective_date => p_effective_date,
4016: p_base_table_name => 'BEN_ELIG_CVRD_DPNT_F',
4017: p_base_key_column => 'elig_cvrd_dpnt_id',
4018: p_base_key_value => l_egd.elig_cvrd_dpnt_id,
4019: p_correction => l_correction,
4020: p_update => l_update,
7452: hr_utility.set_location('l_env_rec.mode_cd '|| l_env_rec.mode_cd , 100);
7453: hr_utility.set_location('p_source '|| p_source , 100);
7454: -- 3398353
7455: /* In case of Unrestricted Enrollment running on the same-day,
7456: the dependent designation records (ben_elig_cvrd_dpnt_f) also
7457: needs to be end-dated / voided, when the enrollment is voided.
7458: */
7459: -- Also call the below code for 3797391 when source is benelinf
7460: --
9396: ,ecd.CVG_PNDG_FLAG
9397: ,ecd.cvg_thru_dt
9398: ,ecd.object_version_number
9399: ,ecd.per_in_ler_id
9400: from ben_elig_cvrd_dpnt_f ecd,
9401: ben_per_in_ler pil
9402: where ecd.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
9403: and ecd.cvg_strt_dt is not null
9404: -- and ecd.cvg_thru_dt = hr_api.g_eot
9415: pil.per_in_ler_stat_cd is null)
9416: /* Bug 4520785 Pick only those cvrd dependent records where either cvg thru date hasn't
9417: been end dated yet or cvg thru date is more than effective date */
9418: and ecd.elig_cvrd_dpnt_id not in (select ecd1.elig_cvrd_dpnt_id
9419: from ben_elig_cvrd_dpnt_f ecd1
9420: where ecd1.elig_cvrd_dpnt_id = ecd.elig_cvrd_dpnt_id
9421: and ecd1.cvg_thru_dt <= p_effective_date
9422: and p_effective_date <= ecd1.effective_end_date ) -- Bug 4710937
9423: /* End Bug 4520785 */
9522: -- thru date.
9523: --
9524: dt_api.find_dt_upd_modes
9525: (p_effective_date => p_effective_date,
9526: p_base_table_name => 'BEN_ELIG_CVRD_DPNT_F',
9527: p_base_key_column => 'ELIG_CVRD_DPNT_ID',
9528: p_base_key_value => orec.elig_cvrd_dpnt_id,
9529: p_correction => l_correction,
9530: p_update => l_update,
10323: and pl.pl_id = r.pl_id
10324: and p_effective_date between
10325: pl.effective_start_date and pl.effective_end_date
10326: and ((c.alws_dpnt_dsgn_flag = 'Y'
10327: and not exists (select 'x' from ben_elig_cvrd_dpnt_f pdp
10328: where pdp.prtt_enrt_rslt_id = r.prtt_enrt_rslt_id
10329: and p_effective_date between
10330: pdp.effective_start_date and pdp.effective_end_date))
10331: OR