VIEW: APPS.BEN_ENRT_RSLT_LOV_INFO_V

Source

View Text - Preformatted

SELECT PEN.PRTT_ENRT_RSLT_ID , PEN.OIPL_ID , PEN.PERSON_ID , PEN.PGM_ID , PEN.PL_ID , PEN.PTIP_ID , PEN.PL_TYP_ID , PEN.LER_ID , PEN.PER_IN_LER_ID , PEN.SSPNDD_FLAG , PLN.NAME , PGM.NAME , OPT.NAME , TYP.NAME , LER.NAME , PEN.EFFECTIVE_START_DATE , PEN.EFFECTIVE_END_DATE , PEN.BUSINESS_GROUP_ID , PEN.OBJECT_VERSION_NUMBER FROM BEN_PRTT_ENRT_RSLT_F PEN , ben_per_in_ler pil , ben_pl_f pln , ben_pgm_f pgm , ben_pl_typ_f typ , ben_oipl_f oipl , ben_opt_f opt , ben_ler_f ler , FND_SESSIONS SE WHERE pen.per_in_ler_id = pil.per_in_ler_id and pil.per_in_ler_stat_cd = 'STRTD' and pen.prtt_enrt_rslt_stat_cd is null and se.session_id = userenv('sessionid') and se.effective_date between pen.effective_start_date and pen.effective_end_date and pen.pl_id = pln.pl_id and pln.pl_stat_cd = 'A' and se.effective_date between pln.effective_start_date and pln.effective_end_date and pen.pl_typ_id = typ.pl_typ_id and typ.pl_typ_stat_cd = 'A' and se.effective_date between typ.effective_start_date and typ.effective_end_date and pen.ler_id = ler.ler_id and se.effective_date between ler.effective_start_date and ler.effective_end_date and pen.pgm_id = pgm.pgm_id(+) and pgm.pgm_stat_cd(+) = 'A' and se.effective_date between nvl(pgm.effective_start_date, se.effective_date) and nvl(pgm.effective_end_date, se.effective_date) and pen.oipl_id = oipl.oipl_id(+) and oipl.oipl_stat_cd(+) = 'A' and se.effective_date between nvl(oipl.effective_start_date, se.effective_date) and nvl(oipl.effective_end_date, se.effective_date) and oipl.opt_id = opt.opt_id(+) and se.effective_date between nvl(opt.effective_start_date, se.effective_date) and nvl(opt.effective_end_date, se.effective_date)

View Text - HTML Formatted

SELECT PEN.PRTT_ENRT_RSLT_ID
, PEN.OIPL_ID
, PEN.PERSON_ID
, PEN.PGM_ID
, PEN.PL_ID
, PEN.PTIP_ID
, PEN.PL_TYP_ID
, PEN.LER_ID
, PEN.PER_IN_LER_ID
, PEN.SSPNDD_FLAG
, PLN.NAME
, PGM.NAME
, OPT.NAME
, TYP.NAME
, LER.NAME
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE
, PEN.BUSINESS_GROUP_ID
, PEN.OBJECT_VERSION_NUMBER
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PER_IN_LER PIL
, BEN_PL_F PLN
, BEN_PGM_F PGM
, BEN_PL_TYP_F TYP
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_LER_F LER
, FND_SESSIONS SE
WHERE PEN.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND PIL.PER_IN_LER_STAT_CD = 'STRTD'
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND SE.SESSION_ID = USERENV('SESSIONID')
AND SE.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.PL_ID = PLN.PL_ID
AND PLN.PL_STAT_CD = 'A'
AND SE.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND PEN.PL_TYP_ID = TYP.PL_TYP_ID
AND TYP.PL_TYP_STAT_CD = 'A'
AND SE.EFFECTIVE_DATE BETWEEN TYP.EFFECTIVE_START_DATE
AND TYP.EFFECTIVE_END_DATE
AND PEN.LER_ID = LER.LER_ID
AND SE.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE
AND PEN.PGM_ID = PGM.PGM_ID(+)
AND PGM.PGM_STAT_CD(+) = 'A'
AND SE.EFFECTIVE_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND PEN.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OIPL_STAT_CD(+) = 'A'
AND SE.EFFECTIVE_DATE BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(OIPL.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)