VIEW: APPS.BEN_ENRT_OVERRIDE_NON_OAB_V

Source

View Text - Preformatted

SELECT f.effective_date effective_start_date, opt.opt_id opt_id, opt.name opt_name, pgm.pgm_id pgm_id, pgm.name pgm_name, pl.pl_id pl_id, pl.name pl_name, typ.pl_typ_id pl_typ_id, typ.name pl_typ_name, 'N' sspndd_flag, decode(pgm.pgm_id, null, pl.nip_pl_uom, pgm.pgm_uom) uom, f.effective_date orgnl_enrt_dt, 'O' enrt_mthd_cd, 'Y' enrt_ovridn_flag, f.effective_date enrt_cvg_strt_dt, to_date('12/31/4712','MM/DD/rrrr') enrt_cvg_thru_dt, pl.business_group_id business_group_id from ben_pl_f pl, ben_plip_f plip, ben_pgm_f pgm, ben_oipl_f oipl, ben_opt_f opt, ben_pl_typ_f typ, fnd_sessions f WHERE pl.elig_apls_flag = 'N' and f.effective_date between pl.effective_start_date and pl.effective_end_date and pl.pl_id = plip.pl_id (+) and f.effective_date between nvl(plip.effective_start_date, f.effective_date) and nvl(plip.effective_end_date, f.effective_date) and plip.pgm_id = pgm.pgm_id (+) and pgm.elig_apls_flag (+) = 'N' and f.effective_date between nvl(pgm.effective_start_date, f.effective_date) and nvl(pgm.effective_end_date, f.effective_date) and pl.pl_id = oipl.pl_id (+) and oipl.elig_apls_flag (+) = 'N' and f.effective_date between nvl(oipl.effective_start_date, f.effective_date) and nvl(oipl.effective_end_date, f.effective_date) and oipl.opt_id = opt.opt_id (+) and f.effective_date between nvl(opt.effective_start_date, f.effective_date) and nvl(opt.effective_end_date, f.effective_date) and pl.pl_typ_id = typ.pl_typ_id and f.effective_date between typ.effective_start_date and typ.effective_end_date and f.session_id = userenv('sessionid')

View Text - HTML Formatted

SELECT F.EFFECTIVE_DATE EFFECTIVE_START_DATE
, OPT.OPT_ID OPT_ID
, OPT.NAME OPT_NAME
, PGM.PGM_ID PGM_ID
, PGM.NAME PGM_NAME
, PL.PL_ID PL_ID
, PL.NAME PL_NAME
, TYP.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_NAME
, 'N' SSPNDD_FLAG
, DECODE(PGM.PGM_ID
, NULL
, PL.NIP_PL_UOM
, PGM.PGM_UOM) UOM
, F.EFFECTIVE_DATE ORGNL_ENRT_DT
, 'O' ENRT_MTHD_CD
, 'Y' ENRT_OVRIDN_FLAG
, F.EFFECTIVE_DATE ENRT_CVG_STRT_DT
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR') ENRT_CVG_THRU_DT
, PL.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM BEN_PL_F PL
, BEN_PLIP_F PLIP
, BEN_PGM_F PGM
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
WHERE PL.ELIG_APLS_FLAG = 'N'
AND F.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND PL.PL_ID = PLIP.PL_ID (+)
AND F.EFFECTIVE_DATE BETWEEN NVL(PLIP.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PLIP.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND PLIP.PGM_ID = PGM.PGM_ID (+)
AND PGM.ELIG_APLS_FLAG (+) = 'N'
AND F.EFFECTIVE_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND PL.PL_ID = OIPL.PL_ID (+)
AND OIPL.ELIG_APLS_FLAG (+) = 'N'
AND F.EFFECTIVE_DATE BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(OIPL.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND OIPL.OPT_ID = OPT.OPT_ID (+)
AND F.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND PL.PL_TYP_ID = TYP.PL_TYP_ID
AND F.EFFECTIVE_DATE BETWEEN TYP.EFFECTIVE_START_DATE
AND TYP.EFFECTIVE_END_DATE
AND F.SESSION_ID = USERENV('SESSIONID')