Transfer file from PL SQL to third party system using java concurrent program
Transfer the CLOB variable to third party system using https protocol and java concurrent program.
package xxpo.ebs;
import java.io.IOException;
import oracle.apps.fnd.cp.request.CpContext;
import oracle.apps.fnd.cp.request.JavaConcurrentProgram;
import oracle.apps.fnd.cp.request.LogFile;
import oracle.apps.fnd.cp.request.ReqCompletion;
import oracle.apps.fnd.util.NameValueType;
import oracle.apps.fnd.util.ParameterList;
import org.json.JSONException;
public class XXONT_OutboundInterfaceServlet_ABC implements JavaConcurrentProgram {
private static final String CLASS_NAME = “XXONT_OutboundInterfaceServlet_ABC”;
public void runProgram(CpContext paramCpContext) {
ReqCompletion localReqCompletion = paramCpContext.getReqCompletion();
ParameterList localParameterList = paramCpContext.getParameterList();
LogFile localLogFile = paramCpContext.getLogFile();
String record_id = “”;
boolean bool = false;
localLogFile.writeln(“XXONT_OutboundInterfaceServlet_ABC : runProgram() executing: “, 0);
while (localParameterList.hasMoreElements()) {
NameValueType localNameValueType =
localParameterList.nextParameter();
record_id = localNameValueType.getValue();
localLogFile.writeln(“Param servletUrl: XXONT_OutboundInterfaceServlet_ABC : runProgram() : ” +
record_id, 0);
XXpoSoPoOutbound_EBS printUtil = new XXpoSoPoOutbound_EBS();
try {
bool = printUtil.getDataTable(record_id, localLogFile);
if (bool) {
localReqCompletion.setCompletion(0,
“Request Completed Normal”);
localLogFile.writeln(“Execution Completed for : XXONT_OutboundInterfaceServlet_ABC : runProgram()”,
0);
} else {
localReqCompletion.setCompletion(1,
“Request Completed Error”);
localLogFile.writeln(“Execution Failed for : XXONT_OutboundInterfaceServlet_ABC : runProgram()”,
0);
}
} catch (Exception exception) {
localLogFile.writeln(“Exception for :XXONT_OutboundInterfaceServlet_ABC : runProgram() : ” +
exception.getMessage(), 0);
}
}
}
}
package xxpo.ABC;
import java.io.BufferedReader;
import java.io.IOException;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.HashMap;
import org.json.JSONException;
import org.json.JSONObject;
import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;
import okhttp3.RequestBody;
import okhttp3.Request.Builder;
import okhttp3.MediaType;
import oracle.apps.fnd.cp.request.LogFile;
import oracle.apps.ibe.util.IBEUtil;
import xxibe.epm.util.lookups.XXibeEPMLookupObject;
import xxibe.epm.util.lookups.XXibeEPMLookups;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.apps.fnd.cp.request.CpContext;
import oracle.apps.jtf.aom.transaction.TransactionScope;
public class XXONTSoOutbound_ABC {
final static public String CLASS_NAME = “XXONTSoOutbound_ABC”;
public boolean getDataTable(String record_id, LogFile paramLogFile) {
Connection conn = null;
PreparedStatement pStmt = null;
ResultSet rs5 = null;
String URL = “”;
String PAYLOAD_JSON = “”;
String client_id = “”;
String client_secret = “”;
boolean b = false;
String query =
“Select URL,PAYLOAD_JSON,client_id,client_secret from apps.xxbom_mic_iface_extract WHERE record_id =:1 “;
try {
conn = TransactionScope.getConnection();
pStmt = conn.prepareStatement(query);
pStmt.setString(1, record_id);
rs5 = pStmt.executeQuery();
if (rs5.next()) {
URL = rs5.getString(“URL”);
PAYLOAD_JSON = rs5.getString(“PAYLOAD_JSON”);
client_id = rs5.getString(“client_id”);
client_secret = rs5.getString(“client_secret”);
}
paramLogFile.writeln(“Calling getApiData: XXONTSoOutbound_ABC : “, 0);
b = getApiData(record_id, URL, PAYLOAD_JSON, client_id, client_secret,paramLogFile);
paramLogFile.writeln(“Response from getApiDatat: XXONTSoOutbound_ABC : “+b, 0);
} catch (Exception e) {
paramLogFile.writeln(“Exception – XXONTSoOutbound_ABC : getDataTable() : ” + e.getMessage(), 0);
} finally {
try {
if (rs5 != null)
rs5.close();
} catch (Exception e) {
paramLogFile.writeln(“Exception while closing rs5- XXONTSoOutbound_ABC : getDataTable() : ” + e.getMessage(), 0);
}
try {
if (pStmt != null)
pStmt.close();
} catch (Exception e) {
paramLogFile.writeln(“Exception while closing pStmt- XXONTSoOutbound_ABC : getDataTable() : ” + e.getMessage(), 0);
}
try {
if (conn != null)
TransactionScope.releaseConnection(conn);
} catch (Exception e) {
paramLogFile.writeln(“Exception while closing conn- XXONTSoOutbound_ABC : getDataTable() : ” + e.getMessage(), 0);
}
}
return b;
}
public boolean getApiData(String record_id, String URL, String PAYLOAD_JSON,
String client_id, String client_secret,LogFile paramLogFile) {
final MediaType JSON = MediaType.parse(“application/json; charset=utf-8″);
boolean res = false;
try {
OkHttpClient httpClient = new OkHttpClient();
JSONObject obj = new JSONObject();
JSONObject data = new JSONObject();
Response ibpResponse = null;
String err_msg =”Null response”;
paramLogFile.writeln(“Calling URL for :XXONTSoOutbound_ABC : getApiData : ” + URL, 0);
paramLogFile.writeln(“Calling PAYLOAD_JSON for :XXONTSoOutbound_ABC : getApiData : ” + PAYLOAD_JSON, 0);
RequestBody body = RequestBody.create(JSON, PAYLOAD_JSON);
Builder callIBBuilder = new Request.Builder().url(URL);
callIBBuilder.addHeader(“client_id”,client_id);
callIBBuilder.addHeader(“client_secret”,client_secret);
paramLogFile.writeln(“getApiData: URL : ” + URL, 0);
paramLogFile.writeln(“getApiData: Client_id : ” + client_id, 0);
paramLogFile.writeln(“getApiData: Client_secret : ” + client_secret, 0);
Request callIBPRequest = callIBBuilder.post(body).build();
ibpResponse = httpClient.newCall(callIBPRequest).execute();
paramLogFile.writeln(“Connection Response : ” + ibpResponse.isSuccessful(), 0);
if(ibpResponse !=null){
err_msg = ibpResponse.toString();
}
if (ibpResponse.isSuccessful()) {
paramLogFile.writeln(“ibpResponse for XXONTSoOutbound_ABC :Success”, 0);
res=true;
String responseBody = ibpResponse.body().string();
updateTable(responseBody,record_id,paramLogFile);
}
else
{
paramLogFile.writeln(“ibpResponse for XXONTSoOutbound_ABC :Failure”, 0);
String responseBody = ibpResponse.body().string();
updateTable(responseBody,record_id,paramLogFile);
}
} catch (JSONException jse) {
if (IBEUtil.logEnabled()) {
IBEUtil.log(“JSONException while getApiData…. for ” +
CLASS_NAME, jse.getMessage());
}
} catch (IOException ioe) {
if (IBEUtil.logEnabled()) {
IBEUtil.log(“IOException while getApiData…. for ” +
CLASS_NAME, ioe.getMessage());
}
} catch (Exception e) {
if (IBEUtil.logEnabled()) {
IBEUtil.log(“Exception while getApiData…. for ” +
CLASS_NAME, e.getMessage());
}
}
return res;
}
public void updateTable(String response, String record_id,LogFile paramLogFile) {
Connection conn = null;
PreparedStatement pStmt = null;
String query =
“UPDATE apps.xxpo_po_so_outbound_asco_stg SET response_json =:1, last_updated_by = apps.fnd_global.user_id, last_update_date = SYSDATE WHERE record_id =:2 “;
try {
conn = TransactionScope.getConnection();
pStmt = conn.prepareStatement(query);
pStmt.setString(1, response);
pStmt.setString(2, record_id);
int r = pStmt.executeUpdate();
} catch (Exception e) {
paramLogFile.writeln(“Exception while updating table – XXONTSoOutbound_ABC : updateTable() : ” + e.getMessage(), 0);
if (IBEUtil.logEnabled()) {
IBEUtil.log(“Exception while updating table – updateTable() ” +
CLASS_NAME, e.getMessage());
}
} finally {
try {
if (pStmt != null)
pStmt.close();
} catch (Exception e) {
if (IBEUtil.logEnabled()) {
IBEUtil.log(“Exception while closing pStmt…. for ” +
CLASS_NAME, e.getMessage());
}
}
try {
if (conn != null) {
TransactionScope.releaseConnection(conn);
}
} catch (Exception e) {
if (IBEUtil.logEnabled()) {
IBEUtil.log(“Exception while closing conn…. for ” +
CLASS_NAME, e.getMessage());
}
}
}
}
}