Oracle EBS - Java - Creating csv File from Database for Oracle Apps Java Concurrent Program

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;

        }

 

    }

 

}

 Thank for reaching to my blog. 

REST API Client (Oracle Apps Java Concurrent Program)

Sample java REST API client class for Oracle EBS Concurrent program

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 JavaConcRestAPIClient implements JavaConcurrentProgram {

    String pDate;

 

    public JavaConcRestAPIClient() {

    }

 

    public void runProgram(CpContext pCpContext)  {

        // get the JDBC connection object 

        Connection mJConn = pCpContext.getJDBCConnection();

 

        // get parameter list object from CpContext 

        ParameterList lPara = pCpContext.getParameterList();

 

        // get ReqCompletion object from CpContext 

        ReqCompletion lRC = pCpContext.getReqCompletion();

 

        // get OutFile object from CpContext 

        OutFile lOF = pCpContext.getOutFile();

 

        // get LogFile object from CpContext 

        LogFile lLF = pCpContext.getLogFile();

 

        // value and use it as the application short name in the query 

        while (lPara.hasMoreElements()) {

            NameValueType aNVT = lPara.nextParameter();

            if (aNVT.getName().equals("pdate"))

                pDate = aNVT.getValue();

        }

      lOF.writeln("+--------------------------------------+");

      lOF.writeln("My Concurrent Program REST API Client");

      lOF.writeln("+---------------------------------------+");

      lOF.writeln("");

 

      try {

         

      lOF.writeln("+---------------------------------------+");

      lOF.writeln("Calling API ");

      lOF.writeln("+----------------------------------------+");

      lOF.writeln("");

 

        try {

 

            URL url;

                url = new URL("http://dummy.restapiexample.com/api/v1/employees");

         

            lOF.writeln("URL : " + url);

         

            HttpURLConnection con;

                con = (HttpURLConnection) url.openConnection();

    

            con.setRequestMethod("GET");

 

             int status = con.getResponseCode();

             BufferedReader in = new BufferedReader(new InputStreamReader(con.getInputStream()));

             String inputLine;

             StringBuffer content = new StringBuffer();

             while((inputLine = in.readLine()) != null) {

                     content.append(inputLine);

                    System.out.println(inputLine);

             }

             in.close();

             con.disconnect();

             System.out.println("Response status: " + status);

             System.out.println(content.toString());

             

            lOF.writeln("Content : " + content.toString());

 

 

            lOF.writeln("");

            lOF.writeln("+--------------------------------------+");

            lOF.writeln("My Concurrent Program REST API Complete");

            lOF.writeln("+--------------------------------------+");

 

            lRC.setCompletion(ReqCompletion.NORMAL, 

                    "Request Completed Normal");

       

        } catch (ProtocolException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

        

         } catch (IOException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

        

        } /*catch (MalformedURLException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

              

        }*/ 

        finally {

            pCpContext.releaseJDBCConnection();

        }

    }

}

 

Oracle Apps Java Concurrent Program

Oracle Apps Java Concurrent Program

We can build Java concurrent programs to use java capabilities. 

Following are some steps 

1. Create and compile java Class in jDeveloper.

1. my suggestion is to create Java class package oracle.apps.fnd.cp.request
2. Java class must implements JavaConcurrentProgram

Sample Java Class

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 JavaConcRestAPIClient implements JavaConcurrentProgram {

    String pDate;

 

    public JavaConcRestAPIClient() {

    }

 

    public void runProgram(CpContext pCpContext)  {

        // get the JDBC connection object 

        Connection mJConn = pCpContext.getJDBCConnection();

 

        // get parameter list object from CpContext 

        ParameterList lPara = pCpContext.getParameterList();

 

        // get ReqCompletion object from CpContext 

        ReqCompletion lRC = pCpContext.getReqCompletion();

 

        // get OutFile object from CpContext 

        OutFile lOF = pCpContext.getOutFile();

 

        // get LogFile object from CpContext 

        LogFile lLF = pCpContext.getLogFile();

 

        // value and use it as the application short name in the query 

        while (lPara.hasMoreElements()) {

            NameValueType aNVT = lPara.nextParameter();

            if (aNVT.getName().equals("pdate"))

                pDate = aNVT.getValue();

        }

      lOF.writeln("+--------------------------------------+");

      lOF.writeln("My Concurrent Program REST API Client");

      lOF.writeln("+---------------------------------------+");

      lOF.writeln("");

 

      try {

         

      lOF.writeln("+---------------------------------------+");

      lOF.writeln("Calling API ");

      lOF.writeln("+----------------------------------------+");

      lOF.writeln("");

 

        try {

 

            URL url;

                url = new URL("http://dummy.restapiexample.com/api/v1/employees");

         

            lOF.writeln("URL : " + url);

         

            HttpURLConnection con;

                con = (HttpURLConnection) url.openConnection();

    

            con.setRequestMethod("GET");

 

             int status = con.getResponseCode();

             BufferedReader in = new BufferedReader(new InputStreamReader(con.getInputStream()));

             String inputLine;

             StringBuffer content = new StringBuffer();

             while((inputLine = in.readLine()) != null) {

                     content.append(inputLine);

                    System.out.println(inputLine);

             }

             in.close();

             con.disconnect();

             System.out.println("Response status: " + status);

             System.out.println(content.toString());

             

            lOF.writeln("Content : " + content.toString());

 

 

            lOF.writeln("");

            lOF.writeln("+--------------------------------------+");

            lOF.writeln("My Concurrent Program REST API Complete");

            lOF.writeln("+--------------------------------------+");

 

            lRC.setCompletion(ReqCompletion.NORMAL, 

                    "Request Completed Normal");

       

        } catch (ProtocolException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

        

         } catch (IOException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

        

        } /*catch (MalformedURLException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

              

        }*/ 

        finally {

            pCpContext.releaseJDBCConnection();

        }

    }

}

 

2. Move the .class file to $JAVA_TOP$/oracle/apps/fnd/cp/request

3. Register as a Concurrent program
Executable

Concurrent Program

Must put the below reference jars into options without any space

-classpath /apps/apps_st/comn/java/classes:/apps/apps_st/appl/au/12.0.0/java/appsborg.zip:/apps/apps_st/appl/au/12.0.0/java/appsborg2.zip