AP Supplier Data

  -----------------------------------------Supplier Data-------------------------------------------------------------------

select distinct * from 

(

select distinct aps.segment1 Supplier, aps.VENDOR_NAME

--,apsa.VENDOR_SITE_ID

,apsa.VENDOR_SITE_CODE

,apsa.ADDRESS_LINE1 || '-' || apsa.ADDRESS_LINE2 || '-' || apsa.ADDRESS_LINE3||'-' ||apsa.city Address,apsa.phone

from ap_suppliers aps,ap_supplier_sites_all apsa

where apsa.VENDOR_ID=aps.vendor_id

--and aps.segment1 = 1030

order by aps.segment1

)tab1,

(

SELECT  distinct asu.segment1 Supp_Num

,hpcp.email_address

FROM

 hz_relationships hr

,ap_suppliers asu

,ap_supplier_sites_all assa

,hz_org_contacts hoc

,hz_parties hpr

,hz_contact_points hpcp

WHERE 

1 = 1

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.party_id = hpr.party_id

AND hpr.party_type='PARTY_RELATIONSHIP'

AND hpcp.contact_point_type='EMAIL'

--AND assa.org_id = xxxx

AND hpr.party_id = hpcp.owner_table_id

AND hpcp.owner_table_name = 'HZ_PARTIES'

)tab2

where tab1.Supplier = tab2.Supp_Num(+)

--and tab1.Supplier = 4897

----------------------------------------------------------------------------------------------------------