APPS Blogs

Oracle Reports using BI Publisher

OracleAppsBlog - Mon, 2008-04-14 18:32
Categories: APPS Blogs

Oracle Transparent Gateways - General Description - Part I

Aviad Elbaz - Sun, 2008-04-13 17:15

A lot of companies have several applications based on more than one database system (e.g DB2, SQL Server, Sybase, etc).
Each database system store its own data and naturally there's a need to share data among the various heterogeneous database systems.

Oracle, starting with Oracle Database 9i, offers the "Oracle Transparent Gateways" (Oracle Database Gateways) to allow integration of Oracle database with non-Oracle databases.
Unlike "Oracle Generic Connectivity" that provide a generic solution to connect any ODBC/OLEDB compliant non-Oracle system using ODBC and OLEDB standards, the "Oracle Transparent Gateways" are solutions specifically tailored for each target non-Oracle database system.
The "Oracle Transparent Gateways" communicates using the target database native interface, it's make it possible to access to non-Oracle systems as if they were Oracle databases.

The Transparent Gateway solution composed of two parts:

  • Heterogeneous Services (HS) - this is a general integrated component that make it possible to connect to non-Oracle systems from Oracle database
  • Oracle Database Gateways (agent) - these are specific tailored agents for non-Oracle systems that make it possible to interacts with the target non-Oracle system.

Heterogeneous Services (HS)

This is a generic component for connecting to non-Oracle systems.
It's an integrated component of the database that "extends the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information" (Oracle Doc').

The following services are provided by the Heterogeneous Services (HS):

  • Transaction service
    Responsible for establishing authenticated connection when the non-Oracle system is accessed and close the connection when session end.
    Also responsible for global data integrity using two phase commit protocol, even for non-Oracle systems that do not support two phase commit natively.
  • SQL Service
    Provide the translation capabilities: SQL & data dictionary translations.
    The SQL services uses an information arrived from the Gateway to translate Oracle SQL to the appropriate SQL dialect of the non-Oracle system. Also, references to data dictionary tables in a query will be rewrite by the SQL Service and result with a result set as from Oracle database.
    ** Data type translation performed by the Gateway.
  • Procedural Service
    An interface for executing stored procedures on non-Oracle system.
  • Pass through SQL
    A mechanism for issuing a SQL statement against the non-Oracle system. It is useful when the statement/function/procedure are not supported by the Gateway.

Oracle Database Gateways (agents)

This component responsible for the interface to the remote non-Oracle system.
It's also responsible for SQL mappings and data type conversions.
The Gateway interacts with Heterogeneous Services to make it possible to transparently connect from an Oracle Database to a non-Oracle System.
In contrast to the HS (Heterogeneous Services) which is a generic component, the Gateways are tailored specifically for each target non-Oracle system.
There are Gateways for many systems such as: DB2, Sybase, Informix, SQL Server, IMS, VSAM, Adabas, Ingres, Teradata, to name a few.
The Gateway can be installed on the same server like the non-Oracle system or on the same server like the Oracle system or on a separate server.

Next post I'll show an example of connecting and retrieving data from a SQL Server database to an Oracle database using Oracle Transparent Gateway for Microsoft SQL Server including all configuration required for Transparent Gateway and the source Oracle System.

Related Documents for more information:

- Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit)

- Database Gateways Technical Whitepaper

You are more than welcome to leave a comment.

Aviad

Categories: APPS Blogs

Accrual Balance Display in Self Service HR

RameshKumar Shanmugam - Fri, 2008-04-11 19:18
One of the comon requirement that often times come in the Absence Managment is HR team want their employee to be able to see their available accrued/PTO balance.
As an employee it is always good to know how much vacation balance an employee is left with

Here is a simple setup that can enable your employee to view the available balance through the Self Service absence Management

Step 1: Define the Absence element
Step 2: Link the Element based on the Eligibilty Criteria
Step 3: Define the Absence Type
Step 4: Define the Accrual Plan
Step 5: Attach the Accrual Plan to the Employee
Step 6: Complete the setup for the Self Service HR
Step 7: Make sure employee is able access the absence Management Functionality

Setup steps to enable the Entitlement Balance in the Absence Management

Step 1: Create an Element set with the type as ‘Run Set’
Step 2: Attach the element set to the Profile option HR: Accrual Plan Element Set Displayed to User at the Responsibility level (Employee Self Service)
Step 3: Bounce the Appache

Now Navigate to the Employee Self Service > Absence Management > (T) Entitlement Balance


Try this out
Categories: APPS Blogs

LinkedIn Oracle Contractors Group

OracleAppsBlog - Thu, 2008-04-03 17:46
Categories: APPS Blogs

Forgot your Password?

Aviad Elbaz - Tue, 2008-03-25 02:58

Almost every website that uses username & password have a "forget password" functionality to retrieve users passwords, and so also the Oracle E-Business Suite.

This is a very useful functionality since it reduces the number of SR's opened to the helpdesk team regarding login problems and moreover satisfying the customers which can get a new password in a very short time with no helpdesk intervention.

The implementation of this functionality is very simple and easy.
To enable it you should:

  1. set the profile "Local Login Mask" to the current value plus 8 (e.g. current value is 32 -> set value to 40)
  2. Bounce Apache

The "Local Login Mask" profile used to customize some attributes of the login page (AppsLocalLogin.jsp), one of them is the "forgot your password" link.
You should set the value of this profile to the sum of all attribute's mask values you are interested in.

The full attributes list is:

Attribute

Mask Value Binary value Hint for Username 01 00000001 Hint for Password 02 00000010 Cancel button 04 00000100 Forgot Password link 08 00001000 Registration link 16 00010000 Language Images 32 00100000 Corporate Policy Message 64 01000000

 

Setting the Forgot Password link mask value will add the following TIP to the login page:

The reset password process:

- Click on "Forgot your password?" link will ask for a username to which reset the password.

- After typing the username and click OK, a new workflow process is started (Item type UMXUPWD) and you'll get this confirmation message:

- Shortly you'll get this email - "Password reset required approval" (expired after 4 hours).

- Click on "Approve" to confirm you are interested in a new password.

- Shortly you'll get an email with a temporary password which you have to change on first login.

Very nice and easy to implement functionality, which could be very beneficial.

Related Note 399766.1 - Reset Password Functionality FAQ

You are welcome to leave a comment

Aviad

Categories: APPS Blogs

Job Vs Position

RameshKumar Shanmugam - Sun, 2008-03-09 16:23
As a Functional consultant the first thing that we should decide before we can design the solution for a customer is whether the system going to be a Single Business Group or Multi Business Group.

The second main important thing we need to decide is whether the customer is going to use Job or position.

When we put this question to the customer the first expected question for the Customer side would be what is the difference between Job and Position

The content in this blog is more of my own view and the simplistic approach I always like. review the documentation before you can decide on the approach you want to take

To explain it in a very high level
Jobs are Generic Title or Role within a Business Group, independent of any single organization. Required. Usually more specific if positions are not used.

Position are Specific occurrence of one job, fixed within an organization. Not required

If you are in US legislation your job will drive you FLSA and EEO reporting. Personally I feel maintaining the Position is hard in an unstructured organization. Position Hierarchy will suite for the University/college/School and Government Organization

The Maintenance is more in the Position hierarchy than in the Job. If your customer feel they need less maintenance activity then you should recommend Job not the Position
Categories: APPS Blogs

Upgrade from Jinitiator 1.3 to Java Plugin 1.6.0.x

Aviad Elbaz - Fri, 2008-03-07 05:51

Lately Oracle announced the end of Error Correction Support for Jinitiator 1.3 for E-Business Suite 11i, effective July 2009.

This is the sign it’s about time to upgrade to the native Java Plug-in… :-)

Among other things, one of the main advantages of upgrading from Jinitiator to the native Java Plug-in is the prevention of conflicts between them.

This upgrade is great news to all are working with Oracle Discoverer Plus (with Java plug-in) and Oracle EBS 11i (with Jinitiator) and experiencing those conflicts.

I’ll skip all the others advantages and disadvantages of upgrading to Java Plug-in as they are well described in Steven Chan’s post - Jinitiator 1.1.8 To Be Desupported for Apps 11i and in Metalink Note: 290807.1 - Upgrading Sun JRE with Oracle Applications 11i.

So I will focus on the upgrade process itself - step by step.

I tested the upgrade on the following test environment:

  • EBS 11.5.10.2
  • Database 10.2.0.3
  • ATG Rollup 5
  • Developer 6i patchset 18
  • OS RHEL4.

Be aware that before upgrading to Java Plug-in you must upgrade to Developer 6i patchset 18 or later (currently the latest patchset is 19).

* You can use my previous post in order to Upgrading Developer 6i with Oracle Apps 11i to patchset 18.

  1. Download JRE plug-in Oracle E-Business Suite interoperability patch - 6863618
  2. Download the Sun JRE Plug-in 
    • Select Java Runtime Environment (JRE) 6 Update X (select the latest available update, currently it’s 5)
    • Select Windows offline installation, multi-language
  3. Rename the downloaded installation file jre-6_uX-windows-i586-p.exe to j2se1600x.exe
    In my case rename jre-6_u5-windows-i586-p.exe to ==>> j2se16005.exe
  4. Copy the j2se1605.exe file to $COMMON_TOP/util/jinitiator on the Apps Tier node
  5. If you are on Developer 6i patchset 18 you should apply forms patches 6195758 & 5884875.
    ** Skip this step if you are on Developer 6i patchset 19.
    • Download Patches 6195758 & 5884875
    • Apply patch 6195758
      • Stop all applications processes by adstpall.sh
      • Unzip p6195758_60827_GENERIC.zip
      • cd 6195758
      • cp -r $ORACLE_HOME/forms60/java/oracle/forms/
        handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/
        handler/UICommon.class.PRE_BUG6195758
      • cp -r $ORACLE_HOME/forms60/java/ oracle/forms
        /handler/ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/
        handler/ComponentItem.class.PRE_BUG6195758
      • cp oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/
        handler/UICommon.class
      • cp oracle/forms/handler/ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/
        handler/ComponentItem.class
    • Apply Patch 5884875
      • Unzip p5884875_60827_GENERIC.zip
      • cd 5884875
      • cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/
        engine/Main.class.PRE_BUG5884875
      • cp -r $ORACLE_HOME/forms60/java/ oracle/forms/
        handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/
        handler/AlertDialog.class.PRE_BUG5884875
      • cp oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
      • cp oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/
        engine/AlertDialog.class
      • Run adadmin -> Generate Applications Files menu -> Generate product JAR files
  6. Apply the Interoperability patch 6863618
    • Make sure all applications processes are down
    • Enable maintenance mode:
      Execute adadmin -> Change Maintenance Mode (5) -> Enable Maintenance Mode (1)
    • Unzip p6863618_11i_GENERIC.zip
    • Cd 6863618
    • Run adpatch to apply patch driver u6863618.drv
    • cd [PATCH_NUMBER]/fnd/bin
    • Execute the following command where X represent the update number:
      $ txkSetPlugin.sh 1600X
      In my case:
      $ txkSetPlugin.sh 16005
    • Disable maintenance mode:
      Execute adadmin -> Change Maintenance Mode (5) -> Disable Maintenance Mode (2)
  7. Start all applications processes by adstrtall.sh
  8. Verify installation by sign in Oracle EBS and select a forms based responsibility.

For those who worry about performance, take a look at this benchmark:
Benchmark comparison test with Jinitiator 1.3.1.23 and Java Plug-in 1.5.0_05 – performance whitepaper

For more information - Note: 290807.1 - Upgrading Sun JRE with Oracle Applications 11i

Aviad

Categories: APPS Blogs

UTL_FILE_DIR issue after applying patch 5985992 'TXK AUTOCONFIG RUP Q'

Aviad Elbaz - Mon, 2008-02-04 09:17

Do you have more than 240 characters in your utl_file_dir?

if so, you should read this before applying patch 5985992 'TXK AUTOCONFIG RUP Q (Jul/Aug 2007)'.

After applying this patch, AutoConfig on database tier failed with the following error:

[PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: [RDBMS_ORACLE_HOME]/appsutil/install/[context_name] 
      afdbprf.sh              INSTE8_PRF         1

AutoConfig is exiting with status 1

It wasn't clear why afdbprf.sh failed so I ran it manually from ssh terminal and I got this:

$ [RDBMS_ORACLE_HOME]/appsutil/install/[context_name]/afdbprf.sh

afdbprf.sh started at Tue Jan 29 17:43:21 IST 2008

The environment settings are as follows ...

       ORACLE_HOME : ....
       .........
       .........

Application Id : 0
Profile Name : BIS_DEBUG_LOG_DIRECTORY
Level Id : 10001
New Value : /usr/tmp
Old Value : /usr/tmp
declare
*
ERROR at line 1:
ORA-12899: value too large for column
"APPLSYS"."FND_PROFILE_OPTION_VALUES"."PROFILE_OPTION_VALUE" (actual: 486,
maximum: 240)
ORA-06512: at line 44
ORA-06512: at line 139

Looking into afdbprf.sql  (executed by afdbprf.sh) reveals the problem:

.......

--Setting  BIS_DEBUG_LOG_DIRECTORY
           set_profile(0, 'BIS_DEBUG_LOG_DIRECTORY',
                       10001, 0,
                       '&BIS_DEBUG_LOG_DIR',
                       NULL);

-- This profile option was earlier set in addbprf.sql via bug 2843457, Now moved here for bug 5722442
--
-- Set up UTL_FILE_LOG profile option
--
          set_profile(1, 'UTL_FILE_LOG',
                      10001, 0, '/usr/tmp,........[more than 240 characters..... :-) ]',
                      NULL);

In order to fix bug 5722442, the UTL_FILE_LOG updated with the value of s_db_util_filedir from the database context file and a new bug was created (not a bug according to Bug 6404909).

If the s_db_util_filedir contains more than 240 characters it can't be stored in a profile since the PROFILE_OPTION_VALUE column defined as varchar2(240).

The s_db_util_filedir initialized when creating the database context file by adbldxml.pl, and set up with the value of utl_file_dir database parameter.

I'm not sure why it should be updated with all directories within utl_file_dir and not with the relevant directories only...?! 
The UTL_FILE_LOG profile (or "Stored procedure log directory") wasn't updated with AutoConfig before applying this patch, so I'm not sure regarding the purpose of this profile.
Before applying this patch I have this profile set to a directory that doesn't exist...

The solution according to Note:458511.1 - "After patch 5985992 AutoConfig On Database Tier Fails with script afdbprf.sh" is to change the value of s_db_util_filedir in the database context file to a value less the 240 characters and run AutoConfig again.

And it works, of course...

What should I do if I my utl_file_dir contains more than 240 characters?!

Actually I don't have a good answer to this question but I will try to locate all the places on which this issue might affect when s_db_util_filedir will be updated with a value less than 240 chars.

  1. AutoConfig on the DB Tier creates the [SID]_APPS_BASE.ora file under $RDBMS_ORACLE_HOME/dbs (if it's not already exist), which contains the utl_file_dir database parameter generated respectively to the s_db_util_filedir from the database context file.
    So if you plan to rebuild your init.ora with AutoConfig you will need to update the utl_file_dir manually.
  2. When executing adbldxml.pl on DB Tier to rebuild the database context file, the s_db_util_filedir will be filled with the utl_file_dir database parameter - that might contain more than 240 chars.
    Therefore, before executing AutoConfig you should edit the new context file and shorten the value of s_db_util_filedir.
  3. The UTL_FILE_LOG profile ("Stored procedure log directory")  will be updated with s_db_util_filedir shortened value.

You are welcome to leave a comment or update with additional information regarding this issue.

Aviad

Categories: APPS Blogs

Oracle Discoverer Query Prediction functionality and Performance

Aviad Elbaz - Tue, 2008-01-22 12:21

Lately we noticed that our Discoverer reports runs very slow.
Actually, the problem wasn’t the Discoverer reports query but the query prediction that for some reason took so long.

What is Query Prediction in Discoverer?

“Discoverer includes functionality to predict the time required to retrieve the information in a Discoverer query.
The query prediction appears before the query begins, enabling Discoverer users to decide whether or not to run the query.
This is a powerful facility that enables Discoverer users to control how long they wait for large reports.”
(from Oracle doc’)

The query prediction is the elapsed time while the following message appeared on the bottom left of Discoverer Desktop window: "Determining query time estimate".

For each report we tested, we found query prediction runs 30%-50% (!!!) from the report’s total run time.

Next phase was to start a SQL trace on Discoverer session to see what actually happens when running a Discoverer report.

This is the relevant section from the SQL Trace:

SELECT QS_ID, QS_COST, QS_ACT_CPU_TIME,
       QS_ACT_ELAP_TIME, QS_EST_ELAP_TIME,
       QS_CREATED_DATE, QS_OBJECT_USE_KEY,
       QS_NUM_ROWS,QS_STATE
FROM [EUL_USER].EUL5_QPP_STATS WHERE  QS_COST IS NOT NULL
AND    QS_OBJECT_USE_KEY = :OBJECTUSEKEY
ORDER BY QS_CREATED_DATE DESC


As you can see, the query prediction functionality tries to retrieve statistics information from EUL5_QPP_STATS and it takes 35 seconds. (total time for this report is 55 seconds).

The query prediction based, among other things, on the query prediction statistics table – EUL5_QPP_STATS.
This table records query prediction statistics while running Discoverer reports.

There is no logic by estimating query run time longer than the report’s query itself…

Since the query prediction functionality is important to our users we avoid from disable this functionality (by setting the QPPEnable to 0).
Furthermore, I found that we have statistics data in this table from 7 years ago… 
There is no meaning to hold these statistics…

I tried to find information about purging the EUL5_QPP_STATS and I found this: “How to delete old query prediction statistics” in Oracle® Business Intelligence Discoverer Administration Guide 10g Release 2 (10.1.2.1)

There is a SQL script at [ORACLE_ HOME]\discoverer\util\eulstdel.sql – that deletes all query prediction statistics that were created before a specified date.

Great!
I executed this sql on my database, gave 90 days as a parameter and it deleted 460,000 (from 468,000) rows.
I ran a Discoverer report again, but still query prediction takes too long, same like before.
I checked the explain plan and the cost of the above SQL and it remains the same.
I tried to gather statistics on EUL5_QPP_ENABLE table and rebuild its indexes but cost become higher… (More than 103, something like 800…).

I had no choice but rebuild the EUL5_QPP_ENABLE table (by export, drop table and import).

After recreation of EUL5_QPP_STATS table I ran a Discoverer report again and query prediction takes insignificant time, almost nothing…  :-)

This is from the trace I took after:

SELECT QS_ID, QS_COST, QS_ACT_CPU_TIME,
       QS_ACT_ELAP_TIME, QS_EST_ELAP_TIME,
       QS_CREATED_DATE, QS_OBJECT_USE_KEY,
       QS_NUM_ROWS,QS_STATE
FROM [EUL_USER].EUL5_QPP_STATS WHERE  QS_COST IS NOT NULL
AND    QS_OBJECT_USE_KEY = :OBJECTUSEKEY
ORDER BY QS_CREATED_DATE DESC


The elapsed time for this sql reduced to 0.05 seconds!! (was 35 sec’ before)


Sql cost reduced from 103 to 31!

I checked this issue on Discoverer Desktop 10g (10.1.2.2) but it is relevant to the web tools (Discoverer viewer and Discoverer Plus) as well, since the query prediction functionality exist in these tools like in the client version.

You are welcome to leave a comment.

Aviad

Categories: APPS Blogs

R12 Suitability Matching functionality

RameshKumar Shanmugam - Sat, 2008-01-19 18:02
Suitability Matching Functionality is the new functionality available in both Employee and Manager Self Service.
To explain this is in a very common language - this function helps the employee to find the opportunity in the organization that suites his role and competency.The employee can compare the various competency that is desired and essential for a particular role
For the manager Self service this function helps them to find the employee / applicant based on the competency and the role


List of Options available in the Manager Self Service




In the manager Self Service lets take an example of searching the employee/contingent worker/an applicant based on the competency profile and View it in a Graph for analysing the competency for the Job

Start with the option 'Find Suitable People by Competency'

Add the list of competency that you want to search for



Once you select the continue button, you will be able to list of people that match your competency requirement



Click the button Graph people by Competency button that will help you out in the viewing the graph based on the Min , max and the personal proficiency level



Try this out!!!
Categories: APPS Blogs

Agile PLM 9.2.2.1 – Part III – Application Node Installation

Aviad Elbaz - Wed, 2008-01-09 08:48
This is the 3rd (and last..) post about Oracle Agile 9.2.2.1 installation.
In this post we will see the Agile application node installation step by step including all Agile application required components.

Previous related posts:
- Agile PLM 9.2.2.1 – Part I
- Agile PLM 9.2.2.1 – Part II – Database Node Installation


The Agile Application node installation composed from the following steps:
- Complete all application installation pre requisites
- Oracle Application Server 10.1.2.0.2 Installation
- Oracle Applications Server Patch
- Agile Application Installation
- Agile Viewer Installation
- Deploy Agile application on Oracle Application Server
- Verify Installation
- Configure IIS as a Proxy Server for Agile PLM
- Configure File Manager with IIS
- Verify File Manager installation


Prerequisites

1) Copy Platform directory from Disk2 to Disk1 to the setup.exe level
2) Make sure Microsoft IIS (Internet Information Services) is installed on this box.

*** It is important to install MS IIS before proceeding with the Oracle AS 10.1.2.0.2 installation, otherwise you might get into port conflict between IIS and Oracle AS 10.1.2.0.2.


Oracle Application Server 10.1.2.0.2 Installation

1) Run installer - setup.exe (from Disk1 directory)


2) Oracle Home destination:
a. Name: oracleas1
b. Path: d:\OraHome_1


3) Language: Choose the appropriate languages.


4) Check the Administrative privileges


5) At Select Configuration Options window, leave the upper 2 options checked only


6) Port configuration: Automatic


7) Fill in the Instance name and ias_admin password:


8) Install


9) Exit


10) Shutdown Oracle AS:
a. Open a cmd window
b. cd oraHome_1\bin
c. emctl stop iasconsole
d. opmnctl stopall


Oracle Applications Server Patch Installation

1) Open a cmd window
a. set ORACLE_HOME=d:\OraHome_1
b. cd [Installation Dir]\Windows\patches\oas101202\OPatch
c. opatch apply d:\ [Installation Dir]\Windows\patches\oas101202\OPatch\3992805


2) Type "Y" (for : Is this system ready …?)



Agile Application Installation

1) From Disk1: cd [Installation Dir]\Windows
2) Execute setup.exe


3) Accept the license agreement
4) Enter license & username


5) Select Applications Server + File Manager + Web Proxies


6) Location to install Agile application: D:\agile\Agile9221


7) Select Oracle Application Server 10g (10.1.2.0.2)


8) Select Standalone Installation


9) Enter Oracle Application Server Home directory: d:\OraHome_1


10) Click on Use Existing


11) Choose: No, use a Database for authentication


12) Hostname: agileapp.[domain]


13) Web Server information: agileapp.[domain]:80


14) Agile viewer information: agileapp.[domain]:5099


15) Database details:
a. Agile Database Host Name: agiledb
b. Agile Database Port: 1521
c. Agile Database SID: agile9
d. Agile Database User: agile


16) Virtual path: Agile


17) At File Manager User Authentication window select: Use Internal user account


18) File Manager Virtual Path: Filemgr


19) Agile File Manager window: agileapp.[domain]:80


20) Agile File Manager Storage Location: e:\agile\agile9221\files


21) Select to create product icons in an new Program Group called: Agile


22) Install…


23) Restart the system


Agile Viewer Installation

1) From Agile Viewer installation directory execute: setup_win.exe


2) Accept the license agreement
3) Enter User name and License key
4) Check the Agile Viewer only


5) Select New Install


6) Location: d:\Agile\Agile9221


7) Select Regular Agile Viewer


8) Enter hostname & port: agileapp.[domain]:5099


9) Done



Deploy Agile application on Oracle Application Server

1) cd OraHome_1\opmn\bin
a. Stop all Oracle AS processes - opmnctl stopall
b. Start all Oracle AS processes - opmnctl startall
c. cd d:\agile\agile9221\agileDomain\bin
d. Execute command: DeployAgile


2) Verify deployment
a. cd \OraHome1\dcm\bin
b. dmctl listapplications



Verify Installation

1) Run in browser the following url: http://agileapp:7777/Agile/PLMServlet
2) Connect with admin user


Configure IIS as a Proxy Server for Agile PLM

1) Navigate to: Control Panel -> Administrative tools -> Internet Information Services (IIS) Manager


2) Right click on “Default Web Site” (under Web Sites) -> properties
3) Select the “Home Directory” tab
4) In the “Execute permissions” list, select “scripts and executables”


5) Select the “ISPAI filter” tab -> add
a. Filter Name: oproxy
b. Executable: D:\Agile\Agile9221\AgileProxies\oracle_proxy.dll


6) Right click on Default Web Site-> new -> Virtual directory


7) Alias: oproxy


8) Path: d:\agile\agile9221\AgileProxies


9) Check the read and execute options


10) Finish.
11) Navigate to “Web service extension” -> select: “all unknown ISAPI extensions and Click “Allow”


12) Navigate to: Control Panel -> Administrative tools -> Services
13) Restart the “IIS Admin Service”
14) Run in browser: http://agileapp/Agile/PLMServlet (without port 7777)
15) Logon with admin user to verify IIS configuration.


Configure File Manager with IIS

1) Edit d:\agile\agile9221\Tomcat\conf\server.xml
2) Look for the port after the following text:
!-- Define a Coyote/JK2 AJP 1.3 Connector on port 8009 --

3) Edit file jk2.properties -> channelSocket.port=8009 (the previous port)
4) Navigate to: Control Panel -> Administrative tools -> IIS Manager
5) Go to ISPAI Filter tab -> add
a. name: Jakarta IIS Connector
b. Executable: D:\Agile\Agile9221\AgileProxies\isapi_redirect.dll
6) Right click on default web site-> new -> Virtual directory
a. Alias : Jakarta
b. Path: d:\agile\agile9221\agileproxies
7) Check the read and executable options
8) Restart IIS Admin Service again.


Verify File Manager installation

1) Startup tomcat server by: d:\agile\agile9221\tomcat\bin\catalina start
2) Open the following url in browser to check Java installation on client: http://agileapp/JavaClient/start.html


3) In order to use the Agile java client we should install Java JRE 1.5.x
4) Open the following url again: http://agileapp/JavaClient/start.html
5) Click on Launch
6) Login with admin user.
7) Navigate to: Server setting -> locations
Verify all locations (especially under the File Manager tab)


Now when the Agile application node installed, the Agile system are ready for use.
If you have an initial dump file to export, you can do it now with agile9imp.bat script.

For more information:
Installing Agile PLM for OAS

You are welcome to leave a comment for any issue or additional information.

Aviad

Categories: APPS Blogs

R12 Global Deployment functionality

RameshKumar Shanmugam - Sat, 2007-12-29 17:04
It is a common Operation process in any industry to move people around or transfer employee’s temporary basis for a particular project or Assignment. Or transfer them permanently to a different country.

Though this functionality was available in 11i for the HR professionals to do it manually if the cross business group is enabled they can update organization and location in the assignment form, or another method of doing this is to terminate and hire the employee in the new business group.

Now in R12 this functionality has been made as a standard functionality in the Manager Self Service Responsibility Under the function 'Transfer'

Manager Self Service > Transfer



Select the employee you wanted to transfer, follow the wizard which will take you through complete process like new salary change, new direct report, New Location Change, Time card approver, work Schedule etc., finally you will receive a Review summary page where you can review and submit for the approval

Note: if you are a Oracle Payroll Customer you need to take necessary actions when changing the work location for the payroll Taxation
Try this out!!!
Categories: APPS Blogs

Agile PLM 9.2.2.1 – Part II – Database Node Installation

Aviad Elbaz - Mon, 2007-12-24 01:51
Following my last post about Agile PLM 9.2.2.1 – Part I, in this post I’ll show, step by step, how to install Agile PLM 9.2.2.1 system.

The latest version of Agile PLM is 9.2.2.2, but it's too much new to install it for production..

I installed the Agile PLM 9.2.2.1 on 2 nodes configuration:

1) Agiledb – Oracle Database server Node
C:\ drive – OS only
D:\ drive – Database 10g software and Datafiles

2) Agileapp – Agile Application + IIS + Oracle AS + Agile File Manager Node
C:\ drive – OS only
D:\ drive – Agile 9.2.2.1 applications + Oracle AS
E:\ drive – File Vault (managed by the File Manager)

** Both servers installed with Windows 2003 Server OS.

This post will describe all phases of database node installation, and next post I’ll describe the application node installation.

The Agile Database node installation composed from the following steps:
- Complete all database installation pre requisites
- Oracle Database 10.2.0.1 installation
- Oracle Datbase companion installation
- Agile Database creation
- Listener configuration

Database Node Installation - Pre Requisites

1) If server is DHCP configured you should setup a loopback (10g installation requirements)
a. Control panel -> Add hardware



b. Choose: Yes, I have already connected the hardware


c. Choose: Add a new hardware device (the last item on list)


d. Choose: Install the hardware that I manually select from a list


e. Choose: Network adapters


f. Choose at the left side: Microsoft at right side choose: Microsoft loopback adapter



g. Edit the server host file at c:\windows\system32\driver\etc\hosts
Add the following line:
10.10.10.10 agiledb.domain agiledb


h. Navigate to network connections: Start -> settings -> Network Connections
i. Right Click on Local Area Connection – Microsoft Loopback Adapter -> properties


ii. Select TCP/IP -> properties


iii. Edit the ip address to 10.10.10.10
iv. Edit the subnet mask to 255.255.255.0


2) Restart the server


Oracle Database 10.2.0.1 installation

1) Run database installer – setup.exe (from database directory)
2) Select Basic installation – Standard Edition
a. Oracle Home Location: d:\oracle\product\10.2.0\db_1
b. Uncheck the Create Starter Database


3) Review prerequisite checks


4) Click on Install…


5) Exit



Database Companion Installation

1) Run the installer – setup.exe (from companion directory)


2) Select Oracle Database 10g Products 10.2.0.1.0


3) Specify Home details:
a. Name: OraDb10g_home1
b. Path: D:\oracle\product\10.2.0\db_1


3) Review prerequisite checks


4) Install…


5) Exit


Agile Database Creation

1) Copy the “Platform” directory from Agile Disk2 directory to Agile Disk1 directory to the setup.exe level


2) Run [Disk1]:\windows\setup.exe


3) Accept license agreement
4) Enter the license key and user name.


5) Select : Database Server only


6) Enter the location to install the selected Agile 9.2.2.1 components:
D:\agile\agile9221


7) Select Oracle 10g Database Server


8) Install


9) Destination folder for database customization files: d:\Agile9Tmp


10) Select “Medium” Database size
For more information regarding each possibility - small, medium, large... - read the Capacity Planning Guide


11) Select the previous installed oracle home


12) Oracle SID: agile9


13) Enter passwords for Sys and System and user name and password for Agile schema.

14) Click some next’s…




15) Agile installation will create the database


16) Done



Configure Database Listener

1) Navigate to: Start Menu -> Programs -> Oracle - OraDb10g_home1 -> Configuration and Migration Tools -> Net Manager


2) Create new Listener, Name: Listener


3) Select Database Services from the drop down list
a. Click on Add database
b. Global Database Name: Agile9
c. Oracle Home Directory: d:\oracle\product\10.2.0\db_1
d. SID: agile9


4) File -> Save Network Configuration
5) Restart the Listener
a. Lsnrctl stop
b. Lsnrctl start


That’s all for the Agile Database node.
Next post I’ll show the Agile 9.2.2.1 Application node installation.

For more information:
Capacity Planning Guide
OracleAgile Database Installation Guide

You are welcome to leave a comment.
Aviad
Categories: APPS Blogs

Agile PLM 9.2.2.1 - Part I

Aviad Elbaz - Thu, 2007-12-20 02:27
In May this year, Oracle has acquired Agile, a leading provider of Product Lifecycle Management (PLM) software solutions.

In this post I will explain about the various components of Agile PLM system, and the following posts will be dedicated to Agile PLM installation.

The components of Agile PLM application are:
1) Agile Database
2) Agile Application Server
3) Agile File Manager
4) Agile Web Proxies
These components should be installed in the above order.

Agile Database
Used to hold all Agile data.
It is recommended to install the database and application server on separated machines.

Agile Application Server
The Agile Application Server can be run on Oracle Application Server 10g or BEA WebLogic Server.
The Application Server is the main component of the Agile system, all services and business logic reside on it.
The Agile application deployed on the Application Server.
All users (Java/Web clients) connect to the Application Server in 2 possible ways:
- Directly
- Indirectly, via Wev proxy Server.

Agile File Manager
The File Manager manages all Agile files in the file system (File Vault).
The File Manager runs on Tomcat.

Agile Web Proxies Server
The Agile web client allows connection to both internal and external users.
Agile web client uses Microsoft IIS (Internet Information Services) or Apache web server.
The Agile web client connects to an Agile Application Server the same way like other Agile Application Server clients.



Next post I will show, step by step, how to install Agile PLM 9.2.21 on two nodes configuration.

Aviad
Categories: APPS Blogs

Data Auditing in Oracle Applications - Audit Trail

Aviad Elbaz - Wed, 2007-11-28 02:01
Following my last post about Audit users in Oracle Applications, in this post I’m going to talk about the Audit Trail - the data auditing feature in Oracle Applications.
The AuditTrail enable us to know who, when and what was changed on each table we would like to audit.
For example: Some of the profiles in Oracle Applications are very critical and important for normal activity of the system (like MO: Operating Unit).
We might be interested to know who changed the value of critical profiles and the Audit Trail certainly can help us.

Now I’ll show a step by step demo how to start Audit Trail on a table, I will go on with my previous example on the profiles table.
The table we would like to audit is FND_PROFILE_OPTION_VALUES.

1) Logon to system with System Administrator responsibility.

2) Make sure the profile “AuditTrail:Activate” is set to Yes

3) Check the audited table's owner
select owner
from dba_tables
where table_name like 'FND_PROFILE_OPTION_VALUES';

The owner is APPLSYS.

4) Check that audit is enabled for APPLSYS user
Go to: Security -> AuditTrail -> Install -> Query for APPLSYS




5) Define new Audit Group
Since the audit enabled on groups of tables, we should define a new Audit Group which will contain the FND_PROFILE_OPTION_VALUES table.

Find the table’s application name by this query:
select fav.application_name
from fnd_application_vl fav, fnd_tables ft
where fav.application_id = ft.application_id
and ft.table_name = 'FND_PROFILE_OPTION_VALUES';

The query result: Application Object Library

Navigate to: Security -> AuditTrail -> Groups

Application: Application Object Library
Audit Group: AC FND Profile Values Audit
Group State: Enable Requested


At this level, the columns that will be audited are the columns of the primary key or the first unique index on the audited table.
You can add columns to be audited as much as you want.

6) Check/Add column to audit
Navigate to: Security -> AuditTrail -> Tables
Query for FND_PROFILE_OPTION_VALUES table.


Add column PROFILE_OPTION_VALUE to be audited.


7) Run the “AuditTrail Update Tables” concurrent to enable the audit
Navigate to: Requests -> Run -> Single Request -> choose request name: “AuditTrail Update Tables” -> Submit


All definitions we made until here will not take effect until we execute this concurrent.
This concurrent will create all objects (triggers and procedures) required for audit, and views to retrieve the audited data.

This request should be executed each time we make changes in audit definitions, to generate new audit objects.

To make sure it works, we can check if all audit objects were created:


All audit objects names will be the first 26 characters of the audied table + suffix (_A, _AC, _AD...).
We can also run report “AuditTrail Report for Audit Group Validation” to make sure all audit objects created successfully.


Now let’s make a test to see how it works:
To test the audit on the profile's table, we will change a value for a profile (any profile) and check the shadow table (fnd_profile_option_value_a) for an audit inormation.

Navigate to: Profile -> System , and update the profile “FND: Diagnostics” (just for testing... you can choose any profile) to Yes.

To see the audited data of the last change we can run this query:


The change was audited as expected...

As you can see, the “U” in the audit_transaction_type column indicates an update and the profile_option_value contain the value before update.

The audit_transaction_type could be:
1) U - update
2) D - delete
3) I - insert


How to disable Audit Trail?

The AuditTrail could be stopped by 3 ways:
1) Disable Prepare for Archive
2) Disable Interrupt Audit
3) Disable Purge Table

I've tried the third only… It deletes the data from the shadow table and drops all the audit objects from database.

1) Login to application and choose the System Administrator responsibility.
2) Security -> AuditTrail -> Groups
3) Query for your group
4) Update the Group State field to: “Disable – Purge Table”.
5) Run the “AuditTrail Update Tables” concurrent to make the changes.

That’s all about auditing…

You can read more at "Oracle Applications System Administrator’s Guide - Security Release 11i" - Chapter 5 - User and Data Auditing

To get my posts directly to your email, you can register for email subscription by using the box at the right side bar.

You are welcome to leave a comment.

Aviad

Categories: APPS Blogs

HRMS PFK RUP2 Global name Search

RameshKumar Shanmugam - Fri, 2007-10-26 01:33
Mainly in the multi BG environment we end up in creating duplicate employee name, the current HRMS search functionality some times the duplicates are missed out due to the localization and the Name format used in each legislation.

In the Family Pack K Rup 2, System Uses Global Name format to Search for the Duplicates Across Business Group.
The Global name format is independent of the Legislation which helps us to avoid the duplication.

To Enable this Functionality we need to enable the profile HR:Cross BG duplication Person Check.

Still the HRMS uses the same existing functionality to search the employee in single business group

Try this out!!!

Regards
Ramesh Kumar S
Categories: APPS Blogs

Payroll Reversal Pack K Rollup 2

RameshKumar Shanmugam - Sun, 2007-10-14 15:44
HRMS Family Pack RUP 2 was released on Jun 2007.
Due to current project schedule i didn't had much time to test the new functionality.
This week i had the chance to install the PF K RUP 2 to my test instance, the lot of functionality which i was expecting is here now...

The first thing which i would like to highlight is Payroll Reversal functionality.
If you want to reverse assignments that have been processed as part of a payroll run or QuickPay run, you can now reverse them as a group by assignment set instead of reversing by individual assignment only. Any assignment sets that you use for reversals can also use the normal assignment set features such as:
  • Include or exclude specific assignments
  • Make formula-based amendments

The original reversal by single assignment is still available, and you can continue to access it from the Assignment window.

Payroll Reversal Details http://ramesh-oraclehrms.blogspot.com/2007/09/payroll-reversal.html

Defining Assignment Set http://ramesh-oraclehrms.blogspot.com/2007/08/assignment-set.html

Try it out!!!

Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator - APPS Blogs