VIEW: APPS.BEN_DEENROLL_V

Source

View Text - Preformatted

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 or (pen.prtt_enrt_rslt_stat_cd = 'VOIDD' and pen.enrt_cvg_strt_dt > nvl(pen.enrt_cvg_thru_dt, pen.enrt_cvg_strt_dt))) 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 (+)

View Text - HTML Formatted

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 OR (PEN.PRTT_ENRT_RSLT_STAT_CD = 'VOIDD'
AND PEN.ENRT_CVG_STRT_DT > NVL(PEN.ENRT_CVG_THRU_DT
, PEN.ENRT_CVG_STRT_DT)))
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 (+)