EBS预置文件设置以及系统之间的移植

由于我们有很多单位，并且每个单位的系统都是一样的，统一由我们这边维护。所以每个所的需求不一样，系统的功能就会存在差别，这就可能造成系统版本无法控制，以后升级也很难操作，维护起来也比较麻烦。比如一个FORM，有些用户需要一些按钮，有些用户不需要。我们要做成统一的FORM，至于需要哪些按钮就让用户去控制，控制的方法就是系统的配置文件的值。这里说的配置文件就类似于一个开关，控制一些功能的可用和不可用性。

责任-〉应用开发员

菜单：配置文件

名称：配置文件的必须唯一，在ORACLE应用对象库中使用这个名字。

比如上面所说的控制某个按钮可不可用，可以通过查询这个配置文件的值来进行控制。

读取这个配置文件值得方法是：fnd_profile.value(‘PA_TASKS_CREATE_BUTTON’),括号中的就是这个配置文件的名称。

用户配置文件名：这是用户看到的配置文件名。用户在下面的窗口中为这个配置文件选定值。

责任-〉系统管理员。

菜单路径〉配置文件-〉系统。找到上面定义的用户配置文件值

在这里用户可以直接为这个配置文件选定值。比如如果选是，则按钮可用，选否，按钮不可用。

 

下面说一下定义配置文件中最重要的一项：SQL验证。

如果你想让你的用户在为配置文件选定值的时候出现一个值列表，让用户在值列表中选定值（当然也可以没有这个值列表），就需要定义SQL验证这个FIELD.

要验证你的配置文件选项，把配置文件的值放入到:PROFILE_OPTION_VALUE和:VISIBLE_OPTION_VALUE中，其中前者是用FND_PROFILE.VALUE来读取的，后者是用户看到的配置文件的值。

语法如下：

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 ：一个SELECT语句. 选择要在你的LOV中显示的行数据。在这个SQL语句中，你可以指定列的别名。INTO语句是把这些列的值在FORM中显示。

建议：列的别名不要超过30个字符，否则就容易出错误。

HEADING选项会覆盖COLUMN的长度和别名。

这里的SQL语句不同于别的SQL语句，首先，如果你想在你的COLUMN别名中包括大小，你必须用一个反斜杠和双引号。如下例所示：

SQL="SELECT LOOKUP_CODE,MEANING \"Retirement Cost Processing \"
INTO  :PROFILE_OPTION_VALUE, :VISIBLE_OPTION_VALUE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE='YES_NO'"
COLUMN="\"Retirement Cost Processing\"(15)"
你可以在主查询中使用GROUP BY或者HAVING语句，但是不能在子查询中使用。

可以在SELECT或者HAVING中使用MIN(),MAX(),SUM(),COUNG()等，但是被选择进:PROFILE_OPTION_VALUE或者VISIBLE_OPTION_VALUE的列不能用这些函数。

对这个SQL语句的限制还有很多。请参考下面的英文文档。

 

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

                                   EPT.DEPTNAME, EPT.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.

 

 

 

下面说一下配置文件的移植。

如果你有多个一样的ERP系统，如果没有下面的移植，你需要在每个系统中做一遍这样的操作。而用下面的移植的方法，只需在服务器上执行两个命令就可以。

配置文件

Download 配置文件使用如下脚本:

$FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct xxx.ldt

PROFILE FND_PROFILE_OPTION_VALUES  PROFILE_NAME=”xxx“ APPLICATION_SHORT_NAME=”xxx”

Upload配置文件使用如下脚本:

$FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct xxx.ldt