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”

Click here for code: http://retoscripts.blogspot.com/2009/09/sending-email-after-report-code.html

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

Click here for code: http://retoscripts.blogspot.com/2009/09/sending-email-unix-code.html