Key Flexfield Routines for Special Validation
Syntax for Key Flexfield Routines
If you want to create a special value set (for a report parameter) that uses key flexfield routines, see the section on Special Validation Value Sets for additional arguments and argument options you use for special value sets (in addition to this section).
Note that Special/Pair value sets are user-exit values sets, and should be used with Forms-based applications only.
Use the argument list appropriate for the type of flexfield you want as a value set for a report parameter (foreign key reference, or range flexfield).
For further information on how an application developer creates a new key flexfield and builds a combinations form, see the Oracle E-Business Suite Developer's Guide.
Foreign Key Reference Flexfield
The POPID/LOADID/VALID calling sequence for a foreign key reference flexfield (for most flexfield report parameters) is:
Syntax
#FND {POPID|LOADID|VALID}
CODE="flexfield code"
APPL_SHORT_NAME="application_short_name"
VALIDATE="{FULL|PARTIAL|NONE|QUERY}"
SEG="block.concatenated values field name"
[BLOCK="block_name"]
[FIELD="field_name"]
[DERIVED=":block.field\nSegment qualifier"]
[READ_ONLY="{Y|N}"]
[DINSERT="{Y|N}"]
[WINDOW="{Y|N}"]
[ID="block.unique ID field"]
[REQUIRED="{Y|N}"]
[DISPLAY="{ALL | flexfield qualifier | segment number}"]
[UPDATE="{ALL | flexfield qualifier | segment number}"]
[INSERT="{ALL | flexfield qualifier | segment number}"]
[DATA_FIELD="concatenated hidden IDs field"]
[DESC="block.concatenated description field name"]
[TITLE="window title"]
[VDATE="date"]
[NAVIGATE="{Y|N}"]
[AUTOPICK="{Y|N}"]
[NUM=":structure defining field"]
[COPY=":block.field\n{ALL | flexfield qualifier}"]
[VRULE="flexfield qualifier\n
segment qualifier\n
{I[nclude]|E[xclude]}\n APPL=shortname;
NAME=Message Dictionary message name\n
validation value1\n
validation value2..."]
[VALATT=":block.field\n
flexfield qualifier\n
segment qualifier"]
[USEDBFLDS="{Y|N}"]
[COLUMN="{column1(n) | column1 alias(n) [, column2(n), ...]}]
[WHERE="where clause"]
[SET="set number"]
[ALLOWNULLS="{Y|N}"]
[QUERY_SECURITY="{Y|N}"]
[QBE_IN="{Y|N|B}"]
[LONGLIST="{Y|N}"]
[NO_COMBMSG="MESG_NAME"]
| Variable | Description |
|---|---|
| CODE | The flexfield code you specify when you set up this flexfield using the Register Key Flexfield form. This code must match the code you registered. |
| APPL_SHORT_ NAME | The application short name with which your flexfield is registered. |
| VALIDATE | Use a validation type of FULL to validate all segment values and generate a new code combination and dynamically insert it into the combinations table when necessary. If you specify FULL, Oracle Application Object Library checks the values your user enters against the existing code combinations in the code combinations. If the combination exists, Oracle Application Object Library retrieves the code combination ID. If the combination does not exist, Oracle Application Object Library creates the code combination ID and inserts the combination into the combinations table. If you (or an installer) define the flexfield structure with Dynamic Inserts Allowed set to No, then Oracle Application Object Library issues an error message when a user enters a combination that does not already exist. In this case, Oracle Application Object Library does not create the new code combination. FULL is the usual argument for a form with a foreign key reference. Use PARTIAL to validate each individual segment value but not create a new valid combination or check the combinations table for an existing combination. You would use PARTIAL when you want to have application logic that requires flexfield segment values but does not require an actual code combination. For example, Oracle Application Object Library's Define Shorthand Aliases form requires that a user enters valid values for each segment, but does not require (or check) that the actual code combination already exists in the combinations table. The Define Shorthand Aliases form does not create the combination, either. Use NONE if you wish no validation. Use QUERY (not QUERY_BASE) for POPID in a FND_PRE_QUERY trigger. The default value is FULL. Use the same value in your LOADID and VALID as you use in your POPID in your KEY_PREFIELD trigger. Do not use FOR_INSERT for a form with a foreign key reference. If you wish to implement shorthand flexfield entry for your form with a foreign key reference, you must use FULL for POPID in your KEY_PREFIELD trigger (as well as LOADID and VALID). |
| SEG | block.concatenated values field name is a displayed, non-database form field that contains your concatenated segment values plus delimiters. |
| DERIVED | Use DERIVED to get the derived value of segment qualifiers for a combination that someone types in. Use block.field to specify the block and field you want Oracle Application Object Library to load the derived value into. Use Segment qualifier to specify the segment qualifier name you want. Note: do not put spaces around \n, and \n must be lowercase. Oracle Application Object Library uses the following rules to get the derived qualifier value from the individual segment qualifier values: if the segment qualifier is unique, the derived value is the segment qualifier value; for non-unique segment qualifiers, if any segment's qualifier value = N, then the derived value is N, otherwise, the derived value is Y. The only exception to this rule is for the internal SUMMARY_FLAG segment qualifier; the rule for this is if any segment value is a parent, then the derived value of SUMMARY_FLAG is Y. Oracle Application Object Library loads derived values into the combinations table qualifier column that you specify when you define your qualifier. You do not need the three DERIVED=":block.SUMMARY_FLAG\n SUMMARY_FLAG", DERIVED=":block.START_DATE_ACTIVE\n START_DATE_ACTIVE", and DERIVED=":block. END_DATE_ACTIVE\nEND_DATE_ACTIVE" parameters for a form with a foreign key reference. |
| READ_ONLY | This parameter prevents any updating of your flexfield, whether from shorthand alias, copy, or any other method. |
| DINSERT | The DINSERT parameter turns dynamic inserts off or on for this form. You must set this parameter to N for flexfields within flexfields such as flexfields in a Special validation value set. |
| WINDOW | Specify N if your flexfield contains only a single display segment and you want your users to type directly into the field, instead of into an invisible pop-up window. |
| ID | Specify the block.field that contains the unique ID for this flexfield. The default value is "block.ID column name" where block is the current block and ID column name is the Unique ID Column Name specified for this flexfield using the Register Key Flexfield form. |
| REQUIRED | Specify whether your user can exit the flexfield window without entering segment values. You should specify the same value for REQUIRED in your POPID, LOADID, and VALID triggers. You do not need the REQUIRED parameter for POPID in an FND_PRE_QUERY trigger. The default value is Y. If you specify Y, then Oracle Application Object Library prevents your user from leaving any required segment (a segment whose value set has Value Required set to Yes) without entering a valid value for that segment. Also, if your user tries to save a row without ever entering the flexfield pop-up window, VALID attempts to use default values to fill in any required segments and issues an error message if not all required segments can be filled. If you specify Y and VALIDATE="FULL", then when your user queries up a row with no associated flexfield (the foreign key flexfield ID column contains NULL), Oracle Application Object Library issues an error message to warn the user that a NULL ID has been returned for a required flexfield. The LOADID routine also returns failure. If you specify N, Oracle Application Object Library allows your user to save a row without ever entering the flexfield pop-up window. If you specify N, Oracle Application Object Library also lets your user navigate (without stopping) through a flexfield window without entering or changing any values. However, if a user enters or changes any segment value in the flexfield, Oracle Application Object Library prevents the user from leaving the flexfield window until all required segments contain valid values. If you specify N and a user does not open or enter values in the window, VALID allows the user to save the row whether the flexfield has required segments. In this case, VALID does not save default values as segment values for the required segments, and it does not issue an error message. If you specify N and VALIDATE="FULL", then when your user queries up a row with no associated flexfield (the foreign key flexfield ID column contains NULL), Oracle Application Object Library validates the individual segment values returned by the query. Specify N if you want to query up non-required flexfields without getting an error message. Note that even if REQUIRED="N", a user who starts entering segment values for this flexfield must either fill out the flexfield in full, or abandon the flexfield. |
| 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 which the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form. For example, if you specify that you want to display only segment number 1, your flexfield displays only the first segment that would normally appear in the pop-up window (for the structure you specify in NUM). If you include the DISPLAY parameter in your POPID, you must include the DISPLAY parameter with the exact same argument in your LOADID and VALID calls. The default value for DISPLAY is ALL, which makes your flexfield display all segments. Alternatively, you can specify a flexfield qualifier name or a segment number. You can use DISPLAY as a toggle switch by specifying it more than once. For example, if you want your flexfield to display all but the first segment, you would specify: DISPLAY="ALL" DISPLAY="1" If you do not display all your segments, but you use default values to fill in your non-displayed segments, you must also have hidden SEGMENT1 through SEGMENTn fields in your form. You need these hidden fields because Oracle Application Object Library writes the values for all displayed fields to the concatenated values field, but does not write the values for the non-displayed defaulted fields. Since Oracle Application Object Library normally uses the values in the concatenated values field to update and insert to the database, the default values for the non-displayed fields are not committed. However, if you have the extra hidden fields (similar to a combinations form), Oracle Application Object Library writes flexfield values to those fields as well as to the concatenated segment values field. The non-displayed values are written only to the hidden fields, but are used to update and insert to the database. |
| UPDATE / INSERT | The UPDATE / INSERT parameters determine whether your users can update or insert segments that represent specified unique flexfield qualifiers or segment numbers, where segment numbers are the order in which the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form. You do not need the UPDATE and INSERT parameters for LOADID or VALID. The default value for each is ALL, which allows your user to update/insert all segments. Alternatively, you can specify a flexfield qualifier name or a segment number. You can enter UPDATE="" or INSERT="" to prevent your user from updating or inserting values for any segments. You can use these parameters as toggle switches by specifying them more than once. For example, if you want your user to be able to update all but the first segment, you would specify: UPDATE="ALL" UPDATE="1" If you use INSERT="" to prevent your user from inserting values for any segments, Shorthand Flexfield Entry is disabled for that form. |
| DATA_FIELD | The concatenated hidden IDs field is a non-displayed form field that contains the concatenated segment hidden IDs. |
| DESC | block.concatenated description field name is a displayed, non-database, non-enterable field that contains concatenated descriptions of your segment values. If you do not specify the DESC parameter, Oracle Application Object Library does not display concatenated segment descriptions. |
| TITLE | window title appears at the top of the pop-up window. The default value is the Flexfield Name you specify when you set up this flexfield using the Define Key Segments form. |
| VDATE | date is the validation date against which the Start Date and End Date of individual segment values is checked. You enter a Start Date and End Date for each segment value you define using the Define Key Segment Values form. See: Define Segment Values. For example, if you want to check values against a date that has already passed (say, the closing date of an accounting period), you might specify that date as VDATE using a field reference (VDATE=:block.field) and compare your segment values against that date. The default value is the current date. |
| NAVIGATE | Specify Y if flexfields should automatically determine the navigation out of the flexfield pop-up window (that is, if your user exits the window by pressing [Next Field], then the cursor appears in the field after flexfield. Alternatively, if your user exits the flexfield by pressing [Previous Field], then the cursor appears in the field before the flexfield). This value should be Y for POPID in a KEY_PREFIELD trigger, but is not needed for LOADID or VALID. Omit this argument for a POPID in an FND_PRE_QUERY trigger. The default value is N for backward compatibility. |
| AUTOPICK | Specify N if flexfields should not pop up a list of values window when a user enters an invalid value. You do not need the AUTOPICK parameter for LOADID or VALID. The default value is Y. |
| NUM | The non-displayed database :block.field that holds the identification number of your flexfield structure. You may also specify :$PROFILES$.your_profile_option_name to retrieve a value you set in a user profile option. You can "hardcode" a structure number, such as 101, into this parameter instead of providing a field reference, but such a number prevents you from using multiple structures for your flexfield. You must use this option if you are using multiple structures. You can use the following SQL statement to retrieve the structure identification numbers for your flexfield: SELECT ID_FLEX_NUM, ID_FLEX_STRUCTURE_NAME FROM FND_ID_FLEX_STRUCTURES WHERE APPLICATION_ID = 'application id' AND ID_FLEX_CODE = 'flexfield code'; where flexfield code is the code you specify when you register your flexfield. The default value for NUM is 101. |
| COPY | Copies a non-null value from :block.field into the segment representing the specified flexfield qualifier or segment number before the flexfield window pops up. Alternatively, if you specify ALL, COPY copies a set of non-null, concatenated set of segment values (and their segment separators) that you have in :block.field into all of your segments. For example, if you have a three-segment flexfield, and your :block.field contains 001.ABC.05, COPY puts 001 into the first segment, ABC into the second segment, and 05 into the third segment. The value you COPY into a segment must be a valid value for that segment. The value you COPY overrides any default value you set for your segment(s) using the Define Key Segments form. However, shorthand flexfield entry values override COPY values. COPY does not copy a NULL value over an existing (default) value. However, if the value you copy is not a valid value for that segment, it gives the appearance of overriding a default value with a NULL value: the invalid value overrides the default value, but Oracle Application Object Library then erases the copied value because it is invalid. You should ensure that the field you copy from contains valid values. When the flexfield window closes, Oracle Application Object Library automatically copies the value in the segment representing the specified flexfield qualifier or segment number into :block.field. Alternatively, if you specify ALL, Oracle Application Object Library automatically copies the concatenated values of all your segments into :block.field. You can specify one or more COPY parameters. Later COPY parameters override earlier COPY parameters. For example, assume you have a field that holds concatenated flexfield values, called Concatenated_field, and it holds the string 01-ABC-680. You also have a field, Value_field, that holds a single value that you want to copy into your second segment, and it holds the value XYZ. You specify: COPY="block.Concatenated_field\nALL"
COPY="block.Value_field\n2"
When your user opens the flexfield window, Oracle Application Object Library executes the two COPY parameters in order, and your user sees the values in the window as: 01 XYZ 680 After the flexfield window closes, Oracle Application Object Library copies the values back into the two fields as 01-XYZ-680 and XYZ respectively. Note that XYZ overrides ABC in this case. You do not need the COPY parameter for LOADID or VALID, or in POPID in an FND_PRE_QUERY. The delimiter \n must be lowercase. |
| VRULE | Use VRULE to put extra restrictions on what values a user can enter in a flexfield segment based on the values of segment qualifiers (which are attached to individual segment values). You can specify the name of a flexfield qualifier and a segment qualifier, whether to Include or Exclude the validation values, and the Message Dictionary message name for the message Oracle Application Object Library displays if the user enters an improper value. The delimiter \n must be lowercase. For example, suppose you build a form where you want to prevent your users from entering segment values for which detail posting is not allowed into all segments of Oracle General Ledger's Accounting Flexfield. DETAIL_POSTING_ALLOWED is the segment qualifier, based on the global flexfield qualifier GL_GLOBAL, that you want to use in your rule. You want to exclude all values where the value of DETAIL_POSTING_ALLOWED is N (No). Your message name is "GL Detail Posting Not Allowed", and it corresponds to a message that says "you cannot use values for which detail posting is not allowed." You would specify your rule as: VRULE="GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nE \nNAME=GL Detail Posting Not Allowed\nN" When your user enters an excluded value in one of the segments affected by this qualifier, your user gets the message you specify. In addition, the excluded values do not appear in the Lists of Values on your segments. All other values, not being specifically excluded, are included. You can specify one or more VRULE parameters. Oracle Application Object Library checks multiple VRULE parameters bottom-up relative to the order you list them. You should order your rules carefully so that your user sees the most useful error message first. |
| VALATT | VALATT copies the segment qualifier value of the segment representing the unique flexfield qualifier into :block.field when the flexfield window closes. The delimiter \n must be lowercase. Include the same value for the VALATT parameter in your POPID (KEY_PREFIELD), LOADID, and VALID. You do not need this parameter in POPID in FND_PRE_QUERY. |
| USEDBFLDS | Specify this parameter if your form is based on a table that has foreign key references to two or more flexfields, and if you have non-database SEGMENT1 through N fields on your form (where N is the number of segments in your combinations table). If such fields exist, Oracle Application Object Library by default will load values into them that correspond to the combination of segment values in the current flexfield. If you set this parameter to N, Oracle Application Object Library will not load the segment fields for the current flexfield. If you have more than one flexfield on your form, use this parameter to specify which one should use the segment fields (specify Y for one flexfield's routine calls, and specify N for other flexfields' routine calls). The default value is Y. |
| COLUMN | Use COLUMN to display other columns from the combinations table in addition to the current segment columns, where n is the display width of the column. You can place the values of the other columns into fields on the current form. The value is automatically copied into the field when the user selects an existing flexfield. For example, to display a description column called SEG_DESC and an error message from E_FLAG with the column headings DESCRIPTION and ERROR FLAG, you could set COLUMN="SEG_DESC DESCRIPTION(15), E_FLAG \"ERROR FLAG \"(*)". The (*) sets a dynamic column width, with the size determined by the value selected. If you wanted to place the description into the field block_1.field_1 and the error message into block_1.field_2, you would set COLUMN="SEG_DESC DESCRIPTION(15) INTO BLOCK_1.FIELD_1, E_FLAG \" ERROR FLAG \"(*) into BLOCK1_FIELD_2" You may only use 32 distinct INTO columns in your COLUMN= clause. Your maximum width for additional columns is 240 characters. |
| WHERE | Specify a WHERE clause to customize which code combinations to display in the combination-level List of Values pop-up window. Normally, the List of Values displays a combination-level List of Values of all current valid combinations, instead of a single-segment List of Values, when the validation type of the segment's value set is NONE. This argument also prevents a user from selecting a combination that does not fit the WHERE clause. In the case of a single-segment flexfield where the segment uses a validated value set, this may have the effect that a user will initially see all values in the List of Values (the segment-level List of Values), but then will get an error message if the value chosen is not already an existing combination (as well as being a valid individual segment value) if dynamic inserts are not allowed. You should use this token with flexfields that do not allow dynamic inserts, either using DINSERTS="N" or preventing dynamic inserts at the structure level. Do not specify the word "WHERE" in this where clause argument. |
| SET | Specify the :block.field that holds the set identifier for your flexfield. SET specifies which set of code combinations to use for this flexfield. For each flexfield structure, you can divide code combinations in your combinations table into sets (for example, parts with high prices, medium prices, and low prices). You can only use SET if you implement a structure defining column (that is, you must specify NUM). The default for SET is your structure number (as specified in NUM). If you use SET, your application must maintain a separate table that contains the correspondences between sets and key flexfield structures. For example, your correspondences table could contain values such as these: Structure - 101, Set - 1, Set Description - Low-priced truck parts Structure - 101, Set - 2, Set Description - Medium-priced truck parts Structure - 101, Set - 3, Set Description - High-priced truck parts Structure - 102, Set - 4, Set Description - Low-priced car parts Structure - 102, Set - 5, Set Description - Medium-priced car parts Structure - 103, Set - 6, Set Description - Low-priced motorcycle parts Structure - 103, Set - 7, Set Description - High-priced motorcycle parts If you use SET, Oracle Application Object Library stores the set number in the structure defining column instead of the structure number. Note that you cannot have duplicate set numbers in your correspondences table, though you can have more than one set number for a given structure number. You must derive SET and NUM from different :block.fields (or profile options, or "hardcoded" numbers) since they are distinctly different numbers. If you have a flexfield query-by-example POPID in a FND_PRE_QUERY trigger, you should add an extra step to copy the set number (SET) in addition to the step that copies the structure number (NUM). Specify the same value for SET in POPID, LOADID, and VALID. |
| ALLOWNULLS | Determines whether NULLs should be allowed into any segment. ALLOWNULLS overrides the value set definition (Value Required is Yes) for each segment only if you specify PARTIAL or NONE for the VALIDATE parameter. |
| QUERY_ SECURITY | Determines whether flexfield value security applies to queries as well as inserts and updates. If you specify Y, your users cannot query up existing code combinations that contain restricted values. If you specify N, your users can query and look at code combinations containing restricted values. Users can update the restricted values to non-restricted values, but they cannot enter restricted values or update values to restricted values. The default value is N. This option has no effect unless your users have enabled and defined flexfield value security for your flexfield's value sets (using the Define Value Sets form, the Define Flexfield Security Rule form, and the Assign Flexfield Security Rules form). Put this option in your LOADID call only. You do not need QUERY_SECURITY in POPID or VALID. |
| QBE_IN | Controls the type of subquery Oracle Application Object Library uses to select the desired rows in flexfield query-by-example. Use this option only in a POPID in an FND_PRE_QUERY trigger. Do not use in POPID in your KEY_PREFIELD trigger or in LOADID or VALID. The default value is N. If you specify N, Oracle Application Object Library generates a correlated subquery. This query is effectively processed once for each row returned by the main query (generated by the rest of the form), and it uses the code combination ID as a unique index. Choose N if you expect your main query to return a small number of rows and you expect your flexfield query-by-example to return many rows. If you specify Y, Oracle Application Object Library generates a non-correlated subquery using the "IN" SQL clause. Oracle Application Object Library processes the query only once, but returns all the rows in your combinations table that match your flexfield query-by-example criteria. Choose Y when you expect your main query to return many rows and you expect your flexfield query-by-example to return a small number of rows (less than about 100). Such a condition usually corresponds to a small number of rows in the combinations table and many rows in the application table. For example, assume you have a Part Flexfield, where your company handles only a limited number of parts (say, 75), but you have thousands of orders for your parts (and a correspondingly large Orders table). For this case, choosing Y would greatly improve your application performance on flexfield queries-by-example. You can specify B if your Forms block is based on the combinations table. No subquery is used. If you set QBE_IN to B, you must also set USEDBFLDS to Y. |
| LONGLIST | Specify Y or N to allow using LongList with this flexfield. LongList allows users to specify a partial value when querying a flexfield combination. |
| NO_COMBMSG | If you wish to display your own message when a user enters an invalid combination, specify the message name here. Otherwise flexfields uses the standard Application Object Library Message. |
Range Key Flexfield
The POPIDR/LOADIDR/VALIDR calling sequence for a parameter with a range key flexfield is:
Syntax
#FND {POPIDR|LOADIDR|VALIDR}
CODE="flexfield code"
APPL_SHORT_NAME="application_short_name"
VALIDATE="{PARTIAL|NONE}"
[REQUIRED="{Y|N}"]
[DISPLAY="{ALL | flexfield qualifier |
segment number}"]
[UPDATE="{ALL | flexfield qualifier | segment number}"]
[INSERT="{ALL | flexfield qualifier | segment number}"]
[SEG=":block.concatenated values field name"]
[DESC=":block.concatenated description field name"]
[TITLE="window title"]
[VDATE="date"]
[NAVIGATE="{Y|N}"]
[AUTOPICK="{Y|N}"]
[NUM="structure defining field"]
[VRULE="flexfield qualifier\n
segment qualifier\n
{I[nclude]|E[xclude]} APPL=shortname;
NAME=Message Dictionary message name\n
validation value1\n
validation value2..."]
[ALLOWNULLS="{Y|N}"]
| Variable | Description |
|---|---|
| CODE | The flexfield code you specify when you set up this flexfield using the Register Key Flexfield form. This code must match the code you registered. |
| APPL_SHORT_ NAME | The application short name with which your flexfield is registered. |
| VALIDATE | Use a validation type of PARTIAL to validate each individual segment value a user enters. PARTIAL validation does not create a new valid combination or check the combinations table to determine if a code combination already exists. Use NONE if you wish no validation (this is the usual argument for a range flexfield). Do not use FULL or FOR_INSERT for a range flexfield. Use the same value in your LOADIDR and VALIDR as you use in your POPIDR. |
| REQUIRED | Specify whether your user can exit the flexfield window without entering a value. You should specify the same value for REQUIRED in both your POPIDR and VALIDR triggers. You do not need the REQUIRED parameter for LOADIDR. The default value is Y. Note: Even if REQUIRED="N", a user who starts entering segment values for this flexfield must either: a) fill out the flexfield in full, or b) abandon the flexfield. |
| 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 which the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form. For example, if you specify that you want to display only segment number 1, your flexfield displays only the first segment that would normally appear in the pop-up window (for the structure you specify in NUM). If you include the DISPLAY parameter in your POPIDR, you must include the DISPLAY parameter with the exact same argument in your LOADIDR and VALIDR calls. The default value for DISPLAY is ALL, which makes your flexfield display all segments. Alternatively, you can specify a flexfield qualifier name or a segment number. You can use DISPLAY as a toggle switch by specifying it more than once. For example, if you want your flexfield to display all but the first segment, you would specify: DISPLAY="ALL" DISPLAY="1" |
| UPDATE / INSERT | The UPDATE / INSERT parameters determine whether your users can update or insert segments that represent specified unique flexfield qualifiers or segment numbers, where segment numbers are the order in which the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form. You do not need the UPDATE and INSERT parameters for LOADIDR or VALIDR. The default value for each is ALL, which allows your user to update/insert all segments. Alternatively, you can specify a flexfield qualifier name or a segment number. You can enter UPDATE="" or INSERT="" to prevent your user from updating or inserting values for any segments. You can use these parameters as toggle switches by specifying them more than once. For example, if you want your user to be able to update all but the first segment, you would specify: UPDATE="ALL" UPDATE="1" |
| SEG | :block.concatenated values field name is a displayed, non-database form field that contains your concatenated segment values plus delimiters. If you do not specify the SEG parameter, Oracle Application Object Library does not display concatenated segment values. You do not need to specify _LOW and _HIGH, however, since Oracle Application Object Library adds the suffixes for you. |
| DESC | :block.concatenated description field name is a displayed, non-database, non-enterable field that contains concatenated descriptions of your segment values. If you do not specify the DESC parameter, Oracle Application Object Library does not display concatenated segment descriptions. You do not need to specify _LOW and _HIGH, however, since Oracle Application Object Library adds the suffixes for you. |
| TITLE | window title appears at the top of the pop-up window. The default value is the Flexfield Name you specify when you set up this flexfield using the Define Key Segments form. |
| VDATE | date is the date against which the Start Date and End Date of individual segment values is checked. You enter a Start Date and End Date for each segment value you define using the Define Key Segment Values form. For example, if you want to check values against a date that has already passed (say, the closing date of an accounting period), you might specify that date as VDATE using a field reference (VDATE=:block.field) and compare your segment values against that date. The default value is the current date. |
| NAVIGATE | Specify Y if flexfields should automatically determine the navigation out of the flexfield pop-up window (that is, if your user exits the window by pressing [Next Field], then the cursor appears in the field after the flexfield. Alternatively, if your user exits the flexfield by pressing [Previous Field], then the cursor appears in the field before the flexfield). This value should be Y for POPID, but is not needed for LOADID or VALID. The default value is N for backward compatibility. |
| AUTOPICK | Specify N if flexfields should not pop up a list of values window when a user enters an invalid value. You do not need the AUTOPICK parameter for LOADIDR or VALIDR. The default value is Y. |
| NUM | The non-displayed database :block.field that holds the identification number of your flexfield structure. You may also specify :$PROFILES$.your_profile_option_name to retrieve a value you set in a user profile option. You can "hardcode" a structure number, such as 101, into this parameter instead of providing a field reference, but such a number prevents you from using multiple structures for your flexfield. You must use this option if you are using multiple structures. You can use the following SQL statement to retrieve the structure identification numbers for your flexfield: SELECT ID_FLEX_NUM, ID_FLEX_STRUCTURE_NAME FROM FND_ID_FLEX_STRUCTURES WHERE APPLICATION_ID = 'application id' AND ID_FLEX_CODE = 'flexfield code'; where flexfield code is the code you specify when you register your flexfield. The default value for NUM is 101. |
| VRULE | Use VRULE to put extra restrictions on what values a user can enter in a flexfield segment based on the values of segment qualifiers (which are attached to individual segment values). You can specify the name of a flexfield qualifier and a segment qualifier, whether to Include or Exclude the validation values, and the Message Dictionary message name for the message Oracle Application Object Library displays if the user enters an improper value. The delimiter \n must be lowercase. For example, suppose you build a form where you want to prevent your users from entering segment values for which detail posting is not allowed into all segments of Oracle General Ledger's Accounting Flexfield. DETAIL_POSTING_ALLOWED is the segment qualifier, based on the global flexfield qualifier GL_GLOBAL, that you want to use in your rule. You want to exclude all values where the value of DETAIL_POSTING_ALLOWED is N (No). Your message name is "GL Detail Posting Not Allowed", and it corresponds to a message that says "you cannot use values for which detail posting is not allowed." You would specify your rule as: VRULE="GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nE \nNAME=GL Detail Posting Not Allowed\nN" When your user enters an excluded value in one of the segments affected by this qualifier, your user gets the message you specify. In addition, the excluded values do not appear in the Lists of Values on your segments. All other values, not being specifically excluded, are included. You can specify one or more VRULE parameters. Oracle Application Object Library checks multiple VRULE parameters bottom-up relative to the order you list them. You should order your rules carefully so that your user sees the most useful error message first. |
| ALLOWNULLS | Determines whether NULLs should be allowed into any segment. ALLOWNULLS overrides the value set definition (Value Required is Yes) for each segment only if you specify PARTIAL or NONE for the VALIDATE parameter. |
Related Topics
Foreign Key Reference Flexfield
Special Validation Value Sets
Special validation value sets allow you to call key flexfield user exits to validate a flexfield segment or report parameter using a flexfield-within-a-flexfield mechanism. You can call flexfield routines and use a complete flexfield as the value passed by this value set.
Example of a Special Validation Value Set
Warning: You should never change or delete a predefined value set that Oracle E-Business Suite supplies. Such changes may unpredictably affect the behavior of your application features such as reporting.
You use the Special Validation Routines window of the Value Set form to define special user exit validation for a Special value set. You also use that region to define validation routines for a Pair value set.
When you define a special validation value set, you specify two things: an event and a function. The event is the time when your function occurs, and your function is your call to a key flexfield user exit. For example, the Validate event occurs once a user enters a value, and your function would validate that value.
You can use a special validation value set to let your users enter an entire key flexfield combination within a single segment of a descriptive flexfield or report parameter. For example, you may want to pass concatenated key flexfield segments as a parameter to a report. With this type of value set, a user can enter the descriptive flexfield segment or report parameter and then see the "normal" behavior of a key flexfield, such as the key flexfield pop-up window and segment Lists of Values associated with that key flexfield. You can use Oracle Application Object Library flexfield routines to perform flexfield data entry and validation functions on segment values or report parameters.
Caution: You should take special care to avoid a situation where you have a value set that contains a flexfield which in turn contains a flexfield (as a value set of one of its segments). There are two situations where this could cause a problem. The first situation (recursion) is where a flexfield calls itself as one of its segments, leading to an infinite chain of pop–up windows. Such a loop may also be indirect. The second potential problem may lead to data truncation and data corruption problems: since a flexfield is often passed as its concatenated flexfield values, the length of these concatenated flexfields can quickly exceed the maximum size of the value set and the underlying segment column in the flexfield table. This is less likely to cause a problem for key flexfields than for descriptive flexfields or range flexfields, because key flexfields are usually passed as a single code combination ID number instead of as concatenated segment values and therefore take less space. Though the Define Value Set form and the Define Segments forms do not prevent you from defining flexfield loops or multiple flexfields within flexfields, you can cause serious truncation problems and possible data corruption problems in your application by allowing this to occur. Plan and define your value sets carefully to avoid these value sets within value sets.
Related Topics
Descriptive Flexfield Segments
Using Flexfield Information in Your Report Parameters
Special Validation Events
You specify the event at which your special validation routine should fire. Valid events include:
-
Edit
-
Validate
-
Load
The following events are present in Oracle E-Business Suite for compatibility with future versions, and you should not use them.
-
Insert/Update
-
Query
-
Edit/Edit
-
ListVal
You may have only one of each type of event. Usually, you use special validation to call an existing key flexfield, and you should usually define one of each type of event. However, you should not define a Load event if you do not use either an ID field (a field that contains the code combination ID number) or a data field (a field that contains the hidden ID numbers corresponding to the values of value sets that use hidden ID columns).
Edit
Calls your special validation routine when your user's cursor enters the segment in a data entry mode. You usually use POPID(R) for your Edit event.
Load
Calls your special validation routine immediately after a query to populate your segment. You usually use LOADID(R) for your Load event.
The user exit you define for Load obtains a value and description based on a stored hidden ID, and fires when your user queries data into the flexfield segment. You should define a Load event if and only if you use a hidden ID. If you have a Load event, you must have a non-null ID field (a field that contains the code combination ID number) or data field (a field that contains the hidden ID numbers corresponding to the values of a value set that uses a hidden ID column). If you have a Load event, you must use :!ID (described below) with either an ID field or data field. Your user exit passes the contents of :!ID to your report or flexfield instead of the contents of :!VALUE (described below).
Validate
Calls your special validation routine whenever the user's cursor leaves the segment or closes the pop-up window, or whenever a default value is copied into the segment or report parameter. The Validate event also fires after a query to generate value descriptions for queried values. You usually use VALID(R) for your Validate event.
You must have a Validate event.
Defining Your Special Validation Function
Enter your user exit syntax exactly as you would call it from a form trigger, except that you need not include the # sign (that is, instead of entering #FND, you may enter just FND).
Special validation provides several special arguments you can use to pass values to and from your user exits:
-
:!ID
-
:!VALUE
-
:!MEANING
-
!DIR
:!ID
You can use :!ID to pass different information depending upon the circumstances. For flexfield routines, :!ID can pass either a combination ID number of an entire combination of segment values (key flexfields only), or it can pass a concatenated string of the individual flexfield segment values (either key or descriptive flexfields).
When you use :!ID to pass a concatenated string of individual segment values, :!ID should contain the hidden ID values, if any, of the values in your value sets. If your value set does not use a hidden ID column, :!ID contains the actual value from the value column of your value set.
For a foreign key flexfield where you are using the VALIDATE=FULL argument, you should use the ID=:!ID argument, and you should not use the DATA_FIELD=:!ID argument. If you are coding a foreign key flexfield where you are using the VALIDATE=PARTIAL (or NONE) argument, you should use the DATA_FIELD=:!ID argument and you must not use the ID=:!ID argument. Note that if you use the DATA_FIELD=:!ID argument for a key flexfield, you must ensure that the total length of the concatenated segments and their separators is less than 240 characters.
You cannot use ID=:!ID with the #FND POPIDR, LOADIDR, or VALIDR routines for range flexfields, but you may use the DATA_FIELD=:!ID argument.
If you have a Load event, you must use :!ID with either an ID field or data field. Your user exit passes the contents of :!ID to your report or flexfield instead of the contents of :!VALUE.
:!VALUE
You use :!VALUE to access the user's input. :!VALUE refers to the displayed values that appear in the flexfield window and in the concatenated values field. :!VALUE contains the concatenated values for the flexfield your value set uses. If you do not specify a value for :!ID, then :!VALUE is passed to your report or stored in your segment column.
If you have a Load event, you must use :!ID with either an ID field or data field. Your user exit passes the contents of :!ID to your report or flexfield instead of the contents of :!VALUE.
:!MEANING
You use :!MEANING to pass the concatenated descriptions of your flexfield values. The value description appears as usual next to the flexfield segment value and in the concatenated description field. If you are writing your own function, you should code your user exit to write the value description into :!MEANING.
!DIR
Use !DIR for the NAVIGATE argument of key and descriptive flexfields routines. !DIR allows the flexfields routines to determine the proper navigation direction when you use a flexfield as a segment value set. Do not use a colon when you specify !DIR for POPID or other flexfield routines.
Additional Arguments for Pair Value Sets
If you are defining validation for a Pair type value set but you are not using the flexfield routines #FND POPIDR, LOADIDR, or VALIDR for range flexfields, you may use special forms of these arguments: :!ID_LOW and :!ID_HIGH, :!VALUE_LOW and :!VALUE_HIGH, and :!MEANING_LOW and :!MEANING_HIGH. However, usually you should use the key flexfield routines for a range flexfield (POPIDR, LOADIDR, and VALIDR), and these routines add the _LOW and _HIGH suffixes to :!ID, :!VALUE and :!MEANING for you automatically.
DINSERT and Dynamic Inserts
When you use a key flexfield user exit for special validation, you must include the token DINSERT=N in your Edit, Load, and Validate events. You cannot perform dynamic inserts from a flexfield within a flexfield, even if the flexfield has dynamic inserts allowed.
Using Hidden IDs
Though you must use the ID=:!ID argument when you want to pass a key flexfield combination ID number, you could use either the DATA_FIELD=:!ID argument or the SEG=:!VALUE argument to pass concatenated key segment values. Even if the value sets your flexfield uses do not use hidden ID columns and values, you may want to write explicitly to the :!ID field (and define a Load event) so that it is clear which values you are storing in the database or passing to your report. If your value sets do not use hidden ID columns, :!ID contains the actual values from the value columns of your value sets. You can have a mixture of displayed values and hidden ID values (depending on which value sets your flexfield segments use) concatenated in :!ID. If you are passing information to an Oracle Reports report that uses flexfield routines, you must have a data field and use the DATA_FIELD=:!ID argument.
Hints for Using Special Validation
If your special (or pair) value set does not behave the way you expect, you should check your value set definition to be sure that you typed your function correctly. Common errors include misplaced exclamation marks ( ! ) and colons ( : ). You should check that these punctuation marks are not missing or in the wrong order or present when they should not be. Other common problems include misspelling token names, missing or extra apostrophes ( ' ), and missing or extra quotation marks ( " ).
Example of Special Validation
Here is an example of how to use Special validation (an example for Pair validation follows this example). Suppose you want to let your users pass a single combination of concatenated Accounting Flexfield segments as a parameter to a report. To let your user choose a single combination, you must provide a key flexfield window from within the report parameters window on the Run Reports form. To do this, you simply define a value set with Special validation and use your familiar flexfield user exits. Since you want to pass an existing combination (that is, you want to pass the ID number of the combination) and this is a foreign key flexfield, you use VALIDATE=FULL and the ID=:!ID argument. You do not use the DATA_FIELD=:!ID argument. This example uses structure 101 of the Accounting Flexfield (though normally you might get your structure number from a prior segment or a profile option, depending on how you use your value set). You define your Events and Functions in this field as follows:
For data entry validation (Event = Edit), you would enter:
FND POPID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=101
REQUIRED=Y
VALIDATE=FULL
ID=:!ID
SEG=:!VALUE
DESC=:!MEANING
NAVIGATE=!DIR
DINSERT=N
For data query (Event = Load), you would enter:
FND LOADID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=101
REQUIRED=Y
VALIDATE=FULL
ID=:!ID
SEG=:!VALUE
DESC=:!MEANING
DINSERT=N
For data validation (Event = Validate), you would enter:
FND VALID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=101
REQUIRED=Y
VALIDATE=FULL
ID=:!ID
SEG=:!VALUE
DESC=:!MEANING
DINSERT=N
Example of Special Validation for a Single Segment
Here is an example of how to use Special validation when you want to let your users pass a single Accounting Flexfield segment value as a parameter to a report. To let your user choose a single segment, you must provide a key flexfield window from within the report parameters window on the Run Reports form. Since you want to pass an existing segment value and this is a foreign key flexfield, you use VALIDATE=PARTIAL. You do not use the DATA_FIELD=:!ID or ID=:!ID argument in this case because you do not use hidden ID value sets with the Accounting Flexfield. You do not use a Load event because you are not using :!ID. This example uses structure 101 of the Accounting Flexfield (though normally you might get your structure number from a prior segment or a profile option, depending on how you use your value set), and the flexfield qualifier FA_COST_CTR identifies which segment it passes. You define your Events and Functions in this field as follows.
For data entry validation (Event = Edit), you would enter:
FND POPID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=101
REQUIRED=N
VALIDATE=PARTIAL
DISPLAY="FA_COST_CTR"
SEG=:!VALUE
DESC=:!MEANING
NAVIGATE=!DIR
DINSERT=N
For data validation (Event = Validate), you would enter:
FND VALID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=101
REQUIRED=N
VALIDATE=PARTIAL
DISPLAY="FA_COST_CTR"
SEG=:!VALUE
DESC=:!MEANING
DINSERT=N
Example of Pair Validation
Here is an example of how to use Pair validation. Suppose you want to let your users pass a range of concatenated Accounting Flexfield segments as parameters to a report. For example, you want to let your users request a report on all combinations where the second segment value is between 001 and 101, inclusive. To let your user choose such a range, you must provide a key flexfield range window from within the report parameters window on the Run Reports form. To do this, you simply define a value set with Pair validation and use your familiar range flexfield user exits to pass a range of concatenated segment values. For a range flexfield, you use VALIDATE=PARTIAL (or NONE). Since you use a range flexfield, you cannot use the ID=:!ID argument. You do not use DATA_FIELD=:!ID in this example (hidden ID value sets are not allowed with the Accounting Flexfield), so you do not need a Load event. This example uses structure 101 of the Accounting Flexfield. You define your Events and Functions in this field as follows:
For data entry validation (Event = Edit), you would enter:
FND POPIDR
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=101
VALIDATE=PARTIAL
SEG=:!VALUE
DESC=:!MEANING
NAVIGATE=!DIR
For data validation (Event = Validate), you would enter:
FND VALIDR
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=101
VALIDATE=PARTIAL
SEG=:!VALUE
DESC=:!MEANING
Using Variables with Special and Pair Validation
You can use bind variables in your special validation user exit calls:
| Variable | Description |
|---|---|
| :$FLEX$. value_set_name | Retrieves a value (the hidden ID value, if a hidden ID value is defined) in a prior segment. |
| :$PROFILES$. profile_option | Retrieves the current value of a profile option. You must specify the option name of the profile option, such as GL_SET_OF_BKS_ID (which does not contain the Accounting Flexfield structure number). Note that your profile option must be set wherever you use this value set (including the View Requests form if this value set is used as a report parameter and the user tries to view the status of the report after submission), or your user will see error messages. |
| :block.field | Gets the current value in a field. You must ensure that this value set is only used for forms that have the same block.field. |
For example, the following user exit on a Validate event obtains the Structure (NUM) of the key flexfield from a profile option:
FND VALID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$PROFILES$.MY_STRUCTURE_ID
REQUIRED=Y
VALIDATE=FULL
ID=:!ID
SEG=:!VALUE
DESC=:!MEANING
DINSERT=N
Related Topics