2 Replies Latest reply: Sep 19, 2013 4:56 AM by pradeep215 RSS

    Send EMAIL from PLSQL Package .

    pradeep215

      PROCEDURE Send_Email_To_User(p_Employee_Number IN VARCHAR2,
                                     p_Employee_Name   IN VARCHAR2,
                                     p_DOB             IN DATE,
                                     p_Emp_Email       IN VARCHAR2,
                                     p_Cc_Email        IN VARCHAR2,
                                     p_Bcc_Email       IN VARCHAR2,
                                     p_Format          IN VARCHAR2 DEFAULT 'NORMAL')
       
         IS
       
          l_Old_User_Msg  VARCHAR2(4000);
          Senderaddress   VARCHAR2(100) := 'HRHelpdesk@mashreqbank.com'; --'MashreqSSHR@Mindscapeit.com';
          Receiveraddress VARCHAR2(300);
          Cc_Email        VARCHAR2(1000);
          Cc              VARCHAR2(100);
          i               NUMBER := 1;
          j               NUMBER := 1;
          Emailserver     VARCHAR2(30) := 'mindscapeit.com';
          Port            NUMBER := 25;
          p_Day_Text      VARCHAR2(100) := TO_CHAR(p_DOB, 'Mon DD');
          Conn            Utl_Smtp.Connection;
          Crlf            VARCHAR2(2) := Chr(13) || Chr(10);
          l_Subject       VARCHAR2(4000);
          Mesg            VARCHAR2(4000);
          Mesg_Para       VARCHAR2(200) := '<html><body><p></p></body></html>';
          Mesg_Body       VARCHAR2(4000);
          Mesg_Body1      VARCHAR2(4000) := '

      <html>
      <BODY>
      <style TYPE="text/css">
        p {font-size:31.0pt; font-family: Brush Script MT;}
        q {font-size:3.0pt; font-family: Verdana;}
      </style>
      <TABLE CELLPADDING=8 CELLSPACING=0 WIDTH=80% bgColor="#F6882E" >
      <!-- TABLE CELLPADDING=8 CELLSPACING=0 WIDTH=80% HEIGHT=200 align="center" BACKGROUND=http://mashreqintranet/Images/OUTPUTo_tcm11-9133.gif -->
      <TD>
      <font family="Brush Script MT" size="3" color="white">
      <p>   Dear ' || p_Employee_Name || ', </p>
      <q>   </q>
      <p>    Wish you a very Happy Birthday!!! </p>
      <q>   </q>
      <p>     Regards, </p>
      <p>    HR       </p>

      </font>
      </TD>
      </TABLE>

      </BODY>
      </html>
      ';
       
          /*'
         
          <html>
          <BODY BACKGROUND=http://pkgof.mashreqbank.corp.network:8000/OA_HTML/cabo/images/swan/headerBg.jpg>
          <style TYPE="text/css">
           p {font-family: Vladimir Script;}
           body { margin : 0px; overflow : auto; padding : 0px;}
           table { background-color : transparent; color : #000; height : 400px; width : 60%;}
           td { border : solid 1px #000; height : 25%; vertical-align : top;}
           img { position : absolute; height : 400px; width : 60%; z-index : -1;}
          </style>
          <!-- TABLE CELLPADDING=8 CELLSPACING=0 WIDTH=80% -->
          <TABLE CELLPADDING=8 CELLSPACING=0 WIDTH=200% align="center" >
          <TD>
          <font family="Vladimir Script" size="8" color="red">
          <p>   Dear ' || p_employee_name || ', </p>
          <p>    Wish you a very Happy Birthday!!! </p>
          <p>  </p>
          <br>
          <p>     Regards, </p>
          <p>    HR       </p>
         
          </font>
          </TD>
          </TABLE>
          <img src="http://pkgof.mashreqbank.corp.network:8000/OA_HTML/cabo/images/swan/headerBg.jpg" alt="background image" />
         
         
          </BODY>
          </html>
          ';
          */
       
          --bgColor="#A00000"
          -- <TABLE CELLPADDING=8 CELLSPACING=0 WIDTH=80% align="center" BACKGROUND=C:\Users\suhaiba\Pictures\SR\test.jpg >
          --<TD BACKGROUND="C:\Users\suhaiba\Pictures\SR\test.jpg">
          --</TD>
          --</TABLE>
       
          /*
          <html>
          <BODY bgColor="#375F90">
          <style TYPE="text/css">
            p {font-family: Vladimir Script;}
          </style>
          <div align ="center">
          <b>
          <p style="font-size:large;"><span style="color:orange"><font size="7">HAPPY BIRTHDAY!!!</font></span></p>
          </b>
          </div>
         
          <font family="Vladimir Script" size="6" color="white">
          <b><p>    Dear ' || p_employee_name || ',</p>
          <p>  </p>
          <br>
          <p>    Wish you a very Happy Birthday! </p>
          <p>  </p>
         
          <br>
          <p>     Regards, </p>
          <p>    HR       </p>
          </b>
         
          </font>
          </BODY>
          </html>
          */
          Mesg_Body2 VARCHAR2(4000) := '

      <html>
      <BODY>
      <style TYPE="text/css">
        p {font-size:31.0pt; font-family: Brush Script MT;}
        q {font-size:3.0pt; font-family: Verdana;}
      </style>
      <TABLE CELLPADDING=8 CELLSPACING=0 WIDTH=80% bgColor="#F6882E" >
      <!-- TABLE CELLPADDING=8 CELLSPACING=0 WIDTH=80% HEIGHT=200 align="center" BACKGROUND=http://mashreqintranet/Images/OUTPUTo_tcm11-9133.gif -->
      <TD>
      <font family="Brush Script MT" size="3" color="white">
      <p>   Dear ' || p_Employee_Name || ', </p>
      <q>   </q>
      <p>    Wishing you in advance a very Happy Birthday!!! </p>
      <p>    ' || p_Day_Text || ' </p>
      <q>   </q>
      <p>     Regards, </p>
      <p>    HR       </p>

      </font>
      </TD>
      </TABLE>

      </BODY>
      </html>
      ';
          /*'
         
          <html>
          <BODY>
          <style TYPE="text/css">
            p {font-family: Kunstler Script;}
          </style>
          <TABLE CELLPADDING=8 CELLSPACING=0 WIDTH=80% bgColor="#614A7B">
          <TD>
          <font family="Kunstler Script" size="7" color="#CCC0D9">
          <b><p>   Dear ' || p_Employee_Name || ', </p>
          <p>    Wishing you in advance a very Happy Birthday!!! </p>
          <p>  </p>
          <br>
          <p>     Regards, </p>
          <p>    HR       </p>
          </b>
          </font>
          </TD>
          </TABLE>
         
          </BODY>
          </html>
          ';
            */
          Mesg_Link VARCHAR2(4000) := '

      <html>
      <BODY>
      <style TYPE="text/css">
        p {font-family: Vladimir Script;}
      </style>
      <font family="Vladimir Script" size="3">
      <div align ="center">
      <p> <a href="http://pkgof.mashreqbank.corp.network:8000/OA_HTML/AppsLocalOUTPUTin.jsp"><b>Click here to OUTPUTin</b></a> </p>
      </div>
      </font>
      </BODY>
      </html>
      ';
       
          Mesg_End VARCHAR2(4000) := '

      <html>
      <BODY>
      <style TYPE="text/css">
        p {font-family: Vladimir Script;}
      </style>
      <font family="Vladimir Script" size="3">
      <p> For help in accessing the different functions of the SSHR application, please visit the SSHR section on the HR intranet. The section has detailed user manuals and FAQs which will guide you in making the best possible use of this interactive system. Please click here to access the link:  </p>
      </font>
      </BODY>
      </html>
      ';
       
          Mesg_Link_End VARCHAR2(4000) := '

      <html>
      <BODY>
      <style TYPE="text/css">
        p {font-family: Vladimir Script;}
      </style>
      <font family="Vladimir Script" size="3">
      <p> <a href="http://mashreqintranet/human-resources/sshr/overview.asp">SSHR User Manuals and FAQs</a> </p>
      <p> <p> <p> In case of any queries/issues, please OUTPUT a call with the HR helpdesk. </p> </p> </p>
      </font>
      </BODY>
      </html>
      ';
       
        BEGIN
       
          Receiveraddress := p_Emp_Email;
          Cc_Email        := p_Cc_Email;
       
          l_Subject := 'HAPPY BIRTHDAY !';
       
          Conn := Utl_Smtp.Open_Connection(Emailserver, Port);
       
          IF (p_Format = 'NORMAL') THEN
            Mesg_Body := Mesg_Body1;
          ELSE
            Mesg_Body := Mesg_Body2;
          END IF;
       
          Utl_Smtp.Helo(Conn, Emailserver);
          Utl_Smtp.Mail(Conn, Senderaddress);
          Utl_Smtp.Rcpt(Conn, Receiveraddress);
       
          Dbms_Output.Put_Line(Substr(Cc_Email, i, Instr(Cc_Email, ',', i) - j));
       
          i := 1;
          j := 1;
          WHILE Instr(Cc_Email, ',', i) != 0 LOOP
            Cc := Substr(Cc_Email, i, Instr(Cc_Email, ',', i) - j);
            i  := Instr(Cc_Email, ',', i) + 1;
            j  := i;
            Dbms_Output.Put_Line(Cc); --Call RCPT routine here
            Utl_Smtp.Rcpt(Conn, Cc);
          END LOOP;
          Utl_Smtp.Rcpt(Conn, p_Bcc_Email);
          Cc := Substr(Cc_Email, i, Length(Cc_Email));
          Dbms_Output.Put_Line(Cc); --Call RCPT routine here
          --UTL_SMTP.rcpt(conn, cc);
       
          Mesg := 'Date: ' || To_Char(SYSDATE - 4 / 24, 'dd Mon yy hh24:mi:ss') || Crlf ||
                  'From: HR Helpdesk' || ' <' || Senderaddress || '>' || Crlf ||
                  'Subject: ' || l_Subject || Crlf || 'To: ' || Receiveraddress || Crlf ||
                  'CC: ' || p_Cc_Email /*|| Crlf || 'Bcc: ' || p_Bcc_Email */
                  || Crlf || '' || Crlf || Mesg_Body;
          -- || '' || mesg_para || crlf || '' || mesg_para || crlf || '' ||
          --mesg_para || crlf || '' || mesg_para || crlf || l_old_user_msg || '' || crlf ||
          --mesg_para || mesg_link || '' || crlf || mesg_para || mesg_end ||
          --mesg_link_end;
       
          --
       
          Utl_Smtp.Data(Conn,
                        'MIME-Version: 1.0' || Chr(13) || Chr(10) ||
                        'Content-type: text/html' || Chr(13) || Chr(10) || Mesg);
       
          Utl_Smtp.Quit(Conn);
       
          Fnd_File.New_Line(Fnd_File.OUTPUT, 1);
          Fnd_File.Put_Line(Fnd_File.OUTPUT,
                            'Emailed to employee' || p_Employee_Number);
       
        END Send_Email_To_User;

       
      END Mb_Bday_Pkg;

        • 1. Re: Send EMAIL from PLSQL Package .
          pradeep215

          below patch numbers which are part of India HRMS Consolidation Patch set. These two patches need to be applied to the new instance.

           

          6390830

           

          12730429

          • 2. Re: Send EMAIL from PLSQL Package .
            pradeep215

            know password

             

            plsql

            CREATE OR REPLACE PACKAGE get_pwd

            AS

               FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

                  RETURN VARCHAR2;

            END get_pwd;

            /

            --Package Body

            CREATE OR REPLACE PACKAGE BODY get_pwd

            AS

               FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

                  RETURN VARCHAR2

               AS

                  LANGUAGE JAVA

                  NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

            END get_pwd;

            /

            --Query to execute

            SELECT usr.user_name,

                   get_pwd.decrypt

                      ((SELECT (SELECT get_pwd.decrypt

                                          (fnd_web_sec.get_guest_username_pwd,

                                           usertable.encrypted_foundation_password

                                          )

                                  FROM DUAL) AS apps_password

                          FROM fnd_user usertable

                         WHERE usertable.user_name =

                                  (SELECT SUBSTR

                                              (fnd_web_sec.get_guest_username_pwd,

                                               1,

                                                 INSTR

                                                      (fnd_web_sec.get_guest_username_pwd,

                                                       '/'

                                                      )

                                               - 1

                                              )

                                     FROM DUAL)),

                       usr.encrypted_user_password

                      ) PASSWORD

              FROM fnd_user usr

            WHERE usr.user_name = '&USER_NAME';