报表 3:表格式报表
[@more@]This is a sample report that selects Accounting Flexfield information for a single structure for a single company. This report uses a more complex WHERE clause with an ORDER BY clause. It also contains extra columns for the report header information.
Sample Output
This report contains a list of Accounting Flexfield combinations and a description for each based on their segment values. It has a more complex header that includes the set of books, date, currency, period, and page number.. The company name is also displayed.
Note: Line numbers listed above are for explanation purposes only and do not appear in report output.
Sample Layout
This diagram shows the layout for this report. It has a header region with the report title, the set of books, date, currency, period, and page number, another region for the organization name, and a third region for the Accounting Flexfield combinations and their descriptions.
Note: *'s indicate displayed fields.
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.
The following table lists the 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 the data |
P_STRUCT_NUM | Character | 15 | 101 | Contains structure number |
P_WHERE | Character | 200 | Valid WHERE clause | (4) |
P_ORDERBY | Character | 298 | Valid ORDER BY clause | (5) |
P_OPERAND1 | Character | 15 | Used to construct the P_WHERE parameter | |
P_SET_OF_BOOKS | Character | Obtain from GL | Use in the report header | |
P_CURRENCY | Character | 15 | Use in the report header | |
P_PERIOD | Character | Obtain from GL | Use in the report header |
Note (4): This parameter contains the WHERE clause in the SELECT statement to enforce condition(s) on the data retrieved from the database. The initial value is used to check the validity of query containing this parameter.
Note (5): This parameter contains the ORDER BY clause for the SELECT statement that orders the display of flexfield data. The initial value is used to check the validity of query containing this parameter.
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 Call FND FLEXSQL from your Before Report Trigger to populate P_WHERE
The second call populates the value of lexical P_WHERE to the restriction you wish to apply at run time. You wish this parameter to contain the value "(SEGMENT1 = '01')" if GL_BALANCING segment is segment 1 and value of P_OPERAND1 is "01".
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.USER_EXIT('FND FLEXSQL
CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
OUTPUT=":P_WHERE"
MODE="WHERE"
DISPLAY="GL_BALANCING"
OPERATOR="="
OPERAND1=":P_OPERAND1"');
Step 6 Call FND FLEXSQL from your Before Report Trigger to populate P_ORDERBY
The third call changes the value of lexical P_ORDERBY to the SQL fragment (for example to SEGMENT3, SEGMENT2, SEGMENT4, SEGMENT1) at run time. When this lexical parameter is incorporated into the ORDER BY clause of a query, it enables the query to order by flexfield segments. The user exit call is same as first one except for MODE="ORDER BY" as follows:
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.USER_EXIT('FND FLEXSQL
CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
OUTPUT="P_ORDER_FLEX"
MODE="ORDER BY"
DISPLAY="ALL"');
Step 7 Define your report queries
Define your report queries Q_1 and Q_2:
SELECT &P_FLEXDATA C_FLEXDATA_H [, NORMALCOLUMNS...]
FROM CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN
= &P_STRUCT_NUM
AND ROWNUM < 2
SELECT &P_FLEXDATA C_FLEXDATA [, NORMALCOLUMNS...]
FROM CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN
= &P_STRUCT_NUM
ORDER BY &P_ORDERBY
The first query fetches the data required for region 2 and the second one for region 3.
Note: "ROWNUM < 2" because we want only one record in that region.
When the report runs, the three calls 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,
NORMALCOLUMNS...
FROM CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN
= 101
ORDER BY SEGMENT3, SEGMENT2, SEGMENT4, SEGMENT1
Step 8 Create formula columns
Now create columns corresponding to the values displayed in Region 2. 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).
First create column C_BAL_LPROMPT (for columns corresponding to "Company" in the sample output). In this column incorporate FND FLEXIDVAL calls in the formula field. You pass the concatenated segments along with other information to the user exit:
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA_H);
SRW.USER_EXIT('FND FLEXIDVAL
CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
DATA=":C_FLEXDATA_H"
LPROMPT=":C_BAL_PROMPT"
DISPLAY="GL_BALANCING"');
RETURN(:C_BAL_LPROMPT);
The user exit populates "Company" in the column 'C_BAL_LPROMPT'.
Similarly create columns C_BAL_VAL and C_BAL_DESC (displaying "01" and Widget Corporation) with the following calls.
C_BAL_VAL:
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA_H);
SRW.USER_EXIT('FND FLEXIDVAL
CODE="GL#" NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
DATA=":C_FLEXDATA_H"
VALUE=":C_BAL_VAL"
DISPLAY="GL_BALANCING"');
RETURN(:C_BAL_VAL);
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA_H);
SRW.USER_EXIT('FND FLEXIDVAL
CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
DATA=":C_FLEXDATA_H"
DESCRIPTION=":C_BAL_VAL"
DISPLAY="GL_BALANCING"');
RETURN(:C_BAL_DESC);
Create the above prompt (displaying "Company-Country-Currency-Status") in the sample output by the following call.
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA_H);
SRW.USER_EXIT('FND FLEXIDVAL
CODE="GL#" NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
DATA=":C_FLEXDATA_H"
APROMPT=":C_APROMPT"
DISPLAY="GL_BALANCING"');
RETURN(:C_APROMPT);
Step 9 Create formula columns
Now you construct columns corresponding to the region 3 of the report. All columns now correspond to G_2. 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).
You create formula columns C_FLEXFIELD and C_DESC_ALL to display concatenated segment values and description respectively.
Attention: Use word-wrapping for flexfield columns if necessary to avoid possible truncation of your values. Do this by setting Sizing to Expand.
Step 10 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 call the user exit FND FLEXIDVAL in the formula field.
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 11 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(: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 12 Create your default report layout
Use the Report Wizard to generate the default layout. Deselect C_FLEXDATA, C_FLEXDATA_H. Specify reasonable widths for these columns.
The following table lists the default column settings:
Column | Label | Width |
C_FLEXFIELD | Accounting Flexfield | 30 |
C_DESC_ALL | Flexfield Description | 50 |
C_APROMPT | 100 | |
C_BAL_DESC | 40 | |
C_BAL_LPROMPT | 20 | |
C_BAL_VAL | 4 |
Oracle Reports takes you to the layout painter. Before modifying the default layout in the painter, you may want to generate and run the report with the current layout to test the previous steps.
Step 13 Finish your report
Now you modify the default locations of the fields and create new fields in the layout painter. First [SELECT ALL] and move all the fields to the desired location as shown in the Region 2 & 3.
You modify fields to display "Company", "01" and "Widget Corporation" in the Group 1 (region 2). As shown in the Sample Layout, modify F_BAL_LPROMPT, F_BAL_VAL and F_BAL_DESC fields so that they are side by side with the unit length. Specify "Horizontal Sizing" as "Variable". This ensures that the fields always be apart by fixed amount and adjust due to their variable sizing. Sources of these fields are C_BAL_LPROMPT, C_BAL_VAL and C_BAL_DESC respectively.
Resize and move the field F_APROMPT as shown in the sample layout to display above prompt as displayed in the sample output. Add all the boilerplate text "Accounting Flexfield", underline below and above the above prompt.
In this step you build the layout for Region 1. At the top of report, 'Foreign Currency General Ledger' is a boiler plate that can be added using layout painter. 'Currency:' and 'Period:' are also Boiler plates and the corresponding fields ('CND' and DEC-90) are filled by lexical input parameters P_CURRENCY, P_PERIOD. 'Set of Books 2' is filled by input lexical parameter P_SET_OF_BOOKS. Similarly, the 'Date' and 'Page' fields are filled by system parameters 'Current Date' and 'Logical Page Number'.
Enter in the Field Definition property sheet of F_FLEXFIELD and specify "Vertical Sizing" as "Variable". This ensures that when the data is larger than the field width, the value wraps and it is not truncated. This can be seen in the descriptions of flexfield values in lines 15 and 16 of the sample output.
The following table lists a report summary:
Lexical Parameters | Columns | FND User Exits |
P_CONC_REQUEST_ID | C_APROMPT | FND FLEXIDVAL |
P_FLEXDATA | C_BAL_DESC | FND FLEXSQL |
P_CURRENCY | C_BAL_LPROMPT | FND SRWINIT |
P_OPERAND1 | C_BAL_VAL | FND SRWEXIT |
P_ORDERBY | C_DESC_ALL | |
P_PERIOD | C_FLEXDATA | |
P_SET_OF_BOOKS | C_FLEXDATA_H | |
P_STRUCT_NUM | C_FLEXFIELD | |
P_WHERE |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8368297/viewspace-926950/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8368297/viewspace-926950/