VIEW: APPS.BEN_ENRT_MISC_LOV_INFO_LE_V

Source

View Text - Preformatted

SELECT C.PL_TYP_ID PL_TYP_ID , TYP.NAME PL_TYP_ID_NAME , C.PL_ID PL_ID , PL.NAME PL_ID_NAME , O.OPT_ID OPT_ID , OPT.NAME OPT_NAME , C.OIPL_ID OIPL_ID , B.VAL BNFT_VAL, B.NNMNTRY_UOM BNFT_NNMNTRY_UOM , B.INCRMT_VAL, B.BNFT_TYP_CD, B.CVG_MLT_CD, B.ENTR_VAL_AT_ENRT_FLAG, C.ENRT_CVG_STRT_DT_CD, C.CRNTLY_ENRD_FLAG CRNTLY_ENRD_FLAG , C.BUSINESS_GROUP_ID BUSINESS_GROUP_ID , C.MNDTRY_FLAG MNDTRY_FLAG , C.DFLT_FLAG DFLT_FLAG , C.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID , C.PER_IN_LER_ID PER_IN_LER_ID , PIL.LF_EVT_OCRD_DT, C.OBJECT_VERSION_NUMBER CHOICE_OVN , C.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID , B.ENRT_BNFT_ID ENRT_BNFT_ID , C.ENRT_CVG_STRT_DT ENRT_CVG_STRT_DT , C.ERLST_DEENRT_DT ERLST_DEENRT_DT , PIL.PERSON_ID PERSON_ID , DECODE(B.ENRT_BNFT_ID,NULL,C.CTFN_RQD_FLAG,B.CTFN_RQD_FLAG) CTFN_RQD_FLAG , PEL.UOM UOM , PEL.ACTY_REF_PERD_CD ACTY_REF_PERD_CD , PEL.CLS_ENRT_DT_TO_USE_CD , C.PL_ORDR_NUM , C.PLIP_ORDR_NUM , C.PTIP_ORDR_NUM , C.OIPL_ORDR_NUM, pen.prtt_enrt_rslt_id pen_enrt_prtt_enrt_rslt_id, pen.enrt_cvg_strt_dt pen_enrt_cvg_strt_dt, pen.enrt_cvg_thru_dt pen_enrt_cvg_thru_dt, pen.sspndd_flag pen_sspndd_flag, pen.rplcs_sspndd_rslt_id pen_rplcs_sspndd_rslt_id, b.mn_val , b.mx_val , b.mx_wout_ctfn_val, decode(c.IN_PNDG_WKFLOW_FLAG , 'Y' ,'*' , null ) IN_PNDG_WKFLOW_FLAG_MEANING, c.IN_PNDG_WKFLOW_FLAG FROM BEN_ELIG_PER_ELCTBL_CHC C, ben_prtt_enrt_rslt_f pen, BEN_PIL_ELCTBL_CHC_POPL PEL, BEN_PER_IN_LER PIL, BEN_OIPL_F O, BEN_OPT_F OPT, BEN_ENRT_BNFT B, BEN_PL_F PL, BEN_PL_TYP_F TYP, FND_SESSIONS F, ben_ler_f ler WHERE pil.per_in_ler_stat_cd = 'STRTD' AND pil.per_in_ler_id = c.per_in_ler_id AND pil.per_in_ler_id = pel.per_in_ler_id AND pel.pl_id = c.pl_id AND f.effective_date BETWEEN NVL (pel.enrt_perd_strt_dt, f.effective_date) AND NVL (NVL (pel.procg_end_dt, pel.enrt_perd_end_dt), f.effective_date) AND c.comp_lvl_cd NOT IN ('PLANFC', 'PLANIMP') AND c.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id(+) AND f.effective_date BETWEEN NVL (pen.effective_start_date, f.effective_date) AND NVL (pen.effective_end_date, f.effective_date) AND c.pl_typ_id = typ.pl_typ_id AND typ.pl_typ_stat_cd = 'A' AND pil.lf_evt_ocrd_dt BETWEEN typ.effective_start_date AND typ.effective_end_date AND c.oipl_id = o.oipl_id(+) AND pil.lf_evt_ocrd_dt BETWEEN NVL (o.effective_start_date, pil.lf_evt_ocrd_dt) AND NVL (o.effective_end_date, pil.lf_evt_ocrd_dt) AND o.oipl_stat_cd(+) = 'A' AND o.opt_id = opt.opt_id(+) AND pil.lf_evt_ocrd_dt BETWEEN NVL (opt.effective_start_date, pil.lf_evt_ocrd_dt) AND NVL (opt.effective_end_date, pil.lf_evt_ocrd_dt) AND b.elig_per_elctbl_chc_id(+) = c.elig_per_elctbl_chc_id AND pl.pl_id = c.pl_id AND pl.pl_stat_cd = 'A' AND pl.svgs_pl_flag <> 'Y' AND pil.lf_evt_ocrd_dt BETWEEN pl.effective_start_date AND pl.effective_end_date AND f.session_id = USERENV ('sessionid') AND (c.elctbl_flag = 'Y') AND NOT EXISTS ( SELECT 1 FROM ben_plip_f plip WHERE plip.pl_id = pl.pl_id AND plip.plip_stat_cd = 'A' AND f.effective_date BETWEEN plip.effective_start_date AND plip.effective_end_date) AND ler.ler_id = pil.ler_id AND ler.typ_cd NOT IN ('COMP', 'IREC', 'GSP') AND f.effective_date BETWEEN ler.effective_start_date AND ler.effective_end_date

View Text - HTML Formatted

SELECT C.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_ID_NAME
, C.PL_ID PL_ID
, PL.NAME PL_ID_NAME
, O.OPT_ID OPT_ID
, OPT.NAME OPT_NAME
, C.OIPL_ID OIPL_ID
, B.VAL BNFT_VAL
, B.NNMNTRY_UOM BNFT_NNMNTRY_UOM
, B.INCRMT_VAL
, B.BNFT_TYP_CD
, B.CVG_MLT_CD
, B.ENTR_VAL_AT_ENRT_FLAG
, C.ENRT_CVG_STRT_DT_CD
, C.CRNTLY_ENRD_FLAG CRNTLY_ENRD_FLAG
, C.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, C.MNDTRY_FLAG MNDTRY_FLAG
, C.DFLT_FLAG DFLT_FLAG
, C.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID
, C.PER_IN_LER_ID PER_IN_LER_ID
, PIL.LF_EVT_OCRD_DT
, C.OBJECT_VERSION_NUMBER CHOICE_OVN
, C.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID
, B.ENRT_BNFT_ID ENRT_BNFT_ID
, C.ENRT_CVG_STRT_DT ENRT_CVG_STRT_DT
, C.ERLST_DEENRT_DT ERLST_DEENRT_DT
, PIL.PERSON_ID PERSON_ID
, DECODE(B.ENRT_BNFT_ID
, NULL
, C.CTFN_RQD_FLAG
, B.CTFN_RQD_FLAG) CTFN_RQD_FLAG
, PEL.UOM UOM
, PEL.ACTY_REF_PERD_CD ACTY_REF_PERD_CD
, PEL.CLS_ENRT_DT_TO_USE_CD
, C.PL_ORDR_NUM
, C.PLIP_ORDR_NUM
, C.PTIP_ORDR_NUM
, C.OIPL_ORDR_NUM
, PEN.PRTT_ENRT_RSLT_ID PEN_ENRT_PRTT_ENRT_RSLT_ID
, PEN.ENRT_CVG_STRT_DT PEN_ENRT_CVG_STRT_DT
, PEN.ENRT_CVG_THRU_DT PEN_ENRT_CVG_THRU_DT
, PEN.SSPNDD_FLAG PEN_SSPNDD_FLAG
, PEN.RPLCS_SSPNDD_RSLT_ID PEN_RPLCS_SSPNDD_RSLT_ID
, B.MN_VAL
, B.MX_VAL
, B.MX_WOUT_CTFN_VAL
, DECODE(C.IN_PNDG_WKFLOW_FLAG
, 'Y'
, '*'
, NULL ) IN_PNDG_WKFLOW_FLAG_MEANING
, C.IN_PNDG_WKFLOW_FLAG
FROM BEN_ELIG_PER_ELCTBL_CHC C
, BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PIL_ELCTBL_CHC_POPL PEL
, BEN_PER_IN_LER PIL
, BEN_OIPL_F O
, BEN_OPT_F OPT
, BEN_ENRT_BNFT B
, BEN_PL_F PL
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
, BEN_LER_F LER
WHERE PIL.PER_IN_LER_STAT_CD = 'STRTD'
AND PIL.PER_IN_LER_ID = C.PER_IN_LER_ID
AND PIL.PER_IN_LER_ID = PEL.PER_IN_LER_ID
AND PEL.PL_ID = C.PL_ID
AND F.EFFECTIVE_DATE BETWEEN NVL (PEL.ENRT_PERD_STRT_DT
, F.EFFECTIVE_DATE)
AND NVL (NVL (PEL.PROCG_END_DT
, PEL.ENRT_PERD_END_DT)
, F.EFFECTIVE_DATE)
AND C.COMP_LVL_CD NOT IN ('PLANFC'
, 'PLANIMP')
AND C.PRTT_ENRT_RSLT_ID = PEN.PRTT_ENRT_RSLT_ID(+)
AND F.EFFECTIVE_DATE BETWEEN NVL (PEN.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL (PEN.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND C.PL_TYP_ID = TYP.PL_TYP_ID
AND TYP.PL_TYP_STAT_CD = 'A'
AND PIL.LF_EVT_OCRD_DT BETWEEN TYP.EFFECTIVE_START_DATE
AND TYP.EFFECTIVE_END_DATE
AND C.OIPL_ID = O.OIPL_ID(+)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL (O.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL (O.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND O.OIPL_STAT_CD(+) = 'A'
AND O.OPT_ID = OPT.OPT_ID(+)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL (OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL (OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND B.ELIG_PER_ELCTBL_CHC_ID(+) = C.ELIG_PER_ELCTBL_CHC_ID
AND PL.PL_ID = C.PL_ID
AND PL.PL_STAT_CD = 'A'
AND PL.SVGS_PL_FLAG <> 'Y'
AND PIL.LF_EVT_OCRD_DT BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND F.SESSION_ID = USERENV ('SESSIONID')
AND (C.ELCTBL_FLAG = 'Y')
AND NOT EXISTS ( SELECT 1
FROM BEN_PLIP_F PLIP
WHERE PLIP.PL_ID = PL.PL_ID
AND PLIP.PLIP_STAT_CD = 'A'
AND F.EFFECTIVE_DATE BETWEEN PLIP.EFFECTIVE_START_DATE
AND PLIP.EFFECTIVE_END_DATE)
AND LER.LER_ID = PIL.LER_ID
AND LER.TYP_CD NOT IN ('COMP'
, 'IREC'
, 'GSP')
AND F.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE