OU、库存组织与子库存
select hou.organization_id ou_org_id, --org_id
hou.name ou_name, --ou名称
ood.organization_id org_org_id, --库存组织id
ood.organization_code org_org_code, --库存组织代码
msi.secondary_inventory_name, --子库存名称
msi.description --子库存描述
from hr_organization_information hoi, --组织分类表
hr_operating_units hou, --ou视图
org_organization_definitions ood, --库存组织定义视图
mtl_secondary_inventories msi --子库存信息表
where hoi.org_information1 = 'OPERATING_UNIT'
and hoi.organization_id = hou.organization_id
and ood.operating_unit = hoi.organization_id
and ood.organization_id = msi.organization_id
/*根据描述性弹性域的标题查找描述性弹性域表和列*/ --
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
查找运行请求时间，参数等（可以是某用户的，某个报表）
select c.user_name,
papf.full_name,
b.user_concurrent_program_name,
a.request_date,
a.argument_text,
(a.actual_completion_date - a.actual_start_date) * 24 * 60 minutes,
a.actual_start_date,
a.actual_completion_date,
a.request_id,
a.outfile_name
from fnd_concurrent_requests a,
fnd_concurrent_programs_vl b,
fnd_user c,
per_all_people_f papf
where a.concurrent_program_id = b.concurrent_program_id
and a.requested_by = c.user_id
and c.user_name = papf.employee_number(+)
and a.actual_completion_date is not null
and b.user_concurrent_program_name = '你的程序名称' --- like '%XXX%'
and c.user_name = ' 你要找的用户的'
and a.request_date <=
to_date('2005-03-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and a.request_date >=
to_date('2005-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and a.request_id > 2254198 ---为了提高速度，选一个合适时间点的ID
查询用户客户化的文件配置
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;
EBS查找报表或菜单所属职责
--报表所属职责
SELECT FCPT.USER_CONCURRENT_PROGRAM_NAME,
FRG.REQUEST_GROUP_NAME,
FR.RESPONSIBILITY_KEY
FROM FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_REQUEST_GROUP_UNITS FRGU,
FND_REQUEST_GROUPS FRG,
FND_RESPONSIBILITY FR
WHERE FCPT.CONCURRENT_PROGRAM_ID = FRGU.REQUEST_UNIT_ID
AND FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID
AND lower(FCPT.USER_CONCURRENT_PROGRAM_NAME) LIKE lower('&program_name');
--菜单所属职责
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';
