HRMS Benefits query to fetch the beneficiaries - Oracle Advanced Benefits

Query to Fetch Beneficiaries

SELECT 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 = hr_general.end_of_time
  -- 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 :p_report_end_date BETWEEN pbn.effective_start_date
                              AND pbn.effective_end_date
   AND :p_report_end_date BETWEEN NVL (ppf.effective_start_date,
                                       :p_report_end_date
                                      )
                              AND NVL (ppf.effective_end_date,
                                       :p_report_end_date
                                      )
   AND :p_report_end_date BETWEEN NVL (emp_papf.effective_start_date,
                                       :p_report_end_date
                                      )
                              AND NVL (emp_papf.effective_end_date,
                                       :p_report_end_date
                                      )
   AND :p_report_end_date BETWEEN pen.effective_start_date
                              AND pen.effective_end_date