|
|
|
|
|
|
|
|
|
| Previous | Next | Contents | Index | Navigation | Glossary | Library |
Name
The profile option name must be unique so that different profile options do not conflict. This is the name you use when you access your profile option using the Oracle Application Object Library profile option routines.
User Profile Name
This is the name your users see as their profile option, so it should be short but descriptive.
Enter the dates on which the profile option becomes active/inactive. The start date defaults to the current date, and if the end date is not entered, the option will be effective indefinitely. You cannot delete a user profile option, but you can disable it. Enter the current date if you want to disable the user profile option. If you wish to reactivate a disabled profile option, change the End Date to a date after the current date.
To validate your user profile option, select the profile option value into the fields :PROFILE_OPTION_VALUE and :VISIBLE_OPTION_VALUE. The Profile Values form uses these fields to ensure that your user enters valid values for your profile option.
Syntax
SQL="SQL select statement"
COLUMN="column1(length), column2(length),..."
[TITLE="{title text|*application shortname:message name}"]
[HEADING="{heading1(length), heading2(length),...
|*application shortname:message name|N}"]
| SQL | A SELECT statement that selects the rows to display in your LOV. In the SQL statement you can specify column aliases, use an INTO clause to put values into form fields, display database values without selecting them into form fields (by selecting values INTO NULL), and mix values to put into form fields with display only values in the same INTO clause. |
| If you specify more than one column in your COLUMN option, the LOV displays the columns in the order you specify in your COLUMN statement. |
Suggestion: Column aliases cannot be longer than 30 characters. Larger identifiers will cause errors.
| The HEADING option overrides the COLUMN lengths and aliases. | |
| This SQL statement differs from a normal SQL statement in some ways. First, if you want to include spaces in your column aliases, you must put a backslash and double quotes before and after the column alias, so that the LOV routine recognizes the double quotes as real double quotes, rather than the end of your parameter. For example, your SQL option might look like the following example: |
SQL="SELECT SALES_REPRESENTATIVE_ID, SALES_REPRESENTATIVE_NAME INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE FROM OE_SALES_REPRESENTATIVES ORDER BY SALES_REPRESENTATIVE_NAME"
| We recommend that you provide aliases for your column headings in the HEADING options below. | |
| You can use GROUP BY or HAVING clauses in your SQL statement, but only in your main query; you cannot use them in sub-queries. You can use DISTINCT and ORDER BY clauses as you would normally. | |
| Set functions such as MIN(), MAX(), SUM(), and COUNT() can be used in the SELECT or HAVING clause, but you cannot use them on the columns that you select into the PROFILE_OPTION_VALUE or VISIBLE_OPTION_VALUE fields. | |
| Though you can use a fairly complex WHERE clause and/or an ORDER BY clause in your SQL definition, you cannot use UNION, INTERSECT, or MINUS in your main query. If you need a UNION, INTERSECT, or MINUS to select the proper values, you should create a view on your tables, then select from the view, or include these operators as part of a sub-query. | |
| In addition, you cannot use a CONNECT BY or any other operation that would come after the WHERE clause of a SELECT statement. | |
| Finally, if you use OR clauses, you should enclose them in parentheses. | |
| We recommend that you put parentheses around complex columns in your SQL SELECT statements. For example, your SQL option could look like this: |
SQL="SELECT (DEPTNO ||':' ||DEPT_NAME) Department, LOCATION INTO :DEPT.DEPTNAME, :DEPT.LOCATION FROM DEPARTMENT"
| COLUMN | Lists the names of columns (or column aliases) you want to display in your LOV window, the order in which to display them, and their display widths. If you specify more than one column in your COLUMN option, your LOV displays the columns in the order you list them. This order can differ from the column order in your SQL statement. You must specify column widths in the COLUMN= "..." parameter, although any column widths you specify in the HEADING="..." option below override these values. |
| You can specify static or dynamic column widths in your COLUMN option. Specify a static column width by following the column name with the desired width. Specify a dynamic width column by placing an asterisk instead of a number in the parentheses following the column name. When you specify dynamic width for a column, the LOV adjusts the size of the displayed column to accommodate the longest value in the list. Note that a dynamic column width may vary based on the subset of data queried. The following example shows a possible COLUMN option corresponding to the department and location example, and illustrates the use of static and dynamic column widths. |
COLUMN="Department(20), LOCATION(*)"
| If you do not use the HEADING option to supply column heading or suppress headings, then the LOV uses the names and widths from your COLUMN option to display the column headings. If you specify a column alias in your SQL statement and you want that column to appear in your QuickPick window, you must use that column alias in COLUMN. The column headings appear in the QuickPick window with the same upper- and lowercase letters as you define here. If your column alias has two words, you must put a backslash and double quotes on both sides of the alias. Column aliases cannot be longer than 30 characters. Using the first example from the SQL option, your COLUMN option would look like this: |
COLUMN="\"Sales Representative\"(30)"
| If your display width is smaller than your column name or column alias, the LOV uses the length of the column name or alias, even if you suppress headings in your LOV window (see the HEADING option). For your values to display properly, you must specify a number for the column width. | |
| TITLE | Text you want to display centered and highlighted on the top line of your QuickPick window. The default is no title. |
| You can specify a Message Dictionary token in your LOV definition by providing the application short name and the message name. Any title starting with "*" is treated as a Message Dictionary name, and the message contents are substituted for the title. For example: |
TITLE="*FND:MY_MESG_NAME"
| HEADING | Lets you specify a list of column headings and column widths, separated by spaces or commas. There should be one heading in the HEADING="..." parameter for each column in the COLUMN="..." parameter. Specify column widths as numbers in parentheses following the column name, or as an asterisk in parenthesis for a dynamic column width. |
| Column widths you specify in the HEADING ="..." parameter override columns widths you specify in the COLUMN="..." parameter. We recommend setting the widths in the COLUMN option to * (dynamic width) when using the HEADING and COLUMN options together. | |
| You can suppress headings in your LOV window altogether by setting HEADING="N". | |
| You can specify a Message Dictionary token in your LOV definition by providing the application short name and the message name. Any heading starting with "*" is treated as a Message Dictionary name, and the message contents are substituted for the heading. For example: |
HEADING="*FND:MY_MESG_NAME(*)"
If you do not provide an explicit TITLE and HEADING in your SQL validation, your profile has TITLE="user_profile_option_name" and HEADING="N" appended to the definition at runtime. This appended title overrides any heading defined in a COLUMN token or aliases in the SQL statement.
For example, suppose you have an option called SECURITY_LEVEL that uses the codes 1 and 2 to represent the values High and Low respectively. You should select the code column into :PROFILE_OPTION_VALUE and the meaning column into :VISIBLE_OPTION_VALUE. Then, if you want to change the meaning of your codes, you do not have to change your program or form logic. If the value of your profile option is user-defined, you can select the value into both fields. For example, suppose you have a table and form where you maintain equipment information, and you have a profile option called EQUIPMENT. You can select the same value into both :PROFILE_OPTION_VALUE and :VISIBLE_OPTION_VALUE.
Here is an example of a definition for a new profile option called SET_OF_BOOKS_NAME.
SQL="SELECT SET_OF_BOOKS_NAME, SET_OF_BOOKS_NAME \"Set of Books\" '
INTO :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE,
FROM SETS_OF_BOOKS"
COLUMN="\"Set of Books\"(30)"
If you do not enter validation criteria in this field, your user or system administrator can set any value for your profile option, if you allow them to update it.
If Oracle Application Object Library cannot successfully perform your validation, it does not display your profile option the user queries profiles options. If the profile option Utilities:Diagnostics is No, then no error messages appear either. For example, if a user cannot access the table you reference in your validation statement, Oracle Application Object Library does not display the profile option when the user queries profile options on the Profile Values window, and does not display any error message if Utilities:Diagnostics is set to No.
Indicate whether your end users can see and query this profile option in their personal profiles. Otherwise, they cannot query or update values for this option.
Updatable
Indicate whether your end users can change the value of this profile option using their Profile Values window. Otherwise, your system administrator must set values for this profile option.
Indicate whether you can read the value of your profile option from a user exit or concurrent program.
If you enter Yes, you can construct your application to read the value of a user profile option using the Oracle Application Object Library profiles routines.
Updatable
Indicate whether you can change the value of this profile option using Oracle Application Object Library profiles routines.
Suggestion: You should specify Site-level characteristics of every user profile option you create so that the system administrator can assign a Site-level value for every profile option.
You should provide access to each option at the Site level. You can also provide access for any of the other three levels, Application, Responsibility, and User.
Profile option values set at the User profile level override values set at the Responsibility profile level, which override values set at the Application profile level. If no values are set at these three levels, then the value defaults to the value set at the Site profile level if the Site level value has been set.
If you want your end user to be able to update profile option values in the Profile Values window, that is, you chose Updatable in the User Access region, you must provide user visible and updatable access at the User level here.
Visible
Indicate whether your system administrator can see your profile option while setting user profile option values for the specified profile level.
Updatable
Indicate whether your system administrator can change the value of your profile option while setting user profile option values for the profile level you select.
| Previous | Next | Contents | Index | Navigation | Glossary | Library |