Sunday, April 11, 2010

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) */
, 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

No comments:

Post a Comment