View: BEN_FLX_ENRT_PGM_INFO_V

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

View Text

SELECT DISTINCT PGM.NAME
, PIL.PERSON_ID
, PIL.LER_ID
, LER.NAME
, EPE.PER_IN_LER_ID
, EPE.PGM_ID
, EPE.BUSINESS_GROUP_ID
, PGM.PGM_TYP_CD
, PEL.ENRT_PERD_ID
, PEL.LEE_RSN_ID
, PEL.CLS_ENRT_DT_TO_USE_CD
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PIL_ELCTBL_CHC_POPL PEL
, BEN_PGM_F PGM
, BEN_PER_IN_LER PIL
, BEN_LER_V LER
, FND_SESSIONS SE
WHERE SE.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE
AND SE.EFFECTIVE_DATE BETWEEN NVL(PEL.ENRT_PERD_STRT_DT
, SE.EFFECTIVE_DATE)
AND NVL(PEL.PROCG_END_DT
, NVL(PEL.ENRT_PERD_END_DT
, SE.EFFECTIVE_DATE))
AND PIL.PER_IN_LER_ID = PEL.PER_IN_LER_ID
AND PEL.PGM_ID = EPE.PGM_ID
AND SE.SESSION_ID = USERENV('SESSIONID')
AND PIL.LER_ID = LER.LER_ID
AND PIL.PER_IN_LER_ID = EPE.PER_IN_LER_ID
AND PIL.PER_IN_LER_STAT_CD = 'STRTD'
AND EPE.PGM_ID = PGM.PGM_ID
AND PGM.PGM_STAT_CD = 'A'
AND EPE.COMP_LVL_CD NOT IN ('PLANFC'
, 'PLANIMP')
AND EPE.ELCTBL_FLAG = 'Y'
AND LER.TYP_CD NOT IN ('COMP'
, 'IREC'
, 'GSP')

Columns