View: BEN_ENRT_USED_INFO_V

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

View Text

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')

Columns