SQL Query to find out Duplicate Customer Contacts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
/**********************************************************
 *PURPOSE: To list all Duplicate Customer Contacts with   *
 *         application through which a contact is created *
 *AUTHOR: Shailender Thallam                              *
 **********************************************************/
SELECT hpp.party_name "Contact Person Name",
  hpc.party_name "Customer Name"            ,
  hpc.party_number "Registry ID"            ,
  fat.application_name "Created from Application"
   FROM apps.hz_relationships hr,
  apps.hz_parties hpp           ,
  apps.hz_parties hpc           ,
  fnd_application_tl fat
  WHERE 1                            =1
AND hr.subject_table_name            = 'HZ_PARTIES'
AND hr.object_type                   = 'ORGANIZATION'
AND hr.subject_id                    = hpp.party_id
AND hr.object_id                     = hpc.party_id
AND hr.status                       <> 'I'
AND hpp.application_id               = fat.application_id
AND fat.language                     = 'US'
AND (hpp.party_name,hpc.party_name) IN
  (SELECT hpp.party_name,
    hpc.party_name
     FROM apps.hz_relationships hr,
    apps.hz_parties hpp           ,
    apps.hz_parties hpc
    WHERE 1                 =1
  AND hr.subject_table_name = 'HZ_PARTIES'
  AND hr.object_type        = 'ORGANIZATION'
  AND hr.subject_id         = hpp.party_id
  AND hr.object_id          = hpc.party_id
  AND hr.status            <> 'I'
 GROUP BY hpp.party_name ,
    hpc.party_name
  HAVING COUNT(hpp.party_name) > 1
  )
ORDER BY hpp.party_name ;