VIEW: APPS.BEN_REL_PER_LIFE_EVTS_V

Source

View Text - Preformatted

SELECT csr.person_id, ppf.full_name, ler.name, ler.ler_id, pil.lf_evt_ocrd_dt, pil.ptnl_ler_for_per_stat_cd, pil.CSD_BY_PTNL_LER_FOR_PER_ID 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) Bug 14098974*/ 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

View Text - HTML Formatted

SELECT CSR.PERSON_ID
, PPF.FULL_NAME
, LER.NAME
, LER.LER_ID
, PIL.LF_EVT_OCRD_DT
, PIL.PTNL_LER_FOR_PER_STAT_CD
, PIL.CSD_BY_PTNL_LER_FOR_PER_ID
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) BUG 14098974*/
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