[Home] [Help]
395: -- type or a certification with a contact type of null is found.
396: --
397: -- Bug# 3851427 : Defined out parameter P_SUSP_IF_CTFN_NOT_PRVD_FLAG. This
398: -- parameter holds the value of "Suspend Enrollment" flag defined for
399: -- Dependent Change Of Life Event (BEN_LER_CHG_DPNT_CVG_F). Whenever certifications
400: -- are considered at "Dependent Change Of Life Event" level and NOT at PLN, PTIP or PGM
401: -- level, suspension should also be considered based on "Suspension Enrollment" flag value
402: -- at the "Dependent Change Of Life Event" level. Hence this flag will return valid
403: -- value only when P_CTFN_AT_LER_CHG is also true.
404: --
405: cursor c_ctfns_ler_chg is
406: select ldc.susp_if_ctfn_not_prvd_flag
407: from ben_ler_chg_dpnt_cvg_ctfn_f lcc,
408: ben_ler_chg_dpnt_cvg_f ldc,
409: ben_prtt_enrt_rslt_f pen
410: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
411: and pen.business_group_id = p_business_group_id
412: and p_effective_date between pen.effective_start_date
2345: -- Cursor to retrieve Suspend Enrollment Flag at the various levels
2346: --
2347: cursor c_ldc (p_lvl_cd varchar) is
2348: select ldc.susp_if_ctfn_not_prvd_flag
2349: from BEN_LER_CHG_DPNT_CVG_f ldc,
2350: ben_prtt_enrt_rslt_f pen
2351: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2352: and pen.business_group_id = p_business_group_id
2353: and p_effective_date between pen.effective_start_date
3290: lcc.rlshp_typ_cd,
3291: ldc.susp_if_ctfn_not_prvd_flag,
3292: ldc.ctfn_determine_cd
3293: from ben_ler_chg_dpnt_cvg_ctfn_f lcc,
3294: ben_ler_chg_dpnt_cvg_f ldc,
3295: ben_prtt_enrt_rslt_f pen
3296: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3297: and pen.business_group_id = p_business_group_id
3298: and p_effective_date between pen.effective_start_date