View: BEN_DEENROLL_V

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

View Text

SELECT PGM.NAME ||' '||PLN.NAME||' '|| DECODE(PEN.OIPL_ID
, NULL
, NULL
, (SELECT OPT.NAME
FROM BEN_OPT_F OPT
, BEN_OIPL_F OIPL
, FND_SESSIONS F
WHERE PEN.OIPL_ID = OIPL.OIPL_ID
AND OIPL.OPT_ID = OPT.OPT_ID
AND F.SESSION_ID = USERENV('SESSIONID')
AND F.EFFECTIVE_DATE BETWEEN OIPL.EFFECTIVE_START_DATE
AND OIPL.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE )) COMP_OBJECT
, ENRT_CVG_THRU_DT COVERAGE_END_DATE
, PEN.PERSON_ID
, PEN.BUSINESS_GROUP_ID
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE
, PEN.PRTT_ENRT_RSLT_ID
FROM BEN_PRTT_ENRT_RSLT_F PEN
, FND_SESSIONS F
, BEN_PL_F PLN
, BEN_PGM_F PGM
WHERE F.SESSION_ID = USERENV('SESSIONID')
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.ENRT_CVG_THRU_DT <> TO_DATE('12/31/4712'
, 'MM/DD/RRRR')
AND PEN.COMP_LVL_CD NOT IN ('PLANFC'
, 'PLANIMP')
AND PGM.PGM_ID (+)= PEN.PGM_ID
AND PLN.PL_ID (+)= PEN.PL_ID
AND PEN.EFFECTIVE_START_DATE BETWEEN PGM.EFFECTIVE_START_DATE (+)
AND PGM.EFFECTIVE_END_DATE (+)
AND PEN.EFFECTIVE_START_DATE BETWEEN PLN.EFFECTIVE_START_DATE (+)
AND PLN.EFFECTIVE_END_DATE (+)

Columns