Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Monthly Archives: December 2011

E-Mail using PL/SQL and Gmail

This is something I tried out for myself by following the steps on Havard Kristiansen’s blog. If you are thinking of doing something similar, I would suggest reading his post. The major difference in my case was that I did this on a Oracle 9.2.0.6 database running on RHEL4. The changes that this entailed are:

  1. Since RHEL4 comes with stunnel installed, so all I had to do was to create the stunnel.conf file in /etc/stunnel. The contents of the file is the same as what Havard mentioned. Once that was done, I had to start the stunnel service by using the command ‘stunnel &’.
  2. utl_encode.text_encode is not available in 9.2.0.6 hence I used UTL_ENCODE.BASE64_ENCODE instead

Apart from that I tweaked to code to support multiple recipients. Below is the code that I used:

create or replace package apex_mail_p
is
 g_smtp_host varchar2 (256) := 'localhost';
 g_smtp_port pls_integer := 1925;
 g_smtp_domain varchar2 (256) := 'gmail.com';
 g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
 -- send mail using UTL_SMTP
 procedure mail (
 p_sender in varchar2
 , p_recipient in varchar2
 , p_subject in varchar2
 , p_message in varchar2
 );
end;
/

create or replace package body apex_mail_p
is
 -- Write a MIME header
 procedure write_mime_header (
 p_conn in out nocopy utl_smtp.connection
 , p_name in varchar2
 , p_value in varchar2
 )
 is
 begin
 utl_smtp.write_data ( p_conn
 , p_name || ': ' || p_value || utl_tcp.crlf
 );
 end;
 procedure mail (
 p_sender in varchar2
 , p_recipient in varchar2
 , p_subject in varchar2
 , p_message in varchar2
 )
 is
 l_conn utl_smtp.connection;
 nls_charset varchar2(255);
 p_to varchar2(250);
 j number:=null;
 p_recipient_store varchar2(4000);
 begin
 -- get characterset
 select value
 into nls_charset
 from nls_database_parameters
 where parameter = 'NLS_CHARACTERSET';
 -- establish connection and autheticate
 l_conn := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
 utl_smtp.ehlo(l_conn, g_smtp_domain);
 utl_smtp.command(l_conn, 'auth login');
 utl_smtp.command(l_conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('youraccount@gmail.com'))));
 utl_smtp.command(l_conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('yourpassword'))));
 -- set from/recipient
 utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
 --loop through all reciepients and issue the RCPT TO command for each one
 p_recipient_store:=p_recipient;
 while nvl(length(p_recipient_store),0)>0
 loop
 select decode(instr(p_recipient_store, ','),
 0,
 length(p_recipient_store) + 1,
 instr(p_recipient_store, ','))
 into j
 from dual;
 p_to:=substr(p_recipient_store,1,j-1);
 utl_smtp.command(l_conn, 'RCPT TO: <'||p_to||'>');
 p_recipient_store:=substr(p_recipient_store,j+1);
 end loop;
 -- write mime headers
 utl_smtp.open_data (l_conn);
 write_mime_header (l_conn, 'From', p_sender);
 write_mime_header (l_conn, 'To', p_recipient);
 write_mime_header (l_conn, 'Subject', p_subject);
 write_mime_header (l_conn, 'Content-Type', 'text/plain');
 write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
 utl_smtp.write_data (l_conn, utl_tcp.crlf);
 -- write message body
 utl_smtp.write_data (l_conn, p_message);
 utl_smtp.close_data (l_conn);
 -- end connection
 utl_smtp.quit (l_conn);
 exception
 when others
 then
 begin
 utl_smtp.quit(l_conn);
 exception
 when others then
 null;
 end;
 raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);
 end;
end;
/

To send mail to multiple recipients, call the procedure with the proper parameters:

begin
 apex_mail_p.mail('Oracleappsnotes', 'firstrecipient@example.com,secondrecipient@example.com,thirdrecipient@example.com,', 'Something Interesting', 'Sending mail through Gmail');
end;

Make all responsibilities read-only for a user

Metalink Note#363298.1 provides instructions on how to make all responsibilities read-only for a user through CUSTOM.pll. The note, however, is not detailed enough and some missing information and typographical errors make things even more confusing. This post will provide step-by-step instructions on how to achieve the functionality. I have tested it on a 11.5.10 instance. As always, ensure that you have backed-up the original CUSTOM.pll before you make any changes.

1. Download the CUSTOM.pll from the server to your local machine. Ensure that all the libraries under $AU_TOP/resource/ are present in the proper path on your local machine.  Copying all files from $AU_TOP/resources/ to ORACLE_HOME/TOOLS/OPEN60/PLSQLLIB (where ORACLE_HOME is the Forms6i home directory on your local machine) is an easy way to make sure that all required libraries are present.

2. Open CUSTOM.pll in Form Builder. You will notice that the only attached library is FNDSQF.

3. You need to attach the APPCORE2 library so that the code in the CUSTOM.pll works. To do that, first click on Attached Libraries and then click on the green plus icon on the left to open the Attach Library window. Browse for APPCORE2.pll on your local machine and attach it to CUSTOM.pll. Once that is done, APPCORE2.dll will be displayed in the list of attached libraries.

4. In procedure event of the CUSTOM.pll body, comment out the following three lines

--begin
-- null;
--end event;

and add the following code

 formname varchar2(30);
 blockname varchar2(30);

 begin
 IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
 IF FND_PROFILE.VALUE('USERNAME')='EBUSINESS' THEN
 BEGIN
 COPY('Entering app_form.query_only_mode.','global.frd_debug');
 COPY('YES', 'PARAMETER.QUERY_ONLY');
 APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
 APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
 formname := NAME_IN('system.current_form');
 blockname := GET_FORM_PROPERTY(formname, FIRST_BLOCK);
 WHILE (blockname is not null) LOOP
 IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
 SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
 SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
 SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
 END IF;
 blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
 END LOOP;
 END;
 END IF;
 END IF;
 end event;

Note that I have made all responsibilities read-only for the EBUSINESS user, you can change it as per your requirement. The body of my CUSTOM.pll is attached here.

Body of CUSTOM.pll

5. Save the CUSTOM.pll and transfer it back to the server. Compile it using the following command to complete your work.

f60gen module=CUSTOM.pll userid=apps/***** output_file=CUSTOM.plx module_type=LIBRARY

Forms for all responsibilities will now become read-only for the EBUSINESS user

While for other users, they will behave normally