Previous  Next          Contents  Index  Navigation  Glossary  Library

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:

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:

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".

See Also

Overview of Reporting on Flexfields Data

Overview of Flexfield Views

Oracle Reports 2.5 Flexfield Support API

General Methodology

FND FLEXIDVAL

Oracle Reports and Flexfields Report-Writing Steps

Flexfield Report Examples


         Previous  Next          Contents  Index  Navigation  Glossary  Library