Introduction
Oracle EBS provide the functionality to leverage Java into action. When you love java and to feel in your EBS environment.
I am sharing here a sample java class code that will will be used for Oracle EBS Concurrent program executable and perform the tasks of fetching data from database with power of JDBC and will create a csv file on application server file system.
I have used a string variable to hold string content you as payload to create file
Java Code
package
oracle.apps.fnd.cp.request;
import
java.io.File;
import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
java.io.FileWriter;
import
java.io.IOException;
import
java.io.PrintWriter;
import
java.io.StringWriter;
import
java.security.NoSuchProviderException;
import
java.sql.Connection;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.text.SimpleDateFormat;
import
java.util.Date;
import
oracle.apps.fnd.util.ParameterList;
import
oracle.jdbc.OracleCallableStatement;
public
class GeneratePaymentFile implements JavaConcurrentProgram {
String pDate;
String directory_path = "/Top Directory Path";
public GeneratePaymentFile() {
}
public void runProgram(CpContext pCpContext) {
Connection mJConn = pCpContext.getJDBCConnection();
ReqCompletion lRC = pCpContext.getReqCompletion();
OutFile lOF = pCpContext.getOutFile();
LogFile lLF = pCpContext.getLogFile();
try {
Date date1 = new Date();
String shortDate =
new
SimpleDateFormat("dd-MMM-yyyy").format(date1);
String lQuery =
"SELECT * \n" +
"from XX_PAYMENT_V \n" + " where upper(APPROVAL_STATUS) =
'APPROVED' "; // modify you sql query to pick required rows to prepare
file
lOF.writeln("+-------------------------------+");
lOF.writeln("Generating Payment File
");
lOF.writeln("+--------------------------------+");
lOF.writeln("");
lOF.writeln("Running for Date : "
+ shortDate);
lOF.writeln("");
PreparedStatement lStmt =
mJConn.prepareStatement(lQuery);
ResultSet lRs = lStmt.executeQuery();
String txtLines = "";
FileWriter fw;
int record_count = 0;
Date time1 = new Date();
String mDateTimeStr =
new
SimpleDateFormat("ddMMMyy").format(date1).toUpperCase();
mDateTimeStr =
mDateTimeStr +
new SimpleDateFormat("HHmmss").format(time1);
String file_name =
"/Request/TEST_FILE_NAME_" + mDateTimeStr;
try {
while (lRs.next()) {
if
((txtLines.length()) > 0) {
txtLines = txtLines + "\r\n";
}
txtLines =
txtLines + checkNull(lRs.getString("VALUE_DATE"))
+
"|" +
checkNull(lRs.getString("CURRENCY_CODE")) +
"|" + lRs.getString("AMOUNT") + "|"
+
checkNull(lRs.getString("DEBIT_BANK_ACCOUNT_NUM"))
+
"|" + "OUR|" +
checkNull(lRs.getString("DEBIT_BANK_ACCOUNT_NUM"))
+
"|" + "|" + lRs.getString("CHECK_ID");
try {
OracleCallableStatement eStmt = null;
String stmt =
"Update xx_Payment_tbl \n" + "Set STATUS = 'Sent',
" +
"where CHECK_ID = ?";
eStmt =
(OracleCallableStatement)mJConn.prepareCall(stmt);
eStmt.setString(1, lRs.getString("CHECK_ID"));
eStmt.execute();
eStmt.close();
} catch
(SQLException e) {
lOF.writeln("Please check the log for error details)");
e.printStackTrace();
lRC.setCompletion(ReqCompletion.ERROR, e.toString());
}
record_count++;
}
if (txtLines.length() > 0)
{
lLF.writeln("File Name : " + file_name, LogFile.STATEMENT);
fw =
new
FileWriter(new File(directory_path + file_name + ".csv"));
fw.write(txtLines);
fw.close();
} else {
lOF.writeln("No data found, file not created");
}
} catch (IOException ex) {
lStmt.close();
lOF.writeln("Please check
the log for error details)");
lOF.writeln(ex.getStackTrace().toString());
lRC.setCompletion(ReqCompletion.ERROR, ex.toString());
}
lOF.writeln("");
lOF.writeln("+--------------------------------------+");
lOF.writeln("ayment File
created");
lOF.writeln("+---------------------------------------+");
lStmt.close();
lRC.setCompletion(ReqCompletion.NORMAL,
"Request Completed Normal");
} catch (SQLException e) {
StringWriter errors = new StringWriter();
e.printStackTrace(new PrintWriter(errors));
lOF.writeln(errors.toString());
lRC.setCompletion(ReqCompletion.ERROR,
e.toString());
} finally {
pCpContext.releaseJDBCConnection();
}
}
String checkNull(String m) {
if (m == null) {
return "";
} else {
return m;
}
}
}
