VIEW: APPS.BEN_FLX_ENRT_RT_INFO_LE_V

Source

View Text - Preformatted

SELECT DISTINCT ECR.ENRT_RT_ID , ECR.VAL standard_val , ECR.SPCL_RT_ENRT_RT_ID , ECR2.VAL special_val , PTP.NAME related_pl_typ_name , PLN.NAME related_pl_name , PTP2.NAME selected_pl_typ_name , PLN2.NAME selected_pl_name , EPE.PGM_ID , EPE.PL_ID related_pl_id , EPE.SPCL_RT_PL_ID selected_pl_id , EPE.PER_IN_LER_ID , EPE.PL_TYP_ID , EPE.OIPL_ID related_oipl_id , EPE.SPCL_RT_OIPL_ID selected_oipl_id , OPT.NAME related_oipl_name , OPT2.NAME selected_oipl_name , EPE.BUSINESS_GROUP_ID , decode(epe.pgm_id , null , EPE.SPCL_RT_PL_ID , null) join_pl_id FROM BEN_ELIG_PER_ELCTBL_CHC EPE , ben_per_in_ler pil , BEN_ENRT_RT ECR , BEN_ENRT_RT ECR2 , BEN_PL_F PLN , BEN_PL_F PLN2 , BEN_PL_TYP_F PTP , BEN_PL_TYP_F PTP2 , BEN_OIPL_F O , BEN_OPT_F OPT , BEN_OIPL_F O2 , BEN_OPT_F OPT2 WHERE PIL.LF_EVT_OCRD_DT BETWEEN PLN.EFFECTIVE_START_DATE AND PLN.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN PLN2.EFFECTIVE_START_DATE AND PLN2.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN PTP.EFFECTIVE_START_DATE AND PTP.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN PTP2.EFFECTIVE_START_DATE AND PTP2.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN O.EFFECTIVE_START_DATE AND O.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN OPT.EFFECTIVE_START_DATE AND OPT.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN O2.EFFECTIVE_START_DATE AND O2.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN OPT2.EFFECTIVE_START_DATE AND OPT2.EFFECTIVE_END_DATE AND PTP.PL_TYP_ID = EPE.PL_TYP_ID AND PTP2.PL_TYP_ID = PLN2.PL_TYP_ID AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID AND ECR.ENRT_RT_ID = ECR2.SPCL_RT_ENRT_RT_ID AND EPE.PL_ID = PLN.PL_ID AND EPE.OIPL_ID = O.OIPL_ID(+) AND O.OPT_ID = OPT.OPT_ID(+) AND EPE.SPCL_RT_PL_ID = PLN2.PL_ID AND EPE.SPCL_RT_OIPL_ID = O2.OIPL_ID(+) AND OPT2.OPT_ID(+) = O2.OPT_ID AND PLN.PL_STAT_CD = 'A' AND PLN2.PL_STAT_CD = 'A' AND PTP.PL_TYP_STAT_CD = 'A' AND PTP2.PL_TYP_STAT_CD = 'A' AND O.OIPL_STAT_CD(+) = 'A' AND O2.OIPL_STAT_CD(+) = 'A' AND (EPE.SPCL_RT_PL_ID IS NOT NULL OR EPE.SPCL_RT_OIPL_ID IS NOT NULL) and pil.per_in_ler_id=epe.per_in_ler_id and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')

View Text - HTML Formatted

SELECT DISTINCT ECR.ENRT_RT_ID
, ECR.VAL STANDARD_VAL
, ECR.SPCL_RT_ENRT_RT_ID
, ECR2.VAL SPECIAL_VAL
, PTP.NAME RELATED_PL_TYP_NAME
, PLN.NAME RELATED_PL_NAME
, PTP2.NAME SELECTED_PL_TYP_NAME
, PLN2.NAME SELECTED_PL_NAME
, EPE.PGM_ID
, EPE.PL_ID RELATED_PL_ID
, EPE.SPCL_RT_PL_ID SELECTED_PL_ID
, EPE.PER_IN_LER_ID
, EPE.PL_TYP_ID
, EPE.OIPL_ID RELATED_OIPL_ID
, EPE.SPCL_RT_OIPL_ID SELECTED_OIPL_ID
, OPT.NAME RELATED_OIPL_NAME
, OPT2.NAME SELECTED_OIPL_NAME
, EPE.BUSINESS_GROUP_ID
, DECODE(EPE.PGM_ID
, NULL
, EPE.SPCL_RT_PL_ID
, NULL) JOIN_PL_ID
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_PER_IN_LER PIL
, BEN_ENRT_RT ECR
, BEN_ENRT_RT ECR2
, BEN_PL_F PLN
, BEN_PL_F PLN2
, BEN_PL_TYP_F PTP
, BEN_PL_TYP_F PTP2
, BEN_OIPL_F O
, BEN_OPT_F OPT
, BEN_OIPL_F O2
, BEN_OPT_F OPT2
WHERE PIL.LF_EVT_OCRD_DT BETWEEN PLN.EFFECTIVE_START_DATE
AND PLN.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN PLN2.EFFECTIVE_START_DATE
AND PLN2.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN PTP.EFFECTIVE_START_DATE
AND PTP.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN PTP2.EFFECTIVE_START_DATE
AND PTP2.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN O.EFFECTIVE_START_DATE
AND O.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN OPT.EFFECTIVE_START_DATE
AND OPT.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN O2.EFFECTIVE_START_DATE
AND O2.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN OPT2.EFFECTIVE_START_DATE
AND OPT2.EFFECTIVE_END_DATE
AND PTP.PL_TYP_ID = EPE.PL_TYP_ID
AND PTP2.PL_TYP_ID = PLN2.PL_TYP_ID
AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID
AND ECR.ENRT_RT_ID = ECR2.SPCL_RT_ENRT_RT_ID
AND EPE.PL_ID = PLN.PL_ID
AND EPE.OIPL_ID = O.OIPL_ID(+)
AND O.OPT_ID = OPT.OPT_ID(+)
AND EPE.SPCL_RT_PL_ID = PLN2.PL_ID
AND EPE.SPCL_RT_OIPL_ID = O2.OIPL_ID(+)
AND OPT2.OPT_ID(+) = O2.OPT_ID
AND PLN.PL_STAT_CD = 'A'
AND PLN2.PL_STAT_CD = 'A'
AND PTP.PL_TYP_STAT_CD = 'A'
AND PTP2.PL_TYP_STAT_CD = 'A'
AND O.OIPL_STAT_CD(+) = 'A'
AND O2.OIPL_STAT_CD(+) = 'A'
AND (EPE.SPCL_RT_PL_ID IS NOT NULL OR EPE.SPCL_RT_OIPL_ID IS NOT NULL)
AND PIL.PER_IN_LER_ID=EPE.PER_IN_LER_ID
AND PIL.PER_IN_LER_STAT_CD NOT IN ('VOIDD'
, 'BCKDT')