VIEW: APPS.BEN_RSLT_CHC_ENRT_RT_RT_VAL_V

Source

View Text - Preformatted

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 , PEN.EFFECTIVE_START_DATE effective_start_date , PEN.EFFECTIVE_END_DATE 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.prtt_enrt_rslt_stat_cd IS NULL AND ( PRV.PRTT_RT_VAL_ID is null or PRV.PRTT_RT_VAL_ID = ECR.PRTT_RT_VAL_ID ) /* PRV.PRTT_RT_VAL_ID = ECR.PRTT_RT_VAL_ID AND */ 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, to_date(NULL) effective_start_date, to_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')

View Text - HTML Formatted

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
, PEN.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE 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.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND ( PRV.PRTT_RT_VAL_ID IS NULL OR PRV.PRTT_RT_VAL_ID = ECR.PRTT_RT_VAL_ID ) /* PRV.PRTT_RT_VAL_ID = ECR.PRTT_RT_VAL_ID
AND */
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
, TO_DATE(NULL) EFFECTIVE_START_DATE
, TO_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')