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.

[Home] [Help]

VIEW: APPS.BEN_ENRT_RSLT_ELIG_CHC_V

Source

View Text - Preformatted


SELECT PEN.PERSON_ID , EPE.ELIG_PER_ELCTBL_CHC_ID , to_char(PEN.PRTT_ENRT_RSLT_ID) , PEN.PL_ID , PEN.OIPL_ID , EPE.CRNTLY_ENRD_FLAG , EPE.MNDTRY_FLAG , PEN.SSPNDD_FLAG , PIL.PER_IN_LER_ID , 'Y' , PEN.BUSINESS_GROUP_ID , to_char(PEN.OBJECT_VERSION_NUMBER) , PEN.EFFECTIVE_START_DATE , PEN.EFFECTIVE_END_DATE ,PEN.PEN_ATTRIBUTE_CATEGORY ,PEN.PEN_ATTRIBUTE1 ,PEN.PEN_ATTRIBUTE2 ,PEN.PEN_ATTRIBUTE3 ,PEN.PEN_ATTRIBUTE4 ,PEN.PEN_ATTRIBUTE5 ,PEN.PEN_ATTRIBUTE6 ,PEN.PEN_ATTRIBUTE7 ,PEN.PEN_ATTRIBUTE8 ,PEN.PEN_ATTRIBUTE9 ,PEN.PEN_ATTRIBUTE10 ,PEN.PEN_ATTRIBUTE11 ,PEN.PEN_ATTRIBUTE12 ,PEN.PEN_ATTRIBUTE13 ,PEN.PEN_ATTRIBUTE14 ,PEN.PEN_ATTRIBUTE15 ,PEN.PEN_ATTRIBUTE16 ,PEN.PEN_ATTRIBUTE17 ,PEN.PEN_ATTRIBUTE18 ,PEN.PEN_ATTRIBUTE19 ,PEN.PEN_ATTRIBUTE20 ,PEN.PEN_ATTRIBUTE21 ,PEN.PEN_ATTRIBUTE22 ,PEN.PEN_ATTRIBUTE23 ,PEN.PEN_ATTRIBUTE24 ,PEN.PEN_ATTRIBUTE25 ,PEN.PEN_ATTRIBUTE26 ,PEN.PEN_ATTRIBUTE27 ,PEN.PEN_ATTRIBUTE28 ,PEN.PEN_ATTRIBUTE29 ,PEN.PEN_ATTRIBUTE30 ,EPE.CTFN_RQD_FLAG ,EPE.ERLST_DEENRT_DT ,EPE.ELCTBL_FLAG ,PEN.ENRT_CVG_STRT_DT ,PEN.ENRT_CVG_THRU_DT ENRT_CVG_THRU_DT ,EPE.ENRT_CVG_STRT_DT_CD ,EPE.DPNT_DSGN_CD , PEN.ORGNL_ENRT_DT , PEN.ENRT_OVRID_RSN_CD , PEN.ENRT_OVRID_THRU_DT , DECODE(PEN.RPLCS_SSPNDD_RSLT_ID, null,'N','Y') INTERIM_FLAG , EPE.AUTO_ENRT_FLAG , EPE.MUST_ENRL_ANTHR_PL_ID ,PEL.ACTY_REF_PERD_CD ,PEL.UOM UOM ,PIL.LER_ID ,pel.enrt_perd_id ,pel.lee_rsn_id ,pel.cls_enrt_dt_to_use_cd ,pen.pl_ordr_num ,epe.in_pndg_wkflow_flag FROM BEN_PRTT_ENRT_RSLT_F PEN, BEN_ELIG_PER_ELCTBL_CHC EPE, BEN_PER_IN_LER PIL, BEN_PIL_ELCTBL_CHC_POPL PEL, FND_SESSIONS FND, ben_ler_f ler WHERE FND.SESSION_ID = USERENV('sessionid') AND FND.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE AND PEN.EFFECTIVE_END_DATE and pen.prtt_enrt_rslt_stat_cd is null AND EPE.PER_IN_LER_ID = PEL.PER_IN_LER_ID AND PIL.PER_IN_LER_ID = PEL.PER_IN_LER_ID AND PEL.PL_ID = EPE.PL_ID AND FND.EFFECTIVE_DATE BETWEEN PEL.ENRT_PERD_STRT_DT AND nvl(pel.procg_end_dt, nvl( PEL.ENRT_PERD_END_DT, fnd.effective_date)) 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.OIPL_ID IS NULL AND PIL.PER_IN_LER_STAT_CD = 'STRTD' AND PEN.BUSINESS_GROUP_ID +0 = PIL.BUSINESS_GROUP_ID AND PEN.PRTT_ENRT_RSLT_ID = EPE.PRTT_ENRT_RSLT_ID and ler.ler_id = pil.ler_id and ler.typ_cd not in ('COMP', 'IREC', 'GSP') and fnd.effective_date between ler.EFFECTIVE_START_DATE AND ler.EFFECTIVE_END_DATE UNION SELECT PIL.PERSON_ID ,EPE.ELIG_PER_ELCTBL_CHC_ID ,to_char(EPE.PRTT_ENRT_RSLT_ID) ,EPE.PL_ID ,EPE.OIPL_ID ,EPE.CRNTLY_ENRD_FLAG ,EPE.MNDTRY_FLAG ,'N' ,EPE.PER_IN_LER_ID ,'N' ,EPE.BUSINESS_GROUP_ID ,null ,to_date(NULL) ,to_date(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 ,NULL ,EPE.CTFN_RQD_FLAG ,EPE.ERLST_DEENRT_DT ,EPE.ELCTBL_FLAG ,EPE.ENRT_CVG_STRT_DT ,to_date(null) ,EPE.ENRT_CVG_STRT_DT_CD ,EPE.DPNT_DSGN_CD ,to_date(null) , null , to_date(null) , 'N' INTERIM_FLAG , EPE.AUTO_ENRT_FLAG , EPE.MUST_ENRL_ANTHR_PL_ID ,PEL.ACTY_REF_PERD_CD ,PEL.UOM UOM ,PIL.LER_ID ,pel.enrt_perd_id ,pel.lee_rsn_id ,pel.cls_enrt_dt_to_use_cd ,epe.pl_ordr_num ,epe.in_pndg_wkflow_flag FROM BEN_ELIG_PER_ELCTBL_CHC EPE, ben_pil_elctbl_chc_popl pel, BEN_PER_IN_LER PIL, FND_SESSIONS FND, ben_ler_f ler WHERE FND.SESSION_ID = USERENV('sessionid') AND PIL.PER_IN_LER_ID = EPE.PER_IN_LER_ID and pel.per_in_ler_id = pil.per_in_ler_id and pel.pl_id = epe.pl_id AND FND.EFFECTIVE_DATE BETWEEN PEL.ENRT_PERD_STRT_DT AND nvl(pel.procg_end_dt, nvl( PEL.ENRT_PERD_END_DT, fnd.effective_date)) AND EPE.OIPL_ID IS NULL AND PIL.PER_IN_LER_STAT_CD = 'STRTD' AND EPE.BUSINESS_GROUP_ID +0 = PIL.BUSINESS_GROUP_ID and pel.dflt_asnd_dt is null and not exists (select null from ben_prtt_enrt_rslt_f pen where epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and epe.per_in_ler_id = pen.per_in_ler_id and fnd.effective_date between pen.effective_start_date and pen.effective_end_date and pen.prtt_enrt_rslt_stat_cd is null and pen.enrt_cvg_thru_dt =TO_DATE('12/31/4712','MM/DD/rrrr') ) and ler.ler_id = pil.ler_id and ler.typ_cd not in ('COMP', 'IREC', 'GSP') and fnd.effective_date between ler.EFFECTIVE_START_DATE AND ler.EFFECTIVE_END_DATE

View Text - HTML Formatted

SELECT PEN.PERSON_ID
, EPE.ELIG_PER_ELCTBL_CHC_ID
, TO_CHAR(PEN.PRTT_ENRT_RSLT_ID)
, PEN.PL_ID
, PEN.OIPL_ID
, EPE.CRNTLY_ENRD_FLAG
, EPE.MNDTRY_FLAG
, PEN.SSPNDD_FLAG
, PIL.PER_IN_LER_ID
, 'Y'
, PEN.BUSINESS_GROUP_ID
, TO_CHAR(PEN.OBJECT_VERSION_NUMBER)
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE
, PEN.PEN_ATTRIBUTE_CATEGORY
, PEN.PEN_ATTRIBUTE1
, PEN.PEN_ATTRIBUTE2
, PEN.PEN_ATTRIBUTE3
, PEN.PEN_ATTRIBUTE4
, PEN.PEN_ATTRIBUTE5
, PEN.PEN_ATTRIBUTE6
, PEN.PEN_ATTRIBUTE7
, PEN.PEN_ATTRIBUTE8
, PEN.PEN_ATTRIBUTE9
, PEN.PEN_ATTRIBUTE10
, PEN.PEN_ATTRIBUTE11
, PEN.PEN_ATTRIBUTE12
, PEN.PEN_ATTRIBUTE13
, PEN.PEN_ATTRIBUTE14
, PEN.PEN_ATTRIBUTE15
, PEN.PEN_ATTRIBUTE16
, PEN.PEN_ATTRIBUTE17
, PEN.PEN_ATTRIBUTE18
, PEN.PEN_ATTRIBUTE19
, PEN.PEN_ATTRIBUTE20
, PEN.PEN_ATTRIBUTE21
, PEN.PEN_ATTRIBUTE22
, PEN.PEN_ATTRIBUTE23
, PEN.PEN_ATTRIBUTE24
, PEN.PEN_ATTRIBUTE25
, PEN.PEN_ATTRIBUTE26
, PEN.PEN_ATTRIBUTE27
, PEN.PEN_ATTRIBUTE28
, PEN.PEN_ATTRIBUTE29
, PEN.PEN_ATTRIBUTE30
, EPE.CTFN_RQD_FLAG
, EPE.ERLST_DEENRT_DT
, EPE.ELCTBL_FLAG
, PEN.ENRT_CVG_STRT_DT
, PEN.ENRT_CVG_THRU_DT ENRT_CVG_THRU_DT
, EPE.ENRT_CVG_STRT_DT_CD
, EPE.DPNT_DSGN_CD
, PEN.ORGNL_ENRT_DT
, PEN.ENRT_OVRID_RSN_CD
, PEN.ENRT_OVRID_THRU_DT
, DECODE(PEN.RPLCS_SSPNDD_RSLT_ID
, NULL
, 'N'
, 'Y') INTERIM_FLAG
, EPE.AUTO_ENRT_FLAG
, EPE.MUST_ENRL_ANTHR_PL_ID
, PEL.ACTY_REF_PERD_CD
, PEL.UOM UOM
, PIL.LER_ID
, PEL.ENRT_PERD_ID
, PEL.LEE_RSN_ID
, PEL.CLS_ENRT_DT_TO_USE_CD
, PEN.PL_ORDR_NUM
, EPE.IN_PNDG_WKFLOW_FLAG
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_PIL_ELCTBL_CHC_POPL PEL
, FND_SESSIONS FND
, BEN_LER_F LER
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND EPE.PER_IN_LER_ID = PEL.PER_IN_LER_ID
AND PIL.PER_IN_LER_ID = PEL.PER_IN_LER_ID
AND PEL.PL_ID = EPE.PL_ID
AND FND.EFFECTIVE_DATE BETWEEN PEL.ENRT_PERD_STRT_DT
AND NVL(PEL.PROCG_END_DT
, NVL( PEL.ENRT_PERD_END_DT
, FND.EFFECTIVE_DATE))
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.OIPL_ID IS NULL
AND PIL.PER_IN_LER_STAT_CD = 'STRTD'
AND PEN.BUSINESS_GROUP_ID +0 = PIL.BUSINESS_GROUP_ID
AND PEN.PRTT_ENRT_RSLT_ID = EPE.PRTT_ENRT_RSLT_ID
AND LER.LER_ID = PIL.LER_ID
AND LER.TYP_CD NOT IN ('COMP'
, 'IREC'
, 'GSP')
AND FND.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE UNION SELECT PIL.PERSON_ID
, EPE.ELIG_PER_ELCTBL_CHC_ID
, TO_CHAR(EPE.PRTT_ENRT_RSLT_ID)
, EPE.PL_ID
, EPE.OIPL_ID
, EPE.CRNTLY_ENRD_FLAG
, EPE.MNDTRY_FLAG
, 'N'
, EPE.PER_IN_LER_ID
, 'N'
, EPE.BUSINESS_GROUP_ID
, NULL
, TO_DATE(NULL)
, TO_DATE(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
, NULL
, EPE.CTFN_RQD_FLAG
, EPE.ERLST_DEENRT_DT
, EPE.ELCTBL_FLAG
, EPE.ENRT_CVG_STRT_DT
, TO_DATE(NULL)
, EPE.ENRT_CVG_STRT_DT_CD
, EPE.DPNT_DSGN_CD
, TO_DATE(NULL)
, NULL
, TO_DATE(NULL)
, 'N' INTERIM_FLAG
, EPE.AUTO_ENRT_FLAG
, EPE.MUST_ENRL_ANTHR_PL_ID
, PEL.ACTY_REF_PERD_CD
, PEL.UOM UOM
, PIL.LER_ID
, PEL.ENRT_PERD_ID
, PEL.LEE_RSN_ID
, PEL.CLS_ENRT_DT_TO_USE_CD
, EPE.PL_ORDR_NUM
, EPE.IN_PNDG_WKFLOW_FLAG
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PIL_ELCTBL_CHC_POPL PEL
, BEN_PER_IN_LER PIL
, FND_SESSIONS FND
, BEN_LER_F LER
WHERE FND.SESSION_ID = USERENV('SESSIONID')
AND PIL.PER_IN_LER_ID = EPE.PER_IN_LER_ID
AND PEL.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND PEL.PL_ID = EPE.PL_ID
AND FND.EFFECTIVE_DATE BETWEEN PEL.ENRT_PERD_STRT_DT
AND NVL(PEL.PROCG_END_DT
, NVL( PEL.ENRT_PERD_END_DT
, FND.EFFECTIVE_DATE))
AND EPE.OIPL_ID IS NULL
AND PIL.PER_IN_LER_STAT_CD = 'STRTD'
AND EPE.BUSINESS_GROUP_ID +0 = PIL.BUSINESS_GROUP_ID
AND PEL.DFLT_ASND_DT IS NULL
AND NOT EXISTS (SELECT NULL
FROM BEN_PRTT_ENRT_RSLT_F PEN
WHERE EPE.PRTT_ENRT_RSLT_ID = PEN.PRTT_ENRT_RSLT_ID
AND EPE.PER_IN_LER_ID = PEN.PER_IN_LER_ID
AND FND.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.ENRT_CVG_THRU_DT =TO_DATE('12/31/4712'
, 'MM/DD/RRRR') )
AND LER.LER_ID = PIL.LER_ID
AND LER.TYP_CD NOT IN ('COMP'
, 'IREC'
, 'GSP')
AND FND.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE