FND FLEXSQL

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

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:

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.