Flexfields packages and procedures are included in the FNDSQF library. Call item handlers from your WHEN-NEW-FORM-INSTANCE trigger to define key, range or descriptive flexfields.
To define a key flexfield, use the procedure FND_KEY_FLEX.DEFINE
To define a range or type flexfield, use the procedure FND_RANGE_FLEX.DEFINE
To define a descriptive flexfield, use the procedure FND_DESCR_FLEX.DEFINE
When you call these procedures, you specify three types of arguments:
location(s) of the flexfield (block and fields, including the concatenated values field, the ID field if any, and any description or related fields)
specific registered flexfield you want (application, flexfield, and structure if necessary)
any additional arguments
If you have more than one flexfield, you call a complete flexfield definition procedure for each of your flexfields from handlers in the same WHEN-NEW-FORM-INSTANCE trigger.
Use FND_KEY_FLEX.UPDATE_DEFINITION to update the definition for a key flexfield on a foreign key or combinations form. Other than the ENABLED argument, which you can only use for update procedures, the arguments are the same as you use for the flexfield definition procedures..
FND_KEY_FLEX.UPDATE_DEFINITION(
/* Arguments that specify flexfield location and
thus identify the flexfield */
BLOCK=>'block_name', FIELD=>'concatenated_segments_field_name',
/* Argument to enable or disable flexfield */ [ENABLED=>'{Y|N}',] /* Other optional parameters */
[VALIDATE=>'{FOR_INSERT|FULL|PARTIAL|NONE|
PARTIAL_IF_POSSIBLE}',]
[VDATE=>'date',]
[DISPLAYABLE=>'{ALL | flexfield_qualifier |
segment_number}[\\0{ALL |
flexfield_qualifier | segment_number}]',]
[INSERTABLE=>'{ALL | flexfield_qualifier |
segment_number}[\\0{ALL |
flexfield_qualifier | segment_number}]',]
[UPDATEABLE=>'{ALL | flexfield_qualifier |
segment_number}[\\0{ALL |
flexfield_qualifier | segment_number}]',]
[VRULE=>'flexfield qualifier\\n
segment qualifier\\n
{I[nclude]|E[xclude]}\\n
APPL=application_short_name;
NAME=Message Dictionary message name\\n
validation value1\\n
validation value2...
[\\0flexfield qualifier\\n
segment qualifier\\n
{I[nclude]|E[xclude]}\\n
APPL=application_short_name;
NAME=Message Dictionary message name\\n
validation value1\\n
validation value2...]',]
[COPY=>'block.field\\n{ALL | flexfield
qualifier | segment_number}
[\\0block.field\\n{ALL | flexfield
qualifier | segment_number}]',]
[DERIVED=>'block.field\\nSegment qualifier',]
[DINSERT=>'{Y|N}',]
[VALATT=>'block.field\\n
flexfield qualifier\\n
segment qualifier',]
[TITLE =>'Title',]
[REQUIRED=>'{Y|N}',]
[AUTOPICK=>'{Y|N}',]
[USEDBFLDS=>'{Y|N}',]
[ALLOWNULLS=>'{Y|N}',]
[DATA_SET=>'set number',]
[COLUMN=>'{column1(n) | column1 alias(n) [, column2(n), ...]}',]
[WHERE_CLAUSE=>'where clause',]
[COMBQP_WHERE=>'{Y|N}',]
[WHERE_CLAUSE_MSG=>'APPL=application_short_
name;NAME=message_name',]
[QUERY_SECURITY=>'{Y|N}',]
[QBE_IN=>'{Y|N}',]
[READ_ONLY=>'{Y|N}',]
[LONGLIST=>'{Y|N}',]
[NO_COMBMSG=>'{Y|N}',]
[LOCK_FLAG=>'{Y|N}',]
[AUTOCOMBPICK=>'{Y|N}',]
[DERIVE_ALWAYS=>'{Y|N}',]
[HELP=>'APPL=application_short_name;
TARGET=target_name']
);
Use FND_RANGE_FLEX.UPDATE_DEFINITION for a range flexfield. You use the same procedure for a "type" flexfield (which may also include range flexfield segments) that contains extra fields corresponding to each segment of the related key flexfield.
Other than the ENABLED argument, which you can only use for update procedures, the arguments are the same as you use for the flexfield definition procedures. See: Flexfield Definition Arguments.
Attention: You should not append "_LOW" or "_HIGH" to the FIELD, DESCRIPTION, DATA_FIELD or other values, since this procedure appends them automatically. When you use more than one type column, ensure that all TYPE_ arguments specify type columns in the same order to avoid having argument values applied to the wrong type column.
FND_RANGE_FLEX.UPDATE_DEFINITION(
/* Arguments that specify flexfield location */
BLOCK=>'block_name', FIELD=>'concatenated_segments_field_name', /* Argument to enable or disable flexfield */ [ENABLED=>'{Y|N}',] /* Other optional parameters */
[VALIDATE=>'{PARTIAL|NONE}',]
[VDATE=>'date',]
[DISPLAYABLE=>'{ALL | flexfield_qualifier |
segment_number}[\\0{ALL |
flexfield_qualifier | segment_number}]',]
[INSERTABLE=>'{ALL | flexfield_qualifier |
segment_number}[\\0{ALL |
flexfield_qualifier | segment_number}]',]
[UPDATEABLE=>'{ALL | flexfield_qualifier |
segment_number}[\\0{ALL |
flexfield_qualifier | segment_number}]',]
[VRULE=>'flexfield qualifier\\n
segment qualifier\\n
{I[nclude]|E[xclude]}\\n
APPL=application_short_name;
NAME=Message Dictionary message name\\n
validation value1\\n
validation value2...
[\\0flexfield qualifier\\n
segment qualifier\\n
{I[nclude]|E[xclude]}\\n
APPL=application_short_name;
NAME=Message Dictionary message name\\n
validation value1\\n
validation value2...]',]
[TITLE =>'Title',]
[REQUIRED=>'{Y|N}',]
[AUTOPICK=>'{Y|N}',]
[USEDBFLDS=>'{Y|N}',]
[ALLOWNULLS=>'{Y|N}',]
[DATA_SET=>'set number',]
[READ_ONLY=>'{Y|N}',]
/* Parameters specific to type flexfields */
[TYPE_FIELD=>'block.concatenated_type_values_
field\\ntype field suffix',]
[TYPE_VALIDATION=> 'Value set name\\n
Required\\nDefault value',]
[TYPE_SIZES=>'type_value_display_
size\\nDescription_display_size',]
[TYPE_HEADING=>'type column heading',]
[TYPE_DATA_FIELD=>'block.type_data_field',]
[TYPE_DESCRIPTION=>'block.type_
description_field',]
[SCOLUMN=>'single column title']
[HELP=>'APPL=application_short_name;
TARGET=target_name']
);
Attention: TYPE_FIELD, TYPE_DATA_FIELD and TYPE_DESCRIPTION require the block construction, unlike other flexfield arguments that specify field names without block names.
Use FND_DESCR_FLEX.UPDATE_DEFINITION for a descriptive flexfield. Other than the ENABLED argument, which you can only use for update procedures, the arguments are the same as you use for the flexfield definition procedures. See: Flexfield Definition Arguments.
FND_DESCR_FLEX.UPDATE_DEFINITION(
/* Arguments that specify the flexfield location */
BLOCK=>'block_name', FIELD=>'field_name',
/* Argument to enable or disable flexfield */ [ENABLED=>'{Y|N}',] /* Other optional parameters */
[VDATE=>'date',]
[TITLE =>'Title',]
[AUTOPICK=>'{Y|N}',]
[USEDBFLDS=>'{Y|N}',]
[READ_ONLY=>'{Y|N}',]
[LOCK_FLAG=>'{Y|N}',]
[HELP=>'APPL=application_short_name;
TARGET=target_name',]
[CONTEXT_LIKE=>'WHERE_clause_fragment'}
);
Suppose you do not want to call VALID for a particular key flexfield in PRE-UPDATE, but want to call it for all other flexfields in the block. Here is an example of disabling and enabling a simple key flexfield definition. This definition provides the default structure (101) of the Accounting Flexfield. You would code your PRE-UPDATE trigger for the block as:
FND_KEY_FLEX.UPDATE_DEFINITION(
BLOCK=>'ORDERS',
FIELD=>'KFF_CONCATENATED_VALUES',
ENABLED=>'N');
FND_FLEX.EVENT('PRE-UPDATE');
FND_KEY_FLEX.UPDATE_DEFINITION(
BLOCK=>'ORDERS',
FIELD=>'KFF_CONCATENATED_VALUES',
ENABLED=>'Y');
It is often useful to code a range flexfield in your Find window so that users can specify a value range for the flexfield segments instead of a single value. Create two concatenated segments fields (for low and high values) in your Find window as 2000 character displayed text items. The field names should be of the form XXXX_LOW and XXXX_HIGH. You do not need the individual SEGMENTn fields in your Find window. Define the range flexfield you want on this field using the FND_RANGE_FLEX.DEFINE procedure. This can be done at the same point where you define the flexfield in your base block. Do not pass values for the ID, DESCRIPTION and DATA_FIELD arguments. The following arguments to the define call should be set to the values specified below:
VALIDATE => 'NONE', REQUIRED => 'N', USEDBFLDS => 'N', ALLOWNULLS => 'Y' INSERTABLE => 'ALL', -- Default value UPDATEABLE => 'ALL', -- Default value
Attention: You should set DISPLAYABLE to the same value you used in the definition of the flexfield in your base block.
The value for the VALIDATE argument can be 'PARTIAL' if you want users to enter valid segment values as the upper and lower limit of the ranges they want to query on.
The above definition will allow users to choose values for some segments and leave other segments blank. They can also leave either the high or the low segment value blank to set either the lower limit or the upper limit on the segment values. They can enter the same value for both the low and high fields to query on a specific segment value.
Follow the steps for coding Find windows. In the PRE-QUERY trigger of you base block call the procedure FND_FLEX_FIND.QUERY_ KFLEX_RANGE. The arguments to this function are the application short name, the flexfield code, the structure number, the concatenated low segment values, the concatenated high segment values and the name of the concatenated segments field in your base block. The procedure specification is given below.
PROCEDURE query_kflex_range(appl_short_name VARCHAR2,
code VARCHAR2,
num NUMBER,
low_segments VARCHAR2,
high_segments VARCHAR2,
segments_field VARCHAR2);
Attention: The call to FND_FLEX.EVENT('PRE-QUERY') must be made after the FND_FLEX_FIND.QUERY _KFLEX_RANGE procedure is called.
If you choose to use a range flexfield instead of a key flexfield in the preceding example the flexfield definition and the PRE-QUERY trigger will be:
FND_RANGE_FLEX.DEFINE( BLOCK => 'MY_BLOCK_QF', FIELD => 'SEGMENTS', APPL_SHORT_NAME => 'SQLGL', CODE => 'GL#', NUM => 101, VALIDATE => 'NONE', REQUIRED => 'N', USEDBFLDS => 'N', ALLOWNULLS => 'Y');
The PRE-QUERY trigger on MY_BLOCK will be:
...
IF (:parameter.G_query_find = 'TRUE') THEN
...
FND_FLEX_FIND.QUERY_KFLEX_RANGE('SQLGL', 'GL#', 101,
:MY_BLOCK_QF.SEGMENTS_LOW,
:MY_BLOCK_QF.SEGMENTS_HIGH,
'MY_BLOCK.SEGMENTS');
...
:parameter.G_query_find = 'FALSE';
END IF;
...
FND_FLEX.EVENT('PRE-QUERY');