View: BEN_ENRT_RSLT_ELIG_CHC_V

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

View Text

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

Columns