Oracle EBS 基础概念：设定档

配置文件影响到应用产品的运行方式，可作用于以下四个不同的层次：地点，应用层，责任层，用户层。

1. Profile的定义
Navigate to: App Dev -> Profile

SQL验证用于提供用户在设置该Profile值的时候提供LOV数据来源，如果LOV来源不是系统原有表中的数据，而是需要自己定义的，那么可使用值集定义好数据源，然后在SQL中使用。

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}"]

验证语法中注意事项：
A. 如果列的别名是两个单词或更多，则需要用一个反斜杠和双引号括起来。

B. 在HEADING中指定列的宽度会把在COLUMN中指定列的值给覆盖掉。所以在同时用COLUMN和HEADING时，一般建议把COLUMN中列的长度指定为动态宽度，即（*）

C. 如果不明确指定TITLE和HEADING,则系统默认TITLE=‘user_profile_option_name’ 和HEADING=‘N’.

2. 程序中如何使用Profile
在程序中可以使用FND_PROFILE.VALUE (‘Profile Name’) 来获取Profile取值

3. 常用Profile
 避免长时间不登录需重新登录
Profile -> ICX:Session Timeout
用户在Application中不进行操作的时间（分钟）
If this profile option to 0 or NULL, then user session will never time out due to inactivity.

Profile -> ICX: Limit time
用户session的绝对持续时间（小时）
This profile determines the absolute maximum duration (in hours) of a user’s session.

 Oracle EBS 用户密码复杂度校验
Profile -> SIGNON_PASSWORD_FAILURE_LIMIT
登录口令失败限制次数

Profile -> SIGNON_PASSWORD_HARD_TO_GUESS
登录口令应难以猜测，密码复杂度包含：
密码至少包含一个字母和一个数字
密码不能包含用户名
密码不能重复某个字母或者数字

Profile -> SIGNON_PASSWORD_LENGTH
登录口令长度：应用用户口令的最小长度，默认为5，可以设置成更大

 其他
Profile -> INV: Updateable item name
更改物料编码

 Oracle EBS Form配色方案
Profile -> Java Look and Feel
Default： title字体，输入栏都不加粗
Oracle： title字体不加粗，输入栏加粗，默认的蓝色调
Generic：title字体不加粗，输入栏加粗，传统Window风格

Profile -> Java Color Scheme

4. 相关SQL
–Profile定义

select (select application_name from FND_APPLICATION_VL 
where application_id = A.application_id) application_name,
       A.profile_option_id,
       A.profile_option_name,
       A.user_profile_option_name,
       A.description,
       A.creation_date,
       (select user_name from fnd_user where user_id = A.created_by) created_by,
       start_date_active, end_date_active,
       A.sql_validation
from FND_PROFILE_OPTIONS_VL A
order by created_by, creation_date

–所有Profile的取值

SELECT p.profile_option_name SHORT_NAME,
       n.user_profile_option_name NAME,
       decode(v.level_id,10001,'Site',10002,'Application',10003,'Responsibility',
              10004,'User',10005,'Server',10006,'Org',
10007,decode(to_char(v.level_value2),'-1','Responsibility',
                    decode(to_char(v.level_value), '-1', 'Server','Server+Resp')),
              'UnDef') LEVEL_SET,
       decode(to_char(v.level_id),'10001','','10002',app.application_short_name,
              '10003',rsp.responsibility_key,'10004',usr.user_name,
              '10005',svr.node_name,'10006',org.name,
              '10007',decode(to_char(v.level_value2),'-1',rsp.responsibility_key,
                      decode(to_char(v.level_value),'-1',
                            (SELECT node_name
                               FROM fnd_nodes
                              WHERE node_id = v.level_value2),
                            (SELECT node_name
                               FROM fnd_nodes
                              WHERE node_id = v.level_value2) || '-' ||
                            rsp.responsibility_key)),'UnDef') "CONTEXT",
       v.profile_option_value VALUE,
      v.creation_date,
      (select user_name from fnd_user where user_id = v.created_by) created_by,
      v.last_update_date,
      (select user_name from fnd_user where user_id = v.last_updated_by) last_updated_by
  FROM fnd_profile_options       p,
       fnd_profile_option_values v,
       fnd_profile_options_tl    n,
       fnd_user                  usr,
       fnd_application           app,
       fnd_responsibility        rsp,
       fnd_nodes                 svr,
       hr_operating_units        org
 WHERE p.profile_option_id = v.profile_option_id(+)
   AND p.profile_option_name = n.profile_option_name
   AND upper(p.profile_option_name) IN
       (SELECT profile_option_name
          FROM fnd_profile_options_tl
         WHERE upper(user_profile_option_name) LIKE
               upper('%&user_profile_name%'))
   AND usr.user_id(+) = v.level_value
   AND rsp.application_id(+) = v.level_value_application_id
   AND rsp.responsibility_id(+) = v.level_value
   AND app.application_id(+) = v.level_value
   AND svr.node_id(+) = v.level_value
   AND org.organization_id(+) = v.level_value
 ORDER BY short_name,user_profile_option_name,
          level_id,level_set;

–两个用户间Profile取值不同

select *
 from (select o.profile_option_id,
              o.user_profile_option_name,
              v.profile_option_value
         from fnd_profile_options_vl  o,
              fnd_profile_option_values  v,
              fnd_user u
         where o.profile_option_id = v.profile_option_id
           and v.level_id = 10004  --user level
           and v.level_value = u.user_id
           and u.user_name = &user1_name) a
    full outer join
---------------------------------------------------------
      (select o.profile_option_id,
              o.user_profile_option_name,
              v.profile_option_value
         from fnd_profile_options_vl  o,
              fnd_profile_option_values  v,
              fnd_user u
         where o.profile_option_id = v.profile_option_id
           and v.level_id = 10004  --user level
           and v.level_value = u.user_id
           and u.user_name = &user2_name) b on a.profile_option_id = b.profile_option_id
---------------------------------------------------------
where a.profile_option_value != b.profile_option_value
   or (a.profile_option_value is null and b.profile_option_value is not null)
   or (a.profile_option_value is not null and b.profile_option_value is null)