Previous | Next | Contents | Index | Navigation | Glossary | Library |
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
SELECT T.VARIOUS_COLUMNS, V.CONTEXT_VALUE, V.CONCATENATED_SEGMENTS FROM BASE_TABLE T, BASE_TABLE_DFV V WHERE V.ROW_ID = T.ROWID
Key Flexfield Concatenated Segment View
Descriptive Flexfield View Example
Oracle Reports 2.5 Flexfield Support API
Previous | Next | Contents | Index | Navigation | Glossary | Library |