后台维护常用SQL

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