View: HR_ATH_ENROLL_ALL_PLANS_V

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

View Text

SELECT TO_CHAR(PEN.PERSON_ID) EMPLOYEE_ID
, TO_CHAR(PEN.PL_ID)||'A'||TO_CHAR(PLIP.PLIP_ID) PLAN_ID
, PEN.ENRT_CVG_STRT_DT START_DATE
, NVL(PEN.ENRT_CVG_THRU_DT
, TO_DATE('12/31/4712'
, 'MM/DD/YYYY')) END_DATE
, OPT.NAME COVERAGE_TIER
, TO_CHAR(PEN.BNFT_AMT) COVERAGE_AMOUNT
, HR_AUTH_INT.GET_COVERAGE(PEN.PRTT_ENRT_RSLT_ID
, 'EE') COVERAGE_COST
, HR_AUTH_INT.GET_COVERAGE(PEN.PRTT_ENRT_RSLT_ID
, 'ER') ER_CONTRIBUTION
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PLIP_F PLIP
WHERE PEN.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(OIPL.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(OPT.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND TRUNC(SYSDATE) BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.PGM_ID = PLIP.PGM_ID(+)
AND PEN.PL_ID = PLIP.PL_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(PLIP.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(PLIP.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))

Columns