View: BEN_ENRT_MISC_LOV_INFO_LE_V

Product:	BEN - Advanced Benefits
Description:	LOV for BENEMSEB
Implementation/DBA Data:	APPS.BEN_ENRT_MISC_LOV_INFO_LE_V

View Text

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(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' OR C.PRTT_ENRT_RSLT_ID IS NOT NULL
AND EXISTS (SELECT NULL
FROM BEN_PRTT_ENRT_RSLT_F PEN
WHERE C.PRTT_ENRT_RSLT_ID = PEN.PRTT_ENRT_RSLT_ID
AND ENRT_CVG_THRU_DT = TO_DATE('12/31/4712'
, 'MM/DD/RRRR')
AND F.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE))
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

Columns