eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.BEN_ENRT_OVERRIDE_V

Source

View Text - Preformatted


SELECT distinct f.effective_date effective_start_date , nvl(epo.per_in_ler_id,pep.per_in_ler_id) per_in_ler_id, to_char(epo.opt_id) opt_id , decode(epo.opt_id, null, null, opt.name) opt_name , to_char(pep.pgm_id) pgm_id , decode(pep.pgm_id, null, null, pgm.name) pgm_name , pep.pl_id pl_id , pl.name pl_name , typ.pl_typ_id pl_typ_id , typ.name pl_typ_name , 'N' sspndd_flag , decode(pep.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 , pep.person_id person_id , pep.business_group_id business_group_id , to_number(null) , to_number(null) , to_number(null) , epo.oipl_ordr_num from ben_pl_f pl , ben_pgm_f pgm , ben_opt_f opt , ben_elig_per_f pep , ben_elig_per_opt_f epo , ben_per_in_ler pil , ben_pl_typ_f typ , fnd_sessions f WHERE pep.elig_flag = 'Y' and f.effective_date between pep.effective_start_date and pep.effective_end_date and pep.pl_id = pl.pl_id and f.effective_date between pl.effective_start_date and pl.effective_end_date and (pep.pgm_id is null or (pep.pgm_id = pgm.pgm_id and f.effective_date between pgm.effective_start_date and pgm.effective_end_date)) and pep.elig_per_id = epo.elig_per_id (+) and epo.elig_flag (+) = 'Y' and f.effective_date between nvl(epo.effective_start_date ,f.effective_date) and nvl(epo.effective_end_date , f.effective_date) and (epo.opt_id is null or (epo.opt_id = opt.opt_id and f.effective_date between opt.effective_start_date and opt.effective_end_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') and pil.per_in_ler_id=nvl(epo.per_in_ler_id, pep.per_in_ler_id) and pil.business_group_id=nvl(epo.business_group_id , pep.business_group_id)+0 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or pil.per_in_ler_stat_cd is null ) and ( pl.svgs_pl_flag = 'N' OR exists ( select 'x' from ben_prtt_enrt_rslt_f pen where pen.per_in_ler_id = pil.per_in_ler_id and pen.pl_id = pl.pl_id and pl.svgs_pl_flag = 'Y' and pen.comp_lvl_cd = 'PLAN' and pen.prtt_enrt_rslt_stat_cd is null and f.effective_date between pen.effective_start_date and pen.effective_end_date )) UNION select distinct f.effective_date effective_start_date , pep.per_in_ler_id per_in_ler_id, null opt_id , null opt_name , null pgm_id , null pgm_name , pep.pl_id pl_id , pl.name pl_name , typ.pl_typ_id pl_typ_id , typ.name pl_typ_name , 'N' sspndd_flag , pl.nip_pl_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 , pep.person_id person_id , pep.business_group_id business_group_id , to_number(null) , to_number(null) , to_number(null) , to_number(null) from ben_pl_f pl , ben_elig_per_f pep , ben_per_in_ler pil , ben_pl_typ_f typ , fnd_sessions f WHERE pep.elig_flag = 'Y' and f.effective_date between pep.effective_start_date and pep.effective_end_date and pep.pl_id = pl.pl_id and pl.svgs_pl_flag = 'Y' and f.effective_date between pl.effective_start_date and pl.effective_end_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') and pil.per_in_ler_id=pep.per_in_ler_id and pil.business_group_id=pep.business_group_id+0 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or pil.per_in_ler_stat_cd is null ) MINUS select f.effective_date effective_start_date , pen.per_in_ler_id per_in_ler_id, to_char(opt.opt_id) opt_id , opt.name opt_name , to_char(pen.pgm_id) pgm_id , pgm.name pgm_name , pen.pl_id pl_id , pl.name pl_name , pen.pl_typ_id pl_typ_id , typ.name pl_typ_name , 'N' sspndd_flag ,pen.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 , pen.person_id person_id , pen.business_group_id business_group_id , to_number(null) , to_number(null) , to_number(null) , pen.oipl_ordr_num from ben_prtt_enrt_rslt_f pen , ben_pl_f pl , ben_pgm_f pgm , ben_oipl_f oipl , ben_opt_f opt , ben_per_in_ler pil , ben_pl_typ_f typ , fnd_sessions f where f.effective_date between pen.effective_start_date and pen.effective_end_date and pen.enrt_cvg_thru_dt = TO_DATE('12/31/4712','MM/DD/rrrr') and pen.prtt_enrt_rslt_stat_cd is null and pen.pl_id = pl.pl_id and f.effective_date between pl.effective_start_date and pl.effective_end_date and pen.pgm_id = pgm.pgm_id(+) and f.effective_date between nvl(pgm.effective_start_date, f.effective_date) and nvl(pgm.effective_end_date, f.effective_date) and pen.oipl_id = oipl.oipl_id (+) and f.effective_date between nvl(oipl.effective_start_date, f.effective_date) and nvl(oipl.effective_end_date, f.effective_date) and nvl(oipl.opt_id,-1) = 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 pen.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') and pil.per_in_ler_id=pen.per_in_ler_id and pil.business_group_id=pen.business_group_id+0 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or pil.per_in_ler_stat_cd is null )

View Text - HTML Formatted

SELECT DISTINCT F.EFFECTIVE_DATE EFFECTIVE_START_DATE
, NVL(EPO.PER_IN_LER_ID
, PEP.PER_IN_LER_ID) PER_IN_LER_ID
, TO_CHAR(EPO.OPT_ID) OPT_ID
, DECODE(EPO.OPT_ID
, NULL
, NULL
, OPT.NAME) OPT_NAME
, TO_CHAR(PEP.PGM_ID) PGM_ID
, DECODE(PEP.PGM_ID
, NULL
, NULL
, PGM.NAME) PGM_NAME
, PEP.PL_ID PL_ID
, PL.NAME PL_NAME
, TYP.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_NAME
, 'N' SSPNDD_FLAG
, DECODE(PEP.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
, PEP.PERSON_ID PERSON_ID
, PEP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, EPO.OIPL_ORDR_NUM
FROM BEN_PL_F PL
, BEN_PGM_F PGM
, BEN_OPT_F OPT
, BEN_ELIG_PER_F PEP
, BEN_ELIG_PER_OPT_F EPO
, BEN_PER_IN_LER PIL
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
WHERE PEP.ELIG_FLAG = 'Y'
AND F.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND PEP.PL_ID = PL.PL_ID
AND F.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND (PEP.PGM_ID IS NULL OR (PEP.PGM_ID = PGM.PGM_ID
AND F.EFFECTIVE_DATE BETWEEN PGM.EFFECTIVE_START_DATE
AND PGM.EFFECTIVE_END_DATE))
AND PEP.ELIG_PER_ID = EPO.ELIG_PER_ID (+)
AND EPO.ELIG_FLAG (+) = 'Y'
AND F.EFFECTIVE_DATE BETWEEN NVL(EPO.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(EPO.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND (EPO.OPT_ID IS NULL OR (EPO.OPT_ID = OPT.OPT_ID
AND F.EFFECTIVE_DATE BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_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')
AND PIL.PER_IN_LER_ID=NVL(EPO.PER_IN_LER_ID
, PEP.PER_IN_LER_ID)
AND PIL.BUSINESS_GROUP_ID=NVL(EPO.BUSINESS_GROUP_ID
, PEP.BUSINESS_GROUP_ID)+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )
AND ( PL.SVGS_PL_FLAG = 'N' OR EXISTS ( SELECT 'X'
FROM BEN_PRTT_ENRT_RSLT_F PEN
WHERE PEN.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND PEN.PL_ID = PL.PL_ID
AND PL.SVGS_PL_FLAG = 'Y'
AND PEN.COMP_LVL_CD = 'PLAN'
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND F.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE )) UNION SELECT DISTINCT F.EFFECTIVE_DATE EFFECTIVE_START_DATE
, PEP.PER_IN_LER_ID PER_IN_LER_ID
, NULL OPT_ID
, NULL OPT_NAME
, NULL PGM_ID
, NULL PGM_NAME
, PEP.PL_ID PL_ID
, PL.NAME PL_NAME
, TYP.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_NAME
, 'N' SSPNDD_FLAG
, PL.NIP_PL_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
, PEP.PERSON_ID PERSON_ID
, PEP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM BEN_PL_F PL
, BEN_ELIG_PER_F PEP
, BEN_PER_IN_LER PIL
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
WHERE PEP.ELIG_FLAG = 'Y'
AND F.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND PEP.PL_ID = PL.PL_ID
AND PL.SVGS_PL_FLAG = 'Y'
AND F.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_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')
AND PIL.PER_IN_LER_ID=PEP.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID=PEP.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL ) MINUS SELECT F.EFFECTIVE_DATE EFFECTIVE_START_DATE
, PEN.PER_IN_LER_ID PER_IN_LER_ID
, TO_CHAR(OPT.OPT_ID) OPT_ID
, OPT.NAME OPT_NAME
, TO_CHAR(PEN.PGM_ID) PGM_ID
, PGM.NAME PGM_NAME
, PEN.PL_ID PL_ID
, PL.NAME PL_NAME
, PEN.PL_TYP_ID PL_TYP_ID
, TYP.NAME PL_TYP_NAME
, 'N' SSPNDD_FLAG
, PEN.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
, PEN.PERSON_ID PERSON_ID
, PEN.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PEN.OIPL_ORDR_NUM
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PL_F PL
, BEN_PGM_F PGM
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PER_IN_LER PIL
, BEN_PL_TYP_F TYP
, FND_SESSIONS F
WHERE F.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.ENRT_CVG_THRU_DT = TO_DATE('12/31/4712'
, 'MM/DD/RRRR')
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.PL_ID = PL.PL_ID
AND F.EFFECTIVE_DATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND PEN.PGM_ID = PGM.PGM_ID(+)
AND F.EFFECTIVE_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND PEN.OIPL_ID = OIPL.OIPL_ID (+)
AND F.EFFECTIVE_DATE BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(OIPL.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND NVL(OIPL.OPT_ID
, -1) = 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 PEN.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')
AND PIL.PER_IN_LER_ID=PEN.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID=PEN.BUSINESS_GROUP_ID+0
AND ( PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT') OR PIL.PER_IN_LER_STAT_CD IS NULL )