Supplier Contact Query -R12 AP

The below query will be handy to fetch the supplier contact information in Oracle apps R12 -AP

SELECT DISTINCT asu.party_id, asu.segment1 Supp_Num

,asu.vendor_name

--,asu.segment1 vendor_num

,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'

AND Asu.SEGMENT1 in ('160V')

Happy Learning !