Ben Root Data Model

SELECT PERSON_ID
       , PER_IN_LER_ID
       , LAST_UPDATE_DATE

       , LIFE_EVENT

       , PRTT_ENRT_RSLT_ID

       , ORGNL_ENRT_DT

       , CVG_STRT_DT

       , CVG_THRU_DT

       , BNFT_AMT

       , BEN_PL

       , PL_CD

       , PL_FULL_NAME

       , OPTD_FOR

FROM (

SELECT BPIL.PERSON_ID

, BPIL.PER_IN_LER_ID

, BLF.NAME LIFE_EVENT

, BPER.PRTT_ENRT_RSLT_ID

, BPER.ORGNL_ENRT_DT

, BPER.ENRT_CVG_STRT_DT CVG_STRT_DT

, BPER.ENRT_CVG_THRU_DT CVG_THRU_DT

, BPER.LAST_UPDATE_DATE

, BPER.BNFT_AMT

, BPTF.NAME BEN_PL

, BPF.SHORT_NAME PL_CD

, BPF.NAME PL_FULL_NAME

, BOF.NAME OPTD_FOR 

, ROW_NUMBER() OVER (PARTITION BY BPER.PERSON_ID ORDER BY BPER.ENRT_CVG_STRT_DT DESC) ROW_ID

FROM BEN_PER_IN_LER BPIL

, BEN_LER_F BLF

, BEN_PRTT_ENRT_RSLT BPER

, BEN_PL_TYP_F BPTF

, BEN_PL_F BPF

, BEN_OPT_F BOF

WHERE 1=1

AND BPIL.PER_IN_LER_STAT_CD IN ('STRTD','PROCD')

AND BLF.LER_ID = BPIL.LER_ID

AND BPIL.LF_EVT_OCRD_DT BETWEEN BLF.EFFECTIVE_START_DATE AND BLF.EFFECTIVE_END_DATE

AND BPER.PERSON_ID = BPIL.PERSON_ID

AND BPER.BENEFIT_RELATION_ID = BPIL.BENEFIT_RELATION_ID

AND BPER.PER_IN_LER_ID = BPIL.PER_IN_LER_ID

AND BPER.ENRT_CVG_STRT_DT < BPER.ENRT_CVG_THRU_DT

AND BPER.ENRT_CVG_STRT_DT <= :ToDate

AND BPTF.PL_TYP_ID = BPER.PL_TYP_ID

AND BPTF.NAME IN ('Medical')

AND BPER.ENRT_CVG_STRT_DT BETWEEN BPTF.EFFECTIVE_START_DATE AND BPTF.EFFECTIVE_END_DATE

AND BPF.PL_ID = BPER.PL_ID

AND BOF.OPT_ID(+) = BPER.OPT_ID

AND BPER.ENRT_CVG_STRT_DT BETWEEN BOF.EFFECTIVE_START_DATE(+) AND BOF.EFFECTIVE_END_DATE(+)

AND ((:ChangesOnly = 'Y' AND BPER.LAST_UPDATE_DATE BETWEEN :FromDate AND :ToDate) 

OR (NVL(:ChangesOnly, ' ') <> 'Y'))

-- AND BPER.PERSON_ID = 1234567890 

)

WHERE 1=1

AND ROW_ID = 1

AND PL_CD LIKE 'ANT%'

AND (:ToDate + 1 BETWEEN CVG_STRT_DT and CVG_THRU_DT

OR ( CVG_THRU_DT BETWEEN :FromDate AND :ToDate

OR (CVG_THRU_DT <= :ToDate AND TRUNC(LAST_UPDATE_DATE) BETWEEN :FromDate AND :ToDate)

)

)