VIEW: APPS.BEN_FLX_ENRT_POOL_LE_V

Source

View Text - Preformatted

SELECT DISTINCT ECR.VAL ,PTP.NAME PL_TYP_NAME , PL.NAME PL_NAME , OPT.NAME OPT_NAME , PL.NAME||' '||OPT.NAME PL_OPT_NAME , NULL cmbn_name , POOL.NAME POOL_NAME , EPE.PGM_ID , EPE.CMBN_PLIP_ID , EPE.CMBN_PTIP_ID , EPE.CMBN_PTIP_OPT_ID , EPE.PL_ID , EPE.PL_TYP_ID , EPE.PTIP_ID , EPE.PLIP_ID , EPE.OIPL_ID , EPE.PER_IN_LER_ID , PIL.PERSON_ID , PIL.LER_ID , EPE.BUSINESS_GROUP_ID, POOL.BNFT_PRVDR_POOL_ID FROM BEN_ELIG_PER_ELCTBL_CHC EPE , BEN_ENRT_RT ECR , BEN_PL_TYP_F PTP , BEN_PL_F PL , BEN_OIPL_F OIPL , BEN_OPT_F OPT , BEN_CMBN_PLIP_F CMBN_PLIP , BEN_CMBN_PTIP_F CMBN_PTIP , BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT , BEN_PER_IN_LER PIL , BEN_BNFT_PRVDR_POOL_F POOL WHERE PIL.LF_EVT_OCRD_DT BETWEEN nvl(PTP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND nvl( PTP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN POOL.EFFECTIVE_START_DATE AND POOL.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(PL.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(PL.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OIPL.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(OIPL.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(OPT.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP_OPT.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PTIP_OPT.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PLIP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PLIP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PTIP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND EPE.PER_IN_LER_ID = PIL.PER_IN_LER_ID AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID AND ECR.RT_USG_CD = 'FLXCR' AND EPE.PL_TYP_ID = PTP.PL_TYP_ID(+) AND EPE.BNFT_PRVDR_POOL_ID = POOL.BNFT_PRVDR_POOL_ID AND EPE.PL_ID = PL.PL_ID(+) AND EPE.OIPL_ID = OIPL.OIPL_ID(+) AND OIPL.OPT_ID = OPT.OPT_ID(+) AND EPE.CMBN_PLIP_ID = CMBN_PLIP.CMBN_PLIP_ID(+) AND EPE.CMBN_PTIP_ID = CMBN_PTIP.CMBN_PTIP_ID(+) AND EPE.CMBN_PTIP_OPT_ID = CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID(+) AND EPE.CMBN_PTIP_ID IS NULL AND EPE.CMBN_PLIP_ID IS NULL AND EPE.CMBN_PTIP_OPT_ID IS NULL 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 DISTINCT ECR.VAL ,'Multiple' PL_TYP_NAME , Null PL_NAME , Null OPT_NAME , Null PL_OPT_NAME , CMBN_PTIP.NAME CMBN_NAME , POOL.NAME POOL_NAME , EPE.PGM_ID , EPE.CMBN_PLIP_ID , EPE.CMBN_PTIP_ID , EPE.CMBN_PTIP_OPT_ID , to_number(null) PL_ID , to_number(null) PL_TYP_ID , to_number(null) PTIP_ID , to_number(null) PLIP_ID , to_number(null) OIPL_ID , EPE.PER_IN_LER_ID , PIL.PERSON_ID , PIL.LER_ID , EPE.BUSINESS_GROUP_ID, POOL.BNFT_PRVDR_POOL_ID FROM BEN_ELIG_PER_ELCTBL_CHC EPE , BEN_ENRT_RT ECR , BEN_PL_TYP_F PTP , BEN_PL_F PL , BEN_OIPL_F OIPL , BEN_OPT_F OPT , BEN_CMBN_PLIP_F CMBN_PLIP , BEN_CMBN_PTIP_F CMBN_PTIP , BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT , BEN_PER_IN_LER PIL , BEN_BNFT_PRVDR_POOL_F POOL WHERE PIL.LF_EVT_OCRD_DT BETWEEN nvl(PTP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND nvl( PTP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN POOL.EFFECTIVE_START_DATE AND POOL.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(PL.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(PL.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OIPL.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(OIPL.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(OPT.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PLIP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PLIP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PTIP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP_OPT.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PTIP_OPT.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND EPE.PER_IN_LER_ID = PIL.PER_IN_LER_ID AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID AND ECR.RT_USG_CD = 'FLXCR' AND EPE.PL_TYP_ID = PTP.PL_TYP_ID(+) AND EPE.BNFT_PRVDR_POOL_ID = POOL.BNFT_PRVDR_POOL_ID AND EPE.PL_ID = PL.PL_ID(+) AND EPE.OIPL_ID = OIPL.OIPL_ID(+) AND OIPL.OPT_ID = OPT.OPT_ID(+) AND EPE.CMBN_PLIP_ID = CMBN_PLIP.CMBN_PLIP_ID(+) AND EPE.CMBN_PTIP_ID = CMBN_PTIP.CMBN_PTIP_ID(+) AND EPE.CMBN_PTIP_OPT_ID = CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID(+) AND EPE.CMBN_PTIP_ID IS NOT NULL 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 DISTINCT ECR.VAL , Null PL_TYP_NAME , Null PL_NAME , Null OPT_NAME , 'Multiple' PL_OPT_NAME , CMBN_PLIP.NAME CMBN_NAME , POOL.NAME POOL_NAME , EPE.PGM_ID , EPE.CMBN_PLIP_ID , EPE.CMBN_PTIP_ID , EPE.CMBN_PTIP_OPT_ID , to_number(null) , to_number(null) , to_number(null) , to_number(null) , to_number(null) , EPE.PER_IN_LER_ID , PIL.PERSON_ID , PIL.LER_ID , EPE.BUSINESS_GROUP_ID,POOL.BNFT_PRVDR_POOL_ID FROM BEN_ELIG_PER_ELCTBL_CHC EPE , BEN_ENRT_RT ECR , BEN_PL_TYP_F PTP , BEN_PL_F PL , BEN_OIPL_F OIPL , BEN_OPT_F OPT , BEN_CMBN_PLIP_F CMBN_PLIP , BEN_CMBN_PTIP_F CMBN_PTIP , BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT , BEN_PER_IN_LER PIL , BEN_BNFT_PRVDR_POOL_F POOL WHERE PIL.LF_EVT_OCRD_DT BETWEEN nvl(PTP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND nvl( PTP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN POOL.EFFECTIVE_START_DATE AND POOL.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(PL.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(PL.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OIPL.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(OIPL.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(OPT.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PLIP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PLIP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PTIP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP_OPT.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PTIP_OPT.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND EPE.PER_IN_LER_ID = PIL.PER_IN_LER_ID AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID AND ECR.RT_USG_CD = 'FLXCR' AND EPE.PL_TYP_ID = PTP.PL_TYP_ID(+) AND EPE.BNFT_PRVDR_POOL_ID = POOL.BNFT_PRVDR_POOL_ID AND EPE.PL_ID = PL.PL_ID(+) AND EPE.OIPL_ID = OIPL.OIPL_ID(+) AND OIPL.OPT_ID = OPT.OPT_ID(+) AND EPE.CMBN_PLIP_ID = CMBN_PLIP.CMBN_PLIP_ID(+) AND EPE.CMBN_PTIP_ID = CMBN_PTIP.CMBN_PTIP_ID(+) AND EPE.CMBN_PTIP_OPT_ID = CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID(+) AND EPE.CMBN_PLIP_ID IS NOT NULL 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 DISTINCT ECR.VAL , PTP.NAME PL_TYP_NAME , NULL PL_NAME , NULL OPT_NAME , 'Multiple' PL_OPT_NAME , CMBN_PTIP_OPT.NAME CMBN_NAME , POOL.NAME POOL_NAME , EPE.PGM_ID , EPE.CMBN_PLIP_ID , EPE.CMBN_PTIP_ID , EPE.CMBN_PTIP_OPT_ID , to_number(null) PL_ID , to_number(null) PL_TYP_ID , to_number(null) PTIP_ID , to_number(null) PLIP_ID , to_number(null) OIPL_ID , EPE.PER_IN_LER_ID , PIL.PERSON_ID , PIL.LER_ID , EPE.BUSINESS_GROUP_ID,POOL.BNFT_PRVDR_POOL_ID FROM BEN_ELIG_PER_ELCTBL_CHC EPE , BEN_ENRT_RT ECR , BEN_PL_TYP_F PTP , BEN_PL_F PL , BEN_OIPL_F OIPL , BEN_OPT_F OPT , BEN_CMBN_PLIP_F CMBN_PLIP , BEN_CMBN_PTIP_F CMBN_PTIP , BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT , BEN_PER_IN_LER PIL , BEN_BNFT_PRVDR_POOL_F POOL WHERE PIL.LF_EVT_OCRD_DT BETWEEN nvl(PTP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND nvl( PTP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN POOL.EFFECTIVE_START_DATE AND POOL.EFFECTIVE_END_DATE AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(PL.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(PL.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OIPL.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(OIPL.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(OPT.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PLIP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PLIP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PTIP.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP_OPT.EFFECTIVE_START_DATE , PIL.LF_EVT_OCRD_DT) AND NVL(CMBN_PTIP_OPT.EFFECTIVE_END_DATE , PIL.LF_EVT_OCRD_DT) AND EPE.PER_IN_LER_ID = PIL.PER_IN_LER_ID AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID AND ECR.RT_USG_CD = 'FLXCR' AND EPE.PL_TYP_ID = PTP.PL_TYP_ID(+) AND EPE.BNFT_PRVDR_POOL_ID = POOL.BNFT_PRVDR_POOL_ID AND EPE.PL_ID = PL.PL_ID(+) AND EPE.OIPL_ID = OIPL.OIPL_ID(+) AND OIPL.OPT_ID = OPT.OPT_ID(+) AND EPE.CMBN_PLIP_ID = CMBN_PLIP.CMBN_PLIP_ID(+) AND EPE.CMBN_PTIP_ID = CMBN_PTIP.CMBN_PTIP_ID(+) AND EPE.CMBN_PTIP_OPT_ID = CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID(+) AND EPE.CMBN_PTIP_OPT_ID IS NOT NULL 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 DISTINCT ECR.VAL
, PTP.NAME PL_TYP_NAME
, PL.NAME PL_NAME
, OPT.NAME OPT_NAME
, PL.NAME||' '||OPT.NAME PL_OPT_NAME
, NULL CMBN_NAME
, POOL.NAME POOL_NAME
, EPE.PGM_ID
, EPE.CMBN_PLIP_ID
, EPE.CMBN_PTIP_ID
, EPE.CMBN_PTIP_OPT_ID
, EPE.PL_ID
, EPE.PL_TYP_ID
, EPE.PTIP_ID
, EPE.PLIP_ID
, EPE.OIPL_ID
, EPE.PER_IN_LER_ID
, PIL.PERSON_ID
, PIL.LER_ID
, EPE.BUSINESS_GROUP_ID
, POOL.BNFT_PRVDR_POOL_ID
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_ENRT_RT ECR
, BEN_PL_TYP_F PTP
, BEN_PL_F PL
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_CMBN_PLIP_F CMBN_PLIP
, BEN_CMBN_PTIP_F CMBN_PTIP
, BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT
, BEN_PER_IN_LER PIL
, BEN_BNFT_PRVDR_POOL_F POOL
WHERE PIL.LF_EVT_OCRD_DT BETWEEN NVL(PTP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL( PTP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN POOL.EFFECTIVE_START_DATE
AND POOL.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(PL.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(PL.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OIPL.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP_OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PTIP_OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PLIP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PLIP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PTIP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND EPE.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID
AND ECR.RT_USG_CD = 'FLXCR'
AND EPE.PL_TYP_ID = PTP.PL_TYP_ID(+)
AND EPE.BNFT_PRVDR_POOL_ID = POOL.BNFT_PRVDR_POOL_ID
AND EPE.PL_ID = PL.PL_ID(+)
AND EPE.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND EPE.CMBN_PLIP_ID = CMBN_PLIP.CMBN_PLIP_ID(+)
AND EPE.CMBN_PTIP_ID = CMBN_PTIP.CMBN_PTIP_ID(+)
AND EPE.CMBN_PTIP_OPT_ID = CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID(+)
AND EPE.CMBN_PTIP_ID IS NULL
AND EPE.CMBN_PLIP_ID IS NULL
AND EPE.CMBN_PTIP_OPT_ID IS NULL
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 DISTINCT ECR.VAL
, 'MULTIPLE' PL_TYP_NAME
, NULL PL_NAME
, NULL OPT_NAME
, NULL PL_OPT_NAME
, CMBN_PTIP.NAME CMBN_NAME
, POOL.NAME POOL_NAME
, EPE.PGM_ID
, EPE.CMBN_PLIP_ID
, EPE.CMBN_PTIP_ID
, EPE.CMBN_PTIP_OPT_ID
, TO_NUMBER(NULL) PL_ID
, TO_NUMBER(NULL) PL_TYP_ID
, TO_NUMBER(NULL) PTIP_ID
, TO_NUMBER(NULL) PLIP_ID
, TO_NUMBER(NULL) OIPL_ID
, EPE.PER_IN_LER_ID
, PIL.PERSON_ID
, PIL.LER_ID
, EPE.BUSINESS_GROUP_ID
, POOL.BNFT_PRVDR_POOL_ID
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_ENRT_RT ECR
, BEN_PL_TYP_F PTP
, BEN_PL_F PL
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_CMBN_PLIP_F CMBN_PLIP
, BEN_CMBN_PTIP_F CMBN_PTIP
, BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT
, BEN_PER_IN_LER PIL
, BEN_BNFT_PRVDR_POOL_F POOL
WHERE PIL.LF_EVT_OCRD_DT BETWEEN NVL(PTP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL( PTP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN POOL.EFFECTIVE_START_DATE
AND POOL.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(PL.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(PL.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OIPL.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PLIP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PLIP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PTIP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP_OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PTIP_OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND EPE.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID
AND ECR.RT_USG_CD = 'FLXCR'
AND EPE.PL_TYP_ID = PTP.PL_TYP_ID(+)
AND EPE.BNFT_PRVDR_POOL_ID = POOL.BNFT_PRVDR_POOL_ID
AND EPE.PL_ID = PL.PL_ID(+)
AND EPE.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND EPE.CMBN_PLIP_ID = CMBN_PLIP.CMBN_PLIP_ID(+)
AND EPE.CMBN_PTIP_ID = CMBN_PTIP.CMBN_PTIP_ID(+)
AND EPE.CMBN_PTIP_OPT_ID = CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID(+)
AND EPE.CMBN_PTIP_ID IS NOT NULL
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 DISTINCT ECR.VAL
, NULL PL_TYP_NAME
, NULL PL_NAME
, NULL OPT_NAME
, 'MULTIPLE' PL_OPT_NAME
, CMBN_PLIP.NAME CMBN_NAME
, POOL.NAME POOL_NAME
, EPE.PGM_ID
, EPE.CMBN_PLIP_ID
, EPE.CMBN_PTIP_ID
, EPE.CMBN_PTIP_OPT_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, EPE.PER_IN_LER_ID
, PIL.PERSON_ID
, PIL.LER_ID
, EPE.BUSINESS_GROUP_ID
, POOL.BNFT_PRVDR_POOL_ID
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_ENRT_RT ECR
, BEN_PL_TYP_F PTP
, BEN_PL_F PL
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_CMBN_PLIP_F CMBN_PLIP
, BEN_CMBN_PTIP_F CMBN_PTIP
, BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT
, BEN_PER_IN_LER PIL
, BEN_BNFT_PRVDR_POOL_F POOL
WHERE PIL.LF_EVT_OCRD_DT BETWEEN NVL(PTP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL( PTP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN POOL.EFFECTIVE_START_DATE
AND POOL.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(PL.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(PL.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OIPL.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PLIP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PLIP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PTIP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP_OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PTIP_OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND EPE.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID
AND ECR.RT_USG_CD = 'FLXCR'
AND EPE.PL_TYP_ID = PTP.PL_TYP_ID(+)
AND EPE.BNFT_PRVDR_POOL_ID = POOL.BNFT_PRVDR_POOL_ID
AND EPE.PL_ID = PL.PL_ID(+)
AND EPE.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND EPE.CMBN_PLIP_ID = CMBN_PLIP.CMBN_PLIP_ID(+)
AND EPE.CMBN_PTIP_ID = CMBN_PTIP.CMBN_PTIP_ID(+)
AND EPE.CMBN_PTIP_OPT_ID = CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID(+)
AND EPE.CMBN_PLIP_ID IS NOT NULL
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 DISTINCT ECR.VAL
, PTP.NAME PL_TYP_NAME
, NULL PL_NAME
, NULL OPT_NAME
, 'MULTIPLE' PL_OPT_NAME
, CMBN_PTIP_OPT.NAME CMBN_NAME
, POOL.NAME POOL_NAME
, EPE.PGM_ID
, EPE.CMBN_PLIP_ID
, EPE.CMBN_PTIP_ID
, EPE.CMBN_PTIP_OPT_ID
, TO_NUMBER(NULL) PL_ID
, TO_NUMBER(NULL) PL_TYP_ID
, TO_NUMBER(NULL) PTIP_ID
, TO_NUMBER(NULL) PLIP_ID
, TO_NUMBER(NULL) OIPL_ID
, EPE.PER_IN_LER_ID
, PIL.PERSON_ID
, PIL.LER_ID
, EPE.BUSINESS_GROUP_ID
, POOL.BNFT_PRVDR_POOL_ID
FROM BEN_ELIG_PER_ELCTBL_CHC EPE
, BEN_ENRT_RT ECR
, BEN_PL_TYP_F PTP
, BEN_PL_F PL
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_CMBN_PLIP_F CMBN_PLIP
, BEN_CMBN_PTIP_F CMBN_PTIP
, BEN_CMBN_PTIP_OPT_F CMBN_PTIP_OPT
, BEN_PER_IN_LER PIL
, BEN_BNFT_PRVDR_POOL_F POOL
WHERE PIL.LF_EVT_OCRD_DT BETWEEN NVL(PTP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL( PTP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN POOL.EFFECTIVE_START_DATE
AND POOL.EFFECTIVE_END_DATE
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(PL.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(PL.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OIPL.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OIPL.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PLIP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PLIP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PTIP.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND PIL.LF_EVT_OCRD_DT BETWEEN NVL(CMBN_PTIP_OPT.EFFECTIVE_START_DATE
, PIL.LF_EVT_OCRD_DT)
AND NVL(CMBN_PTIP_OPT.EFFECTIVE_END_DATE
, PIL.LF_EVT_OCRD_DT)
AND EPE.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND ECR.ELIG_PER_ELCTBL_CHC_ID = EPE.ELIG_PER_ELCTBL_CHC_ID
AND ECR.RT_USG_CD = 'FLXCR'
AND EPE.PL_TYP_ID = PTP.PL_TYP_ID(+)
AND EPE.BNFT_PRVDR_POOL_ID = POOL.BNFT_PRVDR_POOL_ID
AND EPE.PL_ID = PL.PL_ID(+)
AND EPE.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND EPE.CMBN_PLIP_ID = CMBN_PLIP.CMBN_PLIP_ID(+)
AND EPE.CMBN_PTIP_ID = CMBN_PTIP.CMBN_PTIP_ID(+)
AND EPE.CMBN_PTIP_OPT_ID = CMBN_PTIP_OPT.CMBN_PTIP_OPT_ID(+)
AND EPE.CMBN_PTIP_OPT_ID IS NOT NULL
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')