Reporting on Flexfields Data
Overview of Reporting on Flexfields Data
The Oracle E-Business Suite products provide many predefined reports that you can use to report on your organization's financial, manufacturing, and human resources data. However, nearly every organization occasionally needs to create custom reports specific to that organization, and for most of the Oracle E-Business Suite products, that data includes flexfields data. Oracle E-Business Suite provides two primary methods you can use to report on your flexfields data.
Flexfield Views
When you freeze and compile a flexfield structure, Oracle E-Business Suite automatically generates one or more database views of the appropriate flexfield tables. These views use column names that match your segment names and make ad hoc reporting simpler. See: Overview of Flexfield Views.
Flexfields-Oracle Reports API
Oracle E-Business Suite provides special flexfield user exits you can call from your custom Oracle Reports reports. See: Oracle Reports Flexfield Support API.
Standard Request Submission
While Standard Request Submission doesn't necessarily report on flexfields data, it does use flexfield segments as report parameters. See: Overview of Flexfields and Standard Request Submission.
Overview of Flexfield Views
When you freeze and compile a flexfield structure, Oracle E-Business Suite automatically generates one or more database views of the appropriate flexfield tables. These views make ad hoc reporting simpler by providing view columns that correspond directly to your flexfield segments. You can use these views for your reporting by joining them to other application tables that contain flexfield-related data such as code combination ID numbers (CCIDs).
The segment columns in the views use the segment names (not the segment prompts) you define using the (Key or Descriptive) Flexfield Segments forms. Each column has a data type that matches the segment's value set format type, regardless of whether the actual segment column matches that data type. Segments that do not use a value set or use a value set with a hidden ID use the same view column type as the underlying table column. See: Key Flexfield Segments, Descriptive Flexfield Segments.
Key Flexfields
Key Flexfields can have two views into the code combination table:
-
Key Flexfield Concatenated Segments View
-
Key Flexfield Structure View
Descriptive Flexfields
A descriptive flexfield has one view:
-
Descriptive Flexfield View
Related Topics
Overview of Reporting on Flexfields Data
Key Flexfield Concatenated Segment View
Using Flexfield Views to Write a Report
Descriptive Flexfield View Example
Key Flexfield Concatenated Segment View
The key flexfield concatenated segment view name is obtained by adding "_KFV" to the code combination table name. The code combination table name is truncated if necessary so that the view name does not exceed the maximum permissible length of SQL object names (30).
The view shows the concatenated segment values of all the structures in the key flexfield as a single column in the view. This column is called "CONCATENATED_SEGMENTS". The view also includes a copy of the structure defining column to differentiate among combinations for different structures. There exist no columns for individual segments.
The view also contains a column called "PADDED_ CONCATENATED_SEGMENTS", which is similar to the CONCATENATED_SEGMENTS column except that all numeric segment values are right-justified and all other segments values are left justified (that is, the numeric segment values are left padded with blanks and the other values right padded with blanks to the maximum size specified in the value set definition). You can use this column to order by the concatenated segment values.
For example, if you have a 5-segment code combination where the maximum sizes of the segments are 2, 4, 4, 1 and 4, the values in the two columns would look something like this:
CONCATENATED_SEGMENTS PADDED_CONCATENATED_SEGMENTS 2.20.ABCD.4.5000 2. 20.ABCD.4.5000 32.150.ST.4.300 32. 150.ST .4.3000 2.1230.1000.6.300 2.1230.1000.6. 300 32.20.TGW.4.300 32. 20.TGW .4.3000 2.30.10.6.300 2. 30.10 .6. 300
In this example, the third segment uses character format, so the 10 in the last row is left justified like the alphabetic values for that segment.
Related Topics
Overview of Reporting on Flexfields Data
Key Flexfield Structure View
For a key flexfield, Oracle E-Business Suite generates a separate view for each structure of your key flexfield. You specify the view name for your structure in the Key Flexfield Segments form when you define your key flexfield structure. You must specify a name for each structure for which you want to create a view. If you do not specify a view name, Oracle E-Business Suite does not generate a view for that structure.
The key flexfield structure view contains a column for each segment in your flexfield structure, and it uses the segment names, not the segment prompts, as view column names. In the view column names, characters become uppercase and underscores ( _ ) replace all non-alphanumeric characters. For example, "Segment Value" becomes "SEGMENT_VALUE" and "Manager's Title" becomes "MANAGER_S_TITLE".
If the code combinations table contains columns for segment qualifiers, the segment qualifier columns will use the segment qualifier names as view column names, for example GL_ACCOUNT_TYPE.
In addition to the segment and qualifier columns, the view also contains the code combination ID column, START_DATE_ACTIVE, END_DATE_ACTIVE, SUMMARY_FLAG, ENABLED_FLAG, ROW_ID (not ROWID), and all other columns in the code combination table that are not enabled as flexfield columns. The Structure view does not have the structure defining column as all the information in this view pertains to one structure of the flexfield.
Related Topics
Overview of Reporting on Flexfields Data
Key Flexfield Concatenated Segment View
Using Flexfield Views to Write a Report
Descriptive Flexfield View
For a descriptive flexfield, Oracle E-Business Suite generates a view named TABLE_NAME_DFV, where TABLE_NAME is the name of the table that contains the descriptive flexfield segment columns. The table name is truncated if necessary so that the view name does not exceed the maximum permissible length of SQL object names (30). For example, the descriptive flexfield that appears on the Segment Values form uses the table FND_FLEX_VALUES, so its resulting view is named FND_FLEX_VALUES_DFV.
The descriptive flexfield view into the underlying table contains a column for each segment in your descriptive flexfield structure. Since this view contains columns for all the segments of all structures of the descriptive flexfield, the view also includes a copy of the structure defining column to differentiate among rows for different structures.
The view uses each structure's segment names as view column names. The context (structure) column uses the context prompt as the view column name (this may be something like "Context_Value" or "Client_Type"). In the view column names, underscores ( _ ) replace all non-alphanumeric characters. For example, "Context Value" becomes "CONTEXT_VALUE" and "Manager's Title" becomes "MANAGER_S_TITLE".
If segments in different structures (contexts) have identical names, these segments share the same view column. If two or more segments share a view column, then these segments should use value sets of the same format type.
The Descriptive Flexfield View also shows the concatenated segment values in the flexfield as a single column in the view. That column also contains the context value as a "segment" value. The CONCATENATED_SEGMENTS column contains global segments (if any are enabled), the context value, and any context-sensitive segments, in that order. The view does not contain any other columns from the underlying table except a ROW_ID (not ROWID) column, the context column and the columns that are used by enabled segments. The ROW_ID column in the view corresponds to ROWID in the actual table.
Related Topics
Overview of Reporting on Flexfields Data
Using Flexfield Views to Write a Report
Descriptive Flexfield View Example
Creating a Flexfield View
Oracle E-Business Suite creates your flexfield views in the same Oracle ID as the original table. For example, if you have an Oracle General Ledger or Oracle Public Sector General Ledger Oracle ID called GL and you generate a flexfield view for the Accounting Flexfield, your view appears in the GL Oracle ID.
If you have more than one datagroup for your installation of Oracle E-Business Suite, then your flexfield view is created in each Oracle ID corresponding to an Oracle E-Business Suite product. For example, if you have two datagroups that use different Oracle IDs for your Oracle Payables product, AP1 and AP2, then a view for an Oracle Payables descriptive flexfield would be created in each of the two Oracle IDs. Because the two installations of Oracle Payables share a single descriptive flexfield definition, the structure of the two views would be the same, though the views would contain different data.
Occasionally an Oracle E-Business Suite form may use a "fake" table for its descriptive flexfield. In this case, no view is created. Usually these special descriptive flexfields appear in a form block that contains more than one descriptive flexfield (normally a block may contain only one descriptive flexfield).
If the application to which the flexfield belongs is not an Oracle E-Business Suite installed or shared application, the view generator does not create a view. The view generator does not create views for non-Oracle E-Business Suite (custom) flexfields.
If the total number of uniquely-named segments (after segment names have been corrected for non-alphanumeric characters) for a descriptive flexfield exceeds 253, Oracle E-Business Suite cannot create your descriptive flexfield view and include columns for all of your segments (a view can contain only 256 columns). In this case, the flexfield view generator creates your descriptive flexfield view without columns for the individual segments, but does include the ROW_ID, CONCATENATED_SEGMENTS, and structure defining column (context column).
If you plan to use many segments (over all structures, both global and context-sensitive) for your descriptive flexfield, you should plan to use duplicate segment names. For example, if you define the Asset Category descriptive flexfield in Oracle Assets, you may have many structures (one for each category of asset, such as vehicles) that each have several segments. For this flexfield, you could easily exceed 253 uniquely-named segments.
However, you can intentionally share segment names among context-sensitive structures, and thus stay below 253 uniquely-named segments. For example, you might have a segment in a VEHICLE structure for vehicle type, and you might have a segment in a FURNITURE structure for furniture type. You could name both segments Type, and they would share a column in the view. Since the context (structure) column appears in the view, you can easily differentiate between the two uses of the column. Also, since the view uses the segment name, instead of the segment prompt, you can use different prompts for these segments and avoid confusing users. Be sure that none of the segment names for your context-sensitive segments duplicate the names for any global segments you have, however.
You should always verify that your view generation concurrent request completes successfully. If the concurrent request fails for some reason, such as duplicate column names, the view generator attempts to create a "null view" so that any grants and synonyms from a previously-existing view are preserved. In these cases, you should identify and fix the problem and then regenerate your view. The report file for your concurrent request contains a description of your view.
Updating a Flexfield View
If you want to recreate a flexfield view, you refreeze and recompile your flexfield structure.
Related Topics
Overview of Reporting on Flexfields Data
Using Flexfield Views to Write a Report
Descriptive Flexfield View Example
Segment Naming Conventions
The flexfield view generator will use your segment name as a column name and change all spaces and special characters to underscores (_). You should begin your segment name with a letter and avoid using special characters such as +, -, ., !, @, ', or # as segment names. You should ensure that none of the segment names in your flexfield are the same once the flexfield view generator has changed all spaces and special characters to underscores (_). You should also ensure that none of the segment names in your flexfield result in the same names as other column names in the code combinations table for the flexfield. For example, the name DESCRIPTION often appears as a column name, so you should avoid naming your segment "Description" (it is not case-sensitive). You should not use a non-alphabetic character as the first character of your segment name, since the first character of a database object name (that is, your view column name) must be a letter. For example, a segment name of "# of dependents" becomes "__of_dependents", which is an illegal column name.
If two or more segment names map to identical view column names, the flexfield view generator will not be able to create your view (it will fail with a "Duplicate Column" error), except in the case of segments belonging to different contexts in a descriptive flexfield. The view generator uses underscores ( _ ) to replace all characters in the segment name that are other than alphanumeric characters and underscores. The segment names in a structure should not be identical after correction for non-alphanumeric characters. For example, the names "Segment 1's Name" and "Segment_1_s_Name" would be the same once the space and apostrophe become underscores ( _ ).
You should avoid using segment names that become SQL reserved words such as NUMBER or DEFAULT.
For descriptive flexfields, the context prompt is used as the view column name for the context column, so you should not create a segment name that results in the same name as the context prompt.
Keep these conventions in mind while naming flexfield segments using the (Key or Descriptive) Flexfield Segments windows. See: Key Flexfield Segments, Descriptive Flexfield Segments.
Key Flexfields
The segment names in a structure and any qualifier names in the flexfield should not be identical after correction for non-alphanumeric characters.
Since the key flexfield view includes non-flexfield columns, your segment names should not match any other non-flexfield column in the code combination table. For example, a segment named DESCRIPTION and a non-flexfield column by the same name in the code combination table will conflict in the view. If there is a column named "CONCATENATED_SEGMENTS" or "ROW_ID" in the code combination table, the table column by this name would not be included in the view since these names would conflict (the view generator creates the view columns as usual).
Descriptive Flexfields
The context prompt is used as the view column name for the context column, so the context prompt should not appear as a segment name. The global segment names should be unique. That is, other global segments and context sensitive segments should not have identical view column names.
Related Topics
Overview of Reporting on Flexfields Data
Using Flexfield Views to Write a Report
When you want to write a report on Oracle E-Business Suite data, you typically want to report on information that is not directly related to flexfields, but that includes flexfields data as part of that information.
Example of a Simple SQL*Plus Report for a Key Flexfield
For example, suppose you wanted to write a report of your orders for the month of March. The information you want is about the orders themselves, such as the name of the client who placed the order, the date of the order, the number of objects ordered, and so on. However, part of the order is information about what objects your client ordered, and that information is in the form of a flexfield: your Part Number Key Flexfield.
In this example, your ORDER_LINES table would contain a column for QUANTITY and a column for ORDER_ID. It would also contain a column for the PART_ID (the CCID of your part number), and a column to hold the structure number for the Part Number Key Flexfield (our imaginary key flexfield). It would not contain columns for the individual segments of the key flexfield. However, your report would not be very meaningful to its readers without the segment values for your part number (and your readers are not likely to know the unique ID number associated with each part number flexfield combination). You need a way to display the part number combinations instead of the unique ID numbers in your report about orders. You use your key flexfield view for this purpose.
Here is a very simplified example of a SQL*Plus query you could write as your report (note that there is no formatting in this example and that the ORDER_ID, ORDER_DATE, and CLIENT_ID columns would print out for every order line):
SELECT O.ORDER_ID ORDER, O.CLIENT_ID CLIENT, O.ORDER_DATE,
L.ORDER_LINE_ID LINE, QUANTITY,
PN.CONCATENATED_SEGMENTS PART_NO
FROM ORDERS O, ORDER_LINES L, PART_ COMBINATIONS_KFV PN
WHERE O.ORDER_ID = L.ORDER_ID
AND O.ORDER_DATE BETWEEN '28-FEB-1994' AND '01-APR-1994'
AND L.PART_ID = PN.PART_ID
The report you would get as a result would be like:
ORDER CLIENT ORDER_DATE LINE QUANTITY PART_NO
----- ------ ----------- ---- -------- --------------------
1 ABC 03-MAR-1994 1 15 PAD-YEL-8.5X11
1 ABC 03-MAR-1994 2 9 CUT-SCISSOR-7 INCH
1 ABC 03-MAR-1994 3 23 PEN-BALLPT-BLK
2 XXYYZZ 14-MAR-1994 1 8 PAPER-COPY-WHT-A4-RM
3 QRS2 24-MAR-1994 1 3 CUT-SCISSOR-7 INCH
3 QRS2 24-MAR-1994 2 35 PAD-YEL-8.5X11
3 QRS2 24-MAR-1994 3 15 PEN-BALLPT-BLU
Writing a Report for a Descriptive Flexfield
For a descriptive flexfield, you typically want to report on the information already contained in the descriptive flexfield table, but you want to include concatenated descriptive flexfield segment values in your report instead of individual values, or you want to include information from particular named segments (as opposed to ATTRIBUTEn columns). For these reports, you would use the ROW_ID column in the view to join with the ROWID of the descriptive flexfield base table.
SELECT T.VARIOUS_COLUMNS,
V.CONTEXT_VALUE, V.CONCATENATED_SEGMENTS
FROM BASE_TABLE T, BASE_TABLE_DFV V
WHERE V.ROW_ID = T.ROWID
Related Topics
Overview of Reporting on Flexfields Data
Examples of Flexfield Views
The following pages show examples of views created for the Accounting Flexfield and the Oracle Assets Asset Category Descriptive Flexfield. The columns shown in bold print are columns that particularly pertain to the flexfield itself. You should note the differences between the boldfaced columns in the underlying table and those in its view.
Key Flexfield Views Examples
The following pages show examples of views created for the Accounting Flexfield, which uses the GL_CODE_COMBINATIONS table. The columns shown in bold print are columns that particularly pertain to the flexfield itself. You should note the differences between the boldfaced columns in the underlying table and those in its view. The key flexfield columns in this table include thirty SEGMENTn columns, the CODE_COMBINATION_ID column, and the CHART_OF_ACCOUNTS_ID column (structure column). DETAIL_POSTING_ALLOWED_FLAG, DETAIL_BUDGETING_ALLOWED_FLAG, and ACCOUNT_TYPE are segment qualifier columns for the flexfield. The flexfield also uses ENABLED_FLAG, SUMMARY_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE to determine the status of a combination.
Note that the GL_CODE_COMBINATIONS table contains columns for the key flexfield, but it also contains many other columns. LAST_UPDATE_DATE and LAST_UPDATED_BY columns provide information for the Who feature. The ATTRIBUTEn and CONTEXT columns belong to a descriptive flexfield, and the SEGMENT_ ATTRIBUTEn columns belong to a special flexfield used by the Oracle Public Sector Financials products. These other columns all appear in your flexfield view because they are not columns used by the Accounting Flexfield directly.
Our example structure for the Accounting Flexfield contains segments for COMPANY, COST_CENTER, REGION, PRODUCT, ACCOUNT, and SUB_ACCOUNT, so those columns appear in the structure view.
Original Key Flexfield Code Combinations Table
SQL> DESCRIBE GL_CODE_COMBINATIONS Name Null? Type ------------------------------- -------- ---- CODE_COMBINATION_ID NOT NULL NUMBER(15) LAST_UPDATE_DATE NOT NULL DATE LAST_UPDATED_BY NOT NULL NUMBER(15) CHART_OF_ACCOUNTS_ID NOT NULL NUMBER(15) DETAIL_POSTING_ALLOWED_FLAG NOT NULL VARCHAR2(1) DETAIL_BUDGETING_ALLOWED_FLAG NOT NULL VARCHAR2(1) ACCOUNT_TYPE NOT NULL VARCHAR2(1) ENABLED_FLAG NOT NULL VARCHAR2(1) SUMMARY_FLAG NOT NULL VARCHAR2(1) SEGMENT1 VARCHAR2(25) SEGMENT2 VARCHAR2(25) . . . . . . SEGMENT29 VARCHAR2(25) SEGMENT30 VARCHAR2(25) DESCRIPTION VARCHAR2(240) TEMPLATE_ID NUMBER(15) ALLOCATION_CREATE_FLAG VARCHAR2(1) START_DATE_ACTIVE DATE END_DATE_ACTIVE DATE ATTRIBUTE1 VARCHAR2(150) ATTRIBUTE2 VARCHAR2(150) ATTRIBUTE3 VARCHAR2(150) ATTRIBUTE4 VARCHAR2(150) ATTRIBUTE5 VARCHAR2(150) ATTRIBUTE6 VARCHAR2(150) ATTRIBUTE7 VARCHAR2(150) ATTRIBUTE8 VARCHAR2(150) ATTRIBUTE9 VARCHAR2(150) ATTRIBUTE10 VARCHAR2(150) CONTEXT VARCHAR2(150) SEGMENT_ATTRIBUTE1 VARCHAR2(60) SEGMENT_ATTRIBUTE2 VARCHAR2(60) . . . . . . SEGMENT_ATTRIBUTE41 VARCHAR2(60) SEGMENT_ATTRIBUTE42 VARCHAR2(60)
View for the Entire Key Flexfield
View Name: GL_CODE_COMBINATIONS_KFV
Name Null? Type ------------------------------- -------- ---- ALLOCATION_CREATE_FLAG VARCHAR2(1) ATTRIBUTE1 VARCHAR2(150) ATTRIBUTE10 VARCHAR2(150) ATTRIBUTE2 VARCHAR2(150) ATTRIBUTE3 VARCHAR2(150) ATTRIBUTE4 VARCHAR2(150) ATTRIBUTE5 VARCHAR2(150) ATTRIBUTE6 VARCHAR2(150) ATTRIBUTE7 VARCHAR2(150) ATTRIBUTE8 VARCHAR2(150) ATTRIBUTE9 VARCHAR2(150) CHART_OF_ACCOUNTS_ID NOT NULL NUMBER(22) CODE_COMBINATION_ID NOT NULL NUMBER(22) CONCATENATED_SEGMENTS VARCHAR2(155) PADDED_CONCATENATED_SEGMENTS VARCHAR2(155) CONTEXT VARCHAR2(150) DESCRIPTION VARCHAR2(240) DETAIL_BUDGETING_ALLOWED NOT NULL VARCHAR2(1) DETAIL_POSTING_ALLOWED NOT NULL VARCHAR2(1) ENABLED_FLAG NOT NULL VARCHAR2(1) END_DATE_ACTIVE DATE GL_ACCOUNT_TYPE NOT NULL VARCHAR2(1) LAST_UPDATED_BY NOT NULL NUMBER(22) LAST_UPDATE_DATE NOT NULL DATE ROW_ID ROWID SEGMENT_ATTRIBUTE1 VARCHAR2(60) SEGMENT_ATTRIBUTE2 VARCHAR2(60) . . . . . . SEGMENT_ATTRIBUTE41 VARCHAR2(60) SEGMENT_ATTRIBUTE42 VARCHAR2(60) START_DATE_ACTIVE DATE SUMMARY_FLAG NOT NULL VARCHAR2(1) TEMPLATE_ID NUMBER(22)
View for a Key Flexfield Structure
View Name: GL_AFF_STD_VIEW
Name Null? Type ------------------------------- -------- ---- ACCOUNT VARCHAR2(25) ALLOCATION_CREATE_FLAG VARCHAR2(1) ATTRIBUTE1 VARCHAR2(150) ATTRIBUTE10 VARCHAR2(150) ATTRIBUTE2 VARCHAR2(150) ATTRIBUTE3 VARCHAR2(150) ATTRIBUTE4 VARCHAR2(150) ATTRIBUTE5 VARCHAR2(150) ATTRIBUTE6 VARCHAR2(150) ATTRIBUTE7 VARCHAR2(150) ATTRIBUTE8 VARCHAR2(150) ATTRIBUTE9 VARCHAR2(150) CODE_COMBINATION_ID NOT NULL NUMBER(22) COMPANY VARCHAR2(25) CONTEXT VARCHAR2(150) COST_CENTER VARCHAR2(25) DESCRIPTION VARCHAR2(240) DETAIL_BUDGETING_ALLOWED NOT NULL VARCHAR2(1) DETAIL_POSTING_ALLOWED NOT NULL VARCHAR2(1) ENABLED_FLAG NOT NULL VARCHAR2(1) END_DATE_ACTIVE DATE GL_ACCOUNT_TYPE NOT NULL VARCHAR2(1) LAST_UPDATED_BY NOT NULL NUMBER(22) LAST_UPDATE_DATE NOT NULL DATE PRODUCT VARCHAR2(25) REGION VARCHAR2(25) ROW_ID ROWID SEGMENT_ATTRIBUTE1 VARCHAR2(60) SEGMENT_ATTRIBUTE2 VARCHAR2(60) . . . . . . SEGMENT_ATTRIBUTE41 VARCHAR2(60) SEGMENT_ATTRIBUTE42 VARCHAR2(60) START_DATE_ACTIVE DATE SUB_ACCOUNT VARCHAR2(25) SUMMARY_FLAG NOT NULL VARCHAR2(1) TEMPLATE_ID NUMBER(22)
Related Topics
Overview of Reporting on Flexfields Data
Descriptive Flexfield View Example
Here is an example view and report created for the Oracle Assets Asset Category Descriptive Flexfield, which uses the table FA_ADDITIONS. The columns shown in bold print are columns that particularly pertain to the flexfield itself. You should note the differences between the boldfaced columns in the underlying table and those in its view. The descriptive flexfield columns in this table include the ATTRIBUTEn columns and the CONTEXT column (structure column).
Original Underlying Descriptive Flexfield Table
SQL> describe FA_ADDITIONS Name Null? Type ------------------------------- -------- ---- ASSET_ID NOT NULL NUMBER(15) ASSET_NUMBER NOT NULL VARCHAR2(15) ASSET_KEY_CCID NUMBER(15) CURRENT_UNITS NOT NULL NUMBER(4) ASSET_TYPE NOT NULL VARCHAR2(11) TAG_NUMBER VARCHAR2(15) DESCRIPTION NOT NULL VARCHAR2(80) ASSET_CATEGORY_ID NOT NULL NUMBER(15) PARENT_ASSET_ID NUMBER(15) MANUFACTURER_NAME VARCHAR2(30) SERIAL_NUMBER VARCHAR2(35) MODEL_NUMBER VARCHAR2(40) PROPERTY_TYPE_CODE VARCHAR2(10) PROPERTY_1245_1250_CODE VARCHAR2(4) IN_USE_FLAG NOT NULL VARCHAR2(3) OWNED_LEASED NOT NULL VARCHAR2(6) NEW_USED NOT NULL VARCHAR2(4) UNIT_ADJUSTMENT_FLAG NOT NULL VARCHAR2(3) ADD_COST_JE_FLAG NOT NULL VARCHAR2(3) ATTRIBUTE1 VARCHAR2(150) ATTRIBUTE2 VARCHAR2(150) . . . . . . ATTRIBUTE29 VARCHAR2(150) ATTRIBUTE30 VARCHAR2(150) ATTRIBUTE_CATEGORY_CODE NOT NULL VARCHAR2(210) CONTEXT VARCHAR2(210) LEASE_ID NUMBER(15) LAST_UPDATE_DATE NOT NULL DATE LAST_UPDATED_BY NOT NULL NUMBER(15) CREATED_BY NUMBER(15) CREATION_DATE DATE LAST_UPDATE_LOGIN NUMBER(15)
This descriptive flexfield has three context-sensitive structures: VEHICLE.OWNSTD, VEHICLE.HEAVY, and BUILDING.OFFICE. The BUILDING.OFFICE structure has two segments, square footage and insurer. The VEHICLE.OWNSTD structure has five segments, as shown. The VEHICLE.HEAVY structure has five segments as well, square footage cargo, number of axles, transmission type, insurance company, and insurance policy number. The two VEHICLE structures share the same segment name for the insurance company segment.
The resulting view contains a total of eleven segment columns, rather than twelve, for the three structures. The column CONTEXT_VALUE in the view corresponds to the column CONTEXT in the table (the context field prompt defined in the Descriptive Flexfield Segments window is "Context Value"). See: Descriptive Flexfield Segments.
Descriptive Flexfield View
SQL> describe FA_ADDITIONS_DFV Name Null? Type ------------------------------- -------- ---- ROW_ID ROWID CONTEXT_VALUE VARCHAR2(210) SQUARE_FOOTAGE NUMBER INSURER VARCHAR2(150) LICENSE_NUMBER VARCHAR2(150) INSURANCE_COMPANY VARCHAR2(150) INSURANCE_POLICY_NUMBER VARCHAR2(150) SQ_FOOTAGE_CARGO NUMBER NUMBER_OF_AXLES NUMBER TRANSMISSION_TYPE VARCHAR2(150) LICENSE_RENEWAL_DATE DATE POLICY_RENEWAL_DATE DATE POLICY_NUMBER VARCHAR2(150) CONCATENATED_SEGMENTS VARCHAR2(1116)
Example of Reporting from a Descriptive Flexfield View
Here is a simple example of selecting some data from the table and its corresponding view.
SQL> select ADD.ASSET_NUMBER ASSET, ADD.DESCRIPTION,
CONTEXT_VALUE, CONCATENATED_SEGMENTS
from FA_ADDITIONS ADD, FA_ADDITIONS_DFV
where ADD.rowid = ROW_ID;
Note that in this simple report, the structure name (BUILDING.OFFICE, VEHICLE.HEAVY, and VEHICLE.OWNSTD) appears in two columns: CONTEXT_VALUE (the structure column) and in the CONCATENATED_SEGMENTS column as the first "segment" value (the context value appears first because there are no enabled global segments). Some context values do not have any enabled segments, so the CONCATENATED_SEGMENTS column is empty for those assets. Some assets, such as asset number 363, while they belong to structures with enabled segments, do not have values filled in for the descriptive flexfield. For those assets, the CONCATENATED_ SEGMENTS column contains the structure name and several periods (segment separators) that designate empty segment values.
ASSET DESCRIPTION CONTEXT_VALUE CONCATENATED_SEGMENTS
----- ---------------- ----------------- ---------------------
334 Sales Vehicles VEHICLE.LEASESTD VEHICLE.LEASESTD.....
363 Mgt Vehicles VEHICLE.OWNSTD VEHICLE.OWNSTD.....
325 Mahogany Desk FURNITURE.DESKS
343 Paris Sales Bldg BUILDING.OFFICE BUILDING.OFFICE.39200.
Prudential
346 Paris Stor. Bldg BUILDING.STORAGE BUILDING.STORAGE..
352 Desk Phone COMM.PHONE
315 486PC w/20MB Mem COMPUTER.COMPUTER
340 9600 Baud Modem COMPUTER.NETWORK
365 4Dw File Cabinet FURNITURE.CABINET
369 Mgtt Vehicles VEHICLE.OWNSTD VEHICLE.OWNSTD.2FMA934.
10-MAR-94.ALLSTATE.
C-34878.21-SEP-93
348 Bonn Sales Bldg BUILDING.OFFICE BUILDING.OFFICE..
351 Bonn Stor. Bldg BUILDING.STORAGE BUILDING.STORAGE..
338 Laptop Computer COMPUTER.COMPUTER
339 Color Monitor COMPUTER.COMPUTER
332 Sales Vehicles VEHICLE.LEASESTD VEHICLE.LEASESTD.....
333 Mgt Vehicles VEHICLE.OWNSTD VEHICLE.OWNSTD.2FOB834.
10-MAR-94.ALLSTATE.
C-34865.21-SEP-93
335 Mgt Vehicles VEHICLE.OWNSTD VEHICLE.OWNSTD.....
347 Bonn Sales Bldg BUILDING.OFFICE BUILDING.OFFICE..
310 4Dw File Cabinet FURNITURE.CABINET
311 Std Office Chair FURNITURE.CHAIRS
312 Conf. Room Desk FURNITURE.DESKS
292 Mgt Vehicles VEHICLE.OWNLUXURY VEHICLE.OWNLUXURY.....
298 Mgt Vehicles VEHICLE.OWNSTD VEHICLE.OWNSTD.....
283 Flat Bed Trucks VEHICLE.HEAVY VEHICLE.HEAVY.2FOB837.
ALLSTATE.C-34065.200.
5-Speed Manual
276 Cvrd. Trailers VEHICLE.HEAVY VEHICLE.HEAVY.2FOX537.
ALLSTATE.C-34465.100.
157 Sacr. Open Space LAND.OPEN
69 Conf. Room Phone COMM.PHONE
21 Austin Mfg Bldg BUILDING.MFG BUILDING.MFG.60000.
Prudential
43 NY Sales Bldg BUILDING.OFFICE BUILDING.OFFICE..
46 Rocklin HQ Bldg BUILDING.OFFICE BUILDING.OFFICE.78300.
Fidelity Mutual
47 NY Office Bldg BUILDING.OFFICE BUILDING.OFFICE.90000.
Prudential
58 Rome Stor. Bldg BUILDING.STORAGE BUILDING.STORAGE..
Related Topics
Overview of Reporting on Flexfields Data
Oracle Reports Flexfield Support API
Using Oracle E-Business Suite flexfields routines with Oracle Reports, you can build reports that display flexfields data easily and in a number of ways:
-
Display any individual segment value, prompt, or description.
-
Display segment values, prompts, or descriptions from multiple flexfield structures (or contexts) in the same report.
-
Display segment values, prompts, or descriptions from different flexfields in the same report.
-
Display two or more flexfield segment values, prompts, or descriptions, concatenated with delimiters, in the correct order. This includes description information for dependent, independent, and table validated segments.
-
Restrict output based upon a flexfield range (low and high values).
-
Prevent reporting on flexfield segments and values that users do not have access to (flexfield value security).
-
Specify order by, group by, and where constraints using one or more, or all segment columns.
Related Topics
Overview of Reporting on Flexfields Data
Oracle Reports and Flexfields Report-Writing Steps
Report 1: Simple Tabular Report
Report 2: Simple Tabular Report With Multiple Structures
Report 4: Master-Detail Report
Report 5: Master-detail Report on Multiple Structures
General Methodology
You use a two step method to report on flexfield values. The first step creates the appropriate SQL statement dynamically based upon the user's flexfield. The output of the first step is used as input to the second step. The second step formats this raw data for display.
Step 1 (Construction):
The first step requires you to include one or more lexical parameters (Oracle Reports variables that can be changed at runtime) in your SQL statement. You call the user exit FND FLEXSQL with different arguments to specify that part of the query you would like to build. The user exit retrieves the appropriate column names (SQL fragment) and inserts it into the lexical parameter at runtime before the SQL query is executed. The query then returns site- and runtime-specific flexfield information. For example, suppose you have the following query:
SELECT &LEXICAL1 alias, column FROM table WHERE &LEXICAL2
The preliminary calls to FND FLEXSQL replace values of LEXICAL1 and LEXICAL2 at execution time with the SQL fragments. For example, LEXICAL1 becomes "SEGMENT1||'\n'||SEGMENT2" and LEXICAL2 becomes "SEGMENT1 < 2" (assuming the user's flexfield is made up of two segments and the user requested that the segment value of SEGMENT1 be less than 2). The actual executed SQL query might be:
SELECT SEGMENT1||'\n'||SEGMENT2 alias, column FROM table WHERE SEGMENT1 < 2
The SQL statement for a user with a different flexfield structure might be:
SELECT SEGMENT5||'\n'||SEGMENT3||'\n'||SEGMENT8 alias, column FROM table WHERE SEGMENT3 < 2
With this step you can alter the SELECT, ORDER BY, GROUP BY, or WHERE clause. You use this step to retrieve all the concatenated flexfield segment values to use as input to the user exit FND FLEXIDVAL in step 2 (described below).
You call this user exit once for each lexical parameter you use, and you always call it at least once to get all segments. This raw flexfield information is in an internal format and should never be displayed (especially if the segment uses a "hidden ID" value set).
Step 2 (Display):
The second step requires you to call another user exit, FND FLEXIDVAL, on a "post-record" basis. You create a new formula column to contain the flexfield information and include the user exit call in this column. This user exit determines the exact information required for display and populates the column appropriately. By using the flexfield routines the user exit can access any flexfield information. Use this step for getting descriptions, prompts, or values. This step derives the flexfield information from the already selected concatenated values and populates the formula column on a row by row basis.
You call FND FLEXIDVAL once for each record of flexfield segments.
The flexfield user exits for Oracle Reports are similar to their Oracle Application Object Library (using SQL*Forms) counterparts LOADID(R) or LOADDESC and POPID(R) or POPDESC; one to construct or load the values (FLEXSQL), the other to display them (FLEXIDVAL). The token names and meanings are similar.
Related Topics
Overview of Reporting on Flexfields Data
Oracle Reports and Flexfields Report-Writing Steps
Basic Implementation Steps
Step 1 - Call FND SRWINIT from your Before Report Trigger
You call the user exit FND SRWINIT from your Before Report Trigger. FND SRWINIT fetches concurrent request information and sets up profile options. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).
Step 2 - Call FND SRWEXIT from your After Report Trigger
You call the user exit FND SRWEXIT from your After Report Trigger. FND SRWEXIT frees all the memory allocation done in other Oracle E-Business Suite user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).
Step 3 - Call FND FLEXSQL from the Before Report Trigger
You need to pass the concatenated segment values from the underlying code combinations table to the user exit so that it can display appropriate data and derive any descriptions and values from switched value sets as needed. You get this information by calling the Oracle Application Object Library user exit FND FLEXSQL from the Before Report Trigger. This user exit populates the lexical parameter that you specify with the appropriate column names/SQL fragment at run time. You include this lexical parameter in the SELECT clause of your report query. This enables the report itself to retrieve the concatenated flexfield segment values. You call this user exit once for each lexical to be set. You do not display this column in your report. You use this "hidden field" as input to the FND FLEXIDVAL user exit call. This user exit can also handle multi-structure flexfield reporting by generating a decode on the structure column. If your report query uses table joins, this user exit can prepend your code combination table name alias to the column names it returns.
SELECT &LEXICAL alias, column
becomes, for example,
SELECT SEGMENT1||'\n'||SEGMENT2 alias, column
Note: Oracle Reports needs the column alias to keep the name of column fixed for the lexicals in SELECT clauses. Without the alias, Oracle Reports assigns the name of the column as the initial value of the lexical and a discrepancy occurs when the value of the lexical changes at run time.
Step 4 - Restrict report data based upon flexfield values
You call the user exit FND FLEXSQL with MODE="WHERE" from the Before Report Trigger. This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.
WHERE tax_flag = 'Y' and &LEXICAL < &reportinput
becomes, for example,
WHERE tax_flag = 'Y' and T1.segment3 < 200
The same procedure can be applied for a HAVING clause.
Step 5 - Order by flexfield columns
You call the user exit FND FLEXSQL with MODE="ORDER BY" from the Before Report Trigger. This user exit populates the lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the ORDER BY clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.
ORDER BY column1, &LEXICAL
becomes, for example,
ORDER BY column1, segment1, segment3
Step 6 - Display flexfield segment values, descriptions, and prompts
Create a Formula Column (an Oracle Reports data construct that enables you to call a user exit). Call the user exit FND FLEXIDVAL as the Formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that you do not have to use complicated table joins to the flexfield tables. Then you create a new field (an Oracle Reports construct used to format and display Columns), assign the Formula Column as its source, and add this field to your report using the screen painter. You need to include this field on the same Repeating Frame (an Oracle Reports construct found in the screen painter that defines the frequency of data retrieved) as the rest of your data, where data could be actual report data, boilerplate, column headings, etc. The user exit is called and flexfield information retrieved at the frequency of the Repeating Frame that contains your field. In the report data case, the user exit is called and flexfield information retrieved once for every row retrieved with your query.
All flexfield segment values and descriptions are displayed left justified. Segment values are not truncated, that is, the Display Size defined in Define Key Segments screen is ignored. Segment value descriptions are truncated to the description size (if one is displayed) or the concatenated description size (for concatenated segments) defined in the form.
Related Topics
Overview of Reporting on Flexfields Data
Oracle Reports Flexfield Support API
Oracle Reports and Flexfields Report-Writing Steps
FND FLEXSQL
Call this user exit to create a SQL fragment usable by your report to tailor your SELECT statement that retrieves flexfield values. This fragment allows you to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or sort the flexfield values returned by your SELECT statement. You call this user exit once for each fragment you need for your select statement. You define all flexfield columns in your report as type CHARACTER even though your table may use NUMBER or DATE or some other datatype.
Syntax:
FND FLEXSQL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
OUTPUT=":output lexical parameter name"
MODE="{ SELECT | WHERE | HAVING | ORDER BY}"
[DISPLAY="{ALL | flexfield qualifier | segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[NUM=":structure defining lexical" |
MULTINUM="{Y | N}"]
[TABLEALIAS="code combination table alias"]
[OPERATOR="{ = | < | > | <= | >= | != | "||" |
BETWEEN | QBE}"]
[OPERAND1=":input parameter or value"]
[OPERAND2=":input parameter or value"]
Options:
The following options are available.
CODE
Specify the flexfield code for this report (for example, GL#). You call FLEXSQL multiple times to set up SQL fragments when reporting on multiple flexfields in one report.
APPL_SHORT_NAME
Specify the short name of the application that owns this flexfield (for example, SQLGL).
OUTPUT
Specify the name of the lexical parameter to store the SQL fragment. You use this lexical later in your report when defining the SQL statement that selects your flexfield values. The datatype of this parameter should be character.
MODE
Specify the mode to use to generate the SQL fragment. Valid modes are:
| Variable | Description |
|---|---|
| SELECT | Retrieves all segments values in an internal (non-displayable) format. If you SELECT a flexfield qualifier, and that flexfield segment is a dependent segment, then flexfields automatically selects both the parent segment and the dependent segment. For example, if the qualifier references the Subaccount segment, then both the Account (the parent) and the Subaccount segment columns are retrieved. Note: You reuse the lexicals you use in the SELECT clause in the GROUP BY clause. |
| WHERE | Restrict the query by specifying constraints on flexfield columns. The fragment returned includes the correct decode statement if you specify MULTINUM. You should also specify an OPERATOR and OPERANDS. You can prepend a table alias to the column names using the TABLEALIAS token. |
| HAVING | Same calling procedure and functionality as WHERE. |
| ORDER BY | Order queried information by flexfield columns. The fragment orders your flexfield columns and separates them with a comma. The fragment returned includes the correct decode statement if you specify MULTINUM. |
You use the MODE token with the DISPLAY token. The DISPLAY token specifies which segments are included in your SQL fragment in your lexical parameter. For example, if your MODE is SELECT, and you specify DISPLAY="ALL", then your SELECT statement includes all segments of the flexfield. Similarly, if your MODE is WHERE, and you specify DISPLAY="ALL", then your WHERE clause includes all segments. Frequently you would not want all segments in your WHERE clause, since the condition you specify for the WHERE clause in your actual query would then apply to all your segments (for example, if your condition is " = 3", then SEGMENT1, SEGMENT2, ... , SEGMENTn would each have to be equal to 3).
DISPLAY
You use the DISPLAY token with the MODE token. The DISPLAY parameter allows you to specify which segments you want to use. You can specify segments that represent specified flexfield qualifiers or specified segment numbers, where segment numbers are the order in that the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form. Application developers normally use only flexfield qualifiers in the DISPLAY token, whereas users may customize the report and use a DISPLAY token that references a segment number once the flexfield is set up.
The default is ALL, which displays all segments. Alternatively, you can specify a flexfield qualifier name or a segment number.
If you specify a non-unique flexfield qualifier, then the routine returns the first segment with this qualifier that appears in the user's window, not all segments with this qualifier. Only unique segment qualifiers are supported for the WHERE clause.
You can use these parameters as toggle switches by specifying them more than once. For example, if you want to use all but the account segment, you specify:
DISPLAY="ALL" DISPLAY="GL_ACCOUNT"
Or, if you want to use all but the first two segments, you specify:
DISPLAY="ALL" DISPLAY="1" DISPLAY="2"
Note that the order in that flexfield column values are used depends on the order in which they appear in the user's window, not the order in which you specify them in the report, nor the order in that they appear in the database table.
SHOWDEPSEG
SHOWDEPSEG="N" disables automatic addition of depended upon segments to the order criteria. The default value is "Y". This token is valid only for MODE="ORDER BY" in FLEXSQL.
NUM or MULTINUM
Specify the name of the lexical or source column that contains the flexfield structure information. If your flexfield uses just one structure, specify NUM only and use a lexical parameter to hold the value. If your flexfield uses multiple structures, specify MULTINUM only and use a source column to hold the value. In this case the user exit builds a decode statement to handle the possible changing of structures mid-report. The default is NUM="101".
TABLEALIAS
Specify the table alias you would like prepended to the column names. You use TABLEALIAS if your SELECT joins to other flexfield tables or uses a self-join.
OPERATOR
Specify an operator to use in the WHERE clause. The operators "= | < | > | <= | >= | != | QBE | BETWEEN" perform lexical comparisons, not numeric comparisons. With QBE (Query By Example) and BETWEEN operators, the user can specify partial flexfield values to match for one or more segments.
For example, if OPERAND1 is "01--CA%-" (assuming a four-segment flexfield with a delimiter of '-'), the first segment must match 01 and the third segment is like 'CA%'. The resulting SQL fragment is:
SEGMENT1='01' AND SEGMENT3 LIKE 'CA%'
For the BETWEEN operator, if OPERAND1 is "01--CA-" and OPERAND2 is "05--MA-" then the resulting SQL fragment is:
(SEGMENT1 BETWEEN '01' AND '05') AND (SEGMENT3 BETWEEN 'CA' AND 'MA')
OPERAND1
Specify an operand to use in the WHERE clause.
OPERAND2
Specify a second operand to use with OPERATOR="BETWEEN".
Related Topics
Overview of Reporting on Flexfields Data
Oracle Reports Flexfield Support API
Oracle Reports and Flexfields Report-Writing Steps
FND FLEXIDVAL
Call this user exit to populate fields for display. You pass the key flexfields data retrieved by the query into this exit from the formula column. With this exit you display values, descriptions and prompts by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).
Syntax:
FND FLEXIDVAL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
DATA=":source column name"
[NUM=":structure defining source column/lexical"]
[DISPLAY="{ALL|flexfield qualifier|segment number}"]
[IDISPLAY="{ALL|flexfield qualifier|segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[VALUE=":output column name"]
[DESCRIPTION=":output column name"]
[APROMPT=":output column name"]
[LPROMPT=":output column name"]
[PADDED_VALUE=":output column name"]
[SECURITY=":column name"]
Options:
The following options are available.
CODE
Specify the flexfield code for this report (for example, GL#). You call FLEXIDVAL multiple times, using a different CODE, to display information for multiple flexfields in one report.
APPL_SHORT_NAME
Specify the short name of the application that owns this flexfield (for example, SQLGL).
DATA
Specify the name of the field that contains the concatenated flexfield segment values retrieved by your query.
NUM
Specify the name of the source column or parameter that contains the flexfield structure information.
DISPLAY
The DISPLAY parameter allows you to display segments that represent specified flexfield qualifiers or specified segment numbers, where segment numbers are the order in that the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form.
The default is ALL, which displays all segments. Alternatively, you can specify a flexfield qualifier name or a segment number. You can use these parameters as toggle switches by specifying them more than once. For example, if you to display all but the first segment, you would specify:
DISPLAY="ALL" DISPLAY="1"
IDISPLAY
You use this parameter to tell FLEXIDVAL what segments you used in your SELECT clause in the corresponding FLEXSQL call. FLEXIDVAL needs this information to determine the format of raw data retrieved by FLEXSQL. You set IDISPLAY to the same value as your DISPLAY parameter in your FLEXSQL call. The default value is ALL, so if you used DISPLAY="ALL" in FLEXSQL, you do not need to use IDISPLAY here.
SHOWDEPSEG
SHOWDEPSEG="N" disables automatic display of depended upon segments. The default value is Y.
VALUE
Specify the name of the column in which you want to display flexfield values.
DESCRIPTION
Specify the name of the column in which you want to display flexfield descriptions.
APROMPT
Specify the name of the column in which you want to display flexfield above prompts.
LPROMPT
Specify the name of the column in which you want to display flexfield left prompts.
PADDED_VALUE
Specify the name of the column in which you want to display padded flexfield values. The segment values are padded to the segment size with blanks.
SECURITY
Specify the name of the column into which flag "S" will be placed if the segment values are secured. You then write logic to hide or display values based on this flag. This token is applicable only for segment values and does not apply to description, left prompt or above prompt.
Note: The datatype of the column as specified by VALUE, DESCRIPTION, APROMPT and LPROMPT is CHARACTER.
Related Topics
Overview of Reporting on Flexfields Data
Oracle Reports Flexfield Support API
Oracle Reports and Flexfields Report-Writing Steps
Oracle Reports and Flexfields Report-Writing Steps
These are the basic steps you use every time you write an Oracle Reports report that accesses flexfields data. This section assumes you already have a thorough knowledge of Oracle Reports. Though these examples contain only the Accounting Flexfield, you can use these methods for any key flexfield.
Step 1 - Define your Before Report Trigger (this step is always the same)
You always call FND SRWINIT from the Before Report Trigger:
SRW.USER_EXIT('FND SRWINIT');
This user exit sets up information for use by flexfields, user profiles, the concurrent manager, and other Oracle E-Business Suite features. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).
Step 2 - Define your After Report Trigger (this step is always the same)
You always call FND SRWEXIT from the After Report Trigger:
SRW.USER_EXIT('FND SRWEXIT');
This user exit frees all the memory allocation done in other Oracle E-Business Suite user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).
Step 3 - Define your required parameters
You define the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.
The following table lists lexical parameters:
| Name | Data Type | Width | Initial Value | Notes |
|---|---|---|---|---|
| P_CONC_REQUEST_ID | Number | 15 | 0 | Always create |
| P_FLEXDATA | Character | approximately 600 (single structure) to 6000 (roughly ten structures) | Long string | Cumulative width more than expected width required to hold data |
You must always create the P_CONC_REQUEST_ID lexical parameter. "FND SRWINIT" uses this parameter to retrieve information about the concurrent request that started this report.
The P_FLEXDATA parameter holds the SELECT fragment of the SQL query. The initial value is used to check the validity of a query containing this parameter and to determine the width of the column as specified by the column alias. Its initial value is some string that contains columns with a cumulative width more than the expected width required to hold the data. Make sure the width of this column is sufficient. If there are total 30 segments in the table then the safest initial value will be:
(SEGMENT1||'\n'||SEGMENT2||'\n'||SEGMENT3 ... SEGMENT30)
You determine the width by determining the length of that string. That length is roughly the number of characters in the table alias plus the length of the column name, times the number of segments your code combinations table contains, times the number of structures you expect, plus more for delimiter characters as shown in the string above.
Step 4 - Define your other parameters
You define the rest of the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.
You can use the following table to guide you in listing your other parameters and their requirements:
| Name | Data Type | Width | Initial Value | Notes |
|---|---|---|---|---|
| Other parameters | Parameters specific to your report |
Step 5 - Call FND FLEXSQL from your Before Report Trigger to populate P_FLEXDATA
Next, given that you want to display flexfield information like concatenated values and descriptions, and arrange them in order, you make one call to FND FLEXSQL from the Before Report Trigger specifying the lexical parameters. This call changes the value of the lexical parameter P_FLEXDATA at runtime to the SQL fragment that selects all flexfields value data. For example, the parameter changes to (SEGMENT1||'\n'||SEGMENT2||'\n'||SEGMENT3||'\n'||SEGMENT4).
When you incorporate this lexical parameter into the SELECT clause of a query, it enables the query to return the concatenated segment values that are needed as input to other AOL user exits. These exits then retrieve the actual flexfield information for display purposes.
Here is an example FND FLEXSQL call. Notice that the arguments are very similar to other flexfield routine calls; CODE= and NUM= designate the key flexfield and its structure, respectively. For a report on a different key flexfield (such as the System Items flexfield), you would use a different CODE and NUM.
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"');
You should always reference any source column/parameter that is used as a source for data retrieval in the user exit. This guarantees that this column/parameter will contain the latest value and is achieved by "SRW.REFERENCE" call as shown above.
Step 6 - Call FND FLEXSQL from your Before Report Trigger to populate other parameters
You call FND FLEXSQL once for every lexical parameter such as P_WHERE or P_ORDERBY.
Step 7- Define your report query or queries
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
The query fetches the data required to be used as input for the FLEXIDVAL user exit later.
Note: Always provide a column alias (C_FLEXDATA in this example) in the SELECT clause that is the name of column. This name of the column is required in FND FLEXIDVAL.
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 8 - 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).
Important: Use word-wrapping for flexfield columns if necessary to avoid possible truncation of your values. Do this by setting Sizing to Expand.
Step 9 - Populate segment values formula column
To retrieve the concatenated flexfield segment values and description, you incorporate the flexfields user exits in these columns. In the column definition of C_FLEXFIELD, you incorporate the FND FLEXIDVAL user exit call in the formula field. You pass the concatenated segments along with other information to the user exit, and the user exit populates the concatenated values in this column as specified by the VALUE token. A typical call to populate segment values in this column looks as follows:
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 10 - Populate segment descriptions
To populate the segment description use DESCRIPTION="C_DESC_ALL" instead of VALUE="C_FLEXFIELD" as in the previous call. 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 11 - 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 following table 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.
Step 12 - Finish your report
Adjust your report layout as needed.
Related Topics
Overview of Reporting on Flexfields Data
Oracle Reports Flexfield Support API
Flexfield Report Examples
This section demonstrates how to include flexfield data in your report and how to build different types of reports on flexfields using Oracle Application Object Library (FND) user exits. The following sample reports demonstrate the methodology involved in constructing five types of reports.
The first two examples display elementary steps involved in building reports with flexfield support. The next two examples report on a single flexfield structure and show additional features of flexfield support. The fifth report demonstrates how to use these features with multiple flexfield structures.
Important: The previous section, "Oracle Reports and Flexfields Report-Writing Steps", provides additional explanatory detail for each step.
Related Topics
Overview of Reporting on Flexfields Data
Oracle Reports Flexfield Support API
Oracle Reports and Flexfields Report-Writing Steps
Report 1: Simple Tabular Report
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
These are the steps for a simple tabular report.
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 |
Report 2: Simple Tabular Report With Multiple Structures
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
These are the steps for a simple tabular report with multiple structures.
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, 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).
Important: 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:
| 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 |
| C_NUM | FND SRWINIT | |
| FND SRWEXIT |
Report 3: Tabular Report
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
These are the steps for a tabular report.
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 6 - 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);
C_BAL_DESC:
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.
Important: 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 |
Report 4: Master-Detail Report
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
These are the steps for a master/detail report.
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 | Initial value is some string that contains columns with 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 | 298 | Valid ORDER BY clause | Used to construct ORDER BY clause |
| P_OPERAND1 | Character | 15 | Used to construct the P_WHERE parameter | |
| P_COMPANY | Character | 300 | Long string | Use to construct SELECT clause |
| 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 |
Step 4 - Build query 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_NUM
AND &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_TABLE WHERE 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).
Important: 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 |
Report 5: Master-detail Report on Multiple Structures
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
These are the steps for a master/detail report on multiple flexfield structures.
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"');
Important: 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).
Important: 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 | ||
| C_NUM_M | ||
| C_NUM_D |