View: BEN_ENRT_RSLTS_INFO_V

Product:	BEN - Advanced Benefits
Description:	- Retrofitted
Implementation/DBA Data:	APPS.BEN_ENRT_RSLTS_INFO_V

View Text

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
, TO_CHAR(E.EFFECTIVE_START_DATE
, 'DD-MON-YYYY') EFFECTIVE_START_DATE
, TO_CHAR(E.EFFECTIVE_END_DATE
, 'DD-MON-YYYY') 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 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
, NULL EFFECTIVE_START_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 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

Columns