报表 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.

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:

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

The following table lists a report summary:

报表 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.

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).

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.

Step 9 Create your default report layout

Use the Report Wizard to generate the default layout. Deselect C_FLEXDATA and C_NUM. Specify "Label" and reasonable "Width" for these columns.

The following table lists the default layout column settings:

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

The following table lists a report summary: