Friday, January 11, 2019

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

No comments:

Post a Comment

Clear BNE Cache for WebADI Changes

It Sometime happens that WebAdi Changes doesn't reflect once migrated in controlled instances. Here are the quick steps(Generally perfor...