View: BEN_CWB_PERIOD_LOV_V

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

View Text

SELECT DISTINCT NVL(LTRIM(TO_CHAR(WYR.STRT_MO
, '09'))
, TO_CHAR(YR.START_DATE
, 'MM')) || '/' || TO_CHAR(YR.START_DATE
, 'YYYY') || '-' || NVL(LTRIM(TO_CHAR(WYR.END_MO
, '09'))
, TO_CHAR(YR.END_DATE
, 'MM')) || '/' || TO_CHAR(YR.END_DATE
, 'YYYY') || ' ' || PL.NAME VALUE
, PERD.ENRT_PERD_ID ID
FROM BEN_YR_PERD YR
, BEN_WTHN_YR_PERD WYR
, BEN_ENRT_PERD PERD
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PER_IN_LER PIL
, BEN_PL_F PL
, BEN_PL_TYP_F PT
WHERE PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PER_IN_LER_ID = POPL.PER_IN_LER_ID
AND POPL.PL_ID = PL.PL_ID
AND POPL.ENRT_TYP_CYCL_CD = 'COMP'
AND PL.PL_STAT_CD = 'A'
AND PL.PL_TYP_ID = PT.PL_TYP_ID
AND PT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PT.EFFECTIVE_START_DATE
AND PT.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND POPL.ENRT_PERD_ID = PERD.ENRT_PERD_ID
AND PERD.YR_PERD_ID = YR.YR_PERD_ID
AND PERD.WTHN_YR_PERD_ID = WYR.WTHN_YR_PERD_ID (+)

Columns