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_RSLTS_INFO_V

Source

View Text - Preformatted


SELECT E.PLIP_ORDR_NUM ,E.OIPL_ORDR_NUM ,EPE.PL_TYP_ID PL_TYP_ID , EPE.PL_ID PL_ID , EPE.OIPL_ID OIPL_ID , EPE.PGM_ID , EPE.PTIP_ID , EPE.PLIP_ID , B.NNMNTRY_UOM bnft_nnmntry_uom , E.bnft_amt BNFT_VAL , B.DFLT_VAL , B.MN_VAL , B.MX_VAL , b.mx_wout_ctfn_val , B.INCRMT_VAL , B.DFLT_FLAG BNFT_DFLT_FLAG , E.ORGNL_ENRT_DT , E.ENRT_OVRID_RSN_CD , E.ENRT_OVRID_THRU_DT , DECODE(E.RPLCS_SSPNDD_RSLT_ID , null ,'N' ,'Y') INTERIM_FLAG , EPE.AUTO_ENRT_FLAG , EPE.MUST_ENRL_ANTHR_PL_ID , B.BNFT_TYP_CD , B.CVG_MLT_CD , B.ENTR_VAL_AT_ENRT_FLAG , DECODE(EPE.CRNTLY_ENRD_FLAG,'N','N','Y', DECODE(B.ENRT_BNFT_ID,NULL,'Y',B.CRNTLY_ENRLD_FLAG)) CRNTLY_ENRD_FLAG , EPE.BUSINESS_GROUP_ID ,e.erlst_deenrt_dt , E.ENRT_CVG_STRT_DT , EPE.ENRT_CVG_STRT_DT_CD , E.ENRT_CVG_THRU_DT ,EPE.DPNT_DSGN_CD , EPE.MNDTRY_FLAG MNDTRY_FLAG , NVL(E.SSPNDD_FLAG ,'N') SSPNDD_FLAG , 'Y' DFLT_FLAG , EPE.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID , EPE.PER_IN_LER_ID , E.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID , B.ENRT_BNFT_ID ENRT_BNFT_ID , E.EFFECTIVE_START_DATE EFFECTIVE_START_DATE , E.EFFECTIVE_END_DATE EFFECTIVE_END_DATE , TO_CHAR(E.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER , DECODE(B.ENRT_BNFT_ID , NULL , EPE.CTFN_RQD_FLAG , B.CTFN_RQD_FLAG) CTFN_RQD_FLAG ,E.PEN_ATTRIBUTE_CATEGORY ,E.PEN_ATTRIBUTE1 ,E.PEN_ATTRIBUTE2 ,E.PEN_ATTRIBUTE3 ,E.PEN_ATTRIBUTE4 ,E.PEN_ATTRIBUTE5 ,E.PEN_ATTRIBUTE6 ,E.PEN_ATTRIBUTE7 ,E.PEN_ATTRIBUTE8 ,E.PEN_ATTRIBUTE9 ,E.PEN_ATTRIBUTE10 ,E.PEN_ATTRIBUTE11 ,E.PEN_ATTRIBUTE12 ,E.PEN_ATTRIBUTE13 ,E.PEN_ATTRIBUTE14 ,E.PEN_ATTRIBUTE15 ,E.PEN_ATTRIBUTE16 ,E.PEN_ATTRIBUTE17 ,E.PEN_ATTRIBUTE18 ,E.PEN_ATTRIBUTE19 ,E.PEN_ATTRIBUTE20 ,E.PEN_ATTRIBUTE21 ,E.PEN_ATTRIBUTE22 ,E.PEN_ATTRIBUTE23 ,E.PEN_ATTRIBUTE24 ,E.PEN_ATTRIBUTE25 ,E.PEN_ATTRIBUTE26 ,E.PEN_ATTRIBUTE27 ,E.PEN_ATTRIBUTE28 ,E.PEN_ATTRIBUTE29 ,E.PEN_ATTRIBUTE30 ,'N' from_choice_flag ,to_number(null) pre_tax_val ,E.PL_ORDR_NUM ,E.PTIP_ORDR_NUM ,epe.in_pndg_wkflow_flag FROM BEN_ELIG_PER_ELCTBL_CHC epe , ben_per_in_ler pil , BEN_ENRT_BNFT B , BEN_PRTT_ENRT_RSLT_F E , FND_SESSIONS F , ben_ler_v ler WHERE epe.comp_lvl_cd not in ('PLANFC' ,'PLANIMP') AND decode(b.enrt_bnft_id , null,epe.prtt_enrt_rslt_id, B.PRTT_ENRT_RSLT_ID) = E.PRTT_ENRT_RSLT_ID AND E.ENRT_CVG_THRU_DT =TO_DATE('12/31/4712','MM/DD/rrrr') AND F.EFFECTIVE_DATE BETWEEN E.EFFECTIVE_START_DATE AND E.EFFECTIVE_END_DATE and e.prtt_enrt_rslt_stat_cd is null and epe.elctbl_flag = 'Y' AND B.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID AND F.SESSION_ID = USERENV('sessionid') and pil.per_in_ler_id=epe.per_in_ler_id and epe.per_in_ler_id = e.per_in_ler_id(+) and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') and pil.ler_id = ler.ler_id and ler.typ_cd not in ('COMP', 'IREC', 'GSP') and F.EFFECTIVE_DATE BETWEEN ler.EFFECTIVE_START_DATE AND ler.EFFECTIVE_END_DATE UNION SELECT epe.PLIP_ORDR_NUM , epe.oipl_ordr_num , EPE.PL_TYP_ID PL_TYP_ID , EPE.PL_ID PL_ID , EPE.OIPL_ID OIPL_ID , EPE.PGM_ID , EPE.PTIP_ID , EPE.PLIP_ID , B.NNMNTRY_UOM bnft_nnmntry_uom , nvl(B.DFLT_VAL , B.VAL) BNFT_VAL , B.DFLT_VAL ,B.MN_VAL , B.MX_VAL ,b.mx_wout_ctfn_val , B.INCRMT_VAL , B.DFLT_FLAG BNFT_DFLT_FLAG , to_date(null) , null , to_date(null) , 'N' INTERIM_FLAG , EPE.AUTO_ENRT_FLAG , EPE.MUST_ENRL_ANTHR_PL_ID , B.BNFT_TYP_CD , B.CVG_MLT_CD , B.ENTR_VAL_AT_ENRT_FLAG , DECODE(EPE.CRNTLY_ENRD_FLAG,'N','N','Y', DECODE(B.ENRT_BNFT_ID,NULL,'Y',B.CRNTLY_ENRLD_FLAG)) CRNTLY_ENRD_FLAG , EPE.BUSINESS_GROUP_ID , epe.erlst_deenrt_dt , EPE.ENRT_CVG_STRT_DT , EPE.ENRT_CVG_STRT_DT_CD , to_date(null) enrt_cvg_thru_dt , EPE.DPNT_DSGN_CD , EPE.MNDTRY_FLAG MNDTRY_FLAG , 'N' SSPNDD_FLAG , DECODE(EPE.DFLT_FLAG,'N','N','Y', DECODE(B.ENRT_BNFT_ID,NULL,'Y',B.DFLT_FLAG)) DFLT_FLAG , EPE.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID , EPE.PER_IN_LER_ID , EPE.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID , B.ENRT_BNFT_ID ENRT_BNFT_ID , to_date(null) EFFECTIVE_START_DATE , to_Date(NULL) EFFECTIVE_END_DATE , NULL OBJECT_VERSION_NUMBER , DECODE(B.ENRT_BNFT_ID ,NULL , EPE.CTFN_RQD_FLAG , B.CTFN_RQD_FLAG) CTFN_RQD_FLAG , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null, 'Y' from_choice_flag , to_number(null) pre_tax_val ,epe.PL_ORDR_NUM ,epe.PTIP_ORDR_NUM ,epe.in_pndg_wkflow_flag FROM BEN_ELIG_PER_ELCTBL_CHC epe , ben_per_in_ler pil , BEN_PIL_ELCTBL_CHC_POPL PEL , BEN_ENRT_BNFT B , ben_ler_v ler , FND_SESSIONS F WHERE epe.comp_lvl_cd not in ('PLANFC' ,'PLANIMP') AND B.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID and (epe.dflt_flag = 'Y' or nvl(b.dflt_flag ,'Y') = 'Y') AND ( EPE.ELCTBL_FLAG = 'Y' and AUTO_ENRT_FLAG = 'N') AND PEL.PER_IN_LER_ID = EPE.PER_IN_LER_ID AND PEL.PGM_ID = EPE.PGM_ID AND ( PEL.ELCNS_MADE_DT IS NULL or ( PEL.ELCNS_MADE_DT < F.EFFECTIVE_DATE and ler.typ_cd = 'SCHEDDU' and /*epe.prtt_enrt_rslt_id is null and */ (( not exists (select null from ben_elig_per_elctbl_chc where per_in_ler_id = epe.per_in_ler_id and prtt_enrt_rslt_id is not null) OR ( epe.dflt_flag = 'Y' AND epe.prtt_enrt_rslt_id IS NULL AND NOT EXISTS ( SELECT '1' FROM ben_prtt_enrt_rslt_f pen WHERE pen.enrt_cvg_thru_dt = to_date('12/31/4712' ,'MM/DD/rrrr') AND f.effective_date BETWEEN pen.effective_start_date AND pen.effective_end_date AND pen.prtt_enrt_rslt_stat_cd is NULL AND nvl(epe.pgm_id ,-1) = nvl(pen.pgm_id ,-1) AND nvl(epe.pl_typ_id ,-1) = nvl(pen.pl_typ_id ,-1) /*AND nvl(epe.pl_id ,-1) = nvl(pen.pl_id ,-1) AND nvl(epe.oipl_id ,-1) = nvl(pen.oipl_id ,-1)*/ ) ) ) ) ) ) and pel.dflt_asnd_dt is null and pil.per_in_ler_id=epe.per_in_ler_id and pil.business_group_id+0=epe.business_group_id+0 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') and pil.ler_id = ler.ler_id and ler.typ_cd not in ('COMP', 'IREC', 'GSP') and F.EFFECTIVE_DATE BETWEEN ler.EFFECTIVE_START_DATE AND ler.EFFECTIVE_END_DATE and F.SESSION_ID = USERENV('sessionid') and (ler.typ_cd = 'SCHEDDU' OR not exists ( select 'x' from ben_prtt_enrt_rslt_f pen where F.EFFECTIVE_DATE BETWEEN pen.effective_start_date and pen.effective_end_date and pen.effective_end_date = to_date('12/31/4712','MM/DD/RRRR') and pen.enrt_cvg_thru_dt = to_date('12/31/4712','MM/DD/RRRR') and pen.per_in_ler_id = epe.per_in_ler_id and pen.pgm_id = epe.pgm_id and pen.pl_id = epe.pl_id and nvl(pen.oipl_id,-1)= nvl(epe.oipl_id,-1) )) order by 78, 2

View Text - HTML Formatted

SELECT E.PLIP_ORDR_NUM
, E.OIPL_ORDR_NUM
, EPE.PL_TYP_ID PL_TYP_ID
, EPE.PL_ID PL_ID
, EPE.OIPL_ID OIPL_ID
, EPE.PGM_ID
, EPE.PTIP_ID
, EPE.PLIP_ID
, B.NNMNTRY_UOM BNFT_NNMNTRY_UOM
, E.BNFT_AMT BNFT_VAL
, B.DFLT_VAL
, B.MN_VAL
, B.MX_VAL
, B.MX_WOUT_CTFN_VAL
, B.INCRMT_VAL
, B.DFLT_FLAG BNFT_DFLT_FLAG
, E.ORGNL_ENRT_DT
, E.ENRT_OVRID_RSN_CD
, E.ENRT_OVRID_THRU_DT
, DECODE(E.RPLCS_SSPNDD_RSLT_ID
, NULL
, 'N'
, 'Y') INTERIM_FLAG
, EPE.AUTO_ENRT_FLAG
, EPE.MUST_ENRL_ANTHR_PL_ID
, B.BNFT_TYP_CD
, B.CVG_MLT_CD
, B.ENTR_VAL_AT_ENRT_FLAG
, DECODE(EPE.CRNTLY_ENRD_FLAG
, 'N'
, 'N'
, 'Y'
, DECODE(B.ENRT_BNFT_ID
, NULL
, 'Y'
, B.CRNTLY_ENRLD_FLAG)) CRNTLY_ENRD_FLAG
, EPE.BUSINESS_GROUP_ID
, E.ERLST_DEENRT_DT
, E.ENRT_CVG_STRT_DT
, EPE.ENRT_CVG_STRT_DT_CD
, E.ENRT_CVG_THRU_DT
, EPE.DPNT_DSGN_CD
, EPE.MNDTRY_FLAG MNDTRY_FLAG
, NVL(E.SSPNDD_FLAG
, 'N') SSPNDD_FLAG
, 'Y' DFLT_FLAG
, EPE.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID
, EPE.PER_IN_LER_ID
, E.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID
, B.ENRT_BNFT_ID ENRT_BNFT_ID
, E.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, E.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, TO_CHAR(E.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
, DECODE(B.ENRT_BNFT_ID
, NULL
, EPE.CTFN_RQD_FLAG
, B.CTFN_RQD_FLAG) CTFN_RQD_FLAG
, E.PEN_ATTRIBUTE_CATEGORY
, E.PEN_ATTRIBUTE1
, E.PEN_ATTRIBUTE2
, E.PEN_ATTRIBUTE3
, E.PEN_ATTRIBUTE4
, E.PEN_ATTRIBUTE5
, E.PEN_ATTRIBUTE6
, E.PEN_ATTRIBUTE7
, E.PEN_ATTRIBUTE8
, E.PEN_ATTRIBUTE9
, E.PEN_ATTRIBUTE10
, E.PEN_ATTRIBUTE11
, E.PEN_ATTRIBUTE12
, E.PEN_ATTRIBUTE13
, E.PEN_ATTRIBUTE14
, E.PEN_ATTRIBUTE15
, E.PEN_ATTRIBUTE16
, E.PEN_ATTRIBUTE17
, E.PEN_ATTRIBUTE18
, E.PEN_ATTRIBUTE19
, E.PEN_ATTRIBUTE20
, E.PEN_ATTRIBUTE21
, E.PEN_ATTRIBUTE22
, E.PEN_ATTRIBUTE23
, E.PEN_ATTRIBUTE24
, E.PEN_ATTRIBUTE25
, E.PEN_ATTRIBUTE26
, E.PEN_ATTRIBUTE27
, E.PEN_ATTRIBUTE28
, E.PEN_ATTRIBUTE29
, E.PEN_ATTRIBUTE30
, 'N' FROM_CHOICE_FLAG
, TO_NUMBER(NULL) PRE_TAX_VAL
, E.PL_ORDR_NUM
, E.PTIP_ORDR_NUM
, EPE.IN_PNDG_WKFLOW_FLAG
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_ENRT_BNFT B
, BEN_PRTT_ENRT_RSLT_F E
, FND_SESSIONS F
, BEN_LER_V LER
WHERE EPE.COMP_LVL_CD NOT IN ('PLANFC'
, 'PLANIMP')
AND DECODE(B.ENRT_BNFT_ID
, NULL
, EPE.PRTT_ENRT_RSLT_ID
, B.PRTT_ENRT_RSLT_ID) = E.PRTT_ENRT_RSLT_ID
AND E.ENRT_CVG_THRU_DT =TO_DATE('12/31/4712'
, 'MM/DD/RRRR')
AND F.EFFECTIVE_DATE BETWEEN E.EFFECTIVE_START_DATE
AND E.EFFECTIVE_END_DATE
AND E.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND EPE.ELCTBL_FLAG = 'Y'
AND B.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND F.SESSION_ID = USERENV('SESSIONID')
AND PIL.PER_IN_LER_ID=EPE.PER_IN_LER_ID
AND EPE.PER_IN_LER_ID = E.PER_IN_LER_ID(+)
AND PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT')
AND PIL.LER_ID = LER.LER_ID
AND LER.TYP_CD NOT IN ('COMP'
, 'IREC'
, 'GSP')
AND F.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE UNION SELECT EPE.PLIP_ORDR_NUM
, EPE.OIPL_ORDR_NUM
, EPE.PL_TYP_ID PL_TYP_ID
, EPE.PL_ID PL_ID
, EPE.OIPL_ID OIPL_ID
, EPE.PGM_ID
, EPE.PTIP_ID
, EPE.PLIP_ID
, B.NNMNTRY_UOM BNFT_NNMNTRY_UOM
, NVL(B.DFLT_VAL
, B.VAL) BNFT_VAL
, B.DFLT_VAL
, B.MN_VAL
, B.MX_VAL
, B.MX_WOUT_CTFN_VAL
, B.INCRMT_VAL
, B.DFLT_FLAG BNFT_DFLT_FLAG
, TO_DATE(NULL)
, NULL
, TO_DATE(NULL)
, 'N' INTERIM_FLAG
, EPE.AUTO_ENRT_FLAG
, EPE.MUST_ENRL_ANTHR_PL_ID
, B.BNFT_TYP_CD
, B.CVG_MLT_CD
, B.ENTR_VAL_AT_ENRT_FLAG
, DECODE(EPE.CRNTLY_ENRD_FLAG
, 'N'
, 'N'
, 'Y'
, DECODE(B.ENRT_BNFT_ID
, NULL
, 'Y'
, B.CRNTLY_ENRLD_FLAG)) CRNTLY_ENRD_FLAG
, EPE.BUSINESS_GROUP_ID
, EPE.ERLST_DEENRT_DT
, EPE.ENRT_CVG_STRT_DT
, EPE.ENRT_CVG_STRT_DT_CD
, TO_DATE(NULL) ENRT_CVG_THRU_DT
, EPE.DPNT_DSGN_CD
, EPE.MNDTRY_FLAG MNDTRY_FLAG
, 'N' SSPNDD_FLAG
, DECODE(EPE.DFLT_FLAG
, 'N'
, 'N'
, 'Y'
, DECODE(B.ENRT_BNFT_ID
, NULL
, 'Y'
, B.DFLT_FLAG)) DFLT_FLAG
, EPE.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID
, EPE.PER_IN_LER_ID
, EPE.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID
, B.ENRT_BNFT_ID ENRT_BNFT_ID
, TO_DATE(NULL) EFFECTIVE_START_DATE
, TO_DATE(NULL) EFFECTIVE_END_DATE
, NULL OBJECT_VERSION_NUMBER
, DECODE(B.ENRT_BNFT_ID
, NULL
, EPE.CTFN_RQD_FLAG
, B.CTFN_RQD_FLAG) CTFN_RQD_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'Y' FROM_CHOICE_FLAG
, TO_NUMBER(NULL) PRE_TAX_VAL
, EPE.PL_ORDR_NUM
, EPE.PTIP_ORDR_NUM
, EPE.IN_PNDG_WKFLOW_FLAG
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_PIL_ELCTBL_CHC_POPL PEL
, BEN_ENRT_BNFT B
, BEN_LER_V LER
, FND_SESSIONS F
WHERE EPE.COMP_LVL_CD NOT IN ('PLANFC'
, 'PLANIMP')
AND B.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND (EPE.DFLT_FLAG = 'Y' OR NVL(B.DFLT_FLAG
, 'Y') = 'Y')
AND ( EPE.ELCTBL_FLAG = 'Y'
AND AUTO_ENRT_FLAG = 'N')
AND PEL.PER_IN_LER_ID = EPE.PER_IN_LER_ID
AND PEL.PGM_ID = EPE.PGM_ID
AND ( PEL.ELCNS_MADE_DT IS NULL OR ( PEL.ELCNS_MADE_DT < F.EFFECTIVE_DATE
AND LER.TYP_CD = 'SCHEDDU'
AND /*EPE.PRTT_ENRT_RSLT_ID IS NULL
AND */ (( NOT EXISTS (SELECT NULL
FROM BEN_ELIG_PER_ELCTBL_CHC
WHERE PER_IN_LER_ID = EPE.PER_IN_LER_ID
AND PRTT_ENRT_RSLT_ID IS NOT NULL) OR ( EPE.DFLT_FLAG = 'Y'
AND EPE.PRTT_ENRT_RSLT_ID IS NULL
AND NOT EXISTS ( SELECT '1'
FROM BEN_PRTT_ENRT_RSLT_F PEN
WHERE PEN.ENRT_CVG_THRU_DT = TO_DATE('12/31/4712'
, 'MM/DD/RRRR')
AND F.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND NVL(EPE.PGM_ID
, -1) = NVL(PEN.PGM_ID
, -1)
AND NVL(EPE.PL_TYP_ID
, -1) = NVL(PEN.PL_TYP_ID
, -1) /*AND NVL(EPE.PL_ID
, -1) = NVL(PEN.PL_ID
, -1)
AND NVL(EPE.OIPL_ID
, -1) = NVL(PEN.OIPL_ID
, -1)*/ ) ) ) ) ) )
AND PEL.DFLT_ASND_DT IS NULL
AND PIL.PER_IN_LER_ID=EPE.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID+0=EPE.BUSINESS_GROUP_ID+0
AND PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT')
AND PIL.LER_ID = LER.LER_ID
AND LER.TYP_CD NOT IN ('COMP'
, 'IREC'
, 'GSP')
AND F.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE
AND F.SESSION_ID = USERENV('SESSIONID')
AND (LER.TYP_CD = 'SCHEDDU' OR NOT EXISTS ( SELECT 'X'
FROM BEN_PRTT_ENRT_RSLT_F PEN
WHERE F.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.EFFECTIVE_END_DATE = TO_DATE('12/31/4712'
, 'MM/DD/RRRR')
AND PEN.ENRT_CVG_THRU_DT = TO_DATE('12/31/4712'
, 'MM/DD/RRRR')
AND PEN.PER_IN_LER_ID = EPE.PER_IN_LER_ID
AND PEN.PGM_ID = EPE.PGM_ID
AND PEN.PL_ID = EPE.PL_ID
AND NVL(PEN.OIPL_ID
, -1)= NVL(EPE.OIPL_ID
, -1) )) ORDER BY 78
, 2