10 Replies Latest reply: May 22, 2014 9:05 AM by Elco RSS

    APEX auto login from EBS

    Elco

      HI,

      I'm trying to get the auto login from EBS working to APEX. But at this moment without success. I have followed several documentations and forums but I don’t see where the issue is…

      I get the following error:

       

      An exception occured.

       

      URL=http://oracle-fst1lx.dpi.nl:8031/OA_HTML/LaunchApex.jsp?transactionid=1559771240&language_code=US&CallFromForm='Y'&hostname=oracle-fst1lx&port=8080&appnum=106&pagenum=101&oas=-VWWoHJRjmsaPGS9ErF-Dg..

       

      1. javax.servlet.ServletException: oracle.classloader.util.AnnotatedClassNotFoundException:

       

      Missing class: _LaunchApex

       

              Dependent class: oracle.jsp.runtimev2.JspPageInfo

      Loader: oc4j:10.1.3

      Code-Source: /u01/oracle/dedb/apps/tech_st/10.1.3/j2ee/home/lib/ojsp.jar

      Configuration:  in META-INF/boot.xml in /u01/oracle/dedb/apps/tech_st/10.1.3/j2ee/home/oc4j.jar

       

      This load was initiated at oacore.web.html.jsp7846644:0.0.0 using the loadClass() method.

       

      The missing class is not available from any code-source or loader in the system.

       

       

      I have the following in apex (database ID 106)

      (On load before header)

      BEGIN
      apps.ebs_getsession(:P101_USERNAME);
      :P101_PASSWORD :=
      apps.XXAPX_SECURITY_PKG.generate_hash
      (apps.FND_GLOBAL.user_name);
      IF :P101_PASSWORD IS NOT NULL THEN
      APEX_CUSTOM_AUTH.login(
      P_UNAME => :P101_USERNAME,
      P_PASSWORD => :P101_PASSWORD,
      P_SESSION_ID => v('APP_SESSION'),
      P_APP_PAGE => :APP_ID||':1'
      );
      END IF;
      END;

       

      Authentication Scheme

       

      Authentication Function Name                     APPS.apex_integration_sample_apis.apex_validate_login

       

      --------------------------------------------------------------------------------------------------------------------------------

      function validate_login (p_username   in   varchar2

                             , p_password   in varchar2) return boolean

      is

      begin

      return p_password = wwv_flow_item.md5(p_username, 'SECRETKEY');

      end validate_login;

      1. LaunchApex.jsp

      <%@ page contentType="text/html;charset=UTF-8" %><%--

      /* LaunchApex.jsp */

      --%><%@ page

      import="java.io.*"

      import="java.net.*"

      import="java.sql.*"

      import="javax.servlet.http.*"

      import="java.util.Enumeration"

      import="java.util.Properties"

      import="java.lang.Math"

       

      import="oracle.apps.fnd.common.VersionInfo"

      import="oracle.apps.fnd.functionSecurity.Function"

      import="oracle.apps.fnd.functionSecurity.RunFunction"

      import="oracle.apps.fnd.common.WebAppsContext"

      import="oracle.apps.fnd.common.AppsEnvironmentStore"

      import="oracle.apps.fnd.common.WebRequestUtil"

      import="oracle.apps.fnd.common.ResourceStore"

      import="oracle.apps.fnd.security.CSS"

      import="oracle.apps.fnd.common.Message"

       

      import="oracle.jdbc.OracleConnection"

      import="oracle.jdbc.OraclePreparedStatement"

      import="oracle.jdbc.OracleResultSet" %><%!  /* <--- there's the blighter there */

       

      // Session has to be validated first

      WebAppsContext ctx = WebRequestUtil.validateContext(request, response);

       

      if (ctx==null) {

      return; }

      String cookieName = ctx.getSessionCookieName();

       

      boolean validSession = ctx.validateSession(cookieName);

      1. WebRequestUtil.setClientEncoding(response, ctx);

       

      %>

      <html>

      <head>

      <title>Launch Apex</title>

      <!-- LaunchApex.jsp -->

      </head>

      <body>

      <%

       

      String p_application = request.getParameter("application");

      p_application = ( p_application==null ? "NONE" : p_application);

       

       

      String p_page = request.getParameter("page");

      p_page = ( p_page==null ? "1" : p_page);

       

      String p_item_names = request.getParameter("item_names");

      p_item_names = ( p_item_names==null ? "" : p_item_names);

       

      String p_item_values = request.getParameter("item_values");

      p_item_values = ( p_item_values==null ? "" : p_item_values);

       

      AppsEnvironmentStore m_env = (AppsEnvironmentStore) ctx.getEnvStore();

       

      try {

      String l_launcher = ctx.getProfileStore().getProfile("APEX_HTTP_SERVER");

      l_launcher = l_launcher + "/pls/apex/f?p=" + p_application + ":" + p_page;

      l_launcher = l_launcher + "::APPS:::" + p_item_names + ":" + p_item_values;

       

      if (ctx!=null) ctx.freeWebAppsContext();

       

      1. response.sendRedirect(l_launcher);

      } //try

      catch (Exception e) {

      • out.println("Exception found :

       

      ");

             e.printStackTrace(new PrintWriter(out));

            out.println("

      ");

      } //catch

      %>

      </body>

      </html>

       

      JSP COMPILE

       

      DEDB $ ojspCompile.pl --compile -s '[LaunchApex.jsp]'
      logfile set: /u01/oracle/dedb/inst/apps/DEDB_oracle-fst1lx/logs/appl/rgf/ojsp/ojspc_error.log
      starting...(compiling delta)
      using 10i internal ojsp ver: 10
      synchronizing dependency file:
      loading deplist...8103
      enumerating jsps...8104
      updating dependency...3
      parsing jsp...3
      writing deplist...8104

      initializing compilation:

        enumerating jsps...8104

        eliminating children...6033 (-2071)

        searching uncompiled...2 (1 were stale)

      translating and compiling:

        searching untranslated...2

        translating jsps...2/2 in 1s

        compiling jsps...2/2 [failed: 1] in 1s

      Finished!

       

      DEDB $ ojspCompile.pl --compile -s '[LaunchApex.jsp]'

      logfile set: /u01/oracle/dedb/inst/apps/DEDB_oracle-fst1lx/logs/appl/rgf/ojsp/ojspc_error.log

      1. starting...(compiling delta)

      using 10i internal ojsp ver: 10

      synchronizing dependency file:

        loading deplist...8104

        enumerating jsps...8104

        updating dependency...0

      initializing compilation:

        enumerating jsps...8104

        eliminating children...6033 (-2071)

        searching uncompiled...0

      No compilations needed.

       

       

      IN EBS

       

      Profiel:

      FND: APEX URL     = http://oracle-fst1lx:8080/apex

       

      Function :

      Type : SSWA plsql function
      parameter : hostname=oracle-fst1lx&port=8080&appnum=106&pagenum=101
      Html Web : LaunchApex.jsp

      ----------------------------------------------------------------------

       

      CREATE OR REPLACE PACKAGE BODY APPS.XXAPX_SECURITY_PKG AS

      g_key VARCHAR2(100);

      FUNCTION generate_hash (

      p_string IN VARCHAR2

      , p_offset IN NUMBER DEFAULT 0) RETURN VARCHAR2

      IS

      BEGIN

      IF p_string IS NULL THEN RETURN NULL; END IF;

      RETURN RAWTOHEX(UTL_RAW.cast_to_raw(

      DBMS_OBFUSCATION_TOOLKIT.MD5(

      input_string=>p_string||':'||

      TO_CHAR(SYSDATE-(p_offset/24*60*60), 'YYYYMMDD HH24MISS')||g_key)));

      END generate_hash;

      FUNCTION validate_hash (

      p_string IN VARCHAR2

      , p_hash IN VARCHAR2

      , p_delay IN NUMBER DEFAULT 5) RETURN BOOLEAN

      IS

      BEGIN

      FOR i IN 0..p_delay LOOP

      IF p_hash = generate_hash (p_string, i) THEN RETURN TRUE; END IF;

      END LOOP;

      RETURN FALSE;

      END validate_hash;

      FUNCTION ebs_authenticate (

      p_username IN VARCHAR2

      , p_password IN VARCHAR2) RETURN BOOLEAN

      AS

      BEGIN

      IF apps.xxapx_security_pkg.validate_hash (p_username, p_password) THEN RETURN TRUE; END IF;

      RETURN (apps.FND_WEB_SEC.validate_login (p_username, p_password) = 'Y');

      END ebs_authenticate;

      --

      BEGIN

      SELECT encrypted_user_password INTO g_key

      FROM FND_USER WHERE user_name = 'SYSADMIN';

      END XXAPX_SECURITY_PKG;

      /

       

       

      create or replace PROCEDURE apex_launch (application IN NUMBER

      , page IN NUMBER DEFAULT 1

      , request IN VARCHAR2 DEFAULT NULL

      , item_names IN VARCHAR2 DEFAULT NULL

      , item_values IN VARCHAR2 DEFAULT NULL)

      AS

      BEGIN

      OWA_UTIL.mime_header('text/html', FALSE);

      OWA_COOKIE.send(name=>'APEX_APPS_'||application,

      value=>FND_GLOBAL.user_name||':'||apps.XXAPX_SECURITY_PKG.generate_hash (apps.FND_GLOBAL.user_name),

      path=>'/'

      );

      OWA_UTIL.redirect_url(

      'http://apex-server:port'||'/apex/f?p='||

      application||':'||page||'::'||request||':::'||

      item_names||':'||item_values);

      END apex_launch;\

       

       

       

      GRANT EXECUTE ON apex_launch TO APEX_EBS

      CREATE OR REPLACE procedure APPS.ebs_getsession(p_username out nocopy varchar2)

      as

      begin

      wfa_sec.getsession(p_username);

      end ebs_getsession;

      /

       

       

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      <%@ page contentType="text/html;charset=UTF-8" %><%--

      /* LaunchApex.jsp */

      --%><%@ page

      import="java.io.*"

      import="java.net.*"

      import="java.sql.*"

      import="javax.servlet.http.*"

      import="java.util.Enumeration"

      import="java.util.Properties"

      import="java.lang.Math"

       

       

      import="oracle.apps.fnd.common.VersionInfo"

      import="oracle.apps.fnd.functionSecurity.Function"

      import="oracle.apps.fnd.functionSecurity.RunFunction"

      import="oracle.apps.fnd.common.WebAppsContext"

      import="oracle.apps.fnd.common.AppsEnvironmentStore"

      import="oracle.apps.fnd.common.WebRequestUtil"

      import="oracle.apps.fnd.common.ResourceStore"

      import="oracle.apps.fnd.security.CSS"

      import="oracle.apps.fnd.common.Message"

       

       

      import="oracle.jdbc.OracleConnection"

      import="oracle.jdbc.OraclePreparedStatement"

      import="oracle.jdbc.OracleResultSet" %><%!  /* <--- there's the blighter there */

       

       

      // Session has to be validated first

      WebAppsContext ctx = WebRequestUtil.validateContext(request, response);

       

       

      if (ctx==null) {

      return; }

      String cookieName = ctx.getSessionCookieName();

       

       

      boolean validSession = ctx.validateSession(cookieName);

      WebRequestUtil.setClientEncoding(response, ctx);

       

       

      %>

      <html>

      <head>

      <title>Launch Apex</title>

      <!-- LaunchApex.jsp -->

      </head>

      <body>

      <%

       

       

      String p_application = request.getParameter("application");

      p_application = ( p_application==null ? "NONE" : p_application);

       

       

       

       

      String p_page = request.getParameter("page");

      p_page = ( p_page==null ? "1" : p_page);

       

       

      String p_item_names = request.getParameter("item_names");

      p_item_names = ( p_item_names==null ? "" : p_item_names);

       

       

      String p_item_values = request.getParameter("item_values");

      p_item_values = ( p_item_values==null ? "" : p_item_values);

       

       

      AppsEnvironmentStore m_env = (AppsEnvironmentStore) ctx.getEnvStore();

       

       

      try {

      String l_launcher = ctx.getProfileStore().getProfile("APEX_HTTP_SERVER");

      l_launcher = l_launcher + "/pls/apex/f?p=" + p_application + ":" + p_page;

      l_launcher = l_launcher + "::APPS:::" + p_item_names + ":" + p_item_values;

       

       

      if (ctx!=null) ctx.freeWebAppsContext();

       

       

      response.sendRedirect(l_launcher);

      } //try

      catch (Exception e) {

      out.println("Exception found :

       

       

      ");

             e.printStackTrace(new PrintWriter(out));

            out.println("

      ");

      } //catch

      %>

      </body>

      </html>

        • 1. Re: APEX auto login from EBS
          Elco

          I see one problem.. I can compile the JSP file but I can't flush it..

          • 2. Re: APEX auto login from EBS
            Tom Petrus

            What are you doing? Is this EBS 11 or 12? If it's 12, and it probably is since you seem to have "FND: APEX URL", why are you trying to use a custom jsp? Your problem is just not being able to redirect from ebs to apex at this point, auto-login or not.

            • 3. Re: APEX auto login from EBS
              Elco

              EBS 12.

               

              We want to automatically login.

               

              Indeed tom, it has something to do with the JSP file. Can the problem be that there is a script error in the JSP file. So that the file can be compiled but not flushed?

               

              I use the correct commando for this (other JSP files can be flushed)

               

              Regards

              Elco

              -----------------------------------------------------------------------------------------------------------------------------------------------

              DEDB $ $FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'LaunchApex.jsp'

              logfile set: /u01/oracle/dedb/inst/apps/DEDB_oracle-fst1lx/logs/appl/rgf/ojsp/ojspc_error.log

              starting...(compiling delta)

              using 10i internal ojsp ver: 10

              synchronizing dependency file:

                loading deplist...8104

                enumerating jsps...8104

                updating dependency...0

              initializing compilation:

                files to compile...1

                eliminating children...1 (-0)

                searching uncompiled...0

              No compilations needed.

               

               

              DEDB $ $FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'LaunchApex.jsp' --flush

              logfile set: /u01/oracle/dedb/inst/apps/DEDB_oracle-fst1lx/logs/appl/rgf/ojsp/ojspc_error.log

              starting...(compiling all)

              using 10i internal ojsp ver: 10

              quick compile:

                files to compile...1

              translating and compiling:

                translating jsps...1/1 in 1s

                compiling jsps...1/1 [failed: 1] in 0s

              Finished!

              • 4. Re: APEX auto login from EBS
                Tom Petrus

                I don't understand why you use the custom jsp. Why don't you just use the functionality outlined in the ebs-apex integration whitepaper for r12? There are no jsp problems in that case since there is none involved. You've posted on this before and I gave a general outline on how to deal with this Apex login automatically with Business suite "SSWA jsp function"

                Aside from the auto-login, which in my opinion is only on the apex side of things, you just need to get your redirect working. So just go through the motions:

                http://www.oracle.com/technetwork/developer-tools/apex/learnmore/apex-ebs-extension-white-paper-345780.pdf

                Does your installation qualify or not and did you apply the required patches.

                If not and you can not or are unwilling to upgrade, then use the r11 whitepaper which outlines the use of a custom jsp. I can't help you there as I'm no wiz on that and did not have to use this - yet. R12 by far is easier, and your own struggle with the jsp is evidence enough.

                • 5. Re: APEX auto login from EBS
                  Elco

                  Hi Tom,

                   

                  The redirection did work with the whitepaper for r12 a few weeks ago.. But then we wanted to get the auto login working with a custom JSP.

                   

                  At this moment I don't get the redirection from the whitepaper working anymore.

                   

                  When I click on the link in EBS it says the following..

                   

                  Error

                   

                   

                  An error occurred when trying to perform the specified action.

                   

                  I can't see what I missing...

                  • 6. Re: APEX auto login from EBS
                    Tom Petrus

                    Ok - so can you not revert then? If it was working you should be able to get it working once again. Delete the JSP and go through the setup steps again for profiles, functions, responsibilities, menus.

                     

                    The auto-login (and I repeat myself) has nothing to do with the redirects. As is shown in the r11 integration, the only code that matters in that setup is code to be ran on the apex login page, and not through a jsp or url.

                    • 7. Re: APEX auto login from EBS
                      Elco

                      Hi tom,immediately

                       

                      I have it working now with the JSP file. the redirection is to the 101 page. I see then immediately this error:

                       

                      ORA-20002: 3402: No valid session exists for this user. Please establish a new session through the login page.

                       

                       

                      This is my 101 page

                       

                       

                      Before header

                       

                      BEGIN

                      apps.ebs_getsession(:P101_USERNAME);

                      :P101_PASSWORD :=

                      apps.XXAPX_SECURITY_PKG.generate_hash

                      (apps.FND_GLOBAL.user_name);

                      IF :P101_PASSWORD IS NOT NULL THEN

                      APEX_CUSTOM_AUTH.login(

                      P_UNAME => :P101_USERNAME,

                      P_PASSWORD => :P101_PASSWORD,

                      P_SESSION_ID => v('APP_SESSION'),

                      P_APP_PAGE => :APP_ID||':1'

                      );

                      END IF;

                      END;

                       

                       

                       

                       

                      Afther submit:

                      wwv_flow_custom_auth_std.login(

                         P_UNAME       => :P101_USERNAME,

                         P_PASSWORD    => :P101_PASSWORD,

                         P_SESSION_ID  => v('APP_SESSION'),

                         P_FLOW_PAGE   => :APP_ID||':14'

                         );

                       

                       

                      Set username Cookie:

                      apex_authentication.send_login_username_cookie (

                          p_username => lower(:P101_USERNAME) );

                       

                      And

                      P101_username

                      P101_password

                      P101_login

                       

                      Hidden

                       

                       

                       

                       

                      APPS.apex_integration_sample_apis.apex_validate_login

                       

                      FUNCTION ebs_authenticate (

                      p_username IN VARCHAR2

                      , p_password IN VARCHAR2) RETURN BOOLEAN

                      AS

                      BEGIN

                      RETURN (apps.FND_WEB_SEC.validate_login

                      (p_username, p_password) = 'Y');

                      END ebs_authenticate;

                      • 8. Re: APEX auto login from EBS
                        Tom Petrus

                        Look, I'll just reiterate myself again. I'm sure you're happy you have your JSP working and all, but given the amount of topics you keep opening regarding apex-ebs it's obvious you struggle with the matter. I do not understand how you are willing to complicate things for yourself so much. You persevere in using the custom jsp while it is not required at all. Why do you want to deviate from all this?

                        The same remark with packages, users, and grants. Do you understand what you are trying to do and what should be done?

                         

                        The error you are seeing here is in the before header process. You are NOT submitting the page, you're simply being redirected to it so it can not be the after submit process. The error is also an EBS error, so logically you should look at ebs_getsession. Do you understand what it is trying to do? It should call the ebs functionality that will retrieve the current EBS session in the browser through the ICX session cookie. It should be a wrapper for wfa_sec.getsession. The package you wrapped this in: what schema is it in, what AUTHID clause does it have? DEFINER?  For all I know, the custom JSP may be a faulty step. I do not have it set up that way so I can't comment on it.

                        • 9. Re: APEX auto login from EBS
                          Elco

                          Hi,

                           

                          Thanks for your answer..

                           

                          The reason why is because the GWY.jsp is also not working and I have read some posts that auto login with GWY.jsp is impossible..

                           

                          I have changed some things but still not working:

                           

                          Apex
                          Get username cookie

                           

                          Begin

                          APEX_EBS.logon;

                          end;

                           

                          CREATE OR REPLACE PROCEDURE APPS.apps_logon

                          IS

                          v_username       VARCHAR2(100);

                          v_password       VARCHAR2(32);

                          BEGIN

                          wfa_sec.getsession(v_username);

                          v_password := xxapx_security_pkg.generate_hash(FND_GLOBAL.user_name);

                           

                           

                          IF v_password IS NOT NULL THEN

                              APEX_CUSTOM_AUTH.login(

                                  P_UNAME       => v_username,

                                  P_PASSWORD    => v_password,

                                  P_SESSION_ID  => v('APP_SESSION'),

                                  P_APP_PAGE    => apex_application.g_flow_id||':1'

                                  );

                          ELSE

                          RAISE_APPLICATION_ERROR(-20001, 'Password is null, session id = '||icx_sec.g_session_id);

                          END IF;

                           

                           

                          END apps_logon;

                          /

                          • 10. Re: APEX auto login from EBS
                            Elco

                            Does somebody know what the problem can be?