How to design An Accounting Flexfield Parameter for your Report?


If you want to design similar functionality as below in your report parameter, do the below steps.

Concurrent Program

1] Design two parameters to capture Accounting Flexfield From and To and use them in your report. Also set the default values.

CP Parameters

Here use the value set: XLA_SRS_ACCOUNTING_FLEXFIELD

2] XLA_SRS_ACCOUNTING_FLEXFIELD valueset is designed as below:

XLA_SRS_ACCOUNTING_FLEXFIELD

Edit:

FND POPIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”

  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS REQUIRED=”Y”

  VALIDATE=”NONE” SEG=”:!VALUE” DESC=”:!MEANING”

  NAVIGATE=”!DIR” VDATE=””

Validate:

FND VALIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”

  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS VALIDATE=”NONE”

  REQUIRED=”Y” DESC=”:!MEANING” SEG=”:!VALUE” VDATE=””

Special Value set

SRW Package in Oracle Report


SRW (Sql Report Writer) Package is a built in package in Oracle Reports Builder. It is a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.

The PL/SQL provided by the SRW package enables you to perform such actions as change the formatting of fields, run reports from within other reports, create customized messages to display in the event of report error, and execute SQL statements. There are nearly 70 functions, procedures, and exceptions are there in this package. Here I am giving brief information and uses of few important functions, procedures, and exceptions.

SRW.MESSAGE:

It is a Procedure that displays a message with the message number and text that you specify. It is mainly used to debug a report in Reports Builder.

SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);

Example:

function foo return boolean is
begin
  if :sal < 0 then
    SRW.MESSAGE(100, 'Found a negative salary. Check the EMP table.');
    raise SRW.PROGRAM_ABORT;
  else
    :bonus := :sal * .01;
  end if;
  return(true);
end;

SRW.PROGRAM_ABORT:

This exception stops the report execution and raises the following error message: REP-1419: PL/SQL program aborted. SRW.PROGRAM_ABORT stops report execution when you raise it.

SRW.DO_SQL:

This procedure executes the specified SQL statement from within Reports Builder. The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data). DML statements are usually faster when they are in PL/SQL, instead of in SRW.DO_SQL.

Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing them within Reports Builder.

Example:

FUNCTION CREATETABLE RETURN BOOLEAN IS
BEGIN
  SRW.DO_SQL('CREATE TABLE TEST_EMP (EMPNO NUMBER NOT NULL
    PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75');
    RETURN (TRUE);
   EXCEPTION
    WHEN SRW.DO_SQL_FAILURE  THEN
    SRW.MESSAGE(100, 'ERROR WHILE CREATING TEST_EMP TABLE.');
    RAISE
    SRW.PROGRAM_ABORT;
END;

SRW.DO_SQL_FAILURE:

Reports Builder raises this exception when the SRW.DO_SQL packaged procedure fails. This exception stops the report execution and raises the following error message:

REP-1425: Error running DO_SQL package – REP-msg ORA-msg.

SRW.GET_REPORT_NAME:

This function returns the file name of the report being executed.

SRW.GET_REPORT_NAME (report_name);

Example:

function AfterPForm return boolean is
    my_var varchar2(80);
  BEGIN
    SRW.GET_REPORT_NAME (my_var);
    SRW.MESSAGE(0,'Report Filename = '||my_var);
   RETURN (TRUE);
  END;

SRW.RUN_REPORT:

This procedure synchronously executes the specified report within the context of the currently running report.

SRW.RUN_REPORT (“report=test.rdf … “)

SRW.SET_FIELD:

This procedure sets the value of a character, number, or date field. This is useful when you want to conditionally change a field’s value.

SRW.SET_FIELD (object_id, text CHAR | number NUM | date DATE);

Example:

Suppose you want to conditionally change the number of a field, based on each employee’s salary. In the format trigger for the field, you could type the following:

FUNCTION CHGFIELD RETURN BOOLEAN IS
TMP NUMBER;
 BEGIN
  if :sal >= 5000 then
  tmp := :sal * 1.10;
  srw.set_field (0, tmp);
 else
  srw.set_field (0, 4000);
 end if;
 RETURN (TRUE);
END;

SRW.SET_FIELD should be used only to change the contents of a field’s datatype, not change the field to a different datatype.

Others in Brief:

  • SRW.SET_FONT_FACE: This procedure specifies font face for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_FACE(‘arial’);
  • SRW.SET_FONT_SIZE: This procedure specifies font size for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_SIZE(10);
  • SRW.SET_FONT_STYLE: This procedure specifies font style for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE);
  • SRW.SET_FORMAT_MASK: This procedure specifies the format mask for the DATE or NUMBER field. SRW.SET_FORMAT_MASK(‘mask’);
  • SRW.SET_TEXT_COLOR: This procedure specifies the global text color of the CHAR, DATE, or NUMBER field. SRW.SET_TEXT_COLOR(‘color’);

 

Bind & Lexical Parameters in Reports


Bind & Lexical Parameters in Reports

 

Bind Parameters:

Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses. You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

Lexical Parameters:

Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.

Calling a Report from a Form


Calling a Report from a Form

Using RUN_PRODUCT Built-in we can call a Report from a Form.

It will take 7 Parameters as follows

1) Product name :: REPORT/GRAPHICS

2) Module name  :: Report name

3) Communication Mode :: Synchronous /Asynchronous

SYN :: we cannot work with the Form until and unless you closes the Report.

ASYN:: We can work with Form & Reprot (Navigate)

4) Exec :: Batch/Run time

Batch :: it will fetch all records at a time

Run time :: It will fetch only one Record at a time

5) File name :: database/Filesystem

6) Parameters :: Report Parameters

7) Display :: For Graphics (Mostly null)

Ex :: If we have :p_deptno parameter then we can call as Follows

RUN_PRODUCT (‘REPORT’,’C:\TEST1.rep’,SYN,BATCH,DATABASE,p_deptno,’ ‘);

 

User Exits in Oracle Reports


User Exits in Oracle Reports

We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.

These are the user exits available in Oracle Reports that makes AOL integration.

FND SRWINIT
FND SRWEXIT
FND FORMAT_CURRENCY
FND FLEXIDVAL
FND FLEXSQL

FND SRWINIT

  • This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.
  • It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing).
  • FND SRWINIT also allows your report to use the correct organization automatically.
  • Can be used in BEFORE-REPORT Trigger.
  • Syntax is : SRW.USER_EXIT(‘FND SRWINIT’);

FND SRWEXIT

  • This user exit ensures that all the memory allocated for AOL user exits has been freed up properly.
  • Can be used in AFTER- REPORT Trigger.
  • Syntax is : SRW.USER_EXIT(‘FND SRWEXIT’);

FND FORMAT_CURRENCY

  • To format the currency amount dynamically depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.
  • You obtain the currency value from the database into an Oracle Reports column.
  • Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value.
  • A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.

Syntax:


FND FORMAT_CURRENCY
CODE=:column containing currency
DISPLAY_WIDTH=field width for display
AMOUNT=:source column name
DISPLAY=:display column name
[MINIMUM_PRECISION=:P_MIN_PRECISION]
[PRECISION={STANDARD|EXTENDED}]
[DISPLAY_SCALING_FACTOR=:P_SCALING_FACTOR]

FND FLEXSQL

Call this user exit to create a SQL fragment usable by your report to tailor your SELECT statement that retrieves flexfield values. This fragment allows you to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or sort the flexfield values returned by your SELECT statement. You call this user exit once for each fragment you need for your select statement. You define all flexfield columns in your report as type CHARACTER even though your table may use NUMBER or DATE or some other datatype.

Syntax:


FND FLEXSQL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
OUTPUT=”:output lexical parameter name”
MODE=”{ SELECT | WHERE | HAVING | ORDER BY}”
[DISPLAY=”{ALL | flexfield qualifier | segment number}”]
[SHOWDEPSEG=”{Y | N}”]
[NUM=”:structure defining lexical” | MULTINUM=”{Y | N}”]
[TABLEALIAS=”code combination table alias”]
[OPERATOR=”{ = | < | > | <= | >= | != | “||” | BETWEEN | QBE}”]
[OPERAND1=”:input parameter or value”]
[OPERAND2=”:input parameter or value”]

FND FLEXIDVAL

Call this user exit to populate fields for display. You pass the key flexfields data retrieved by the query into this exit from the formula column. With this exit you display values, descriptions and prompts by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).

Syntax:


FND FLEXIDVAL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
DATA=”:source column name”
[NUM=”:structure defining source column/lexical”]
[DISPLAY=”{ALL|flexfield qualifier|segment number}”]
[IDISPLAY=”{ALL|flexfield qualifier|segment
number}”]
[SHOWDEPSEG=”{Y | N}”]
[VALUE=”:output column name”]
[DESCRIPTION=”:output column name”]
[APROMPT=”:output column name”]
[LPROMPT=”:output column name”]
[PADDED_VALUE=”:output column name”]
[SECURITY=”:column name”]

Summary column vs. Formula Column vs. Placeholder Column


Summary column vs. Formula Column vs. Placeholder Column

We use these columns in our oracle reports, but always have few doubts about which column to use for what purposes. Here are few brief differences among these columns and the purpose of their use in oracle reports.

Summary column:

It summarizes another column and can recalculate for each record in a specified group. The following properties apply specifically to summary columns:

  • Function: The calculation to be performed on the values of the column specified in Source.
  • Source: The name of the column whose values are to be summarized.
  • Reset At: The group at which the summary column value resets to zero.
  • Compute At: The group for which a % of Total summary column is computed.

The datatype of a summary column depends on the data type of the source of the summary. If you change the data type of the source column, the datatype of the summary also changes. The Report Wizard does not support page summaries. If you select a page summary in the Field tab of the Report Wizard, an error message appears.

Formula Column:

A formula column performs a user-defined computation on the data of one or more other columns. A formula column executes a PL/SQL function and must return a value. The value can be Character, Number, or Date and returned value must match data type.

Placeholder Column:

A placeholder column is an empty container at design time. The placeholder can hold a value at run time that has been calculated and placed into it by PL/SQL code from another object.Using placeholder columns, you can:

  • Populate multiple columns from one piece of code. You can calculate several values in one block of PL/SQL code in a formula column and assign each value to a different placeholder column. Thus, you create and maintain only one program unit instead of many.
  • Store a temporary value for future reference. For example, store the current maximum salary as records are retrieved.

A Scenario:

The goal is to design a salary report of all employees. The aim of the report is to:

  • Calculate and temporarily store the name of the employee who earns the highest salary in the company.
  • Display the highest earner and the maximum salary once at the beginning of the report.

For this report, you need to create the following columns:

  • A summary to show the maximum salary for the company.
  • A placeholder to contain the highest earner’s name at run time.
  • A formula to:
    • Compare each employee salary with the maximum salary.
    • Populate the placeholder with the employee name if salary equals maximum salary.

 

Registering Reports in Oracle Application


Registering Reports in Oracle Application

 

Steps Required for Registering a Simple Report

1. Create a Report using Report Builder.

2. Compile and copy .RDF file in module specific directory.

3. Register the executable with System Administrator Module.

4. Define the Concurrent Program.

5. Assign the executable to Concurrent Program.

6. Assign the Concurrent Program to Request Group.

7. Assign the Request Group to the Responsibility.

8. Assign the Responsibility to the User.

 

Registering Parametric Reports

1. Create a Report using Report Builder with parameters.

2. Compile and copy .RDF file in module specific directory.

3. Register the executable with System Administrator Module.

4. Define the Value set to validate the parameters.

5. Define the Concurrent Program.

6. Assign the executable to Concurrent Program.

7. Define Parameters.

8. Assign Value Set to the Parameters.

9. Assign bind parameter of yours to the TOKEN.

Note: Token is used to map bind parameters with the formal parameters of the Concurrent Program.

10. Assign the Concurrent Program to Request Group.

11. Assign the Request Group to the Responsibility.

12. Assign the Responsibility to the User.

To reference the values of Prior Parameters of a particular program into the values of other parameter is based on the Value Set. The value of the 1st parameter is to be referenced in where clause of the 2nd Parameter.

WHERE Deptno = :$FLEX$.First_Parameter

Flex mode and Confine mode


Flex mode and Confine mode

Confine mode:

On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

On: parent borders “stretch” when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against
them.

 

Steps to Create Report in Oracle Reports Builder 10g


Steps to Create Report in Oracle Reports Builder 10g

In Oracle Reports, you have two options for building a paper report. The first one is to use the wizards and editors in Reports Builder. The second one is to define the data model and/or layout for your paper report in XML.

Here we will discuss the steps to build a paper report using the Report Wizard.

Step 1: Invoking Reports Builder and the Report Wizard

When you invoke Reports Builder, the Welcome dialog box gives you the option of using the Wizard to build a new report. The Report Wizard provides an easy step-by-step interface to create a new report.

The Report Wizard opens with a Welcome page. To suppress this page, clear the “Display at startup” check box. You can reinstate this page in the same way as the Welcome dialog box in Reports Builder; select the Wizard tab in the Preferences dialog box and then select Report Wizard Welcome Page.

Each page of the Report Wizard asks you for information to help you create your initial report. Step through the wizard pages, selecting Next and Back, until you are satisfied with the initial information that you have entered. On the last page, select Finish.

Welcome to Reports Builder

Report Wizard

Step 2: Choosing the Layout Type

Here you have to specify the type of layout you want the Wizard to generate. The available options are:

  • Web and Paper Layout 
  • Web Layout only 
  • Paper Layout only

Report Layout Type

 Step 3: Choosing a Report Style

This page of the Report Wizard shows the various styles of reports. Select Tabular and then click Next.

Report Style

Step 4: Selecting the Data Source Type

Next, you have to define the data source type for your report. Through the implementation of the Pluggable Data Source (PDS) feature in Oracle Reports, the data for your report can come from any source you choose. Reports Builder provides interface definitions that act as a translator between Reports Builder and a PDS by redefining Reports Builder’s requests in terms your data source uses.

Oracle Express Server, OLAP, JDBC, Text and XML pluggable data sources are shipped with Oracle Reports. You can also define your own data source.

Data Source Type

Step 5: Building a Query using Query Builder

Building your query with the Query Builder GUI saves you time and increases the ease of use for developers not familiar with building SQL statements or with the application tables.

To build a query using Query Builder:

  1. Select Query Builder from the Query page in the Report Wizard.
  2. Enter your username, password, and alias in the Connect dialog box that appears if you have not already connected to the database. 
  3. Select the data tables to build the query. 
  4. Click Include. The tables appear in the selection area. 
  5. Click Close to close the Select Data Tables window. 
  6. In each table, double-click the column names that you want in the query, or use the check boxes. To select all columns, double-click the Table title bar.
  7. Click OK.

Query Builder copies the query syntax into the Report Wizard. You can modify the query by reentering Query Builder or by modifying the SQL query statement text.

Note: If you prefer to write your own SQL statement, enter the syntax directly in the SQL query statement area of the Query page. Alternatively, you can import the contents of a file by clicking Import SQL Query.

Query Builder

Step 6: Selecting Displayed Fields

In the Field page, select each field from the Available Fields list and click >. The selected fields move to the Displayed Fields list. To display all fields, click >>.

You can alter the sequence of displayed fields by dragging one field above or below another in the list. The sequence of fields in this list determines how the fields appear in the report output. In a tabular report, the fields appear in sequence from left to right across the page.

Fields that remain in the Available Fields list are available for you to reference in your report definition as hidden fields or in PL/SQL trigger code.

In the report output, the user sees only those fields that you transfer to the Displayed Fields list.

Displayed Fields

Step 7: Totals and Labels

In the next two pages of the Report Wizard, you can create totals based on any of the displayed fields and modify the labels and width of the displayed fields.

Totals: Standard SQL aggregate functions are provided for creating totals in your report.

Total Fields

Labels: The field label is displayed on one or more lines in the report output. In a tabular report, the labels appear above the field values.

  • If the initial label is wider than the field, Reports Builder allows enough space for the label, or displays it on multiple lines.
  • If you increase the number of characters in the label text in the reentrant Wizard, the label can appear truncated in the report output.

Labels

Step 8: Selecting a Report Template

Report Templates enforce corporate standards as well as create professional-looking paper reports easily.

Select a template from the list of predefined template names. In a template, the fonts, styles, and colors are already selected for designated objects. A variety of templates are available with the standard Reports installation.

To select a predefined template:

  1. Select the Predefined Template option button, if it is not already selected.
  2. Select a template from the Template list.
  3. Click Finish.

Report Template

Step 9: Viewing the Paper Report Output

When you finish creating your report in the Report Wizard, the output appears in the Paper Design view of the Report Editor.

Magnifying the Output

The Paper Design view contains a Magnify tool in the vertical toolbar. This provides a view of the area of layout you want to see. You can also use the View menu to magnify or reduce the size of the output. Select View > Zoom to see your options.

Viewing Different Pages

The Paper Design toolbar contains four buttons, and the specific page option, with which you can scroll through the pages of your report.

Report Output

Step 10: Saving the Report Definition

Remember to save the report frequently by selecting Save in the toolbar, or by using the File > Save menu option. The recommended format for storing paper reports is with an .rdf extension.

If you want to make a copy of the report definition in a different filename, use the menu option File > Save As. There is no toolbar button for the Save As option.

Source: Oracle

Oracle Reports:An Introduction


Oracle Reports:An Introduction

Oracle Reports, the high-fidelity enterprise reporting tool, enables businesses to give immediate access to information to all levels within and outside of the organization in a scalable and secure environment. Using Oracle Reports, you can publish information from any data source, in any format (PDF, HTML, printed, XML, etc.), to any destination (Web, Portal, e-mail, file, etc.) in a scalable, efficient manner.

The main features are:

  • You can access data from any data source
  • You can obtain the report data in a graphical representation by a query builder
  • You can use default report templates or you can customize it if needed
  • A live editor that allows you to modify paper report layouts in WYSIWYG (“what you see is what you get”) mode
  • The ability to add dynamic report output to an HTML page by embedding custom JavaServer Page (JSP) tags within an HTML document
  • You can graphically represent report data with the help of  integrated chart builder
  • You can use various web publishing tools that dynamically generate Web pages based on your data
  • You can show the report data in various formats such as HTML, HTMLCSS, XML, PDF, PCL (Printer Control Language), Postscript, and ASCII
  • Support for run-time customization
  • You can execute dynamic SQL statements within PL/SQL procedures
  • Seamless integration of Oracle Reports with OracleAS Portal for administering report security
  • The ability to publish report output to portlets

Designing Reports

Before you create a report using any report-writing software, you must first consider the type of report that you are being asked to produce. You will have a specification of the needs, required output, and the expected publishing medium, but you also need to know the underlying structure that supports the requirement and the most efficient way to retrieve data.

Common report styles

The majority of report requirements fall into the following categories:

  • Tabular: One group
  • Master-detail: Master-detail hierarchy (may be several levels)
  • Master and multiple details: Two or more detail groups at the same level
  • Matrix: Two masters, one detail

Running a Report

There are many ways of running a report, depending on the application design. You can call a report from:

  • A Web browser
  • An OracleAS Portal application
  • The command line, using the Start > Run option in Windows
  • The OracleAS Reports Queue Manager
  • A Java application
  • A database trigger
  • A customized menu in a Forms application
  • A button in a Forms application

Supported File Types

  • RDF: Report Definition File: binary file containing source code
  • REP: Report: binary file without source code
  • JSP: JavaServer Page format
  • HTML: HyperText Markup Language
  • XML: Extensible Markup Language

Reports Builder Modules

The Reports Builder interface enables you to create a number of different types of modules, and it provides a Report Editor in which you can view the structure and objects in a report module. The Reports Builder module types are:

  • Report: A report definition
  • Template: A skeleton definition containing common style and standards
  • PL/SQL Library: A stand-alone library containing PL/SQL program units—procedures, functions, packages—that can be called from multiple reports

Report Data and Layout

A report definition defines two main parts of a report and brings them together in the output.

  • Data: Data structure and data to be displayed
  • Layout: Formatting information about how the data appears in the output

Each report module can have a data model, a paper layout, and a Web layout. The data model, as well as program units, can be shared by the paper and Web layouts.

A report can consist of:

  • A data model and a paper layout.
  • A data model and a Web layout.
  • A data model, a paper layout, and a Web layout.

Reports Builder Components

Object Navigator

The Object Navigator is a hierarchical browsing and editing interface that enables you to locate and manipulate application objects quickly and easily.

Report Editor

The Report Editor contains different views to help you handle the data objects and layout objects for Web and paper reports.

Property Inspector

All objects in a module, including the module itself, have properties that you can see and modify in the Property Inspector.

PL/SQL Editor

The PL/SQL Editor enables you to create and compile program units such as procedures, functions, and packages within the current report.

Wizards in Reports Builder

Wizards provide an easy step-by-step interface for commonly performed tasks. The wizards in Reports Builder are:

  • Report Wizard: The Report Wizard guides you through the steps to create a basic paper report. Each page of the wizard asks you for information to help you create your initial report.
  • Data Wizard: This wizard helps you quickly define or modify a query for a multiquery data models.
  • Graph Wizard: You can add a variety of charts and graphs, including true 3-dimensional graphs, to a report using the Graph Wizard. Charting is implemented in Reports Builder with the Oracle BI graph bean.
  • Report Block Wizard: This wizard enables you to quickly create a JSP report by embedding report data into a Web page using Reports custom JSP tags.