后台维护常用SQL

后台维护常用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

/*根据描述性d性域的标题查找描述性d性域表和列*/ --

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