List of Customer Accounts that have 2 or more Identifying Addresses

Owing to some bug in 11.5.9, a customer can end up having two or more identifying addresses. This is incorrect and causes problem in some forms such as Oracle Service Request form. This query can identify such accounts and such accounts should be fixed using customer standard form.

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