View: BEN_ELIG_PER_F_V2

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

View Text

SELECT PLN.PL_TYP_ID
, PEP.PGM_ID
, PTP.NAME
, PEP.PERSON_ID
, PEP.BUSINESS_GROUP_ID
FROM BEN_ELIG_PER_F PEP
, BEN_PER_IN_LER PIL
, BEN_PL_F PLN
, BEN_PL_TYP_F PTP
, BEN_PTIP_F CTP
, FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID')
AND SE.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND PEP.PL_ID = PLN.PL_ID
AND PLN.PL_TYP_ID = PTP.PL_TYP_ID
AND PTP.PL_TYP_ID NOT IN (SELECT PL_TYP_ID
FROM BEN_ELIG_PER_WV_PL_TYP_F ETW
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_F PEP
, FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID')
AND SE.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND PEP.ELIG_PER_ID = ETW.ELIG_PER_ID
AND ETW.WVD_FLAG = 'Y'
AND PIL.PER_IN_LER_ID(+)=PEP.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PEP.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL ))
AND PTP.PL_TYP_ID = CTP.PL_TYP_ID
AND CTP.WVBL_FLAG = 'Y'
AND PIL.PER_IN_LER_ID(+)=PEP.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID(+)=PEP.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL ) GROUP BY PLN.PL_TYP_ID
, PEP.PGM_ID
, PTP.NAME
, PEP.PERSON_ID
, PEP.BUSINESS_GROUP_ID

Columns