Waive Coverage EE DM

 /* Waive Coverage - Associate Record */
SELECT PAPF.PERSON_ID
, PAPF.PERSON_NUMBER PER_NUM
, PNIF.NATIONAL_IDENTIFIER_NUMBER SSN
, PPNF.FIRST_NAME FNAME
, PPNF.LAST_NAME LNAME
, PPNF.MIDDLE_NAMES MNAME
, PP.DATE_OF_BIRTH DOB
, PPLF.SEX
, PPOS.HIRE_DT
, PPOS.TERM_DT
, PPOS.SVRN_ST_DATE
, PAAM.ASSIGNMENT_ID
, PAAM.REASON_CODE
, PAST.USER_STATUS
, XLE.LEGAL_ENTITY_IDENTIFIER COMPANY
, (SELECT (PPH.AREA_CODE || '-' || PPH.PHONE_NUMBER) FROM PER_PHONES PPH WHERE PPH.PERSON_ID = PAPF.PERSON_ID  AND PPH.PHONE_ID = PAPF.PRIMARY_PHONE_ID AND ROWNUM = 1) PR_PH
, HLAFV.ATTRIBUTE1 LOCATION
, HLAFV.REGION_2 WRK_STATE
, PEA.EMAIL
, PAF.ADDRESS_LINE_1 ADDR_LN1
, PAF.ADDRESS_LINE_2 ADDR_LN2
, PAF.TOWN_OR_CITY CITY
, PAF.REGION_2 STATE
, PAF.REGION_1 COUNTY
, PAF.COUNTRY
, NVL(REPLACE(PAF.POSTAL_CODE, '-', ''), ' ') ZIP
FROM PER_ALL_PEOPLE_F PAPF
, PER_NATIONAL_IDENTIFIERS PNIF
, PER_PERSON_NAMES_F PPNF
, PER_PERSONS PP
, PER_PEOPLE_LEGISLATIVE_F PPLF
, (SELECT P.PERSON_ID, P.PERIOD_OF_SERVICE_ID, P.DATE_START HIRE_DT, P.ORIGINAL_DATE_OF_HIRE ORIG_HIRE_DT
, P.ACTUAL_TERMINATION_DATE TERM_DT, P.ATTRIBUTE_DATE1 SVRN_ST_DATE
, ROW_NUMBER() OVER (PARTITION BY P.PERSON_ID ORDER BY P.DATE_START DESC) ROW_ID
FROM PER_PERIODS_OF_SERVICE P) PPOS
, PER_ALL_ASSIGNMENTS_M PAAM
, PER_ASSIGNMENT_STATUS_TYPES_TL PAST
, HR_ALL_ORGANIZATION_UNITS_F_VL HAOU_LE
, XLE_ENTITY_PROFILES XLE
, HR_LOCATIONS_ALL_F_VL HLAFV
, (SELECT PEA.PERSON_ID, PEA.EMAIL_ADDRESS EMAIL, ROW_NUMBER() OVER (PARTITION BY PEA.PERSON_ID ORDER BY PEA.DATE_FROM DESC) ROW_ID FROM PER_EMAIL_ADDRESSES PEA WHERE PEA.EMAIL_TYPE = 'H1') PEA
, PER_ADDRESSES_F PAF
WHERE 1=1
AND PPOS.ROW_ID = '1'
AND PAPF.PERSON_ID = PPOS.PERSON_ID
AND :ToDate BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PNIF.PERSON_ID(+) = PAPF.PERSON_ID
AND PNIF.NATIONAL_IDENTIFIER_ID(+) = PAPF.PRIMARY_NID_ID
AND PPNF.PERSON_ID = PAPF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND :ToDate BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND PP.PERSON_ID = PAPF.PERSON_ID
AND PPLF.PERSON_ID(+) = PAPF.PERSON_ID
AND PPLF.LEGISLATION_CODE(+) = 'US'
AND :ToDate BETWEEN PPLF.EFFECTIVE_START_DATE AND PPLF.EFFECTIVE_END_DATE
AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND :ToDate BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND PAST.ASSIGNMENT_STATUS_TYPE_ID = PAAM.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.LANGUAGE = 'US'
AND HAOU_LE.ORGANIZATION_ID = PAAM.LEGAL_ENTITY_ID
AND :ToDate BETWEEN HAOU_LE.EFFECTIVE_START_DATE AND HAOU_LE.EFFECTIVE_END_DATE
AND XLE.LEGAL_ENTITY_ID = HAOU_LE.LEGAL_ENTITY_ID
AND HLAFV.LOCATION_ID(+) = PAAM.LOCATION_ID
AND HLAFV.REGION_2 <> 'PR'
AND :ToDate BETWEEN HLAFV.EFFECTIVE_START_DATE(+) AND HLAFV.EFFECTIVE_END_DATE(+)
AND PEA.PERSON_ID(+) = PAPF.PERSON_ID
AND PEA.ROW_ID(+) = 1
AND PAF.ADDRESS_ID(+) = PAPF.MAILING_ADDRESS_ID
AND :ToDate BETWEEN PAF.EFFECTIVE_START_DATE(+) AND PAF.EFFECTIVE_END_DATE(+)