Oracle Reports and Flexfields Report-Writing Steps (User, System Administrator, and Flexfields Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Oracle Reports and Flexfields Report-Writing Steps

These are the basic steps you use every time you write an Oracle Reports report that accesses flexfields data. This section assumes you already have a thorough knowledge of Oracle Reports. Though these examples contain only the Accounting Flexfield, you can use these methods for any key flexfield.

Step 1 Define your Before Report Trigger (this step is always the same)

You always call FND SRWINIT from the Before Report Trigger:

SRW.USER_EXIT('FND SRWINIT');

This user exit sets up information for use by flexfields, user profiles, the concurrent manager, and other Oracle Applications features. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 2 Define your After Report Trigger (this step is always the same)

You always call FND SRWEXIT from the After Report Trigger:

SRW.USER_EXIT('FND SRWEXIT');

This user exit frees all the memory allocation done in other Oracle Applications user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 3 Define your required parameters

You define the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

Lexical Parameters

Name Data Type Width Initial Value Notes

P_CONC_REQUEST_ID Number 15 0 Always create

P_FLEXDATA Character approximately 600 (single structure) to 6000 (roughly ten structures) Long string Cumulative width more than expected width required to hold data

Table 1 - 67. (Page 1 of 1)

Lexical Parameters

Name	Data Type	Width	Initial Value	Notes
P_CONC_REQUEST_ID	Number	15	0	Always create
P_FLEXDATA	Character	approximately 600 (single structure) to 6000 (roughly ten structures)	Long string	Cumulative width more than expected width required to hold data
Table 1 - 67. (Page 1 of 1)

You must always create the P_CONC_REQUEST_ID lexical parameter. "FND SRWINIT" uses this parameter to retrieve information about the concurrent request that started this report.

The P_FLEXDATA parameter holds the SELECT fragment of the SQL query. The initial value is used to check the validity of a query containing this parameter and to determine the width of the column as specified by the column alias. Its initial value is some string that contains columns with a cumulative width more than the expected width required to hold the data. Make sure the width of this column is sufficient. If there are total 30 segments in the table then the safest initial value will be:

(SEGMENT1||'\n'||SEGMENT2||'\n'||SEGMENT3  ... SEGMENT30)

You determine the width by determining the length of that string. That length is roughly the number of characters in the table alias plus the length of the column name, times the number of segments your code combinations table contains, times the number of structures you expect, plus more for delimiter characters as shown in the string above.

Step 4 Define your other parameters

You define the rest of the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

Lexical Parameters

Name Data Type Width Initial Value Notes

Other parameters Parameters specific to your report

Table 1 - 68. (Page 1 of 1)

Lexical Parameters

Name	Data Type	Width	Initial Value	Notes

Other parameters				Parameters specific to your report
Table 1 - 68. (Page 1 of 1)

Step 5 Call FND FLEXSQL from your Before Report Trigger to populate P_FLEXDATA

Next, given that you want to display flexfield information like concatenated values and descriptions, and arrange them in order, you make one call to FND FLEXSQL from the Before Report Trigger specifying the lexical parameters. This call changes the value of the lexical parameter P_FLEXDATA at runtime to the SQL fragment that selects all flexfields value data. For example, the parameter changes to (SEGMENT1||'\n'||SEGMENT2||'\n'||SEGMENT3||'\n'||SEGMENT4).

When you incorporate this lexical parameter into the SELECT clause of a query, it enables the query to return the concatenated segment values that are needed as input to other AOL user exits. These exits then retrieve the actual flexfield information for display purposes.

Here is an example FND FLEXSQL call. Notice that the arguments are very similar to other flexfield routine calls; CODE= and NUM= designate the key flexfield and its structure, respectively. For a report on a different key flexfield (such as the System Items flexfield), you would use a different CODE and NUM.

SRW.REFERENCE(:P_STRUCT_NUM);
SRW.USER_EXIT('FND FLEXSQL 
CODE="GL#" 
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL" 
OUTPUT=":P_FLEXDATA" 
MODE="SELECT" 
DISPLAY="ALL"');

You should always reference any source column/parameter that is used as a source for data retrieval in the user exit. This guarantees that this column/parameter will contain the latest value and is achieved by "SRW.REFERENCE" call as shown above.

Step 6 Call FND FLEXSQL from your Before Report Trigger to populate other parameters

You call FND FLEXSQL once for every lexical parameter such as P_WHERE or P_ORDERBY.

Step 7 Define your report query or queries

Define your report query Q_1:

SELECT &P_FLEXDATA C_FLEXDATA
FROM CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN 
= &P_STRUCT_NUM

The query fetches the data required to be used as input for the FLEXIDVAL user exit later.

Note: Always provide a column alias (C_FLEXDATA in this example) in the SELECT clause that is the name of column. This name of the column is required in FND FLEXIDVAL.

When the report runs, the call to FND FLEXSQL fills in the lexical parameters. As a result the second query would look something like:

SELECT (SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||
       SEGMENT4) C_FLEXDATA
FROM   CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN = 101

Step 8 Create formula columns

Now create columns C_FLEXFIELD and C_DESC_ALL (and any others your report uses) corresponding to the values and descriptions displayed in the report. They all are in group G_1. Be sure to adjust the column width as appropriate for the value the column holds (such as a prompt, which might be as long as 30 characters).

Step 9 Populate segment values formula column

To retrieve the concatenated flexfield segment values and description, you incorporate the flexfields user exits in these columns. In the column definition of C_FLEXFIELD, you incorporate the FND FLEXIDVAL user exit call in the formula field. You pass the concatenated segments along with other information to the user exit, and the user exit populates the concatenated values in this column as specified by the VALUE token. A typical call to populate segment values in this column looks as follows:

SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL 
CODE="GL#" 
NUM=":P_STRUCT_NUM" 
APPL_SHORT_NAME="SQLGL" 
DATA=":C_FLEXDATA"
VALUE=":C_FLEXFIELD" 
DISPLAY="ALL"');
RETURN(:C_FLEXFIELD);

Step 10 Populate segment descriptions

To populate the segment description use DESCRIPTION="C_DESC_ALL" instead of VALUE="C_FLEXFIELD" as in the previous call. The user exit call becomes:

SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL 
CODE="GL#" 
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL" 
DATA=":C_FLEXDATA"
DESCRIPTION=":C_DESC_ALL"
DISPLAY="ALL"');
RETURN(:C_DESC_ALL);

You have created parameters and columns that are containers of all the values to be displayed. Now, in the following steps, you create the layout to display these values on the report.

Step 11 Create your default report layout

First choose Default Layout to generate the default layout. Deselect C_FLEXDATA. Specify a "Label" and a reasonable "Width" for the columns you want to display.

Default Layout Column Settings

Column Label Width

C_FLEXFIELD Accounting Flexfield 30

C_DESC_ALL Flexfield Description 50

Table 1 - 69. (Page 1 of 1)

Default Layout Column Settings

Column	Label	Width
C_FLEXFIELD	Accounting Flexfield	30
C_DESC_ALL	Flexfield Description	50
Table 1 - 69. (Page 1 of 1)

Oracle Reports takes you to the layout painter. Generate and run the report.

Step 12 Finish your report

Adjust your report layout as needed.