Oracle EBS R12.1.3 - Supplier/Vendor Email Address

Many times we have a requirement to send a common email to all the suppliers who are registered in Oracle EBS .

We usually tend to take the supplier email from the supplier sites, though the email address of the supplier is available at different levels like Parties and/or contact points level in the TCA table.

The below function consolidates the email defined to a specific supplier at various levels, eliminates the repetitive email addresses and concatenates them as one string and returns it.  

So that it can be used in the sql for reports or any concurrent programs which are intended to send Remittance advices/common letters to Suppliers using XML Bursting.

The below example refers to Oracle EBS R12.1.3

CREATE OR REPLACE FUNCTION xx_get_vendor_email(p_vendor_id in ap_suppliers.vendor_id%type)

   RETURN VARCHAR2

IS

   l_supplier_emails VARCHAR2(2000);

BEGIN

   l_supplier_emails := NULL;

   FOR j IN (SELECT DISTINCT a.email_address

               FROM (SELECT LOWER(TRIM(email_address)) email_address

                       FROM ap_supplier_sites_all

                      WHERE email_address IS NOT NULL

                            AND vendor_id = p_vendor_id

                     UNION ALL

                     SELECT LOWER(TRIM(email_address)) email_address

                       FROM hz_parties hp

                      WHERE hp.email_address IS NOT NULL

                            AND hp.party_id IN

                                   (SELECT pv.party_id

                                      FROM ap_suppliers pv

                                     WHERE pv.vendor_id = p_vendor_id)

                     UNION ALL

                     SELECT LOWER(TRIM(email_address))

                       FROM hz_contact_points

                      WHERE     email_address IS NOT NULL

                            AND owner_table_name = 'HZ_PARTIES'

                            AND owner_table_id = p_vendor_id

                     UNION ALL

                     SELECT LOWER(TRIM(email_address))

                       FROM hz_contact_points

                      WHERE email_address IS NOT NULL

                            AND owner_table_name = 'HZ_PARTY_SITES'

                            AND owner_table_id IN

                                   (SELECT vendor_site_id

                                      FROM ap_supplier_sites_all

                                     WHERE vendor_id = p_vendor_id)) a)

   LOOP

      SELECT SUBSTR(

                j.email_address

                || DECODE(l_supplier_emails, NULL, NULL, ',' || l_supplier_emails),

                1,

                2000)

        INTO l_supplier_emails

        FROM DUAL;

   END LOOP;

   RETURN l_supplier_emails;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN SQLERRM;

END xx_get_vendor_email;

/

Once the function is created the usage can be as below

select xx_get_vendor_email (vendor_id) from ap_suppliers;