List of Customer Accounts that have 2 or more Identifying Addresses

/* Formatted on 7/17/2014 12:07:32 PM (QP5 v5.115.810.9015) */
SELECT     /*+ use_nl(ps ca) */
      ca.account_number, cnt, ca.creation_date, ca.last_update_date
FROM (SELECT ps.party_id, COUNT (ps.party_id) cnt
      FROM hz_party_sites ps
      WHERE identifying_address_flag = 'Y'
      GROUP BY ps.party_id
      HAVING COUNT (ps.party_id) > 1) ps, hz_cust_accounts ca
WHERE ps.party_id = ca.party_id


/* Formatted on 7/17/2014 12:07:51 PM (QP5 v5.115.810.9015) */
SELECT ca.account_number, cnt, ca.creation_date, ca.last_update_date
FROM (SELECT ps.party_id, COUNT (ps.party_id) cnt
      FROM hz_party_sites ps
      WHERE identifying_address_flag = 'Y'
      GROUP BY ps.party_id
      HAVING COUNT (ps.party_id) > 1) ps, hz_cust_accounts ca
WHERE ps.party_id = ca.party_id