HOW TO SEND A MAIL FROM UTL_SMTP OR FROM BACK END

HOW TO SEND A MAIL FROM UTL_SMTP OR FROM BACK END :



create or replace FUNCTION xxx_popmd_upd_email_notif (p_po_number         VARCHAR2,
                                                      p_po_line           NUMBER,
                                                      p_po_shipment_num   NUMBER,
                                                      p_po_release_num    NUMBER,
                                                      p_po_promissed_date VARCHAR2,
                                                      p_justification     VARCHAR2)
   RETURN VARCHAR2
AS

   v_mail_conn       utl_smtp.connection;
   v_mail_host       VARCHAR2 (30);
   v_port_no         NUMBER;
   v_to_recipients   apps.alr_distribution_lists.to_recipients%TYPE;
   v_cc_recipients   apps.alr_distribution_lists.cc_recipients%TYPE;
   v_mail_subject    VARCHAR2 (3000);
   v_from            VARCHAR2 (80) := 'OracleMailerTest@xxx.com';
   crlf              VARCHAR2 (2) := CHR (13) ||CHR (10);
   v_employee_name   apps.fnd_user.user_name%TYPE;
   v_cp_date         VARCHAR2 (30);
   v_user_id         NUMBER := fnd_profile.value ('USER_ID');
   v_req_id          NUMBER := fnd_global.conc_request_id;
   l_cc              VARCHAR2 (3000);
   v_cc              VARCHAR2 (3000);
   l_to              VARCHAR2 (2000);
   l_tmp             VARCHAR2 (3000);
   n                 NUMBER;
BEGIN
   SELECT to_recipients,
          cc_recipients
     INTO v_to_recipients,
          v_cc_recipients
     FROM apps.alr_distribution_lists
    WHERE name = 'ORIGINAL_PROMISE_DATE_UPDATE';

   dbms_output.put_line ('Email Distribution List: ' || v_to_recipients);

   SELECT DISTINCT NVL (pf.full_name, fu.user_name)
     INTO v_employee_name
     FROM per_all_people_f pf,
          fnd_user fu
    WHERE fu.employee_id = pf.person_id(+)
      AND user_id = v_user_id;

   dbms_output.put_line ('Update by User Name: ' || v_employee_name);

    SELECT NVL (TO_CHAR (actual_start_date, 'DD-MON-YYYY HH24:MI:SS'), sysdate)
      INTO v_cp_date
      FROM fnd_concurrent_requests fcr
     WHERE request_id = v_req_id;

   dbms_output.put_line ('Update by date: ' || v_cp_date);

   SELECT SUBSTR (v.profile_option_value, 1, instr (v.profile_option_value, ':') - 1),
          to_number (SUBSTR (v.profile_option_value, 20, 2))
     INTO v_mail_host,
          v_port_no
     FROM fnd_profile_option_values v,
          fnd_profile_options o
    WHERE v.profile_option_id = o.profile_option_id
      AND o.profile_option_name = 'XX_CUS_TOP_EMAIL_SMTP';

   l_cc := v_to_recipients;

   LOOP
      EXIT WHEN l_cc IS NULL;

      v_mail_subject := 'Original Promise Date Update - ' || p_po_number || ' by ' || v_employee_name;
      v_mail_conn := utl_smtp.open_connection (v_mail_host, v_port_no);

      utl_smtp.helo (v_mail_conn, v_mail_host);
      utl_smtp.mail (v_mail_conn, v_from);

      n := instr (l_cc, ',');

      --IF n =0 THEN exit; end if;
      l_tmp := SUBSTR (l_cc, 1, n - 1);

      IF n = 0 THEN
         l_tmp := l_cc;
         l_cc := NULL;
      END IF;

      dbms_output.put_line ('Output1: ' || l_tmp);
      dbms_output.put_line ('Output2: ' || l_cc);

      --utl_smtp.rcpt( l_tmp );
      utl_smtp.rcpt (v_mail_conn, l_tmp);
      utl_smtp.data (v_mail_conn,  'Date: '                            || TO_CHAR (sysdate, 'Dy, DD Mon YYYY hh24:mi:ss')
                        || crlf || 'From: '                            || v_from
                        || crlf || 'Subject: '                         || v_mail_subject
                        || crlf || 'To: '                              || v_to_recipients
                        || crlf || 'Cc: '                              || v_cc_recipients
                        || crlf || 'PO Number: '                       || p_po_number
                        || crlf || 'Line Number: '                     || p_po_line
                        || crlf || 'Shipment Number: '                 || p_po_shipment_num
                        || crlf || 'Release Number: '                  || p_po_release_num
                        || crlf || 'New Original Promised Date: '      || p_po_promissed_date
                        || crlf || 'Justification: '                   || p_justification|| crlf
                        || crlf || 'Updated By    :' ||v_employee_name
                        || crlf || 'Date Updated  :' ||v_cp_date|| crlf);

      l_cc := SUBSTR (l_cc, n + 1);

      dbms_output.put_line ('Output3: ' || l_cc);

      utl_smtp.quit (v_mail_conn);
   END LOOP;

   v_cc:=v_cc_recipients;
    LOOP
      EXIT WHEN v_cc IS NULL;

      v_mail_subject := 'Original Promise Date Update - ' || p_po_number || ' by ' || v_employee_name;
      v_mail_conn := utl_smtp.open_connection (v_mail_host, v_port_no);

      utl_smtp.helo (v_mail_conn, v_mail_host);
      utl_smtp.mail (v_mail_conn, v_from);

      n := instr (v_cc, ',');

      --IF n =0 THEN exit; end if;
      l_tmp := SUBSTR (v_cc, 1, n - 1);

      IF n = 0 THEN
         l_tmp := v_cc;
         v_cc := NULL;
      END IF;

      dbms_output.put_line ('Output1: ' || l_tmp);
      dbms_output.put_line ('Output2: ' || v_cc);

      --utl_smtp.rcpt( l_tmp );
      utl_smtp.rcpt (v_mail_conn, l_tmp);
      utl_smtp.data (v_mail_conn,  'Date: '                            || TO_CHAR (sysdate, 'Dy, DD Mon YYYY hh24:mi:ss')
                        || crlf || 'From: '                            || v_from
                        || crlf || 'Subject: '                         || v_mail_subject
                        || crlf || 'To: '                              || v_to_recipients
                        || crlf || 'Cc: '                              || v_cc_recipients
                        || crlf || crlf || 'PO Number:  '              || p_po_number
                        || crlf || 'Line Number: '                     || p_po_line
                        || crlf || 'Shipment Number: '                 || p_po_shipment_num
                        || crlf || 'Release Number: '                  || p_po_release_num
                        || crlf || 'New Original Promised Date: '      || p_po_promissed_date
                        || crlf || 'Justification: '                   || p_justification|| crlf
                        || crlf || 'Updated By    :' ||v_employee_name
                        || crlf || 'Date Updated  :' ||v_cp_date|| crlf);

      v_cc := SUBSTR (v_cc, n + 1);

      dbms_output.put_line ('Output3: ' || v_cc);

      utl_smtp.quit (v_mail_conn);
   END LOOP;

   RETURN ('Notification Sent');
EXCEPTION
   WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
      raise_application_error ( - 20000, 'Unable to Send Mail', true);
   WHEN OTHERS THEN
      raise_application_error ( - 20001, 'Other Errors: '||sqlerrm||';'||SQLCODE);
END xxx_popmd_upd_email_notif;