Previous	Next		Contents	Index	Navigation	Glossary	Library

Sample Output

Note: Line numbers listed above are for explanation purposes only and do not appear in report output.

Report Writing Steps

Step 1 Define your Before Report Trigger

SRW.USER_EXIT('FND SRWINIT');

Step 2 Define your After Report Trigger

SRW.USER_EXIT('FND SRWEXIT');

Step 3 Define your parameters

Define the following parameters 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	600	Long string	Cumulative width more than expected width required to hold data
P_STRUCT_NUM	Character	15	101	Contains structure number
Table 1 - 73. (Page 1 of 1)

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

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"');

Step 5 Define your report query

Define your report query Q_1:

SELECT &P_FLEXDATA C_FLEXDATA,
CHART_OF_ACCOUNTS_ID C_NUM
FROM CODE_COMBINATIONS_TABLE

Please note the difference in the query from the queries earlier. This query contains one extra column C_NUM. You use this column to supply the structure number in the user exit FND FLEXIDVAL.

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

SELECT (SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||
       SEGMENT4) C_FLEXDATA,
CHART_OF_ACCOUNTS_ID C_NUM
FROM   CODE_COMBINATIONS_TABLE

Step 6 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 7 Populate segment values formula column

To retrieve the concatenated flexfield segment values and description, you incorporate the AOL user exits in these columns. In the column definition of C_FLEXFIELD you incorporate the FND FLEXIDVAL call in the formula field.

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

Step 8 Populate segment descriptions

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

SRW.REFERENCE(:C_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL 
CODE="GL#" 
NUM=":C_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 9 Create your default report layout

First choose Default Layout to generate the default layout. Deselect C_FLEXDATA and C_NUM. Specify "Label" and reasonable "Width" for these columns.

Default Layout Column Settings
Column	Label	Width
C_FLEXFIELD	Accounting Flexfield	30
C_DESC_ALL	Flexfield Description	50
Table 1 - 74. (Page 1 of 1)

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

Report Summary
Lexical Parameters	Columns	FND User Exits
P_CONC_REQUEST_ID	C_FLEXDATA	FND FLEXIDVAL
P_FLEXDATA	C_DESC_ALL	FND FLEXSQL
	C_NUM	FND SRWINIT
		FND SRWEXIT
Table 1 - 75. (Page 1 of 1)

See Also