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_ELCTBL_CHC_BNFT_V

Source

View Text - Preformatted


SELECT epe.per_in_ler_id ,decode(opt.name,null,pln.name, pln.name||': '||opt.name) pl_and_opt_names ,epe.pil_elctbl_chc_popl_id ,epe.business_group_id ,epe.pl_id ,epe.oipl_id ,enb.val ,epe.pl_typ_id ,enb.nnmntry_uom ,epe.crntly_enrd_flag ,epe.enrt_cvg_strt_dt_cd ,epe.enrt_cvg_strt_dt ,epe.dpnt_dsgn_cd ,epe.ler_chg_dpnt_cvg_cd ,epe.dpnt_cvg_strt_dt_cd ,enb.bnft_typ_cd ,enb.cvg_mlt_cd ,enb.entr_val_at_enrt_flag ,enb.mn_val ,enb.mx_val ,enb.incrmt_val ,enb.dflt_val ,epe.elctbl_flag ,epe.ctfn_rqd_flag ,epe.must_enrl_anthr_pl_id ,epe.elig_per_elctbl_chc_id ,decode(enb.enrt_bnft_id , null , epe.elig_per_elctbl_chc_id , null) fake_choice_id ,epe.erlst_deenrt_dt ,enb.enrt_bnft_id ,epe.dflt_flag ,epe.mndtry_flag ,epe.auto_enrt_flag ,epe.roll_crs_flag ,epe.elig_flag ,epe.INELIG_RSN_CD ,epe.ELIG_OVRID_PERSON_ID ,epe.ELIG_OVRID_DT ,epe.MGR_OVRID_PERSON_ID ,epe.MGR_OVRID_DT ,epe.WS_MGR_ID ,epe.COMMENTS ,ctp.ordr_num ptip_ordr_num ,ptp.name pl_typ_name ,cpp.ordr_num plip_ordr_num ,pln.ordr_num pl_ordr_num ,pln.name pl_name ,cop.ordr_num oipl_ordr_num ,opt.name opt_name ,enb.ordr_num enb_ordr_num ,epe.enrt_cvg_strt_dt_rl ,epe.in_pndg_wkflow_flag ,epe.dpnt_cvg_strt_dt_rl ,epe.alws_dpnt_dsgn_flag ,epe.procg_end_dt ,epe.comp_lvl_cd ,epe.pgm_id ,epe.plip_id ,epe.ptip_id ,epe.oiplip_id ,epe.cmbn_plip_id ,epe.cmbn_ptip_id ,epe.cmbn_ptip_opt_id ,epe.assignment_id ,epe.spcl_rt_pl_id ,epe.spcl_rt_oipl_id ,epe.interim_elig_per_elctbl_chc_id ,epe.prtt_enrt_rslt_id ,epe.bnft_prvdr_pool_id ,epe.yr_perd_id ,epe.cryfwd_elig_dpnt_cd ,epe.epe_attribute_category ,epe.epe_attribute1 ,epe.epe_attribute2 ,epe.epe_attribute3 ,epe.epe_attribute4 ,epe.epe_attribute5 ,epe.epe_attribute6 ,epe.epe_attribute7 ,epe.epe_attribute8 ,epe.epe_attribute9 ,epe.epe_attribute10 ,epe.epe_attribute11 ,epe.epe_attribute12 ,epe.epe_attribute13 ,epe.epe_attribute14 ,epe.epe_attribute15 ,epe.epe_attribute16 ,epe.epe_attribute17 ,epe.epe_attribute18 ,epe.epe_attribute19 ,epe.epe_attribute20 ,epe.epe_attribute21 ,epe.epe_attribute22 ,epe.epe_attribute23 ,epe.epe_attribute24 ,epe.epe_attribute25 ,epe.epe_attribute26 ,epe.epe_attribute27 ,epe.epe_attribute28 ,epe.epe_attribute29 ,epe.epe_attribute30 ,epe.last_update_date ,epe.last_updated_by ,epe.last_update_login ,epe.created_by ,epe.creation_date ,epe.request_id ,epe.program_application_id ,epe.program_id ,epe.program_update_date ,epe.object_version_number from ben_enrt_bnft enb , ben_elig_per_elctbl_chc epe , ben_per_in_ler pil , ben_ptip_f ctp , ben_pl_typ_f ptp , ben_pl_f pln , ben_oipl_f cop , ben_opt_f opt , ben_plip_f cpp , fnd_sessions se WHERE ENB.ELIG_PER_ELCTBL_CHC_ID (+) = EPE.ELIG_PER_ELCTBL_CHC_ID AND ENB.MX_WO_CTFN_FLAG(+) = 'N' /* Bug 2205237 --AND EPE.ELCTBL_FLAG = 'Y' */ AND EPE.PTIP_ID = CTP.PTIP_ID (+) AND SE.EFFECTIVE_DATE BETWEEN NVL(CTP.EFFECTIVE_START_DATE,SE.EFFECTIVE_DATE) AND NVL(CTP.EFFECTIVE_END_DATE,SE.EFFECTIVE_DATE) AND CTP.PL_TYP_ID = PTP.PL_TYP_ID (+) AND SE.EFFECTIVE_DATE BETWEEN NVL(PTP.EFFECTIVE_START_DATE,SE.EFFECTIVE_DATE) AND NVL(PTP.EFFECTIVE_END_DATE,SE.EFFECTIVE_DATE) AND EPE.PL_ID = PLN.PL_ID AND SE.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE AND PLN.EFFECTIVE_END_DATE AND EPE.PLIP_ID = CPP.PLIP_ID (+) AND SE.EFFECTIVE_DATE BETWEEN NVL(CPP.EFFECTIVE_START_DATE,SE.EFFECTIVE_DATE) AND NVL(CPP.EFFECTIVE_END_DATE,SE.EFFECTIVE_DATE) AND EPE.OIPL_ID = COP.OIPL_ID (+) AND COP.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) AND SE.EFFECTIVE_DATE BETWEEN NVL(COP.EFFECTIVE_START_DATE,SE.EFFECTIVE_DATE) AND NVL(COP.EFFECTIVE_END_DATE,SE.EFFECTIVE_DATE) AND SE.SESSION_ID = USERENV('SESSIONID') AND PIL.PER_IN_LER_ID=EPE.PER_IN_LER_ID AND PIL.BUSINESS_GROUP_ID=EPE.BUSINESS_GROUP_ID AND PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD','BCKDT')

View Text - HTML Formatted

SELECT EPE.PER_IN_LER_ID
, DECODE(OPT.NAME
, NULL
, PLN.NAME
, PLN.NAME||': '||OPT.NAME) PL_AND_OPT_NAMES
, EPE.PIL_ELCTBL_CHC_POPL_ID
, EPE.BUSINESS_GROUP_ID
, EPE.PL_ID
, EPE.OIPL_ID
, ENB.VAL
, EPE.PL_TYP_ID
, ENB.NNMNTRY_UOM
, EPE.CRNTLY_ENRD_FLAG
, EPE.ENRT_CVG_STRT_DT_CD
, EPE.ENRT_CVG_STRT_DT
, EPE.DPNT_DSGN_CD
, EPE.LER_CHG_DPNT_CVG_CD
, EPE.DPNT_CVG_STRT_DT_CD
, ENB.BNFT_TYP_CD
, ENB.CVG_MLT_CD
, ENB.ENTR_VAL_AT_ENRT_FLAG
, ENB.MN_VAL
, ENB.MX_VAL
, ENB.INCRMT_VAL
, ENB.DFLT_VAL
, EPE.ELCTBL_FLAG
, EPE.CTFN_RQD_FLAG
, EPE.MUST_ENRL_ANTHR_PL_ID
, EPE.ELIG_PER_ELCTBL_CHC_ID
, DECODE(ENB.ENRT_BNFT_ID
, NULL
, EPE.ELIG_PER_ELCTBL_CHC_ID
, NULL) FAKE_CHOICE_ID
, EPE.ERLST_DEENRT_DT
, ENB.ENRT_BNFT_ID
, EPE.DFLT_FLAG
, EPE.MNDTRY_FLAG
, EPE.AUTO_ENRT_FLAG
, EPE.ROLL_CRS_FLAG
, EPE.ELIG_FLAG
, EPE.INELIG_RSN_CD
, EPE.ELIG_OVRID_PERSON_ID
, EPE.ELIG_OVRID_DT
, EPE.MGR_OVRID_PERSON_ID
, EPE.MGR_OVRID_DT
, EPE.WS_MGR_ID
, EPE.COMMENTS
, CTP.ORDR_NUM PTIP_ORDR_NUM
, PTP.NAME PL_TYP_NAME
, CPP.ORDR_NUM PLIP_ORDR_NUM
, PLN.ORDR_NUM PL_ORDR_NUM
, PLN.NAME PL_NAME
, COP.ORDR_NUM OIPL_ORDR_NUM
, OPT.NAME OPT_NAME
, ENB.ORDR_NUM ENB_ORDR_NUM
, EPE.ENRT_CVG_STRT_DT_RL
, EPE.IN_PNDG_WKFLOW_FLAG
, EPE.DPNT_CVG_STRT_DT_RL
, EPE.ALWS_DPNT_DSGN_FLAG
, EPE.PROCG_END_DT
, EPE.COMP_LVL_CD
, EPE.PGM_ID
, EPE.PLIP_ID
, EPE.PTIP_ID
, EPE.OIPLIP_ID
, EPE.CMBN_PLIP_ID
, EPE.CMBN_PTIP_ID
, EPE.CMBN_PTIP_OPT_ID
, EPE.ASSIGNMENT_ID
, EPE.SPCL_RT_PL_ID
, EPE.SPCL_RT_OIPL_ID
, EPE.INTERIM_ELIG_PER_ELCTBL_CHC_ID
, EPE.PRTT_ENRT_RSLT_ID
, EPE.BNFT_PRVDR_POOL_ID
, EPE.YR_PERD_ID
, EPE.CRYFWD_ELIG_DPNT_CD
, EPE.EPE_ATTRIBUTE_CATEGORY
, EPE.EPE_ATTRIBUTE1
, EPE.EPE_ATTRIBUTE2
, EPE.EPE_ATTRIBUTE3
, EPE.EPE_ATTRIBUTE4
, EPE.EPE_ATTRIBUTE5
, EPE.EPE_ATTRIBUTE6
, EPE.EPE_ATTRIBUTE7
, EPE.EPE_ATTRIBUTE8
, EPE.EPE_ATTRIBUTE9
, EPE.EPE_ATTRIBUTE10
, EPE.EPE_ATTRIBUTE11
, EPE.EPE_ATTRIBUTE12
, EPE.EPE_ATTRIBUTE13
, EPE.EPE_ATTRIBUTE14
, EPE.EPE_ATTRIBUTE15
, EPE.EPE_ATTRIBUTE16
, EPE.EPE_ATTRIBUTE17
, EPE.EPE_ATTRIBUTE18
, EPE.EPE_ATTRIBUTE19
, EPE.EPE_ATTRIBUTE20
, EPE.EPE_ATTRIBUTE21
, EPE.EPE_ATTRIBUTE22
, EPE.EPE_ATTRIBUTE23
, EPE.EPE_ATTRIBUTE24
, EPE.EPE_ATTRIBUTE25
, EPE.EPE_ATTRIBUTE26
, EPE.EPE_ATTRIBUTE27
, EPE.EPE_ATTRIBUTE28
, EPE.EPE_ATTRIBUTE29
, EPE.EPE_ATTRIBUTE30
, EPE.LAST_UPDATE_DATE
, EPE.LAST_UPDATED_BY
, EPE.LAST_UPDATE_LOGIN
, EPE.CREATED_BY
, EPE.CREATION_DATE
, EPE.REQUEST_ID
, EPE.PROGRAM_APPLICATION_ID
, EPE.PROGRAM_ID
, EPE.PROGRAM_UPDATE_DATE
, EPE.OBJECT_VERSION_NUMBER
FROM BEN_ENRT_BNFT ENB
, BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_PTIP_F CTP
, BEN_PL_TYP_F PTP
, BEN_PL_F PLN
, BEN_OIPL_F COP
, BEN_OPT_F OPT
, BEN_PLIP_F CPP
, FND_SESSIONS SE
WHERE ENB.ELIG_PER_ELCTBL_CHC_ID (+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND ENB.MX_WO_CTFN_FLAG(+) = 'N' /* BUG 2205237 --AND EPE.ELCTBL_FLAG = 'Y' */
AND EPE.PTIP_ID = CTP.PTIP_ID (+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(CTP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(CTP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND CTP.PL_TYP_ID = PTP.PL_TYP_ID (+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(PTP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(PTP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND EPE.PL_ID = PLN.PL_ID
AND SE.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND EPE.PLIP_ID = CPP.PLIP_ID (+)
AND SE.EFFECTIVE_DATE BETWEEN NVL(CPP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(CPP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND EPE.OIPL_ID = COP.OIPL_ID (+)
AND COP.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)
AND SE.EFFECTIVE_DATE BETWEEN NVL(COP.EFFECTIVE_START_DATE
, SE.EFFECTIVE_DATE)
AND NVL(COP.EFFECTIVE_END_DATE
, SE.EFFECTIVE_DATE)
AND SE.SESSION_ID = USERENV('SESSIONID')
AND PIL.PER_IN_LER_ID=EPE.PER_IN_LER_ID
AND PIL.BUSINESS_GROUP_ID=EPE.BUSINESS_GROUP_ID
AND PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT')