Reports (Quick Interview Guide)

1) Oracle Report Developer uses many Environment Variables. These have Default Values & can be modified in our own environment for different applications.
Some of the variables are,
a) REPORTS60_PATH(Path that Report Builder searches for files at Runtime
b) UI_ICON (Path that all builder search for icon files at run time)
c) REPORTS60_TMP (Path that will be used to Create temporary files)
Note: Windows Registry is used to modify this paths,
Registry Path: HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME1

2) If we have MAPI (Microsoft Messaging Application Program Interface) mail client, we can send Reports as an E-Mail Attachment.

3) Report Editor contains,
a) Data Model: Displays Structural Representation of Data in a Report
b) Layout Model: Displays the layout objects in a report & allows you to make many modifications to any layout object.
c) Live Previewer: Displays Report output & allows you to make commonly Required modifications to the layout.
d) Web Previewer: Preview your Report in your Web Browser in formats like PDF, HTML, XML.etc.
e) Parameter Form: Displays the layout of the parameter form that, at run Time, allows user input of parameter values

4) Data Model Objects: Query, Group, Columns(Formula, Summary & Placeholder), Data Link, Parameters (User & System Parameters)
Layout Model Objects: Repeating Frame, Main Frame, Field, Boilerplate Text
Parameter Form Objects: Field, Boilerplate Text

5) Three Significant Features in Form-Like Style that differ from Tabular Style are,
a) Labels appear to the left of each Field
b) Each Field Appears to the left of Previous Field, across the page
c) Each Record Appears on a new page

6) Difference Between Mailing Label & Form Letter
a) Mailing Label: Multiple Records on one page
b) Form Letter: One Record on each page

7) Live Previewer Window Consists of,
a) Toolbar
b) Tool palette
c) Stylebar(Currency Symbol, Add & Remove Decimal Places,% Symbol,1000 Seperator)
d) Statusbar
Note: In Live Previewer we can Format the Objects Conditionally by option Format-Conditional Formatting

8) .rdf : Full Report Definition, Not Modifiable Binary, Executable, Portable if Transferred as binary
.rep : No Source Code or Comments, Not Modifiable Binary, Executable, Portable as binary if no PL/SQL is present
.rex : Full Report Definition, Not Modifiable ASCII text, not Executable, 100% Portable

9) Maximum Rows to Fetch Restrict the Rows Returned from a Query. It is the Property of Query. Whereas Group Filter Property is of Group where we can filter Records using First, Last Option or PL/SQL Code.

10) There are 4 types of columns in Report:
a) Database columns
b) Formula columns: This are used to perform Computations of another columns data including Placeholder column. It returns one value which is Computed using PL/SQL
c) Summary columns: This columns Performs Computation on another columns data
d) Placeholder columns: This are the columns for which Datatype & Values are set in PL/SQL Code. This code is written at Formula level trigger or Report level trigger

Note: We can Reset Summary Column at Report, Group or Page Level.

11) There are 2 types of User Parameters in Report:
a) Lexical Parameters:
· Lexical Parameters are Placeholders for Text
· We can use Lexical References to Replace clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, START WITH.
· We cannot make Lexical References in PL/SQL Statements
· Lexical References are also used for Multiple Clauses
· Lexical References are created by entering ‘&’ followed by Parameter name

b) Bind Parameters:
· Bind Parameters are used to Replace a Single Value in SQL or PL/SQL, such as character string, number or date
· Bind References are also used to replace Expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, & START WITH.
· We cannot make Bind References in From Clause or in place of Reserved words
· Bind References are created by entering colon (:) followed by Parameter name

12) There are 2 types of Modes in Report:
Confine Mode
ON- We cannot move object outside its correct Enclosing object
OFF- We can move the object outside its Enclosing object & Resize it
Flex ModeON- When we move or Resize one object all affected objects are also Adjusted
OFF- We can move object Individually without affecting other objects

13) Additional Layout Option: It is used to create more than one Layout for the Same Report

14) Buttons enables users to interact with a Report in the Previewer at Runtime. Buttons have no meaning in a printed report. They are created in Report area to,
a) Access a URL
b) Drill down to a separate detail report
c) Display Multimedia information related to Report items

15) Anchors: This are used to Determine the Vertical & Horizontal Positioning of a child object relative to its Parent. Some of the Important Rules are;
a) An object may be Anchored to only one other object
b) Matrix objects like Repeating Frame, Anchors & Margin cannot be anchored to anything.
c) Objects cannot be anchored in such a way that they have a Circular dependency
d) Nothing can be anchored to a hidden object
e) An anchor cannot be resized

16) There are 4 types of Object Elasticity:

a) Fixed: Identical to Layout object size
b) Expand: Can be larger than Layout, but not smaller
c) Contract: Can be smaller than layout, but not larger
d) Variable: Can be larger or smaller than layout

17) Print Direction: It is the Property of Repeating Frame. They are of 4 types:
a) Down: Prints sequential records down the page. At the end of this page, records overflow onto the next page
b) Across: Prints sequential records across the page. When records reach the right edge of the page, they overflow onto a new page.
c) Down/Across: Prints sequential records down the page. At the end of the page, records move across to form a new column if there is a room, otherwise they overflow onto a new page. It is used for Panel/Print Order Property.
d) Across/Down: Prints sequential records across the page. When records reach the right edge of the page, they move down below the previous set of records if there is room, otherwise they overflow onto a new page. Used for Panel/Print Order property.

18) System Variables & System Parameters are,
System Variablesa) Current Date
b) Page Number
c) Panel Number
d) Physical Page Number
e) Total Pages
f) Total Panels
g) Total Physical Pages

System Parameters
a) Background
b) Copies
c) Currencies
d) Decimal
e) Desformat
f) Desname
g) Destype
h) Mode
i) Orientation
j) Print Job
k) Thousands

19) Types of Triggers:
a) Report Trigger: Order of Execution of Report Trigger is,
· Before Parameter form trigger is fired
· Runtime Parameter form appears
· After Parameter form trigger is fired
· Report is Compiled & Queries are Parsed
· Before Report Trigger is fired
· Report is Executed & Data is fetched from database
· Between pages fires for each page except last one
· After Report trigger is fired

b) Group Filter Trigger: It is a PL/SQL Function that determines which record to include in a group. The function must return Boolean value. If it returns True then record is included in group & if False then Excluded.

c) Formula Column Trigger: This are PL/SQL Functions that populate formula or placeholder columns. A column of Datatype Number can have a formula that returns a value of datatype Number.

d) Validation Trigger: This are PL/SQL Functions that are Executed when Parameter values are Specified on the Command line, when we accept Runtime Parameter form. It returns only a Boolean Value.

e) Format Trigger: This are PL/SQL Functions that are Executed before the object is formatted. It is used to dynamically change the formatting attributes of the object. It returns Boolean Value i.e. True or False to include or Exclude Current instance of object from Report output.

f) Action Trigger: This are PL/SQL Procedures Executed when a button is Selected in the Runtime Previewer. The Trigger is used to dynamically call another report or execute any other PL/SQL code.

20) Cursor Variables: This are the Variables to which Datatype Refcursor is Assigned. This are like C or Pascal pointers, which hold the memory location of some item instead of item itself. Cursor variables are mainly used to pass Query Result sets between PL/SQL stored subprograms.
Ref Cursor Query uses PL/SQL to fetch the data for the report. In Refcursor query, you specify a PL/SQL function that returns a cursor value from a Cursor Variable.

Eg: Package cv is
Type comp_rec is RECORD
(deptno number, ename varchar2(10), compensation number);
Type comp_cv is REFCURSOR return comp_rec;
Function emprefc(deptno1 number) return comp_cv
End;

PACKAGE BODY cv Is
Function emprefc(deptno1 number) return comp_cv is
Temp_cv cv.comp_cv
Begin
If deptno1>20 then
Open temp_cv for select deptno, ename, 1.25*(sal+nvl(comm,0)) Compensation
From emp where deptno = deptno1;
Else
Open temp_cv for select deptno, ename, 1.15*(sal+nvl(comm,0)) Compensation
From emp where deptno = deptno1;
End if;
Return temp_cv;
End;

21) User Exit: It is used when we want to pass control from Report Builder to a program which performs some function, and then returns control to Report Builder. They are of 3 types:
a) ORACLE Precompiler user Exits
b) OCI (Oracle Call Interface) User Exits
c) Non-Oracle User Exits

Some of the Important User Exits used if one is using Oracle Application Object Library Features in his Report are,
a) FND SRWINIT: It Fetches Concurrent Request Information & sets up the Profile Options. It is called always from Before Report Trigger

b) FND SRWEXIT: It frees all the Memory Allocations done in other Oracle Applications user exits. It is called always from After Report Trigger

c) FND FLEXSQL: It is also called from Before Report Trigger. It is used to Pass the concatenated segment values from the underlying Code Combination Tables to the user exit so that it can display appropriate data and derive the description & values from switched value set as needed. Its Main Parameters are,
1) CODE
2) APPL_SHORT_NAME
3) OUTPUT (Name of the Lexical Parameter to store the SQL Fragment)
4) MODE (Select, Where, Having, Order By, Group By)
5) DISPLAY (Specify Segment Number, Flexfield Qualifier or ALL for all segments)
6) NUM OR MULTINUM (Chart of Account Id)
7) OPERATOR (Between And, etc.)
8) OPERAND1
9) OPERAND2

d) FND FLEXIDVAL: It is Called from Formula Column. It automatically fetches more Complicated Information such as Descriptions & Prompts so that one does not have to use Complicated Table joins to the flex field tables. Its Main Parameters are,
1) CODE
2) APPL_SHORT_NAME
3) DATA (Source Column or Parameter indicated by Colon)
4) NUM (Chart of Account Id)
5) SHOWDEPSEG
6) DISPLAY (Column where the Output is Displayed)
7) IDISPLAY
8) APROMPT

e) FND FORMAT_CURRENCY: It is used to format the Currency Amount dynamically depending upon the Location (Country) of site.
1) CODE (Column Which Contains Currency code for the Amount)
2) DISPLAY_WIDTH (Width of the field in which formatted Amount is displayed)
3) AMOUNT (Name of Column which Contains Amount Retrieved from Database)
4) DISPLAY (Name of Column into which we want to display formatted values)
5) MINIMUM_PRECISION (Bind Parameter)
6) PRECISION (Standard or Extended)
7) DISPLAY_SCALING_FACTOR (Bind Parameter)

22) Matrix Contains at least 4 Groups:
a) Column Group b) Row Group
c) Cross Product Group d) Cell Group

23) OCI: It is Oracle Call Interface. When applications developers demand the most powerful
interface to the Oracle Database Server, they call upon the Oracle Call Interface (OCI). OCI provides the most comprehensive access to all of the Oracle Database functionality. The newest performance, scalability, and security features appear first in the OCI API. If you write applications for the Oracle Database, you likely already depend on OCI. Some types of applications that depend upon OCI are:
· PL/SQL applications executing SQL
· C++ applications using OCCI
· Java applications using the OCI-based JDBC driver
· C applications using the ODBC driver
· VB applications using the OLEDB driver
· Pro*C applications
· Distributed SQL

24) Precompiler: A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Examples are the Pro*C Precompiler for C, Pro*Cobol for Cobol, SQLJ for Java etc.

25) Term is a Terminal Definition file that describes the Terminal from which you are using R20DES (Report Designer)