VIEW: APPS.BEN_ENRT_USED_INFO_V

Source

View Text - Preformatted

SELECT 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.VAL BNFT_VAL , DECODE(B.ENRT_BNFT_ID , NULL , NVL(R1.DFLT_VAL , R1.VAL) , NVL(RB1.DFLT_VAL , RB1.VAL)) PRE_TAX_VAL , DECODE(B.ENRT_BNFT_ID , NULL , NVL(R2.DFLT_VAL , R2.VAL) , NVL(RB2.DFLT_VAL , RB2.VAL)) AFTR_TAX_VAL , DECODE(B.ENRT_BNFT_ID , NULL , (NVL(R1.DFLT_VAL , NVL(R1.VAL ,0)) + NVL(R2.DFLT_VAL , NVL(R2.VAL ,0))) , (NVL(RB1.DFLT_VAL , NVL(RB1.VAL , 0)) + NVL(RB2.DFLT_VAL , NVL(RB2.VAL ,0)))) NET_VAL , EPE.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 , DECODE(B.ENRT_BNFT_ID , NULL , R1.ENRT_RT_ID , RB1.ENRT_RT_ID) PRE_TAX_RT_ID , DECODE(B.ENRT_BNFT_ID , NULL , R2.ENRT_RT_ID , RB2.ENRT_RT_ID) AFTR_TAX_RT_ID , DECODE(B.ENRT_BNFT_ID , NULL , R1.DECR_BNFT_PRVDR_POOL_ID , RB1.DECR_BNFT_PRVDR_POOL_ID) PRE_TAX_RT_POOL_ID , DECODE(B.ENRT_BNFT_ID , NULL , R2.DECR_BNFT_PRVDR_POOL_ID , RB2.DECR_BNFT_PRVDR_POOL_ID) AFTR_TAX_RT_POOL_ID , B.ENRT_BNFT_ID ENRT_BNFT_ID , EPE.BUSINESS_GROUP_ID , ROWNUM MYROW FROM BEN_ELIG_PER_ELCTBL_CHC epe , ben_per_in_ler pil , BEN_ENRT_BNFT B , BEN_ENRT_RT R1 , BEN_ENRT_RT R2 , BEN_ENRT_RT RB1 , BEN_ENRT_RT RB2 WHERE B.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID AND R1.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID AND R2.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID AND R1.TX_TYP_CD(+) = 'PRETAX' AND R2.TX_TYP_CD(+) = 'AFTERTAX' AND R1.DSPLY_ON_ENRT_FLAG(+)='Y' AND R1.SPCL_RT_ENRT_RT_ID IS NULL AND R2.DSPLY_ON_ENRT_FLAG(+)='Y' AND R2.SPCL_RT_ENRT_RT_ID IS NULL AND RB1.ENRT_BNFT_ID(+) = B.ENRT_BNFT_ID AND RB2.ENRT_BNFT_ID(+) = B.ENRT_BNFT_ID AND RB1.TX_TYP_CD(+) = 'PRETAX' AND RB2.TX_TYP_CD(+) = 'AFTERTAX' AND RB1.DSPLY_ON_ENRT_FLAG(+)='Y' AND RB1.SPCL_RT_ENRT_RT_ID IS NULL AND RB2.DSPLY_ON_ENRT_FLAG(+)='Y' AND RB2.SPCL_RT_ENRT_RT_ID IS NULL AND EPE.ELCTBL_FLAG = 'Y' AND EPE.DFLT_FLAG = 'Y' 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')

View Text - HTML Formatted

SELECT 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.VAL BNFT_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, NVL(R1.DFLT_VAL
, R1.VAL)
, NVL(RB1.DFLT_VAL
, RB1.VAL)) PRE_TAX_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, NVL(R2.DFLT_VAL
, R2.VAL)
, NVL(RB2.DFLT_VAL
, RB2.VAL)) AFTR_TAX_VAL
, DECODE(B.ENRT_BNFT_ID
, NULL
, (NVL(R1.DFLT_VAL
, NVL(R1.VAL
, 0)) + NVL(R2.DFLT_VAL
, NVL(R2.VAL
, 0)))
, (NVL(RB1.DFLT_VAL
, NVL(RB1.VAL
, 0)) + NVL(RB2.DFLT_VAL
, NVL(RB2.VAL
, 0)))) NET_VAL
, EPE.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
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.ENRT_RT_ID
, RB1.ENRT_RT_ID) PRE_TAX_RT_ID
, DECODE(B.ENRT_BNFT_ID
, NULL
, R2.ENRT_RT_ID
, RB2.ENRT_RT_ID) AFTR_TAX_RT_ID
, DECODE(B.ENRT_BNFT_ID
, NULL
, R1.DECR_BNFT_PRVDR_POOL_ID
, RB1.DECR_BNFT_PRVDR_POOL_ID) PRE_TAX_RT_POOL_ID
, DECODE(B.ENRT_BNFT_ID
, NULL
, R2.DECR_BNFT_PRVDR_POOL_ID
, RB2.DECR_BNFT_PRVDR_POOL_ID) AFTR_TAX_RT_POOL_ID
, B.ENRT_BNFT_ID ENRT_BNFT_ID
, EPE.BUSINESS_GROUP_ID
, ROWNUM MYROW
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_ENRT_BNFT B
, BEN_ENRT_RT R1
, BEN_ENRT_RT R2
, BEN_ENRT_RT RB1
, BEN_ENRT_RT RB2
WHERE B.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND R1.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND R2.ELIG_PER_ELCTBL_CHC_ID(+) = EPE.ELIG_PER_ELCTBL_CHC_ID
AND R1.TX_TYP_CD(+) = 'PRETAX'
AND R2.TX_TYP_CD(+) = 'AFTERTAX'
AND R1.DSPLY_ON_ENRT_FLAG(+)='Y'
AND R1.SPCL_RT_ENRT_RT_ID IS NULL
AND R2.DSPLY_ON_ENRT_FLAG(+)='Y'
AND R2.SPCL_RT_ENRT_RT_ID IS NULL
AND RB1.ENRT_BNFT_ID(+) = B.ENRT_BNFT_ID
AND RB2.ENRT_BNFT_ID(+) = B.ENRT_BNFT_ID
AND RB1.TX_TYP_CD(+) = 'PRETAX'
AND RB2.TX_TYP_CD(+) = 'AFTERTAX'
AND RB1.DSPLY_ON_ENRT_FLAG(+)='Y'
AND RB1.SPCL_RT_ENRT_RT_ID IS NULL
AND RB2.DSPLY_ON_ENRT_FLAG(+)='Y'
AND RB2.SPCL_RT_ENRT_RT_ID IS NULL
AND EPE.ELCTBL_FLAG = 'Y'
AND EPE.DFLT_FLAG = 'Y'
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')