弹性域报表实例(四）

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

[@[email protected]]

This example illustrates how to build a master/detail report on
multiple flexfield structures.

Sample Output

Same as sample output in the "Master-Detail Report"

Sample Layout

Same as sample layout in the "Tabular Report"

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 parameters:

Name	Data Type	Width	Initial Value	Notes
P_CONC_REQUEST_ID	Number	15	0	Always create
P_FLEXDATA	Character	6000	Very 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	Used to construct WHERE clause
P_ORDERBY	Character	16000	Valid ORDER BY clause	Used to construct ORDER BY clause
P_OPERAND1	Character	15		Used to construct the P_WHERE parameter
P_COMPANY	Character	16000	Very long string
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

P_ORDERBY and P_COMPANY are very long strings because they contain long DECODE statements for multiple structures.

Step 4 Build query parameters

Now you build parameters for three queries. First query Q_COMPANY retrieves all the companies, The second query Q_MASTER fetches one record of flexfield data for each company to build company left prompt, above prompts etc. Thus the first two queries are used to build the master record. The third query (Q_DETAIL) fetches all the flexfield data for each company.

First you populate all the parameters to be used in the first query for getting all the companies (Q_COMPANY) . Call FND FLEXSQL to populate P_COMPANY. Use this parameter to retrieve all the master records. Call this user exit as follows-

SRW.USER_EXIT('FND FLEXSQL 

CODE="GL#" 

MULTINUM="YES"

APPL_SHORT_NAME="SQLGL" 

OUTPUT=":P_COMPANY"

MODE="SELECT" 

DISPLAY="GL_BALANCING"');

Attention: In a multi-structure flexfield report MODE="WHERE" is invalid.

Step 5 Call FND FLEXSQL from your Before Report Trigger

Next, you build all the parameters of the next two queries for obtaining flexfield data. You make two calls to FND FLEXSQL from the Before Report Trigger specifying the lexical parameters.

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

SRW.USER_EXIT('FND FLEXSQL 

CODE="GL#" 

MULTINUM="YES"

APPL_SHORT_NAME="SQLGL" 

OUTPUT=":P_FLEXDATA" 

MODE="SELECT" 

DISPLAY="ALL"');

Step 7 Call FND FLEXSQL from your Before Report Trigger to populate P_ORDERBY

The second 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 AOL call is same as first one except for MODE="ORDER BY" as follows:

SRW.USER_EXIT('FND FLEXSQL 

CODE="GL#" 

MULTINUM="YES"

APPL_SHORT_NAME="SQLGL" 

OUTPUT=":P_ORDERBY"

MODE="ORDER BY" 

DISPLAY="ALL"');

Step 8 Define your report queries

Define your report's first query (Q_COMPANY) to fetch all the different companies and flexfield structure numbers.

SELECT  DISTINCT &P_COMPANY C_MASTER, 

CHART_OF_ACCOUNTS_ID C_NUM_C

FROM CODE_COMBINATIONS_TABLE 

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

When the report runs, the call to FND FLEXSQL fills in the lexical parameter to look something like:

SELECT  DISTINCT (SEGMENT1) C_MASTER,

CHART_OF_ACCOUNTS_ID C_NUM_C

FROM   CODE_COMBINATIONS_TABLE

The second query (Q_MASTER) fetches one record of flexfield data for each company to build the company left prompt and description. It is also used for constructing the above prompt for displaying concatenated flexfield value descriptions retrieved in the detail query.

SELECT  &P_COMPANY C_MASTER2,

STRUCTURE_DEFINING_COLUMN C_NUM_M,

&P_FLEXDATA C_FLEXDATA_MASTER 

FROM CODE_COMBINATIONS_TABLE 

WHERE ROWNUM < 2

AND &P_COMPANY = :C_MASTER

AND STRUCTURE_DEFINING_COLUMN = :C_NUM_C

This query has Q_COMPANY as its parent group.

You use "ROWNUM < 2" because you want only one record in that region. You use the parent-child relationship "AND &P_COMPANY = :C_MASTER" within your query, instead of using "link", so that Oracle Reports can recognize that the columns specified by your parameters are related. You create an "empty link" to G_COMPANY to make G_COMPANY the parent group.

Now you define your report detail query (Q_FLEX):

SELECT &P_COMPANY C_DETAIL,

CHART_OF_ACCOUNTS_ID C_NUM_D,

&P_FLEXDATA C_FLEXDATA [, NORMALCOLUMNS...]

FROM CODE_COMBINATIONS_TABLE

WHERE &P_COMPANY = :C_MASTER

AND STRUCTURE_DEFINING_COLUMN = :C_NUM_C

ORDER BY &P_ORDERBY

When the report runs, the four calls to FND FLEXSQL fill in the lexical parameters to look something like:

SELECT (SEGMENT1) C_DETAIL,

CHART_OF_ACCOUNTS_ID C_NUM_D

(SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||

       SEGMENT4) C_FLEXDATA [, NORMALCOLUMNS...]

FROM   CODE_COMBINATIONS_TABLE

WHERE (SEGMENT1) = :C_MASTER

AND STRUCTURE_DEFINING_COLUMN = :C_NUM_C

ORDER BY SEGMENT3, SEGMENT2, SEGMENT4, SEGMENT1

This query has G_MASTER as its parent group.

Step 9 Create Region 2 formula columns

Now create columns corresponding to the values displayed in Region 2. They all are in Q_MASTER group. To retrieve the flexfield segment value, left prompt and description, you incorporate the AOL user exits in the corresponding columns. 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(:C_NUM_M);

SRW.REFERENCE(:C_FLEXDATA_MASTER);

SRW.USER_EXIT('FND FLEXIDVAL 

CODE="GL#" 

NUM=":C_NUM_M"

APPL_SHORT_NAME="SQLGL" 

DATA=":C_FLEXDATA_MASTER"

LPROMPT=":C_BAL_LPROMPT" 

DISPLAY="GL_BALANCING"');

RETURN(:C_BAL_LPROMPT);

The user exit populates "Company" in the column 'C_BAL_LPROMPT'.

Similarly create columns C_BAL_DESC (displaying Widget Corporation) with the following calls:

SRW.REFERENCE(:C_NUM_M);

SRW.REFERENCE(:C_FLEXDATA_MASTER);

SRW.USER_EXIT('FND FLEXIDVAL 

CODE="GL#" 

NUM=":C_NUM_M" 

APPL_SHORT_NAME="SQLGL"

DATA=":C_FLEXDATA_MASTER"

DESCRIPTION=":C_BAL_DESC" 

DISPLAY="GL_BALANCING"');

RETURN(:C_BAL_DESC);

Create the above prompt ("Company-Country-Currency-Status") in the sample output by the following call:

SRW.REFERENCE(:C_NUM_M);

SRW.REFERENCE(:C_FLEXDATA_MASTER);

SRW.USER_EXIT('FND FLEXIDVAL 

CODE="GL#" 

NUM=":C_NUM_M" 

APPL_SHORT_NAME="SQLGL" 

DATA=":C_FLEXDATA_MASTER"

APROMPT=":C_APROMPT" 

DISPLAY="GL_BALANCING"');

RETURN(:C_APROMPT);

You construct columns corresponding to the region 3 of the report in the following steps.

Step 10 Create formula columns

Create formula columns C_FLEXFIELD and C_DESC_ALL to display concatenated segment values and description respectively. These columns have same group (G_DETAIL) as C_FLEXDATA. 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 11 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 incorporate AOL user exit (FND FLEXIDVAL) call in the formula field.

SRW.REFERENCE(:C_NUM_D);

SRW.REFERENCE(:C_FLEXDATA);

SRW.USER_EXIT('FND FLEXIDVAL 

CODE="GL#" 

NUM=":C_NUM_D"

APPL_SHORT_NAME="SQLGL" 

DATA=":C_FLEXDATA"

VALUE=":C_FLEXFIELD" 

DISPLAY="ALL"');

RETURN(:C_FLEXFIELD);

Step 12 Populate segment descriptions

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

SRW.REFERENCE(:C_NUM_D);

SRW.REFERENCE(:C_FLEXDATA);

SRW.USER_EXIT('FND FLEXIDVAL 

CODE="GL#" 

NUM=":C_NUM_D"

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 13 Create your default report layout

Use the Report Wizard to generate the default layout. Deselect group G_COMPANY and columns C_FLEXDATA_MASTER, C_DETAIL, C_FLEXDATA. Delete all the labels of C_BAL_LPROMPT, C_MASTER2, C_BAL_DESC, C_APROMPT as these labels are not required. 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_MASTER2		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 14 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 fields to the desired location as shown in the sample layout of Regions 2 and 3. Remove M_MASTER_HDR. Enlarge M_MASTER_GRPFR (that is the header and group frames for Master) by three lines so that it can contain boiler plate text "Accounting Flexfield" and the underline. 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 boiler plate text "Accounting Flexfield", underline below and underline below the above prompt.

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_MASTER2 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 a fixed amount and adjust due to their variable sizing. Sources of these fields are C_BAL_LPROMPT, C_MASTER2 and C_BAL_DESC respectively.

In this step you build the layout for Region 1. At the top of report, 'Foreign Currency General Ledger' is boilerplate that can be added using the 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'.

Use the Field Definition property sheet of F_FLEXFIELD to 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 description of flexfield values in line 15 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_MASTER
P_COMPANY	C_DETAIL
P_STRUCT_NUM	C_FLEXFIELD
P_WHERE	C_MASTER
	C_MASTER2
	C_NUM_C
	来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8368297/viewspace-926957/，如需转载，请注明出处，否则将追究法律责任。 0 0 分享到： 上一篇： 弹性域报表实例(三） 下一篇： 转贴：缩短向Oracle 11i迁移过程中的停机时间：对打补丁进行改进（本人受益非浅） 请登录后发表评论 登录 全部评论 <%=items[i].createtime%> <%=items[i].content%> <%if(items[i].items.items.length) { %> <%for(var j=0;j <%=items[i].items.items[j].createtime%> 回复 <%=items[i].items.items[j].username%>   回复   <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%> <%}%> <%if(items[i].items.total > 5) { %> 还有<%=items[i].items.total-5%>条评论) data-count=1 data-flag=true>点击查看 <%}%> <%}%> <%}%> yichao0614 注册时间：2009-03-06 博文量 7 访问量 14526 最新文章 转贴：缩短向Oracle 11i迁移过程中的停机时间：对打补丁进行改进（本人受益非浅） 弹性域报表实例(三） 弹性域报表实例(二） 弹性域报表实例(一） Oracle Reports 6.0 的弹性域支持 API（二） Oracle Reports 6.0 的弹性域支持 API(一） 支持我们 作者招募 用户协议 FAQ Contact Us 北京盛拓优讯信息技术有限公司. 版权所有  京ICP备09055130号-4  北京市*局海淀*网监中心备案编号：11010802021510 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员