Query to get customer contacts by site

Summary:

Query to get customer contacts by site

Content (please ensure you mask any confidential information):

Hi,

I am trying to fetch AR Customer contact information which is displayed on Oracle Fusion screen. In case of multiple sites with each site having it's each contact person, I am getting cross joins between tables and not able to fetch the correct data. These type of customers do not have data in HZ_CONTACT_POINTS table for their corresponding sites. Hence, I am finding no way to join the contact person with the individual site.

SELECT hp.party_name,

       hps.party_site_number,

       hp1.party_name    contact_person,

       hp1.email_address contact_person_email

FROM   hz_parties hp,

       hz_party_sites hps,

       hz_relationships hr,

       hz_parties hp1

WHERE  hp.party_id = hps.party_id

       AND hr.object_id = hp.party_id

       AND hr.subject_id = hp1.party_id