Sending EMail from oracle application
Sending EMail from oracle application
Scenario: After ran a RFQ Report, I have to send the output of a RFQ report to the suppliers mail id automatically.
I have used the below step to achieve this.
Technology: Unix
Shell Script is used as the base technology in the design of the
global program. A shell script which would send email notifications to
email ids is developed first. A Concurrent Program then wraps the shell
script, acting as the user interface for the same. This concurrent
program can be called from Forms, Reports, PL/SQL Packages etc with
appropriate parameters to send email notifications
System Requirements:
The following is the system requirements for the global program.
Operating System: HP–UX. Any other flavour of Unix which supports mailx command.
Oracle Applications Version: R12 / 11i
Setups:
The complete setup can be summarized in three steps:
1. Placing the script in the bin directory,
2. Creating Value Set
3. Creating Apps Executable & Concurrent program
Steps in detail:
1. Shell
Script: Place the shell script mentioned in the “appropriate top”/bin
folder in the Unix Box. (For Example, $CUSTOM_TOP/bin, Unix code given
below in this post).
2. Create Value Set: Navigate to the following path
Responsibility: System Administrator
Navigation: Application à Validation à Set
Create a value with the following information:
Format Type: Char
Maximum Size: 225
Validation Type: None.
3.Create Apps Executable: Create an Oracle Apps Executable (Responsibility: System Administrator Navigation: Concurrent à Program à Executable) for the shell script
4.
Create Concurrent Program: Create an Oracle Apps Concurrent Program
(Responsibility: System Administrator Navigation: Concurrent à Program à Define) linking it to the executable created in Step 3
5. Concurrent
Program Parameter: Create the following parameters to the concurrent
program and attach the valueset created in Step 2 :
S.No
|
Parameter
|
Description
|
1
|
TO Mail Id
|
List of comma separated TO email ids
|
2
|
CC Mail Id
|
List of comma separated CC email ids
|
3
|
Subject
|
Subject of the mail
|
4
|
Body-1
|
Max 225 Char - If Body has more than 225 characters, then the remaining characters has to be populated in Body – 2
|
5
|
Body-2
|
Max
225 Char - If Body has more than 225 characters, then the first 225
characters should be populated in Body-1 and remaining in Body – 2.
|
5
|
File Path
|
File path of the file to be attached (Can have environment variables like $APPLCSF/$APPLOUT)
|
6
|
File Name
|
Name of the file to be attached
|
7
|
File Display Name
|
Display name of the file in the email.
|
The setup is now complete and the concurrent program can now be used to send email with attachments.
Mail Text Formating: The following points needs to be taken care while formatting the body of the email.
(i) To
have a “New Line” character (“Enter” Key Character in the body ), four
backward slash with the character “n” should be used, i.e. \\\\n
(ii) To get additional formatting like Bold, Underline etc, the
body of the mail can be sent with HTML tags and the Email Client would
display it appropriately (If HTML is supported by the email client)
Sample Code:
Attached is a sample code making a call to the Concurrent Program to send email with attachments.
The
following sample code would send email notification with the report
generated in pdf format, as attachment from a RDF report.
Code in “After Report Trigger”
function AfterReport return boolean is
l_mail_reqid NUMBER;
l_to_mail_id VARCHAR2(200);
l_cc_mail_id VARCHAR2(200);
l_subject VARCHAR2(225);
l_body VARCHAR2(225);
l_file_path VARCHAR2(100);
l_file_name VARCHAR2(100);
l_file_disp VARCHAR2(100);
BEGIN
l_to_mail_id := 'anto.natesh@abc.com; -- To Email Id
l_cc_mail_id := 'anto.natesh@gmail.com'; -- CC Mail
l_subject := 'XXX Process is completed at '||SYSDATE;
l_body := 'Hi,\\\\nThe process XXX has completed and '|| 'attached is the Summary Report\\\\nThanks,\\\\nAnto';
l_file_path := '$APPLCSF/$APPLOUT';
l_file_name := 'o'||:p_conc_request_id||'.out';
l_file_disp := 'RFQ_Report.pdf';
srw.message('1', 'id:'||:p_conc_request_id);
-- Submit the request
l_mail_reqid := FND_REQUEST.SUBMIT_REQUEST( 'XXCUST',
'SEND_MAIL',
NULL, -- Description (Optional)
NULL, -- Start Time (Optional)
FALSE, -- TRUE if this is to be child request
l_to_mail_id, -- Comma separated TO Email Ids
l_cc_mail_id, -- Comma separated CC Email Ids
l_subject, -- Subject of the mail
l_body, -- Body of the mail
NULL, -- Second part of body, if more than 225 Characters
l_file_path, -- File path
l_file_name, -- File to be attached
l_file_disp, -- Display name of the file
CHR(0),
'','','','','','','','', '', '', '','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','');
IF l_mail_reqid <> 0 THEN
COMMIT;
ELSE
srw.message('1005','Concurrent Request Submission for sending email failed!!!');
END IF;
RETURN TRUE;
END;
Limitations: Due to restrictions imposed by Oracle Apps and Unix, there are few limitations to this approach.
a. Oracle Apps Limitation:
Oracle Apps has a limitation of the length of concurrent program
parameter which cannot exceed 240 character. But since the body might
exceed this length, a workaround was needed to send the body of the
email as it can exceed 240 characters
The
workaround is: Two “Body” parameters have been created: Body-1 and
Body-2. If the length of Body-1 exceeds 225 character, it should be put
in Body-2. The gobal notification program would ensure that the two
body parts are concatenated and sent in one email
It is presumed, that in general, the body of an automated email would not exceed more than 450 characters.
b. Unix Limitation:
Email to only those ids can be sent as per the configuration of the
Unix Mailx utility. If the Unix box is configured to not to send email
to ids such as hotmail and yahoo, then email cannot be sent to those
ids.
Unix Code:-
Below
is the shell script code. File Name XXCUST_SEND_MAIL (without
extension). copy paste the code into a file and save it as
XXCUST_SEND_MAIL.
Move to $XXCUST_TOP/bin
## Parameter passed to Oracle Apps is copied into a local variable
P_PARAM=$1
echo "Parameters passed by Apps to the shell script"
echo $P_PARAM
##The parameters as supplied by User, after removing the default Oracle Apps Parameters.
P_USER_PARAM=`echo $P_PARAM|cut -d" " -f 9-`
echo "\nParameters supplied by the user"
echo $P_USER_PARAM
##Extracting indivdual parameters from the user supplied params, using " as the delimiter
P_TO_ID=`echo $P_USER_PARAM|cut -d'"' -f 2`
P_CC_ID=`echo $P_USER_PARAM|cut -d'"' -f 4`
P_SUBJECT=`echo $P_USER_PARAM|cut -d'"' -f 6`
P_BODY_1=`echo $P_USER_PARAM|cut -d'"' -f 8`
P_BODY_2=`echo $P_USER_PARAM|cut -d'"' -f 10`
P_FILE_PATH=`echo $P_USER_PARAM|cut -d'"' -f 12`
P_FILE_NAME=`echo $P_USER_PARAM|cut -d'"' -f 14`
P_FILE_DISP=`echo $P_USER_PARAM|cut -d'"' -f 16`
##Echoing user supplied params
echo "\nindivudal User Supplied Parameters"
echo "TO Mail Id:"$P_TO_ID
echo "CC Email Id:"$P_CC_ID
echo "Subject:"$P_SUBJECT
echo "Body1:"$P_BODY_1
echo "Body2:"$P_BODY_2
echo "File Attachment Path:"$P_FILE_PATH
echo "File Name:"$P_FILE_NAME
echo "File Display Name:"$P_FILE_DISP
P_BODY=$P_BODY_1$P_BODY_2
echo "\n Complete Body"
echo $P_BODY
## IF there is no file attachment
if [ -z "$P_FILE_PATH" ]
then
echo "\nFile attachment not specified"
## IF there is no CC ID
if [ -z "$P_CC_ID" ]
then
echo "\nCC Id is NULL"
(echo $P_BODY) | mailx -s "${P_SUBJECT}" $P_TO_ID
else
echo "\nCC Id is NOT NULL"
(echo $P_BODY) | mailx -s "${P_SUBJECT}" -c $P_CC_ID $P_TO_ID
fi
fi
## If file attachment is present
if [ -n "$P_FILE_PATH" ]
then
echo "\nFile attachment is specified"
P_FILE_PATH=`echo $P_FILE_PATH|sed 's/\/$//'`
echo "\nFile Path after removing trailing /"
echo $P_FILE_PATH
P_FILE_PATH=`eval echo $P_FILE_PATH`
echo "\nFile Path after evaluating any Environment Variables"
echo $P_FILE_PATH
## cd command is given only to check that the file path is a valid file path.
## If it is invalid, shell script will error out.
cd $P_FILE_PATH
P_FILE_ATTACH=$P_FILE_PATH/$P_FILE_NAME
echo "\nComplete file path with attachment name"
echo $P_FILE_ATTACH
## IF there is no CC ID
if [ -z "$P_CC_ID" ]
then
echo "\nCC Id is NULL"
((echo $P_BODY);uuencode "${P_FILE_ATTACH}" "${P_FILE_DISP}") | mailx -s "${P_SUBJECT}" $P_TO_ID
else
echo "\nCC Id is NOT NULL"
((echo $P_BODY);uuencode "${P_FILE_ATTACH}" "${P_FILE_DISP}") | mailx -s "${P_SUBJECT}" -c $P_CC_ID $P_TO_ID
fi
fi
if [ $? -eq 1 ]
then
echo 'ERROR while sending email. Please check log file'
exit 1
fi
###### End of Shell Script ######
No comments:
Post a Comment