报表 4：主 - 明细报表

This example illustrates how to build a master/detail report. In this sample report detailed flexfields data is fetched corresponding to each company (master record). 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 is similar to Report 3 with a complex header that includes the set of books, date, currency, period, and page number. However, the Accounting Flexfield combinations and descriptions are listed under their company names.

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

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

Now you build parameters for three queries. The 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 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.

SRW.REFERENCE(:P_STRUCT_NUM);

SRW.USER_EXIT('FND FLEXSQL 

CODE="GL#" 

NUM=":P_STRUCT_NUM"

APPL_SHORT_NAME="SQLGL" 

OUTPUT=":P_COMPANY"

MODE="SELECT" 

DISPLAY="GL_BALANCING"');

The second call populates the value of lexical P_WHERE with the restriction you want to apply at run time. You want this parameter to contain the value "(SEGMENT1 < '04')" if GL_BALANCING segment is segment 1 and the value of P_OPERAND1 is "04". You call the user exit as follows:

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 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 to specify the lexical parameters.

Step 6 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 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 FLEXSQL call is the same as the 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_ORDERBY"

MODE="ORDER BY" 

DISPLAY="ALL"');

Step 8 Define your report queries

Then you define your report's first master query (Q_COMPANY) to fetch all the different companies.

SELECT DISTINCT &P_COMPANY C_MASTER   

FROM CODE_COMBINATIONS_TABLE 

WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN       = &P_STRUCT_NUMAND &P_WHERE 
When the report runs, the two calls to FND FLEXSQL fill in the lexical parameters to look something like:
SELECT  DISTINCT (SEGMENT1) C_MASTER
FROM   CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN 
= 101
AND SEGMENT1 < '04'
The second master query (Q_MASTER) fetches one record of flexfield data for each company to build 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,
&P_FLEXDATA C_FLEXDATA_MASTER  
FROM CODE_COMBINATIONS_TABLE 
       WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN 


      = &P_STRUCT_NUM
AND &P_COMPANY = :C_MASTER
AND ROWNUM < 2 
This query has G_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's detail query (Q_FLEX):
SELECT &P_COMPANY C_DETAIL,
&P_FLEXDATA C_FLEXDATA [, NORMALCOLUMNS...]FROM CODE_COMBINATIONS_TABLEWHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN = &P_STRUCT_NUM
AND &P_COMPANY = :C_MASTER
ORDER BY &P_ORDERBY
When the report runs, the two calls to FND FLEXSQL fill in the lexical parameters to look something like:
SELECT (SEGMENT1) C_DETAIL,
(SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||
       SEGMENT4) C_FLEXDATA [, NORMALCOLUMNS...]
FROM   CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN = 101

AND (SEGMENT1) = :C_MASTER



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 FLEXIDVAL 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. 
SRW.REFERENCE(:P_STRUCT_NUM);SRW.REFERENCE(:C_FLEXDATA_MASTER);
SRW.USER_EXIT('FND FLEXIDVAL 
CODE="GL#" 
NUM=":P_STRUCT_NUM"
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, you create columns C_BAL_DESC (displaying Widget Corporation) with the following call:
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA_MASTER);
SRW.USER_EXIT('FND FLEXIDVAL 
CODE="GL#" 
NUM=":P_STRUCT_NUM" 
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(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA_MASTER);
SRW.USER_EXIT('FND FLEXIDVAL 
CODE="GL#" 
NUM=":P_STRUCT_NUM" 
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 next few steps.
Step 10 Create formula columns
You create formula columns C_FLEXFIELD and C_DESC_ALL to display concatenated segment values and description respectively. These columns have same group 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(: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 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(: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 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 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 a boiler plate that can be added using layout painter. 'Currency:' and 'Period:' are also Boiler plate 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 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 description of flexfield value 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