Modification of Oracle Java mailer

The process explains how to check Incoming Emails Attachmenbts coming to the IMAP account set for Standalone Oracle Workflow. Download the Attachments (File) in the UNIX Directory. The email will be coming with Specific Subject so it will be recognized that it’s the same mail that must be read. The file had Multi Tab Excel and was needed to be converted into Single linear CSV having one record per order and containing all the Orders. This CSV was stored in Database as CLOB Object along with Details of File.

Important points to be Considered
· The below process is implemented for Oracle Standalone Workflow on Unix Box.
· The Emails come into the IMAP account set for Workflow Notification Mailer to receive e-mail notification responses. The Email can be Response to the Notification send earlier or it can be just send by the User.
· Incase the email is not Response to the Notification an Unsolicited mail message will be sent to the Sender. To avoid this one needs to make sure that the parameter “Send Warning for Unsolicited E-mail” is to ‘No’ in IASCONSOLE> Workflow > Service Components>Notification Mailer>Email Servers.
· The Workflow Process is scheduled, the Workflow process should be active all the time to continuously process the Incoming mails into the IMAP Account. Incase of any error, restart the workflow process.
· Once the Workflow is running the Java Listener process must be started and it runs continuously at the Background. Any errors in Java program will be logged on to the Listener Process Screen.
· The Jar files such as jxl.jar required for the Java program must be present in Unix Box. If not put the file into some custom Directory and include the path into the Listener File.
· The file is stored into the Database as CLOB object using DBMS_LOB Package.Make sure that the utl_file_path has SQL directory created and the value for this entered in the UTL_FILE parameter of init.ora file.

Process Approach

Fig .1 Flow Diagram for Conversion Process.


Following are the Components of the Process

Workflow Process: Workflow is designed to call the External Java Function and then it will wait for Specific Time. Once the Timeout Occurs it will again give a call to the Java Function and this process will run continuously.

Java Listener Process: Once started the Listener process will listen to the Call from Workflow process for any External Java Function and execute the Java Function. This process runs continuously at the Background.

Java Program: It has two different Classes to perform two different Functions
1. DownattcLaunch: This class extends WFFunctionAPI and executes the second class. The WFFunctionAPI Java class is the abstract class from which the Java procedures for all external Java function activities are derived.
2. MsgShowEml: This is public class that uses javax.mail for Reading the Attachment and saving it to UNIX folder. It uses the jxl.jar Package for converting the Multi Tab Excel file to Single Linear CSV file. It then calls the PLSQL procedure using CallableStatement class to insert the file to Database.

PLSQL procedure: This uses DBMS_LOB package to insert File as CLOB object into the Database.




Workflow Process

Fig.2 Workflow Process

Once Launched the Workflow will move to Download Attachment Node and Call the Java Function. The Function type for this node is set to ‘External Java’ and the Function Name will be the Name of Class that extends WFFunctionAPI. The Result type set was Boolean as the Java function returns True if it executes successfully and returns false if there are any Errors.

Fig.3 Properties of Node


Workflows will then move to the Wait node and wait till the specified Time. Once the Timeout Occurs the Workflow will again go to Download Attachment Node. This process will continue.

To Launch the Workflow process go the Workflow Monitor, select the Launch Process option. Then select the Workflow and enter an unique Item key and click on Start process button. The Workflow will be launched at it will go to the First node.


Java Listener Process
Java listener Process is nothing but Java Function Activity Agent that dequeues the messages related to external Java activities from the 'Outbound' queue for external function processing and calls the appropriate Java functions.

Following steps need to be done for running the Listener
1. The Java class files (e.g. DownattcLaunch.class) must be placed in some directory on Unix Box, it can be the custom directory also.
2. On UNIX platform the File wfjvlsnr.csh must be modified to include the Path of directory where the custom Java classes are placed. This file is available in the Oracle Admin directory. Incase the Jar files such as jxl.jar used in the Java Function are not available in Unix Box you need to place the files in some directory and include the path of the files also in the wfjvlsnr.csh.
3. You can use the following command to run the wfjvlsnr.csh script on UNIX

wfjvlsnr.csh /@ []


Java Program

DownattcLaunch.class: It’s the main program that uses oracle.apps.fnd.wf.* Package. The path of this file must be included in the wfjvlsnr.csh. This class extends WFFunctionAPI and uses the method execute to call the function msg from MsgShowEml.class. It logs into the IMAP Account using the Details of IMAP account passed as String to the execute method. Following is the sample code used

import java.io.*;
import oracle.apps.fnd.common.*;
import oracle.apps.fnd.wf.engine.*;
import oracle.apps.fnd.wf.*;
import oracle.apps.fnd.*;

public class DownattcLaunch extends WFFunctionAPI {

//The execute function called invoked by the Java Function Agent
public boolean execute(WFContext pWCtx) {
try{
//Details of Imap account login
String[] a = {"-T","imap","-H","mail.comp.com","-U","User Name"," P","Password","-f","INBOX","-S"};

//Calls the Other Function
MsgShowEml msg = new MsgShowEml();
msg.execute(a);
loadItemAttributes(pWCtx);
resultOut = "T";

//Returns True
return true;
}
catch (Exception e) {
System.out.println("Exception in OuterVLAN : ");
System.out.println("The message is : "+e.getMessage());
e.printStackTrace();

//Returns error to Workflow
ErrorStack es = pWCtx.getWFErrorStack();
es.addMessage("WF", "WF_FN_ERROR");
es.addToken("MODULE", this.getClass().getName());
es.addToken("ITEMTYPE", itemType);
es.addToken("ITEMKEY", itemKey);
es.addToken("ACTID", actID.toString());
es.addToken("FUNCMODE", funcMode);
es.addToken("ERRMESSAGE", e.getMessage());
return false;
}} }

MsgShowEml.class:
Its public class that uses javax.mail Package. Following things are performed
By the function
Connects the Database using the conn Object

Class.forName ("oracle.jdbc.driver.OracleDriver");
Properties prop2 = new Properties ();
conn = DriverManager.getConnection("jdbc:oracle:thin:@IP:Port:SID ","owf_mgr","owf_mgr");

Logs into Inbox and Checks for a new mail
Folder folder = store.getDefaultFolder();
if (folder == null) {

System.exit(1);
}

if (mbox == null)
mbox = "INBOX";
folder = folder.getFolder(mbox);
if (folder == null) {

System.exit(1);
}

To make sure that only the new mails, i.e. the mails not read in previous run (before 5mins) are processed by the Program, a date is
set 5mins before SYSDATE and then compared with the Message received date
java.util.Date dtyesterday =
new java.util.Date(System.currentTimeMillis() - 1000 * 60 * 5);

if (p instanceof Message)
{
m = (Message)p;
subject = m.getSubject();
dtMessage = m.getReceivedDate();

if (dtyesterday.before(dtMessage))
{
pr("yesterday:" + dtyesterday.toString());
pr("received:" + dtMessage.toString());
saveAttachment(p, subject);
}
}

Logs into Inbox gets the Attachment and Saves to Unix Directory. If the Message has attachment it will be of type "multipart/*" and also to make sure that only the Mails with Specified subject are read a check is made before saving the Attachment

if (subject != null &&("Confirmation Report".equals(subject.trim ())))
{
if (saveAttachments && level != 0 &&
!p.isMimeType("multipart/*"))
{ String disp = p.getDisposition();
System.out.println("Inside Attachment");
// many mailers don't include a Content-Disposition
if (disp == null
disp.equalsIgnoreCase(Part.ATTACHMENT)) {
if (filename == null) {
filename = "Attachment" + attnum++;
pr("No attachment found ");
} else {

pr("Saving attachment to file " + filename);
pr("*** Subject is ** " + subject);

try
{
File f = new File("/development/u01 /MYDIR/"+filename);
OutputStream os =
new BufferedOutputStream(new FileOutputStream(f));
InputStream is = p.getInputStream();
int c;
while ((c = is.read()) != -1)
os.write(c);
os.close();

}
catch (IOException ex)

{
ex.printStackTrace();
//pr("Failed to save attachment: " + ex);
}

Convert the Multi Tab Excel file to Single CSV file and save it to the Unix Directory. We need to Open a new File in Unix Directory ( .csv ), get each sheet from the Excel file and read the columns and write it to the .csv file.
try {
//File to store data in form of CSV
File f = new File("/development/u01/MYDIR/"+fname+".csv");

OutputStream os = (OutputStream)new FileOutputStream(f);
String encoding = "UTF8";
OutputStreamWriter osw = new OutputStreamWriter(os, encoding);
BufferedWriter bw = new BufferedWriter(osw);

WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));
Workbook w = Workbook.getWorkbook(new File("/development/u01/MYDIR/"+file1),ws);

// Gets the sheets from workbook
for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) { Sheet s = w.getSheet(sheet); bw.write(s.getName()); bw.write(','); Cell[] row = null; // Gets the cells from sheet for (int i = 0 ; i < 1 ; i++) { row = s.getRow(i); if (row.length > 0)
{
bw.write(row[0].getContents());
for (int j = 1; j < row.length; j++)
{
bw.write(',');
bw.write(row[j].getContents());
} }
bw.newLine(); }}
bw.flush();
bw.close();
}

Call the PLSQL procedure to Insert the File from Unix Directory to the Database as CLOB object. PLSQL procedure can be called using an Object of CallableStatement class.
Try
{
CallableStatement pstmt = conn.prepareCall("{call test_pkg.test_proc (?,?,?)}");

//Set the Input and Output Parameters
pstmt.registerOutParameter(1, Types.VARCHAR);
pstmt.registerOutParameter(2, Types.VARCHAR);
pstmt.setString(3, File);
pstmt.executeUpdate();

String o_errmsg = pstmt.getString(1);
String o_errcode = pstmt.getString(2);
System.out.print("Error Message"+o_errmsg);
pstmt.close();
conn.close();
}


PLSQL Procedure
The file is stored into the Database as CLOB object. The Table must be created having one of the Columns as CLOB object type.
-- initialize directory (the name of the directory you created in the database)
l_output_directory := 'MYDIR';
p_out_chr_code := '0';

--Get the File Id
BEGIN
SELECT TEST_s.nextval
INTO l_chr_file_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
p_out_chr_msg :='Error getting the file id: 'SQLERRM;
RAISE e_exit;
END;

--
BEGIN
--Insert the file into Database
INSERT INTO TEST_SUP_FILE_DET_T
( vsf_file_id
, vsf_file_name
, vsf_document
, vsf_created_by
, vsf_last_updated_by
, vsf_last_update_date)
VALUES ( l_chr_file_id
, p_in_file_name
, empty_clob()
,'WF'
,'WF'
,g_dte_sysdate)
--Intialise the CLOB Object
RETURNING vsf_document INTO l_clob;
EXCEPTION
WHEN OTHERS
THEN
p_out_chr_msg :='Error inserting file into Database: 'SQLERRM;
RAISE e_exit;
END;

l_bfile := BFILENAME( l_output_directory
, p_in_file_name);

--Open the file stored in Unix Directory
DBMS_LOB.fileopen(l_bfile);

--Load the file into the Table
DBMS_LOB.loadfromfile( l_clob
, l_bfile
, DBMS_LOB.getlength(l_bfile)
);

DBMS_LOB.fileclose(l_bfile);