首页 > Linux操作系统 > Linux操作系统 > EBS 基本的SQL 汇总
以下内容转载自: 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/,如需转载,请注明出处,否则将追究法律责任。