Oracle ADF - How to center the popup to the center of the page?

 

Jdeveloper 11g R2.

To center the popup ,just don’t provide “align” and “alignId” attributes in “Show Popup Behavior” component

<af:showPopupBehavior popupId="confirm" triggerType="click"/>

OAF Populate View Object with a data from DB2 database

 

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…

Connect to Oracle EBS apps schema without password by using JDBC connection

 

The following post is not a hack. It just describes the method which is used by Oracle EBS to connect to APPS schema without providing username and password.

  The method will work only with Oracle Application EBS environment. Standalone Oracle databases have nothing to do with it. Also you need to understand that this method is for obtaining JDBC Connection object only

Lets start straight with a code, because it is very simple.

import oracle.apps.fnd.common.AppsContext;
import java.sql.*;

public class ConnectionToApps
{   
   public static void main(String[] args)   
   {     
       String  dbcFile = "Enter Here The Path To Dbc File";      
       Connection _conn=null;     
       AppsContext ac = new AppsContext(dbcFile);      
       _conn = ac.getJDBCConnection();          
    }
}

Only 4 lines of code.

But the trick is to get the path to DBC file.

It is also simple. DBC files are located under $FND_SECURE directory (if connected to the server with application owner OS user)

Bellow is the shell example

/home/applDVIS12>cd $FND_SECURE
/space2/DVIS12/inst/apps/DVIS12_cow/appl/fnd/12.0.0/secure>ls
DVIS12.dbc
/space2/DVIS12/inst/apps/DVIS12_cow/appl/fnd/12.0.0/secure>

Obviously the correct file is DVIS12.dbc. But many times there are a lot of files in this directory. So how do you know the correct one.

It is also not a problem. The file name (without .dbc) always can be found by looking into “Applications Database ID” profile in EBS or by just running the following query

select fnd_profile.VALUE('APPS_DATABASE_ID') from dual

So now you have everything to assemble the PATH to DBC file

To summarize it , the path is $FND_SECURE/ + select fnd_profile.VALUE('APPS_DATABASE_ID') from dual +/.dbc