Encounter with Oracle ADI

Oracle ADI and its integration with E-Business Suite


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.

DBA perspective: - From DBA point of view, fundamentals are quite simple, For publishing report, ADI will take value of profile option "RRA: Service Prefix" from e-biz and connect it with node name from fnd_concurrent_request table for that particular request. Say, I have my CP tier on EBUS_CM (hostname) and RRA:prefix profile option set to FNDFS_ then ADI will search for FNDFS_EBUS_CM in its tnsnames.ora( i.e c:/orant/network/admin or c:/orant/net80/admin) and produce the output file.
Your tnsnames.ora and sqlnet.ora on client and server side should exactly match for instance and FNDFS_hostname entry.

Install ADI: - ADI can be installed on users machine using patch from metalink. Oracle ADI 7.2 base release patch is 3966101. Current version of ADI is 7.2.11.07.29(patch 7286213). Installation is straight forward. Just go through the patch readme and that’s all.

Integrate ADI with Oracle Application E-Business Suite: -
Changes to be done as part of Oracle ADI.
Edit tnsnames.ora file at c:/orant/network/admin and put tns entries of your ebusiness suite server.
Basically two entries are required(per instance), one for resolving instance name while connecting to E-Business Suite(initial sign on) and other for publishing report, viewing out and log file. This entry should be like FNDFS_hostname.

Sample tnsnames.ora:
#for initial sign-on
EBUS = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EBUS_CM)(PORT=1531))
(CONNECT_DATA=(SID=EBUS))
)
#for publishing report, viewing out and log files.
FNDFS_EBUS_CM= (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EBUS_CM)(PORT=1636))
(CONNECT_DATA=(SID=FNDFS))
)

Edit sqlnet.ora file at c:/orant/network/admin. Sample file
AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
names.directory_path = (TNSNAMES)
names.default_domain = oracle.com
name.default_zone = oracle.com
names.default_domain=oracle.com

Copy tnsnames.ora and sqlnet.ora from c:/orant/network/admin to c:/orant/net80/admin

Changes to be done as part of E-BIZ: - Change "RRA: Service Prefix" profile option at site level to FNDFS_

Using ADI: - You will get Request centre and ADI icons on your machine once it has been installed.
(i) Click on Application Desktop integrator button and screen will be presented to you.
(ii) Click on "hand" icon
If this is fresh installation then you need to define database connect string in ADI
Click on Cylinder button and database details screen will be presented to you:
NAME: Any name as per your convenience
Description: As per convenience
GWYUID: applsyspub/pub in most of the cases.
FNDNAM: apps
Connect String: E-BIZ database connect string
NCA Connection: - Not Required
Server ID: - Not required in most of the case but if required take this value from dbc file located on e-biz server at $FND_TOP/secure

(iii) Click on tick button and signon details screen will be presented to you.
Username: E-BIZ application user name
Passowrd: Password of username used for connecting ADI
Database: Select your E-BIZ instance name from drop down
After providing details you will screen saying Gathering set of books information blah blah...
If above screen continue to appear for more then couple of minutes then you are hitting bug and need to upgrade ADI. Check metalink for more.
Click on "Submit Report" green button i.e. first button from upper-left
click at Report area
select report
click on "tick" button at upper-right corner
Click ok

on pending tab you can view your request running normal
for publishing report click yes

Published report willl be presented to you.



Useful Metalink Note ID’s for troubleshooting:
Note.117012.1 - Troubleshooting the "Error Occurred While Attempting to Establish an Applications File Server Connection"
Note.469724.1 Ext/Mod Cannot View Published Report In Request Center Gen
Note.311349.1 Ext/Pub Not able to to publish reports when connecting to other Application Instances on the same Application Server
Note.201492.1 Ext/Pub ADI RRA Prefix When Connecting to Server with Multiple Apps Instances