弹性域报表实例(一）

最新推荐文章于 2024-10-02 00:27:17 发布

congjiyin7250

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量136

点赞数

文章标签：数据库

本节示范如何将弹性域数据纳入报表，以及如何使用 Oracle Application Object Library (AOL) 用户出口来建立不同类型的弹性域报表。以下样本报表示范了五种构造报表的方法。

[@more@]

报表 1：简单表格式报表

报表 2：具有多弹性域结构的简单表格式报表

报表 3：表格式报表

报表 4：主 - 明细报表

报表 5：多结构的主 - 明细报表

前两个实例显示了建立具有弹性域支持的报表所涉及到的基本步骤。接下来的两个实例是单一弹性域结构的报表，并显示弹性域支持的附加功能。第五个报表示范如何使用这些多弹性域结构的功能。

报表 1：简单表格式报表

This is a sample report that selects Accounting Flexfield values for a single structure for a single company. This report uses a simple WHERE clause and does not use an ORDER BY clause.

Sample Output

This report contains a list of Accounting Flexfield combinations and a description for each based on their segment values.

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 parameters in the following table using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

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

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

FROM CODE_COMBINATIONS_TABLE

WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN

= &P_STRUCT_NUM

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 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 descriptions, you incorporate the AOL user exits in these columns. In the column definition of C_FLEXFIELD, you incorporate the FND FLEXIDVAL user exit call 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 8 Populate segment descriptions

To populate the concatenated segment descriptions 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 9 Create your default report layout

Use the Report Wizard to generate the default layout. Deselect C_FLEXDATA. Specify a "Label" and a reasonable "Width" for the columns you want to display.

The table below lists the default layout column settings:

Column	Label	Width

C_FLEXFIELD	Accounting Flexfield	30
C_DESC_ALL	Flexfield Description	50

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

The following table lists a report summary:

Lexical Parameters	Columns	FND User Exits

P_CONC_REQUEST_ID	C_FLEXDATA	FND FLEXIDVAL
P_FLEXDATA	C_DESC_ALL	FND FLEXSQL
P_STRUCT_NUM		FND SRWINIT
		FND SRWEXIT

报表 2：具有多弹性域结构的简单表格式报表

This is a sample report that selects Accounting Flexfield values for multiple flexfield structures (charts of accounts). This report uses a simple WHERE clause and does not use an ORDER BY clause, but differs from Report 1 in that this report selects a structure number.

Sample Output

This report contains a list of Accounting Flexfield combinations and a description for each based on their segment values.

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 parameters in the following table using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

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

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

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

Attention: Use word-wrapping for flexfield columns if necessary to avoid possible truncation of your values. Do this by setting Sizing to Expand.

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.