EBS 开发常用SQL

1.查看当前系统定义了那些值是中文的段值。

SELECT *
FROM
fnd_flex_value_sets ffvs
WHERE
ffvs.flex_value_set_name LIKE 'CUX%'
AND
EXISTS (SELECT 1
FROM
fnd_flex_values_vl ffv
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
AND
(ascii(substr(ffv.flex_value
,1
,1)) < 0 OR ascii(substr(ffv.flex_value
,1
,1)) > 127)
AND
ffv.enabled_flag = 'Y');

 

2.查询当前系统程序包中引用内容

SELECT *
FROM
all_source t
WHERE
t.type = 'PACKAGE BODY'
AND
t.name LIKE 'XX%'
AND
t.owner LIKE 'XX%'
AND
t.text LIKE '%per_org_structure_versions%';

 

3.查询当前EBS的版本

SELECT *
FROM
fnd_product_groups;

 

4.查看单取EBS数据库版本

SELECT *
FROM
v$version;

5.查看所选表的字段信息

SELECT *
FROM
all_tab_columns
WHERE
table_name = 'PO_HEADERS_ALL';

 

6.查找当前应用服务器的网址

SELECT fnd_profile.value('APPS_WEB_AGENT')
FROM
dual;

7.查看表或者视图被某个对象引用

SELECT *
FROM
all_dependencies dep
WHERE
dep.referenced_name = 'MTL_SAFETY_STOCKS';

8.查看当前最耗时的进程

SELECT * FROM
v$session_longops;

 

10.查看系统配置文件状态

SELECT fp.level_id
,fp.level_value
,fp.profile_option_value
FROM
fnd_profile_options_vl
fpo
,fnd_profile_option_values fp
WHERE
fpo.application_id = fp.application_id
AND
fpo.profile_option_id = fp.profile_option_id
AND
fpo.user_profile_option_name = 'FND: Debug Log Enabled';

11.查看当前系统的应用模块

SELECT fnd_application.application_short_name
,fnd_application.application_id
,fnd_product_installations.status
FROM
fnd_application
,fnd_product_installations
WHERE
fnd_application.application_id = fnd_product_installations.application_id;

12.利用应用简称或应用名称查找应用模块详细信息

SELECT fapp.application_short_name
,fapptl.application_name
,fapp.*
FROM
fnd_application
fapp
,fnd_application_tl fapptl
WHERE
fapptl.application_name = 'Purchasing' -- fapp.application_short_name='XXSAT'
AND
fapp.application_id = fapptl.application_id
AND
fapptl.language = 'US'

13.查看当前EBS系统注册的用户

SELECT *
FROM
fnd_user
WHERE
user_name = upper('sysadmin');

14.查询消息的定义

SELECT fa.application_short_name
,
fm.language_code
,
fm.message_name
,
fm.message_text
,
fm.type
FROM
fnd_new_messages fm
,
fnd_application
fa
WHERE
fm.message_name = 'FWK_TBX_T_PAGE_GENERAL'
AND
fm.application_id = fa.application_id
AND
fa.application_short_name = 'AK'

 

15.查看EBS系统当前的语言配置

SELECT userenv('language')
FROM
dual;

 

16.查看所有配置文件的名字

SELECT *
FROM
fnd_profile_options_tl t
WHERE
t.profile_option_name = 'AFLOG_ENABLED';

17.查看配置文件的sql

SELECT t.user_profile_option_name "Profile Option"
,decode(a.level_id
,10001
,'Site'
,10002
,'Application'
,10003
,'Responsibility'
,10004
,'User') "Level"
,decode(a.level_id
,10001
,'Site'
,10002
,b.application_short_name
,10003
,c.responsibility_key
,10004
,d.user_name) "Level Value"
,a.profile_option_value "Profile Value"
FROM
fnd_profile_option_values a
,fnd_application
b
,fnd_responsibility
c
,fnd_user
d
,fnd_profile_options
e
,fnd_profile_options_tl
t
WHERE
a.profile_option_id = e.profile_option_id
AND
e.profile_option_name = 'RCV_TP_MODE'
AND
a.level_value = b.application_id(+)
AND
a.level_value = c.responsibility_id(+)
AND
a.level_value = d.user_id(+)
AND
t.profile_option_name = e.profile_option_name
AND
t.language = 'US'
ORDER
BY e.profile_option_name
,a.level_id DESC;

15.时间转换为数字

SELECT to_number(substr(to_char(to_date('12:56:23'
,'hh24:mi:SS')
,'hh24miss')
,1
,2)) * 3600 + to_number(substr(to_char(to_date('12:56:23'
,'hh24:mi:SS')
,'hh24miss')
,3
,2)) * 60 + to_number(substr(to_char(to_date('12:56:23'
,'hh24:mi:SS')
,'hh24miss')
,5
,2))
FROM
dual;

 

16.数字转换为时间

SELECT to_char(trunc(46583 / 3600)) || ':' || to_char(trunc(MOD(46583
,3600) / 60)) || ':' ||
to_char(trunc(MOD(MOD(46583
,3600)
,60)))
FROM
dual;

 

17.获得当前会话编号

SELECT userenv('SESSIONID')
FROM
dual;

 

18.查看定义的并发程序挂在那个职责

SELECT responsibility_name
,frg.request_group_name
,fcpv.concurrent_program_name
,fcpv.user_concurrent_program_name
,fcpv.description
FROM
fnd_request_groups
frg
,fnd_request_group_units
frgu
,fnd_concurrent_programs_vl fcpv
,fnd_responsibility_vl
frv
WHERE
frgu.request_unit_type = 'P'
AND
frgu.request_group_id = frg.request_group_id
AND
frgu.request_unit_id = fcpv.concurrent_program_id
AND
frv.request_group_id(+) = frg.request_group_id
AND
fcpv.user_concurrent_program_name = < 并发程序全称，例： 'CUX:接收入库单' >
ORDER
BY responsibility_name

 

19.查看所查内容被那些包引用

SELECT *
FROM
all_source t
WHERE
t.text LIKE '%IBY_DISBURSEMENT_COMP_PUB%';

 

20.查看当前EBS数据库对应字符集

SELECT *
FROM
nls_database_parameters;

 

21.查看数据库用户密码

略

22.查找功能所属职责

SELECT fffv.function_name
,fm.menu_name
,fr.responsibility_key
FROM
fnd_form_functions_vl fffv
,fnd_menu_entries_vl
fmev
,fnd_menus
fm
,fnd_responsibility
fr
WHERE
fffv.function_id = fmev.function_id
AND
fmev.menu_id = fm.menu_id
AND
fmev.menu_id = fr.menu_id(+)
AND
fffv.function_name = '&function_name';

 

23.查看配置文件配置

SELECT pro.profile_option_name
,pro.user_profile_option_name
,lev.level_type TYPE
,lev.level_code
,lev.level_name
,prv.profile_option_value
FROM
apps.fnd_profile_options_vl pro
,applsys.fnd_profile_option_values prv
,(SELECT 10001 level_id
,'Site' level_type
,0 level_value
,'Site' level_code
,'Site' level_name
FROM
dual
UNION ALL
SELECT 10002 level_id
,'App' level_type
,app.application_id level_value
,app.application_short_name level_code
,app.application_name level_name
FROM
apps.fnd_application_vl app
UNION ALL
SELECT 10003 level_id
,'Resp' level_type
,resp.responsibility_id level_value
,resp.responsibility_key level_code
,resp.responsibility_name level_name
FROM
apps.fnd_responsibility_vl resp
UNION ALL
SELECT 10004 level_id
,'User' level_type
,usr.user_id level_value
,usr.user_name level_code
,usr.user_name level_name
FROM
applsys.fnd_user usr) lev
WHERE
pro.profile_option_id = prv.profile_option_id(+)
AND
prv.level_id = lev.level_id(+)
AND
prv.level_value = lev.level_value(+)
AND
pro.user_profile_option_name LIKE 'CUX%' --Profile名称
ORDER
BY pro.profile_option_name
,lev.level_type
,lev.level_name;

24.查看Oracle Patch版本

SELECT dd.patch_name
,pp.creation_date
,pp.driver_file_name
,lang.language
FROM
ad_patch_drivers
pp
,ad_applied_patches
dd
,ad_patch_driver_langs lang
WHERE
pp.applied_patch_id = dd.applied_patch_id
AND
lang.patch_driver_id = pp.patch_driver_id
AND
lang.language = userenv('LANG')
ORDER
BY pp.creation_date;

 

25.根据描述性弹性域的标题查找描述性弹性域表和列

SELECT fnd_dfv.title
,fnd_dfv.descriptive_flexfield_name
,fnd_dfv.application_table_name
,fnd_dfu.application_column_name
,fnd_dfu.form_left_prompt
,fnd_dfu.form_above_prompt
FROM
fnd_descriptive_flexs_vl
fnd_dfv
,fnd_descr_flex_col_usage_vl fnd_dfu
WHERE
fnd_dfv.title = '物料' --如：物料
AND
fnd_dfu.descriptive_flexfield_name = fnd_dfv.descriptive_flexfield_name;

 

26.查看当前环境登录的用户

SELECT u.user_name
,app.application_short_name
,fat.application_name
,fr.responsibility_key
,frt.responsibility_name
,fff.function_name
,fft.user_function_name
,icx.function_type
,icx.first_connect
,icx.last_connect
FROM
icx_sessions
icx
,fnd_user
u
,fnd_application
app
,fnd_application_tl
fat
,fnd_responsibility
fr
,fnd_responsibility_tl frt
,fnd_form_functions
fff
,fnd_form_functions_tl fft
WHERE
1 = 1
AND
u.user_id = icx.user_id
AND
icx.responsibility_application_id = app.application_id
AND
fat.application_id = icx.responsibility_application_id
AND
fat.language = 'ZHS'
AND
fr.application_id = icx.responsibility_application_id
AND
fr.responsibility_id = icx.responsibility_id
AND
frt.language = 'ZHS'
AND
frt.application_id = icx.responsibility_application_id
AND
frt.responsibility_id = icx.responsibility_id
AND
fff.function_id = icx.function_id
AND
fft.function_id = icx.function_id
AND
icx.disabled_flag != 'Y'
AND
icx.pseudo_flag = 'N'
AND
(icx.last_connect + decode(fnd_profile.value('ICX_SESSION_TIMEOUT')
,NULL
,icx.limit_time
,0
,icx.limit_time
,fnd_profile.value('ICX_SESSION_TIMEOUT') / 60) / 24) > SYSDATE
AND
icx.counter < icx.limit_connects;

 

27.查看当前系统所有存在个性化的功能

SELECT DISTINCT fun.user_function_name
,fun.function_name
FROM
fnd_form_custom_rules fcr
,fnd_form_functions_vl fun
WHERE
fun.function_name = fcr.function_name
ORDER
BY fun.function_name;

 

28.查看当前请求挂在那些请求组中

SELECT rg.request_group_name
,fcp.concurrent_program_name
,fcp.user_concurrent_program_name
,rg.application_id
,rg.request_group_id
FROM
fnd_request_groups
rg
,fnd_request_group_units
rgu
,fnd_concurrent_programs_vl fcp
WHERE
rg.request_group_id = rgu.request_group_id
AND
rgu.request_unit_id = fcp.concurrent_program_id
AND
fcp.user_concurrent_program_name = 'CUX:库存帐龄表';

 

29.查看配置文件的配置情况

SELECT t.user_profile_option_name us_name
,l.user_profile_option_name zhs_name
,decode(v.level_id
,10001
,'地点层'
,10002
,'应用层'
,10003
,'职责层'
,10004
,'用户层'
,10005
,'服务器层'
,10006
,'组织层'
,'其它') level_name
,decode(v.level_id
,10002
,(SELECT application_name
FROM
apps.fnd_application_vl
WHERE
application_id = v.level_value)
,10003
,(SELECT responsibility_name
FROM
apps.fnd_responsibility_vl
WHERE
responsibility_id = v.level_value
AND
application_id = v.level_value_application_id)
,10004
,(SELECT user_name
FROM
apps.fnd_user
WHERE
user_id = v.level_value)
,10005
,(SELECT node_name
FROM
apps.fnd_nodes
WHERE
node_id = v.level_value)
,10006
,(SELECT NAME
FROM
apps.hr_operating_units
WHERE
organization_id = v.level_value)
,v.level_value) level_value
,v.profile_option_value
,u.user_name
,v.last_update_date
FROM
apps.fnd_profile_options_vl
t
,apps.fnd_profile_options_tl
l
,apps.fnd_profile_option_values v
,apps.fnd_user
u
WHERE
t.application_id = v.application_id
AND
t.profile_option_id = v.profile_option_id
AND
t.profile_option_name = l.profile_option_name
AND
l.language = 'ZHS'
AND
u.user_id = v.last_updated_by
AND
t.start_date_active <= SYSDATE
AND
nvl(t.end_date_active
,SYSDATE) >= SYSDATE
AND
user_id NOT IN (-1
,0
,1
,2
,3
,4
,5
,6
,7)
AND
l.user_profile_option_name = 'MO：安全性配置文件'
ORDER
BY t.user_profile_option_name;

 

30.报表trace定位

SELECT 'Request id: ' || request_id
,'Trace id: ' || oracle_process_id
,'Trace Flag: ' || req.enable_trace
,'Trace Name: ' || dest.value || '/' || lower(dbnm.value) || '_ora_' || oracle_process_id || '.trc'
,'Prog. Name: ' || prog.user_concurrent_program_name
,'File Name: ' || execname.execution_file_name || execname.subroutine_name
,'Status : ' || decode(phase_code
,'R'
,'Running') || '-' || decode(status_code
,'R'
,'Normal')
,'SID Serial: ' || ses.sid || ',' || ses.serial#
,'Module : ' || ses.module
FROM
fnd_concurrent_requests
req
,v$session
ses
,v$process
proc
,v$parameter
dest
,v$parameter
dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables
execname
WHERE
req.request_id = &request_id
AND
req.oracle_process_id = proc.spid(+)
AND
proc.addr = ses.paddr(+)
AND
dest.name = 'user_dump_dest'
AND
dbnm.name = 'db_name'
AND
req.concurrent_program_id = prog.concurrent_program_id
AND
req.program_application_id = prog.application_id
AND
prog.application_id = execname.application_id
AND
prog.executable_id = execname.executable_id;

 

31.停止正在运行的请求

SELECT fcr.rowid
,fcr.*
FROM
fnd_concurrent_requests fcr
WHERE
1 = 1
AND
fcr.phase_code IN ( /*'P',*/'R')
AND
fcr.request_id = 558746; --phase_code,status_code = C

 

32.查看当前EBS环境的服务节点部署情况

SELECT NAME,
server_type
FROM
fnd_app_servers,
fnd_nodes
WHERE
fnd_app_servers.node_id = fnd_nodes.node_id
AND
server_type IN ('APPS'
,'DB');