View: BEN_ENRT_RSLT_CHC_MISC_V

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

View Text

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
, TO_CHAR(E.EFFECTIVE_START_DATE
, 'DD-MON-YYYY') EFFECTIVE_START_DATE
, TO_CHAR(E.EFFECTIVE_END_DATE
, 'DD-MON-YYYY') 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 = 'STRTD'
AND LER.LER_ID = PIL.LER_ID
AND LER.TYP_CD <> 'COMP'
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 NVL(C.PRTT_ENRT_RSLT_ID
, -1) = NVL(E.PRTT_ENRT_RSLT_ID(+)
, -1)
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 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' ) ) ) )

Columns