Plan Enrollment Results report in Oracle Advanced Benefits (OAB)

SELECT DISTINCT per.full_name pl_full_name, per.first_name pl_first_name,
                ben_batch_utils.get_pl_name (pen.pl_id,
                                             :p_business_group_id,
                                             :p_report_end_date
                                            ) pl_pl_name,
                DECODE
                   (pen.oipl_id,
                    NULL, NULL,
                    ben_batch_utils.get_opt_name (pen.oipl_id,
                                                  :p_business_group_id,
                                                  :p_report_end_date
                                                 )
                   ) pl_opt_name,
                per.person_id pl_person_id,
                pen.orgnl_enrt_dt pl_orgnl_enrt_dt,
                pen.enrt_cvg_strt_dt pl_enrt_dt, per.last_name pl_last_name,
                pen.uom pl_uom
           FROM per_all_people_f per,
                per_all_assignments_f asg,
                ben_prtt_enrt_rslt_f pen,
                ben_per_in_ler pil
          WHERE (:p_person_id IS NULL OR per.person_id = :p_person_id)
            AND :p_report_end_date BETWEEN per.effective_start_date
                                       AND per.effective_end_date
            AND per.business_group_id = :p_business_group_id
            AND asg.person_id = per.person_id
            AND asg.assignment_type <> 'C'
            AND asg.primary_flag = 'Y'
            AND pen.person_id = per.person_id
            AND pen.prtt_enrt_rslt_stat_cd IS NULL
            AND pen.business_group_id = :p_business_group_id
            AND (   pen.enrt_cvg_thru_dt >= pen.effective_end_date
                 OR pen.enrt_ovridn_flag = 'Y'
                )
            AND (   :p_report_start_date BETWEEN pen.enrt_cvg_strt_dt
                                             AND enrt_cvg_thru_dt
                 OR :p_report_end_date BETWEEN pen.enrt_cvg_strt_dt
                                           AND enrt_cvg_thru_dt
                 OR (    :p_report_start_date >= pen.enrt_cvg_strt_dt
                     AND :p_report_end_date <= enrt_cvg_thru_dt
                    )
                 OR (    :p_report_start_date <= pen.enrt_cvg_strt_dt
                     AND :p_report_end_date >= enrt_cvg_thru_dt
                    )
                )
            AND (   (    pen.effective_end_date < pen.enrt_cvg_thru_dt
                     AND (   :p_report_start_date
                                BETWEEN pen.effective_start_date
                                    AND pen.effective_end_date
                          OR :p_report_end_date BETWEEN pen.effective_start_date
                                                    AND pen.effective_end_date
                          OR (    :p_report_start_date >=
                                                      pen.effective_start_date
                              AND :p_report_end_date <= pen.effective_end_date
                             )
                          OR (    :p_report_start_date <=
                                                      pen.effective_start_date
                              AND :p_report_end_date >= pen.effective_end_date
                             )
                         )
                    )
                 OR pen.effective_end_date >= pen.enrt_cvg_thru_dt
                )
            AND pen.sspndd_flag = 'N'
            AND pen.per_in_ler_id = pil.per_in_ler_id
            AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
            AND pil.person_id = per.person_id
            AND ben_batch_utils.get_pl_name (pen.pl_id,
                                             :p_business_group_id,
                                             :p_report_end_date
                                            ) LIKE 'Life%UAE%'