Oracle Apps file Architecture, Applications Directory and File Types,Integration of base product schema by using APPS schema

Oracle Apps file Architecture

Applications Directory and File Types

Subdirectory Name

Contains Oracle Forms generated runtime (.fmx) files (Oracle Forms form files).

Contains Oracle Reports .rdf files for each product, which are platform-specific binary report files. Reports for each language are stored in subdirectories of the reports directory.

Contains SQL*Plus scripts used to upgrade data, and .pkh, .pkb, and .pls scripts to create PL/SQL stored procedures.

Location where .pll files (PL/SQL library files for Oracle Reports) are unloaded, later in the installation they are moved to the plsql subdirectory in the AU_TOP directory.
log and out

Contains output files for concurrent programs:
.mgr (master log file for concurrent manager)
.req (log file for a concurrent process)
Note: Log and out subdirectories under a product directory are
not used if you set up a common directory for log and output
files (except in FND_TOP).

Forms display messages at the bottom of the screen and in popup boxes. Concurrent programs also print messages in the log and output files. These messages are translated and stored in message files separate from the forms and concurrent programs. This directory contains the .msb files (binary message files used at runtime), and language-specific message files (such as a US.msb file for American English and a D.msb file for German.)
PROD_TOP/admin contains files used by Auto Upgrade to upgrade each product. Note that this directory is not the APPL_TOP/admin directory. The APPL_TOP/admin directory contains upgrade-related files for all products. This PROD_TOP/admin directory contains product-specific upgrade files.
Contains concurrent program
Contains .drv files (driver files). Auto Upgrade processes in several phases, and each phase is controlled by a driver file.


Contains DataMerge files used to upgrade seed data.

Contains object description files (.odf files) used to create tables
and other database objects.

Contains the online help source files. Within this directory are subdirectories for each language you choose to install.

HTML, Javascript, and Java Serve Page files, primarily for Self-Service Applications products.

Contains C language header (.h) files that my be linked with files in the lib directory. Not all products require this directory.
Contains files used to relink concurrent programs with the Oracle server libraries.
These files include:
object files (.o) with compiled code specific to one of the product’s programs;
a library file (.a) with compiled code common to the product’s programs;
a makefile (.mk) that specifies how to link executables.
Contains JAR files (Java Archive files) and Java dependency files. Copies of JAR files are also located in JAVA_TOP.

The desktop client displays text and graphics from the .gif files
in this directory.

Updates to the data or data model use this directory to store the patch files.

Contains .pll files (PL/SQL library files for Oracle Forms),
which, like the plsql directory files, are later copied to AU_TOP.

Custom Top :

This module is to support customer new requirements other than provided by oracle Corp. Custom Top is used to create the customer ( client ) own forms / reports / or other data base objects as per the requirement of the customer. It will be created by the customer. DBA is the responsible to create this module. At least on custom top is required for every client. Multiple custom tops can also be created as per the demand.

Client need not to develop the modules such AP, PO, INV, AR, GL and etc. These will be provide by the oracle corp.  If any customer developed the database objects like forms/reports/ or any other objects inside the modules provided by the Oracle Corp., these will be over written when the patches are applied to the oracle application.

Custom Top module can be uploaded and down loaded from the server where as other modules can only be down loaded from the server but can not be uploaded to the server.

US folder :

This folder is a language specific. Default language is American English. Regional languages such as Japanese, German, Hindi, Chinese and etc can be used for better under standing of the end users. This folder is applicable only for Forms and Reports as they contain G.U.I components.

To display the available languages use the following query.

Select  *  from FND_LANGUAGES

Select *  from FND_LANGUAGES where  installed_flag  in (‘B’, ‘D’);

B à Base languages, D à Disabled languages,   I à Installed languages.

11.5.0 folder : It depends version of Oracle Application.

Functional consultant must have the clear idea about the version of the Oracle Application and its features

Most of the applications are being developed with 11.5.0 and R-12.

Oracle Database Architecture

The compatibility of version :

Apps 9i / 11i à Database 9i
Apps  R-12 à Database 10 G ( minimum )

The basic things which are required in development of Oracle apps
1) Schema   2) Synonym 

Schema is an area in the oracle database, where we can create database objects. One schema or more than one schema can be created in one database as per the client requirement.

Schema contains database objects such as Tables, procedures, functions, triggers, views, indexes, synonyms, packages.

One schema is required for every module.

Synonym  is an alternative name for any database object.

If any user logged into any schema he can access concerned objects only. He can not have the access to other schema objects.  To other schemas user must have privileges.

There are two schemas called   X   and   Y . if user logged into schema X then he can access the objects of schema X only . He can not have the access to Schema  Y. If user wants access the Schema Y, then user must be granted permissions by the Schema Y.

There is no integration between schemas. To integrate the all schemas in a database oracle provides Apps Schema.

APPS schema:

Oracle provides Apps Schema, which enable the user to access all schemas in a database. Apps schema integrate all the schemas in a database.

The APPS schema has access to the complete Oracle E-Business Suite datamodel. It is analogous to the SYSTEM schema, which has access to the entire database. Oracle Applications responsibilities connect to an APPS schema and the environment variable FNDNAM is set to the name of the APPS schema.

APPS schema contains only synonyms to the database objects in other schemas. Every object in the base product schema will have a synonym  in the APPS schema.

APPS and Base Product Schemas
There is one schema for each product—a base product schema—that owns the data objects for that product. There is one schema—APPS—that owns all the code objects for the E-Business Suite and has access to all data objects. There is one APPS schema for every product installation group. The following code objects are installed in the APPS schema:
■ Views
■ Packages
■ Procedures
■ Functions
■ Triggers
The following objects are installed in the base product schemas:
■ Tables
■ Sequences
■ Indexes
■ Constraints
In addition, there are grants from the tables and sequences to the APPS schema, as well as synonyms from the APPS schema to those objects.

Integration of base product schema by using APPS schema



Ex: Item information Report
            Item Details
Supplier Details
Name of the Employee who received the item
Issued Cheque No
Item details will be obtained form the inventory table
( Inventory Schema  )
Supplier details will be obtained  form the supplier table
( P.O Shcema )
Employee details will be obtained from emp table
( HRMS schema )
Cheque details will be obtained form cheque table
( A.P Schema )

We have to select the data from four tables. Apps Schema provides corresponding synonym for every table in other Schemas, so as to enable the user to access the data from tables of different schemas.
A.P Schema is having 50 objects
P.O Schema is having 100 objects
INV schema is having 100 objects
A.R Schema is having 50 objects
HRMS Schema is having 100 objects

Then apps Schema will maintain 350 ( A.P + P.O + INV + A.R + HRMS ) synonyms.
For convenient name of the synonym and database object will be the same.

Select * form emp, item, supplier where ……

emp, item, supplier are synonyms available in the Apps Schema

            Emp à HRMS.emp,    item à INV.item,        supplieràPO.supplier , 

Difference between Apps Schema and  base product schemas

Apps Schema
Base Product Schemas
1) It will not maintain any data

2) only synonyms and few of Multi Org views

3) User will always be connected to Apps schema

4) Apps is having grants to other schemas

1) It will maintain data in the form of  tables

2) all Database Objects

3) User will never be connected. Some times may be connected to particular schema only.

4) Do not have the grants.

In development of oracle applications,  we use  95 %  DML and TCL commands and 5 % DDL commands.

If customer want to add own custom schema, then we have create the custom schema and grant the privileges to Apps schema. It is a job of DBA. The Technical consultant must have idea of all important tables, which are used in the application.

Connection to Apps Schema

User id :  apps
Password : apps
SID      :  vis
Sql > conn apps/apps@vis
Sql > select count (*) from tab;
Sql > conn po/po@vis;

Sql > select count (*) from tab;
Sql > conn ap/ap@vis;

Sql > select count (*) from tab;
Sql> desc AP_CHECKS_ALL;

Sql> conn apps/apps@vis

Sql> desc AP_CHECKS_ALL;

Utilities used in the development of Oracle Application are 1) TOAD  2) FTP 3) Winscp

Post a Comment