Infolinks

Wednesday 6 June 2012

Sending EMail from oracle application

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

5Concurrent 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