Oracle Apps D2K Reports Overview

Report Builder is one of the Oracle GUI (Graphical user Interface) Tool

By using this we can develop simple and Complex reports

Oracle Applications reports can be developed and registered with below approaches

  • Report Builder
  • XML Publisher/BI Publisher
  • PLSQL

In this section, we mainly focus on the concepts of Report builder and how can we register the reports in Oracle apps through Report builder

We can develop the report in two ways

  • Wizard
  • Manual

There are two concepts to remember while developing the reports as below

  • Data Model
  • Layout model

Data Model will be used to select the data from Database and generally will include SQL statements, Place Holder, Summary Columns, Formula Columns etc.,

Layout model is used as User Interface(UI) application which should be visible to the end users along with the data in respective Fields.

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 columns            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.

  1. Before Parameter Form
  2. After Parameter Form
  3. Before report
  4. Between pages
  5. 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
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

BindLexical
To pass the Values into the WHERE Clause.
We will use the symbol “:”
Can be used only in the WHERE Clause.
Any Data Type.
Values will be passed in the Parameter Form.
To Replace the String in the Query.
We will use the symbol “&”
In any Clause we can use this.
Must be Character.
Values will be assigned dynamically form in the After Parameter Form Trigger.

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 unitsPL/SQL Objects
Will be stored in the file
Can be used only in the Current Report
Improve the Performance
Stored in the Database
We can use any report
It takes more time

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 in Oracle Applications:

1) Based on Client requirement, prepare SQL statement and develop the report (.rdf) 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@VIS


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:  Ideally, you have to move the .rdf file to CUST_TOP path in Server but here we were moving to PO_TOP for explanation. But in real project, we use only CUST_TOP.
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 XX_TEST_RPT.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 XX_TEST_RPT.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 Program


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.

 

  1. Go to File => switch Responsibility => select our responsibility
  2. Go to View => Request => find then we can fine below screen.
  3.  

 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 executable types. You can find these in executable Form

Navigation => Application Developer => Concurrent Program => Executable
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’

Under Progress………….