This discussion is archived
2 Replies Latest reply: Sep 19, 2013 2:56 AM by pradeep215 RSS

Send EMAIL from PLSQL Package .

pradeep215 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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';