Zafar

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

As an Oracle developer, many times we want to quickly check the code of a given function/view/package or a package body to see the current code in the database may be as an output of a given SQL. Take example of an object AP_INVOICES_PKG where we want to check the output  SELECT     'select dbms_metadata.get_ddl('''        || object_type        || ''','''        || object_name        || ''') from dual;'   FROM all_objects   WHERE object_name = 'AP_INVOICES_PKG'        AND object_type NOT IN ( 'PACKAGE BODY' ) ; The above SQL will give an output as below select dbms_metadata.get_ddl('PACKAGE','AP_INVOICES_PKG') from dual; And when we run this SQL , we will get an output which is nothing but the code for package spec and package body for ap_invoices_pkg.