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_RSLT_CHC_MISC_V

Source

View Text - Preformatted


SELECT NVL (e.pl_ordr_num, c.pl_ordr_num) pl_ordr_num, NVL (e.oipl_ordr_num, c.oipl_ordr_num) opil_ordr_num, c.pl_id pl_id, c.pl_typ_id pl_typ_id, o.opt_id opt_id, c.oipl_id oipl_id, NVL (e.bnft_amt, NVL (b.dflt_val, b.val)) bnft_val, b.nnmntry_uom bnft_nnmntry_uom, c.elig_per_elctbl_chc_id elig_per_elctbl_chc_id, c.prtt_enrt_rslt_id prtt_enrt_rslt_id, c.object_version_number object_version_number, e.effective_start_date effective_start_date, e.effective_end_date effective_end_date, c.per_in_ler_id per_in_ler_id, pil.lf_evt_ocrd_dt, c.crntly_enrd_flag crntly_enrd_flag, c.business_group_id business_group_id, c.mndtry_flag mndtry_flag, NVL (e.sspndd_flag, 'N') sspndd_flag, DECODE (c.dflt_flag, 'N', 'N', 'Y', DECODE (b.enrt_bnft_id, NULL, 'Y', b.dflt_flag) ) dflt_flag, b.enrt_bnft_id enrt_bnft_id, TO_CHAR (e.object_version_number) rslt_ovn, c.erlst_deenrt_dt erlst_deenrt_dt, pil.person_id person_id, pil.ler_id, NVL (e.enrt_cvg_strt_dt, c.enrt_cvg_strt_dt) enrt_cvg_strt_dt, e.enrt_cvg_thru_dt enrt_cvg_thru_dt, c.enrt_cvg_strt_dt_cd, c.dpnt_dsgn_cd, 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, c.auto_enrt_flag, c.must_enrl_anthr_pl_id, b.bnft_typ_cd, b.cvg_mlt_cd, b.entr_val_at_enrt_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, DECODE (b.enrt_bnft_id, NULL, c.ctfn_rqd_flag, b.ctfn_rqd_flag ) ctfn_rqd_flag, pel.uom uom, pel.acty_ref_perd_cd acty_ref_perd_cd, pel.enrt_perd_id, pel.lee_rsn_id, pel.cls_enrt_dt_to_use_cd, DECODE (e.prtt_enrt_rslt_id, NULL, 'Y', 'N'), NVL (e.plip_ordr_num, c.plip_ordr_num) plip_ordr_num, NVL (e.ptip_ordr_num, c.ptip_ordr_num) ptip_ordr_num, c.in_pndg_wkflow_flag FROM ben_enrt_bnft b, ben_oipl_f o, ben_elig_per_elctbl_chc c, ben_pl_f pln, ben_pil_elctbl_chc_popl pel, ben_prtt_enrt_rslt_f e, fnd_sessions f, ben_ler_f ler, ben_per_in_ler pil WHERE pil.per_in_ler_stat_cd NOT IN ('VOIDD','BCKDT') AND ler.ler_id = pil.ler_id AND ler.typ_cd not in ( 'COMP','IREC','GSP','ABS') AND f.effective_date BETWEEN ler.effective_start_date AND ler.effective_end_date AND f.session_id = USERENV ('sessionid') /* AND e.enrt_cvg_thru_dt(+) = TO_DATE ('12/31/4712', 'MM/DD/rrrr') AND e.prtt_enrt_rslt_stat_cd IS NULL AND f.effective_date BETWEEN NVL (e.effective_start_date, f.effective_date ) AND NVL (e.effective_end_date, f.effective_date) */ AND pil.per_in_ler_id = pel.per_in_ler_id AND pel.pil_elctbl_chc_popl_id = c.pil_elctbl_chc_popl_id AND pel.per_in_ler_id = c.per_in_ler_id AND f.effective_date BETWEEN NVL (pel.enrt_perd_strt_dt, f.effective_date) AND NVL (pel.procg_end_dt, NVL (pel.enrt_perd_end_dt, f.effective_date ) ) AND pel.pl_id = pln.pl_id AND pln.pl_stat_cd = 'A' AND pln.svgs_pl_flag = 'N' AND c.comp_lvl_cd NOT IN ('PLANFC', 'PLANIMP') AND c.oipl_id = o.oipl_id(+) AND f.effective_date BETWEEN NVL (o.effective_start_date, f.effective_date) AND NVL (o.effective_end_date, f.effective_date) AND o.oipl_stat_cd(+) = 'A' AND pel.pl_id = c.pl_id AND pel.pl_id NOT IN ( SELECT plip.pl_id FROM ben_plip_f plip WHERE plip.pl_id = pln.pl_id AND plip.plip_stat_cd = 'A' AND f.effective_date BETWEEN plip.effective_start_date AND plip.effective_end_date) AND f.effective_date BETWEEN pln.effective_start_date AND pln.effective_end_date AND c.prtt_enrt_rslt_id = e.prtt_enrt_rslt_id(+) AND DECODE (c.prtt_enrt_rslt_id, NULL, 'Y', c.dflt_flag) = c.dflt_flag AND c.elig_per_elctbl_chc_id = b.elig_per_elctbl_chc_id(+) AND ( ( c.prtt_enrt_rslt_id IS NOT NULL AND EXISTS ( SELECT NULL FROM ben_prtt_enrt_rslt_f pen WHERE pen.prtt_enrt_rslt_id = c.prtt_enrt_rslt_id AND pen.enrt_cvg_thru_dt = TO_DATE ('12/31/4712', 'MM/DD/rrrr') AND pen.effective_start_date = e.effective_start_date AND pen.prtt_enrt_rslt_stat_cd IS NULL AND f.effective_date BETWEEN pen.effective_start_date AND pen.effective_end_date) ) OR ( c.prtt_enrt_rslt_id IS NULL AND ( pel.elcns_made_dt IS NULL OR ( pel.elcns_made_dt < f.effective_date AND ler.typ_cd = 'SCHEDDU' ) ) ) )

View Text - HTML Formatted

SELECT NVL (E.PL_ORDR_NUM
, C.PL_ORDR_NUM) PL_ORDR_NUM
, NVL (E.OIPL_ORDR_NUM
, C.OIPL_ORDR_NUM) OPIL_ORDR_NUM
, C.PL_ID PL_ID
, C.PL_TYP_ID PL_TYP_ID
, O.OPT_ID OPT_ID
, C.OIPL_ID OIPL_ID
, NVL (E.BNFT_AMT
, NVL (B.DFLT_VAL
, B.VAL)) BNFT_VAL
, B.NNMNTRY_UOM BNFT_NNMNTRY_UOM
, C.ELIG_PER_ELCTBL_CHC_ID ELIG_PER_ELCTBL_CHC_ID
, C.PRTT_ENRT_RSLT_ID PRTT_ENRT_RSLT_ID
, C.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, E.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, E.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, C.PER_IN_LER_ID PER_IN_LER_ID
, PIL.LF_EVT_OCRD_DT
, C.CRNTLY_ENRD_FLAG CRNTLY_ENRD_FLAG
, C.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, C.MNDTRY_FLAG MNDTRY_FLAG
, NVL (E.SSPNDD_FLAG
, 'N') SSPNDD_FLAG
, DECODE (C.DFLT_FLAG
, 'N'
, 'N'
, 'Y'
, DECODE (B.ENRT_BNFT_ID
, NULL
, 'Y'
, B.DFLT_FLAG) ) DFLT_FLAG
, B.ENRT_BNFT_ID ENRT_BNFT_ID
, TO_CHAR (E.OBJECT_VERSION_NUMBER) RSLT_OVN
, C.ERLST_DEENRT_DT ERLST_DEENRT_DT
, PIL.PERSON_ID PERSON_ID
, PIL.LER_ID
, NVL (E.ENRT_CVG_STRT_DT
, C.ENRT_CVG_STRT_DT) ENRT_CVG_STRT_DT
, E.ENRT_CVG_THRU_DT ENRT_CVG_THRU_DT
, C.ENRT_CVG_STRT_DT_CD
, C.DPNT_DSGN_CD
, 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
, C.AUTO_ENRT_FLAG
, C.MUST_ENRL_ANTHR_PL_ID
, B.BNFT_TYP_CD
, B.CVG_MLT_CD
, B.ENTR_VAL_AT_ENRT_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
, DECODE (B.ENRT_BNFT_ID
, NULL
, C.CTFN_RQD_FLAG
, B.CTFN_RQD_FLAG ) CTFN_RQD_FLAG
, PEL.UOM UOM
, PEL.ACTY_REF_PERD_CD ACTY_REF_PERD_CD
, PEL.ENRT_PERD_ID
, PEL.LEE_RSN_ID
, PEL.CLS_ENRT_DT_TO_USE_CD
, DECODE (E.PRTT_ENRT_RSLT_ID
, NULL
, 'Y'
, 'N')
, NVL (E.PLIP_ORDR_NUM
, C.PLIP_ORDR_NUM) PLIP_ORDR_NUM
, NVL (E.PTIP_ORDR_NUM
, C.PTIP_ORDR_NUM) PTIP_ORDR_NUM
, C.IN_PNDG_WKFLOW_FLAG
FROM BEN_ENRT_BNFT B
, BEN_OIPL_F O
, BEN_ELIG_PER_ELCTBL_CHC C
, BEN_PL_F PLN
, BEN_PIL_ELCTBL_CHC_POPL PEL
, BEN_PRTT_ENRT_RSLT_F E
, FND_SESSIONS F
, BEN_LER_F LER
, BEN_PER_IN_LER PIL
WHERE PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT')
AND LER.LER_ID = PIL.LER_ID
AND LER.TYP_CD NOT IN ( 'COMP'
, 'IREC'
, 'GSP'
, 'ABS')
AND F.EFFECTIVE_DATE BETWEEN LER.EFFECTIVE_START_DATE
AND LER.EFFECTIVE_END_DATE
AND F.SESSION_ID = USERENV ('SESSIONID') /*
AND E.ENRT_CVG_THRU_DT(+) = TO_DATE ('12/31/4712'
, 'MM/DD/RRRR')
AND E.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND F.EFFECTIVE_DATE BETWEEN NVL (E.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE )
AND NVL (E.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE) */
AND PIL.PER_IN_LER_ID = PEL.PER_IN_LER_ID
AND PEL.PIL_ELCTBL_CHC_POPL_ID = C.PIL_ELCTBL_CHC_POPL_ID
AND PEL.PER_IN_LER_ID = C.PER_IN_LER_ID
AND F.EFFECTIVE_DATE BETWEEN NVL (PEL.ENRT_PERD_STRT_DT
, F.EFFECTIVE_DATE)
AND NVL (PEL.PROCG_END_DT
, NVL (PEL.ENRT_PERD_END_DT
, F.EFFECTIVE_DATE ) )
AND PEL.PL_ID = PLN.PL_ID
AND PLN.PL_STAT_CD = 'A'
AND PLN.SVGS_PL_FLAG = 'N'
AND C.COMP_LVL_CD NOT IN ('PLANFC'
, 'PLANIMP')
AND C.OIPL_ID = O.OIPL_ID(+)
AND F.EFFECTIVE_DATE BETWEEN NVL (O.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL (O.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND O.OIPL_STAT_CD(+) = 'A'
AND PEL.PL_ID = C.PL_ID
AND PEL.PL_ID NOT IN ( SELECT PLIP.PL_ID
FROM BEN_PLIP_F PLIP
WHERE PLIP.PL_ID = PLN.PL_ID
AND PLIP.PLIP_STAT_CD = 'A'
AND F.EFFECTIVE_DATE BETWEEN PLIP.EFFECTIVE_START_DATE
AND PLIP.EFFECTIVE_END_DATE)
AND F.EFFECTIVE_DATE BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND C.PRTT_ENRT_RSLT_ID = E.PRTT_ENRT_RSLT_ID(+)
AND DECODE (C.PRTT_ENRT_RSLT_ID
, NULL
, 'Y'
, C.DFLT_FLAG) = C.DFLT_FLAG
AND C.ELIG_PER_ELCTBL_CHC_ID = B.ELIG_PER_ELCTBL_CHC_ID(+)
AND ( ( C.PRTT_ENRT_RSLT_ID IS NOT NULL
AND EXISTS ( SELECT NULL
FROM BEN_PRTT_ENRT_RSLT_F PEN
WHERE PEN.PRTT_ENRT_RSLT_ID = C.PRTT_ENRT_RSLT_ID
AND PEN.ENRT_CVG_THRU_DT = TO_DATE ('12/31/4712'
, 'MM/DD/RRRR')
AND PEN.EFFECTIVE_START_DATE = E.EFFECTIVE_START_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND F.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE) ) OR ( C.PRTT_ENRT_RSLT_ID IS NULL
AND ( PEL.ELCNS_MADE_DT IS NULL OR ( PEL.ELCNS_MADE_DT < F.EFFECTIVE_DATE
AND LER.TYP_CD = 'SCHEDDU' ) ) ) )