The problem is the following. I have a view object that displays item+lot information from EBS.
Part of the data is located on the remote DB2 server. The goal is somehow to join the data from DB2 with a data in local EBS database.
The idea is the following . We will create global temporary table that will be populated with a data from DB2 when the OAF page is loaded.
Here is the table
create global temporary table XX_OM_LOT_ALLOC_FROM_DB2
(
FACILITY VARCHAR2(1000),
ITEM_ID NUMBER,
LOT_NUMBER VARCHAR2(1000),
BOX_QUANTITY NUMBER,
ONHAND_QUANTITY NUMBER,
ALLOCATED_ONHAND_QUANTITY NUMBER,
EXPIRY_DAYS NUMBER,
UNITS_INNER_PACK NUMBER,
NBR_OF_INNER_IN_OUTER NUMBER
)
on commit delete rows
/
The View Object will be based on the following SQL.
select *
from XX_OM_LOT_ALLOC_FROM_DB2 db2,
ic_item_mst_b items
where db2.item_id = items.item_id
So all we have to do is to populate the temporary table with a data
To connect connect to DB2 we will use open source jt400.jar file.
Bellow is the java class that connects to the DB2 , reads the data and populates the temporary table. Explanation about the code are found in the code comment
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import com.ibm.as400.access.*;
import java.sql.*;
public class LotConnector
{
/* All parameters to the fucntion are ment to be used
to connect to DB2. Except "localConn", which is used to
connect to local database (EBS database)
*/
public static String getItemOnHandQuantity(String env,
String item,
String serverHost,
java.lang.Integer serverPort,
String serverUser,
String serverPwd,
String serverDb,
String serverLib,
Connection localConn)
{
//insert statement to populate the temporary table
final String insertSql = "insert into XX_OM_LOT_ALLOC_FROM_DB2 values (?,?,?,?,?,?,?,?,?)";
String PkEnv = env;
String Item_id = item;
String server = serverHost;
int port = serverPort.intValue();
String username = serverUser;
String password = serverPwd;
String dbName = serverDb;
String LibraryName = serverLib;
Connection connection = null;
CallableStatement proc_stmt = null;
ResultSet rs = null;
boolean isResultSetExists = false;
//do something only if connection details to DB2 exist
try {
if(PkEnv!=null && server!=null && port!=0 && username!=null && password!=null && dbName!=null && LibraryName!=null)
{
// load the driver and set properties for connection
// to db2
DriverManager.registerDriver( new AS400JDBCDriver());
Properties props = new Properties();
props.setProperty("user",username);
props.setProperty("password",password);
props.setProperty("naming","system");
props.setProperty("date_format","iso");
props.setProperty("transaction_isolation","none");
connection = DriverManager.getConnection("jdbc:as400://"+server+":"+port+"/"+dbName+"", props);
//note the format of the call
// in Oracle it is MY_PACKAGE.MY_FUNCTION
// but for DB2 we specify MY_LIBRARY/MYFUNCTION
proc_stmt = connection.prepareCall("{call "+LibraryName+"/TV38U0RP(?,?)}"); //IN Parameter
proc_stmt.setString(1, PkEnv);
proc_stmt.setString(2,Item_id);
proc_stmt.execute();
rs = proc_stmt.getResultSet();
Connection localConnection = localConn;
//insert to local table
PreparedStatement ps = localConnection.prepareStatement(insertSql);
while (rs.next())
{
//indication that something found
isResultSetExists = true;
//record data from DB2
String facility = rs.getString(1);
String ITEM_ID = rs.getString(2);
String lot = rs.getString(3);
String InnerPackQuantity = rs.getString(4);
String OnHandQuantity = rs.getString(5);
String AllocatedOnHandQuantity = rs.getString(6);
String ExpiryDays = rs.getString(7);
String UnitsInnerPack = rs.getString(8);
String NbrOfInnerInouter = rs.getString(9);
//local insert
ps.setString(1,facility);
ps.setInt(2,Integer.parseInt(ITEM_ID.trim()));
if (lot!=null && !lot.equals(""))
ps.setString(3,lot);
else
ps.setNull(3,Types.NULL);
if (InnerPackQuantity!=null && !InnerPackQuantity.equals(""))
ps.setString(4,InnerPackQuantity);
else
ps.setNull(4,Types.NULL);
if (OnHandQuantity!=null && !OnHandQuantity.equals(""))
ps.setString(5,OnHandQuantity);
else
ps.setNull(5,Types.NULL);
if (AllocatedOnHandQuantity!=null && !AllocatedOnHandQuantity.equals(""))
ps.setString(6,AllocatedOnHandQuantity);
else
ps.setNull(6,Types.NULL);
if (ExpiryDays!=null && !ExpiryDays.equals(""))
ps.setString(7,ExpiryDays);
else
ps.setNull(7,Types.NULL);
if (UnitsInnerPack!=null && !UnitsInnerPack.equals(""))
ps.setString(8,UnitsInnerPack);
else
ps.setNull(8,Types.NULL);
if (NbrOfInnerInouter!=null && !NbrOfInnerInouter.equals(""))
ps.setString(9,NbrOfInnerInouter);
else
ps.setNull(9,Types.NULL);
//use batch insert to improve the performance
ps.addBatch();
} //end while
if (isResultSetExists)
{
ps.executeBatch();
ps.close();
}
}
else
{
return ("Parameter missing.");
}
}
// handle exceptions and connections
catch (SQLException sqle)
{
sqle.printStackTrace();
return ("SQLException: " + sqle.getMessage());
}
catch (Exception e) {
e.printStackTrace();
return ("SQLException: " + e.getMessage());
}
finally
{
if (proc_stmt != null) {
try {
proc_stmt.close();
} catch (SQLException e) {
e.printStackTrace();
return ("SQLException: " + e.getMessage());
}
}
if (rs!= null){
try {
rs.close();
}catch (Exception expre) {
expre.printStackTrace();
return ("Exception : " + expre.getMessage());
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException exp) {
exp.printStackTrace();
return ("SQLException: " + exp.getMessage());
}
}
}
return "OK";
}
}
We will define in application module the following function
public class LotAllocationAMImpl extends OAApplicationModuleImpl
{
public String pullDataFromDb2(String env,
String itemId,
String server,
String port,
String user,
String pwd,
String name,
String library)
{
Connection conn = getOADBTransaction().getJdbcConnection();
//call the class we defined above
return LotConnector.getItemOnHandQuantity(env,
itemId,
server,
new Integer(port),
user,
pwd,
name,
library,
conn);
}
}
All we need to do now is to call this code when the page is loaded , before VO is executed.
As every OAF developer knows the way to do it , is to call this function from processRequest functon in the page controller
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
// for obvious reasons I don’t publish the connection details
String db2Env = "dummy";
String itemId = "dummy";
String db2Server = "dummy";
String db2Port = "dummy";
String db2User = "dummy";
String db2Pwd = "dummy";
String db2Name = "dummy";
String db2Lib = "dummy";
Serializable[] db2Params = { db2Env, itemId,db2Server,db2Port,db2User,db2Pwd,db2Name,db2Lib};
String result = (String)pageContext.getRootApplicationModule().invokeMethod("pullDataFromDb2",db2Params);
}
Enjoy…
