HRMS Benefits query to fetch the dependent of the beneficiaries

SELECT DISTINCT emp_papf.full_name employee_full_name,
emp_papf.national_identifier employee_ssn,
ppf.full_name dependent_name,
ppf.national_identifier dependent_ssn
FROM ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp,
per_contact_relationships pcr,
per_all_people_f ppf,
per_all_people_f emp_papf,
ben_per_in_ler pil
WHERE epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pdp.dpnt_person_id = pcr.contact_person_id
AND pcr.contact_person_id = ppf.person_id
AND pcr.person_id = emp_papf.person_id
AND pcr.personal_flag = 'Y'
AND epe.prtt_enrt_rslt_id IS NOT NULL
AND epe.per_in_ler_id = pil.per_in_ler_id
AND pcr.person_id = pil.person_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN pdp.effective_start_date
AND pdp.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd')
BETWEEN NVL (pcr.date_start,
TO_DATE (sysdate, 'rrrr/mm/dd'))
AND NVL (pcr.date_end, TO_DATE (sysdate, 'rrrr/mm/dd'))
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd')
BETWEEN emp_papf.effective_start_date
AND emp_papf.effective_end_date
AND TO_DATE (sysdate, 'rrrr/mm/dd') BETWEEN pen.effective_start_date
AND pen.effective_end_date
AND pdp.cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pdp.per_in_ler_id = pil.per_in_ler_id

HRMS Benefits query to fetch the beneficiaries

SELECT DISTINCT emp_papf.full_name employee_name,
emp_papf.national_identifier employee_ssn,
ppf.full_name beneficiary_name,
ppf.national_identifier beneficiary_ssn
FROM per_all_people_f ppf,
per_contact_relationships pcr,
ben_prtt_enrt_rslt_f pen,
ben_pl_bnf_f pbn,
per_all_people_f emp_papf
WHERE 1 = 1
AND pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.enrt_cvg_thru_dt = TO_DATE ('4712/12/31', 'rrrr/mm/dd')
AND pcr.personal_flag(+) = 'Y'
AND pbn.bnf_person_id = pcr.contact_person_id(+)
AND pbn.bnf_person_id = ppf.person_id(+)
AND emp_papf.person_id = pcr.person_id
AND TO_DATE (SYSDATE, 'rrrr/mm/dd') BETWEEN pbn.effective_start_date
AND pbn.effective_end_date
AND TO_DATE (SYSDATE, 'rrrr/mm/dd')
BETWEEN NVL (ppf.effective_start_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND NVL (ppf.effective_end_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND TO_DATE (SYSDATE, 'rrrr/mm/dd')
BETWEEN NVL (emp_papf.effective_start_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND NVL (emp_papf.effective_end_date,
TO_DATE (SYSDATE, 'rrrr/mm/dd'))
AND TO_DATE (SYSDATE, 'rrrr/mm/dd') BETWEEN pen.effective_start_date
AND pen.effective_end_date