Installation and Configuration of Applications Desktop Integrator (ADI)

What is ADI ?

      Oracle ADI is a spreadsheet-based extension to Oracle Applications that offers full cycle accounting and asset management within the comfort and familiarity of a spreadsheet. Oracle ADI combines a spreadsheet’s ease of use with the power of Oracle Applications, to provide true desktop integration during every phase of your accounting cycle. You can create budgets, record transactions, add assets, reconcile inventory, and run financial statements and inventory reports all without leaving your spreadsheet.
      You can run Oracle ADI as a stand-alone application, without installing the full client version of Oracle General Ledger (GL) or Oracle Assets on your PC. This gives you the ability to confirm and reconcile inventory, revise budgets, create journal entries, and define financial and asset reports from any location, without being connected to your server. You only need to connect when you want to transfer data to or from Oracle Applications.

How to install and configure ADI ?

      In this paper, I am going to focus on installing ADI to the 32-bit Windows XP environment. Since nearly all end-users use Windows XP operating systems, I think this will be useful.Addtionally, I will force the program to work with Turkish characters. All configurations are same for all extra languages. The only thing you should know is the character set of your language.

(*) If you are using only English, you do not have to additional character set configurations.

Steps to install Oracle ADI Software

1- First of all; you should download the latest base release of ADI software and the last patch-set. At the moment, the last base release of ADI is 7.2 (metalink patch number: 3966101) and the last update is ADI 7.2 ROLLUP 11 (metalink patch number: 7286213).

2- All installation processes have to be performed by a priviledged user such as local administrator and at least Excel has to be installed to the computer.

3- After downloading the right software for your environment, extract first the base release on a folder and run Setupadi.exe.

4- Select Oracle Applications Desktop Integrator for Excel 2000/XP/2003 (It works for Excel 2007 as well) and confirm by pressing Yes.

5- Type your Company Name(eg. oracle), Name (eg. oracle), Location (eg. C:\ADI) and select English as the language. Press Ok.

6- In the Software Asset Manager screen, select all the products available on the left side. Press Install.

    ADI Online Help (HTML Format) 7.2.0.09.12

    +Applications Desktop Integrator Products 7.2.0.09.12

    Oracle Installer

    +Optional Components Install

7- Confim the Information message box by pressing Yes and select English(United States) as the language. Press Ok.

8- Select All the optional components from the list. Press Ok twice.

    Oracle Net8 Client 8.0.6.0.0

    Oracle Objects for OLE - Runtime 8.0.6.3.9

    Oracle Trace Collection Services 7.3.4.0.0

    Oracle Trace Collection Services 8.0.6.0.0

    Required Support Files 8.0.6.0.0

    Required Support Files 7.3.4.1.1

    SQL*Net Client 2.3.4.0.2

9- On the ADI Online Help screen press Next and Finish.

10- First part of the installation is finished. Verify the installed programs on the right side, press Exit and restart the computer manually.

11- Extract the ROLLUP patch to a folder and select Oracle Intaller from Start>Programs>Oracle for Windows NT

12- Press the From button and select NT.PRD file from the rollup extracted folder.

13- Select Applications Desktop Integrator Products 7.2.11.07.29 from Available products pane and press Install button. After installation, press Exit and restart the computer.

Steps to configure Oracle ADI Software

1- Copy tnsnamer.ora and sqlnet.ora from the database server to the ADI client under C:\ADI\NET80\ADMIN and C:\ADI\NETWORK\ADMIN

2- Open ADI from Start>Programs>Oracle ADI>Applications Desktop Integrator. An alter will prompt to define a database. Pres Ok.

3- Press Signon>Define Databases>Add button.

4- Type _database as name, APPLSYSPUB/PUB as GWYUID, APPS as FNDNAM and database tns name for connect string which is in thetnsnames.ora file.

5- Click Ok twice and enter the credentials to test the connectivity.

Steps to configure the regional language settings

1- When you enter the program, if you use non-English characters in entries, you will have some difficulties to understand the words.

2- Type Regedit to Run command and go [HKLM\SOFTWARE\ORACLE\NLS_LANG] expandable string value and change to the appropriate character set value. Try your database character set. You also have to change the language for non-Unicode programs appropriately.

3- Restart the computer.

Now, the ADI software has to be ready.

Congratulations.

Oracle to MySQL Connection

I don' t know whether you want to connect a different database to Oracle, but it is sometimes necesarry to do that. Since MySQL is opensource and freely distributable, it comes embedded with some applications and you are subject to make the connection.

Purpose

Make an external database link between Oracle 10g Database and MySQL 5.1 server.

Prerequisites

Platforms and databases that will be used are:

Oracle 10g Database (10.2.0.3.0) on Windows XP / 2003 / 2008

MySQL Server 5.1 on Windows XP / 2003 / 2008

Also, since I am going to use the ODBC connection:

Openlink ODBC Driver for MySQL 5.1(UDA-Single Tier)

The reason not to use the free MySQL Oracle ODBC driver is that when I use the classic one, I faced with some problems about querying string characters. OpenLink’s ODBC driver solved this problem.

General

There are three steps to do this:

1. MySQL Server-Side Configuration

2. MySQL Driver Installaton and Configuration

3. Oracle Heterogeneous Services Configuration

MySQL Server-Side Configuration

It is better to create an addtional account and give appropriate permissions for this purpose. But, I am going to use the “root” account to access MySQL, so I enable access from remote machines for “root” account.

MySQL Driver Installation and Configuration

As you know, MySQL driver has to be installed on the server where Oracle Database resides.

After the installation, first we have to create the ODBC connection. To do this, we go to the ODBC Data Sources by typing “odbcad32” to the Run Window. In the System DSN tab, we add a new Data Source using Openlink Lite for MySQL 5[6.0] driver. The next window asks a name for this datasource. This name is important because you are going to use this name in Oracle-Side configuration. I recommend to use MYSQL as name because I will use this name in my examples. Configuration continues with hostname, port number, database name and login credentials. Enter the computer name or Ip address for the hostname, MySQL database port number and the database name that you want to connect from Oracle. On login credentials, use the account that you have created in MySQL configuration side.

E.g. Hostname : 192.168.1.12

Port : 3306

Database : Mysql

Login ID : root

Password : *****

Other configuration parameters are not that important but by setting up these parameters, you can make some constraints to your connections such as read-only connectivity, character set obligation and so on. At the end, it will be helpful to use Test Data Source button to diagnose problems.

Oracle Heterogeneous Services Configuration

In this section, we are going to tell to Oracle Database to use the configured ODBC driver in order to reach the datas in MySQL server. In general, there are several ways to connect non-Oracle databases to an Oracle database like Transparent Gateway and Generic Connectivity. But, MySQL server do not let us to use Transparent Gateway technology, so we use Heterogeneous Services ODBC agent.

First; we have to make a copy of inithsodbc.ora file and rename it like init'SID'.ora in $ORACLE_HOME/hs/admin folder. This file will be responsible of which ODBC driver will be used in ODBC agent. 'SID' parameter can be anything but it is better to note it down because it will be used in tnsnames.ora and listener.ora files. In this file, two parameters HS_FDS_CONNECT_INFO and HS_FDS_TRACE_LEVEL must be set correctly. HS_FDS_CONNECT_INFO parameter has to set to the name of ODBC Data Source created in MySQL Driver Instalaltion and Configuration Side. HS_FDS_TRACE_LEVEL is set to 0 generally.

E.g. initMYSQL.ora

HS_FDS_CONNECT_INFO = MYSQL

HS_FDS_TRACE_LEVEL = 0

After saving the initMYSQL.ora file, we have to set the SID_LSIT_'LISTENER_NAME' part of the listener.ora file. (The listener.ora file locates under $ORACLE_HOME/network/admin folder.)

We add another SID_DESC entry in SID_LIST:

( SID_DESC =

(SID_NAME = MYSQL)

(ORACLE_HOME= C:\oracle\product\10.2.0\db)

(PROGRAM = hsodbc)

)

Any changes in listener.ora file leads us to restart the Listener service. To do this, you can use lsnrctl stop and lsnrctl start commands in Run Window.

After restarting the Listener service, we have to configure the tnsnames.ora file which is in the same folder with listener.ora file. The configuration is only another entry like the database connection:

MYSQL=

(

DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST='ORACLE_DB_SERVER_NAME')(PORT='PORT_NUMBER'))

(CONNECT_DATA=

(SID='SID')

)

(HS = OK)

)

MYSQL = The connection identifier name, it can be anything, in my example is MYSQL

'ORACLE_DB_SERVER_NAME' = The hostname or ip address of Oracle Database server.

'PORT_NUMBER'= The Oracle database port number; default value is 1521.

'SID'= It stands fort he value given above. In our example it is MYSQL.

(HS = OK) means that this connection will use Oracle heterogeneous services.

Conclusion

After all of this configuration, the connection must work properly. The last part is to create a classic database link using the new entry in tnsnames.ora file and use it. The commands are:

Create database link DB_LINK

connect to “root” identified by “******”

using ‘MYSQL’;

select * from employees@DB_LINK;

DB_LINK is the database link name, root is the privileged username to connect MySQL server and mysql database and ‘MYSQL’ is the tnsnames.ora entry.

Hope this will be useful.