Rdf Report Creation Process in oracle apps
Rdf Report Creation Process in oracle apps
Report Builder is one of the Oracle GUI Tool
Graphical user Interface
There are two ways we can develop the report
ü Wizard base
ü Manual
While developing Report we have two stages.
ü Data Model
ü Layout model
Data Model will be used to select the data from Database,
SELECT Statemen and Formulas,Summary functions and so on…….
Layout model will used to displays the datamodel fields in the desired format.
Layout Model Objects:
1)Text : Will be used to display the standard text information
like
titles,prompts,headings,Address……………
2)Frame : Will be used to display the layout objects only one time
like
totals,titles,Headings and so on
3)Repeating Frame: Will be used to display the objects multiple times
like database
columns,Sub Totals,Page Totals…….
4)Field : Will be used to display the Data base columns,variables
and so on.
First page : Employee Information Report
Second Page : Empno Ename Sal hiredate Comm
Last page : End of the report
Summary Column : If we want to apply summary functions then we will go
for summary column like Average,count,min,max and so on.
1)Group level :
Executed for every record in the group
2)Report Level :
Executed
only once for entire report.
Total Emp:
Total Sal:
Min Salary :
Max Salary :
Formula Columns : When we want implement some logic and return the
value then we will use formula columns.
It is a PL/SQL Function. where we can write PL/SQL code we can return only one
value.
1)Group Level
2)Report Level
Place Holder Columns:
A datatype which stores some value and we can use this across the report.
It will work lika Global variable in the reports.
If we want to return more than one value from the Formula Columns then it will
be used.
Triggers:
Format Trigger
Validation Trigger
Action Trigger
Report Triggers
Format Trigger : To Hide or display the layout objects dynamically it
will be used.
layout objects means frame, repeating frame, field,…………
Action Triggers: If we want to develop drill down reports then we will
use Action Triggers.
Place the Button in the layout when user press the button we can execute
some PL/SQL code.
Report Triggers: 5 Types which will fired automatically when we run the
report.
ü Before Parameter Form
ü after Parameter Form
ü Before report
ü Between pages
ü After Report
Before Parameter Form : Will Execute before the parameter form is going
to open.
To assign any default values for the parameter.
After Parameter Form : After the Parameters are entered in the
form and submit it will be executed.To change the Parameter values dynamically
And To populate the Lexical parameter values.
Before Report : It will be executed before report is executing and
select data from database.
Between Pages : When cursor moves from one page to another page it will
be executed .
It will be executed (n-1) times.
After Report : after completion of the report. once the output is reach
the destination (Printer , file, fax , e-mail) then it will be executed.
Parameters :
ü System Parameters
ü User Defined
Parameters
ü Bind
ü Lexical
User Parameters:
Bind parameters will used to pass the the value into the query in the
WHERE Clause
Lexical parameters will be used to replace the string in side of the
query in any clause.
SELECT * FROM EMP
&P_ORDER_BY
Ex:
After Parameter Form Trigger
If user pass the deptno then select between that otherwise select all the dept
employees data
SELECT *
FROM EMP
WHERE DEPTNO BETWEEN 
_FROM AND _TO
If P_from is entered then it should retrieve all dept from the specified dept
if p_to is entered then it whould retireve upto specified deptno.
WHERE DEPTNO>=:P_FROM
WHERE DEPTNO<=:P_TO
|
Bind |
Lexical |
|
ü
To pass the Values into the WHERE Clause. |
ü
To Replace the String in the Query. |
System Parameters:
DESFORMAT :
Html, pdf…..
DESNAME :
Name
of the Printer, E-mail,Fax,File
DESTYPE
:
File,
Screen, Print, Fax, E-mail
MODE
:
Bitmap, Character
Confine Mode: Lock mode if it is on then we can not take the child
object out of the Parent Object. If it is off then we can take out of the
parent.
Flex Mode: If Flex mode is on . If
we Incr/Decr the child object automatically parent object also incr/decr.
If it is off then Only child object will be decr/Incr.
Program Unit :
Is a PL/SQL objects(Procedures, Functions, Packages) which will be
stored in the Report we can use only in the Current Report.
|
Program units |
PL/SQL Objects |
|
ü
Will be stored in the file. |
ü
Stored in the Database |
Libraries : Group of Program units and Libraries We can attach the Attach the
Library to another report and use the Program units.
We can not attach the Program units to another reports directley that’s why we
will PL/SQL Library to attach.
.pll – Source Code –
Program Link Library
.plx – Executable Code –
Program Link Executable
Report Registration Steps:
1) Develop the report (.rdf) as per client requirement by using
Reports Builder
Step –
1:
select “Built a new Report manually” option and click OK button.
Step -2: Now you are in Data Model, select SQL query tool and
write required query.
Step – 3: Click on OK and connect to apps/apps@VISR12
Step- 4 : As our query is having parameters its creating two bind parameters
Step – 5: Go to Paper layout and design Frame, then keep Labels and then
design Repeating Frame with in Frame and keep Fields with in Repeating
Frames.
Go to Frame Properties
set Vertical Elasticity as Expand
Go to Repeating Frame
Properties set like
following
Go to Field Properties set like below.
Step – 6: Run the form and enter details like below.
2) Move the report from the local machine in to the server by using
WINSCP
A. CUS_TOP\11.5.0\reports\US .rdf (or)
Reports Deployment
Note: Plz notice that as we don’t have CUST_TOP in our server, we
are feeling PO_TOP as CUST_TOP
for our practice. But in real time we will use only CUST_TOP which is named
with Client Name.
Step – 1: open Putty and type below commands to know path of Custom top
Step – 2: Open WinSCP to reach the Custom Path through GUI
Step – 3: Deploy the test.rdf into Custom top Location.
3) Select System Administrator
a. Create Executable
b. Executable name
c. Application Name
d. Execution Method
e. Report (.rdf) file name
Step – 4: Create Executable by linking to test.rdf
Executable File name should be Report name which is available in server only.
4) Create Concurrent Program and attach :
concurrent program is Instance of executable file along with input
parameters and incompatible programs. Development of sample report and
Move to oracle apps and execution.
a. Executable
b. Parameters
c. Incompatibilities
Step – 5: Create Concurrent Program, while creating provide Executable name to
this.
Step – 6: Click on Parameters Tab and go to screen to provide details
like below screen. Token values should be like parameter from Report Builder.
5) Create Request group and attach Concurrent Programe
Step – 7: Go to Request Group screen , create group, or open existing
group to attach our Concurrent Program Name.
6) Create Responsibility
a. Request Group – Collection of Concurrent Programs
b. Data Group – Collection of User IDs
c. Menu – Collection of forms
Step – 7: Go to Responsibility Screen and create one responsibility and attach
our Request Group to this Responsibility.
7) Create user attach Responsibility to the user
Step – 9: Go to User Screen and attach our responsibility to existing Business
User.
Run the Report through SRS window.
- Go to File —-à switch Responsibility —–à select our responsibility
- Go to View—à Request —à find then we can fine below screen.
Click on Submit New Request button.
Click on ok button to submit single request. Then call our report to run
SRS window.
Provide the Values for Parameters before click on OK button.
Click on No button for not to submit another request.
Click on Refresh Button to make our report into complete Normal Stage.
Types of executables : These are programs
Oracle Application supports 12 types of 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 (OAF) -->
.jsp
10) PERL
scripting
--> .perl
11) Java Server Faces(ADF --> .JSF
Default Types
1) Constant
2) SQL Statement
3) Segment
4) Current Date
5) Current Time
6) Profile
These are of 8 types of Validation types
1) NONE
2) Dependent
3) Independent
4) Table
5) Translate dependent
6) Translate Independent
7) Special
8) Pair
Request Set:Request set is a collection of report / Programs that you
group together and can be submitted to run is a single interaction.
Request Id: When submit a request oracle application assigns a request
id to the request. We can use this request id to search for request.
Concurrent Manager: A concurrent manager is a
component of concurrent processing that monitors and runs tasks without typing
up your computer.
View LOG:Using this we can see the technical details of the concurrent
programs.
View Output:Using this we can see the actual result data.
Concurrent Program: After creation of executable
we will create concurrent Program by attachingExecutable, if parameters are
there we will attach parameter if incompatibility Programs are there we will
add those programs , for single executable we create Multiple concurrent
programs with different parameters.
Request Group: After creation of concurrent program we must add the
program to the request
Group. Request Group is nothing but collection of Concurrent Programs and
Reports.
Responsibility: It is level of authority where we will combine
Data Group, Request Group and
Menu. Request Group is optional, where as menu and data group is mandatory.
Data Group: Data Group is nothing but collection of applications
names and Oracle User
Names based on this Username data will be retrieved from database.
Menu: Menu is nothing but collection of functions (forms) and submenus.
Value Set: values set is nothing but list values. It will be used
to validate values while entering the parameters.
Token: Token is one the field will be used to map
concurrent Program parameters with report builder Bind variable. We will enter
the Bind variable name in the toke field so that parameters will be passed to
the Bind variable.
Required check box: By using
this check box we can make the parameters mandatory or optional
Enabled Check Box: By
using this we can enable or disable the parameter.
Display Check Box: By
using this we can hide or display the parameters in SRS Window.
Range Option:While defining the from and to parameters if we
wanted to accept Values in accession order menus from values is low and to
value is high we will Select the options called low and high.
Default Types: If we are hiding the parameters user can’t enter
the values that Time we can pass default values by using default type and
default value filed.
Scheduling the Concurrent program We
can submit the Concurrent program future date or date by using the schedule
button in SRS window.
1. As soon as possible: This is default option whenever we submit the
request it will submit the as soon as possible
2. Once: It will submit the rest only once for future date.
3. Periodically: WE can specify the from_date and to_date to submit
program periodically no of. Days months, hours, minutes and so on.
4. Specific Days: If we want submit concurrent program in the specific
days we write select this option
5. Save this Schedule: This check box will be used to save the schedule
and apply same schedule to other concurrent programs by selecting the button
called ‘Apply save schedule’
Comments
Post a Comment