Waive Coverage Root UE

 /* Waive Coverage */
SELECT A.PERSON_ID
, A.PER_IN_LER_ID
, A.ROW_ID
, A.LAST_UPDATE_DATE
, A.LIFE_EVENT
, A.PRTT_ENRT_RSLT_ID
, A.BNFT_AMT
, A.BEN_PL
, A.PL_CD
, A.PL_FULL_NAME
, A.OPTD_FOR
, A.PREV_PL
, A.PREV_ENRT_RSLT_ID
, B.ORGNL_ENRT_DT
, B.ENRT_CVG_STRT_DT CVG_STRT_DT
, B.ENRT_CVG_THRU_DT CVG_THRU_DT
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
, LEAD(BPF.SHORT_NAME) OVER (PARTITION BY BPER.PERSON_ID ORDER BY BPER.ENRT_CVG_STRT_DT DESC) PREV_PL
, LEAD(BPER.PRTT_ENRT_RSLT_ID) OVER (PARTITION BY BPER.PERSON_ID ORDER BY BPER.ENRT_CVG_STRT_DT DESC) PREV_ENRT_RSLT_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 = '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 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 BPF.SHORT_NAME LIKE 'ANT%'
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 = 9133000047229599 */
) A, BEN_PRTT_ENRT_RSLT B
WHERE 1=1
AND A.ROW_ID = 1
AND (A.PL_CD NOT LIKE 'ANT%' AND A.PREV_PL like 'ANT%' AND A.LIFE_EVENT NOT like '%Hire'
AND (
A.CVG_STRT_DT BETWEEN :FromDate AND :ToDate
OR
(A.CVG_STRT_DT <= :ToDate AND TRUNC(A.LAST_UPDATE_DATE) BETWEEN :FromDate AND :ToDate)
)

)
AND B.PRTT_ENRT_RSLT_ID = A.PREV_ENRT_RSLT_ID