View: BEN_REL_PER_LIFE_EVTS_V

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

View Text

SELECT CSR.PERSON_ID
, PPF.FULL_NAME
, LER.NAME
, LER.LER_ID
, PIL.LF_EVT_OCRD_DT
, PIL.PTNL_LER_FOR_PER_STAT_CD
FROM PER_CONTACT_RELATIONSHIPS CSR
, PER_PEOPLE_F PPF
, BEN_PTNL_LER_FOR_PER PIL
, BEN_LER_F LER
, FND_SESSIONS SE
WHERE SE.SESSION_ID = USERENV('SESSIONID')
AND PPF.PERSON_ID = CSR.CONTACT_PERSON_ID
AND SE.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CSR.PERSONAL_FLAG = 'Y'
AND SE.EFFECTIVE_DATE BETWEEN NVL(CSR.DATE_START
, SE.EFFECTIVE_DATE)
AND NVL(CSR.DATE_END
, SE.EFFECTIVE_DATE)
AND PIL.LER_ID = LER.LER_ID
AND PIL.PERSON_ID = CSR.CONTACT_PERSON_ID
AND PIL.BUSINESS_GROUP_ID = CSR.BUSINESS_GROUP_ID
AND LER.BUSINESS_GROUP_ID = CSR.BUSINESS_GROUP_ID
AND PIL.PTNL_LER_FOR_PER_STAT_CD IN ( 'DTCTD'
, 'UNPROCD')
AND SE.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE

Columns