1 2 3 4 Previous Next 46 Replies Latest reply on Feb 14, 2014 5:31 PM by Ally_p

    APEX integration with EBS R12.1.1

      Dear All,

      As i need to integrate APEX and EBS R12.1.1 without SSO

      For that i followed the below steps described in the following link


      The steps are

      Set up in Apex:

      1. Create a blank page in OAE application, e.g. P2.

      2. Edit page attribute and set the Security - Authentication to ‘Page is Public’.

      3. Create a branch in this page, as follow:

      Branch Type Branch to Page Accept Processing (not common)
      Branch Point On Load: Before Header
      Action – Page 101 (This is my login page id)

      Set up in EBS:

      1. Create a profile under Application Developer Responsibility as follow:

      Application <whatever>
      User Profile Name Oracle Application Express Launcher
      Description Oracle Application Express Launcher

      2. Set above profile value under System Administrator Responsibility -> Profile -> System, and search for the ‘OAE_LAUNCHER’. Set the value at Site Level as:

      http://<hostname>:<Apex port>/pls/apex

      3. Create a function under System Administrator Responsibility -> Application -> Function, as follow:

      Function Name LAUNCH_APEX
      User Function Name Launch ApEX Application
      Property type SSWA plsql function
      Web HTML OAE_PKG.LaunchOAE

      4. Create a Web PL/SQL entry under System Administrator Responsibility -> Security -> Web PL/SQL, as follow:

      Name OAE_PKG
      Type Package

      5. Create an entry in any menu that need to use this link, as follow:

      Prompt Launch ApEX Application
      Function Launch ApEX Application
      Description Launch ApEX Application

      *6. Compile the OAE_PKG package in EBS database*

      procedure LaunchOAE(parameters in varchar2) is

      l_apex_launcher varchar2(2000);
      l_apex_launcher_profile varchar2(2000);
      l_userid applsys.FND_USER.user_id%type;
      l_username applsys.FND_USER.user_name%type;
      l_launcher_params VARCHAR2(2000);
      l_resp_id NUMBER;
      l_resp_appl_id NUMBER;
      l_security_group_id NUMBER;
      l_org_id NUMBER;


      l_apex_launcher_profile := fnd_profile.value('OAE_LAUNCHER');

      if l_htmldb_launcher_profile IS NULL Then
      htp.p ('Please contact System Administrator. ');
      htp.p ('Profile - OAE_LAUNCHER is null') ;
      end if;

      l_userid := FND_GLOBAL.USER_ID;
      l_username := FND_GLOBAL.USER_NAME;
      l_resp_id := FND_GLOBAL.RESP_ID;
      l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
      l_security_group_id := FND_GLOBAL.SECURITY_GROUP_ID;
      l_org_id := FND_GLOBAL.ORG_ID;

      l_launcher_params := l_launcher_uri := '/f?p=101:2:::NO::P101_USERNAME,P101_PASSWORD,P101_RESP_ID,P101_RESP_APPL_ID,P101_SECURITY_GROUP_ID,P101_ORG_ID:' || l_username || ',' || l_password||','||l_resp_id||','||l_resp_appl_id||','||l_security_group_id||','||l_org_id;
      l_apex_launcher := l_apex_launcher_profile || l_launcher_params;


      end if;

      when others then
      end LaunchOAE;

      I have finished upto 5 steps but in step 6 while compiling the procedure i'm getting an error ORA-00900: invalid SQL statement

      could anyone help me to resolve this error also i have followed Rod West document

      If any other stepwise document available for Apex & EBS R12 Integration please Share.

        • 1. Re: APEX integration with EBS R12.1.1
          Hi ,

          Did you get a solution for the compilation error?

          • 2. Re: APEX integration with EBS R12.1.1
            Dear Chitra,

            Can u clearly explain what is your requirement.

            We have successfully integrated R12 EBS with APEX.

            • 3. Re: APEX integration with EBS R12.1.1

              Could you please provide more information on Integrating APEX with EBS R12 and authentication method to use EBS credentials for APEX login.

              We have a requirement to open an APEX page from Oracle Form screen.

              • 4. Re: APEX integration with EBS R12.1.1
                Hi Shesh,

                What kind of information you want ? For More info Follow the below Document


                If you have any other doubt in this document you can raise your queries.


                Mark the answers appropriately

                • 5. Re: APEX integration with EBS R12.1.1
                  Hi Saro,

                  I am working on the mock test using the document from Rod West. Will be back if I stumble upon something.

                  Thank you very much.

                  • 6. Re: APEX integration with EBS R12.1.1
                    Hi Saro
                    Can you please describe how you were able to call apex from the R12 menu as "SSWA plsql function" is no longer supported in R12 and PLSQL functions are obsolete in R12

                    • 7. Re: APEX integration with EBS R12.1.1
                      Hi Coenraad,

                      SSWA plsql function, is just a property that you are going to set while creating a FUNCTION under SYSTEM ADMINISTRATOR RESPONSIBILITY....

                      Whether you want to integrate APEX with EBS 11i or R12....

                      As of in R12, the JSP that is given in the Rod west document will call the APEX page soon after clicking the menu in EBS...

                      So Compile the JSP correctly by following the steps given in the document, if u compiled successfully, then only the APEX page will be redirected by clicking the MENU in the EBS..

                      Make sure U compile it successfully.....

                      All izz Well

                      REWARDS: Please remember to mark helpful or correct posts on the forum, not just for my answers but for everyone!

                      Edited by: GTA on Jan 26, 2011 10:01 PM

                      Edited by: GTA on Jan 26, 2011 10:02 PM

                      Edited by: GTA on Jan 26, 2011 10:43 PM
                      • 8. EBS 11.5 integration with APEX.
                        I am following cobat consulting pdf , i am trying to integrate the EBS and apex.

                        While creating the function i am using sswa plsql function type but this function is being over ride once i query it back again .
                        When i searched for it , i came to know that we must specify the Host name .

                        My fucntion is

                        create or replace procedure  apex_launch+
                        OWA_UTIL.mime_header('text/html', false);+


                        END apex_launch;+

                        But for the url
                        http://detux4.aam.net:8680/apex/f?p=105:101: what should be my host name .

                        Kindly! help.

                        Nandini Thakur.
                        • 9. Re: APEX integration with EBS R12.1.1
                          I followed cobalt white paper but till now i am able to call the apex form from ebs.

                          how can i pass the login credential from EBS to apex.

                          • 10. Re: APEX integration with EBS R12.1.1

                            I'm also trying to implement R12 integration with Apex to automatically login a user from EBS. I'm using a document by 'Rod West, Cabot Consulting'. This issue is that it does not log the user into the apex application but it redirects it to the R12 EBS login page.

                            If I change the before header process in Apex from, I hard coded the username:
                            :P101_PASSWORD :=
                            XXAPX_SECURITY_PKG.generate_hash (FND_GLOBAL.user_name);
                            IF :P101_PASSWORD IS NOT NULL THEN
                            P_UNAME     => :P101_USERNAME, P_PASSWORD     => :P101_PASSWORD, P_SESSION_ID => v('APP_SESSION'), P_APP_PAGE     => :APP_ID||':1'
                            END IF;
                            EXCEPTION WHEN OTHERS THEN NULL; END;


                            :P101_USERNAME := 'TEST';
                            :P101_PASSWORD :=
                            XXAPX_SECURITY_PKG.generate_hash ('TEST');
                            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; EXCEPTION WHEN OTHERS THEN NULL; END;

                            It works fine. It seems the issue is with the wfa_sec.getsession procedure, when this is used then it redirects it from apex to EBS login.

                            What could be the issue? Our domain/address for Apex and EBS apps are different, could that be the issue and how do we fix this?

                            • 11. Re: APEX integration with EBS R12.1.1
                              Rod West

                              Using wfa_sec.getsession procedure relies on the EBS and Apex being in the same domain. The wfa_sec.getsession procedure reads the ICX cookie and if the procedure is called in a different domain then cookie is not available and so the EBS logon page is displayed to set the cookie.

                              Ideally you should use Oracle SSO for both EBS and Apex then Apex will not need to read the ICX cookie. Another option is to pass the EBS username and an MD5 hash to secure the user in the Apex URL. The username and hash are then used to log in to Apex. Another option is to use a custom table in the database to hold the connection details which is updated by the EBS launch function and read by the Apex login page.

                              Rod West
                              • 12. Re: APEX integration with EBS R12.1.1
                                Hi Rod West Glad to see you in the form,

                                We all are trying your way of integrating EBS with APEX. It is the best method so far. I have integrated EBS R12 with APEX 4.0 following your document.

                                But i couldnt able to achieve SSO feature with it, and that is more pending still now. I need your support inorder to achieve it.

                                What is my problem is, when i clicked the menu in the EBS means, it is redirecting to the APEX page.

                                But the APEX login page is appearing for it. But i dont need to redirect to the APEX login page soon after clicking the menu, it has to redirect to the APEX first page soon after clicking the menu in EBS.

                                Due to that, it is asking for the double login credentials one is EBS login page and the other is APEX login page.

                                How i make the APEX to recognize the users of the EBS, so that it will accept only the EBS login credentials and it should not ask for the APEX login page and it has to go to directly to the APEX first page(not the login page) soon after clicking the menu.

                                How to proceed with it Rod. I need your help.

                                I need your stepwise suggestion inorder to achieve it.



                                Mark Answers Promptly
                                • 13. Re: APEX integration with EBS R12.1.1
                                  Hi Rod

                                  Thanks for the update and the valuable information. I'm now trying to add the username in the jsp as we are on R12 and add that to the Apex URL as a parameter. If I don't come right with that I'll try the custom table option.


                                  • 14. Re: APEX integration with EBS R12.1.1

                                    I'm having trouble trying to get the username in the JSP. How can I use or fetch the fnd_global.user_name in the jsp. I was trying to do this:
                                    String outParam = null;
                                    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
                                    String sql = "select fnd_global.user_name from dual";
                                    PreparedStatement pstmt = conn.prepareStatement(sql);
                                    ResultSet rs = pstmt.executeQuery();
                                    outParam = rs.getString(1);

                                    Is this correct or is there an easier way to get the current username in the JSP for me to include it in the apex url?

                                    Looking forward to any help.

                                    1 2 3 4 Previous Next