以下内容转载自: http://moonsoft.itpub.net/category

--查看当前系统应用的模块

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

-- 检查某个模块的安装状态 (S代表共享安装 I 代表完全安装 n 代表没有安装
select fpi.application_id, fou.install_group_num, fk.application_short_name
  from fnd_product_installations fpi,
       fnd_oracle_userid         fou,
       fnd_application           fk
 where fpi.oracle_id = fou.oracle_id
   and fpi.application_id = fk.application_id
   and fpi.application_id <> 1
 order by 1;

--EBS Interface Tables
select *
  from dba_objects db
 where db.object_type = 'TABLE'
   and db.object_name like '%INTERFACE%'

select *
  from dba_objects db
 where db.object_type = 'TABLE'
   and db.object_name like '%INTERFACE%'
   and owner like 'FA'

--查找报表在哪个权限下运行
--%_ONT_Request_Group
SELECT frg.request_group_name,
       frg.request_group_id --, frt.RESPONSIBILITY_NAME
  FROM fnd_request_groups         frg,
       fnd_request_group_units    frgu,
       fnd_concurrent_programs_tl fcpt
-- fnd_responsibility_tl frt,
--fnd_responsibility fr
 WHERE frg.application_id = frgu.application_id
   AND frg.request_group_id = frgu.request_group_id
   AND frgu.application_id = fcpt.application_id
   AND frgu.request_unit_id = fcpt.concurrent_program_id
      --and frg.request_group_id= fr.request_group_id
      -- AND fr.responsibility_id=frt.responsibility_id
   and fcpt.language = 'US'
      --and frt.language='ZHS'
   AND fcpt.user_concurrent_program_name = 'Contract Execution Report' --报表名字
  

--查看关键用户
select distinct login_name
  from (select c.user_name           as login_name,
               d.full_name           as employee_name,
               f.name                as department_name,
               a.user_id             as user_id,
               a.responsibility_id   as responsibility_id,
               b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME
          from FND_USER_RESP_GROUPS         a,
               FND_RESPONSIBILITY_VL        b,
               fnd_user                     c,
               hr_employees                 d,
               per_assignments_f            e,
               hr_all_organization_units_tl f
         where a.user_id = c.user_id
           and c.employee_id = d.employee_id
           and c.employee_id = e.PERSON_ID
           and e.ORGANIZATION_ID = f.organization_id
           and a.responsibility_id = b.RESPONSIBILITY_ID
           and sysdate > e.EFFECTIVE_START_DATE
           and sysdate < e.EFFECTIVE_END_DATE
           and b.RESPONSIBILITY_NAME not in ('员工自助 (1)')
         order by c.description, c.user_name, a.responsibility_id) a
        
--得到员工的部门和成本中心

select a.full_name as employee_name,
       c.name as hr_depart,
       cux_hr11_report.getDepartmentNameByName(a.full_name) as account_name
  from (select *
          from apps.PER_PEOPLE_f a
         where a.PERSON_TYPE_ID = 6
           and a.LAST_NAME not like '虚拟%'
           and sysdate > a.EFFECTIVE_START_DATE
           and sysdate < a.EFFECTIVE_END_DATE) a,
       per_assignments_f b,
       hr_all_organization_units_tl c
 where a.PERSON_ID = b.ASSIGNMENT_ID
   and b.ORGANIZATION_ID = c.organization_id
   and c.language = 'ZHS'
   and sysdate > b.EFFECTIVE_START_DATE
   and sysdate < b.EFFECTIVE_END_DATE
 order by c.name

--导出AR退款的SQL
begin
  fnd_client_info.set_org_context(85);
end;

select tt.customer_id,
       tt.customer_name,
       tt.customer_number,
       tt.gl_date as shoukuan_date,
       t.GL_DATE as tuikuan_date,
       t.AMOUNT_APPLIED,
       t.TRX_NUMBER,
       tt.receipt_number
  from AR_RECEIVABLE_APPLICATIONS_V t, AR_CASH_RECEIPTS_V tt
 where t.TRX_NUMBER = 'Receipt Write-off'
   and t.CASH_RECEIPT_ID = tt.cash_receipt_id
   and t.GL_DATE > to_date('20050101', 'YYYYMMDD')
   and t.GL_DATE < to_date('20050331', 'YYYYMMDD');

--基于gl_balances 的部门费用SQL
--这是一个统计某个会计科目在某个会计期内的费用合计，在SQL中
--这即是假设你的segment2 为部门段，segment3为会计科目
--budget_version_id为预算的ID，可以用select * from gl.gl_budgets t确认相应的预算id
--set_of_books_id 的值可以用 select * from gl_sets_of_books确认
--该SQL实际证明是完全可*和可信赖的，我们的很多取值都用这个SQL。

select sum(a.period_net_dr - a.period_net_cr)
  from apps.gl_balances a, apps.gl_code_combinations b
 where b.enabled_flag = 'Y'
   and a.set_of_books_id = 1
   and a.code_combination_id = b.code_combination_id
   and nvl(a.budget_version_id, 1022) = 1022
   AND (upper(a.ACTUAL_FLAG) = upper(p_money_type))
   AND trim(nvl(a.PERIOD_NAME, 'XXX')) =
       trim(to_char(to_date(2005 || '-' || 1, 'YYYY-MM'),
                    'MON-YY',
                    'NLS_DATE_LANGUAGE=American'))
   and b.segment2 = p_department_id
   and b.segment3 = p_account_id

--有查询User具体权限
select c.user_name           as login_name,
       d.full_name           as employee_name,
       f.name                as department_name,
       a.user_id             as user_id,
       a.responsibility_id   as responsibility_id,
       b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME
  from FND_USER_RESP_GROUPS         a,
       FND_RESPONSIBILITY_VL        b,
       fnd_user                     c,
       hr_employees                 d,
       per_assignments_f            e,
       hr_all_organization_units_tl f
 where a.user_id = c.user_id
   and c.employee_id = d.employee_id
   and c.employee_id = e.PERSON_ID
   and e.ORGANIZATION_ID = f.organization_id
   and a.responsibility_id = b.RESPONSIBILITY_ID
   and sysdate > e.EFFECTIVE_START_DATE
   and sysdate < e.EFFECTIVE_END_DATE
 order by c.description, c.user_name, a.responsibility_id;

--关联一下fnd_application 表就可以了
select c.user_name as login_name,
       d.full_name as employee_name,
       f.name as department_name,
       a.user_id as user_id,
       a.responsibility_id as responsibility_id,
       b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME,
       g.application_short_name
  from FND_USER_RESP_GROUPS         a,
       FND_RESPONSIBILITY_VL        b,
       fnd_user                     c,
       hr_employees                 d,
       per_assignments_f            e,
       hr_all_organization_units_tl f,
       fnd_application              g
 where a.user_id = c.user_id
   and c.employee_id = d.employee_id
   and c.employee_id = e.PERSON_ID
   and e.ORGANIZATION_ID = f.organization_id
   and a.responsibility_id = b.RESPONSIBILITY_ID
   and sysdate > e.EFFECTIVE_START_DATE
   and sysdate < e.EFFECTIVE_END_DATE
   and b.APPLICATION_ID = g.application_id
 order by c.description, c.user_name, a.responsibility_id;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17087603/viewspace-687320/，如需转载，请注明出处，否则将追究法律责任。