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';