Integrating Apex 3.2 with R12 (With Oracle Application Server 10g on the other Machine)

Today, I need to blog out my experience with Apex integration with R12 E Business Suite!!!

How I did: as follows.

As you know how to install Oracle Application Server 10.1.2.0 (Database 10.1.0.4)

1. Download Application Server 10g from OTN
2. Unzip the content. It will create 4 Disks (Disk1, Disk2, Disk3 and Disk4)

3. From Disk1 run
$./runInstaller

This will bring up the welcome screen. Select the Oracle home and other requisites. Later,
Select to Oracle Application Server Infrastructure 10g (10.1.2.0.2)

It will create one new database, (10g, Version 10.1.0.4) and New OID, Oracle Application server single-sing on, metadata repository and others. (I will detailedly explain how we can configure Application server in a separate blog)

Finish the installation. This will bring you basic HTTP server (OHS managed by OPMN)

This is what we want right at the moment.

Write a profile for database as follows.
export ORACLE_HOME=/oracle/ASHOME/
export ORACLE_SID=apex
export PATH=$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export ORACLE_OWNER=oracle
export TNS_ADMIN=$ORACLE_HOME/network/admin
export CLASSPATH=$ORACLE_HOME/jdbc/lib/classes12.zip
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LDPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32

So my ORACLE_HOME is /oracle/ASHOME

Now the Application server is ready for operation. Check the Application server page correctly displays using
http://hostname:port (here my port no is 7778 ie. Apache Port from Application Server 10g)

Now, Apex Installation. Download apex_3.2.zip from here
Unzip the apex_3.2.zip into a separate direcory. It will create a folder called apex. My location is /oracle/apex

4. Connect as sys user.
5. Create a tablespace for apex. i created apex32 with datafile apex32
6. Run apexins.sql

SQL>@apexins.sql apex32 apex32 temp /i/
where apex32 is the tablespace name
where apex32 is the datafile name
temp is the temporary tablespace
/i/ is the location of the image folder to be given in the configuration file. (simply give /i/)

Successful installation will have the following content at the end.

Thank you for installing Oracle Application Express. Oracle Application Express is installed in the FLOWS_030200 schema.

The structure of the link to the Application Express administration services is as follows: http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql) http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)The structure of the link to the Application Express development interface is as follows: http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)Installation logfile will be located under /oracle/apex as installYYYY-MM-DD_HH24-MI-SS.log.

7. Change Password for ADMIN Account
- Change directory to apex
- Connect to DB as sys user and execute
- @apxchpwd

Configure HTTP Server
1. Unlock the APEX_PUBLIC_USER account
alter user APEX_PUBLIC_USER account unlock
alter user APEX_PUBLIC_USER identified by (new password)

2. Copy APEX Images
- Copy /oracle/apex/images to /oracle/ASHOME/Apache/Apache/images/ .
This is the /i/ location where we installed our images.
$cp -rf /oracle/apex/images /oracle/ASHOME/Apache/Apache/images
Adding DAD entry in dads.conf file (in /oracle/ASHOME/Apache/modplsql/conf/)

Alias /i/ “[ Put the IMAGE_LOC Value] “ as /oracle/ASHOME/Apache/Apache/images
AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString hostname:port:SID (here my entry is genius:1521:apex (MY SID Name is apex)
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword APEX_PASSWORD (here my entry is welcome)
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all

</Location>

Bounce the Apache Server as
/oracle/ASHOME/opmn/bin/opmnctl stopproc ias-component=HTTP_server

or if you want to stopall, then give ./opmnctl stopall. to bounce all the services.

Now Check with
http://hostname:port:/pls/apex/apex_admin (should bring you the welcome page of the Apex with images. Here my port is 7778 as installed in Application Server 10g OHS server)
if images are not coming, then further navigation is not possible. So check with the dads.conf. Please be sure to put /" at the end of the image location. I Forget that slash, later found from the error log.



If you face any errors, look into ORACLE_IAS_ORACLE_HOME/Apache/Apache/logs/error_log.xxxxxxxxxx

Now Oracle E BIZ R12 part.
Copy the apex3.2.zip file to the server where you have to install the Application Express.
Unzip the content into a directory
Source the database environment file

1. Connect as sys user from the directory where you unzipped apex files.
2. run @apexins.sql into the database (my R12 database version is 10.2.0.3)
after completion,
unlock the APEX_PUBLIC_USER account.
Change the password using @apxchpwd.sql from apex directory.

Login to the Application server 10g server.
1. Edit the dads.conf file from /Apache/modplsql/conf/
Now add the entry

<Location pls/apex_R12>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString hostname:port:SID (here my entry is fserver:1521:VIS (MY SID Name is VIS in the EBIZ server)
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword APEX_PASSWORD (here my entry is welcome)
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all

</Location>

Now bounce the Apache of the Application server.

Test the Apex Installation by
http://hostname:port/pls/apex_r12/apex_admin

Troubleshooting is most vulnerable part here.
1. To check wwv_flow_fnd_user schema is locked or not
use
alter session set current_schema=flows_030100
/
select user_name':'account_locked':'security_group_id from wwv_flow_fnd_user
/

2. TO change the Admin Password:
Changing the Password for the ADMIN Account
In a new installation of Oracle Application Express, or if you are converting a runtime environment to a development environment, you must change the password of the internal ADMIN account. In an upgrade scenario, the password will be preserved and carried over from the prior release.
To change the password for the ADMIN account:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:
On Windows:
SYSTEM_DRIVE:\ sqlplus /nolog
connect sys as sysdba

On UNIX and Linux:
$ sqlplus /nolog
connect sys as sysdba
When prompted, enter the appropriate password.
2. Run apxchpwd.sql. For example:
@apxchpwd
When prompted enter a password for the ADMIN account."

This does not require to change the password in the
ORACLE_HTTPSERVER_HOME/Apache/modplsql/conf/dads.conf File.

3. If You are getting ADMIN/ADMIN_PASSWORD as "Invalid Login Credential Error", then
run
alter user FLOWS_030200 account unlock;

connect as FLOWS_030200 and execute the procedure
begin
wwv_flow_api.set_security_group_id(p_security_group_id=>10);

wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'admin2',
p_email_address => 'myemail@mydomain.com',
p_web_password => 'admin2') ;

end;
/

Please be sure
alter user FLOWS_030200 lock;

Then try with "admin2" and "admin2" as password. This should work...

Stuffs to Read
http://download.oracle.com/docs/cd/E10513_01/doc/index.htmTo Remove the APEX installed schema from Oracle database, Run
from apex installed directory
sql>sqlplus "/as sysdba"
sql>@apxremov.sql

Please use this link for........

Apex 4.0.2 Configured with Oracle EBS 12.1.2

http://balajiabhi.blogspot.in/2010/12/apex-402-configured-with-oracle-ebs.html