Supplier Contact Information Queries

Hi Everyone,
Recently i had got a requirement to gather all the Email ids , those can be attached to any supplier.


Their are 4 places, we can store Email Addresses for any supplier in Oracle R12.

1. Address Book--> Communication Field.
2. Contact Directory
3. Remittance Email id at Site Level
4. Remittance Email id at Header Level

Queries : 

• Address Book --> Communication Field

select ass.vendor_name, ass.segment1 supplier_num,hcp.email_address

from hz_party_sites hps,

hz_contact_points hcp,

ap_suppliers ass,

ap_supplier_sites_all assa

where 1=1 

and ass.vendor_id=assa.vendor_id

and hcp.owner_table_name='HZ_PARTY_SITES'

and hps.party_id= ass.party_id

and hcp.owner_table_id= hps.party_site_id

and hcp.contact_point_type='EMAIL'

and assa.org_id = xxxx;

--AND ass.segment1='77439'

• Contact Directory

SELECT  asu.segment1 Supp_Num

,asu.vendor_name

,hpcp.email_address

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

--AND asu.segment1='77439'

• Remittance Email id at Site Level

  SELECT

       s.vendor_name              "Vendor Name",

       s.segment1                 "Vendor Number",

       s.vendor_type_lookup_code  "Vendor Type",

       st.vendor_site_code        "Vendor Site Code",

       ou.name                    "Operating Unit",

       iepa.remit_advice_delivery_method  "Remittance Delivery Method",

       iepa.remit_advice_email            "Remittance Advice Email"

  FROM

       ap.ap_suppliers              s,

       ap.ap_supplier_sites_all     st,

       hr_operating_units           ou,

       iby.iby_external_payees_all  iepa

 WHERE

       1=1

   AND TRUNC (SYSDATE) BETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL (s.end_date_active, SYSDATE+1))

   AND s.enabled_flag = 'Y'

   AND iepa.supplier_site_id = st.vendor_site_id

   AND iepa.payee_party_id = s.party_id

   AND st.org_id = ou.organization_id

   AND st.vendor_id = s.vendor_id

  -- and s.segment1='77439'

   and st.org_id= xxx

 ORDER BY s.vendor_name, st.vendor_site_code;

• Remittance Email id at Header Level

SELECT aps.vendor_name, aps.segment1 supplier_num,iepa.remit_advice_email 

       FROM  APPS.ap_suppliers aps,

          apps.ap_supplier_sites_all apss

       ,APPS.iby_external_payees_all iepa

       WHERE  iepa.payee_party_id = aps.party_id

       and aps.vendor_id= apss.vendor_id

         AND default_payment_method_code is null

        -- AND  aps.segment1='77439'

        and apss.org_id = xxx

         and iepa.party_site_id IS null