Query to get Supplier Contacts in Oracle Apps R12

 SELECT DISTINCT

asu.party_id

,asu.segment1 vendor_number

,asu.vendor_name 

,hpc.party_name contact_name 

,hpr.primary_phone_country_code 

,hpr.primary_phone_area_code 

,hpr.primary_phone_number 

,assa.vendor_site_code 

,assa.vendor_site_id 

,asco.vendor_contact_id 

,assa.party_site_id

    ,asco.org_party_site_id

FROM apps.hz_relationships hr 

    ,apps.ap_suppliers asu 

,apps.ap_supplier_sites_all assa 

,apps.ap_supplier_contacts asco 

,apps.hz_org_contacts hoc 

,apps.hz_parties hpc 

,apps.hz_parties hpr 

,apps.hz_contact_points hpcp

WHERE hoc.party_relationship_id = hr.relationship_id

  AND hr.subject_id         = asu.party_id

  AND hr.relationship_code  = 'CONTACT'

  AND hr.object_table_name  = 'HZ_PARTIES'

  AND asu.vendor_id         = assa.vendor_id

  AND hr.object_id          = hpc.party_id

  AND hr.party_id           = hpr.party_id

  AND asco.relationship_id  = hoc.party_relationship_id

  AND assa.party_site_id    = asco.org_party_site_id

  AND hpr.party_type        ='PARTY_RELATIONSHIP'

  AND hpr.party_id          = hpcp.owner_table_id

  AND hpcp.owner_table_name = 'HZ_PARTIES'

  AND assa.vendor_site_id   = asco.vendor_site_id

  AND asu.vendor_name       = 'XX_VENDOR_NAME'