VIEW: APPS.BEN_CWB_PERIOD_LOV_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)