View: BEN_RSLT_CHC_ENRT_RT_RT_VAL_V

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

View Text

SELECT PEN.OIPL_ORDR_NUM
, EPE.ELIG_PER_ELCTBL_CHC_ID
, PEN.PRTT_ENRT_RSLT_ID
, PEN.PL_ID
, PEN.OIPL_ID
, EPE.CRNTLY_ENRD_FLAG
, EPE.MNDTRY_FLAG
, PEN.SSPNDD_FLAG
, PEN.PER_IN_LER_ID
, 'Y' DFLT_FLAG /* EPE.DFLT_FLAG */
, 'Y' RESULT_EXISTS_FLAG
, PEN.BUSINESS_GROUP_ID
, TO_CHAR(PEN.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
, TO_CHAR(PEN.EFFECTIVE_START_DATE
, 'DD-MON-YYYY') EFFECTIVE_START_DATE
, TO_CHAR(PEN.EFFECTIVE_END_DATE
, 'DD-MON-YYYY') EFFECTIVE_END_DATE
, PRV.PRTT_RT_VAL_ID
, ECR.ENRT_RT_ID
, ECR.MN_ELCN_VAL
, ECR.MX_ELCN_VAL
, ECR.INCRMT_ELCN_VAL
, PRV.RT_VAL
, PRV.TX_TYP_CD
, PRV.RT_TYP_CD
, PRV.ACTY_TYP_CD
, NVL(ECR.DSPLY_ON_ENRT_FLAG
, 'Y' ) DSPLY_ON_ENRT_FLAG
, NVL(ECR.ENTR_VAL_AT_ENRT_FLAG
, 'N') ENTR_VAL_AT_ENRT_FLAG
, 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
, COP.OPT_ID
, EPE.CTFN_RQD_FLAG
, PEN.ENRT_CVG_STRT_DT 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
, DECODE(PRV.PRTT_RT_VAL_ID
, NULL
, NVL(ECR.CMCD_DFLT_VAL
, ECR.CMCD_VAL)
, PRV.CMCD_RT_VAL) CMCD_VAL
, DECODE(PRV.PRTT_RT_VAL_ID
, NULL
, ECR.CMCD_ACTY_REF_PERD_CD
, PRV.CMCD_REF_PERD_CD) CMCD_REF_PERD
, ECR.CMCD_MN_ELCN_VAL
, ECR.CMCD_MX_ELCN_VAL
, PRV.ANN_RT_VAL ANN_VAL
, ECR.ANN_MN_ELCN_VAL
, ECR.ANN_MX_ELCN_VAL
, ECR.NNMNTRY_UOM RT_NNMNTRY_UOM
, DECODE(PRV.PRTT_RT_VAL_ID
, NULL
, ECR.RT_STRT_DT
, PRV.RT_STRT_DT) RT_STRT_DT
, PRV.RT_END_DT
, PRV.RT_OVRIDN_FLAG
, PRV.RT_OVRIDN_THRU_DT
, DECODE(PEN.RPLCS_SSPNDD_RSLT_ID
, NULL
, 'N'
, 'Y') INTERIM_FLAG
, EPE.AUTO_ENRT_FLAG
, EPE.MUST_ENRL_ANTHR_PL_ID
, PEN.ENRT_OVRID_RSN_CD
, PEN.ENRT_OVRID_THRU_DT
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PRTT_RT_VAL PRV
, BEN_ENRT_RT ECR
, BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_OIPL_F COP
, FND_SESSIONS FND
WHERE FND.SESSION_ID = USERENV ('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND PEN.ENRT_CVG_THRU_DT = TO_DATE ('31-12-4712'
, 'DD-MM-YYYY')
AND PEN.PRTT_ENRT_RSLT_ID = EPE.PRTT_ENRT_RSLT_ID
AND PEN.PER_IN_LER_ID = EPE.PER_IN_LER_ID
AND EPE.ELCTBL_FLAG = 'Y'
AND EPE.ELIG_PER_ELCTBL_CHC_ID = ECR.ELIG_PER_ELCTBL_CHC_ID(+)
AND PEN.PRTT_ENRT_RSLT_ID = PRV.PRTT_ENRT_RSLT_ID(+)
AND /* PEN.BUSINESS_GROUP_ID = PRV.BUSINESS_GROUP_ID
AND */ PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND /* PRV.PRTT_RT_VAL_ID = ECR.PRTT_RT_VAL_ID
AND */ PRV.PRTT_RT_VAL_STAT_CD IS NULL
AND ECR.DSPLY_ON_ENRT_FLAG(+) = 'Y'
AND PEN.OIPL_ID = COP.OIPL_ID
AND PEN.BUSINESS_GROUP_ID = COP.BUSINESS_GROUP_ID
AND COP.OIPL_STAT_CD = 'A'
AND FND.EFFECTIVE_DATE BETWEEN COP.EFFECTIVE_START_DATE
AND COP.EFFECTIVE_END_DATE
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') UNION SELECT EPE.OIPL_ORDR_NUM
, EPE.ELIG_PER_ELCTBL_CHC_ID
, EPE.PRTT_ENRT_RSLT_ID
, EPE.PL_ID
, EPE.OIPL_ID
, EPE.CRNTLY_ENRD_FLAG
, EPE.MNDTRY_FLAG
, 'N' SSPNDD_FLAG
, EPE.PER_IN_LER_ID
, EPE.DFLT_FLAG
, 'N' RESULT_EXISTS_FLAG
, EPE.BUSINESS_GROUP_ID
, NULL OBJECT_VERSION_NUMBER
, NULL EFFECTIVE_START_DATE
, NULL EFFECTIVE_END_DATE
, ECR.PRTT_RT_VAL_ID
, ECR.ENRT_RT_ID
, ECR.MN_ELCN_VAL
, ECR.MX_ELCN_VAL
, ECR.INCRMT_ELCN_VAL
, NVL (ECR.DFLT_VAL
, ECR.VAL) RT_VAL
, ECR.TX_TYP_CD
, NULL RT_TYP_CD /*ECR.RT_TYP_CD */
, ECR.ACTY_TYP_CD
, ECR.DSPLY_ON_ENRT_FLAG
, ECR.ENTR_VAL_AT_ENRT_FLAG
, NULL PEN_ATTRIBUTE_CATEGORY
, NULL PEN_ATTRIBUTE1
, NULL PEN_ATTRIBUTE2
, NULL PEN_ATTRIBUTE3
, NULL PEN_ATTRIBUTE4
, NULL PEN_ATTRIBUTE5
, NULL PEN_ATTRIBUTE6
, NULL PEN_ATTRIBUTE7
, NULL PEN_ATTRIBUTE8
, NULL PEN_ATTRIBUTE9
, NULL PEN_ATTRIBUTE10
, NULL PEN_ATTRIBUTE11
, NULL PEN_ATTRIBUTE12
, NULL PEN_ATTRIBUTE13
, NULL PEN_ATTRIBUTE14
, NULL PEN_ATTRIBUTE15
, NULL PEN_ATTRIBUTE16
, NULL PEN_ATTRIBUTE17
, NULL PEN_ATTRIBUTE18
, NULL PEN_ATTRIBUTE19
, NULL PEN_ATTRIBUTE20
, NULL PEN_ATTRIBUTE21
, NULL PEN_ATTRIBUTE22
, NULL PEN_ATTRIBUTE23
, NULL PEN_ATTRIBUTE24
, NULL PEN_ATTRIBUTE25
, NULL PEN_ATTRIBUTE26
, NULL PEN_ATTRIBUTE27
, NULL PEN_ATTRIBUTE28
, NULL PEN_ATTRIBUTE29
, NULL PEN_ATTRIBUTE30
, COP.OPT_ID
, EPE.CTFN_RQD_FLAG
, EPE.ENRT_CVG_STRT_DT ENRT_CVG_STRT_DT
, TO_DATE (NULL) ENRT_CVG_THRU_DT
, EPE.ENRT_CVG_STRT_DT_CD
, EPE.DPNT_DSGN_CD
, TO_DATE (NULL) ORGNL_ENRT_DT
, NVL (ECR.CMCD_DFLT_VAL
, ECR.CMCD_VAL) CMCD_VAL
, ECR.CMCD_ACTY_REF_PERD_CD CMCD_REF_PERD
, ECR.CMCD_MN_ELCN_VAL
, ECR.CMCD_MX_ELCN_VAL
, NVL (ECR.ANN_DFLT_VAL
, ECR.ANN_VAL) ANN_VAL
, ECR.ANN_MN_ELCN_VAL
, ECR.ANN_MX_ELCN_VAL
, ECR.NNMNTRY_UOM RT_NNMNTRY_UOM
, ECR.RT_STRT_DT
, TO_DATE (NULL) RT_END_DT
, 'N' RT_OVRIDN_FLAG
, TO_DATE (NULL) RT_OVRIDN_THRU_DT
, 'N' INTERIM_FLAG
, EPE.AUTO_ENRT_FLAG
, EPE.MUST_ENRL_ANTHR_PL_ID
, NULL ENRT_OVRID_RSN_CD
, TO_DATE (NULL) ENRT_OVRID_THRU_DT
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PIL_ELCTBL_CHC_POPL PEL
, BEN_PER_IN_LER PIL
, BEN_ENRT_RT ECR
, BEN_OIPL_F COP
, FND_SESSIONS FND
WHERE FND.SESSION_ID = USERENV ('SESSIONID')
AND EPE.ELCTBL_FLAG = 'Y'
AND EPE.PIL_ELCTBL_CHC_POPL_ID = PEL.PIL_ELCTBL_CHC_POPL_ID
AND PEL.ELCNS_MADE_DT IS NULL
AND PEL.DFLT_ASND_DT IS NULL
AND EPE.ELIG_PER_ELCTBL_CHC_ID = ECR.ELIG_PER_ELCTBL_CHC_ID(+)
AND ECR.DSPLY_ON_ENRT_FLAG(+) = 'Y'
AND EPE.OIPL_ID = COP.OIPL_ID
AND EPE.BUSINESS_GROUP_ID = COP.BUSINESS_GROUP_ID
AND COP.OIPL_STAT_CD = 'A'
AND FND.EFFECTIVE_DATE BETWEEN COP.EFFECTIVE_START_DATE
AND COP.EFFECTIVE_END_DATE
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')

Columns