-- Search Supplier Contact
SELECT DISTINCT asu.party_id, asu.segment1 Supp_Num
,asu.vendor_name
,hpc.party_name Contact_Name
,hpr.primary_phone_country_code cnt_cntry
,hpr.primary_phone_area_code cnt_area
,hpr.primary_phone_number cnt_phone
,assa.vendor_site_code
,assa.vendor_site_id
,asco.vendor_contact_id
FROM
 hz_relationships hr
,ap_suppliers asu
,ap_supplier_sites_all assa
,ap_supplier_contacts asco
,hz_org_contacts hoc
,hz_parties hpc
,hz_parties hpr
,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';


Ur's
AmarAlam