View: BEN_ELCTBL_CHC_BNFT_V

Product:	BEN - Advanced Benefits
Description:
Implementation/DBA Data:	APPS.BEN_ELCTBL_CHC_BNFT_V

View Text

SELECT EPE.PER_IN_LER_ID
, DECODE(OPT.NAME
, NULL
, PLN.NAME
, PLN.NAME||': '||OPT.NAME) PL_AND_OPT_NAMES
, EPE.PIL_ELCTBL_CHC_POPL_ID
, EPE.BUSINESS_GROUP_ID
, EPE.PL_ID
, EPE.OIPL_ID
, ENB.VAL
, EPE.PL_TYP_ID
, ENB.NNMNTRY_UOM
, EPE.CRNTLY_ENRD_FLAG
, EPE.ENRT_CVG_STRT_DT_CD
, EPE.ENRT_CVG_STRT_DT
, EPE.DPNT_DSGN_CD
, EPE.LER_CHG_DPNT_CVG_CD
, EPE.DPNT_CVG_STRT_DT_CD
, ENB.BNFT_TYP_CD
, ENB.CVG_MLT_CD
, ENB.ENTR_VAL_AT_ENRT_FLAG
, ENB.MN_VAL
, ENB.MX_VAL
, ENB.INCRMT_VAL
, ENB.DFLT_VAL
, EPE.ELCTBL_FLAG
, EPE.CTFN_RQD_FLAG
, EPE.MUST_ENRL_ANTHR_PL_ID
, EPE.ELIG_PER_ELCTBL_CHC_ID
, DECODE(ENB.ENRT_BNFT_ID
, NULL
, EPE.ELIG_PER_ELCTBL_CHC_ID
, NULL) FAKE_CHOICE_ID
, EPE.ERLST_DEENRT_DT
, ENB.ENRT_BNFT_ID
, EPE.DFLT_FLAG
, EPE.MNDTRY_FLAG
, EPE.AUTO_ENRT_FLAG
, EPE.ROLL_CRS_FLAG
, EPE.ELIG_FLAG
, EPE.INELIG_RSN_CD
, EPE.ELIG_OVRID_PERSON_ID
, EPE.ELIG_OVRID_DT
, EPE.MGR_OVRID_PERSON_ID
, EPE.MGR_OVRID_DT
, EPE.WS_MGR_ID
, EPE.COMMENTS
, CTP.ORDR_NUM PTIP_ORDR_NUM
, PTP.NAME PL_TYP_NAME
, CPP.ORDR_NUM PLIP_ORDR_NUM
, PLN.ORDR_NUM PL_ORDR_NUM
, PLN.NAME PL_NAME
, COP.ORDR_NUM OIPL_ORDR_NUM
, OPT.NAME OPT_NAME
, ENB.ORDR_NUM ENB_ORDR_NUM
, EPE.ENRT_CVG_STRT_DT_RL
, EPE.IN_PNDG_WKFLOW_FLAG
, EPE.DPNT_CVG_STRT_DT_RL
, EPE.ALWS_DPNT_DSGN_FLAG
, EPE.PROCG_END_DT
, EPE.COMP_LVL_CD
, EPE.PGM_ID
, EPE.PLIP_ID
, EPE.PTIP_ID
, EPE.OIPLIP_ID
, EPE.CMBN_PLIP_ID
, EPE.CMBN_PTIP_ID
, EPE.CMBN_PTIP_OPT_ID
, EPE.ASSIGNMENT_ID
, EPE.SPCL_RT_PL_ID
, EPE.SPCL_RT_OIPL_ID
, EPE.INTERIM_ELIG_PER_ELCTBL_CHC_ID
, EPE.PRTT_ENRT_RSLT_ID
, EPE.BNFT_PRVDR_POOL_ID
, EPE.YR_PERD_ID
, EPE.CRYFWD_ELIG_DPNT_CD
, EPE.EPE_ATTRIBUTE_CATEGORY
, EPE.EPE_ATTRIBUTE1
, EPE.EPE_ATTRIBUTE2
, EPE.EPE_ATTRIBUTE3
, EPE.EPE_ATTRIBUTE4
, EPE.EPE_ATTRIBUTE5
, EPE.EPE_ATTRIBUTE6
, EPE.EPE_ATTRIBUTE7
, EPE.EPE_ATTRIBUTE8
, EPE.EPE_ATTRIBUTE9
, EPE.EPE_ATTRIBUTE10
, EPE.EPE_ATTRIBUTE11
, EPE.EPE_ATTRIBUTE12
, EPE.EPE_ATTRIBUTE13
, EPE.EPE_ATTRIBUTE14
, EPE.EPE_ATTRIBUTE15
, EPE.EPE_ATTRIBUTE16
, EPE.EPE_ATTRIBUTE17
, EPE.EPE_ATTRIBUTE18
, EPE.EPE_ATTRIBUTE19
, EPE.EPE_ATTRIBUTE20
, EPE.EPE_ATTRIBUTE21
, EPE.EPE_ATTRIBUTE22
, EPE.EPE_ATTRIBUTE23
, EPE.EPE_ATTRIBUTE24
, EPE.EPE_ATTRIBUTE25
, EPE.EPE_ATTRIBUTE26
, EPE.EPE_ATTRIBUTE27
, EPE.EPE_ATTRIBUTE28
, EPE.EPE_ATTRIBUTE29
, EPE.EPE_ATTRIBUTE30
, EPE.LAST_UPDATE_DATE
, EPE.LAST_UPDATED_BY
, EPE.LAST_UPDATE_LOGIN
, EPE.CREATED_BY
, EPE.CREATION_DATE
, EPE.REQUEST_ID
, EPE.PROGRAM_APPLICATION_ID
, EPE.PROGRAM_ID
, EPE.PROGRAM_UPDATE_DATE
, EPE.OBJECT_VERSION_NUMBER
FROM BEN_ENRT_BNFT ENB
, BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_PTIP_F CTP
, BEN_PL_TYP_F PTP
, BEN_PL_F PLN
, BEN_OIPL_F COP
, BEN_OPT_F OPT
, BEN_PLIP_F CPP
, FND_SESSIONS SE
WHERE ENB.ELIG_PER_ELCTBL_CHC_ID (+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND ENB.MX_WO_CTFN_FLAG(+) = 'N' /* BUG 2205237 --AND EPE.ELCTBL_FLAG = 'Y' */
AND EPE.PTIP_ID = CTP.PTIP_ID (+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(CTP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(CTP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND CTP.PL_TYP_ID = PTP.PL_TYP_ID (+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(PTP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PTP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND EPE.PL_ID = PLN.PL_ID
AND SE.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND EPE.PLIP_ID = CPP.PLIP_ID (+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(CPP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(CPP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND EPE.OIPL_ID = COP.OIPL_ID (+)
AND COP.OPT_ID = OPT.OPT_ID (+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND SE.EFFECTIVE_DATE BETWEEN NVL(COP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(COP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND SE.SESSION_ID = USERENV('SESSIONID')
AND PIL.PER_IN_LER_ID=EPE.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID=EPE.BUSINESS_GROUP_ID
AND PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT')

Columns