Person Profile Overlapping Start and End Dates

/* Formatted on 7/17/2014 12:03:09 PM (QP5 v5.115.810.9015) */
SELECT pp_out.party_id,
       (SELECT account_number
        FROM ar.hz_cust_accounts ca
        WHERE ca.party_id = pp_out.party_id)
          acct_num,
       (SELECT pty.party_type || ',' || pty.orig_system_reference
        FROM ar.hz_parties pty
        WHERE pty.party_id = pp_out.party_id)
          party_type,
       pp_out.person_profile_id,
       pp_out.creation_date,
       pp_out.effective_start_date,
       pp_out.effective_end_date,
       pp_out.*
FROM ar.hz_person_profiles pp_out
WHERE pp_out.party_id IN
            (SELECT party_id
             FROM ar.hz_person_profiles pp
             WHERE EXISTS
                      (SELECT 1
                       FROM ar.hz_person_profiles pp_in
                       WHERE pp_in.party_id = pp.party_id
                             AND pp_in.person_profile_id <> pp.person_profile_id
                             AND (pp.effective_start_date BETWEEN pp_in.effective_start_date
                                                              AND  NVL (pp_in.effective_end_date,
                                                                        SYSDATE
                                                                        + 100
                                                                   )
                                  OR NVL (pp.effective_end_date, SYSDATE) BETWEEN pp_in.effective_start_date
                                                                              AND  NVL (pp_in.effective_end_date,
                                                                                        SYSDATE
                                                                                        + 100
                                                                                   ))))
ORDER BY pp_out.party_id DESC