Benefits Dropped Dependent Data Model

/* Dropped Dependents  */
SELECT BPIL.PERSON_ID
, BPIL.PER_IN_LER_ID
, BPIL.LF_EVT_OCRD_DT
, BPIL.LAST_UPDATE_DATE
, BLF.NAME LIFE_EVENT
, BPER.ENDED_PER_IN_LER_ID
, BPER.ASSIGNMENT_ID
, BPER.ORGNL_ENRT_DT
, BPER.ENRT_CVG_STRT_DT CVG_STRT_DT
, BPER.ENRT_CVG_THRU_DT CVG_THRU_DT
, BPER.BNFT_AMT
, BPTF.NAME BEN_PL
, BPF.SHORT_NAME PL_CD
, BPF.NAME PL_FULL_NAME
, BOF.NAME OPTD_FOR 
, BECD.DPNT_PERSON_ID
, PCRF.CONTACT_TYPE RLNSHP_CD
, PCRF.CONT_INFORMATION2 SSN_DP_2
, (SELECT PNIF_DP.NATIONAL_IDENTIFIER_NUMBER 
FROM PER_NATIONAL_IDENTIFIERS PNIF_DP
WHERE PNIF_DP.PERSON_ID = BECD.DPNT_PERSON_ID AND PNIF_DP.NATIONAL_IDENTIFIER_TYPE = 'SSN') SSN_DP
, PPNF_DP.FIRST_NAME FNAME_DP
, PPNF_DP.MIDDLE_NAMES MNAME_DP
, PPNF_DP.LAST_NAME LNAME_DP
, PPNF_DP.SUFFIX SUFFIX_DP
, PP_DP.DATE_OF_BIRTH DOB_DP
, PPLF_DP.SEX SEX_DP
, PPLF_DP.MARITAL_STATUS MARITAL_STATUS_DP
, FLV.MEANING RLNSHP
, (SELECT BPLHCF.REGISTERED_DISABLED_FLAG FROM BEN_PER_LE_HABITS_COV_F BPLHCF WHERE BPLHCF.PERSON_ID = BECD.DPNT_PERSON_ID
AND :ToDate BETWEEN BPLHCF.EFFECTIVE_START_DATE AND BPLHCF.EFFECTIVE_END_DATE AND ROWNUM = 1) REGISTERED_DISABLED_FLAG
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
, BEN_ELIG_CVRD_DPNT BECD
, BEN_PRTT_ENRT_RSLT BPER_NEXT
, PER_CONTACT_RELSHIPS_F PCRF
, FND_LOOKUP_VALUES FLV
, PER_PERSON_NAMES_F PPNF_DP
, PER_PERSONS PP_DP
, PER_PEOPLE_LEGISLATIVE_F PPLF_DP
WHERE BPIL.PER_IN_LER_STAT_CD = 'PROCD'
AND BLF.LER_ID = BPIL.LER_ID
-- AND ff_user_tables_pkg.get_table_value_ent (1, BPIL.LF_EVT_OCRD_DT,'LB_I_BN199_COBRA_OUTBOUND_UDT', 'Dependent_Life_Event' , BLF.NAME) = 'Y'
-- AND BLF.NAME IN ('LB Drop Dependent - Divorce','LB Drop Dependent - COBRA')
AND BPIL.LF_EVT_OCRD_DT BETWEEN BLF.EFFECTIVE_START_DATE AND BLF.EFFECTIVE_END_DATE
AND BPIL.PERSON_ID = BPER.PERSON_ID
AND BPER.BENEFIT_RELATION_ID = BPIL.BENEFIT_RELATION_ID
AND BPER.ENDED_PER_IN_LER_ID = BPIL.PER_IN_LER_ID
AND BPER.PER_IN_LER_ID <> BPER.ENDED_PER_IN_LER_ID
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.NAME NOT IN ('Waive Medical')
AND BOF.OPT_ID(+) = BPER.OPT_ID
AND BPER.ENRT_CVG_STRT_DT BETWEEN BOF.EFFECTIVE_START_DATE(+) AND BOF.EFFECTIVE_END_DATE(+)
AND BECD.PRTT_ENRT_RSLT_ID = BPER.PRTT_ENRT_RSLT_ID
AND BECD.PER_IN_LER_ID = BPER.PER_IN_LER_ID
AND BPER_NEXT.PERSON_ID = BPER.PERSON_ID
AND BPER_NEXT.ENRT_CVG_STRT_DT = (BPER.ENRT_CVG_THRU_DT + 1)
AND BPER_NEXT.PL_TYP_ID = BPER.PL_TYP_ID
AND BPER_NEXT.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND NOT EXISTS (SELECT 1 FROM BEN_ELIG_CVRD_DPNT BECD_NEXT WHERE BECD_NEXT.DPNT_PERSON_ID = BECD.DPNT_PERSON_ID AND BECD_NEXT.PRTT_ENRT_RSLT_ID = BPER_NEXT.PRTT_ENRT_RSLT_ID)
AND PCRF.CONTACT_PERSON_ID = BECD.DPNT_PERSON_ID
AND :ToDate BETWEEN PCRF.EFFECTIVE_START_DATE AND PCRF.EFFECTIVE_END_DATE
AND PCRF.CONTACT_TYPE = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'CONTACT'
AND FLV.LANGUAGE = 'US'
AND PPNF_DP.PERSON_ID = BECD.DPNT_PERSON_ID
AND PPNF_DP.NAME_TYPE = 'GLOBAL'
AND :ToDate BETWEEN PPNF_DP.EFFECTIVE_START_DATE AND PPNF_DP.EFFECTIVE_END_DATE
AND PP_DP.PERSON_ID = BECD.DPNT_PERSON_ID
AND PPLF_DP.PERSON_ID(+) = BECD.DPNT_PERSON_ID
AND PPLF_DP.LEGISLATION_CODE(+) = 'US'
AND :ToDate BETWEEN PPLF_DP.EFFECTIVE_START_DATE AND PPLF_DP.EFFECTIVE_END_DATE
AND ((BPER.ENRT_CVG_THRU_DT <= :FromDate AND TRUNC(BPER.LAST_UPDATE_DATE) BETWEEN :FromDate AND :ToDate)
OR (BPER.ENRT_CVG_THRU_DT BETWEEN :FromDate AND :ToDate)
)