1. Can I write into log and output of concurrent program, i.e. equivalent FND_FILE?
Answer : Yes, you can use logFile.writeln
2. Will I have fnd_global context set in jcp?
Answer: Yes, internally the java classes call fnd_global before passing you the connection object.
3. Is java concurrent program stable?
Answer : Yes, very stable. I have been using this for 4years to do variety of things, ever since it was released in 11.5.8.
4. Can I make a Java Concurrent Program end with warning?
Answer: Yes you can, by doing CpContext.getReqCompletion.setCompletion(ReqCompletion.WARNING, pCompletionText)
5. On which server should the class file be deployed?
Answer : On the server where your Concurrent Managers are running, which traditionally is the database server.
Please find the same java concurrent program for interfacing with pseudo PeopleSoft.
The code below contains compiled example for all the above operations.
import java.sql.*;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.apps.fnd.cp.request.* ;
import java.io.*;
import oracle.apps.fnd.util.*;
* This class will interface data from a pseudo Peoplsoft SQL Server into APPS
public class xxInterfaceFromPeopleSoft implements JavaConcurrentProgram {
// Application Short Name
private String applName;
// Single CUSTOMER Number for which the process is being run
private String singleCUSTOMERNumber;
private String singleCUSTOMERNumberTemp;
// The order by clause
private String orderByClauseSQL;
//File Separator
private String mFileSeparator;
//
private String mControlFilepath;
// Debug Flag
private String mDebugFlag;
// mPeopleSoftServerName fetched from profile
private String mPeopleSoftServerName;
//1=Yes, 2=No
private String errorsEncounteredFlag ;
CpContext mCtx;
LogFile logFile;
OutFile outFile;
Connection mConn = null;
ReqCompletion lRC;
int mRequestStatus = -1; //uninitialized
//to set the maximum number of records that can be processed
int max_number_of_records = 99999999;
// long max_number_of_records = 2;
public static final String M_SUCCESS = "SUCCESS";
public static final String M_ERROR = "ERROR";
public static final String M_WARNING = "WARNING";
String mProfileValue = " Declare l_value Varchar2(50); " + " BEGIN "
+ " :1 := FND_PROFILE.VALUE(:2); " + " END;";
String doesErrorExist = " Declare l_value Varchar2(50); " + " BEGIN "
+ " :1 := xxPeopleSoftInterfacePkg.does_error_exist; " + " END;";
String mLastRunDate = " Declare l_value Varchar2(50); " + " BEGIN "
+ " :1 := xxPeopleSoftInterfacePkg.get_last_run_date(:2); " + " END;";
String mCaptureIFaceCtrl = " Declare " + " BEGIN "
+ " xxPeopleSoftInterfacePkg.capture_control_record ; "
+ " END;";
private void registerControlRecord()
{
OracleCallableStatement lStmt_ctrl = null;
try {
logMessage("Inside registerControlRecord()");
lStmt_ctrl = (OracleCallableStatement) mConn
.prepareCall(mCaptureIFaceCtrl);
lStmt_ctrl.execute();
lStmt_ctrl.close();
} catch (SQLException s) {
logMessage(
"In Caputre Trx registering control record: Exception thrown w/ error message: "
+ s.getMessage(), "Y");
lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
} finally {
try {
if (lStmt_ctrl != null)
lStmt_ctrl.close();
} catch (SQLException e) {
}
}
}
private void logMessage(String str, String lDebugMode) {
java.util.Date now = new java.util.Date();
logFile.writeln(now.toString() + ": " + str, LogFile.STATEMENT);
}
private void logMessage(String str) {
java.util.Date now = new java.util.Date();
logFile.writeln(now.toString() + ": " + str, LogFile.STATEMENT);
}
/**
* A generic method made to return the profile value of the passed profile
* name.
*/
private String getProfileValue(String profile) {
String l_debug_mode = null;
OracleCallableStatement lStmt = null;
try {
lStmt = (OracleCallableStatement) mConn.prepareCall(mProfileValue);
lStmt.setString(2, profile);
lStmt.registerOutParameter(1, java.sql.Types.VARCHAR, 0, 255);
lStmt.execute();
l_debug_mode = lStmt.getString(1);
lStmt.close();
logMessage("Profile '" + profile + "' Value : " + l_debug_mode,
mDebugFlag == null ? "Y" : mDebugFlag);
} catch (SQLException s) {
logMessage(
"FileLoader.getProfileValue(): Exception thrown w/ error message: "
+ s.getMessage(), "Y");
lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
} finally {
try {
if (lStmt != null)
lStmt.close();
} catch (SQLException e) {
}
}
return (l_debug_mode);
}
private String getErrorFlag() {
String l_error_flag = null;
OracleCallableStatement lStmt = null;
try {
lStmt = (OracleCallableStatement) mConn.prepareCall(doesErrorExist);
lStmt.registerOutParameter(1, java.sql.Types.VARCHAR, 0, 255);
lStmt.execute();
l_error_flag = lStmt.getString(1);
lStmt.close();
logMessage("Error API Returned '" + "' Value : " + l_error_flag);
} catch (SQLException s) {
logMessage(
"Exception thrown in getErrorFlag: "
+ s.getMessage(), "Y");
lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
} finally {
try {
if (lStmt != null)
lStmt.close();
} catch (SQLException e) {
}
}
return (l_error_flag);
}
public String getMessage(String appName, String msgName) {
if (mCtx != null && mCtx.getResourceStore() != null)
return (mCtx.getResourceStore().getResourceText(appName, msgName));
else
return msgName;
}
/**
* Returns the file separator
*/
private String getFileSeparator() {
return (System.getProperty("file.separator"));
}
static Connection con = null;
// static Connection oraConn = null;
static String mCaptureCustomer = " Declare " + " BEGIN "
+ " xxPeopleSoftInterfacePkg.manage_Customer(p_CUSTOMER => :1,"
+ " p_peopleSoft_customer_id => :2, "
+ " p_deleted => :3, "
+ " p_name => :4, "
+ " p_parentCustomerName => :5, "
+ " p_type_ => :6"
+ "); "
+ " END;";
// ####################### Begin manage_Customer #######################
private void manage_Customer
(
String p_CUSTOMER
, String p_peopleSoft_customer_id
, String p_deleted
, String p_name
, String p_parentCustomerName
, String p_type_
)
{
String l_debug_mode = null;
OracleCallableStatement lStmt = null;
logMessage("Fetching data for Customerator CUSTOMER=>"
+ p_CUSTOMER + " Deletion Flag=>"+ p_deleted);
try {
lStmt = (OracleCallableStatement) mConn
.prepareCall(mCaptureCustomer);
lStmt.setString(1, p_CUSTOMER);
lStmt.setString(2, p_peopleSoft_customer_id);
lStmt.setString(3, p_deleted);
lStmt.setString(4, p_name);
lStmt.setString(5, p_parentCustomerName);
lStmt.setString(6, p_type_);
lStmt.execute();
lStmt.close();
} catch (SQLException s) {
logMessage(
"In Caputre for CUSTOMER=>" + p_CUSTOMER + " Exception thrown w/ error message: "
+ s.getMessage(), "Y");
// lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
}
finally {
try {
if (lStmt != null)
lStmt.close();
} catch (SQLException e) {
}
}
}
// ####################### End manage_Customer #######################
public void openConection() {
logMessage( "In openConection()");
try {
logMessage( "Before Loading Driver com.microsoft.jdbc.sqlserver.SQLServerDriver");
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
logMessage( "After Loading Driver com.microsoft.jdbc.sqlserver.SQLServerDriver");
logMessage( "Attempting to connect to " + mPeopleSoftServerName);
con = DriverManager
.getConnection("jdbc:microsoft:sqlserver://" + mPeopleSoftServerName + ":1433;User=pplsoft_apps;Password=guestpeoplesoft");
//Use the default port for PeopleSoft SQL*Server 1433
logMessage( "After con assigned from getConnection(jdbc:microsoft:sqlserver://" + mPeopleSoftServerName + ":1433;User=pplsoft_apps;Password=*****");
} catch (java.lang.ClassNotFoundException e) {
logMessage("openConection() ClassNotFoundException: " + e);
} catch (SQLException ex) {
logMessage("openConection() SQLException: " + ex);
}
}
public void closeSQLServerConnection() {
try {
logMessage( "In closeSQLServerConnection().......closing JDBC Connection");
if (con != null)
{
logMessage( "Con is not null");
con.close();
logMessage( "Con Closed Successfully");
}
else
logMessage( "Connection object was null, hence skipping close");
} catch (SQLException ex) {
logMessage("SQLException: " + ex);
}
catch (Exception ex) {
logMessage("Exception in closeSQLServerConnection() : " + ex);
}
}
/**
* Method that returns the date when this interface ran the last time
*/
private Date getLastRunDate() {
String l_debug_mode = null;
CallableStatement lStmt = null;
Date lastRunDate = null;
try {
lStmt = (CallableStatement) mConn.prepareCall(mLastRunDate);
lStmt.setString(2, singleCUSTOMERNumberTemp);
lStmt.registerOutParameter(1, Types.DATE);
lStmt.execute();
lastRunDate = lStmt.getDate(1);
lStmt.close();
logMessage("getLastDate is returning : " + lastRunDate , "Y");
} catch (SQLException s) {
logMessage(
"getLastRunDate(): Exception thrown w/ error message: "
+ s.getMessage(), "Y");
lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
} finally {
try {
if (lStmt != null)
lStmt.close();
} catch (SQLException e) {
}
}
return (lastRunDate);
}
private void processPeopleSoftCustomerRecords() {
int number_of_recs = 0;
Date lastRunDate = getLastRunDate();
//############# Begin Customerator #############
try {
{
PreparedStatement stmt = con.prepareStatement("SELECT CUSTOMER_NUMBER ,Customer_id ,deleted_flag ,CustomerName ,Parent_Customer_name FROM pplapps.PeopleSoftCustomerView WHERE ModifiedWhen >= ?" + singleCUSTOMERNumber + orderByClauseSQL );
stmt.setDate(1,lastRunDate);
ResultSet rst = stmt.executeQuery();
while (rst.next()) {
number_of_recs++;
if (number_of_recs == max_number_of_records)
break;
manage_Customer(
rst.getString(1), rst.getString(2)
,rst.getString(3), rst.getString(4)
,rst.getString(5), "" /*We do not have any value for Customer
* Type_ from peoplesoft*/
);
}
stmt.close();
rst.close();
}
mConn.commit();
} catch (SQLException ex) {
logMessage("SQLException: " + ex);
}
catch (Exception ex) {
logMessage("Exception:: " + ex);
}
closeSQLServerConnection();
}
/**
* Concurrent Processing provides an interface 'JavaConcurrentProgram' with
* abstract method runProgram().
* It is within this method that we load the SQL*Server Driver for
* TCA Customers.
* All the inserts/updates in APPS will be done using various TCA PL/SQL
* APIs within xxPeopleSoftInterfacePkg
*/
public void runProgram(CpContext pCpContext) {
applName = "MSC";
String l_file_path = null;
String l_msc_top = null;
mCtx = pCpContext;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
// assign logfile
logFile = pCpContext.getLogFile();
// assign outfile
outFile = pCpContext.getOutFile();
// get the JDBC connection object
mConn = pCpContext.getJDBCConnection();
// assign fileseparator
mFileSeparator = getFileSeparator();
//get debug mode
mDebugFlag = getProfileValue("XX_ENABLE_DEBUG_MODE");
//get Pubs server name
mPeopleSoftServerName = getProfileValue("XX_PEOPLESOFT_SERVER_NAME");
l_file_path = ((new File(outFile.getFileName())).getParent() == null ? ""
: (new File(outFile.getFileName())).getParent());
// get parameter list object from CpContext
ParameterList lPara = pCpContext.getParameterList();
while(lPara.hasMoreElements())
{
NameValueType aNVT = lPara.nextParameter() ;
//Currently using only one Parameter, else we need to use aNVT.getName
singleCUSTOMERNumber = aNVT.getValue() ;
singleCUSTOMERNumberTemp = singleCUSTOMERNumber ;
}
if (!(singleCUSTOMERNumber == null || singleCUSTOMERNumber == ""))
singleCUSTOMERNumber = " and CUSTOMER = " + singleCUSTOMERNumber ;
orderByClauseSQL = " order by ModifiedWhen, ID " ;
logMessage("singleCUSTOMERNumber=>"+ singleCUSTOMERNumber );
/** openConection() Opens the connection to SQL Server Database*/
openConection() ;
logMessage("Before Calling processPeopleSoftCustomerRecords()");
processPeopleSoftCustomerRecords();
logMessage("After Calling processPeopleSoftCustomerRecords()");
logMessage("singleCUSTOMERNumber=>" + singleCUSTOMERNumber );
//The control record will be registered only when the CUSTOMER passed in is NULL
if (singleCUSTOMERNumber == null || singleCUSTOMERNumber == "")
{
logMessage("singleCUSTOMERNumber=>" + singleCUSTOMERNumber );
registerControlRecord() ;
}
try
{
// get OutFile object from CpContext
OutFile lOF = pCpContext.getOutFile();
errorsEncounteredFlag = getErrorFlag() ;
if (errorsEncounteredFlag.equals("1"))
setCompletion(ReqCompletion.WARNING, "Request Encountered some errors");
else
{
outFile.writeln("No Errors Encountered.");
setCompletion(ReqCompletion.NORMAL, "Request Completed Normal");
}
} catch (Exception e) {
setCompletion(ReqCompletion.ERROR, e.toString());
} finally {
pCpContext.releaseJDBCConnection();
}
}
/**
* Sets the request completion status based on proper precedence. ERROR >
* WARNING > NORMAL
*
* @param pStatus
* Status of the request.
* @param pCompletionText
* Request's completion text.
*/
public void setCompletion(int pStatus, String pCompletionText) {
if ((pStatus == ReqCompletion.ERROR)
|| ((pStatus == ReqCompletion.WARNING) && (mRequestStatus != ReqCompletion.ERROR))
|| ((pStatus == ReqCompletion.NORMAL)
&& (mRequestStatus != ReqCompletion.WARNING) && (mRequestStatus != ReqCompletion.ERROR))) {
mRequestStatus = pStatus;
lRC.setCompletion(pStatus, pCompletionText);
}
}
}
Answer : Yes, you can use logFile.writeln
2. Will I have fnd_global context set in jcp?
Answer: Yes, internally the java classes call fnd_global before passing you the connection object.
3. Is java concurrent program stable?
Answer : Yes, very stable. I have been using this for 4years to do variety of things, ever since it was released in 11.5.8.
4. Can I make a Java Concurrent Program end with warning?
Answer: Yes you can, by doing CpContext.getReqCompletion.setCompletion(ReqCompletion.WARNING, pCompletionText)
5. On which server should the class file be deployed?
Answer : On the server where your Concurrent Managers are running, which traditionally is the database server.
Please find the same java concurrent program for interfacing with pseudo PeopleSoft.
The code below contains compiled example for all the above operations.
import java.sql.*;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.apps.fnd.cp.request.* ;
import java.io.*;
import oracle.apps.fnd.util.*;
* This class will interface data from a pseudo Peoplsoft SQL Server into APPS
public class xxInterfaceFromPeopleSoft implements JavaConcurrentProgram {
// Application Short Name
private String applName;
// Single CUSTOMER Number for which the process is being run
private String singleCUSTOMERNumber;
private String singleCUSTOMERNumberTemp;
// The order by clause
private String orderByClauseSQL;
//File Separator
private String mFileSeparator;
//
private String mControlFilepath;
// Debug Flag
private String mDebugFlag;
// mPeopleSoftServerName fetched from profile
private String mPeopleSoftServerName;
//1=Yes, 2=No
private String errorsEncounteredFlag ;
CpContext mCtx;
LogFile logFile;
OutFile outFile;
Connection mConn = null;
ReqCompletion lRC;
int mRequestStatus = -1; //uninitialized
//to set the maximum number of records that can be processed
int max_number_of_records = 99999999;
// long max_number_of_records = 2;
public static final String M_SUCCESS = "SUCCESS";
public static final String M_ERROR = "ERROR";
public static final String M_WARNING = "WARNING";
String mProfileValue = " Declare l_value Varchar2(50); " + " BEGIN "
+ " :1 := FND_PROFILE.VALUE(:2); " + " END;";
String doesErrorExist = " Declare l_value Varchar2(50); " + " BEGIN "
+ " :1 := xxPeopleSoftInterfacePkg.does_error_exist; " + " END;";
String mLastRunDate = " Declare l_value Varchar2(50); " + " BEGIN "
+ " :1 := xxPeopleSoftInterfacePkg.get_last_run_date(:2); " + " END;";
String mCaptureIFaceCtrl = " Declare " + " BEGIN "
+ " xxPeopleSoftInterfacePkg.capture_control_record ; "
+ " END;";
private void registerControlRecord()
{
OracleCallableStatement lStmt_ctrl = null;
try {
logMessage("Inside registerControlRecord()");
lStmt_ctrl = (OracleCallableStatement) mConn
.prepareCall(mCaptureIFaceCtrl);
lStmt_ctrl.execute();
lStmt_ctrl.close();
} catch (SQLException s) {
logMessage(
"In Caputre Trx registering control record: Exception thrown w/ error message: "
+ s.getMessage(), "Y");
lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
} finally {
try {
if (lStmt_ctrl != null)
lStmt_ctrl.close();
} catch (SQLException e) {
}
}
}
private void logMessage(String str, String lDebugMode) {
java.util.Date now = new java.util.Date();
logFile.writeln(now.toString() + ": " + str, LogFile.STATEMENT);
}
private void logMessage(String str) {
java.util.Date now = new java.util.Date();
logFile.writeln(now.toString() + ": " + str, LogFile.STATEMENT);
}
/**
* A generic method made to return the profile value of the passed profile
* name.
*/
private String getProfileValue(String profile) {
String l_debug_mode = null;
OracleCallableStatement lStmt = null;
try {
lStmt = (OracleCallableStatement) mConn.prepareCall(mProfileValue);
lStmt.setString(2, profile);
lStmt.registerOutParameter(1, java.sql.Types.VARCHAR, 0, 255);
lStmt.execute();
l_debug_mode = lStmt.getString(1);
lStmt.close();
logMessage("Profile '" + profile + "' Value : " + l_debug_mode,
mDebugFlag == null ? "Y" : mDebugFlag);
} catch (SQLException s) {
logMessage(
"FileLoader.getProfileValue(): Exception thrown w/ error message: "
+ s.getMessage(), "Y");
lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
} finally {
try {
if (lStmt != null)
lStmt.close();
} catch (SQLException e) {
}
}
return (l_debug_mode);
}
private String getErrorFlag() {
String l_error_flag = null;
OracleCallableStatement lStmt = null;
try {
lStmt = (OracleCallableStatement) mConn.prepareCall(doesErrorExist);
lStmt.registerOutParameter(1, java.sql.Types.VARCHAR, 0, 255);
lStmt.execute();
l_error_flag = lStmt.getString(1);
lStmt.close();
logMessage("Error API Returned '" + "' Value : " + l_error_flag);
} catch (SQLException s) {
logMessage(
"Exception thrown in getErrorFlag: "
+ s.getMessage(), "Y");
lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
} finally {
try {
if (lStmt != null)
lStmt.close();
} catch (SQLException e) {
}
}
return (l_error_flag);
}
public String getMessage(String appName, String msgName) {
if (mCtx != null && mCtx.getResourceStore() != null)
return (mCtx.getResourceStore().getResourceText(appName, msgName));
else
return msgName;
}
/**
* Returns the file separator
*/
private String getFileSeparator() {
return (System.getProperty("file.separator"));
}
static Connection con = null;
// static Connection oraConn = null;
static String mCaptureCustomer = " Declare " + " BEGIN "
+ " xxPeopleSoftInterfacePkg.manage_Customer(p_CUSTOMER => :1,"
+ " p_peopleSoft_customer_id => :2, "
+ " p_deleted => :3, "
+ " p_name => :4, "
+ " p_parentCustomerName => :5, "
+ " p_type_ => :6"
+ "); "
+ " END;";
// ####################### Begin manage_Customer #######################
private void manage_Customer
(
String p_CUSTOMER
, String p_peopleSoft_customer_id
, String p_deleted
, String p_name
, String p_parentCustomerName
, String p_type_
)
{
String l_debug_mode = null;
OracleCallableStatement lStmt = null;
logMessage("Fetching data for Customerator CUSTOMER=>"
+ p_CUSTOMER + " Deletion Flag=>"+ p_deleted);
try {
lStmt = (OracleCallableStatement) mConn
.prepareCall(mCaptureCustomer);
lStmt.setString(1, p_CUSTOMER);
lStmt.setString(2, p_peopleSoft_customer_id);
lStmt.setString(3, p_deleted);
lStmt.setString(4, p_name);
lStmt.setString(5, p_parentCustomerName);
lStmt.setString(6, p_type_);
lStmt.execute();
lStmt.close();
} catch (SQLException s) {
logMessage(
"In Caputre for CUSTOMER=>" + p_CUSTOMER + " Exception thrown w/ error message: "
+ s.getMessage(), "Y");
// lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
}
finally {
try {
if (lStmt != null)
lStmt.close();
} catch (SQLException e) {
}
}
}
// ####################### End manage_Customer #######################
public void openConection() {
logMessage( "In openConection()");
try {
logMessage( "Before Loading Driver com.microsoft.jdbc.sqlserver.SQLServerDriver");
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
logMessage( "After Loading Driver com.microsoft.jdbc.sqlserver.SQLServerDriver");
logMessage( "Attempting to connect to " + mPeopleSoftServerName);
con = DriverManager
.getConnection("jdbc:microsoft:sqlserver://" + mPeopleSoftServerName + ":1433;User=pplsoft_apps;Password=guestpeoplesoft");
//Use the default port for PeopleSoft SQL*Server 1433
logMessage( "After con assigned from getConnection(jdbc:microsoft:sqlserver://" + mPeopleSoftServerName + ":1433;User=pplsoft_apps;Password=*****");
} catch (java.lang.ClassNotFoundException e) {
logMessage("openConection() ClassNotFoundException: " + e);
} catch (SQLException ex) {
logMessage("openConection() SQLException: " + ex);
}
}
public void closeSQLServerConnection() {
try {
logMessage( "In closeSQLServerConnection().......closing JDBC Connection");
if (con != null)
{
logMessage( "Con is not null");
con.close();
logMessage( "Con Closed Successfully");
}
else
logMessage( "Connection object was null, hence skipping close");
} catch (SQLException ex) {
logMessage("SQLException: " + ex);
}
catch (Exception ex) {
logMessage("Exception in closeSQLServerConnection() : " + ex);
}
}
/**
* Method that returns the date when this interface ran the last time
*/
private Date getLastRunDate() {
String l_debug_mode = null;
CallableStatement lStmt = null;
Date lastRunDate = null;
try {
lStmt = (CallableStatement) mConn.prepareCall(mLastRunDate);
lStmt.setString(2, singleCUSTOMERNumberTemp);
lStmt.registerOutParameter(1, Types.DATE);
lStmt.execute();
lastRunDate = lStmt.getDate(1);
lStmt.close();
logMessage("getLastDate is returning : " + lastRunDate , "Y");
} catch (SQLException s) {
logMessage(
"getLastRunDate(): Exception thrown w/ error message: "
+ s.getMessage(), "Y");
lRC.setCompletion(ReqCompletion.WARNING, M_WARNING);
s.printStackTrace();
} finally {
try {
if (lStmt != null)
lStmt.close();
} catch (SQLException e) {
}
}
return (lastRunDate);
}
private void processPeopleSoftCustomerRecords() {
int number_of_recs = 0;
Date lastRunDate = getLastRunDate();
//############# Begin Customerator #############
try {
{
PreparedStatement stmt = con.prepareStatement("SELECT CUSTOMER_NUMBER ,Customer_id ,deleted_flag ,CustomerName ,Parent_Customer_name FROM pplapps.PeopleSoftCustomerView WHERE ModifiedWhen >= ?" + singleCUSTOMERNumber + orderByClauseSQL );
stmt.setDate(1,lastRunDate);
ResultSet rst = stmt.executeQuery();
while (rst.next()) {
number_of_recs++;
if (number_of_recs == max_number_of_records)
break;
manage_Customer(
rst.getString(1), rst.getString(2)
,rst.getString(3), rst.getString(4)
,rst.getString(5), "" /*We do not have any value for Customer
* Type_ from peoplesoft*/
);
}
stmt.close();
rst.close();
}
mConn.commit();
} catch (SQLException ex) {
logMessage("SQLException: " + ex);
}
catch (Exception ex) {
logMessage("Exception:: " + ex);
}
closeSQLServerConnection();
}
/**
* Concurrent Processing provides an interface 'JavaConcurrentProgram' with
* abstract method runProgram().
* It is within this method that we load the SQL*Server Driver for
* TCA Customers.
* All the inserts/updates in APPS will be done using various TCA PL/SQL
* APIs within xxPeopleSoftInterfacePkg
*/
public void runProgram(CpContext pCpContext) {
applName = "MSC";
String l_file_path = null;
String l_msc_top = null;
mCtx = pCpContext;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
// assign logfile
logFile = pCpContext.getLogFile();
// assign outfile
outFile = pCpContext.getOutFile();
// get the JDBC connection object
mConn = pCpContext.getJDBCConnection();
// assign fileseparator
mFileSeparator = getFileSeparator();
//get debug mode
mDebugFlag = getProfileValue("XX_ENABLE_DEBUG_MODE");
//get Pubs server name
mPeopleSoftServerName = getProfileValue("XX_PEOPLESOFT_SERVER_NAME");
l_file_path = ((new File(outFile.getFileName())).getParent() == null ? ""
: (new File(outFile.getFileName())).getParent());
// get parameter list object from CpContext
ParameterList lPara = pCpContext.getParameterList();
while(lPara.hasMoreElements())
{
NameValueType aNVT = lPara.nextParameter() ;
//Currently using only one Parameter, else we need to use aNVT.getName
singleCUSTOMERNumber = aNVT.getValue() ;
singleCUSTOMERNumberTemp = singleCUSTOMERNumber ;
}
if (!(singleCUSTOMERNumber == null || singleCUSTOMERNumber == ""))
singleCUSTOMERNumber = " and CUSTOMER = " + singleCUSTOMERNumber ;
orderByClauseSQL = " order by ModifiedWhen, ID " ;
logMessage("singleCUSTOMERNumber=>"+ singleCUSTOMERNumber );
/** openConection() Opens the connection to SQL Server Database*/
openConection() ;
logMessage("Before Calling processPeopleSoftCustomerRecords()");
processPeopleSoftCustomerRecords();
logMessage("After Calling processPeopleSoftCustomerRecords()");
logMessage("singleCUSTOMERNumber=>" + singleCUSTOMERNumber );
//The control record will be registered only when the CUSTOMER passed in is NULL
if (singleCUSTOMERNumber == null || singleCUSTOMERNumber == "")
{
logMessage("singleCUSTOMERNumber=>" + singleCUSTOMERNumber );
registerControlRecord() ;
}
try
{
// get OutFile object from CpContext
OutFile lOF = pCpContext.getOutFile();
errorsEncounteredFlag = getErrorFlag() ;
if (errorsEncounteredFlag.equals("1"))
setCompletion(ReqCompletion.WARNING, "Request Encountered some errors");
else
{
outFile.writeln("No Errors Encountered.");
setCompletion(ReqCompletion.NORMAL, "Request Completed Normal");
}
} catch (Exception e) {
setCompletion(ReqCompletion.ERROR, e.toString());
} finally {
pCpContext.releaseJDBCConnection();
}
}
/**
* Sets the request completion status based on proper precedence. ERROR >
* WARNING > NORMAL
*
* @param pStatus
* Status of the request.
* @param pCompletionText
* Request's completion text.
*/
public void setCompletion(int pStatus, String pCompletionText) {
if ((pStatus == ReqCompletion.ERROR)
|| ((pStatus == ReqCompletion.WARNING) && (mRequestStatus != ReqCompletion.ERROR))
|| ((pStatus == ReqCompletion.NORMAL)
&& (mRequestStatus != ReqCompletion.WARNING) && (mRequestStatus != ReqCompletion.ERROR))) {
mRequestStatus = pStatus;
lRC.setCompletion(pStatus, pCompletionText);
}
}
}