Oracle Reporting Tools and types of Reports in oracle apps,Report Development steps

Reports In Oracle Application
Report à Displaying details in a specific format or layout

Oracle Reporting Tools are

1)      SQL
2)      PL/SQL
3)      RDF
4)      Discoverer
5)      XML Publisher
6)      BI Publisher
7)      OBIEE

SQL :-
Ø  Using SQL we can generate report in a ASCII format.
Ø  We can not in a GUI format.
Ø  Images can not be displayed as it is ASCII output
Ø  SELECT statement is used to generate the report
Ø  Using SQL we can generate report in a ASCII format.

Ø  We can not in a GUI format.
Ø  Images can not be displayed as it is ASCII output
Ø  Cursors are used to generate the report.

RDF :-
Ø  We can display out put in GUI format.
Ø  Images can also be displayed.

Ø  We can not display the output at a time in multiple formats such as PDF, HTML, TEXT, EXCEL …

Ø  Layout designing is very complicated.

Discoverer :- Older one . it is not being used with current version Oracle Apps.

XML Publisher : -
Ø  It is a mostly used one in the current version of Oracle Apps.
Ø  It is an extension to RDF.
Ø  Easy to design the layout

BI Publisher and OBIEE: 

Ø  It is an extension to XML Publisher.
Ø  XML is used to develop the reports.

Application Object Library ( AOL )

Application : It is a combination of development and customization

Object :  These are developed and customized objects

Library :  It is a registry

AOL is a key where we are going to complete Technical and Functional aspects.

It involves two aspects

1)      System Administrator
2)      Application Developer
System Administrator :

Ø  Creation of users
Ø  Security Groups
Ø  Responsibility
Ø  Profiles
Ø  Programs

Application Developer :

Ø  Forms
Ø  Reports
Ø  Menus
Ø  Form function
Ø  Messages
Ø  Application
Ø  Tables

These are object registry by the application developer
Creation of a user

1)      Start Browser for  login :
User Name : operations
Password    : welcome
2)      System Administrator ( single click ) ( left side pane )

3)      Go to right side pane
Security : User
Define ( click )

4)      Users form

User Name : mnaro10
Password    : xxxxxx  ( at least 5 chars )
( re-enter password ) ( it shows on the status bar of the form )
Description : any one ( purpose of user ) ( is an  optional )

Responsibility                         Application                      Description    Security Group       From            To
Application developer
Application object library

Sysdate Auto

System Administrator
Application object library

 Place the cursor in the responsibility cell and click ( right side …)
Find : appl%         enter key  and select application developer  , then OK

Place the cursor in the responsibility next row and click ( right side …)
Find : system administrator %         enter key  and select application developer, then OK

            Save the form (  save button )
            Status bar shows that : Transaction complete : 3 records applied and saved.
            Close the form

5)      Checking for User  

1st way
Go to Oracle Application Home for checking weather the user created or not
Security : user
Users form : press f11 à to query
When pressed F11, users forms enters into the query mode
User Name : mnrao%  and Ctrl + F11 , it shows user details

            2nd way

            Using query
            Select * from FND_USER ;
            Using Toad Utility :
            User name : apps
            Password : apps
            Database  : vis

            Click on Connect
            Go to editor then execute following query ( Ctrl + Enter à to execute query )
            Select * from FND_USER
            Check in the result set ( at the end)

Creation of one more user
Users form:
New button ( + )  ( 1st one on the tool bar with green color )
Repeat the above procedure and save it.
For checking users form : F11 à to get into query mode,
User Name : mnrao%   : Ctrl + F11 à it queries for the users starting with user
Down arrow key display the users one by one .

Find button : ( 2nd one in the tool bar )
To display users
Click and find : user % ( enter key )

Close Form : to close the form
Clear form : to clear the current form
Delete  : this will be in the disabled position, to delete the user set effective dates

From :
To :
Set the To date same as the form date.

Significance of colors in the form

1)      Yellow color à mandatory field
2)      White à Optional
3)      Gray à read only ( it is not a navigable item )

Finding Table Name
Go to users form F11à query the user ( user name : ctrl + F11 )
After getting user details go to Help menu à Record History
It shows as below

Creation Date:  04-MAR-2011 17:24:42
Table Name:  FND_USER
Update Date:  04-MAR-2011 17:24:42

The above details will be obtained from the Table called   FND_USER

Column names
Help à diagnostics à examine à

Block :  USER  
Filed : USER_NAME,  to see the more fields click at right side, it shows all the columns in the table


using toad utility we can query the fields
Select * from FND_USER
Value : mnrao10

In Oracle Applications, every table will have 4 common columns

To find the current login user

Help à about application

Oracle Applications
Copyright (c) 2004 Oracle Corporation,
Redwood Shores, California.
All Rights Reserved.

Application : System Administration
Responsibility : System Administrator
Security Group : Standard

User Login:

1) Logout form the administrator
Go to home page and logout

2) User Name : mnrao10
     Password : xxxx
            For the fist login, it will be prompted for
            Current Password :
            New password : we should change password for security purpose
            Re-Enter new password:

4)      Logout à to quit from the user session.

Types of executables :

These are programs

Oracle Application supports 12 types of languages
Older version supports only 11 languages
Apps 11i version supports 12 languages.

1)      Oracle Reports             à   .rdf
2)      Sql * Plus                     à   .sql
3)      PL/SQL Procedures      à   .proc
4)      Sql * Loader                à   .ctl
5)      Unix shell script           à   .sh
6)      C – Language               à   .C
7)      Pro - C                         à   .prc
8)      Java Programs              à   .java
9)      Java Serve Pages          à   .jsp
10)  PERL   scripting           à   .perl
11)  Stage functions
12)  Immediate

Once the above programs have been transferred to oracle application, then these are called as concurrent programs.

concurrent program :   Instance of executable file along with input parameters and incompatible programs.

 development of simple report and transfer to oracle apps and execution

Report Registration
1) Develop the report (.rdf) as per client requirement by using Reports 6i Builder

2) Move the report from the local machine in to the server

a. CUS_TOP\11.5.0\reports\US .rdf (or)
b. PO_TOP\11.5.0\reports\US .rdf

3) Select System Administrator

a. Create Executable
b. Executable name
c. Application Name
d. Execution Method
e. Report (.rdf) file name

4) Create Concurrent Program and attach

a. Executable
b. Parameters
c. Incompatibilities

5) Create Request group and attach Concurrent Programe

6) Create Responsibility

a. Request Group – Concurrent Programe
b. Data Group – Collection of User IDs
c. Menu – Collection of forms

7) Create user attach Responsibility to the user

8) User will select the responsibility and go to SRS (Standard Request Submission)

window submit the request
Executable => Concurrent Program => Request Group => User=> SRS

It involves the following steps

Step 1 :
Develop the report (.rdf) as per client Requirement by using reports 6i Builder
Step 2:
Move the report from Local machine into Server

               CUS_TOP\11.5.0\Reports\US\ .rdf
                PO_TOP\11.5.0\Reports\US\ .rdf
Step 3:
Select System Administrator and create EXECUTABLE 
                                                            1)Executable Name
                                                            3)Execution Method
                                                            4)Report(.rdf) file Name
Step 4:              
Create Concurrent Program and attach  1)EXECUTABLE
Step 5:
Create Request Group and attach Concurrent Program
Step 6:
Create Responsibility       and attach
  1)Request Group
                                                               2)Data    Group
Step 7:
Create user,  attach Responsibility to the user.

Step 8:
Select the Responsibility and  go to SRS(Standard Request Submission) Window

Submit the Request.

Post a Comment