List of Phones used by more than one accounts

SELECT hcp_out.phone_area_code
     , hcp_out.phone_number
     , hca_out.account_number
     , party_name
  FROM hz_contact_points hcp_out
     , hz_cust_accounts  hca_out
     , hz_parties        party
     , ( SELECT hcp.phone_area_code
              , hcp.phone_number
           -- , COUNT(DISTINCT hca.account_number)
           FROM hz_contact_points hcp
              , hz_cust_accounts  hca
              , (SELECT hcp_in.phone_area_code
                      , hcp_in.phone_number
                   FROM hz_contact_points hcp_in
                  WHERE hcp_in.creation_date      >= TO_DATE('1-MAR-2008','DD-MON-YYYY')
                    AND hcp_in.creation_date      <  TO_DATE('1-APR-2008','DD-MON-YYYY')
                    AND hcp_in.owner_table_name   = 'HZ_PARTIES'
                    AND hcp_in.contact_point_type = 'PHONE'
                      ) new_phone
          WHERE hcp.phone_area_code = new_phone.phone_area_code
            AND hcp.phone_number    = new_phone.phone_number
            AND hca.party_id           = hcp.owner_table_id
          GROUP BY hcp.phone_area_code
                 , hcp.phone_number
         HAVING COUNT(DISTINCT hca.account_number) > 1
       ) dup_phone
  WHERE hcp_out.phone_area_code = dup_phone.phone_area_code
    AND hcp_out.phone_number    = dup_phone.phone_number
    AND hca_out.party_id        = hcp_out.owner_table_id
    AND party.party_id          = hca_out.party_id
  ORDER BY hcp_out.phone_area_code
         , hcp_out.phone_number
         , hca_out.account_number