View: HR_ATH_DEPENDENT_V

Product:	PER - Human Resources
Description:	View to support Authoria Integration
Implementation/DBA Data:	APPS.HR_ATH_DEPENDENT_V

View Text

SELECT TO_CHAR(PEN.PERSON_ID) EMPLOYEE_ID
, TO_CHAR(PEN.PL_ID)||'A'||TO_CHAR(PLIP.PLIP_ID) PLAN_ID
, TO_CHAR(ECD.DPNT_PERSON_ID) NAME
, PPF.FULL_NAME FULL_NAME
, ECD.CVG_STRT_DT START_DATE
, NVL(ECD.CVG_THRU_DT
, TO_DATE('12/31/4712'
, 'MM/DD/YYYY')) END_DATE
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_ELIG_CVRD_DPNT_F ECD
, PER_ALL_PEOPLE_F PPF
, BEN_PLIP_F PLIP
WHERE PEN.PRTT_ENRT_RSLT_ID = ECD.PRTT_ENRT_RSLT_ID
AND PEN.PER_IN_LER_ID = ECD.PER_IN_LER_ID
AND ECD.DPNT_PERSON_ID = PPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN ECD.EFFECTIVE_START_DATE
AND ECD.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND TRUNC(SYSDATE) BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.PL_ID = PLIP.PL_ID(+)
AND PEN.PGM_ID = PLIP.PGM_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(PLIP.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(PLIP.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))

Columns