查看关键用户

得到员工的部门和成本中心

导出AR退款的SQL

基于gl_balances 的部门费用SQL

有查询User具体权限的SQL吗?

查看关键用户
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中有

and b.segment2 = p_department_id
and b.segment3 = p_account_id

这即是假设你的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具体权限的SQL吗?

这就是

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;