注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Memory extender

Beautiful Day..

 
 
 

日志

 
 

Profile值的查询方式  

2012-03-22 17:25:38|  分类: EBS |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0 
Connected as apps@DEV
 
SQL> 
SQL> SELECT fpo.user_profile_option_name,
  2         fpv.level_id,
  3         fpv.level_value,
  4         fpv.profile_option_value
  5    FROM apps.fnd_profile_option_values fpv, apps.fnd_profile_options_vl fpo
  6   WHERE fpv.application_id = fpo.application_id
  7     AND fpv.profile_option_id = fpo.profile_option_id
  8     AND fpo.profile_option_name = 'APPLICATIONS_HOME_PAGE';
 
USER_PROFILE_OPTION_NAME                                        LEVEL_ID LEVEL_VALUE PROFILE_OPTION_VALUE
---------------------------------------------------------------------------- -------------- -------------------- ----------------------------------------------
Self Service Personal Home Page mode                              10001        0                        FWK
Self Service Personal Home Page mode                              10002        672                    NONE
Self Service Personal Home Page mode                              10004        0                        FWK
Self Service Personal Home Page mode                              10004        1110                  PHP
 
SQL> 
SQL> SELECT flv.lookup_type, flv.lookup_code, flv.meaning
  2    FROM fnd_lookup_values_vl flv
  3   WHERE flv.lookup_type = 'FND_PROFILE_LEVELS';
 
LOOKUP_TYPE                  LOOKUP_CODE   MEANING
----------------------------------- ------------------------- --------------------------------------------------------------------------------
FND_PROFILE_LEVELS    APPLICATION         Application
FND_PROFILE_LEVELS    RESPONSIBILITY   Responsibility
FND_PROFILE_LEVELS    SITE                        Site
FND_PROFILE_LEVELS    USER                      User
 
SQL> 

网上查询到:
10001表示SITE
10002表示APPLICATION
10003表示RESPONSIBILITY
10004表示USER
没找着地儿考证。。。就这样吧。

如果是SITE,后面的LEVEL_VALUE应该只有0吧?
如果是APPLICATION,后面的LEVEL_VALUE则对应FND_APPLICATION中查找对应的APPLICATION
如果是 RESPONSIBILITY ,后面的LEVEL_VALUE则对应FND_ RESPONSIBILITY 中查找对应的RESPONSIBILITY
如果是 USER ,后面的LEVEL_VALUE则对应FND_USER 中查找对应的USER

PROFILE_OPTION_VALUE则是相应LEVEL值所设定的profile值

下面为从网络上某Blog中保存来的抓取Profile值的sql,感谢作者。抱歉的是没有保存链接地址。。

/*抓取Profile值的SQL
The script has the following optional (which means all may be left blank) parameters:

Parameter Description
Default value
Example
LANG Profile options translated in (installed!) language US NL
SEARCH Search argument for profile options % GL%ooks%
MODULE Applications Module % Pay%
LAST_UPDATE Include only the updates on or after this date, format is DD-MON-RRRR 01-JAN-1000 21-DEC-2007
PROF_UPD_BY Username who updated this profile % OPERATIONS



The script has the following columns:

Column Description
Profile Level
Level 1 = Site
Level 2 = Application
Level 3 = Responsibility
Level 4 = User

Site In case of site level value, it has the value 'SITE'
Application Level In case of application level value, it has the name of the application
Responsibility Level In case of responsibility level value, it has the name of the responsibility
User Level In case of user level value, it has the name of the user
Profile Name Profile Option name
Profile Option Value Value of the Profile Option
Source Module Related source module. E.g. ADI profile options belong to General Ledger module
Last Update Date Last update date
Update By User name who performed the last update
*/
SELECT FPOV.LEVEL_ID - 10000 PROFILE_LEVEL, -- SITE level
' ' SITE_LEVEL,
APP.APPLICATION_NAME APPLICATION_LEVEL,
' ' RESPONSIBILITY_LEVEL,
' ' USER_LEVEL,
FPOT.USER_PROFILE_OPTION_NAME PROFILE_NAME,
FPOV.PROFILE_OPTION_VALUE,
FAT.APPLICATION_NAME SOURCE_MODULE,
FPOV.LAST_UPDATE_DATE,
FU.USER_NAME UPDATE_BY
FROM APPS.FND_PROFILE_OPTION_VALUES FPOV,
APPS.FND_APPLICATION_TL APP,
APPS.FND_PROFILE_OPTIONS_TL FPOT,
APPS.FND_PROFILE_OPTIONS FPO,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_USER FU
WHERE FPOV.LEVEL_ID = 10002
AND APP.LANGUAGE = nvl('&LANG', 'US')
AND APP.LANGUAGE = FPOT.LANGUAGE
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPOV.LEVEL_VALUE = APP.APPLICATION_ID
AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
AND FPOV.APPLICATION_ID = FAT.APPLICATION_ID
AND FPOT.USER_PROFILE_OPTION_NAME LIKE '&SEARCH%'
AND FAT.APPLICATION_NAME LIKE '&MODULE%'
AND FAT.LANGUAGE = FPOT.LANGUAGE
AND FPOV.LAST_UPDATE_DATE >=
to_date(nvl('&LAST_UPDATE', '01-JAN-1000'), 'DD-MON-YYYY')
AND FPOV.LAST_UPDATED_BY = FU.USER_ID
AND FU.USER_NAME LIKE UPPER('&PROF_UPD_BY%')
UNION
SELECT LEVEL_ID - 10000 PROFILE_LEVEL, -- USER level
' ',
' ',
' ',
USERS.USER_NAME,
FPOT.USER_PROFILE_OPTION_NAME PROFILE_NAME,
FPOV.PROFILE_OPTION_VALUE,
FAT.APPLICATION_NAME BRON_APPLICATIE,
FPOV.LAST_UPDATE_DATE,
FU.USER_NAME
FROM APPS.FND_PROFILE_OPTION_VALUES FPOV,
APPS.FND_USER USERS,
APPS.FND_PROFILE_OPTIONS_TL FPOT,
APPS.FND_PROFILE_OPTIONS FPO,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_USER FU
WHERE LEVEL_ID = 10004
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPOV.LEVEL_VALUE = USERS.USER_ID
AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
AND FPOT.LANGUAGE = nvl('&LANG', 'US')
AND FPOV.APPLICATION_ID = FAT.APPLICATION_ID
AND FAT.LANGUAGE = FPOT.LANGUAGE
AND FPOT.USER_PROFILE_OPTION_NAME LIKE '&SEARCH%'
AND FAT.APPLICATION_NAME LIKE '&MODULE%'
AND FPOV.LAST_UPDATE_DATE >=
to_date(nvl('&LAST_UPDATE', '01-JAN-1000'), 'DD-MON-YYYY')
AND FPOV.LAST_UPDATED_BY = FU.USER_ID
AND FU.USER_NAME LIKE UPPER('&PROF_UPD_BY%')
UNION
SELECT LEVEL_ID - 10000 PROFILE_LEVEL, -- APPLICATION level
'SITE' SITE,
' ',
' ',
' ',
FPOT.USER_PROFILE_OPTION_NAME PROFILE_NAME,
FPOV.PROFILE_OPTION_VALUE,
FAT.APPLICATION_NAME BRON_APPLICATIE,
FPOV.LAST_UPDATE_DATE,
FU.USER_NAME
FROM APPS.FND_PROFILE_OPTION_VALUES FPOV,
APPS.FND_PROFILE_OPTIONS_TL FPOT,
APPS.FND_PROFILE_OPTIONS FPO,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_USER FU
WHERE LEVEL_ID = 10001
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
AND FPOT.LANGUAGE = nvl('&LANG', 'US')
AND FPOV.APPLICATION_ID = FAT.APPLICATION_ID
AND FAT.LANGUAGE = FPOT.LANGUAGE
AND FPOT.USER_PROFILE_OPTION_NAME LIKE '&SEARCH%'
AND FAT.APPLICATION_NAME LIKE '&MODULE%'
AND FPOV.LAST_UPDATE_DATE >=
to_date(nvl('&LAST_UPDATE', '01-JAN-1000'), 'DD-MON-YYYY')
AND FPOV.LAST_UPDATED_BY = FU.USER_ID
AND FU.USER_NAME LIKE UPPER('&PROF_UPD_BY%')
UNION
SELECT LEVEL_ID - 10000 PROFILE_LEVEL, -- RESPONSIBILITY level
' ',
' ',
FRT.RESPONSIBILITY_NAME,
' ',
FPOT.USER_PROFILE_OPTION_NAME PROFILE_NAME,
FPOV.PROFILE_OPTION_VALUE,
FAT.APPLICATION_NAME BRON_APPLICATIE,
FPOV.LAST_UPDATE_DATE,
FU.USER_NAME
FROM APPS.FND_PROFILE_OPTION_VALUES FPOV,
APPS.FND_PROFILE_OPTIONS_TL FPOT,
APPS.FND_PROFILE_OPTIONS FPO,
APPS.FND_RESPONSIBILITY_TL FRT,
APPS.FND_APPLICATION_TL FAT,
APPS.FND_USER FU
WHERE LEVEL_ID = 10003
AND FRT.RESPONSIBILITY_ID = FPOV.LEVEL_VALUE
AND FRT.LANGUAGE = FPOT.LANGUAGE
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
AND FPOT.LANGUAGE = nvl('&LANG', 'US')
AND FPOV.APPLICATION_ID = FAT.APPLICATION_ID
AND FAT.LANGUAGE = FPOT.LANGUAGE
AND FPOT.USER_PROFILE_OPTION_NAME LIKE '&SEARCH%'
AND FAT.APPLICATION_NAME LIKE '&MODULE%'
AND FPOV.LAST_UPDATE_DATE >=
to_date(nvl('&LAST_UPDATE', '01-JAN-1000'), 'DD-MON-YYYY')
AND FPOV.LAST_UPDATED_BY = FU.USER_ID
AND FU.USER_NAME LIKE UPPER('&PROF_UPD_BY%')
ORDER BY PROFILE_NAME, PROFILE_LEVEL;







  评论这张
 
阅读(1067)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017