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
PL/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
Ø 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 : http://apps.ora.com:8000
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
Define
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
Created By: OPERATIONS
Creation Date: 04-MAR-2011 17:24:42
Table Name: FND_USER
Updated By: OPERATIONS
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
(or)
using toad utility we can query the fields
Select * from FND_USER
Value : mnrao10
In Oracle Applications, every table
will have 4 common columns
1) LAST_UPDATE_DATE
2) LAST_UPDATED_BY
3) CREATION_DATE
4) CREATED_BY
To find the current login user
Help à about application
Oracle Applications
Copyright (c) 2004 Oracle
Corporation,
All Rights Reserved.
----------------------------------------
Login
----------------------------------------
Site : UNKNOWN
Application : System Administration
Responsibility : System Administrator
Security Group : Standard
User Name : OPERATIONS
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
Eg:
PO_TOP\11.5.0\Reports\US\ .rdf
Step 3:
Select System Administrator and
create EXECUTABLE
1)Executable
Name
2)ApplicationName
3)Execution
Method
4)Report(.rdf)
file Name
Step 4:
Create Concurrent Program and
attach 1)EXECUTABLE
2)PARAMETERS
3)INCOMPATIBILITIES
Step 5:
Create Request Group and attach
Concurrent Program
Step 6:
Create Responsibility and
attach
1)Request Group
2)Data
Group
3)Menu
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.
No comments:
Post a Comment