Using Flexfield Views to Write a Report (User, System Administrator, and Flexfields Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Using Flexfield Views to Write a Report

When you want to write a report on Oracle Applications 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

Using Flexfield Views to Write a Report

Example of a Simple SQL*Plus Report for a Key Flexfield

Writing a Report for a Descriptive Flexfield

See Also

**Example of a Simple SQL*Plus Report for a Key Flexfield**