Waive Coverage Dependent DM

 /* Waive Coverage - Dependent Record */
SELECT PCRF.PERSON_ID
, PCRF.CONTACT_TYPE RLNSHP_CD
, PCRF.CONT_INFORMATION2 SSN_DP_2
, BECD.PRTT_ENRT_RSLT_ID
, BECD.PER_IN_LER_ID
, BECD.DPNT_PERSON_ID
, BECD.ORGNL_PLAN_CVG_STRT_DT ORGNL_ENRT_DT
, BECD.CVG_STRT_DT
, BECD.CVG_THRU_DT
, FLV.MEANING RLNSHP
, (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.LAST_NAME LNAME_DP
, PPNF_DP.FIRST_NAME FNAME_DP
, PPNF_DP.MIDDLE_NAMES MNAME_DP
, PP_DP.DATE_OF_BIRTH DOB_DP
, PPLF_DP.SEX SEX_DP
, (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 PER_CONTACT_RELSHIPS_F PCRF
, BEN_ELIG_CVRD_DPNT BECD
, FND_LOOKUP_VALUES FLV
, PER_PERSON_NAMES_F PPNF_DP
, PER_PERSONS PP_DP
, PER_PEOPLE_LEGISLATIVE_F PPLF_DP
WHERE :ToDate BETWEEN PCRF.EFFECTIVE_START_DATE AND PCRF.EFFECTIVE_END_DATE
AND BECD.DPNT_PERSON_ID = PCRF.CONTACT_PERSON_ID
AND FLV.LOOKUP_CODE = (CASE WHEN PCRF.CONTACT_TYPE='A' THEN 'C' ELSE PCRF.CONTACT_TYPE END)
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